Slotwell

Booking for a consultation clinic. Patients browse a practitioner's open times, book one, and manage their bookings.

Patients can self-serve; there is no admin surface, no availability-rules editor, and no email yet. What is deferred and why is written down in docs/decisions.md.

---

Getting started

Requires Node 22+ and Docker.

cp .env.example .env
openssl rand -base64 32   # paste into BETTER_AUTH_SECRET in .env
npm install
npm run db:up          # Postgres 17 in Docker, on port 5433
npm run db:migrate
npm run db:seed        # one clinic, three practitioners, two weeks of slots
npm run dev

Then open http://localhost:3000.

npm test               # the suite, against real Postgres
npm run db:studio      # browse the data in a GUI

The browser test needs Chromium, which npm does not install:

npx playwright install chromium
npm run test:e2e       # one browser path, end to end

---

How booking stays correct

Two guards on the slot, both in the database

The application never decides whether a slot is free. It attempts the claim and reads the answer off how many rows changed:

UPDATE slot SET status = 'booked'
WHERE id IN (...) AND status = 'available' AND starts_at > now()

Postgres evaluates that WHERE while holding the row lock, so of any number of simultaneous callers exactly one can find the slot available. Everyone else gets zero rows back and is told the time has gone.

So we eliminate the need for check-then-write anywhere on the booking path, removing the risk of race conditions.

Behind it sits a constraint that holds even when the application is wrong:

ALTER TABLE booking ADD CONSTRAINT booking_no_overlap
EXCLUDE USING gist (
  practitioner_id WITH =,
  tstzrange(starts_at, ends_at) WITH &&
) WHERE (status IN ('pending_payment', 'confirmed'));

A bug, a future code path that forgets the claim, or a person at a psql prompt cannot put two overlapping active bookings on one practitioner.

A third guard, for the other party

The same mechanism keyed on the patient instead:

ALTER TABLE booking ADD CONSTRAINT booking_no_patient_overlap
EXCLUDE USING gist (
  patient_id WITH =,
  tstzrange(starts_at, ends_at) WITH &&
) WHERE (status IN ('pending_payment', 'confirmed'));

A patient is one person, so two appointments at the same time with different practitioners is either a mistake or slot hoarding, and a hoarded slot is capacity denied to someone else that resolves as a no-show.

Back-to-back appointments are unaffected — ranges are half-open, so touching is not overlapping. So are two different patients at the same time, since the constraint is keyed on the patient rather than on the clinic's clock.

Idempotency

The guards stop two people taking one slot. Idempotency stops one person taking it twice.

The confirm screen generates a key when it renders, not when the button is pressed, and holds it in client state. A double tap, a dropped connection, a phone that backgrounded and retried — all carry the key the screen already had. The key is unique-indexed, so the second request finds the first booking and returns it instead of creating another. A fresh visit to the screen is a new intention and gets a new key.

A key that comes back asking for a different slot is not a retry. It is two intentions sharing a key, and it gets an error rather than a stale booking dressed up as success.

States and transitions

confirmed ──▶ completed
    │
    └──▶ cancelled

completed and cancelled are terminal. Every move goes through one function, which does three separate jobs:

• A transition table rejects moves that make no sense — completing a cancelled appointment.
• An actor table rejects moves the caller is the wrong kind of person to make. Attended and missed are clinical observations, so staff record them and patients do not get to assert them about themselves. Cancelling is the mirror: the patient's own decision, which staff may also take for them.
• A compare-and-swap (WHERE status = $from) rejects moves made against a stale view of the world. A tab left open for an hour cannot cancel an appointment the clinic marked complete ten minutes ago.

Policy lives in the same statement. A patient cannot cancel an appointment that has already begun, and that rule is a clause in the WHERE, not a check before it — a policy enforced in application code first is a policy a stale tab can be timed past. Staff are exempt, because someone has to be able to correct the record after a patient rings in: the same transition, a different actor.

Cancelling releases the slot in the same transaction. There is no window where the booking is cancelled but the time is still held.

The status enum declares states that are not reachable yet — pending_payment, expired, no_show. See docs/decisions.md for why.

Under load

1. The invariants live in Postgres. All three constraints and the idempotency index hold no matter how many application instances are running. Nothing depends on an in-process lock, shared memory, or a single writer, so scaling the web tier changes nothing about correctness.
2. Every correctness-critical write is a single statement. The claim, the compare-and-swap, the keyed insert. There is no multi-step read-modify-write anywhere on the booking path.
3. Reads scale separately. Availability is an indexed read of status = 'available', and it is allowed to be slightly stale. The claim is the source of truth, so an out-of-date read can only produce an honest "just taken" at confirm time — never a double booking. That is what would make it safe to cache or serve from a replica without reopening any of this.
4. Losing a slot is a designed outcome, not an error. The confirm screen answers a lost race with the nearest open times, ready to tap.

---

Testing

Tests run against real Postgres. Assertions are against database state, not return values.

The suite covers the races directly: twelve concurrent bookings of one slot produce exactly one booking, a retried request with the same key produces exactly one booking, and one patient racing themselves across eight same-time slots ends up with one appointment.

There is one browser test — browse, book, see it, cancel to sanity check the overall flow. It is slower to run and expensive to keep working, and the correctness signal already lives in the domain tests. What the browser proves is the thing they cannot: that the screens, the session and the domain are wired to each other. Run it before shipping UI changes.

Everything runs locally; there is no pipeline. Before pushing anything that touches the booking path, the sequence that matters is:

npm test && npm run lint && npm run build

---

Layout

Path	What lives there
lib/db/schema/	Tables. The expensive-to-change part.
lib/db/migrations/	Generated SQL, plus hand-written migrations for the exclusion constraints.
lib/booking/	The only place a booking is created or changes status.
lib/queries/	Reads. Availability, bookings, alternatives.
app/**/actions.ts	Server actions: validate, resolve the session, call the domain. Nothing else.
test/	Postgres harness and fixtures.

Authorisation happens at the route boundary; the domain functions take a patient id and an actor and have no idea a session exists. That is what will let a receptionist book on someone's behalf through exactly the same code.

---

Stack

Next.js (App Router) · Postgres · Drizzle · better-auth · Tailwind with Radix Colors · Base UI · Zod · Vitest.

Why each, what was traded away, and what every shipped feature assumes and does not do: docs/decisions.md.