medOS ultra

Real-Time Inventory & Supply Chain

Master plan for the realtime inventory and supply-chain system.

21 min read diagramsUpdated 2026-05-25docs/architecture/realtime-inventory-supply-chain.md

Status: Approved for implementation Date: 2026-05-24 Scope: ~4,500 LOC across 28 files (migration + edge function + frontend hooks + UI) Depends on: encounter-orchestrator pattern, hospital_events table, existing MongoDB stock entities

1. Problem Statement

The current inventory system is 100% REST → NestJS → MongoDB with no real-time layer:

  • Every page load re-queries MongoDB with heavy populate joins (item, location, vendor, uom, itemType)
  • No Supabase read model — zero inventory tables in the read-model layer
  • No push-based alerts — the “Stock Alert/Expire” tab is just a paginated list view
  • No stock-awareness at order time — doctors/nurses order without seeing availability
  • No morning snapshot — pharmacy staff manually check balances each morning
  • The StockBalanceEventMixin exists but only handles a daily cron for expiry alerts via the old batch-script system

What every other module already does

MongoDB write → Moleculer event → hospital_events INSERT → encounter-orchestrator
  → Supabase read model → frontend realtime subscription

Inventory is the only major domain that never entered this pattern.

2. Architecture Overview

┌─────────────────────────────────────────────────────────────────────┐
│                        WRITE PATH (existing)                        │
│                                                                     │
│  Pharmacy/Nurse/Store ──REST──▶ foundation service ──▶ MongoDB      │
│  (stockTransaction)          (stockBalance, stockItem,              │
│                               stockBatch, stockReturn, etc.)        │
└──────────────────────────┬──────────────────────────────────────────┘
                           │
                    Moleculer events
                    (NEW: STOCK_*)
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────────┐
│                    EVENT BRIDGE (new)                                │
│                                                                     │
│  webhookDispatcher in foundation service                            │
│  → INSERT INTO hospital_events (event_type = 'STOCK_*')             │
│  → pg_notify('hospital_events')                                     │
└──────────────────────────┬──────────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────────┐
│              INVENTORY ORCHESTRATOR (new Supabase edge fn)           │
│                                                                     │
│  infrastructure/medbase/functions/inventory-orchestrator/            │
│                                                                     │
│  Handlers:                                                          │
│  ├── handleStockBalanceChanged   → upsert stock_balance_cache       │
│  ├── handleLowStockDetected     → insert stock_alerts               │
│  ├── handleExpiryApproaching    → insert stock_alerts               │
│  ├── handleGoodReceive          → update stock_balance_cache         │
│  ├── handleStockTransfer        → update both locations              │
│  └── handleStockReserved        → update reserved_qty               │
└──────────────────────────┬──────────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────────┐
│                 SUPABASE READ MODEL (new tables)                    │
│                                                                     │
│  stock_balance_cache     — realtime per-item, per-location balance  │
│  stock_alerts            — low-stock + expiry + stockout alerts     │
│  stock_movement_log      — recent movements for realtime feed       │
│  stock_item_catalog      — denormalized product catalog             │
│  stock_reorder_rules     — min/max/reorder-point per item+location  │
└──────────────────────────┬──────────────────────────────────────────┘
                           │
                    Supabase Realtime
                    (postgres_changes)
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────────┐
│                     FRONTEND (new hooks + UI)                       │
│                                                                     │
│  useStockBalanceRealtime(locationId)   — live balance per store     │
│  useStockAlerts(scope)                 — live alert feed            │
│  useStockAvailability(itemId)          — inline in order dialog     │
│  <StockAlertSurface />                 — global FAB + drawer        │
│  <StockAvailabilityBadge />            — inline in order forms      │
│  <InventoryDashboard />                — morning snapshot view      │
│  <StockAlertInbox />                   — actionable alert inbox     │
└─────────────────────────────────────────────────────────────────────┘

3. Supabase Read Model — Table Designs

3.1 stock_balance_cache

The central realtime projection — one row per (item × location × batch).

CREATE TABLE IF NOT EXISTS public.stock_balance_cache (
  id              uuid DEFAULT gen_random_uuid() PRIMARY KEY,

  -- MongoDB source refs (text, not uuid — MongoDB ObjectId strings)
  mongo_stock_balance_id  text NOT NULL,
  mongo_item_id           text NOT NULL,
  mongo_location_id       text NOT NULL,
  mongo_batch_id          text,                -- nullable (unbatched items)

  -- Denormalized item info (avoids joins)
  item_code               text NOT NULL,
  item_name               text NOT NULL,
  item_name_local         text,                -- TH/JA/FIL name
  item_type_name          text,                -- e.g. "Drug", "Diagnostic", "Medical Supply"
  item_sku                text,
  barcode                 text,
  base_uom_name           text,                -- e.g. "Tablet", "Box", "Piece"

  -- Denormalized location info
  location_code           text NOT NULL,
  location_name           text NOT NULL,
  store_type              text,                -- 'pharmacy' | 'main_store' | 'sub_store' | 'ward'

  -- Balance data (the realtime payload)
  quantity_on_hand        numeric(12,2) NOT NULL DEFAULT 0,
  reserved_qty            numeric(12,2) NOT NULL DEFAULT 0,
  available_qty           numeric(12,2) GENERATED ALWAYS AS
                            (quantity_on_hand - reserved_qty) STORED,
  wac                     numeric(14,4) NOT NULL DEFAULT 0,
  total_value             numeric(16,2) GENERATED ALWAYS AS
                            (quantity_on_hand * wac) STORED,

  -- Batch/expiry info
  batch_number            text,
  expiration_date         date,
  days_until_expiry       integer GENERATED ALWAYS AS
                            (CASE WHEN expiration_date IS NOT NULL
                              THEN (expiration_date - CURRENT_DATE)
                              ELSE NULL END) STORED,

  -- Reorder thresholds (per item+location, editable via admin UI)
  reorder_point           numeric(12,2),       -- when qty < this → LOW_STOCK alert
  reorder_qty             numeric(12,2),       -- suggested order quantity
  min_qty                 numeric(12,2),       -- hard minimum
  max_qty                 numeric(12,2),       -- max storage capacity
  expiry_alert_days       integer DEFAULT 90,  -- days before expiry to alert

  -- Status flags (computed by orchestrator or triggers)
  stock_status            text NOT NULL DEFAULT 'normal'
                            CHECK (stock_status IN (
                              'normal',         -- qty > reorder_point
                              'low',            -- qty <= reorder_point AND qty > min_qty
                              'critical',       -- qty <= min_qty AND qty > 0
                              'stockout',       -- qty = 0
                              'overstock',      -- qty > max_qty
                              'expiring_soon',  -- days_until_expiry <= expiry_alert_days
                              'expired'         -- expiration_date < CURRENT_DATE
                            )),

  -- Timestamps
  last_movement_at        timestamptz,
  last_received_at        timestamptz,
  last_issued_at          timestamptz,
  synced_at               timestamptz NOT NULL DEFAULT now(),
  created_at              timestamptz NOT NULL DEFAULT now(),
  updated_at              timestamptz NOT NULL DEFAULT now(),

  -- Uniqueness: one row per item+location+batch
  UNIQUE (mongo_item_id, mongo_location_id, COALESCE(mongo_batch_id, '__none__'))
);

-- Indexes for common queries
CREATE INDEX idx_sbc_location       ON stock_balance_cache (mongo_location_id);
CREATE INDEX idx_sbc_item           ON stock_balance_cache (mongo_item_id);
CREATE INDEX idx_sbc_status         ON stock_balance_cache (stock_status) WHERE stock_status != 'normal';
CREATE INDEX idx_sbc_expiry         ON stock_balance_cache (expiration_date) WHERE expiration_date IS NOT NULL;
CREATE INDEX idx_sbc_item_type      ON stock_balance_cache (item_type_name);
CREATE INDEX idx_sbc_available      ON stock_balance_cache (available_qty);

-- Enable realtime
ALTER PUBLICATION supabase_realtime ADD TABLE stock_balance_cache;

COMMENT ON TABLE stock_balance_cache IS
  'Realtime projection of stock_balance (MongoDB) — one row per item×location×batch. '
  'Populated by inventory-orchestrator edge function on STOCK_* hospital_events. '
  'Frontend subscribes via useStockBalanceRealtime(). Never written by frontend.';

3.2 stock_alerts

Push-based alerts for low stock, expiry, stockout — replaces the old poll-based StockAlertExpire view.

CREATE TABLE IF NOT EXISTS public.stock_alerts (
  id              uuid DEFAULT gen_random_uuid() PRIMARY KEY,

  -- What triggered this alert
  alert_type      text NOT NULL CHECK (alert_type IN (
                    'low_stock',        -- qty fell below reorder_point
                    'critical_stock',   -- qty fell below min_qty
                    'stockout',         -- qty reached 0
                    'expiring_soon',    -- approaching expiry_alert_days
                    'expired',          -- past expiration_date
                    'overstock',        -- qty exceeded max_qty
                    'reorder_suggested' -- auto-generated PR suggestion
                  )),
  severity        text NOT NULL DEFAULT 'warning'
                    CHECK (severity IN ('info', 'warning', 'critical')),

  -- What item + where
  stock_balance_cache_id  uuid REFERENCES stock_balance_cache(id) ON DELETE CASCADE,
  mongo_item_id           text NOT NULL,
  mongo_location_id       text NOT NULL,
  item_name               text NOT NULL,
  item_code               text NOT NULL,
  location_name           text NOT NULL,

  -- Alert details
  current_qty             numeric(12,2),
  threshold_qty           numeric(12,2),      -- the reorder_point / min_qty that was breached
  expiration_date         date,
  days_until_expiry       integer,

  -- Suggested action
  suggested_action        text,               -- e.g. "Create PR for 500 units"
  suggested_reorder_qty   numeric(12,2),

  -- Resolution tracking
  status                  text NOT NULL DEFAULT 'active'
                            CHECK (status IN ('active', 'acknowledged', 'resolved', 'snoozed', 'auto_resolved')),
  acknowledged_by         uuid,               -- auth.users FK
  acknowledged_at         timestamptz,
  resolved_by             uuid,
  resolved_at             timestamptz,
  resolution_note         text,
  snooze_until            timestamptz,

  -- Dedup key — prevent duplicate alerts for same item+location+type
  dedup_key               text GENERATED ALWAYS AS
                            (mongo_item_id || '::' || mongo_location_id || '::' || alert_type) STORED,

  created_at              timestamptz NOT NULL DEFAULT now(),
  updated_at              timestamptz NOT NULL DEFAULT now()
);

-- Only one active alert per item+location+type
CREATE UNIQUE INDEX idx_sa_dedup ON stock_alerts (dedup_key) WHERE status = 'active';
CREATE INDEX idx_sa_status      ON stock_alerts (status) WHERE status = 'active';
CREATE INDEX idx_sa_severity    ON stock_alerts (severity, status);
CREATE INDEX idx_sa_location    ON stock_alerts (mongo_location_id, status);
CREATE INDEX idx_sa_type        ON stock_alerts (alert_type, status);

-- Enable realtime
ALTER PUBLICATION supabase_realtime ADD TABLE stock_alerts;

COMMENT ON TABLE stock_alerts IS
  'Realtime stock alerts — low stock, expiry, stockout. '
  'Populated by inventory-orchestrator. Deduplicated by item+location+type. '
  'Frontend subscribes via useStockAlerts(). Actionable: acknowledge, snooze, resolve.';

3.3 stock_movement_log

Recent movements for realtime activity feed (last 7 days kept hot, older archived).

CREATE TABLE IF NOT EXISTS public.stock_movement_log (
  id                  uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  mongo_transaction_id text NOT NULL UNIQUE,

  -- Movement details
  movement_type       text NOT NULL CHECK (movement_type IN (
                        'good_receive', 'stock_issue', 'stock_transfer',
                        'stock_adjustment', 'stock_consumption', 'stock_exchange',
                        'stock_lending', 'stock_return', 'disposal',
                        'stock_reserve', 'accept_order', 'cancel_order'
                      )),
  direction           text NOT NULL CHECK (direction IN ('in', 'out', 'internal')),

  -- What moved
  mongo_item_id       text NOT NULL,
  item_name           text NOT NULL,
  item_code           text NOT NULL,
  quantity            numeric(12,2) NOT NULL,
  before_qty          numeric(12,2),
  after_qty           numeric(12,2),

  -- Where
  mongo_location_id   text NOT NULL,
  location_name       text NOT NULL,
  mongo_to_location_id text,                  -- for transfers
  to_location_name    text,

  -- Who
  user_display_name   text,
  user_id             text,
  department_name     text,

  -- Cost
  unit_cost           numeric(14,4),
  total_cost          numeric(16,2),
  wac_before          numeric(14,4),
  wac_after           numeric(14,4),

  -- Refs
  batch_number        text,
  ref_no              text,
  adjust_no           text NOT NULL,
  patient_hn          text,                   -- if patient-linked (e.g. ward issue)
  order_request_id    text,                   -- if linked to an order

  note                text,
  moved_at            timestamptz NOT NULL DEFAULT now(),
  created_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_sml_location   ON stock_movement_log (mongo_location_id, moved_at DESC);
CREATE INDEX idx_sml_item       ON stock_movement_log (mongo_item_id, moved_at DESC);
CREATE INDEX idx_sml_type       ON stock_movement_log (movement_type, moved_at DESC);
CREATE INDEX idx_sml_date       ON stock_movement_log (moved_at DESC);

-- Enable realtime
ALTER PUBLICATION supabase_realtime ADD TABLE stock_movement_log;

COMMENT ON TABLE stock_movement_log IS
  'Realtime stock movement feed — last 7 days kept hot, older rows archived by cron. '
  'Populated by inventory-orchestrator on every STOCK_TRANSACTION_CREATED event.';

3.4 stock_item_catalog

Denormalized product catalog for fast lookups in order dialogs (no MongoDB round-trip).

CREATE TABLE IF NOT EXISTS public.stock_item_catalog (
  id                  uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  mongo_item_id       text NOT NULL UNIQUE,

  -- Product info
  item_code           text NOT NULL,
  item_name           text NOT NULL,
  item_name_local     text,
  item_sku            text,
  barcode             text,
  description         text,

  -- Classification
  item_type_id        text,
  item_type_name      text,
  category            text CHECK (category IN ('drug', 'medical_supply', 'consumable', 'equipment', 'reagent', 'other')),

  -- UOM
  base_uom_id         text,
  base_uom_name       text,

  -- Vendor info (primary vendor)
  primary_vendor_id   text,
  primary_vendor_name text,

  -- Flags
  is_active           boolean NOT NULL DEFAULT true,
  batch_mandatory     boolean DEFAULT false,
  exp_date_mandatory  boolean DEFAULT false,
  is_drug             boolean DEFAULT false,      -- also in product/medication tables

  -- Pricing
  standard_price      numeric(14,4),
  last_purchase_price numeric(14,4),

  -- Aggregated availability (sum across all locations)
  total_on_hand       numeric(12,2) DEFAULT 0,
  total_available     numeric(12,2) DEFAULT 0,
  locations_stocked   integer DEFAULT 0,          -- how many stores carry this

  synced_at           timestamptz NOT NULL DEFAULT now(),
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_sic_name       ON stock_item_catalog USING gin (item_name gin_trgm_ops);
CREATE INDEX idx_sic_code       ON stock_item_catalog (item_code);
CREATE INDEX idx_sic_type       ON stock_item_catalog (item_type_name);
CREATE INDEX idx_sic_category   ON stock_item_catalog (category);
CREATE INDEX idx_sic_active     ON stock_item_catalog (is_active) WHERE is_active = true;

ALTER PUBLICATION supabase_realtime ADD TABLE stock_item_catalog;

COMMENT ON TABLE stock_item_catalog IS
  'Denormalized product catalog for fast search in order dialogs. '
  'Includes aggregated availability across all locations. '
  'Synced by inventory-orchestrator + daily full-sync cron.';

4. Moleculer Event Emission (Backend Changes)

4.1 New events to emit from the foundation service

Every stock transaction already goes through stockTransaction.func.ts which calls methods like doReceive, doIssue, doTransfer, etc. We add event emission after each successful MongoDB write:

// Event types to add to the webhookDispatcher / hospital_events bridge
export enum StockEventType {
  STOCK_TRANSACTION_CREATED = 'STOCK_TRANSACTION_CREATED',
  STOCK_BALANCE_CHANGED     = 'STOCK_BALANCE_CHANGED',
  STOCK_ITEM_UPDATED        = 'STOCK_ITEM_UPDATED',
  STOCK_ITEM_CREATED        = 'STOCK_ITEM_CREATED',
  STOCK_REORDER_TRIGGERED   = 'STOCK_REORDER_TRIGGERED',
  STOCK_BATCH_EXPIRING      = 'STOCK_BATCH_EXPIRING',
}

4.2 Where to add ctx.emit() calls

File After which operation Event
stockTransaction.func.ts → doReceive() After stockBalance update + stockTransaction create STOCK_TRANSACTION_CREATED + STOCK_BALANCE_CHANGED
stockTransaction.func.ts → doIssue() After issue completes STOCK_TRANSACTION_CREATED + STOCK_BALANCE_CHANGED
stockTransaction.func.ts → doTransfer() After both locations updated STOCK_TRANSACTION_CREATED + STOCK_BALANCE_CHANGED (×2 locations)
stockTransaction.func.ts → doAdjustment() After adjustment STOCK_TRANSACTION_CREATED + STOCK_BALANCE_CHANGED
stockTransaction.func.ts → doReturn() After return STOCK_TRANSACTION_CREATED + STOCK_BALANCE_CHANGED
stockTransaction.func.ts → doConsumption() After consumption STOCK_TRANSACTION_CREATED + STOCK_BALANCE_CHANGED
stockTransaction.func.ts → doReserveStock() After reserve STOCK_TRANSACTION_CREATED + STOCK_BALANCE_CHANGED
stockBalance.controller.mixin.ts → alertStockBalanceByStoreIdAndExpDate() After expiry scan STOCK_BATCH_EXPIRING
stockItem.controller.mixin.ts → create/update After item CRUD STOCK_ITEM_CREATED / STOCK_ITEM_UPDATED

4.3 Event payload shape

// hospital_events payload for STOCK_BALANCE_CHANGED
interface StockBalanceChangedPayload {
  event_type: 'STOCK_BALANCE_CHANGED';
  source: 'foundation';
  timestamp: string;          // ISO 8601
  data: {
    stockBalanceId: string;   // MongoDB _id
    itemId: string;
    locationId: string;
    batchId?: string;
    // Denormalized for orchestrator (avoids MongoDB callback)
    itemCode: string;
    itemName: string;
    itemTypeName: string;
    locationCode: string;
    locationName: string;
    storeType: string;
    batchNumber?: string;
    expirationDate?: string;
    // Balance after change
    quantityOnHand: number;
    wac: number;
    receivedQuantity: number;
    // The transaction that caused this change
    transactionType: string;  // StockAdjustmentType value
    transactionQty: number;
    userId: string;
    userDisplayName: string;
  };
}

// hospital_events payload for STOCK_TRANSACTION_CREATED
interface StockTransactionCreatedPayload {
  event_type: 'STOCK_TRANSACTION_CREATED';
  source: 'foundation';
  timestamp: string;
  data: {
    transactionId: string;
    adjustNo: string;
    transactionType: string;    // StockAdjustmentType
    itemId: string;
    itemCode: string;
    itemName: string;
    locationId: string;
    locationName: string;
    toLocationId?: string;
    toLocationName?: string;
    quantity: number;
    beforeQuantity: number;
    stockOnHand: number;
    unitCost: number;
    totalCost: number;
    wacBefore: number;
    wacAfter: number;
    batchId?: string;
    batchNumber?: string;
    userId: string;
    userDisplayName: string;
    departmentName?: string;
    patientHn?: string;
    refNo?: string;
    note?: string;
  };
}

5. Inventory Orchestrator (Supabase Edge Function)

5.1 File structure

infrastructure/medbase/functions/inventory-orchestrator/
├── index.ts                         ← entry point (pg_notify listener)
├── handlers/
│   ├── handleStockBalanceChanged.ts  ← upsert stock_balance_cache + evaluate alerts
│   ├── handleStockTransaction.ts     ← insert stock_movement_log
│   ├── handleStockItemSync.ts        ← upsert stock_item_catalog
│   └── handleExpiryCheck.ts          ← daily cron: scan for approaching expiry
├── evaluators/
│   ├── stockStatusEvaluator.ts       ← compute stock_status from qty vs thresholds
│   └── alertEvaluator.ts             ← decide if alert needed, dedup, auto-resolve
└── _shared/                          ← shared with encounter-orchestrator

5.2 Handler logic

handleStockBalanceChanged

1. Receive STOCK_BALANCE_CHANGED event from hospital_events
2. UPSERT into stock_balance_cache:
   - Set quantity_on_hand, wac, last_movement_at
   - Denormalized item/location info from event payload
3. Evaluate stock_status:
   - If reorder_point set AND qty <= reorder_point → 'low'
   - If min_qty set AND qty <= min_qty → 'critical'
   - If qty = 0 → 'stockout'
   - If max_qty set AND qty > max_qty → 'overstock'
   - If expiration_date set AND days_until_expiry <= expiry_alert_days → 'expiring_soon'
   - If expiration_date < today → 'expired'
   - Else → 'normal'
4. If status changed from 'normal' to something else:
   - INSERT into stock_alerts (dedup by item+location+type)
   - Set severity: stockout/expired → 'critical', low/critical_stock → 'warning', else 'info'
5. If status changed back to 'normal':
   - UPDATE matching stock_alerts to 'auto_resolved'
6. Update stock_item_catalog aggregated fields:
   - SUM(quantity_on_hand) across locations → total_on_hand
   - SUM(available_qty) across locations → total_available
   - COUNT(DISTINCT location) → locations_stocked

handleStockTransaction

1. Receive STOCK_TRANSACTION_CREATED event
2. INSERT into stock_movement_log (idempotent via UNIQUE on mongo_transaction_id)
3. Map StockAdjustmentType → movement_type + direction:
   - good-receive → 'good_receive', 'in'
   - stock-issue → 'stock_issue', 'out'
   - stock-transfer → 'stock_transfer', 'internal'
   - stock-return → 'stock_return', 'in'
   - etc.

6. Frontend Components

6.1 New hooks (in web/packages/inventory-kit/src/hooks/)

Hook Purpose Data source
useStockBalanceRealtime(locationId?, itemTypeFilter?) Live stock balances for a store, auto-updates stock_balance_cache realtime subscription
useStockAlerts(scope: 'all' | locationId) Live alert feed with counts by severity stock_alerts realtime subscription
useStockAvailability(itemId) Get available qty across all locations for one item stock_balance_cache query + subscription
useStockMovementFeed(locationId?) Recent movement activity stream stock_movement_log realtime subscription
useStockItemSearch(query) Fast typeahead for items with availability stock_item_catalog + stock_balance_cache join
useInventoryDashboard(locationId) Morning snapshot: totals, alerts, movements Composite of above hooks

6.2 New UI components

`` — Global alert FAB + drawer

Mounted in App.tsx (alongside existing CdsAlertSurface and AcknowledgementInbox).

  • FAB: Badge with count of active critical/warning alerts
  • Drawer: Grouped by severity → alert type → item
  • Actions per alert: Acknowledge, Snooze (1h/8h/24h/1w), Create PR, View Stock
  • Filter: By location, alert type, severity

`` — Inline in order dialogs

Drop into DialogNewEditOrder.tsx and ProductOrderWorkflow.tsx:

  • Shows: ✅ 450 available / ⚠️ 12 remaining / ❌ Out of stock
  • Colored chip: green (normal), amber (low), red (critical/stockout)
  • Tooltip: breakdown by location (pharmacy A: 200, pharmacy B: 250)
  • Updates in realtime as other departments consume

`` — Morning snapshot (new miniapp)

Route: /inventory/dashboard

  • Summary cards: Total items, total value, alerts by type, items at reorder point
  • Stock status breakdown: Donut chart (normal/low/critical/stockout/expired)
  • Recent movements: Live activity feed (last 24h)
  • Expiring soon table: Items within 90/60/30 days of expiry
  • Low stock table: Items below reorder point with suggested PR quantities
  • All powered by Supabase realtime — opens once in the morning, stays live all day

`` — Actionable alert management

Route: /inventory/alerts

  • Table view of all active alerts with filters
  • Bulk actions: acknowledge all, create PR for selected
  • Resolution workflow: mark as resolved with note
  • History: view resolved/snoozed alerts

6.3 Integration into existing order system

In web/packages/medical-kit/src/order/:

  • DialogNewEditOrder.tsx / DialogNewEditOrder2026.tsx: After item selection, show `` next to the product name
  • ProductOrderWorkflow.tsx: In the item search/selection step, overlay availability data from useStockItemSearch
  • MedicationSupplyPanel.tsx: Sort available items by stock status, show badges
  • Order.tsx: For 'medical-supply' type orders, show stock availability column in the product table

7. Daily Full-Sync Cron

Because the realtime projection is event-driven, we need a daily reconciliation to catch any missed events or drift.

7.1 sync-inventory-cache edge function (or pg_cron)

Schedule: daily at 02:00 local time (via cron_jobs registry)

1. Call foundation API: GET /v2/foundation/stockBalances?populate=item,location,item.itemType,item.baseUom
   (paginated, all locations)
2. For each stockBalance row:
   a. UPSERT into stock_balance_cache
   b. Re-evaluate stock_status
   c. Create/resolve alerts as needed
3. Sync stock_item_catalog from GET /v2/foundation/stockItems
4. Archive stock_movement_log rows older than 7 days
5. Log sync completion to cron_jobs audit

7.2 Registration in cron_jobs table

INSERT INTO cron_jobs (name, schedule, edge_function, description, is_active) VALUES
  ('sync-inventory-cache', '0 2 * * *', 'sync-inventory-cache',
   'Daily full reconciliation of stock_balance_cache from MongoDB', true);

8. Implementation Phases

Phase 1: Supabase tables + migration (Day 1)

Files:

  • infrastructure/medbase/migrations/YYYYMMDD_realtime_inventory_cache.sql

Scope:

  • Create all 4 tables: stock_balance_cache, stock_alerts, stock_movement_log, stock_item_catalog
  • Add to realtime publication
  • RLS policies (same pattern as other read-model tables)
  • Seed reorder rules for common items (from existing StockItem.reorderingMinQty / reorderingMaxQty)
  • Add stock_status_evaluator SQL function for trigger-based status recomputation

Phase 2: Backend event emission (Day 2)

Files:

  • services/foundation/src/api/foundation/modules/stockTransaction/stockTransaction.func.ts — add ctx.emit() after each transaction type
  • services/foundation/src/api/foundation/modules/stockBalance/stockBalance.controller.mixin.ts — add event after balance update
  • services/foundation/src/api/foundation/modules/stockItem/stockItem.controller.mixin.ts — add event after CRUD
  • packages/platform-api-schema/src/foundation/stockEvents/StockEventType.ts — new enum

Approach: Add a private async emitStockEvent(ctx, eventType, payload) helper in stockTransaction.func.ts that:

  1. Calls ctx.emit('stock.event', { eventType, ...payload }) (Moleculer broadcast)
  2. The existing webhookDispatcher pattern (already in administration service) catches this and INSERTs into hospital_events via Supabase client

Phase 3: Inventory orchestrator edge function (Day 3-4)

Files:

  • infrastructure/medbase/functions/inventory-orchestrator/index.ts
  • infrastructure/medbase/functions/inventory-orchestrator/handlers/*.ts
  • infrastructure/medbase/functions/inventory-orchestrator/evaluators/*.ts

Approach: Follow the exact pattern of encounter-orchestrator:

  • Listen on hospital_events inserts where event_type LIKE 'STOCK_%'
  • Route to appropriate handler
  • Each handler does an idempotent UPSERT

Phase 4: Frontend hooks + StockAvailabilityBadge (Day 5-6)

Files:

  • web/packages/inventory-kit/src/hooks/useStockBalanceRealtime.ts
  • web/packages/inventory-kit/src/hooks/useStockAlerts.ts
  • web/packages/inventory-kit/src/hooks/useStockAvailability.ts
  • web/packages/inventory-kit/src/hooks/useStockMovementFeed.ts
  • web/packages/inventory-kit/src/hooks/useStockItemSearch.ts
  • web/packages/inventory-kit/src/components/StockAvailabilityBadge.tsx
  • Integration into DialogNewEditOrder.tsx, ProductOrderWorkflow.tsx

Phase 5: StockAlertSurface + InventoryDashboard (Day 7-8)

Files:

  • web/packages/inventory-kit/src/components/StockAlertSurface.tsx
  • web/packages/inventory-kit/src/components/StockAlertInbox.tsx
  • web/packages/inventory-kit/src/inventory-dashboard/InventoryDashboard.tsx
  • web/packages/inventory-kit/src/inventory-dashboard/components/SummaryCards.tsx
  • web/packages/inventory-kit/src/inventory-dashboard/components/StockStatusChart.tsx
  • web/packages/inventory-kit/src/inventory-dashboard/components/MovementFeed.tsx
  • web/packages/inventory-kit/src/inventory-dashboard/components/ExpiringTable.tsx
  • web/packages/inventory-kit/src/inventory-dashboard/components/LowStockTable.tsx
  • Mount `` in App.tsx

Phase 6: Daily sync cron + seed data (Day 9-10)

Files:

  • infrastructure/medbase/functions/sync-inventory-cache/index.ts
  • infrastructure/medbase/migrations/YYYYMMDD_inventory_cron_seed.sql
  • Bilingual seed data for stock_item_catalog (TH+EN, JA+EN, FIL+EN per market pack)

Phase 7: Polish + existing UI migration (Day 10+)

  • Migrate StockAlertExpire component to use useStockAlerts instead of REST
  • Migrate StockBalance component to use useStockBalanceRealtime instead of REST
  • Add stock availability to pharmacy worklist’s “Request Stock” dialog
  • Add stock availability to OR supply catalog
  • Migrate StockMovement to use useStockMovementFeed

9. File Inventory

New files (28 total)

# Path Purpose
1 infrastructure/medbase/migrations/YYYYMMDD_realtime_inventory_cache.sql Supabase tables
2 infrastructure/medbase/functions/inventory-orchestrator/index.ts Edge fn entry
3 infrastructure/medbase/functions/inventory-orchestrator/handlers/handleStockBalanceChanged.ts Balance upsert
4 infrastructure/medbase/functions/inventory-orchestrator/handlers/handleStockTransaction.ts Movement log
5 infrastructure/medbase/functions/inventory-orchestrator/handlers/handleStockItemSync.ts Catalog sync
6 infrastructure/medbase/functions/inventory-orchestrator/handlers/handleExpiryCheck.ts Expiry scan
7 infrastructure/medbase/functions/inventory-orchestrator/evaluators/stockStatusEvaluator.ts Status calc
8 infrastructure/medbase/functions/inventory-orchestrator/evaluators/alertEvaluator.ts Alert logic
9 infrastructure/medbase/functions/sync-inventory-cache/index.ts Daily full sync
10 packages/platform-api-schema/src/foundation/stockEvents/StockEventType.ts Event enum
11 web/packages/inventory-kit/src/hooks/useStockBalanceRealtime.ts Realtime balance hook
12 web/packages/inventory-kit/src/hooks/useStockAlerts.ts Realtime alerts hook
13 web/packages/inventory-kit/src/hooks/useStockAvailability.ts Per-item availability
14 web/packages/inventory-kit/src/hooks/useStockMovementFeed.ts Movement feed hook
15 web/packages/inventory-kit/src/hooks/useStockItemSearch.ts Catalog search hook
16 web/packages/inventory-kit/src/hooks/useInventoryDashboard.ts Composite dashboard hook
17 web/packages/inventory-kit/src/components/StockAvailabilityBadge.tsx Inline badge
18 web/packages/inventory-kit/src/components/StockAlertSurface.tsx Global FAB + drawer
19 web/packages/inventory-kit/src/components/StockAlertInbox.tsx Alert management UI
20 web/packages/inventory-kit/src/inventory-dashboard/InventoryDashboard.tsx Dashboard page
21 web/packages/inventory-kit/src/inventory-dashboard/components/SummaryCards.tsx Cards
22 web/packages/inventory-kit/src/inventory-dashboard/components/StockStatusChart.tsx Donut chart
23 web/packages/inventory-kit/src/inventory-dashboard/components/MovementFeed.tsx Activity feed
24 web/packages/inventory-kit/src/inventory-dashboard/components/ExpiringTable.tsx Expiry table
25 web/packages/inventory-kit/src/inventory-dashboard/components/LowStockTable.tsx Low stock table
26 web/packages/inventory-kit/src/inventory-dashboard/components/ReorderSuggestions.tsx Auto-PR suggestions
27 infrastructure/medbase/migrations/YYYYMMDD_inventory_cron_seed.sql Cron registration
28 infrastructure/medbase/migrations/YYYYMMDD_inventory_seed_reorder_rules.sql Default reorder rules

Modified files (12 total)

# Path Change
1 services/foundation/.../stockTransaction/stockTransaction.func.ts Add ctx.emit('stock.event') after each transaction type
2 services/foundation/.../stockBalance/stockBalance.controller.mixin.ts Add event emission on balance update
3 services/foundation/.../stockItem/stockItem.controller.mixin.ts Add event emission on item CRUD
4 web/packages/medical-kit/src/order/components/dialogs/dialog-edit-order/DialogNewEditOrder.tsx Add ``
5 web/packages/medical-kit/src/order/components/dialogs/dialog-edit-order/DialogNewEditOrder2026.tsx Add ``
6 web/packages/medical-kit/src/order/components/workflows/ProductOrderWorkflow.tsx Add availability column
7 web/packages/medical-kit/src/order/components/panels/MedicationSupplyPanel.tsx Sort by availability
8 web/src/App.tsx Mount ``
9 web/packages/inventory-kit/src/inventory-system/components/stock-alertexpired/StockAlertExpire.tsx Migrate to realtime hook
10 web/packages/inventory-kit/src/inventory-system/components/stock-balance/StockBalance.tsx Migrate to realtime hook
11 web/packages/inventory-kit/src/inventory-system/components/stock-movement/StockMovement.tsx Migrate to realtime hook
12 web/src/setup/data/inventory/ Register new dashboard route

10. Three Supply Paths — Unified in Central Order Inspector

The codebase has three parallel supply-request paths that were built independently. The inspector now shows all three so the full picture is visible:

Path A: supplyOrderRequest (medication service)
  Doctor → POST /v2/medication/orderRequests { isMedicationSupply: true }
         → child medicationRequest { isMedicationSupply: true }
         → pharmacy/cashier/medical_supply queues
  Status: WIRED — uses existing ORDER_CREATED event + handleOrderCreated orchestrator

Path B: supplyRequest (administration service) — FHIR R4
  FHIR App → POST /v2/administration/supplyRequests
           → SupplyRequest document (intent, category: central/nonstock, deliverFrom/To)
  Status: GAP — no hospital_events emission, no orchestrator handler, no read model

Path C: stockRequest (foundation service) — ward replenishment
  Nurse → POST /v2/foundation/stockRequests
        → StockRequest document (from location → to location, stockItem, qty)
  Status: GAP — no hospital_events emission, no orchestrator handler, no read model

Why the fragmentation exists

  • Path A (medication) is the actual clinical workflow doctors use — it piggybacks on the orderRequest pipeline, just with isMedicationSupply: true
  • Path B (administration) was built for FHIR R4 compliance — SupplyRequest is classified as a logistics/support resource in FHIR, not a clinical order. Has rich FHIR fields but barely wired in the frontend
  • Path C (foundation) is the warehouse/store-keeper path — pure logistics, no patient/encounter context

Unification plan (this system)

  1. Phase 2: Emit SUPPLY_REQUEST_CREATED and STOCK_REQUEST_CREATED hospital_events from administration and foundation services
  2. Phase 3: Add handleSupplyRequestCreated and handleStockRequestCreated to the inventory-orchestrator — project into stock_movement_log and update stock_balance_cache
  3. Phase 7: Wire Path B as the FHIR projection of Path A — when a supply orderRequest is created, auto-create a corresponding FHIR SupplyRequest for interoperability

Central Order Inspector entries

Three new entities added as #16-#18 in web/packages/miniapps/central-order-inspector/catalog.ts:

# Key Service Endpoint Hospital Event Status
16 supplyOrderRequest medication /orderRequests ORDER_CREATED Wired
17 supplyRequest administration /supplyRequests SUPPLY_REQUEST_CREATED Gap
18 stockRequest foundation /stockRequests STOCK_REQUEST_CREATED Gap

11. Invariants

  1. stock_balance_cache is never written by the frontend — only by the inventory-orchestrator edge function and the daily sync cron
  2. Events are idempotent — UPSERT with ON CONFLICT; duplicate hospital_events don’t create duplicate cache rows
  3. Alerts are deduplicated — unique index on (item_id, location_id, alert_type) WHERE status = 'active'; only one active alert per combination
  4. Auto-resolution — when stock level returns to normal, the orchestrator auto-resolves the matching alert
  5. Reorder thresholds are per item+location — stored in stock_balance_cache so each store can have different min/max levels
  6. Daily sync is the safety net — even if events are missed, the nightly cron reconciles everything
  7. Frontend never calls MongoDB for stock queries — after Phase 4, all stock reads go through Supabase
  8. stock_movement_log is append-only — movements are never updated, only inserted; archival cron removes rows older than 7 days
  9. All seed data is bilingual — item names have both item_name (EN) and item_name_local (TH/JA/FIL)

12. Estimated LOC

Phase LOC Files
Phase 1: Migration SQL ~350 1
Phase 2: Backend events ~300 4
Phase 3: Orchestrator ~800 6
Phase 4: Frontend hooks ~600 6
Phase 5: UI components ~1,800 8
Phase 6: Sync cron ~250 2
Phase 7: Migration of existing UI ~400 4
Total ~4,500 28 new + 12 modified
Ask Anything