Cross-Region Policy Gates Deployment

Audience: DevOps engineer deploying medOS to a new region (Japan, Thailand, Philippines, etc.) Status: Verified on PH demo Supabase. Same migrations work for any region.

1. What needs to land in every region’s Supabase

The work shipped on 2026-04-25 includes 6 migration files. All are idempotent and region-agnostic — same SQL on every Supabase project.

Migration	Purpose
20260425_policy_gates.sql	policy_gates table + 6 seed rules (2 active default, 4 draft demo)
20260425_policy_gates_graph_json.sql	Adds graph_json JSONB column for visual builder state
20260425_policy_gates_discharge_seed.sql	3 IPD discharge rules (1 active default, 2 draft demo bypasses)
20260425_billing_queue_auto_sync.sql	trg_sync_billing_queue trigger v1 + backfill
20260425_billing_queue_auto_sync_v2.sql	Trigger v2 — adds patient_name/hn population
20260425_patient_context_propagation.sql	trg_propagate_patient_context trigger + backfill
20260425_ipd_discharge_test_seed.sql	One demo IPD encounter for discharge gate testing

All located in web/supabase/migrations/.

2. Deploying to a new region

2.1 The Supabase project list (current)

Region	Supabase project ref	Used by
Philippines / Demo	hynsmfrevlsegbmjnoiy	his-philippines.vercel.app, his-japan.vercel.app, etc. (shared during demo phase)
Japan (future)	TBD	his-japan-nursing.vercel.app once split
Thailand (future)	TBD	his-thailand.vercel.app once split

Migration plan: During demo/dev, all Vercel projects share the PH Supabase. When a customer commits to production, provision a region-specific Supabase project and re-run all migrations against it.

2.2 Apply migrations via Supabase Management API

For each new region:

SUPABASE_PROJECT_REF=<new-project-ref>
SUPABASE_ACCESS_TOKEN=sbp_<your-pat>

cd web/supabase/migrations

for migration in 20260425_*.sql; do
  echo "Applying $migration..."
  SQL=$(cat "$migration" | jq -Rs .)
  curl -sS -X POST "https://api.supabase.com/v1/projects/$SUPABASE_PROJECT_REF/database/query" \
    -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" \
    -H "Content-Type: application/json" \
    -d "{\"query\": $SQL}"
  echo
done

2.3 Apply via Supabase CLI (preferred for CI/CD)

cd web
supabase link --project-ref $SUPABASE_PROJECT_REF
supabase db push

The CLI tracks applied migrations in supabase_migrations.schema_migrations and skips already-applied ones. Idempotent.

2.4 Region-specific seed adjustments

The default policy_gates seeds are in Thai with English fallbacks. For a non-Thai region, edit the seed migration before applying, OR override after:

Japan example:

UPDATE policy_gates
   SET action_json = jsonb_set(
         action_json,
         '{message}',
         to_jsonb('支払いを完了してから検体を採取してください'::text)
       )
 WHERE name = 'Pathology: payment required before specimen collection';

The message_en field stays the same. The frontend usePolicyGate hook reads message (locale-specific) when i18n.language === 'th' (or ‘ja’) and falls back to message_en otherwise.

3. Verification checklist (run on each region after deploy)

-- 1. policy_gates table exists with seed rules
SELECT count(*) AS rules, count(CASE WHEN status='active' THEN 1 END) AS active
  FROM policy_gates;
-- Expect: rules >= 9, active >= 2

-- 2. graph_json column exists
SELECT column_name FROM information_schema.columns
 WHERE table_name='policy_gates' AND column_name='graph_json';
-- Expect: 1 row

-- 3. Triggers installed
SELECT trigger_name FROM information_schema.triggers
 WHERE trigger_name IN ('trg_sync_billing_queue', 'trg_propagate_patient_context')
 ORDER BY trigger_name;
-- Expect: 2 distinct names (each fires on INSERT and UPDATE → 4 rows total)

-- 4. Realtime is enabled for policy_gates
SELECT pubname FROM pg_publication_tables
 WHERE tablename = 'policy_gates' AND pubname = 'supabase_realtime';
-- Expect: 1 row

-- 5. Smoke test — UPDATE financial_summary on any cache row, confirm trigger fires
UPDATE encounter_journey_cache
   SET financial_summary = jsonb_set(financial_summary, '{updatedAt}', to_jsonb(NOW()::text))
 WHERE financial_summary IS NOT NULL
 LIMIT 1;
-- Then check department_queues — a billing row should exist for that encounter

4. Rolling back (if needed)

Each migration is paired with a rollback. Run in reverse order of application:

-- Rollback IPD seed
DELETE FROM encounter_journey_cache WHERE encounter_id = 'demo-ipd-discharge-001';

-- Rollback patient context propagation trigger
DROP TRIGGER IF EXISTS trg_propagate_patient_context ON encounter_journey_cache;
DROP FUNCTION IF EXISTS propagate_patient_context_from_other_encounters();

-- Rollback billing queue auto-sync trigger
DROP TRIGGER IF EXISTS trg_sync_billing_queue ON encounter_journey_cache;
DROP FUNCTION IF EXISTS sync_billing_queue_from_financial_summary();

-- Rollback discharge seed rules
DELETE FROM policy_gates WHERE trigger_action = 'discharge_patient';

-- Rollback graph_json column
ALTER TABLE policy_gates DROP COLUMN IF EXISTS graph_json;

-- Rollback policy_gates table entirely (NUKES the rule engine — last resort)
DROP TABLE IF EXISTS policy_gates CASCADE;

After rolling back, the legacy hardcoded isPaid checks in pathology dialogs continue to work — usePolicyGate returns gates.length === 0 so the legacy fallback path takes over. No code regression on rollback.

5. Multi-region differences worth knowing

Concern	Thailand / Demo	Japan	Philippines
Default locale	th	ja	fil, en
Insurance scheme	socialSecurity, Bnh90, กรมบัญชีกลาง	kaigo (LTC), kokuho	philhealth
Default IPD discharge rule message	Thai	Japanese	Filipino + English
ER bypass priority	500 (default)	Likely needs region-specific patient class enums	Same as Thailand
Currency	THB	JPY	PHP

The trigger logic is currency-agnostic — it stores total and netPay as numerics with no currency conversion. The metadata.currency field on the queue row tells the frontend how to format. For multi-currency facilities, this is fine; for cross-currency comparisons, add a converter at read time.

6. Performance characteristics (verified PH project, 2026-04-25)

Operation	Time
trg_propagate_patient_context (BEFORE UPDATE)	~5.2 ms
trg_sync_billing_queue (AFTER UPDATE)	~0.4 ms
Total update overhead	~13 ms
policy_gates SELECT (active rules for trigger=collect_specimen)	<1 ms (uses idx_policy_gates_trigger_action)
evaluateGates() JS, 5 active rules, single context	<0.5 ms

Scaling: For 10k encounters/day × 3 financial events each, that’s 30k UPDATE calls × 13ms = 6.5 minutes total CPU/day on the database. Acceptable for any size hospital. If we ever exceed 100k encounters/day per region, consider:

• Move propagation logic into the orchestrator edge function (offload from Postgres)
• Add idx_encounter_journey_cache_patient_id if not already present (it isn’t — TODO)
• Consider partitioning encounter_journey_cache by month or year

7. Related docs

• encounter-orchestrator-triggers.md — master trigger reference
• billing-queue-auto-sync.md — specific trigger design notes
• policy-gates.md — rule engine architecture
• policy-gates-coverage.md — UI gate coverage map
• policy-gates-cashier-discharge.md (in docs/demo-scripts/) — Wednesday demo script