OR Section Sign-Offs
Supabase sync design for OR record section sign-offs.
11 min read diagramsUpdated 2026-05-23docs/architecture/or-section-signoffs-supabase-design.md
Status: Design — no migration deployed yet. Today’s behavior: Fully localStorage. Nothing touches Supabase. This doc is what the future implementer follows to add cross-device sync without disturbing the existing UI.
Architecture at a glance
┌──────────────────────────────────────────────────────────┐
│ Browser tab (tablet / nurse desktop) │
│ │
│ SectionCard chevron │
│ │ │
│ ▼ │
│ writeMarkSynced(key, record) │
│ │ │
│ ├──▶ writeMark(localStorage) ─── INSTANT, offline OK│
│ │ │
│ ├──▶ adapter.upsert(...) ───── fire-and-forget │
│ │ │ │
│ │ └─── on failure → outbox (localStorage) │
│ │ │
│ └──▶ MARK_CHANGE_EVENT ──▶ all UI subscribers │
│ │
│ window.online event ──▶ flushPending(outbox) │
└──────────────────────────────────────────────────────────┘
│
│ POST upsert / DELETE
▼
┌──────────────────────────────────────────────────────────┐
│ Supabase (Postgres + Realtime + RLS) │
│ │
│ or_section_signoffs ← primary state │
│ or_section_signoffs_log ← append-only audit │
│ or_section_prefs ← per-user prefs │
│ or_case_finalizations ← case snapshots │
│ │
│ trg_log_signoff trigger ← writes log row on every │
│ upsert/delete │
│ Realtime publication on ← fan-out to other devices │
│ or_section_signoffs ◀─ via supabase.channel() │
└──────────────────────────────────────────────────────────┘
│
│ realtime payloads
▼
┌──────────────────────────────────────────────────────────┐
│ Other browser tabs (same nurse / different nurse) │
│ │
│ subscribeCaseMarks(orId) handler │
│ │ │
│ └──▶ writeMark(localStorage) │
│ │ │
│ └──▶ MARK_CHANGE_EVENT → UI updates │
└──────────────────────────────────────────────────────────┘
Design principles
- localStorage stays the source of truth for the open UI. Every read goes to localStorage. Supabase is a fan-out + durable backup. UI never blocks on the network.
- Writes are write-through. Local write first (synchronous), then async backend write. Failures queue silently.
- Last-write-wins by client timestamp. No vector clocks, no CRDTs. The mark’s
atISO from the client is the conflict key. - Append-only audit log. Surgical sign-offs are medico-legal; the audit table never DELETEs. Soft-delete via “unmark” rows.
- Tenant-scoped RLS. A nurse only sees marks for their own tenant. Every table includes
tenant_idderived from JWT. - No server-side queue. The frontend outbox handles offline. Supabase is stateless w.r.t. retry — if a write succeeds it succeeds; if it fails the client retries.
Schema
or_section_signoffs — primary state (current marks)
One row per active mark. Deleted when the nurse unmarks.
create table public.or_section_signoffs (
or_request_id text not null, -- MongoDB ObjectId as string
stage text not null check (stage in ('waiting','signin','timeout','signout')),
section text not null, -- slug matching STAGE_SECTIONS registry
marked_by_user_id uuid, -- supabase auth user id (nullable for legacy seed)
marked_by_name text not null, -- denormalized display name
marked_at timestamptz not null, -- client-supplied; LWW conflict key
tenant_id uuid not null,
updated_at timestamptz not null default now(),
primary key (or_request_id, stage, section, tenant_id)
);
create index or_section_signoffs_by_case
on public.or_section_signoffs (tenant_id, or_request_id);
or_section_signoffs_log — append-only audit
Every mark + unmark writes a row. Never updated, never deleted. Powers historical replay, compliance reports, “who undid this attestation” investigations.
create table public.or_section_signoffs_log (
id uuid primary key default gen_random_uuid(),
or_request_id text not null,
stage text not null,
section text not null,
action text not null check (action in ('mark','unmark')),
actor_user_id uuid,
actor_name text not null,
performed_at timestamptz not null default now(),
client_at timestamptz, -- mark's `at` from the client
tenant_id uuid not null
);
create index or_section_signoffs_log_by_case
on public.or_section_signoffs_log (tenant_id, or_request_id, performed_at desc);
or_section_prefs — per-user toggle prefs
Mirrors the localStorage or-section-prefs:enabled:* keys. Lets a nurse’s prefs follow them across devices.
create table public.or_section_prefs (
user_id uuid not null,
stage text not null check (stage in ('waiting','signin','timeout','signout')),
section text not null,
enabled boolean not null default true,
updated_at timestamptz not null default now(),
primary key (user_id, stage, section)
);
or_case_finalizations — case snapshots
Mirrors the localStorage or-case-final:${orId} keys. Captures the timing + sign-off coverage at the moment a case reaches terminal status.
create table public.or_case_finalizations (
or_request_id text not null,
tenant_id uuid not null,
final_status text not null,
finalized_at timestamptz not null,
finalized_by_user_id uuid,
finalized_by_name text,
begin_surgery_date timestamptz,
finish_time timestamptz,
final_duration_mins integer,
marks_summary jsonb not null, -- {waiting: {marked, total}, signin: {...}, ...}
recent_marks jsonb not null, -- up to 50 most recent marks at finalize
primary key (or_request_id, tenant_id)
);
Row Level Security
alter table public.or_section_signoffs enable row level security;
alter table public.or_section_signoffs_log enable row level security;
alter table public.or_section_prefs enable row level security;
alter table public.or_case_finalizations enable row level security;
-- Tenant isolation
create policy or_signoffs_tenant on public.or_section_signoffs
using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid)
with check (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);
create policy or_signoffs_log_tenant on public.or_section_signoffs_log
for select using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);
-- log is insert-only from server triggers; no client write policy
create policy or_case_finals_tenant on public.or_case_finalizations
using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid)
with check (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);
-- Prefs scoped per user
create policy or_prefs_own on public.or_section_prefs
using (user_id = auth.uid())
with check (user_id = auth.uid());
Audit trigger
create or replace function fn_log_or_signoff() returns trigger
language plpgsql
security definer
as $$
begin
if (tg_op = 'INSERT') then
insert into public.or_section_signoffs_log
(or_request_id, stage, section, action, actor_user_id, actor_name, client_at, tenant_id)
values
(new.or_request_id, new.stage, new.section, 'mark',
new.marked_by_user_id, new.marked_by_name, new.marked_at, new.tenant_id);
return new;
elsif (tg_op = 'UPDATE') then
-- Upsert that replaces an existing mark: log as mark with new attribution
insert into public.or_section_signoffs_log
(or_request_id, stage, section, action, actor_user_id, actor_name, client_at, tenant_id)
values
(new.or_request_id, new.stage, new.section, 'mark',
new.marked_by_user_id, new.marked_by_name, new.marked_at, new.tenant_id);
return new;
elsif (tg_op = 'DELETE') then
insert into public.or_section_signoffs_log
(or_request_id, stage, section, action, actor_user_id, actor_name, client_at, tenant_id)
values
(old.or_request_id, old.stage, old.section, 'unmark',
old.marked_by_user_id, old.marked_by_name, old.marked_at, old.tenant_id);
return old;
end if;
return null;
end;
$$;
create trigger trg_log_or_signoff
after insert or update or delete on public.or_section_signoffs
for each row execute function fn_log_or_signoff();
Realtime fan-out
alter publication supabase_realtime add table public.or_section_signoffs;
alter publication supabase_realtime add table public.or_case_finalizations;
-- (prefs not realtime — they only matter on next render of the prefs popover)
Client subscribes per case:
supabase
.channel(`or-signoffs:${orId}`)
.on('postgres_changes',
{ event: '*', schema: 'public', table: 'or_section_signoffs',
filter: `or_request_id=eq.${orId}` },
(payload) => { /* writeMark locally → triggers UI */ })
.subscribe();
Adapter implementation (the only new TypeScript)
The frontend wiring is one file — implements RemoteAdapter from or-section-marks-sync.ts:
// or-section-supabase-adapter.ts
import { createClient } from '@supabase/supabase-js';
import type { RemoteAdapter } from './or-section-marks-sync';
const supabase = createClient(VITE_SUPABASE_URL, VITE_SUPABASE_ANON_KEY);
export function buildSupabaseAdapter(getTenantId: () => string): RemoteAdapter {
return {
async upsert({ orId, stage, section, record }) {
const { error } = await supabase.from('or_section_signoffs').upsert({
or_request_id: orId,
stage,
section,
marked_by_name: record.by,
marked_at: record.at,
tenant_id: getTenantId(),
});
if (error) throw error;
},
async remove({ orId, stage, section }) {
const { error } = await supabase
.from('or_section_signoffs')
.delete()
.match({ or_request_id: orId, stage, section, tenant_id: getTenantId() });
if (error) throw error;
},
async hydrate({ orId }) {
const { data, error } = await supabase
.from('or_section_signoffs')
.select('stage,section,marked_by_name,marked_at')
.eq('or_request_id', orId)
.eq('tenant_id', getTenantId());
if (error) throw error;
return (data ?? []).map((r) => ({
stage: r.stage,
section: r.section,
record: { by: r.marked_by_name, at: r.marked_at },
}));
},
subscribe({ orId, onChange }) {
const channel = supabase
.channel(`or-signoffs:${orId}`)
.on('postgres_changes',
{ event: '*', schema: 'public', table: 'or_section_signoffs',
filter: `or_request_id=eq.${orId}` },
(payload) => {
const row: any = payload.new ?? payload.old;
onChange({
stage: row.stage,
section: row.section,
record: payload.eventType === 'DELETE'
? null
: { by: row.marked_by_name, at: row.marked_at },
});
})
.subscribe();
return () => { void supabase.removeChannel(channel); };
},
};
}
Bootstrap (single top-level call):
// somewhere in App.tsx after auth resolves
import { registerRemoteAdapter } from '@periops-kit/.../or-section-marks-sync';
import { buildSupabaseAdapter } from '@periops-kit/.../or-section-supabase-adapter';
useEffect(() => {
if (!user?.tenantId) return;
registerRemoteAdapter(buildSupabaseAdapter(() => user.tenantId));
return () => registerRemoteAdapter(null);
}, [user?.tenantId]);
Per-dialog hydration + subscription:
// in DialogSignIn.tsx
useEffect(() => {
if (!orRequest?._id) return;
void hydrateCaseMarks(orRequest._id);
return subscribeCaseMarks(orRequest._id);
}, [orRequest?._id]);
That’s the whole adapter integration. No SectionCard / StageBadge / TimeInStatusCell changes.
Sync flow walkthroughs
Mark write (online)
Nurse clicks chevron
→ writeMarkSynced(key, {by, at})
→ writeMark(key, {by, at}) ✓ localStorage
→ dispatch MARK_CHANGE_EVENT ✓ UI updates instantly
→ adapter.upsert({orId, stage, section, record}) ✓ Supabase row inserted
(audit trigger fires)
(realtime broadcasts)
→ other tabs receive realtime payload
→ writeMark(key, record)
→ dispatch MARK_CHANGE_EVENT ✓ all tabs in sync
Mark write (offline)
Nurse clicks chevron
→ writeMarkSynced(key, {by, at})
→ writeMark(key, {by, at}) ✓ localStorage
→ dispatch MARK_CHANGE_EVENT ✓ UI updates instantly
→ navigator.onLine === false → enqueuePending(...)
writes to localStorage outbox
→ dispatch OUTBOX_CHANGE_EVENT ✓ pill shows "Offline · 1"
→ ConnectivityPill renders "Offline · 1 queued"
→ nurse continues marking sections
→ each gets queued
→ WiFi returns → window 'online' event fires
→ flushPending()
→ adapter.upsert() for each queued entry, FIFO
→ on success: shift from outbox, dispatch OUTBOX_CHANGE_EVENT
→ on failure: stop, leave remaining for next attempt
→ pill flips to "Syncing 3…" then disappears
Dialog open
Nurse opens a case dialog
→ hydrateCaseMarks(orId)
→ adapter.hydrate({orId}) returns server marks
→ for each: compare to local; if remote.at > local.at, writeMark(remote)
→ subscribeCaseMarks(orId)
→ opens realtime channel
→ useStageSummary(orId) re-renders with merged marks
→ on close → unsubscribe
Conflict resolution
Two nurses on two devices, same case, same section, near-simultaneous marks:
Nurse A (device 1, 14:32:00.500) → upsert succeeds first → row.marked_at = 14:32:00.500
Nurse B (device 2, 14:32:00.700) → upsert succeeds second → row replaced; marked_at = 14:32:00.700
Realtime broadcasts: both tabs receive update for B's mark.
Both tabs writeMark(B's record) — last-write-wins.
Audit log: 2 rows ('mark' from A, 'mark' from B) — full history preserved.
Migration / rollout plan
Phase 1 — Migration (no code change)
Apply the 4 tables, RLS, trigger, and realtime publication. Frontend continues using localStorage only; tables stay empty.
psql "$SUPABASE_DB_URL" -f infrastructure/medbase/migrations/0NN_or_section_signoffs.sql
Phase 2 — Shadow writes
Register a “tee” adapter that writes to Supabase but doesn’t read from it. localStorage is still authoritative for reads. Lets you build up server-side state for QA without risk.
registerRemoteAdapter({
upsert: realAdapter.upsert,
remove: realAdapter.remove,
hydrate: async () => [], // empty — local still authoritative
subscribe: () => () => {}, // no-op
});
Phase 3 — Read-through
Switch hydrate to the real implementation. Server marks now overlay local on dialog open (LWW). Local stays primary for instant writes.
Phase 4 — Realtime fan-out
Switch subscribe to the real implementation. Now writes from device A appear on device B within ~1 sec.
Phase 5 — Per-user prefs sync (optional)
Build the parallel adapter for or_section_prefs. Same pattern: localStorage primary, Supabase fan-out. Most useful when a nurse logs in on a fresh tablet and wants their toggles to follow them.
What this design intentionally does NOT do
- No server-side outbox. Outbox is purely client-side. If the device is destroyed before flush, those marks are lost (acceptable — the case is still in progress and the next nurse can re-attest).
- No CRDT. LWW is simpler and clinically correct (most recent attestation wins).
- No optimistic locking. Two simultaneous marks for the same section just overwrite each other; the audit log preserves the history.
- No server-side validation of
marked_at. Clients can submit any timestamp. RLS prevents tenant-crossing; audit trail prevents tampering being silent. Defense-in-depth via theclient_atcolumn in the log. - No bulk hydrate-all-cases endpoint.
hydrateCaseMarksis per-case (called on dialog open). The main table renders from localStorage only; cross-device fan-out happens via realtime once a case dialog is open.
If you later want the main table itself to reflect cross-device marks before any dialog has been opened, add a single page-level subscription:
// in MainTab.tsx — only after rolling out phase 4
useEffect(() => {
const channel = supabase
.channel('or-signoffs:all')
.on('postgres_changes',
{ event: '*', schema: 'public', table: 'or_section_signoffs' },
(payload) => {
const row: any = payload.new ?? payload.old;
const key = `${row.or_request_id}:${row.stage}:${row.section}`;
if (payload.eventType === 'DELETE') writeMark(key, null);
else writeMark(key, { by: row.marked_by_name, at: row.marked_at });
})
.subscribe();
return () => { void supabase.removeChannel(channel); };
}, []);
That makes the WR/SI/TO/SO badges in the main table live across devices without any case being open.
Estimated scope
| Phase | LOC | Where |
|---|---|---|
| Migration SQL | ~120 | infrastructure/medbase/migrations/0NN_or_section_signoffs.sql |
Adapter (buildSupabaseAdapter) |
~80 | or-section-supabase-adapter.ts |
| Bootstrap call in App | ~10 | App.tsx |
| Dialog hydrate + subscribe wiring | ~5 | DialogSignIn.tsx |
| Page-level realtime (optional) | ~15 | MainTab.tsx |
| Total to fully sync | ~230 LOC |
Everything else — UI, badge math, offline outbox, finalization snapshots — is already shipped.