# 0011 — Scheduling: local rotas, UTC bookings, and three defences against double-booking

**Status:** Accepted · 2026-08-02

## Context

Two problems dominate appointment software, and both produce failures a clinic
notices in the waiting room rather than in a log.

**Time.** "Dr Ahmed works Tuesdays 09:00–13:00" is a statement about the clock
on the clinic wall. "This patient is booked at 09:00 on 3 August" is a statement
about a moment. Store both the same way and something breaks: store the rota as
UTC and the whole weekly pattern shifts when the clocks change; store the
booking as local time and it becomes ambiguous — or wrong — across a DST
transition.

**Concurrency.** Two receptionists will eventually take the same 09:00 slot in
the same instant. A double-booked doctor with two patients in the waiting room
is a real, visible failure.

## Decision

**Rotas hold local wall-clock times. Appointments hold UTC plus the originating
timezone.** The conversion happens per generated slot, against the actual date,
in `AvailabilityService` and nowhere else — 09:00 is a different instant in
January and in July, so the date is part of the calculation, not just the time.
`Appointment::localStart()` converts back for display using the stored zone, so
a booking made for 09:00 reads as 09:00 for ever.

**Three overlapping defences against double-booking**, in order:

1. **Slot membership.** The requested moment must be one of the slots the rota
   actually generates. This rejects 09:07 in a diary that runs on quarter hours
   — an overlap check alone would accept it.
2. **A row lock over the clinician's day**, taken inside the booking
   transaction. The slot row does not exist yet so there is nothing finer to
   lock; the day is coarse and entirely adequate, held for milliseconds.
3. **A unique index on a derived `slot_key`** (`doctor:timestamp:occurrence`).
   A slot with capacity greater than one accepts several bookings, and the
   occurrence number means the index still caps it exactly.

The third is the one that has to hold. Application logic protects against the
code you wrote; a database constraint protects against the code somebody writes
next year, a second connection, or a refactor that forgets the lock. A unique
violation on insert is caught and re-thrown as the same "slot taken" the
ordinary path produces, so the user sees one message either way.

**Cancelling releases the slot by nulling `slot_key`, not by deleting the row.**
MySQL treats NULLs as distinct in a unique index, which is exactly the behaviour
wanted: the time becomes bookable again while the cancelled booking stays on the
record with its reason.

**Walk-ins carry no slot key at all.** Somebody is standing at the desk;
refusing them because 11:07 is not on the quarter hour would be the software
telling the clinic how to run itself. They get a queue number from the sequence
generator and appear as an extra rather than in a grid position.

## Consequences

**Good.** The failure modes are covered at three levels, and the timezone
behaviour is pinned by a test asserting that a 09:00 Baghdad rota produces a
06:00Z instant. Reschedule cancels and links rather than editing in place, so
"this was moved twice" survives.

**Bad.** Slot generation is computed rather than stored, so a month view for one
clinician generates thirty days of slots. Bounded to 60 days at the endpoint and
adequate at clinic scale; a hospital-sized diary would want a materialised slot
table.

**Bad.** The day-level lock serialises all bookings for one clinician on one
date. At clinic volumes this is invisible. It would not survive a call centre.

**Deliberately not done.** Recurring appointment series, resource/room booking,
and patient-facing online booking. Each is a real requirement; none belongs in
the first version of the diary.