Dynamic Sheet to Biomarker Linking

Status: Plan (2026-06-02). Goal: every dynamic sheet links to one central biomarker basket — across sheets (a vital at time T in any sheet appears in all others at T) and within a sheet (the same LOINC code in multiple rows/groups shares one value at T). Companion to dynamic-sheet-platform-reconciliation.md and observation-bridge.ts.

The basket already exists — we wire to it, not rebuild it

Layer	What	Where
Central basket	observations table — patient + LOINC code + effective_at + category (vital-signs/biomarker/device), reference ranges, interpretation. Realtime publication on. Frontend reads only (RLS); writes go Mongo → orchestrator → project_observation().	infrastructure/medbase/migrations/20260512_observation_unification.sql
Read hook	useObservations({patientId, encounterId, categories, codes, realtime}) → live, sorted, filtered observations.	web/src/services/observation-core/useObservations.ts
Write-through	writeThroughVitals() / writeThroughBiomarkers() → recordObservation() → backend event → orchestrator → observations.	web/src/utils/observation-write-through.ts
LOINC registry	resolveLoincForDisplay(label) → {code, unit}.	web/src/services/observation-core/codes.ts
Proven pattern	Old graphic sheet already links: useObservations + buildHydrationMap + getHydratedCellValue overlay.	web/src/contexts/ui-contexts/DynamicGraphicSheetProvider.tsx:1157-1179
V2 bridge (built, UNWIRED)	mirrorSheetVitals (write) + buildHydrationMap (read) — zero callers in V2.	packages/miniapps/dynamic-sheet-labour-room-v2/observation-bridge.ts

The canonical channel — useObservationSync (Observation Sync) ✅ FACADE BUILT

web/src/services/observation-sync/ — the ONE hook every surface uses to read + write a vital / biomarker / score, linked by (patient, LOINC code, effective_at). A thin, offline-first facade over the pieces above (useObservations + writeThrough* + indexedDBService + useOffline

• resolveLoincForDisplay). Each surface passes only its source tag:

const obs = useObservationSync({ patientId, encounterId, source: 'graphic-sheet' });
obs.values;                              // same-time linked observations (realtime)
obs.write({ code, value, effectiveAt }); // online → basket; offline → IndexedDB outbox → replay
obs.isOnline; obs.unsyncedCount; obs.syncNow();

“Observation Sync” because it covers everything that is an Observation — vitals, biomarkers, NEWS/score totals, assessment values. Status: facade shipped (esbuild-verified, additive — nothing imports it yet, zero risk). The remaining work is wiring it into each surface, starting with the V2 engine.

The only gap: the V2 engine (renders all DS_SCORE_* / DS_IPD_* preset sheets) isn’t plugged into the basket. Wiring it via useObservationSync = “all dynamic sheets link with each other and with themselves,” offline-tolerant.

How linking works (already supported by the core)

buildHydrationMap maps one LOINC code → every (groupId, rowId) that resolves to it and fans a single observation out to all of them (observation-bridge-core.ts:200-235). So:

• Inter-sheet: HR typed on the ICU sheet → observations → hydrates the HR row on the NEWS sheet, the graphic sheet, etc. at the same time bucket.
• Intra-sheet: the NEWS preset’s HR appears in both the scored group and the raw-numeric group → one observation hydrates both.

Keyed by (patient, code, effective_at-bucket) — “same time = linked” is the time-column bucket.

Wiring points (V2)

Data flow: PresetRendererV2(patientId,encounterId) → DynamicSheetMaster → RowDataProvider → MainDataGrid(patientId, encounterId, tabColumns, treeData) → BuildFlowSheet(renderCell).

P1 — Read-hydration (inbound, SAFE, do first)

1. In MainDataGrid (has patientId/encounterId/tabColumns + interval via DynamicSheetSettingsContext):

   const { data: observations } = useObservations({
     patientId, encounterId, categories: ['vital-signs','biomarker'], realtime: true,
   });
   const hydrationMap = useMemo(() => buildHydrationMap({
     groups: templateGroups,          // toolsOrGroups (id + ui_metadata + ontology)
     columns: tabColumns, intervalMinutes, observations: observations ?? [],
   }), [templateGroups, tabColumns, intervalMinutes, observations]);
   

2. Thread getHydratedCellValue(groupId, rowId, field) → BuildFlowSheet.renderCell empty-cell branch: when a numeric INPUT cell is empty, render the hydrated value display-only (greyed, “from ”), never merged into the save store (sheet entry always wins). Mirror getHydratedCellValue in DynamicGraphicSheetProvider.

• Risk: low. Additive, display-only, no save-path change. Falls back to today’s behaviour when no observations / no patient.

P1 prerequisites (confirmed in source — both additive)

1. Plumb patient/encounter into the per-sheet scope. RowDataContext value (contexts/RowDataProvider.tsx:167) exposes only rowsDataMaster/setters/rescheduleColumn — NOT patientId/encounterId, even though RowDataProvider receives them as props. Add both to the context value so SingleSheetItem (MainDataGrid.tsx:200) can read them for useObservations.
2. Source the ontology for LOINC resolution. In SingleSheetItem, the sheet is item (item.definition.ui_metadata, treeData = generateTreePath(item)). The bridge’s displayForCode needs group.ontology.term_definitions.en.terms[code].text — ui_metadata.label is unit-suffixed and won’t match resolveLoincForDisplay. Pass a group shaped as { id: item.id, ui_metadata: item.definition.ui_metadata, ontology: item.definition.ontology ?? item.ontology } to buildHydrationMap. If ontology is absent on item, thread it from the preset’s toolsOrGroups (it carries ontology) via DynamicSheetMaster → MainDataGrid items. If unresolved, hydration is a safe no-op (nothing renders) — never a break.

P2 — Write-through (outbound)

1. In the save path (processRowUpdate / onBatchSave in MainDataGrid), AFTER the existing dynamic_sheet_usages upsert, call mirrorSheetVitals({ group, updatedRow, priorRow, patientRef, encounterRef }) (best-effort, never throws).
2. This routes changed NUMERIC input cells → writeThroughVitals → orchestrator → observations → realtime → every other sheet’s hydration updates.

• Risk: medium. Touches the save path. Best-effort + after existing save, so it can’t block saving.
• Note: full round-trip needs the backend (Mongo→orchestrator→observations). Visible only in a real/deployed env, not frontend-only sandbox.

P3 — LOINC coverage

• Only NUMERIC rows that resolve via resolveLoincForDisplay link. Audit the score-sheet numeric rows (e.g. NEWS_Manual gt_hr/gt_rr/gt_sbp/gt_temp/gt_spo2, ICU biomarkers pH/PaO2/PaCO2/K⁺/glucose) and extend codes.ts (BIOMARKER_DISPLAY_TO_LOINC) for any unmapped ones.
• Scored select bands do NOT link (not numeric, no LOINC) — they stay manual. Auto-deriving a band from a raw value (e.g. raw HR → NEWS HR-band score) is a separate range-lookup feature, out of scope here.

Offline-first / sync-backup layer (HARD REQUIREMENT)

Principle: the sheet is local-first. Data entry, save, score calc, and render must work with no internet. Supabase (dynamic_sheet_usages) and the observations basket are SYNC/BACKUP targets — written best-effort when online, queued + replayed when offline. Connectivity loss must never block the graphic sheet.

Existing infra to REUSE (proven, ~70% built — do NOT reinvent):

Asset	File	Use
IndexedDB (Dexie) MedOSOfflineDB + syncQueue + cache(TTL)	src/services/offline/indexeddb.service.ts	Local draft store + outbox
Offline middleware (network detect, queue, 30s auto-replay)	src/services/offline/offline-middleware.service.ts	Replay engine
useOffline() (isOnline, unsyncedCount, sync()) + useOnlineStatus()	src/hooks/useOffline.ts, useOnlineStatus.ts	UI status + manual sync
Service worker + Workbox (background-sync hooks stubbed)	src/service-worker.ts (sync-vitals TODO)	Background replay

Current gap (confirmed): RowDataProvider.tsx:142 discards on upsert error — no local save, no queue. That’s the one place that makes the sheet internet-dependent.

Design:

1. Sheet save → local-first. ✅ SHIPPED. On every rowsDataMaster change: write the draft to IndexedDB (cache key dsu:{template}:{patient}:{encounter} — always succeeds) then best-effort Supabase upsert; on the online event re-push the draft. (The sheet save is a Supabase-CLIENT upsert, not REST, so it uses cache + reconnect-resync — NOT the REST sync-queue, which the observation writes in §P2 use.) Never discard.
2. Mount → local fallback. Fetch from Supabase if online; on failure use the IndexedDB draft, so a reload offline restores the sheet.
3. Linking write-through = the sync layer. mirrorSheetVitals (P2): online → write-through; offline → addToSyncQueue({entity:'observations'}). Replays on reconnect. Already non-blocking.
4. Read-hydration offline. useObservations realtime when online; offline → last-cached observations (PWA NetworkFirst cache / IndexedDB cache). Overlay is display-only either way.
5. Replay. Wire the SW sync-vitals stub + offline-middleware auto-sync to drain syncQueue (dynamic_sheet_usages + observations) when navigator.onLine returns.
6. UI. Surface useOffline().unsyncedCount + a “Sync now” affordance in the sheet header (OfflineBanner already exists app-wide).

Verification: DevTools → Offline → enter vitals → reload (data restored from IndexedDB) → back Online → queue drains to Supabase + observations → other sheets hydrate. No data loss at any step.

Invariants

1. Frontend never writes observations directly (RLS) — always via write-through → orchestrator.
2. Hydration is display-only; never merged into the save store; sheet-entered values win.
3. Best-effort + additive: a bridge failure never blocks the sheet’s existing save/render.
4. Bundled-JSON / no-patient / no-observations → exact current behaviour (zero regression).
5. Only numeric LOINC-resolved rows link; computed/score/select rows are skipped.
6. Local-first: the sheet never depends on connectivity — entry/save/calc/render work offline; Supabase + observations are sync/backup targets, written best-effort and queued when offline.
7. No data loss: an offline save is persisted to IndexedDB + queued, never discarded, and replayed on reconnect.

Verification

• observation-bridge-core.ts already has unit tests (__tests__/observation-bridge-core.test.ts).
• esbuild transpile-check each touched V2 file.
• Read-hydration: seed an observations row for a sandbox patient at a column time → it appears in the matching empty cell.
• Write-through: real/deployed env only (needs backend round-trip).

Increments

• O0 · offline persistence (sheet local-first) ✅ SHIPPED → RowDataProvider writes an IndexedDB draft on every save, falls back to it on fetch error/empty (offline reload survives), and re-pushes on the online event. Reuses indexedDBService; one file, additive + fail-soft.
• P1 · read-hydration → same-time observations overlay (offline-tolerant via cache).
• P2 · write-through (the sync layer) → sheets feed the basket; queue offline, replay online.
• P3 · LOINC coverage → ensure all numeric biomarker rows resolve.
• O1 · sync-replay wiring → drain syncQueue (SW sync-vitals stub + offline-middleware) on reconnect; surface unsyncedCount + “Sync now”.