medOS ultra

Booking Slot Realtime System

Migration + service layer for realtime booking slots.

15 min read diagramsUpdated 2026-05-18docs/architecture/booking-slot-realtime-system.md

Status: Migration + service layer shipped; UI wiring pending Depends on: 037_schedule_resource_bookings.sql (deployed), 040_schedule_audit_log.sql (deployed), 20260518h_booking_slots.sql (shipped, needs deploy) Supabase role: Write-truth for slot availability (not a read model — Supabase owns slot state; MongoDB owns the clinical appointment record)

How This Fits Into the Existing System

This is NOT a standalone booking system. It is the realtime availability layer that sits underneath the existing appointment + encounter + queue pipeline:

┌──────────────────────────────────────────────────────────────────────────────┐
│                    UNIFIED PATIENT BOOKING PIPELINE                          │
│                                                                              │
│  ┌─────────────────┐    ┌──────────────────┐    ┌───────────────────────┐   │
│  │ booking_slots    │    │ calendarAppoint- │    │ encounter (MongoDB)   │   │
│  │ (Supabase)       │───►│ ment (MongoDB)   │───►│ + department_queues   │   │
│  │                  │    │                  │    │ (Supabase)            │   │
│  │ Slot availability│    │ Clinical record  │    │ Workflow state machine │   │
│  │ + realtime       │    │ of the appt      │    │ + queue priority      │   │
│  └─────────────────┘    └──────────────────┘    └───────────────────────┘   │
│       ▲                       ▲                       ▲                      │
│       │                       │                       │                      │
│  Generate from            Created on              Created on                 │
│  WorkTimeSetting          slot confirmation       patient check-in           │
│  (shift templates)                                                           │
└──────────────────────────────────────────────────────────────────────────────┘

Existing Systems This Connects To

System Location Relationship
CALENDAR (FullCalendar) miniapps/appointment/ Creates calendarAppointment in MongoDB. booking_slots replaces its freeform time selection with constrained slots.
OPD_APPOINTMENT_BOOKING_HIS miniapps/opd-appointment-booking/ Has hardcoded TIME_SLOTS (08:00–16:30, 30 min) + mock OCCUPIED_SLOTS. booking_slots replaces these with real Supabase queries.
Admission Requests miniapps/admission-request-workflow/ IPD admission uses bed table for bed reservation (same Supabase-write-truth pattern). Appointment slots are for OPD time; bed slots are for IPD space.
Queue Priority Rules 20260412_queue_priority_rules.sql Queue trigger trg_auto_priority_rank checks metadata.is_appointment=true → priority_rank=20 (vs walk-in=50). The flag must flow from booking_slots → encounter metadata → queue metadata.
Encounter Orchestrator medbase/functions/encounter-orchestrator/ On patient check-in, orchestrator creates department_queues row. The is_appointment flag comes from encounter metadata.
WorkTimeSetting services/foundation/workTimeSetting/ Shift templates define doctor availability (day, start/end, patientAmount). generate_slots_batch RPC should consume these.
Slot Templates services/ever-administration/slotTemplate.service.ts Admin CRUD for recurring slot patterns. Exists but not wired to any UI. Should be the admin config surface for slot generation.
schedule_resource_bookings Migration 037 Room/device conflict oracle with EXCLUDE USING gist. When a slot needs a specific room, link via resource_booking_id.

What Each System Owns

Concern Owner Table/Collection
Slot availability (is 09:00-09:30 open?) Supabase booking_slots Realtime broadcast, FOR UPDATE locking
Clinical appointment (who, why, orders) MongoDB calendarAppointment / appointment Backend API, Moleculer events
Encounter lifecycle (arrived → in-progress → finished) MongoDB encounter Backend API, triggers hospital_events
Queue position (waiting → called → active) Supabase department_queues encounter-orchestrator, realtime
Bed reservation (IPD admission) Supabase bed admission_reserve_bed RPC, realtime
Room/device booking (OR, lab room) Supabase schedule_resource_bookings EXCLUDE USING gist constraint

Integration Points (TODO — Phase 2)

  1. Replace OPD mock slots — swap TIME_SLOTS / OCCUPIED_SLOTS in AppointmentBookingHIS.tsx with bookingSlotsApi.listByProvider() + realtime subscription
  2. On confirm_slot → create calendarAppointment — POST to /v2/administration/appointments so the existing encounter pipeline fires
  3. On check-in → set is_appointment flag — ensure encounter metadata includes is_appointment: true so trg_auto_priority_rank assigns priority 20
  4. Generate slots from WorkTimeSetting — when admin publishes a doctor’s schedule, auto-call generate_slots_batch for each shift’s time range
  5. Link to schedule_resource_bookings — if a slot needs a specific room, create the resource booking alongside and store resource_booking_id

Why Supabase Owns Slot State (Not MongoDB)

The standard medOS pattern is: MongoDB = write truth, Supabase = read model cache. Booking slots are the exception because:

  1. Realtime is non-negotiable — when someone reserves a slot, every screen showing that time grid must update within ~200ms. Supabase realtime (postgres_changes) delivers this out of the box. Socket.IO requires a running backend process + explicit emit wiring per event.

  2. Atomic conflict prevention — PostgreSQL’s EXCLUDE USING gist + FOR UPDATE row locking gives database-level double-booking prevention. MongoDB’s compare-and-set pattern is racy under concurrent writes (two users clicking “book” within 50ms of each other).

  3. The data is operational, not clinical — slot availability is a scheduling concern. The clinical record (the appointment document in MongoDB) is created after a slot is successfully reserved, not before.

┌─────────────────────────────────────────────────────────────────────┐
│                     DATA OWNERSHIP SPLIT                            │
│                                                                     │
│  ┌─────────────────────┐         ┌──────────────────────────────┐  │
│  │   Supabase (write)  │         │    MongoDB (write)           │  │
│  │                     │         │                              │  │
│  │  booking_slots      │ ──────► │  appointment (clinical doc)  │  │
│  │  (availability,     │  async  │  (patient, doctor, reason,   │  │
│  │   reservation,      │  sync   │   diagnosis, notes, billing) │  │
│  │   realtime state)   │         │                              │  │
│  └─────────────────────┘         └──────────────────────────────┘  │
│           │                                                         │
│           │  realtime broadcast                                     │
│           ▼                                                         │
│  ┌─────────────────────────────────────────────────────────────┐   │
│  │  All connected UIs (appointment grid, ward board, kiosk)    │   │
│  └─────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘

How the Two Stores Interact

Booking Flow (Happy Path)

1. Staff/Patient opens appointment grid
   └─► Supabase query: SELECT * FROM booking_slots WHERE provider_id = X AND date = Y
   └─► Supabase realtime: subscribe to booking_slots changes for that provider+date

2. User clicks an available slot
   └─► Supabase RPC: reserve_slot_atomic(slot_id, patient_id, ...)
       ├─► PostgreSQL: SELECT ... FOR UPDATE (locks the row)
       ├─► PostgreSQL: CHECK status = 'available' (fail if already taken)
       ├─► PostgreSQL: UPDATE status = 'reserved', patient_id = X
       ├─► PostgreSQL: INSERT into schedule_audit_log
       └─► Returns success + slot details

3. Realtime broadcast fires automatically
   └─► All other screens see slot change from 'available' → 'reserved'
   └─► UI updates grid cell color instantly

4. Async sync to MongoDB (fire-and-forget from frontend, or edge function)
   └─► POST /api/v2/foundation/appointment (creates clinical appointment doc)
   └─► Slot's mongo_appointment_id updated for cross-reference

Cancellation Flow

1. Staff cancels appointment
   └─► Supabase RPC: cancel_slot_atomic(slot_id, reason, actor_id)
       ├─► PostgreSQL: UPDATE status = 'available', clear patient fields
       ├─► PostgreSQL: INSERT into schedule_audit_log (with reason)
       └─► Returns success

2. Realtime broadcast: slot goes 'reserved' → 'available'
   └─► All screens see slot open up

3. Async: PATCH /api/v2/foundation/appointment/:id (cancel the Mongo doc)

Slot Generation

Slots are generated in bulk when a doctor’s schedule is published:

1. Admin publishes Dr. Somchai's schedule for next week
   └─► Backend reads WorkTimeSetting (shift template: 09:00-12:00, 30min slots)
   └─► Backend calculates: 6 slots per morning session × 5 days = 30 slots
   └─► Supabase RPC: generate_slots_batch(provider_id, date_range, template)
       └─► INSERT INTO booking_slots (...) ON CONFLICT DO NOTHING
       └─► Existing slots (manually created, already booked) are preserved

Database Schema

Table: booking_slots

CREATE TABLE IF NOT EXISTS public.booking_slots (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Provider (who is offering this slot)
  provider_id       TEXT NOT NULL,          -- doctor/staff MongoDB _id
  provider_name     TEXT NOT NULL,          -- denormalized: "Dr. Somchai P."
  department_id     TEXT,                   -- department MongoDB _id
  department_name   TEXT,                   -- denormalized: "OPD Cardiology"
  facility_id       TEXT,                   -- clinic/hospital MongoDB _id

  -- Time window
  slot_date         DATE NOT NULL,          -- partition-friendly date column
  starts_at         TIMESTAMPTZ NOT NULL,
  ends_at           TIMESTAMPTZ NOT NULL,
  CHECK (ends_at > starts_at),
  slot_duration_min INTEGER NOT NULL DEFAULT 30,

  -- Slot type
  slot_type         TEXT NOT NULL DEFAULT 'regular'
                    CHECK (slot_type IN (
                      'regular',            -- normal appointment
                      'follow_up',          -- follow-up only
                      'new_patient',        -- new patient only
                      'procedure',          -- procedure slot (longer)
                      'walk_in',            -- walk-in buffer
                      'teleconsult',        -- telemedicine
                      'emergency_buffer'    -- reserved for urgent add-ons
                    )),

  -- Status (the core state machine)
  status            TEXT NOT NULL DEFAULT 'available'
                    CHECK (status IN (
                      'available',          -- open for booking
                      'reserved',           -- held by a patient (soft lock, 10min TTL)
                      'confirmed',          -- patient confirmed (appointment created)
                      'checked_in',         -- patient arrived
                      'in_progress',        -- consultation started
                      'completed',          -- done
                      'no_show',            -- patient didn't show
                      'cancelled',          -- cancelled (slot reopens)
                      'blocked'             -- admin-blocked (lunch, meeting, etc.)
                    )),

  -- Patient (populated when reserved/confirmed)
  patient_id        TEXT,                   -- patient MongoDB _id
  patient_name      TEXT,                   -- denormalized: "นาย สมชาย ใจดี"
  patient_hn        TEXT,                   -- denormalized: "HN-000123"
  patient_phone     TEXT,                   -- for SMS reminder

  -- Appointment cross-reference
  mongo_appointment_id TEXT,                -- appointment._id in MongoDB (set after sync)
  encounter_id      TEXT,                   -- encounter._id (set when checked in)

  -- Booking metadata
  booking_channel   TEXT DEFAULT 'staff'
                    CHECK (booking_channel IN (
                      'staff',              -- booked by staff at counter
                      'self_service',       -- patient self-service (QR kiosk)
                      'online',             -- online booking portal
                      'phone',              -- phone booking
                      'referral',           -- inter-department referral
                      'system'              -- auto-generated (follow-up)
                    )),
  booked_by         TEXT,                   -- staff who made the booking (user _id)
  booked_at         TIMESTAMPTZ,
  reserved_until    TIMESTAMPTZ,            -- soft-lock expiry (for 'reserved' status)
  cancellation_reason TEXT,
  cancelled_by      TEXT,
  cancelled_at      TIMESTAMPTZ,
  notes             TEXT,                   -- booking notes / special instructions
  priority          INTEGER DEFAULT 0,      -- 0=normal, 1=urgent, 2=VIP

  -- Room linkage (optional — uses schedule_resource_bookings for conflict)
  resource_booking_id UUID,                 -- FK to schedule_resource_bookings (room hold)

  -- Timestamps
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

Indexes

-- Primary lookup: "show me all slots for Dr. X on date Y"
CREATE INDEX idx_bs_provider_date ON booking_slots (provider_id, slot_date)
  WHERE status NOT IN ('cancelled');

-- Department view: "show me all available slots in Cardiology today"
CREATE INDEX idx_bs_dept_date_status ON booking_slots (department_id, slot_date, status)
  WHERE status = 'available';

-- Patient lookup: "show me all upcoming appointments for patient X"
CREATE INDEX idx_bs_patient ON booking_slots (patient_id, slot_date)
  WHERE patient_id IS NOT NULL AND status NOT IN ('cancelled', 'no_show');

-- Expiry cleanup: find reserved slots past their TTL
CREATE INDEX idx_bs_reserved_expiry ON booking_slots (reserved_until)
  WHERE status = 'reserved';

-- Cross-reference to MongoDB
CREATE INDEX idx_bs_mongo_appointment ON booking_slots (mongo_appointment_id)
  WHERE mongo_appointment_id IS NOT NULL;

Realtime Publication

ALTER PUBLICATION supabase_realtime ADD TABLE booking_slots;

RLS Policies

ALTER TABLE booking_slots ENABLE ROW LEVEL SECURITY;

-- Read: any authenticated user (needed for calendar grid)
CREATE POLICY bs_read ON booking_slots FOR SELECT USING (true);

-- Write: service_role only (all writes go through RPCs)
CREATE POLICY bs_write ON booking_slots FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

Core RPCs (Atomic Operations)

reserve_slot_atomic — The Heart of the System

CREATE OR REPLACE FUNCTION public.reserve_slot_atomic(
  p_slot_id         UUID,
  p_patient_id      TEXT,
  p_patient_name    TEXT,
  p_patient_hn      TEXT,
  p_patient_phone   TEXT DEFAULT NULL,
  p_booked_by       TEXT DEFAULT NULL,
  p_booking_channel TEXT DEFAULT 'staff',
  p_notes           TEXT DEFAULT NULL,
  p_priority        INTEGER DEFAULT 0,
  p_hold_minutes    INTEGER DEFAULT 10       -- soft-lock TTL
) RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_slot   RECORD;
  v_now    TIMESTAMPTZ := now();
BEGIN
  -- 1. Lock the row (prevents concurrent booking)
  SELECT * INTO v_slot
    FROM booking_slots
    WHERE id = p_slot_id
    FOR UPDATE;

  IF NOT FOUND THEN
    RETURN jsonb_build_object('ok', false, 'error', 'SLOT_NOT_FOUND');
  END IF;

  -- 2. Check availability
  IF v_slot.status = 'reserved' AND v_slot.reserved_until > v_now THEN
    RETURN jsonb_build_object(
      'ok', false,
      'error', 'SLOT_HELD',
      'held_by', v_slot.patient_name,
      'held_until', v_slot.reserved_until
    );
  END IF;

  IF v_slot.status NOT IN ('available', 'reserved') THEN
    -- 'reserved' with expired TTL is treated as available
    IF NOT (v_slot.status = 'reserved' AND v_slot.reserved_until <= v_now) THEN
      RETURN jsonb_build_object(
        'ok', false,
        'error', 'SLOT_UNAVAILABLE',
        'current_status', v_slot.status
      );
    END IF;
  END IF;

  -- 3. Reserve it
  UPDATE booking_slots SET
    status          = 'reserved',
    patient_id      = p_patient_id,
    patient_name    = p_patient_name,
    patient_hn      = p_patient_hn,
    patient_phone   = p_patient_phone,
    booked_by       = p_booked_by,
    booked_at       = v_now,
    booking_channel = p_booking_channel,
    notes           = p_notes,
    priority        = p_priority,
    reserved_until  = v_now + (p_hold_minutes || ' minutes')::interval,
    updated_at      = v_now
  WHERE id = p_slot_id;

  -- 4. Audit
  INSERT INTO schedule_audit_log (
    actor_id, action, subject_kind, subject_id, context
  ) VALUES (
    p_booked_by,
    'slot_reserved',
    'appointment',
    p_slot_id::text,
    jsonb_build_object(
      'patient_id', p_patient_id,
      'patient_hn', p_patient_hn,
      'channel', p_booking_channel,
      'slot_time', v_slot.starts_at
    )
  );

  RETURN jsonb_build_object(
    'ok', true,
    'slot_id', p_slot_id,
    'provider_name', v_slot.provider_name,
    'starts_at', v_slot.starts_at,
    'ends_at', v_slot.ends_at,
    'reserved_until', v_now + (p_hold_minutes || ' minutes')::interval
  );
END;
$$;

confirm_slot — Upgrade Reserved → Confirmed

CREATE OR REPLACE FUNCTION public.confirm_slot(
  p_slot_id              UUID,
  p_mongo_appointment_id TEXT,
  p_actor_id             TEXT DEFAULT NULL
) RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_slot RECORD;
BEGIN
  SELECT * INTO v_slot FROM booking_slots WHERE id = p_slot_id FOR UPDATE;

  IF NOT FOUND THEN
    RETURN jsonb_build_object('ok', false, 'error', 'SLOT_NOT_FOUND');
  END IF;

  IF v_slot.status <> 'reserved' THEN
    RETURN jsonb_build_object('ok', false, 'error', 'NOT_RESERVED', 'current_status', v_slot.status);
  END IF;

  UPDATE booking_slots SET
    status               = 'confirmed',
    mongo_appointment_id = p_mongo_appointment_id,
    reserved_until       = NULL,
    updated_at           = now()
  WHERE id = p_slot_id;

  INSERT INTO schedule_audit_log (actor_id, action, subject_kind, subject_id, context)
  VALUES (p_actor_id, 'slot_confirmed', 'appointment', p_slot_id::text,
    jsonb_build_object('mongo_appointment_id', p_mongo_appointment_id));

  RETURN jsonb_build_object('ok', true, 'slot_id', p_slot_id);
END;
$$;

cancel_slot_atomic — Cancel and Reopen

CREATE OR REPLACE FUNCTION public.cancel_slot_atomic(
  p_slot_id    UUID,
  p_reason     TEXT,
  p_actor_id   TEXT
) RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_slot RECORD;
BEGIN
  SELECT * INTO v_slot FROM booking_slots WHERE id = p_slot_id FOR UPDATE;

  IF NOT FOUND THEN
    RETURN jsonb_build_object('ok', false, 'error', 'SLOT_NOT_FOUND');
  END IF;

  IF v_slot.status IN ('completed', 'cancelled') THEN
    RETURN jsonb_build_object('ok', false, 'error', 'CANNOT_CANCEL', 'current_status', v_slot.status);
  END IF;

  UPDATE booking_slots SET
    status              = 'available',
    patient_id          = NULL,
    patient_name        = NULL,
    patient_hn          = NULL,
    patient_phone       = NULL,
    booked_by           = NULL,
    booked_at           = NULL,
    reserved_until      = NULL,
    mongo_appointment_id = NULL,
    encounter_id        = NULL,
    cancellation_reason = p_reason,
    cancelled_by        = p_actor_id,
    cancelled_at        = now(),
    notes               = NULL,
    priority            = 0,
    updated_at          = now()
  WHERE id = p_slot_id;

  INSERT INTO schedule_audit_log (actor_id, action, subject_kind, subject_id, context)
  VALUES (p_actor_id, 'slot_cancelled', 'appointment', p_slot_id::text,
    jsonb_build_object(
      'previous_patient', v_slot.patient_name,
      'previous_hn', v_slot.patient_hn,
      'reason', p_reason
    ));

  RETURN jsonb_build_object('ok', true, 'slot_id', p_slot_id, 'new_status', 'available');
END;
$$;

generate_slots_batch — Bulk Slot Creation

CREATE OR REPLACE FUNCTION public.generate_slots_batch(
  p_provider_id     TEXT,
  p_provider_name   TEXT,
  p_department_id   TEXT DEFAULT NULL,
  p_department_name TEXT DEFAULT NULL,
  p_facility_id     TEXT DEFAULT NULL,
  p_date            DATE DEFAULT CURRENT_DATE,
  p_start_time      TIME DEFAULT '09:00',
  p_end_time        TIME DEFAULT '12:00',
  p_duration_min    INTEGER DEFAULT 30,
  p_slot_type       TEXT DEFAULT 'regular',
  p_count_override  INTEGER DEFAULT NULL     -- NULL = auto-calculate from time range
) RETURNS INTEGER LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_slot_start  TIMESTAMPTZ;
  v_slot_end    TIMESTAMPTZ;
  v_count       INTEGER := 0;
  v_total       INTEGER;
BEGIN
  v_total := COALESCE(p_count_override,
    EXTRACT(EPOCH FROM (p_end_time - p_start_time))::integer / (p_duration_min * 60));

  v_slot_start := (p_date || ' ' || p_start_time)::timestamptz;

  FOR i IN 1..v_total LOOP
    v_slot_end := v_slot_start + (p_duration_min || ' minutes')::interval;

    INSERT INTO booking_slots (
      provider_id, provider_name, department_id, department_name,
      facility_id, slot_date, starts_at, ends_at, slot_duration_min,
      slot_type, status
    ) VALUES (
      p_provider_id, p_provider_name, p_department_id, p_department_name,
      p_facility_id, p_date, v_slot_start, v_slot_end, p_duration_min,
      p_slot_type, 'available'
    )
    ON CONFLICT DO NOTHING;

    v_slot_start := v_slot_end;
    v_count := v_count + 1;
  END LOOP;

  RETURN v_count;
END;
$$;

expire_stale_reservations — TTL Cleanup (pg_cron)

CREATE OR REPLACE FUNCTION public.expire_stale_reservations()
RETURNS INTEGER LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_count INTEGER;
BEGIN
  UPDATE booking_slots SET
    status          = 'available',
    patient_id      = NULL,
    patient_name    = NULL,
    patient_hn      = NULL,
    patient_phone   = NULL,
    booked_by       = NULL,
    booked_at       = NULL,
    reserved_until  = NULL,
    notes           = NULL,
    priority        = 0,
    updated_at      = now()
  WHERE status = 'reserved'
    AND reserved_until < now();

  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$;

-- Run every 2 minutes via pg_cron
-- SELECT cron.schedule('expire-stale-reservations', '*/2 * * * *',
--   $$SELECT expire_stale_reservations()$$);

Frontend Integration Patterns

Realtime Hook: useBookingSlots

// Pattern from: QueueAndHooksProvider.tsx + useClinicalAlerts.ts

function useBookingSlots(providerId: string, date: string) {
  const [slots, setSlots] = useState<BookingSlot[]>([]);
  const [isSubscribed, setIsSubscribed] = useState(false);

  // Initial fetch
  useEffect(() => {
    supabase
      .from('booking_slots')
      .select('*')
      .eq('provider_id', providerId)
      .eq('slot_date', date)
      .neq('status', 'cancelled')
      .order('starts_at')
      .then(({ data }) => { if (data) setSlots(data); });
  }, [providerId, date]);

  // Realtime subscription
  useEffect(() => {
    const channel = supabase
      .channel(`slots-${providerId}-${date}`)
      .on('postgres_changes', {
        event: '*',
        schema: 'public',
        table: 'booking_slots',
        filter: `provider_id=eq.${providerId}`,
      }, (payload) => {
        setSlots(current => {
          switch (payload.eventType) {
            case 'INSERT':
              return [...current, payload.new as BookingSlot].sort(byStartsAt);
            case 'UPDATE':
              return current.map(s => s.id === payload.new.id ? payload.new as BookingSlot : s);
            case 'DELETE':
              return current.filter(s => s.id !== payload.old.id);
            default:
              return current;
          }
        });
      })
      .subscribe((status) => setIsSubscribed(status === 'SUBSCRIBED'));

    return () => { supabase.removeChannel(channel); };
  }, [providerId, date]);

  return { slots, isSubscribed };
}

Reserve Action

// Pattern from: bedStatus.medbase.ts (RPC calls)

async function reserveSlot(slotId: string, patient: PatientInfo): Promise<ReserveResult> {
  const { data, error } = await supabase.rpc('reserve_slot_atomic', {
    p_slot_id: slotId,
    p_patient_id: patient._id || patient.id,
    p_patient_name: patient.fullName,
    p_patient_hn: patient.hn,
    p_patient_phone: patient.phone1 || patient.phone,
    p_booked_by: currentUserId,
    p_booking_channel: 'staff',
  });

  if (error) throw new Error(error.message);
  return data as ReserveResult;   // { ok: true, slot_id, starts_at, ... }
}

Slot State Machine

                    ┌──────────┐
         generate   │          │  cancel / expire TTL
        ──────────► │ available │ ◄───────────────────┐
                    │          │                       │
                    └────┬─────┘                       │
                         │                             │
                    reserve_slot_atomic           cancel_slot_atomic
                         │                             │
                    ┌────▼─────┐                       │
                    │          │                       │
                    │ reserved │ ──── TTL expires ────►│
                    │ (10 min) │                       │
                    └────┬─────┘                       │
                         │                             │
                    confirm_slot                       │
                         │                             │
                    ┌────▼─────┐                       │
                    │          │───────────────────────►│
                    │confirmed │
                    │          │
                    └────┬─────┘
                         │
                    check_in
                         │
                    ┌────▼──────┐
                    │           │
                    │checked_in │
                    │           │
                    └────┬──────┘
                         │
                    start_consultation
                         │
                    ┌────▼───────┐
                    │            │
                    │in_progress │
                    │            │
                    └────┬───────┘
                         │
                    ┌────┴────┐
                    │         │
               ┌────▼──┐  ┌──▼────┐
               │       │  │       │
               │complete│  │no_show│
               │       │  │       │
               └───────┘  └───────┘

Room Linkage (Optional)

When a slot needs a specific room, create a schedule_resource_bookings row alongside:

1. reserve_slot_atomic(slot) → success
2. INSERT INTO schedule_resource_bookings (...) → success (or 23P01 conflict)
3. If room conflict: cancel_slot_atomic(slot) + return conflict error
4. If success: UPDATE booking_slots SET resource_booking_id = new_id

This keeps slot availability and room availability as independent concerns that compose at booking time.

Sync Back to MongoDB

After confirm_slot, an async process creates the MongoDB appointment document:

confirm_slot(slot_id, mongo_appointment_id = NULL)
  │
  ├─► Frontend: POST /api/v2/foundation/appointment { patient, doctor, time, ... }
  │     └─► Backend creates appointment doc → returns appointment._id
  │
  └─► Frontend: confirm_slot(slot_id, mongo_appointment_id = appointment._id)
      └─► Cross-reference established

If the MongoDB write fails, the slot stays reserved (not confirmed) and the 10-minute TTL will auto-release it. No orphaned bookings.

Migration File

Deploy as: infrastructure/medbase/migrations/20260518h_booking_slots.sql

Implementation Priority (Tomorrow Morning)

  1. Migration — create booking_slots table + 4 RPCs + realtime publication
  2. Service layer — web/src/services/medbase/bookingSlots.medbase.ts
  3. Hook — useBookingSlots(providerId, date) with realtime
  4. UI — slot grid component showing provider’s day view with click-to-book
  5. Seed — generate sample slots for demo doctors
Ask Anything