Bed Management System

Dual-source bed state: MongoDB owns truth, Supabase serves the read model. This document is the canonical reference for “where does a bed’s X come from.”

Two Sources, One Picture

┌──────────────────────────┐                        ┌──────────────────────────┐
│   MongoDB (write truth)  │ ─── Moleculer event ─▶ │   Supabase (read model)  │
│                          │   ───────────────────▶ │                          │
│  • Bed entity            │     orchestrator       │  • bed table (cache)     │
│  • 16-value status enum  │     consumes events    │  • 6-value status enum   │
│  • ObjectId references   │                        │  • text refs + uuid id   │
│  • All mutations land    │                        │  • Frontend reads here   │
└──────────────────────────┘                        └──────────────────────────┘

Hard rule: All bed mutations go to MongoDB via backend APIs. Supabase is projected from MongoDB by the encounter orchestrator; the frontend reads only.

What Lives Where

MongoDB — Source of Truth

Owner: services/administration (NestJS + Moleculer).

Field	Type	Notes
_id	ObjectId	24-char hex
name / code	string	Display label (e.g. “A-101”)
ward	ObjectId → Ward	Reference
room	ObjectId → Room	Reference
admissionID	ObjectId → Admission	Set when occupied
displayName	string	Patient name snapshot
hn	string	Patient HN snapshot
status	enum (16 values)	See below
bed_type, gender, genderOfBed	string	Constraints

MongoDB status values (16): empty_bed, have_patient, have_reservation, pending, wait_clean, wait_discharge, closed, regis_bed, plus operational substates.

Supabase — Projected Cache

Table	Purpose	Owner
bed	Per-bed status snapshot	Written by orchestrator
bed_status_log	Append-only audit trail	Written by trigger + RPC
ipd_admissions	Denormalized active admissions	Written by orchestrator
ipd_ward_beds	Per-ward summary counts	Auto-recomputed by trigger
ward_bed_availability (view)	Per-ward bed counts + occupancy %	Computed on read

bed table — key columns

Column	Type	Notes
id	uuid	Supabase-native primary key
mongo_ref	text	Links back to MongoDB _id
ward_id	text	MongoDB-style identifier (e.g. 'ward-female-general' or ObjectId)
room_id	text	MongoDB-style identifier
status	text	6-value enum (see below)
encounter_id	text	MongoDB ObjectId of current encounter
admission_id	text	MongoDB ObjectId of admission
hn_snapshot / an_snapshot / patient_name_snapshot	text	Denormalized patient identity
bed_type, gender, age_group	text	Constraints
active	boolean	Soft-delete flag

Supabase status values (6): vacant, reserved, admitted, discharge_pending, cleaning, out_of_service

bed_status_log table — key columns

Column	Type	Notes
id	uuid	Log entry PK
bed_id	uuid	References bed.id (Supabase-native)
ward_id	text	Matches bed.ward_id
from_status / to_status	text	Both 6-value enum
encounter_id	text	MongoDB ObjectId
admission_log_id	text	MongoDB-style identifier
transition	text	reserve / cancel_reservation / admit / transfer_in / transfer_out / discharge_planned / discharge_completed / clean_done / close / reopen
actor_user_id	text	MongoDB-style identifier
occurred_at	timestamptz	Server clock

Append-only — UPDATE/DELETE blocked by trg_bed_status_log_no_update.

Status Mapping (the bridge)

MongoDB              ⇄         Supabase
────────────────────────────────────────────
empty_bed            ⇄         vacant
have_reservation     ⇄         reserved
have_patient         ⇄         admitted
wait_discharge       ⇄         discharge_pending
wait_clean           ⇄         cleaning
closed               ⇄         out_of_service

Mapping happens in two places:

1. MongoDB → Supabase during orchestrator projection (upsert_bed RPC normalizes to the 6-value enum).
2. Supabase → legacy MongoDB shape in useSupabaseBedsForWardView.ts so legacy renderers like WardBedVisualizer (originally built for MongoDB-shaped beds) can render Supabase rows transparently.

Data Flow — End to End

A. Patient gets a bed (admission)

1. Doctor/nurse calls POST /v2/administration/admissions          (REST → MongoDB)
2. Admission service writes bed.status='have_patient'              (MongoDB)
3. Service emits Moleculer event BED_STATUS_CHANGED / ADMISSION_UPDATED
4. Event lands in Supabase `hospital_events` table
5. encounter-orchestrator (Deno edge fn) reads the event
6. Calls upsert_bed() RPC → `bed` table updated to status='admitted'
7. Trigger trg_bed_status_change_log fires → `bed_status_log` row appended
8. Trigger fn_recompute_ipd_ward_beds fires → `ipd_ward_beds` counts refresh
9. Supabase realtime channel pushes change → frontend rerenders

B. Frontend reads (admission request form)

RequestForAdmin
  └─▶ useSupabaseBedsForWardView(wardId, wardName, enabled)
        └─▶ SELECT * FROM bed WHERE ward_id = $1 AND active = true
        └─▶ mapSupabaseBed() → MongoDB-compatible shape
              (status remapped, fields aliased, ward/room re-nested)
  └─▶ bedsForWardView (MongoDB-sourced, if any)
  └─▶ mergedBedsForWardView — combines both sources, dedupes by `_id`
  └─▶ WardBedVisualizer renders BedTile per row

C. Bed transfer (atomic, between any two beds)

BedTransferDialog
  └─▶ bedStatusApi.transferAtomic({ source_bed_id, dest_bed_id, encounter_id, actor_user_id })
        └─▶ RPC transfer_bed_atomic(uuid, uuid, text, text, text, text, text)
              1. SELECT ... FOR UPDATE on both rows (lock)
              2. Validate: source='admitted', dest='vacant', encounter matches
              3. UPDATE both rows atomically (clear source, populate dest)
              4. INSERT 2 rows into bed_status_log:
                   - transfer_out for source
                   - transfer_in  for dest
              5. RETURN summary row to caller

If any step fails, the whole transaction rolls back — no half-transferred state.

D. IPD command center reads bed via journey cache

The IPD command center doesn’t query bed directly. It reads encounter_journey_cache:

encounter_journey_cache.clinical_context.admission_context
  ├─ wardName
  ├─ bedName
  ├─ bedId
  ├─ admitDateTime   ◀── written by orchestrator (NOT admitDate)
  ├─ chiefComplaint
  ├─ isBedReserved
  └─ approvalStatus / admissionStatus

encounter_journey_cache (hoisted columns)
  ├─ ward_id
  ├─ bed_label
  ├─ admit_at
  └─ attending_doctor_name

useCommandCenter.mapCacheRow() reads both nested context and hoisted columns with a fallback chain.

Component Map

Backend

• services/administration/.../bed.service.ts — write API (CRUD + status transitions)
• services/administration/.../admission.service.ts — admission CRUD that drives bed state
• infrastructure/medbase/functions/encounter-orchestrator/index.ts — Deno edge fn projecting MongoDB events to Supabase

Supabase migrations (in dependency order)

• 20260518g_bed_table.sql — bed table (idempotent superset)
• 20260518k_ipd_ward_bed_tables.sql — ipd_admissions, ipd_ward_beds, ward_bed_availability view + seed data
• 043_inpatient_bed_status_log.sql — original bed_status_log (uuid columns — superseded)
• 048_inpatient_ward_bed_availability_view.sql — view
• 052_inpatient_orchestrator_caches.sql — original transfer_bed_atomic (uuid params — superseded)
• 20260518m_fix_bed_transfer_types.sql — fixed types, idempotent (current source of truth for bed_status_log + transfer_bed_atomic)

Frontend

File	Purpose
web/packages/miniapps/admission-request/RequestForAdmin.tsx	Admission request form orchestrator
web/packages/miniapps/admission-request/WardBedVisualizer.tsx	Bed grid renderer (handles both data sources)
web/packages/miniapps/admission-request/useSupabaseBedsForWardView.ts	Supabase bed query + shape mapper
web/src/services/medbase/bedStatus.medbase.ts	RPC client for transfer_bed_atomic + log queries
web/packages/medical-kit/src/bed-transfer/	BedTransferDialog component package
web/packages/medical-kit/src/ipd-system/command-center/useCommandCenter.ts	Command center cache reader (admission context mapping)

Frontend Field-Name Reference

When reading bed/admission data on the frontend, support both MongoDB and Supabase field names. The dual-data-source rule in web/CLAUDE.md applies here:

// Bed identifiers
const bedId = bed._id || bed.id;

// Ward identifiers
const wardId   = bed.ward?._id || bed.ward_id;
const wardName = bed.ward?.name || bed.wardName || bed.ward_name;

// Patient identity on a bed
const hn          = bed.hn || bed.hn_snapshot;
const patientName = bed.displayName || bed.patient_name_snapshot;

// Admission/encounter linkage
const encounterId = bed.encounter || bed.encounter_id;
const admissionId = bed.admissionID || bed.admission_id;

// IPD command center admission context (mapCacheRow)
admitDate:        adm.admitDateTime ?? adm.admitDate ?? adm.admit_date ?? row.admit_at
attendingDoctor:  adm.attendingDoctor ?? enc.attendingDoctorName ?? row.attending_doctor_name

Type-Consistency Rules (post-fix)

After migration 20260518m:

Identifier	Type	Why
bed.id	uuid	Supabase-native, generated by gen_random_uuid()
bed.ward_id	text	Allows MongoDB ObjectIds OR slug-style IDs ('ward-female-general')
bed.encounter_id	text	MongoDB ObjectIds are 24-char hex, not UUID-formatted
bed_status_log.bed_id	uuid	References bed.id
bed_status_log.ward_id / encounter_id / admission_log_id / actor_user_id	text	Must match the source columns
transfer_bed_atomic params: p_source_bed_id, p_dest_bed_id	uuid	Bed PKs
transfer_bed_atomic params: p_encounter_id, p_actor_user_id	text	MongoDB-style identifiers

Anti-pattern (fixed in 20260518m): declaring encounter_id as uuid anywhere — fails at runtime because MongoDB ObjectIds don’t parse as UUIDs.

Quick Reference: “Where does X come from?”

Question	Answer
Who owns the bed’s truth state?	MongoDB (services/administration)
Where does the frontend read beds?	Supabase bed table (via medbaseClient)
How does a frontend know when a bed changes?	Supabase realtime subscription on bed table
Where’s the bed history audit?	Supabase bed_status_log (append-only)
What triggers ward-level count refresh?	Postgres trigger trg_bed_recompute_ward_summary on bed
How does the patient-bed link get queried?	Either bed.encounter_id (latest) or ipd_admissions.bed_id (denormalized active)
What about the IPD command center?	Reads from encounter_journey_cache (not bed directly) — bed info comes via clinical_context.admission_context.bedName/wardName/admitDateTime
Why is bed.id uuid but bed.encounter_id text?	bed.id is Supabase-generated; encounter_id references MongoDB ObjectIds which are 24-char hex
Can the frontend write to bed?	No. All writes go to MongoDB via REST. Supabase RLS allows SELECT for all, ALL only for service_role.

Invariants

1. MongoDB is the only writable source for bed state. Frontend never INSERTs/UPDATEs bed.
2. Supabase rows always lag MongoDB by one event hop (orchestrator-mediated). UIs that need write-then-read consistency should wait for the realtime event, not rely on read-after-write.
3. bed.id is stable across the lifetime of the bed row — frontends can hold it across refetches.
4. bed_status_log is append-only — corrections happen by appending compensating rows, never editing.
5. transfer_bed_atomic is the only multi-row bed mutation that’s safe — use it instead of two single-row updates.
6. Status values differ between MongoDB and Supabase — always map at the boundary, never compare cross-source.
7. ward_id is text everywhere on the Supabase side — even when the value looks like a UUID, the column type is text to allow slug-style identifiers.

Outstanding Manual Deploy Steps

1. Run 20260518m_fix_bed_transfer_types.sql against Supabase (creates bed_status_log if missing, fixes RPC types — idempotent).
2. Verify bed table itself exists (from 20260518g or 20260518k).
3. Confirm ward_bed_availability view returns expected counts: SELECT * FROM ward_bed_availability;
4. Smoke test transfer_bed_atomic with a real encounter ObjectId to confirm the text params accept MongoDB-style values.