Scheduling Master Plan

The two TOR sections in one diagram

┌──────────────────────────────────────────────────────────────────────┐
│                  PATIENT APPOINTMENT (TOR 2.2.1)                     │
│              (PR #33 — UAT script + coverage audit)                  │
│                                                                      │
│   Slot config · Conflict detection · Booking dialog · Pre-orders     │
│   Bilingual slip + QR · SMS/Email · Cancel reason · No-show report   │
└────────────────┬───────────────────────────────────┬─────────────────┘
                 │                                   │
                 │   shared foundations              │
                 ▼                                   ▼
       ┌─────────────────────┐             ┌─────────────────────┐
       │ DoctorSchedule      │◀────────────│ StaffScheduleMgmt   │
       │ (read-only view     │  *same data │ (Mongo, master)     │
       │  for booking)       │             └─────────┬───────────┘
       └─────────────────────┘                       │
       ┌─────────────────────┐             ┌─────────▼───────────┐
       │ Holiday (per-dept)  │◀────────────│ Holiday (per-dept)  │
       └─────────────────────┘             └─────────────────────┘
       ┌─────────────────────┐             ┌─────────────────────┐
       │ Slot quota (per     │◀────────────│ patientAmount per   │
       │  dept-shift)        │  *same field│  shift item         │
       └─────────────────────┘             └─────────────────────┘
       ┌─────────────────────┐             ┌─────────────────────┐
       │ Cancel reason →     │             │ schedule_audit_log  │
       │  audit trail        │ *same store │  (admin/compliance) │
       └─────────────────────┘             └─────────────────────┘
                 │                                   │
                 ▼                                   ▼
┌──────────────────────────────────────────────────────────────────────┐
│                    STAFF SCHEDULE (TOR 2.2.2)                        │
│              (PR #34 — 7-doc blueprint)                              │
│                                                                      │
│   Shift CRUD · Resource booking · Shift swap · Leave · Reports       │
│   Hour cap · Manpower targets · RBAC · Audit · Conference overlay    │
└──────────────────────────────────────────────────────────────────────┘

Single sources of truth (canonical owners)

Concern	Owner	Touched by
Doctor / staff schedule rows	staffScheduleManagement (Mongo)	TOR 2.2.1.9, .10, .18 / TOR 2.2.2.1, .2
Shift template	workTimeSetting (Mongo)	TOR 2.2.1.5, .6 / TOR 2.2.2.3
Per-slot quota	staffScheduleManagement.item[].patientAmountRemaining	TOR 2.2.1.7, .20 / TOR 2.2.2.4, .10
Holidays (per-dept)	holiday (Mongo, extended with departmentRef)	TOR 2.2.1.24, .26 / TOR 2.2.2.5
Patient appointment record	appointment (Mongo)	TOR 2.2.1.x / TOR 2.2.2.10
Resource (room / device)	schedule_resource_bookings (Supabase, EXCLUDE)	TOR 2.2.2.1, .15 (also TOR 2.2.1.1 “เครื่องมือ”)
Audit log (schedule + cancel reason)	schedule_audit_log (Supabase)	TOR 2.2.1.28 / TOR 2.2.2.6, .7
Calendar projection (FE read)	schedule_calendar_entries (Supabase MV)	TOR 2.2.1.12, .19, .22 / TOR 2.2.2.13, .14

The right column is the key insight. Eight tables / collections, not sixteen. PR #33’s “DoctorSchedule master” is the same row PR #34 calls staffScheduleManagement. PR #33’s “slot quota” is the same column PR #34 calls patientAmountRemaining. Both PRs ask for an audit trail; that’s the same schedule_audit_log table either way.

Principles

1. One write store, one read model. Mongo is canonical; Supabase mirrors via the schedule-calendar-projector edge function (see PR #34 05-ultimate-design.md). The frontend never writes Supabase.
2. DB-level enforcement first, UI affordance second. Concurrency and quota bugs that bypass app-layer checks must still fail at the DB — EXCLUDE USING gist for resources, findOneAndUpdate with predicate for quota.
3. Reuse, don’t fork. PR #34 already specs schedule_audit_log. PR #33’s “cancellation reason history” is a row in that table; do not create a second audit surface.
4. Bilingual + per-region from day one. Slip generators, holiday seed data, and report PDFs ship with th + en at minimum (CLAUDE.md supports ja, fil, th, en). New strings go through the existing i18n pipeline.
5. Policy gates over hard-coded rules. All toggles (quota strict, override allowed, leave blocks appointments, conference auto-attendance) live in the policy_gates table per docs/architecture/policy-gates.md, not inline in service code.

Scope boundaries

In scope:

• Patient appointment booking, edit, cancel, slip print, reminders, reports (TOR 2.2.1.1 – 2.2.1.28).
• Staff shift CRUD, swap, leave, manpower targets, hour caps, resource booking, reports, RBAC, audit (TOR 2.2.2.1 – 2.2.2.18).

Out of scope (deferred to follow-up plans):

• Conference decision capture (conference_decisions Supabase table) — only the calendar overlay of a conference attendance is in scope here.
• Patient-portal self-service booking — staff-mediated only for now.
• HL7v2 / FHIR appointment exchange — covered by docs/CLAUDE.md HL7v2 section, not this plan.
• Japan Kaigo monthly schedule (kaigo_monthly_schedules) — that’s resident-centric, not staff-centric; remains separate.

How to read the rest

• Implementer? Start at 02-already-built.md, then 04-implementation-phases.md.
• Architect? 01-shared-foundations.md first.
• QA / sign-off? 05-uat-coverage-matrix.md maps every TOR clause to its UAT flow and the phase that delivers it.
• DBA? 03-unified-data-model.md.