Confidential · BRC + eSynergy
British Red Cross × Databricks · Discovery Playback

The Common Operating Picture

Emergency status updates from every team, captured straight into governed Databricks tables. The COP Status Update App for British Red Cross.

What we heard, what we propose to build, and how it works: the users, the actual screens, the data model and the architecture. This playback is the story; its companion is the clickable walkthrough. Yours to accept, adjust, or push back on before we build.

Fixed-price PoC Databricks DCIF · $15k Live by 30 Nov 2026 Built on Databricks Apps 100+ registered users at scale
Prepared by eSynergy: Salman Iqbal, Amir Tayabali For Chris, Alexei, Harry (BRC) Companion the interactive walkthrough
01

What we heard

Before we propose anything, here is the problem in your words, so you can tell us if we've understood it correctly.

The trigger

When BRC grades an emergency Grade 3 or 4, a whole-of-organisation response, you stand up the Common Operating Picture. This app is the qualitative capture step inside that process.

The gap today

Status updates are gathered on Microsoft Forms, land in SharePoint, and are manually wrangled into a Power BI picture. Capture, storage and analysis are stitched together after the fact.

The change

Replace the capture step with a Databricks App that writes structured submissions straight into governed Delta tables: capture, governance and analytics on one platform, analysis-ready on submit.

What's captured

The qualitative team-lead updates you can't pull from HR, finance or volunteering systems: anticipated pressure, current capacity, priority-service impact, and free-text concerns.

Two moments

An Initial update once at the foothills of an emergency (anticipated impact), then a Recurring update every week tracking how teams are actually holding up.

Where it lands

The leadership dashboard is out of scope here, but it's the whole reason data must land in Databricks. We build the governed source of truth it reads from, and it opens the door to an AI-assisted insight layer as a follow-on (see §7).

02

Who uses it

Three groups touch the app directly, plus a technical admin. Responses are tracked at team level, not individual; anyone on a team can submit if the usual lead is unavailable.

TL

Team Lead

~20 heads of team, per event
  • Receives a link, logs in with Entra ID
  • Submits the Initial update once, then weekly Recurring updates
  • Desktop-first, but must work on a phone in the field
Submits
IM

IM Cell

Information Management: "the middle"
  • Registers an emergency, sets the distribution list
  • Issues the forms and tracks who has / hasn't responded
  • Exports responses, must not need Databricks or SQL
Operates the process
SI

Strategic Insight

Analyst team (Alexei)
  • Reads the qualitative free-text responses
  • Manually themes and summarises them
  • Feeds summaries onward to the leadership picture
Analyses
AD

Admin

Small, technical group
  • Maintains the app and its form questions
  • Near-term: a config change via the repo, not a UI
  • Full form-builder is a phase-two consideration
Configures
03

How it flows

Each persona has one clear path. The dashed bands are the handoffs, where one person's action becomes another's trigger.

IM Cell

Runs the response
Register emergency
Name, grade, dates & description
SCREEN · Register
→
Invite team leads
Search & multi-select recipients
SCREEN · Invite
→
Track responses
Who's in, who's late, by week
SCREEN · Tracker
→
Analyse & export
Charts + download for analysts
SCREEN · Analytics
HANDOFF ↓Invitations issued: each invited team lead receives an emailed link to the initial form for this emergency.

Team Lead

Reports for a team
Open link / sign in
Entra SSO · name pre-filled
SCREEN · Home
→
See what's due
Clear action vs up-to-date
SCREEN · Emergency
→
Fill in form
~5 questions · desktop or mobile
SCREEN · Form
→
Submit + receipt
Validated · emailed confirmation
SCREEN · Success
HANDOFF ↓Submission stored: written to governed Delta tables against the right emergency & team; visible to the IM Cell tracker and available to analysts within minutes.

Senior Leadership

Consumes the picture
Sign in
Entra SSO · read-only
SCREEN · Login
→
Executive dashboard
Cross-emergency situation view
SCREEN · Dashboard
→
AI insight layer
Auto-summaries & Power BI · phase 2
FUTURE
Journey start IM Cell path Team Lead path Leadership path Cross-persona handoff
04

The screens

These are the actual app screens from the working prototype, the same UI you'll click through in the walkthrough. Desktop is the priority; team leads also get a mobile view for the field.

A

IM Cell: response tracker

for the IM Cell
brc-cop.azuredatabricks.net/apps/status-update/admin
COP Console
Overview
Emergencies
Settings
Storm Ashford GRADE 4
Live · Week 2 · 20 teams invited
ExportAnalytics
Storm Ashford · liveForm ▾ Recurring · week 2Directorate ▾ All
20
Invited
14
Submitted
4
Awaiting
2
Overdue
70%
Response
scroll right for later weeks →SubmittedAwaitingOverdueNot due
TeamDirectorateInitialWk 1Wk 2Wk 3Wk 4Last submitted
Emergency ResponseUK OperationsDoneDoneDone––18 Sep 09:12
LogisticsUK OperationsDoneDoneAwaiting––11 Sep 14:40
VolunteeringInternal ServicesDoneDoneOverdue––08 Sep 16:03
FundraisingMarketing & FundraisingDoneAwaiting–––04 Sep 11:20
International ProgrammesInternationalDoneDoneDone––18 Sep 08:47
Click a row → read that team's full submission history for this event.
B

Team Lead: status update form

for the Team Lead
brc-cop.azuredatabricks.net/apps/status-update  ·  Storm Ashford
RECURRING UPDATE · STORM ASHFORD

Week 2 status update

🔒 Submitting for Volunteering · Aisha Lawson, name, team & email from your sign-in
1. Impact on business as usual over the next month * 1 no impact · 5 critical
12345
2. Confidence in this assessment *
12345
Very lowVery high
3. Current capacity to respond *
12345
HealthyCritically overstretched
4. Concerns, risks or pressures *
Two volunteers redeployed to reception centres; struggling to cover weekend shifts.
5. Compared to last update, the situation is *
ImprovingStableWorsening
✓ Submit updateCancel
C

Team Lead: mobile home

for the Team Lead, in the field
status-update · mobile
AL
Aisha Lawson
Volunteering · UK Ops
1
to do
Needs your attention
Storm Ashford
G4Overdue
Your Week 2 update was due 2 days ago.
✎ Fill in now
Recent submissions
Week 1 · Storm Ashford
Recurring update
08 Sep
Initial · Storm Ashford
Anticipated impact
04 Sep
D

Senior Leadership: executive picture

proposed future phase
brc-cop.azuredatabricks.net/apps/status-update/leadership
COP · Executive PictureAll active emergencies ▾
37
Teams reporting
3.4
Avg impact / 5
6
Critically stretched
Worsening
Trend

Executive summary

✦ AI agent · auto-generated

Across 2 active emergencies, organisational pressure is rising. 37 teams reporting (73%), average impact 3.4/5 and climbing. UK Operations is the primary concern: volunteer availability and reception-centre logistics repeatedly flagged, with 6 teams critically stretched. Suggested focus: mutual-aid for UK Operations this week.

Capacity pressure by directorate

UK Operations
4.1
Internal Services
3.3
International
3.0
Finance & Social Ent.
1.9

Key concerns raised by teams

✦ AI-summarised
Volunteer availability falling: weekend shift coverage flagged by 4 UK Ops teams.
Reception-centre logistics strained: mutual-aid requests from 2 regions.
Fundraising stable: no material BAU disruption, confidence high.
✦ Proposed future phaseThis AI-assisted dashboard isn't part of the current PoC, shown as a proposed follow-on.
05

Data model & the flexible schema

A small star schema on the Bronze → Silver → Gold medallion. Fixed audit & relationship columns stay stable and governable; the form answers live in a JSON object, the hard requirement you set, so a form can change without a table migration or breaking history.

● BronzeRaw capture: exactly as submitted, with audit metadataappend-only
bronze_cop_submissions RAW
PKsubmission_idstring
event_idstring
submitted_by_emailstring
form_name / versionstring
submitted_at_utctimestamp
responses_jsonstring · json
_ingested_attimestamp
● SilverValidated, typed & related: the governed star schemaUnity Catalog
dim_major_events DIM
PKevent_idstring
event_namestring
gradeint
statusactive/closed
opened_at / closed_attimestamp
dim_stakeholder DIM
PKstakeholder_idstring
full_namestring
emailstring
FKteam_idstring
dim_team DIM
PKteam_idstring
team_namestring
directoratestring
dim_form_definition DIM
PKform_idstring
form_typeinitial/recurring
versionstring
fields_jsonstring · json
fact_submission FACT
PKsubmission_idstring
FKevent_id→ dim_major_events
FKstakeholder_id→ dim_stakeholder
FKteam_id→ dim_team
FKform_id→ dim_form_definition
submitted_at_utctimestamp
responses_jsonflexible answers
fact_invitation FACT
PKinvitation_idstring
FKevent_idstring
FKstakeholder_idstring
form_typestring
issued_at / due_attimestamp
statussent/done
im_cell_user DIM
PKuser_idstring
emailstring
rolecoordinator/analyst/admin
● GoldServing layer: JSON unpacked into columns for reportingSQL · Genie · Power BI
vw_cop_submissions VIEW
event_name, team, directorate
impact_1_2h … impact_1mint
current_capacityint
situation_trendstring
+ custom fieldsfrom json
agg_response_rates AGG
event_id, week
invited / submitted / overdueint
response_ratedouble
agent_summaries ✦ FUTURE
event_id, week
themes[]AI · array
exec_narrativeAI · string
suggested_actions[]AI · array

Relationships (crow's-foot: 1 –< ∞)

dim_major_events1 –< ∞fact_submissionone emergency has many submissions
dim_stakeholder1 –< ∞fact_submissiona team lead submits many updates over an event
dim_team1 –< ∞dim_stakeholdera team has one or more people who can submit for it
dim_form_definition1 –< ∞fact_submissiona form version shapes many submissions
dim_major_events1 –< ∞fact_invitationwho was asked to respond, and whether they did

The trade-off, stated plainly: question-level answers, including event-specific custom fields, live in responses_json, so a form change never triggers a migration. The Gold view is where they're unpacked into named columns for Power BI & Genie. That's the price of flexibility you told us was non-negotiable.

06

How it's built: the Databricks stack

Everything on Databricks, deployed as code from BRC's Azure DevOps. Identity wraps the top, governance runs through the middle, DevOps ships it: capture → store → serve as one governed pipeline.

Identity & Accesswho can do what
Microsoft Entra ID
Workspace SSO for all three personas, no separate logins
SSO
Unity Catalog RBAC
Role-based access to catalogs, schemas & tables
authZ
Capture → Store → Servethe data path
Databricks App
Streamlit UI · serverless · validates & writes on submit
Streamlit
→
Delta Lake · medallion
Bronze
raw + audit
Silver
typed star schema
Gold
unpacked views
→
Serving
Databricks SQL · Genie Space · Power BI · CSV/Excel export
SQL Warehouse
Governancespans every layer
Unity Catalog
Central governance across storage & serving: audit logging, data lineage, schema enforcement, one consistent source of truth
lineage · audit · RBAC
Deploy as coderepo → environments
Azure DevOps
Source repo & CI/CD pipelines, lives in BRC's tenant
ADO
→
Asset Bundles
App, tables, jobs & permissions defined declaratively
DABs
→
dev → staging → prod
Promoted through environments to production
3 envs
✦ AI insight layerproposed follow-on
LLM agent
Reads each submission's free text; summarises themes, drafts narrative, flags risks
Model Serving
→
agent_summaries
Auto-generated themes, exec summary & actions, written back to Gold
Delta · Gold
→
Leadership dashboard
Automated executive picture, no manual analyst effort
Power BI / App

The ✦ AI insight layer is out of scope for this PoC. The current build delivers the governed data (Bronze → Silver → Gold) and export. This rail is the natural follow-on: an agent that reads every submission and produces the leadership picture automatically, today's manual analyst step, automated. Shown as a proposed separately-scoped engagement.

SCALE
100+ registered users, low volume, tens to low-hundreds of rows per event. Serverless, event-driven.
LATENCY
Submissions analysis-ready within 5 min (NFR-001), captured straight to governed tables.
HANDOVER
Repo, deployment runbook, docs, a Genie Space over the data & team training.
COMPUTE
Databricks App (serverless) + a small SQL warehouse, no heavy processing at this scope.
07

What's in, what's not

Drawn straight from the signed scope. The right-hand column isn't rejected, it's parked as phase two so we protect the 30 Nov date.

✓ In scope: this PoC
  • → Initial & Recurring status update forms with required-field validation
  • → Structured capture to governed Delta tables with audit fields
  • → Start / stop & concurrent major events, kept separate
  • → IM Cell console: distribution & who's-responded tracking
  • → Admin role to view & download all submissions (CSV / Excel)
  • → A basic reporting example + a Genie Space over the data
  • → Deploy from repo, docs, handover & team training
◔ Phase two: separately scoped
  • · In-app form builder / self-service question config
  • · Notifications & automatic chase reminders
  • · End-user viewing of their own past submissions in-app
  • · The senior-leadership dashboard itself, and an AI agent that auto-summarises submissions into themes, narrative & actions (a strong follow-on)
  • · Rollout beyond the proof of concept

Two things we deliberately kept lightweight for now: question changes happen via a repo config change (the JSON schema absorbs them without breaking data), and form sending stays manual by email, both areas you were relaxed about, and both are natural first candidates for phase two.

✦ Where this goes next · follow-on opportunity

An AI-assisted COP insight layer

Once the governed data is flowing, an AI agent could read every submission the moment it lands, summarising free-text into themes, drafting the executive narrative, and flagging emerging risks and suggested actions automatically. It turns this PoC's clean data into a live leadership decision-support tool with no manual analyst effort. Today that qualitative analysis is done by hand; this would automate it end to end. Out of scope for this build, we'd scope it as a separate follow-on engagement, and screen D above shows exactly what it could look like.

08

Timeline & what we need from you

The funding window fixes the shape: build through autumn, live by 30 November, in time to be a candidate for the November resilience exercise.

AUG 2026 · done

Scoping & discovery

Requirements gathered, this playback, COP field schema for sign-off, workspace & access set-up.

SEP 2026

Build, test & deploy

Harden the app, add the event model & IM Cell console, deploy app + tables from the repo.

OCT 2026

Handover & evaluation

Repo, runbook, docs, Genie Space & training. BRC evaluates against the Forms/SharePoint baseline.

30 NOV 2026

Live & resilience-exercise ready

Production-deployed; the pattern gets a real operational test if the October evaluation succeeds.

To keep us on the critical path

  • Access: guest accounts in BRC's Azure DevOps & Databricks (pending contract finalisation)
  • Environment: confirm the target workspace & Unity Catalog exist, or trigger provisioning
  • Questions: Harry & Alexei's final sign-off on the Initial & Recurring question sets
  • Data classification: agree sensitivity of names, emails & team-pressure data before we build that part
  • Channel: a shared Teams/Slack space for fast feedback loops