medOS ultra

IPD Dispense Cycles

Configurable IPD pharmacy materialization: dispense_cycle_configs + runs + runner edge fn, two-layer canonical/cache model.

8 min read diagramsUpdated 2026-05-13docs/architecture/ipd-dispense-cycles.md

Status: Iteration 1 — landed 2026-05-14. Single-Supabase, per-hospital deployment (each hospital runs its own Supabase project; per-hospital tailoring happens through the super-admin UI, not via separate migrations).

Audience: super-admins configuring per-hospital pharmacy round timing, hold rules, and cart batching; engineers extending the materialization or reconcile logic.

TL;DR

Materializes IPD medication_administration_cycles rows from active inpatient medication_requests according to one or more editable dispense_cycle_configs rules. Replaces the per-render RRULE expansion currently in MarSystemProvider.tsx. Driven by:

  1. Supabase Postgres trigger on medication_requests insert/update (on-demand path), or
  2. pg_cron firing the runner every 30 min (scheduled path, disabled by default), or
  3. Super-admin “Run now” / “Preview” / “Reconcile” buttons (manual paths).

All paths converge on the same dispense-cycle-runner Deno edge function, which writes to medication_administration_cycles (real cycles) and medication_holds (auto-holds), and logs every execution to dispense_cycle_runs.

The page also features Supabase realtime presence: mouse cursors of other super-admins, per-row hover indicators, and edit-locks (clicking “Edit” on a row another admin is editing shows their avatar instead of opening the dialog). Pattern copied from web/packages/miniapps/dynamic-sheet/contexts/PresenceProvider.tsx and CursorPresenceContext.tsx.

Two-layer source-of-truth model

                     ┌──────────────────────────────────────┐
   canonical truth   │  ever-api-medication (NestJS)        │
   ───────────────►  │  domain service, db-agnostic         │
                     │  (currently Mongo, but free to move) │
                     └────────────┬─────────────────────────┘
                                  │ events + periodic projection
                                  ▼
                     ┌──────────────────────────────────────┐
   realtime collab   │  Supabase                            │
   ───────────────►  │  medication_administration_cycles    │
   + projection      │  medication_holds                    │
   + admin config    │  dispense_cycle_configs              │
                     │  dispense_cycle_runs                 │
                     │  + realtime presence channel         │
                     └──────────────────────────────────────┘
  • Canonical: the medication domain service (NestJS) is the system of record for what orders exist, their status, frequency, and lifecycle. Persistence is currently MongoDB but the contract is domain — anything db-of-the-week reaches it the same way.
  • Projection / cache: Supabase holds a materialized view for the frontend to read cheaply and for cross-admin realtime collaboration. The cycles table is a derived projection, not the source of truth for whether a dose was administered — that still flows through the medication service.
  • Reconcile loop: dispense_cycle_reconcile() RPC → edge fn diffs the projection against the domain service and surfaces drift (missing-projection, frequency-mismatch, orphan-projection). Drift is reported, never silently rewritten. Admin decides what to do.

This same model applies to all other Supabase realtime tables (department_queues, encounter_journey_cache, etc.) — Supabase is the collab layer, the domain services are the truth.

Data model

dispense_cycle_configs

Editable rules. Multiple rows allowed; higher priority wins when more than one matches an order. Soft-toggleable via status.

Column Notes
id UUID primary key
name TEXT unique within an org
description TEXT optional
status TEXT draft / active / inactive. Only active is picked up by the runner.
priority INTEGER higher wins on conflict (ties broken by created_at ASC)
organization_id UUID NULLable; forward-compat for the eventual multi-tenant rollout. UI hides the column when NULL.
scope_json JSONB { ward_ids[], patient_classes[], med_categories[], encounter_types[] }. Empty array = “applies to all” for that dimension (mirrors policy_gates).
schedule_json JSONB { timezone, lookahead_hours, generation_mode, rounds[], default_frequency }. generation_mode is on_demand / scheduled / hybrid.
hold_json JSONB { auto_hold_on_npo, auto_hold_on_discharge_pending, auto_hold_on_transfer, hold_reason_default }.
cart_json JSONB { group_by: 'ward'|'round'|'prescriber'|'patient', require_pharmacist_ack }. Hint for the pharmacy worklist UI.
audit cols created_at / updated_at / created_by / updated_by

dispense_cycle_runs

Append-only execution log. One row per cron tick, manual run, trigger fire, preview, or reconcile.

Column Notes
id UUID primary key
config_id UUID FK → dispense_cycle_configs. NULL when running all active configs.
triggered_by TEXT cron / manual / trigger / preview / reconcile
triggered_by_user_id UUID for manual / preview / reconcile
started_at, completed_at timestamps
status TEXT running / success / partial / failed
cycles_materialized / cycles_skipped / cycles_held / orders_scanned counts
summary_json JSONB per-config breakdown, per-order decisions, drift sample for reconcile
error_payload JSONB error list

dispense_cycle_configs_history

UPDATE/DELETE snapshots on dispense_cycle_configs. Append-only — same pattern as blood_bank_config_audit and workflow_action_configurations_history.

Projection target (existing)

medication_administration_cycles from web/supabase/migrations/20250208_emar_system.sql. The runner inserts into this table; no schema changes. Future iteration: extend the table with medication_request_id + scheduled_at unique constraint so idempotency is enforced in SQL rather than the runner’s read-then-write check.

Runtime: the three invocation paths

1. On-demand (Postgres trigger — not yet wired)

Deferred to iteration 2. Will add a BEFORE INSERT OR UPDATE trigger on the medication_requests projection table that calls dispense_cycle_run(NULL, 'trigger', NULL) with a debounce. Today the runner only fires on cron / manual.

2. Scheduled (pg_cron)

One row in cron_jobs: ipd-dispense-cycle-runner, schedule */30 * * * *, command SELECT public.dispense_cycle_run(NULL, 'cron', NULL);. Disabled by default. Enable from /super-admin/cron-jobs after validating round times for the hospital.

3. Manual / preview / reconcile (super-admin UI)

Three buttons on /super-admin/dispense-cycles:

  • Run now (per row) — fires the runner for one config. Writes happen.
  • Preview (per row) — calls the edge function directly with preview: true. No DB writes; dispense_cycle_runs row tagged triggered_by='preview' so previews don’t pollute audit.
  • Reconcile (page-level) — calls dispense_cycle_reconcile() which routes to the edge function with reconcile: true. Edge fn HTTP-fetches the medication domain service, diffs against the Supabase projection, writes drift counts + sample to the run row.

Runtime algorithm (materialization)

For each active dispense_cycle_configs row in priority order:

  1. Load eligible medication_requests from Supabase (status in active|in_progress|verified, limit 1000).
  2. For each order, check scope_json match (patient_class, ward, med_category, encounter_type).
  3. Check hold signals via encounter_journey_cache (npo_active, discharge_pending, transfer_pending). If any matching hold rule fires, insert medication_holds row and skip cycle.
  4. Expand frequency to times: known patterns (OD/BID/TID/QID/Q4H/Q6H/Q8H/Q12H/HS/AM/PM), Thai/English numeric patterns (“วันละ 4 ครั้ง”, “4 times daily”) fall back to rounds[] from the config. Last-resort: default_frequency.
  5. For each day in lookahead_hours, materialize one cycle row per (date, time) tuple. Skip times outside the window.
  6. Idempotency: query existing pending cycles for the same medication_request_id today, dedupe by time before inserting.

Realtime presence (Supabase channel)

One channel dispense-cycles:room (Supabase Realtime presence, not Postgres-changes). Each connected super-admin broadcasts:

{ userId, username, hoveringConfigId, editingConfigId, x, y, lastActive }

The UI consumes this in three places:

  • Peer avatar stack in the page header (DispenseCyclePeerAvatars).
  • Cursor layer floating over the whole page (DispenseCycleCursorLayer).
  • Per-row badge showing peers hovering/editing that config (PeerOnConfigBadge), with the edit button disabled when another admin is editingConfigId of that row.

Cursor throttle: 50ms. Stale-peer prune: 30 s of no lastActive updates.

File index

File Purpose
infrastructure/medbase/migrations/20260514_ipd_dispense_cycles.sql All three tables + history trigger + RPCs + realtime + RLS + seed config (draft) + seed cron job (disabled)
infrastructure/medbase/functions/dispense-cycle-runner/index.ts Deno edge function. Three modes: materialize, preview, reconcile.
web/src/services/super-admin/dispenseCycles.service.ts TS service: CRUD + run + reconcile + realtime subscriptions + defaults
web/src/common/components/super-admin/dispense-cycles/DispenseCyclesPage.tsx List + tabs (configs / runs) + run/preview/reconcile actions
web/src/common/components/super-admin/dispense-cycles/DispenseCycleEditDialog.tsx 5-section editor (Identity / Scope / Schedule / Hold / Cart)
web/src/common/components/super-admin/dispense-cycles/DispenseCycleRunDrawer.tsx Run details: counts, per-order decisions, errors, drift sample (for reconcile runs)
web/src/common/components/super-admin/dispense-cycles/DispenseCyclePresence.tsx Realtime presence: provider + cursor layer + peer avatars + per-row badge
web/src/containers/super-admin-dispense-cycles/page.tsx Route wrapper with super-admin guard
web/src/routes-integrated.tsx Route /super-admin/dispense-cycles
web/src/common/components/super-admin/SuperAdminDashboard.tsx Tile node + shortcut entry

Deployment

Each hospital runs its own Supabase project. To deploy:

  1. Apply migration via Supabase SQL editor (CLI db push is currently blocked by a drifted history table — see CLAUDE.md):
    -- paste 20260514_ipd_dispense_cycles.sql into the Supabase SQL editor
  2. Set DB GUCs so pg_cron’s HTTP-post and the manual RPC know where to call:
    ALTER DATABASE postgres SET app.settings.edge_functions_url
  = 'https://<project>.functions.supabase.co';
ALTER DATABASE postgres SET app.settings.service_role_key
  = '<service-role-jwt>';
  3. Deploy the edge function:
    supabase functions deploy dispense-cycle-runner --project-ref <project-ref>
  4. Set MEDICATION_DOMAIN_URL env on the edge function (or fall back to VITE_APP_API if set), pointing at the medication domain service’s public API.
  5. Frontend auto-picks up the new route from routes-integrated.tsx once the build deploys.
  6. Visit /super-admin/dispense-cycles. The default default-ipd-cycle config is seeded in draft — review the rounds / scope / hold rules for the hospital, then flip status to active.
  7. Cron stays disabled until the admin flips ipd-dispense-cycle-runner on at /super-admin/cron-jobs.

Per-hospital tailoring

Configurable without code changes — done by the super-admin via the UI:

  • Timezone (Asia/Tokyo, Asia/Bangkok, Asia/Manila, etc.) — affects all “today” calculations.
  • Rounds — JP hospitals tend to use 4 rounds (06/12/18/22); some Thai wards do 3 rounds (08/14/20); Philippines varies.
  • Lookahead — 24h for standard wards, 72h for stable LTC.
  • Hold rules — JP nursing home rarely applies NPO holds; acute hospitals always do.
  • Cart grouping — by ward for centralized pharmacy, by patient for unit-dose models.
  • Status — draft → active only after the super-admin reviews everything.

Deferred work

  1. Postgres trigger on medication_requests insert/update to fire dispense_cycle_run(NULL, 'trigger', NULL) with debounce. Currently only cron + manual.
  2. Unique constraint on (medication_request_id, scheduled_times[0], status='pending') to enforce idempotency in SQL instead of the runner’s read-then-write loop.
  3. Reconcile auto-fix — currently reports drift only. Adding an opt-in “Apply drift fix” toggle is straightforward but needs a separate audit story.
  4. Per-hospital config sync — when the multi-tenant model lands, propagate organization_id IS NULL template rows to per-org rows on tenant creation.
  5. Frontend e-MAR refactor — MarSystemProvider.tsx should read materialized cycles from Supabase realtime instead of re-expanding RRULE per render. This is the actual win that justified building this system.
  6. amountGenerateDrug + nextDate cursor — port the his-vajira batching field so “dispense 3 days at a time, advance nextDate, only re-materialize past it” works.

References

Ask Anything