Encounter Orchestrator & Triggers

Read-model orchestration

flowchart TD
  EV[("hospital_events")] --> ORCH["encounter-orchestrator<br/>(Deno edge fn)"]
  ORCH --> JC[("encounter_journey_cache")]
  ORCH --> DQ[("department_queues")]
  JC --> UI["Worklists · Patient 360"]
  DQ --> UI

Audience: Engineers maintaining the medOS read-model layer. Anyone touching encounter_journey_cache, department_queues, hospital_events, or any read-model table.

1. The two-layer architecture in 30 seconds

┌─────────────────────────────────────────────────────────────┐
│  WRITE TRUTH (MongoDB)                                       │
│  Backend Moleculer services (clinical, financial, medication)│
│  ────────────────────────────────────────────────────────── │
│  • patients          • sale_orders     • lab_requests       │
│  • encounters        • prescriptions   • etc.               │
└────────────────┬────────────────────────────────────────────┘
                 │
                 │ Event emit (Moleculer NATS):
                 │   manifest.encounter.seeded
                 │   manifest.order.created
                 │   manifest.financial.updated
                 │   manifest.queue.workflow_transition
                 │   ... (~20 event kinds)
                 ▼
┌─────────────────────────────────────────────────────────────┐
│  EVENT LOG (Supabase: hospital_events)                       │
│  Append-only INSERT — primary record of every change.        │
└────────────────┬────────────────────────────────────────────┘
                 │
                 │ Trigger: on_hospital_event_insert  ── audit / dead-letter
                 │ Trigger: on_hospital_event_cds     ── clinical decision support
                 │ Trigger: trigger_orchestrator      ── ⭐ the orchestrator entry
                 ▼
┌─────────────────────────────────────────────────────────────┐
│  EDGE FUNCTION: encounter-orchestrator (Deno)                │
│  infrastructure/medbase/functions/encounter-orchestrator/    │
│  ────────────────────────────────────────────────────────── │
│  Reads the event, dispatches to the matching handler:        │
│    • handleEncounterSeeded                                   │
│    • handleOrderCreated                                      │
│    • handleTicketUpdated / handleTicketCompleted             │
│    • handleFinancialUpdated  ← updates financial_summary     │
│    • handleEncounterClosed                                   │
│    • handleQueueWorkflowTransition  ← spawns dept queue rows │
│    • handleClinicalUpdated / handleClaimUpdated              │
│    ... 20+ handlers total                                    │
│                                                              │
│  Each handler writes to ONE OR BOTH read-model tables:       │
└────────────────┬────────────────────────────────────────────┘
                 │
                 ├──── WRITES MACRO VIEW ──────────────────────┐
                 ▼                                              ▼
┌────────────────────────────────┐    ┌───────────────────────────────┐
│ encounter_journey_cache         │    │ department_queues             │
│ ───────────────────────────── │    │ ──────────────────────────── │
│ One row per encounter (manifest │    │ One row per queue ticket      │
│ macro view).                    │    │ (operational micro view).     │
│                                 │    │                               │
│ Columns:                        │    │ Columns:                      │
│   encounter_id (PK)             │    │   id, ticket_id (UNIQUE)      │
│   patient_id                    │    │   dept_type ← 'billing',      │
│   pending_tickets    (jsonb)    │    │              'consultation',  │
│   completed_tickets  (jsonb)    │    │              'pharmacy', ...  │
│   financial_summary  (jsonb)    │    │   encounter_id, patient_id    │
│   clinical_data      (jsonb)    │    │   patient_hn, patient_name    │
│   clinical_context   (jsonb)    │    │   encounter_vn                │
│   active_alerts      (jsonb)    │    │   queue_number, priority      │
│   doctor_summary     (jsonb)    │    │   status ('WAITING',          │
│   safety_snapshot    (jsonb)    │    │           'ACTIVE',           │
│   has_active_allergies          │    │           'COMPLETED',        │
│   has_active_medications        │    │           'CANCELLED')        │
│   manifest_version, last_event… │    │   metadata (jsonb)            │
│                                 │    │   called_at, recall_count     │
│ Use cases:                      │    │                               │
│   • Doctor worklist             │    │ Use cases:                    │
│   • Patient profile             │    │   • QueueCockpit (call-next)  │
│   • Finance dashboard            │    │   • Department workstations   │
│   • Clinical sidebar            │    │   • Today-only operational    │
└────────────────┬────────────────┘    │     queue                     │
                 │                     └───────────────┬───────────────┘
                 │                                     │
                 │  Joined by encounter_id at read     │
                 │  time. department_queues is the     │
                 │  primary; cache supplies context.   │
                 └─────────────────┬───────────────────┘
                                   ▼
                ┌──────────────────────────────────────┐
                │ Frontend — React Query + Realtime    │
                │ useWorkflowConfigWorklist            │
                │ useManifestDoctorWorklist            │
                │ useManifestRow                       │
                └──────────────────────────────────────┘

2. Trigger inventory

There are ~130 triggers in the public schema. Most are simple updated_at bumpers. The ones that affect the encounter-orchestrator pipeline are listed below — these are the structural triggers you must understand to maintain or extend the system.

2.1 Critical orchestrator triggers (do NOT touch without coordination)

Trigger	Table	Timing / Event	Function	Purpose
trigger_orchestrator	hospital_events	AFTER INSERT	pg_net.http_post(...) to encounter-orchestrator edge function	The PRIMARY entry into the orchestrator. Every backend event lands here, fires HTTP to Deno.
on_hospital_event_insert	hospital_events	AFTER INSERT	audit trail / dead-letter housekeeping	Records event metadata, retry counters.
on_hospital_event_cds	hospital_events	AFTER INSERT	clinical-decision-support hook	Forks events into the CDS rule engine.

These three fire on every backend write. Performance-sensitive — the orchestrator function must be idempotent and complete in <2s or the event handler retry kicks in.

2.2 Read-model derivation triggers

These triggers maintain derived state in the read-model layer. They run on Postgres, not in the orchestrator.

Trigger	Table	Event	Owner	What it does
⭐ trg_sync_billing_queue	encounter_journey_cache	INSERT, UPDATE OF financial_summary	this doc	Auto-spawns billing-pending row in department_queues whenever financial_summary changes. See billing-queue-auto-sync.md for the full design.
⭐ trg_propagate_patient_context	encounter_journey_cache	BEFORE INSERT/UPDATE	this doc	If clinical_context.patient_context is empty/missing, copies it from any other encounter of the same patient. Runs BEFORE so propagated values are visible to trg_sync_billing_queue. See billing-queue-auto-sync.md §7 for the full 2-layer fix.
⭐ trg_obstetric_risk_from_form	dynamic_form_usage	AFTER INSERT/UPDATE (form_id ∈ high_risk_pregnancy_monitoring, anc_screening_visit)	20260610_obstetric_high_risk_projection.sql	Projects the high-risk-pregnancy + ANC carry-forward summary onto encounter_journey_cache.clinical_flags.obstetric_high_risk + a single deduped active_alerts entry (code='OBSTETRIC_HIGH_RISK'), so the labour charge nurse sees high-risk at a glance (TOR 3.11 req 10/27). A high-risk row → its own (labour) encounter; an ANC row → all the patient’s cache rows. SQL mirror of useLabourRiskSummary.ts. Idempotent (flag compared sans updatedAt), fail-soft, SECURITY DEFINER.
⭐ trg_obstetric_risk_on_cache_insert	encounter_journey_cache	BEFORE INSERT	20260610_obstetric_high_risk_projection.sql	Carry-forward backfill: stamps clinical_flags.obstetric_high_risk + the obstetric active_alert onto a new encounter from the patient’s prior ANC / high-risk forms — covers the common case where ANC flagged high-risk months before the labour encounter existed. Cheap indexed EXISTS gate skips non-obstetric patients. Fail-soft.
⭐ trg_sync_transport_location	transport_request	AFTER INSERT, UPDATE OF status	20260610b_transport_location_sync.sql	Projects porter/stretcher transport status into encounter_journey_cache.clinical_context.transfer_context (the shape resolvePatientLocation() reads) so an en_route patient shows as in transit in worklist location columns and the DCC Timeline. Clears the subkey on completed/cancelled only when transportRequestId matches (never clobbers orchestrator-written PatientTransfer context). Never inserts cache rows. Cascade: fires trg_auto_coding_worklist (short-circuits) — not trg_sync_billing_queue (financial_summary untouched).
trg_auto_queue_number	department_queues	BEFORE INSERT	queue-numbering migration	Generates queue_number like H1, B-042 based on dept_type and today’s count if not provided.
trg_ipd_compute_los	ipd_admissions	BEFORE INSERT/UPDATE	IPD module	Computes length-of-stay from admit/discharge timestamps.
trigger_update_room_occupancy	nursing_home_residents	AFTER INSERT/UPDATE	Nursing home module	Updates nursing_home_rooms.occupancy_count when residents move.
trg_focus_list_history_*	focus_list	AFTER INSERT/UPDATE/DELETE	Patient focus list	Snapshots every change to focus_list_history.
update_cycle_evaluation_trigger	oncology_response_evaluations	AFTER INSERT/UPDATE	Oncology module	Re-derives cycle response classification (CR/PR/SD/PD).
trg_block_finalised_update	blood_lis_results	BEFORE UPDATE	Blood bank	Blocks updates to finalised LIS results (immutability guard).

2.3 Realtime broadcast triggers

These are pure pg_notify triggers used by the Supabase realtime layer. They never write data — they only broadcast change notifications so connected frontends can invalidate caches.

Trigger	Table	Channel
trg_ipd_admissions_notify	ipd_admissions_dashboard	ipd-admissions-dashboard
trg_ipd_bed_summaries_notify	ipd_ward_bed_summaries	ipd-bed-summaries
trg_ipd_shift_summaries_notify	ipd_ward_shift_summaries	ipd-shift-summaries
bed_status_update_trigger	labour_room_bed_assignments	labour-room-beds

2.4 Audit / history triggers

Triggers that snapshot every change to a separate *_history table for regulatory or undo purposes.

Trigger	Source table	Audit table
trg_blood_bank_config_audit	blood_bank_config	blood_bank_config_audit
trigger_audit_report_changes	monthly_nursing_reports	monthly_nursing_reports_audit
trigger_audit_document_changes	visiting_nursing_info_provisions	visiting_nursing_info_provisions_audit
workflow_config_history_trigger	workflow_action_configurations	workflow_action_configurations_history

2.5 updated_at bumpers — the boring 80%

Roughly 80 triggers exist solely to set NEW.updated_at = NOW() on UPDATE. They follow naming conventions:

• update_{table}_updated_at
• trg_{table}_updated_at
• {table}_updated_at

These are safe to add/remove with the corresponding set_updated_at() helper function. Don’t over-engineer them — if you add a new table that needs updated_at, copy the pattern from any existing one.

3. Where to add new logic — decision tree

                Are you adding behavior that…
                            │
            ┌───────────────┴───────────────┐
            │                               │
       Reacts to a backend           Derives state purely from
       Moleculer event                an existing Supabase table?
            │                               │
            ▼                               ▼
   Add a handler in the          Add a Postgres trigger.
   orchestrator edge function     Pros: instant, transactional,
   (Deno).                        fires for ALL writers.
   Pros: business logic           Cons: PL/pgSQL is harder to
   in TypeScript, can call        debug, must be idempotent,
   external services, has         can deadlock with concurrent
   access to all tables via       writes.
   service-role.                  Best for: read-model fan-out,
   Cons: requires deploy,         derived columns, validation
   only fires for events,         guards.
   slower (HTTP round trip).
   Best for: cross-resource
   workflows, external API
   calls, complex transforms.

4. Dual-write hazard

The orchestrator and Postgres triggers can BOTH write to the same row — be careful:

• ⚠ trg_sync_billing_queue on encounter_journey_cache and the orchestrator’s handleQueueWorkflowTransition both write to department_queues (dept_type=‘billing’). Both use ON CONFLICT (ticket_id) DO NOTHING so they’re safe, but you can get duplicate inserts if an event arrives while a manual cache update is mid-flight. The unique constraint on ticket_id prevents data corruption — the second insert just no-ops.
• ⚠ Cascading triggers — if you write a trigger on table A that writes to table B, and another trigger on B writes back to A, you can create infinite loops. Postgres detects and aborts these but the partial writes can leave the cache inconsistent. Always document which tables a trigger writes to in the function COMMENT.

5. Maintenance checklist when adding a new trigger

1. ☐ Place migration in web/supabase/migrations/YYYYMMDD_descriptive_name.sql. Idempotent — wrap in CREATE OR REPLACE FUNCTION and DROP TRIGGER IF EXISTS ... CREATE TRIGGER ....
2. ☐ Add COMMENT ON FUNCTION describing the trigger’s contract — what tables it reads, what it writes, what events fire it, and any cascade hazards.
3. ☐ Add an entry to § 2 of this document so the next maintainer can find it without grepping.
4. ☐ If the trigger writes to a frequently-queried table, run EXPLAIN ANALYZE on the function body’s main statement to ensure no full-table scans.
5. ☐ Test idempotency — run the same INSERT/UPDATE twice and verify no duplicate side effects.
6. ☐ Test cascade behavior — if the trigger writes to a table with its own trigger, verify there’s no loop.
7. ☐ Apply via Supabase Management API or supabase db push (NOT via direct SQL) so the migration is recorded in supabase_migrations.schema_migrations.

6. Debugging a trigger-related issue

Start here when a frontend report says “row didn’t appear” or “update didn’t propagate”:

# 1. Confirm the trigger exists
psql > SELECT * FROM information_schema.triggers WHERE event_object_table = 'YOUR_TABLE';

# 2. Confirm the function definition matches what's in the migration
psql > SELECT pg_get_functiondef('YOUR_FUNCTION'::regproc);

# 3. Run the SQL trigger fires on, with RAISE NOTICE inside the function:
ALTER FUNCTION YOUR_FUNCTION SET log_min_messages = 'NOTICE';
-- Then re-execute the INSERT/UPDATE, look at Supabase logs.

# 4. Check the Supabase Edge Function logs (for orchestrator-triggered work):
#    Supabase dashboard → Edge Functions → encounter-orchestrator → Logs

For the orchestrator specifically:

• Every event has a delivery_id (UUID). Search for that delivery_id in the logs to trace the full pipeline.
• Failed events go to medbase_dead_letter_queue. Inspect the error_payload jsonb.

7. Related docs

• billing-queue-auto-sync.md — trg_sync_billing_queue design + backfill notes
• policy-gates.md — policy_gates runtime evaluation engine
• policy-gates-coverage.md — visual coverage map of policy gates in the UI
• patient-intake-resolver.md — workflow resolver contract
• department-command-center-tabs.md — DCC tabs starter kit
• web/src/services/manifest-data-layer/INTEGRATION_GUIDE.md — frontend manifest hooks reference
• infrastructure/medbase/functions/encounter-orchestrator/index.ts — orchestrator source

8. The 7 invariants

These hold across every trigger and orchestrator handler. Violating any one of them is a system-correctness break:

1. Idempotency. Same input must produce the same output, regardless of how many times the trigger fires. Use ON CONFLICT DO NOTHING or IS DISTINCT FROM guards.
2. No long transactions. Triggers that fire on event-rate tables (hospital_events, encounter_journey_cache) must complete in <100ms. Defer expensive work to the orchestrator edge function via event emission.
3. Encounter-id is sacred. Never delete, never re-use, never null. department_queues.encounter_id NOT NULL is enforced.
4. Sale-order-id is the billing ticket id. department_queues.ticket_id = sales_order._id for billing rows. Don’t generate synthetic IDs.
5. Status transitions are append-only. WAITING → ACTIVE → COMPLETED. Never go backwards in a trigger; that’s a bug — re-emit a new event instead.
6. No silent failures. Every trigger should RAISE NOTICE on skip and RAISE EXCEPTION on contract violations. Empty catch blocks erase the audit trail.
7. Triggers don’t call HTTP. Use pg_net.http_post ONLY in the orchestrator’s trigger_orchestrator. Other triggers must stay in-database.