No double booking, enforced by Postgres: building a clinic booking system

Two patients click the same 10:30 slot at the same instant. Both requests check whether the slot is free. Both see "free". Both insert. You now have a double booking, and no amount of careful application code fixes it, because check-then-insert has a window in the middle by construction.

I built a clinic appointment system, Idyllic Clinic, as a personal project and it grew well past what I first planned. The headline requirement stayed the same throughout: a booked slot is never offered or booked twice. This post is about how I made the database, not the application, the thing that guarantees it.

Live: clinic.ericgitangu.com. Every decision below has an entry, including its cost, at clinic.ericgitangu.com/decisions.

Why SELECT ... FOR UPDATE is not enough

The usual answer is "lock the row". But slots here are not stored. Doctors have working hours, a slot is 30 minutes, and free slots are computed on request: cut each working window into 30-minute steps, subtract the booked starts, drop anything inside the one-hour lead time. There is no slot row to lock, and you cannot lock a row that does not exist yet.

So uniqueness has to live on the appointment itself:

CREATE UNIQUE INDEX uq_appt_doctor_slot_booked
  ON appointments (doctor_id, starts_at) WHERE status = 'booked';

The service just inserts and maps the unique violation (SQLSTATE 23505, matched by constraint name) to 409 SLOT_TAKEN. There is deliberately no "is it taken?" pre-check: any such check is a race window and adds nothing the index does not already enforce. Because the index is partial, a cancelled row drops out of it, so the slot frees up while the history stays. A second index on (patient_id, starts_at) stops one patient holding two slots at the same instant.

The trade-off, which I state on the decisions page: this is correct only for fixed, aligned 30-minute slots. Variable-length appointments would mean migrating to an EXCLUDE USING gist constraint.

A race test that cannot pass vacuously

A concurrency test that passes because the requests happened to serialize is worse than no test. So the harness is built to fail if it stops being concurrent:

• 25 POSTs for one slot are released at an asyncio.Barrier.
• A test-only pg_sleep holds the transaction open for 200 ms while the test samples pg_locks.
• It repeats over 10 slots with a pool large enough for all 25 requests to be in flight, and asserts the peak number of transactions blocked on the uniqueness check is at least 5.
• Per slot it asserts exactly one 201, twenty-four 409 SLOT_TAKEN, and one booked row.

Then a control: the same kind of barrier against a database with the index dropped, using a naive check-then-insert, asserting that more than one row gets booked. That proves the index is what holds, not luck. Production behaves the same way: 20 simultaneous requests for one slot returned one 201 and nineteen 409s. The README has a copy-paste script to reproduce that against the live API.

Architecture

• API: FastAPI on Cloud Run, the single system of record. Every booking rule, idempotency key and RFC 9457 problem+json error lives here. It is IAM-only; a direct request gets a 403.
• Database: Neon Postgres 17. Migrations run as a clinic_owner role in a Cloud Run Job before each deploy; the service runs as clinic_app with no DDL rights. Migrations are expand-only so the previous revision keeps working.
• Web: two Next.js apps on Vercel (patient booking and an admin dashboard) in one pnpm workspace. They are thin BFF layers that reach the API by exchanging Vercel's OIDC token through workload identity federation for a Cloud Run ID token. No credential in the browser, no stored key.
• Live updates: slot events go through an outbox relay to Ably after each commit, with polling as the floor, so a missed push costs at most a minute of staleness and never a wrong booking. Staff events carry patient names, so they use a small read-only Go service on Cloud Run (SELECT-only database role) instead of a third-party pub/sub.
• Types: OpenAPI 3.1 contract, with TypeScript types, zod schemas and an SDK generated from it. CI fails if generated files drift.

Some other details I care about: time is stored as UTC timestamptz with the clinic zone as data, naive datetimes are rejected with 422 TIMEZONE_REQUIRED, and the clock is injected so tests pin time and pass on any weekday. Booking retries replay through an idempotency key, so a lost response does not tell the real booker they lost the slot.

Stack

Python 3.12, FastAPI, SQLAlchemy (async) with asyncpg, Alembic, Next.js 16, React 19, TypeScript, Better Auth, Go with pgx, Postgres 17 on Neon, Terraform on GCP, Vercel, CircleCI.

Testing, CI and deploy

Backend tests run against real Postgres 17 with no mocked repositories. The e2e-real CI job brings up the real API and database with Docker Compose and runs Playwright scenarios against them, including double booking and cancelling twice. The backend job fails under 85% coverage, with a separate gate holding the pure domain code at 95%. Schemathesis, Lighthouse CI, axe-core, gitleaks, Semgrep, Trivy and OWASP ZAP are wired in as gates.

Delivery is CircleCI with keyless OIDC to workload identity federation: a tagged no-traffic Cloud Run revision, a smoke test, promotion, and rollback automation. I should be upfront that no rollback has actually had to fire yet; the traffic move is covered by CI script tests, not a real incident.

What I did not build

I would rather you hear the gaps from me. The public API is unauthenticated so Swagger's Try it out works, which means anyone holding an appointment UUID can change it through that path; the signed-in web screens close this, the raw API does not. There is no rate limiting inside the API, only in the Vercel proxy. The /book page misses the 2.5 s mobile LCP budget (2.80 s measured locally). The 10x and 100x scaling notes are deductions from configuration, not load tests, and are labelled as such.

Take a look

• Live app: clinic.ericgitangu.com
• Every decision and its cost: clinic.ericgitangu.com/decisions
• Code: github.com/ericgitangu/clinic-booking
• More of my work: developer.ericgitangu.com, github.com/ericgitangu

If you have solved the variable-length slot case with EXCLUDE USING gist in production, I would like to hear how it went.