medOS ultra

Procedure Day-Queue

Procedure day-queue + timestamp lifecycle + gated excellence tabs, Supabase-primary architecture.

14 min read diagramsUpdated 2026-05-14docs/architecture/procedure-day-queue.md

Status: design — foundation migrations landed (078/079); architecture pivoted to Supabase-primary. Write RPC (080), frontend hooks, projector, gating engine, custom-feature builder still to ship.

Slots into the 5-PR roadmap for the procedure-worklist project. See the main extensibility memo for context on procedure_workflow_config (the per-department feature toggle table, migrations 075–077).

Architecture pivot — Supabase as the process-driven primary

The original draft of this doc had MongoDB as the canonical write source and Supabase as a read-model projection. That split inverted on 2026-05-14. New model:

  • Supabase = process-driven primary. Every micro-event during a procedure (Sign-In checkbox tick, Time-Out item completion, procedure_start, procedure_end, signature stroke, etc.) writes to Supabase via the record_procedure_event RPC. Postgres NOTIFY pushes the change to every subscribed client in <100 ms. Built for realtime tab-switching UX.
  • MongoDB = grouped final canonical log. On terminal events (discharged / cancelled / transferred_*), a consolidation step rolls up the Supabase event stream into ONE procedureRequest document with embedded events[] array. Mongo holds the legal medical-record artifact; queries for audit / billing / reporting hit this consolidated form.

Why invert:

  • Writing 22+ Mongo docs per procedure (one per event) is expensive — many small docs, frequent updates on the same parent.
  • Supabase realtime is built for the live-collaboration UX (two nurses on the same row, ticking different parts of Sign-In simultaneously).
  • The “complete medical record” is naturally a snapshot — useful as one doc with embedded events, not 22+ joined collections.
  • Replay is cheap: Supabase IS the live record; Mongo is the eventual snapshot.

Cost: We give up Mongo as a sync-of-record during the operation. Mid-procedure crash on the Supabase side would require replaying events from procedure_event_log — solved by hourly Mongo backfills as a safety net.

What this enables

  1. Canonical timestamp lifecycle — every transition (Sign-In completed, Time-Out completed, procedure start, procedure end, Sign-Out, Room out, Recovery in/out, etc.) is captured as an event with at, by, optional payload. The two business-critical pegs are procedure_start_at and procedure_end_at (your “start operation time” and “end operation time”), with operative-time (OT) derived from Δ.
  2. Realtime per-day per-department queue board — procedure_day_queue Supabase table, one row per active procedure for today, projected from MongoDB’s procedureRequestEvent collection. Frontend subscribes via Supabase realtime channel; two nurses on different machines see each other’s Sign-In ticks in real time.
  3. Tab-switching as a realtime read — opening the Time-Out tab on a row just mounts a different panel against the same realtime row stream; no fresh fetch needed.
  4. Excellence-process tabs that gate downstream actions — a department can author a custom checklist (e.g. “Pre-Anesthesia Excellence Audit”) that, when not completed, blocks specific actions (e.g. fire:procedure_start, open:sign_in, even cross-worklist cashier:collect_payment). Piggybacks on the existing policy_gates table (per policy-gates.md).

Storage split (post-pivot)

┌──────────────────────────────────────────────────────────────────────────┐
│  LIVE PATH (Supabase — process-driven primary)                           │
├──────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│  UI button: "เริ่มทำหัตถการ" / Mark procedure start                       │
│      │                                                                   │
│      ▼ supabase.rpc('record_procedure_event', {                          │
│          procedure_request_id, event_type, at, by_user_id, payload,      │
│          tx_id })                                                        │
│      │                                                                   │
│      ▼ Postgres function record_procedure_event(...)                     │
│        - validates tx_id idempotency (unique constraint on tx_id)        │
│        - validates event_type + current status against FSM               │
│        - evaluates gates (procedure_workflow_config.gate + policy_gates) │
│        - if gated → RAISE EXCEPTION → RPC returns 409-equivalent         │
│      │                                                                   │
│      ▼ INSERT INTO procedure_event_log (one row per event)               │
│      │                                                                   │
│      ▼ UPSERT  procedure_day_queue (one row per active procedure-today)  │
│        - sets the right timestamp peg based on event_type                │
│        - recomputes ot_minutes when start + end both present             │
│        - recomputes blocked_actions[] from feature gates                 │
│      │                                                                   │
│      ▼ Postgres NOTIFY (supabase_realtime publication)                   │
│      │                                                                   │
│      ▼ Frontend hooks receive postgres_changes payload in <100ms         │
│        useProcedureDayQueue({ deptId, day })   — board view              │
│        useProcedureDayQueueRow({ id })         — row-detail tabs         │
│        useProcedureEventTimeline({ id })       — audit dialog            │
│      │                                                                   │
│      ▼ UI re-renders without polling — Sign-In tick on Nurse A's screen  │
│        appears on Nurse B's screen 50–100ms later                        │
└──────────────────────────────────────────────────────────────────────────┘
                                  │
                                  ▼ (terminal events only)
┌──────────────────────────────────────────────────────────────────────────┐
│  CONSOLIDATION PATH (MongoDB — grouped final canonical log)              │
├──────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│  Trigger: terminal event recorded                                        │
│    (discharged | transferred_to_ward | transferred_to_icu | cancelled)   │
│      │                                                                   │
│      ▼ Postgres trigger inserts row into procedure_record_final          │
│        with `sync_state = 'pending'`                                     │
│      │                                                                   │
│      ▼ Mongo sync worker (PR 5+; Deno edge function on cron OR Moleculer │
│        worker subscribed to hospital_events)                             │
│        - reads procedure_record_final WHERE sync_state='pending'         │
│        - reads the full event log for procedure_request_id from          │
│          procedure_event_log                                             │
│        - POSTs to Moleculer procedure-request.consolidateRecord with:    │
│          { procedureRequestId, finalSnapshot, events[] }                 │
│      │                                                                   │
│      ▼ MongoDB: procedureRequest doc updated with embedded events[]      │
│        + canonical timestamps map + final status + completion meta       │
│      │                                                                   │
│      ▼ procedure_record_final.sync_state = 'synced'                      │
│      │                                                                   │
│      ▼ Hourly safety-net sweep: procedure_record_final WHERE              │
│        sync_state='pending' AND completed_at < now() - 1 hour            │
│        → re-attempts the consolidate                                     │
└──────────────────────────────────────────────────────────────────────────┘

Writes inverted from the original draft. The frontend now writes events to Supabase via RPC (not to a Mongo REST endpoint). Mongo is updated only when the procedure terminates, as one consolidated doc with all events embedded.

Frontend still NEVER writes to read-model tables directly. All writes go through the record_procedure_event RPC — that’s the only callable surface. The RPC inserts into procedure_event_log and upserts procedure_day_queue under server-side control (FSM + gates).

Timestamp catalog

Built-in event vocabulary. Custom features fire custom_feature_completed with payload.featureId.

Phase Event id Sets queue field Status transition Notes
Pre-procedure requested requested_at → pending already exists in current worklist
accepted accepted_at → accepted already exists
arrived arrived_at – flag only
sign_in_started sign_in_started_at – flag
sign_in_completed sign_in_completed_at – flag; releases gates on dependent actions
room_in room_in_at – flag
time_out_started time_out_started_at – flag
time_out_completed time_out_completed_at – flag; releases gate on procedure_start
Intra-procedure procedure_start procedure_start_at sub-status: operating start operation time
procedure_pause last_pause_at – rare
procedure_resume last_resume_at – rare
procedure_end procedure_end_at – end operation time; recomputes ot_minutes
sign_out_started sign_out_started_at – flag
sign_out_completed sign_out_completed_at – flag; required for discharged
Post-procedure room_out room_out_at – flag
recovery_in recovery_in_at – flag
recovery_out recovery_out_at – flag
discharged discharged_at → completed terminal
transferred_to_ward discharged_at (alias) → completed terminal
transferred_to_icu discharged_at (alias) → completed terminal
cancelled cancelled_at → cancelled terminal
postponed postponed_at back to pending non-terminal
Custom custom_feature_completed row in event_log, updates feature_progress JSONB on day_queue – payload.featureId identifies which custom feature

Storage schemas

Supabase: procedure_event_log (migration 078, primary write target)

Flat event log. One row per recorded event. Cheap to query for an audit timeline. After the pivot, this is the live record during a procedure — not a projection. Mongo catches up at terminal events.

Supabase: procedure_day_queue (migration 079, primary state)

One row per active procedure for the calendar day. Live state — current_phase, all 16 timestamp pegs, feature_progress, blocked_actions. Cleaned nightly (rows with status in ('completed','cancelled') and discharged_at < now() - 7 days archived).

Supabase: procedure_record_final (migration 081, ships next iteration)

The Mongo-sync handoff. One row per completed/cancelled procedure with sync_state ∈ {pending, synced, failed}. Trigger on procedure_day_queue inserts a row when status becomes terminal.

procedure_request_id  TEXT  PRIMARY KEY
department_id         TEXT
final_status          TEXT          -- completed | cancelled
completed_at          TIMESTAMPTZ   -- when terminal event fired
events_count          INT           -- count of procedure_event_log rows for this request
sync_state            TEXT          -- pending | synced | failed
synced_at             TIMESTAMPTZ
mongo_object_id       TEXT          -- procedureRequest._id once synced
last_error            TEXT          -- last sync attempt failure message
created_at            TIMESTAMPTZ DEFAULT now()

MongoDB: procedureRequest (canonical final snapshot, updated via consolidate worker)

After consolidation, the canonical procedureRequest document looks like:

{
  _id: ObjectId,
  // ... existing fields (status, encounterRef, patientRef, items, etc.)
  // ── New fields added during consolidation ────────────────────────
  timestamps: {
    requestedAt: ISODate, acceptedAt: ISODate, arrivedAt: ISODate,
    signInCompletedAt: ISODate, roomInAt: ISODate,
    timeOutCompletedAt: ISODate,
    procedureStartAt: ISODate,                  // ⟵ "start operation time"
    procedureEndAt: ISODate,                    // ⟵ "end operation time"
    signOutCompletedAt: ISODate, roomOutAt: ISODate,
    recoveryInAt: ISODate, recoveryOutAt: ISODate,
    dischargedAt: ISODate, cancelledAt: ISODate,
  },
  otMinutes: number,
  pausedMinutes: number,
  featureProgress: { [featureId]: number },     // 0.0-1.0 per feature
  events: [                                     // full embedded log, ordered by at
    { eventType, at, byUserId, byDisplayName, payload, prevStatus, nextStatus, txId }
  ],
  consolidatedAt: ISODate,
  consolidatedFromSupabaseDayQueueAt: ISODate
}

The events[] array is the audit-of-record. For ongoing reporting/billing/audit, this is what Mongo queries hit. The Supabase tables can be wiped 30 days after consolidatedAt (kept for short-window operational/realtime use only).

Key columns:

procedure_request_id  TEXT  PRIMARY KEY
department_id         TEXT  NOT NULL
day                   DATE  NOT NULL
status                TEXT  NOT NULL              -- FSM state
current_phase         TEXT                        -- which dialog tab is "live"
patient_hn / patient_name / procedure_code / procedure_name
priority              TEXT                        -- routine / urgent / stat
-- Timestamp pegs (16 columns matching the catalog)
requested_at / accepted_at / arrived_at / sign_in_completed_at /
room_in_at / time_out_completed_at /
procedure_start_at / procedure_end_at /
sign_out_completed_at / room_out_at /
recovery_in_at / recovery_out_at /
discharged_at / cancelled_at / postponed_at
-- Derived
ot_minutes                INT       -- procedure_end_at - procedure_start_at
current_phase_started_at  TIMESTAMPTZ
feature_progress          JSONB     -- { sign_in: 1.0, time_out: 0.6, custom:skin_prep: 1.0 }
-- Gating
blocked_actions           TEXT[]    -- which ProcedureActions are currently gated
gate_reasons              JSONB     -- { 'fire:procedure_start': 'Complete Excellence Audit first' }
-- Bookkeeping
last_event_id             TEXT
last_event_at             TIMESTAMPTZ
updated_at                TIMESTAMPTZ DEFAULT now()

Indexes: (department_id, day, status), (day) WHERE status NOT IN ('completed','cancelled') (partial, for the “active today” hot query).

RLS: read all (UI gating), write public (anon-key — same as 076/077; the projector edge function uses the service role).

Feature gating model

Every procedure feature (built-in OR custom) can declare a gate block, stored in procedure_workflow_config.field_overrides.gate:

interface FeatureGate {
  /** When this feature is INCOMPLETE, block these ProcedureActions. */
  blocks?: ProcedureAction[];
  /** When this feature is completed, explicitly unlock these (mostly cosmetic — feature completion already releases its own blocks). */
  unlocks?: ProcedureAction[];
  /** Optional pointer to a row in `policy_gates` for complex / cross-worklist rules. */
  policyGateRef?: string;
  /** Soft vs hard gate. Soft shows a warning toast; hard refuses the action. */
  enforcement?: 'soft' | 'hard';   // default 'hard'
}

type ProcedureAction =
  // Open a feature's dialog
  | `open:${string}`               // 'open:sign_in' | 'open:time_out' | 'open:<customId>'
  // Fire a canonical event
  | `fire:${ProcedureEventType}`   // 'fire:procedure_start' | 'fire:cancel' | etc.
  // Cross-worklist gates routed via policy_gates
  | `policy:${string}`;            // 'policy:cashier_collect_payment'

Example — Dental Pre-Anesthesia Excellence Audit

A dental clinic admin authors a custom feature:

{
  "id": "dental_preanes_excellence",
  "labelTh": "ตรวจสอบมาตรฐานก่อนวางยาสลบ",
  "labelEn": "Pre-Anesthesia Excellence Audit",
  "group": "custom",
  "items": [
    { "kind": "checkbox", "labelTh": "ตรวจ NPO ≥ 6 ชม.", "labelEn": "NPO ≥ 6h verified" },
    { "kind": "checkbox", "labelTh": "ASA class ระบุแล้ว", "labelEn": "ASA class documented" },
    { "kind": "signature", "role": "anesthetist", "labelTh": "ลายเซ็นวิสัญญี" }
  ],
  "gate": {
    "blocks": ["fire:procedure_start", "open:time_out"],
    "enforcement": "hard"
  }
}

Effect on the worklist row:

  • The “เริ่มทำหัตถการ / Procedure Start” button is disabled with a tooltip “ต้องทำ ‘ตรวจสอบมาตรฐานก่อนวางยาสลบ’ ก่อน”.
  • The Time-Out button is disabled with the same reason.
  • Once all items + the anesthetist signature are completed, feature_progress.custom:dental_preanes_excellence = 1.0, the projector re-computes blocked_actions, and the realtime channel pushes the updated row → both buttons un-grey instantly.

Example — Cross-worklist gate via policy_gates

A clinic wants: “the cashier cannot collect payment for a dental procedure until the dental clinical-record audit is complete”. Author the audit as a custom feature with:

"gate": {
  "policyGateRef": "<uuid-of-policy-gates-row>",
  "blocks": ["policy:cashier_collect_payment"]
}

The policy_gates row encodes the cross-worklist condition (matched in the cashier worklist by the existing policy-gate engine). The custom feature’s completion progress is fed into the policy engine as a fact.

Realtime contracts

Channel Filter Use
procedure_day_queue:dept_id= postgres_changes on procedure_day_queue WHERE department_id=X AND day=today Queue board; ProcedureWorkListPage row table
procedure_day_queue_row:id= postgres_changes on procedure_day_queue WHERE procedure_request_id=Y Row-detail dialog; tab panels
procedure_event_log:request= postgres_changes on procedure_event_log WHERE procedure_request_id=Y Audit-timeline dialog

Frontend hooks (ship in PR 5):

useProcedureDayQueue({ deptId, day }): { rows, loading, error }
useProcedureDayQueueRow({ procedureRequestId }): { row, loading, error }
useProcedureEventTimeline({ procedureRequestId }): { events, loading, error }
useRecordProcedureEvent(): { record(eventType, payload) => Promise<{ok, blocked?}> }

Invariants

  1. procedure_day_queue is rebuildable from procedure_event_log — replaying the event log via the RPC produces the same final state.
  2. The record_procedure_event RPC is idempotent on tx_id — UNIQUE constraint dedupes; replaying returns the original event row.
  3. Monotonic timestamps: procedure_start_at and procedure_end_at never overwrite each other; the RPC rejects out-of-order events with at < procedure_start_at.
  4. ot_minutes is computed inside the RPC when both start + end are set, never by the frontend.
  5. Frontend never writes to procedure_event_log, procedure_day_queue, or procedure_record_final directly — only via supabase.rpc('record_procedure_event', ...). Direct table writes blocked by app-level convention (RLS allows them, but the service layer hides the bypass).
  6. Status FSM transitions are enforced inside record_procedure_event; invalid transitions raise an exception and the RPC returns { ok: false, error }.
  7. Day boundary: events at 23:59 belong to today’s day row; 00:00 of the next day to tomorrow’s. The RPC uses the procedure’s facility timezone (default Asia/Bangkok; configurable per deployment).
  8. Gates are evaluated inside record_procedure_event — the frontend’s disabled-button state is cosmetic and the RPC re-checks. If gated: { ok: false, blocked: { action, reason, blockingFeatureId } }.
  9. Mongo consolidation is one-shot per procedure — procedure_record_final.sync_state='synced' is terminal; once Mongo holds the snapshot, the Supabase rows are operational-only.
  10. Replay safety: Wiping procedure_day_queue and re-playing procedure_event_log through a RAISE-free version of the RPC reconstructs identical state, modulo timestamps from now() calls (the RPC uses event at, not now(), so this property holds).

Roadmap (post-pivot) — all shipped 2026-05-13/14

Iteration Scope Status
i1 Pivot the design doc to Supabase-primary ✅ 826919e3b
i2 Migration 080 — record_procedure_event(...) RPC: FSM, gate eval, idempotent tx_id ✅ b195e4b22
i3 Frontend service + useRecordProcedureEvent + useProcedureFeatureLifecycle ✅ c645e4cdc
i4 useProcedureDayQueue({ deptId, day }) realtime hook ✅ 7953ce0d9
i5 useProcedureDayQueueRow + useProcedureEventTimeline per-row hooks ✅ 6fc48d09f
i6 Wire ProcedureWorkListRowData buttons to fire real events via RPC ✅ ed90c48b2
i7 /procedure/day-queue per-dept realtime board page ✅ 872e66f53
i8 Migration 081 — procedure_record_final + terminal-event trigger ✅ d1d0b267f
i9 “+ Add custom feature” builder dialog with items list editor ✅ 3c64aecb0
i10 Custom-feature gate declaration UI (lifecycle + feature + freeform blocks) ✅ 3e33540c0
i11 Render custom features in admin grid with [custom] badge ✅ 73d5438d6
i12 Include custom features in worklist row button render ✅ 9ed4a26e5
i13–14 Stub dialog renders items[] form + Submit fires custom_feature_completed ✅ 7eedba855
i15 procedure-mongo-sync Deno edge function + worker doc + health view (mig 082) ✅ ea36d39bc
i16 SignInPanel — 9-item WHO Sign-In in @medical-kit/surgical-workflow/ ✅ d64edc201
i17 TimeOutPanel — 10-item WHO Time-Out grouped Team/Verify/Risks/Counts ✅ 91e92df98
i18 SignOutPanel — 7-item WHO Sign-Out + specimen list + handoff plan ✅ a63f16c4a
i19 RecoveryPanel — Aldrete/PAR (0-10) + vital signs + discharge readiness ✅ b79324172
i20 Summary doc + roadmap status bump ✅ (this commit)

See procedure-day-queue-summary-i1-i20.md for the full recap, demo path, and remaining backlog (canvas signatures, surgical-team roster, par_score split, periops or_history hoist decision, Moleculer consolidateRecord backend action).

Open questions / risks

  1. Day boundary timezone — confirmed Asia/Bangkok for TH; need to pick the right zone for JP, PH, etc. when those deploy. Suggest a facility_timezone column on the dept config.
  2. Procedure pause/resume — should ot_minutes deduct pause windows? (Probably yes; track last_pause_at/last_resume_at and accumulate.)
  3. Procedure cancellation mid-flight — does cancelled_at after procedure_start_at count as “completed unsuccessfully”? Need a clinical signoff on the FSM diagram.
  4. Gate evaluation perf — the recordEvent action evaluates gates by re-reading the procedure row + all features. At 100 events/min per facility this is fine; at 10k events/min we’d want a denormalised blocked_actions column on the Mongo doc updated as a sub-action.
  5. Edge function cold start — Supabase edge functions can cold-start at ~500ms. Acceptable for queue projection (eventual consistency tolerated for ~1s); not acceptable for gate evaluation (which is sync at the API layer).
Ask Anything