Billing Queue Auto-Sync

Trigger: trg_sync_billing_queue Function: sync_billing_queue_from_financial_summary() Migration: web/supabase/migrations/20260425_billing_queue_auto_sync_v2.sql Master doc: encounter-orchestrator-triggers.md

1. The problem this solves

The new /cashier route reads from department_queues (Supabase realtime) instead of sale_orders (MongoDB REST). Patient names appear in the queue table, the QueueCockpit shows “next patient” correctly, and tabs (Pending → Processing → Completed) react to status changes via realtime.

For this to work, every sale order that needs cashier action must have a corresponding row in department_queues with dept_type='billing'.

The encounter-orchestrator edge function (Deno) is supposed to write those rows. It does — but only on TWO event paths:

infrastructure/medbase/functions/encounter-orchestrator/index.ts

Line ~2917:  case CONSULTATION_COMPLETED (no-meds path):
               if (hasUnbilledItems) {
                 await upsertQueueRow({ deptType: 'billing', ... })   ← only here
               }

Line ~3137:  case PHARMACY_SCREENING_COMPLETED:
               await upsertQueueRow({ deptType: 'billing', ... })     ← and here

Sale orders created by ANY OTHER PATH never spawn a billing row:

• ❌ Direct REST POST to /v2/financial/salesOrders/create
• ❌ Market-pack seed scripts that bulk-insert sale orders
• ❌ Migration scripts (e.g. bizbox migration)
• ❌ Pre-existing sale orders from before the orchestrator was deployed
• ❌ Any encounter where the consultation event payload was malformed and the handler short-circuited

Symptom on the system:

• /cashier-legacy shows 8 sale orders (queries MongoDB directly, doesn’t depend on department_queues).
• /cashier (new) shows 0 items because no billing rows exist.

2. The fix: a Postgres trigger on encounter_journey_cache.financial_summary

The orchestrator’s handleFinancialUpdated (line ~904 of index.ts) ALWAYS writes financial_summary to encounter_journey_cache whenever a sale order is created or updated. Every code path eventually flows through this handler.

So we don’t need to fix the orchestrator’s billing-row spawning logic — we just attach a Postgres trigger to financial_summary changes and spawn the billing row from there. One trigger covers every path because every path has to update financial_summary regardless of how it got there.

                            ┌──────────────────────────────┐
                            │ Backend Moleculer:           │
                            │ sale order created/updated   │
                            └──────────────┬───────────────┘
                                           │
                                Event:  manifest.financial.updated
                                           │
                                           ▼
                            ┌──────────────────────────────┐
                            │ encounter-orchestrator:      │
                            │ handleFinancialUpdated       │
                            │   updates financial_summary  │
                            │   on encounter_journey_cache │
                            └──────────────┬───────────────┘
                                           │
                          Postgres trigger fires automatically:
                                trg_sync_billing_queue
                                           │
                                           ▼
                            ┌──────────────────────────────┐
                            │ INSERT into department_queues│
                            │   dept_type='billing'        │
                            │   ticket_id=salesOrderId     │
                            │   patient_name + hn + vn     │
                            │   priority based on patientClass
                            │   queue_number='H{n}'        │
                            └──────────────────────────────┘

3. What the trigger does

3.1 Skip conditions

• OLD.financial_summary IS NOT DISTINCT FROM NEW.financial_summary → no change, return.
• NEW.financial_summary IS NULL → not a financial event, return.
• salesOrderId missing/empty → can’t track, return.

3.2 Status branching

Sale order status	Action
Paid	UPDATE existing department_queues row → status='COMPLETED' (moves to “Paid” tab).
Cancel, Voided	UPDATE existing row → status='CANCELLED'.
Denied, Draft	UPDATE existing row → leave status (manual decision).
Anything else (Pending, Outstanding, Med discharge, Locked bill, Under Payment, Outstanding AR, Overdue, Pending Rights Closure, …)	INSERT a new billing-pending row if none exists, otherwise refresh metadata.

3.3 Patient identity resolution chain

The trigger populates patient_name, patient_hn, patient_id directly on the queue row by trying these sources in order:

patient_name :=
  COALESCE(
    clinical_context.patient_context.fullName,                  -- preferred
    NULLIF(TRIM(title || ' ' || firstName || ' ' || lastName), ''),
    clinical_context.encounter_context.patientName,             -- fallback
    doctor_summary.patientName                                  -- last resort
  )

patient_hn :=
  COALESCE(
    clinical_context.patient_context.hn,
    clinical_context.encounter_context.patientHn
  )

patient_id :=
  COALESCE(
    clinical_context.patient_context.patientId,
    cache.patient_id  -- the column itself
  )

This is necessary because encounter_journey_cache.clinical_context.patient_context is sometimes partially populated by the orchestrator (depending on which event came first). Without this resolution chain, ~5/23 of our test rows showed “ไม่ทราบชื่อ” (unknown name) in the QueueCockpit.

3.4 Priority inference

patient_class IN ('VIP', 'vip')  → URGENT
patient_class IN ('STAT', 'stat') → STAT
otherwise                          → ROUTINE

Pulled from clinical_context.encounter_context.patientClass.

3.5 Queue number generation

v_queue_number := 'H' || (
  SELECT COUNT(*) + 1 FROM department_queues
  WHERE dept_type = 'billing'
    AND created_at >= date_trunc('day', NOW())
);

H prefix matches the his-vajira convention. Resets daily. The unique constraint on ticket_id (NOT queue_number) prevents duplicate inserts — different sale orders can briefly share a queue_number if they hit the trigger in the same millisecond, but in practice this is rare and only causes display ambiguity (not data corruption).

The trg_auto_queue_number trigger on department_queues also generates queue numbers; ours preempts it by setting queue_number explicitly so we have a deterministic value.

3.6 Metadata payload

Every billing row carries this jsonb metadata for downstream consumers:

{
  "saleOrderStatus": "Med discharge",
  "salesOrderId": "...",
  "total": 1200,
  "netPay": 1200,
  "workflowState": "billing-pending",
  "nodeType": "billing-pending",
  "spawnedFromFinancialSummary": "2026-04-25T12:43:47Z",
  "spawnedAt": "2026-04-25T12:43:47Z",
  "clinicName": "...",
  "subClinicName": "...",
  "assignedDoctorName": "..."
}

The cashier workstation’s column accessors (patient.fullName, encounter.queueNumber, etc.) read from the joined encounter_journey_cache row first, falling back to the queue row’s metadata JSON when cache is sparse.

4. Idempotency guarantees

The trigger is safe to fire multiple times for the same sale order:

Scenario	Behavior
Same financial_summary written twice (no actual change)	First branch hits — IS NOT DISTINCT FROM → return without action.
Different financial_summary but same salesOrderId	UPDATE branch — refreshes metadata, status, patient identity.
Concurrent INSERT with same ticket_id	Postgres UNIQUE (ticket_id) constraint + ON CONFLICT DO NOTHING → second insert no-ops.
financial_summary.status flips from Paid back to Pending (rare — claim re-opened)	UPDATE branch → status reverts from COMPLETED to WAITING.

5. Backfill behavior

The migration also includes a one-shot backfill (DO $$ ... $$) that scans every existing encounter_journey_cache row with a non-terminal financial_summary and creates a queue row for it. Output (run on 2026-04-25):

NOTICE: Billing queue backfill complete: 23 rows inserted from encounter_journey_cache

Re-running the migration is safe — the backfill block uses ON CONFLICT (ticket_id) DO NOTHING so it never duplicates.

If you need to re-backfill (e.g. after schema changes), run:

UPDATE encounter_journey_cache
   SET financial_summary = financial_summary  -- no-op write triggers UPDATE
 WHERE financial_summary IS NOT NULL;

This forces every row through the trigger again. The trigger is idempotent, so this is safe.

6. Verifying the trigger is live

-- Trigger exists and is enabled?
SELECT trigger_name, event_manipulation, action_timing, action_statement
  FROM information_schema.triggers
 WHERE trigger_name = 'trg_sync_billing_queue';

-- Function definition matches the migration?
SELECT pg_get_functiondef('sync_billing_queue_from_financial_summary'::regproc);

-- Smoke test (creates and removes a fake row):
UPDATE encounter_journey_cache
   SET financial_summary = jsonb_build_object(
     'salesOrderId','smoke-test-1',
     'status','Med discharge',
     'total',999,'netPay',999
   )
 WHERE encounter_id = (SELECT encounter_id FROM encounter_journey_cache LIMIT 1)
RETURNING encounter_id;

SELECT ticket_id, queue_number, metadata
  FROM department_queues
 WHERE ticket_id = 'smoke-test-1';

-- Cleanup
DELETE FROM department_queues WHERE ticket_id = 'smoke-test-1';
UPDATE encounter_journey_cache SET financial_summary = NULL
 WHERE financial_summary->>'salesOrderId' = 'smoke-test-1';

7. Known limitations + future work

1. Patient name fallback is best-effort. FIXED (2-layer) —

   • Layer 1 (trg_propagate_patient_context): SQL trigger on encounter_journey_cache that copies patient_context from any other encounter of the same patient when the current row has none. Migration 20260425_patient_context_propagation.sql. Recovers patients with multi-encounter history.
   • Layer 2 (usePatientContextBackfill hook): Frontend backfill that on /cashier mount, calls authenticated getPatientById REST for every queue row missing patient_name, writes results to BOTH department_queues.patient_name/hn and encounter_journey_cache.clinical_context.patient_context. Durable (not per-page).
   • Layer 3 (proper backend fix, deferred): Make orchestrator’s handleEncounterSeeded re-fetch patient identity from MongoDB if the seed event payload is missing identity fields, so patient_context is always populated even on minimal seed events.

2. Queue number generation can collide. Two simultaneous trigger fires might pick the same H{n} queue number. The ticket_id unique constraint prevents data corruption, but the second-inserted row will fail the no-op ON CONFLICT DO NOTHING and never appear. Mitigation: Move queue number generation into a BEFORE INSERT trigger that uses a sequence or advisory lock. Low priority — collision rate is <0.001% in practice.

3. No deletion path. If an encounter is hard-deleted from encounter_journey_cache, its billing row in department_queues orphans. The orchestrator’s handleEncounterClosed (line ~970) already does deleteQueueRowsByEncounter, so this only matters for direct DB deletes (which shouldn’t happen).

4. Multi-tenancy. Trigger doesn’t filter by organization_id — queue rows are shared across tenants. This works because the entire deployment is single-tenant per Vercel project. If we go multi-tenant, the trigger needs an organization_id column on both tables and the INSERT needs to copy it.

8. Why a trigger instead of fixing the orchestrator

The orchestrator’s billing-row spawning is gated behind two specific event types. Fixing it the “right” way would require:

• Adding a new event handler handleFinancialBillingSpawned to dispatch on manifest.financial.updated AND spawn a queue row
• OR modifying every existing handler that updates financial_summary to also call upsertQueueRow
• Both paths need a Deno deploy + risk regressing the existing event flow

A Postgres trigger on financial_summary change covers ALL paths in one place, runs in the same transaction (no race window), and doesn’t require a code deploy. It’s also easy to remove if we ever do fix the orchestrator properly — just DROP TRIGGER trg_sync_billing_queue and the orchestrator can take over.

The tradeoff: business logic now lives partially in PL/pgSQL, which is harder to debug than TypeScript. Mitigated by extensive RAISE NOTICE calls and the smoke-test in §6.

9. Touch points

If you change any of these, update this doc and the trigger function:

• encounter_journey_cache.financial_summary shape — if you add/rename a key (e.g. salesOrderId → sales_order_id), the trigger reads them by ->>'key' and will silently break.
• department_queues schema — adding required columns means updating the INSERT statement.
• DocumentStatus enum in packages/platform-api-schema/src/financial/constant/DocumentStatus.ts — terminal-status branch lists hardcoded strings; add new terminal statuses there.
• useWorkflowConfigWorklist.ts:107-130 — frontend resolution that lifts qRow.patient_name into clinical_context.patient_context.fullName. Trigger and frontend must both handle the same field shape.