Designing a clinic booking system that never double-books

01The problem

When I built the booking flow for Sepolia Health, the first bug report from testing was not a crash. It was two confirmation emails for the same doctor, the same day, the same 9:30. Both patients had pressed Book within a few milliseconds of each other, both requests had read the slot as free, and both had written an appointment. Nothing was technically broken; the code was simply written as if requests arrive one at a time.

This post designs that system properly, from scratch, at a scale larger than the one I shipped: a network of clinics where popular doctors release next week's slots at 7:00 every morning. The goal is a single sentence that must hold no matter what: a slot is held or booked by at most one patient at any moment. Everything else (caching, queues, sharding) is negotiable. That sentence is not.

02Requirements

Functional

• Patients browse a doctor's availability by day and pick a time slot.
• Booking is two-step when prepayment is required: hold the slot, pay, then confirm. Otherwise hold and confirm happen in one call.
• Patients cancel or reschedule; a cancelled slot becomes bookable again.
• Receptionists book walk-in patients at the counter through the same API.
• Confirmation and reminders go out by push, email and SMS.

Non-functional

• Correctness: zero double bookings. This is an invariant, not a percentile.
• Latency: availability reads p99 under 200 ms; hold/confirm p99 under 500 ms.
• Availability: 99.95% for the booking path. Reads may degrade (slightly stale) before writes do.
• Fairness: during the 7:00 release, a patient on a slow phone should not lose to a bot firing 50 requests a second.
• Auditability: every state change of a slot is traceable (who, when, from which channel).

03Back-of-the-envelope numbers

Assume a regional chain: 200 clinics, 3,000 doctors, 24 slots per doctor per day. That is 72,000 slots a day. If 80% fill, about 60,000 bookings a day.

Two conclusions fall out of the table. First, the volume is small: one PostgreSQL primary handles a few thousand short write transactions per second, and eight gigabytes a year fits on a laptop. Second, the peak is about 100× the average and it lands on a tiny set of rows. This is a contention problem, not a throughput problem. Most of the design is about what happens when twenty transactions want the same row at the same instant.

04Data model

Slots are materialized: a nightly job expands each doctor's schedule template into concrete rows weeks ahead. It is tempting to compute availability on the fly from templates minus appointments, but then there is no row to lock and no single place where the invariant lives. A row per slot gives the database something concrete to protect.

CREATE TYPE slot_status AS ENUM ('open', 'held', 'booked', 'blocked');

CREATE TABLE slots (
  id              bigint PRIMARY KEY,
  clinic_id       int         NOT NULL,          -- shard key later
  doctor_id       int         NOT NULL,
  starts_at       timestamptz NOT NULL,
  status          slot_status NOT NULL DEFAULT 'open',
  held_by         bigint,                        -- patient id
  hold_expires_at timestamptz,
  version         int         NOT NULL DEFAULT 0,
  UNIQUE (doctor_id, starts_at)
);
CREATE INDEX slots_by_day ON slots (doctor_id, starts_at) WHERE status = 'open';

CREATE TABLE appointments (
  id          bigint PRIMARY KEY,
  slot_id     bigint NOT NULL REFERENCES slots(id),
  patient_id  bigint NOT NULL,
  status      text   NOT NULL,                   -- confirmed | cancelled | no_show
  channel     text   NOT NULL,                   -- app | web | counter
  created_at  timestamptz NOT NULL DEFAULT now()
);
-- last line of defence: one live appointment per slot
CREATE UNIQUE INDEX one_live_appt_per_slot
  ON appointments (slot_id) WHERE status = 'confirmed';

Two details are deliberate. The partial index slots_by_day only covers open slots, so the availability query stays fast even after years of history. And the partial unique index on appointments means that even if every layer above it has a bug, PostgreSQL itself will refuse a second confirmed appointment for the same slot.

05Guarding the invariant

1. Check, then insert (broken)

The version that produced my two emails reads the slot, sees open, and inserts an appointment. Between the read and the write there is a gap, and under load two requests fit inside it.

Wrapping it in a transaction does not help at PostgreSQL's default READ COMMITTED isolation: both transactions still see the committed state before either one writes. SERIALIZABLE would catch it, at the price of retry loops for every serialization failure. There are cheaper tools.

2. Pessimistic lock: SELECT … FOR UPDATE

Lock the row first, then decide. The second transaction blocks on the lock until the first commits, then reads the new status and backs off. Correct and easy to reason about. The cost shows up at 7:00: twenty transactions queue on one row lock, each holding a pooled connection while it waits. With a pool of 50 connections, three hot slots are enough to starve every other request in the service.

3. Conditional update (the one I use)

Fold the check into the write. A single UPDATE … WHERE status = 'open' both tests and claims the slot; the row count tells you whether you won.

UPDATE slots
SET    status          = 'held',
       held_by         = $2,
       hold_expires_at = now() + interval '5 minutes',
       version         = version + 1
WHERE  id = $1
  AND (status = 'open'
       OR (status = 'held' AND hold_expires_at < now()))  -- lazy expiry
RETURNING version;
-- 1 row  -> you hold the slot
-- 0 rows -> someone else does; answer 409 with nearby free slots

Why this is safe under READ COMMITTED: when two UPDATEs hit the same row, the second waits for the first to commit, then re-evaluates its WHERE clause against the new row version. It now sees held, matches nothing, and returns zero rows. The lock is held only for the microseconds of the update itself, not across an application round trip, so the queue at 7:00 drains fast and losers fail immediately instead of waiting their turn to fail.

A useful variant: when the patient asks for any morning slot rather than a specific one, use SELECT … FOR UPDATE SKIP LOCKED LIMIT 1. Each contender skips rows that others are already claiming and grabs the next free one, so twenty requests spread across twenty slots instead of fighting over the first.

4. Unique constraint as the safety net

The partial unique index from the schema is not the primary mechanism, it is the backstop. A receptionist tool written by another team, a data migration, a retried message: any of them can bypass the hold logic. The index turns those bugs into a loud 23505 unique_violation instead of a quiet double booking.

Why not a Redis lock?

SET slot:123 patient NX PX 5000 is fast and popular. As a pre-filter it is fine: it can reject obvious losers before they touch the database. As the source of truth it is not, because the lock and the data live in different systems. A process can pause (GC, a slow network) past the lock's TTL, another process takes the lock, and both write. Fixing that needs fencing tokens checked by the database anyway, at which point the database is doing the real work. I keep one source of truth and let it be the row.

06Hold, then confirm

Payment takes time: a bank app opens, an OTP arrives, the patient hesitates. We cannot keep a database transaction open for that. Instead the slot moves through explicit states, and each transition is one short conditional update.

Lazy expiry instead of a sweeper

The obvious way to release abandoned holds is a cron job that resets expired rows every minute. It works, but it adds a moving part and up to a minute of dead time on the hottest slots. Notice that the hold query above already treats held AND hold_expires_at < now() as free. Expiry is evaluated at the moment someone wants the slot, so an abandoned hold is reclaimable the instant it expires. The availability query uses the same predicate. A lazy sweeper can still run hourly to tidy up rows for reporting, but correctness no longer depends on it.

Always compare against the database's now(), never the application server's clock. Ten API pods with clocks a few hundred milliseconds apart will otherwise disagree about whether a hold has expired.

The payment that arrives late

The nastiest race is between the payment provider and the clock: the patient pays at minute 4:58, the webhook arrives at 5:03. Lazy expiry gives us a grace period for free. Confirm with WHERE id = $1 AND status = 'held' AND held_by = $2, without checking the expiry time. If nobody has taken the slot since, the row is still held by this patient and the confirm succeeds. If someone has, held_by no longer matches, the update returns zero rows, and we trigger an automatic refund and notify the patient. Both outcomes are explicit; neither is a double booking.

async confirm(slotId: bigint, patientId: bigint, paymentId: string) {
  return this.db.tx(async (tx) => {
    const claimed = await tx.query(
      `UPDATE slots SET status = 'booked', version = version + 1
        WHERE id = $1 AND status = 'held' AND held_by = $2`,
      [slotId, patientId],
    );
    if (claimed.rowCount === 0) {
      // lost the slot after paying: refund, never double-book
      await tx.outbox.push("payment.refund_requested", { paymentId, slotId });
      return { ok: false, reason: "SLOT_TAKEN" } as const;
    }
    await tx.appointments.insert({ slotId, patientId, status: "confirmed" });
    await tx.outbox.push("appointment.confirmed", { slotId, patientId });
    return { ok: true } as const;
  });
}

Rescheduling touches two slots: claim the new one, release the old one, in one transaction. Always lock the two rows in ascending id order; otherwise two patients swapping slots with each other can deadlock.

07Idempotency: the double tap

Mobile networks retry. Impatient thumbs tap twice. Payment providers deliver webhooks at least once. Each of these turns one intention into several requests, and without care the second request either fails confusingly ("slot taken", by yourself) or, for confirm, sends a second email.

The client generates an Idempotency-Key (a UUID) per booking attempt and sends it on every retry. The server stores the key with a hash of the request and the response it produced, in the same transaction as the booking. A repeat with the same key and same body gets the stored response; the same key with a different body is a client bug and gets 422. Webhooks use the provider's event id as the key.

CREATE TABLE idempotency_keys (
  key          uuid PRIMARY KEY,
  patient_id   bigint      NOT NULL,
  request_hash text        NOT NULL,
  response     jsonb,                 -- null while the first attempt is in flight
  created_at   timestamptz NOT NULL DEFAULT now()
);
-- first statement of the booking transaction:
INSERT INTO idempotency_keys (key, patient_id, request_hash)
VALUES ($1, $2, $3)
ON CONFLICT (key) DO NOTHING;   -- 0 rows -> replay: return stored response

08Architecture

• Booking API (NestJS, stateless, horizontally scaled). Owns hold, confirm, cancel. Talks to the primary only.
• Availability API. Reads from Redis, falls back to a read replica. Allowed to be a few seconds stale, because the write path re-checks everything.
• PostgreSQL primary behind PgBouncer in transaction mode, so hundreds of API pods share a few dozen real connections.
• Transactional outbox. Events are written in the same transaction as the state change; a relay publishes them to a queue. Notification workers, cache invalidation and the refund flow all consume from there.
• Waiting room at the gateway, switched on only around release times (next section).

The outbox matters more than it looks. The naive version commits the appointment and then calls the email service. If the process dies between the two, the patient is booked but never told. If the email call goes first and the commit fails, the patient is told about a booking that does not exist. Writing the event into the same transaction makes "booked" and "will be notified" a single atomic fact.

09The 7 a.m. spike

Correctness is settled; what breaks first under the release spike is everything around it. In my load tests the order was always the same: the connection pool, then the availability endpoint, then the database CPU. Each has its own fix.

A waiting room, not a wall

From 6:55 the gateway issues each arriving client a signed ticket with a random position (random, not first-come, so that refreshing at 6:59:59.9 is not rewarded). Tickets are admitted at a fixed rate, say 150 per second, tuned from load tests. Admitted clients get a short-lived token that the Booking API checks. The page shows an honest position in line. This converts a cliff into a ramp and, with per-patient limits (one active hold per doctor per day), takes most of the advantage away from bots.

Make the read path boring

Availability for one doctor-day is a tiny object: 24 slot ids and their status. Cache it in Redis under avail:{doctor}:{date}, pre-warm the hot doctors at 6:59, and invalidate from the outbox stream whenever a slot changes. Between invalidations the cache may show a slot that was taken a second ago. That is acceptable precisely because the write path re-checks: the patient gets a 409 with three nearest alternatives, rendered instantly from the same cache. Never let stale reads leak into the write decision.

Short transactions, few connections

A booking transaction should be three statements and no network calls: idempotency insert, conditional update, outbox insert. No payment API, no email, no logging service inside it. With transactions that short, PgBouncer in transaction mode lets 40 real connections serve thousands of concurrent requests, and a statement timeout of one second guarantees that no single slow query can hold the pool hostage.

10Scaling out

A single primary carries this load for years, so the first scaling steps are vertical and read-side: a bigger instance, more replicas, more cache. When the chain grows past one region, the natural shard key is clinic_id. A booking never spans clinics, so every hold, confirm and cancel stays a single-shard transaction and the invariant needs no distributed coordination. Each clinic has a home region that owns its writes; reads can be served anywhere.

The one query that crosses shards is the patient's "my appointments" screen. Rather than fan out to every shard, a consumer of the outbox stream maintains a per-patient read model keyed by patient_id. It lags by a second or two, which is fine for a list, and it can never cause a double booking because nothing writes through it.

11When things fail

12Trade-offs I chose

• Materialized slots over computed availability. More rows and a nightly job, in exchange for a concrete row to protect and an index-only availability query.
• Conditional update over locks or SERIALIZABLE. Losers fail fast with no retry loop. The cost is that every state transition must be expressible as one UPDATE … WHERE, which the state machine makes easy.
• Lazy expiry over a sweeper. No dead time on hot slots and a free grace period for late payments; reporting queries must apply the same predicate.
• Stale reads, strict writes. The read path is cheap and occasionally wrong; the write path is strict and always right. Users see a polite 409 a few times a day at peak.
• A waiting room over raw capacity. Patients wait seconds in an honest queue instead of us provisioning for a two-minute spike that happens once a day.

13Closing

Put the invariant in exactly one place, make that place the database, and let everything else be allowed to be wrong.

Looking back at Sepolia Health, the fix for those two emails was four lines of SQL. The rest of this design is about keeping those four lines honest when the traffic is a hundred times larger and arrives all at once. If I had to keep only three things: a conditional update on a materialized slot, an idempotency key on every write, and an outbox for everything that happens afterwards.

Written by Vũ Duy Anh, backend engineer in Hà Nội. Questions or corrections are welcome by email.