Court Booking

The problem

Two people open the same free court at the same moment and both press Book. The usual flow checks that the slot is free and then inserts the reservation. Between the check and the insert there is a gap, and two requests can both pass the check. Both reservations get saved.

The requirement is easy to say and hard to guarantee: A court can never have two confirmed reservations that overlap, however many requests arrive at once.

The decision

The application does not check. It inserts, and PostgreSQL decides. An exclusion constraint on the reservation’s time range makes the database refuse any overlap:

ALTER TABLE reservations
  ADD CONSTRAINT no_overlapping_confirmed
  EXCLUDE USING gist (
    resource_id WITH =,
    during WITH &&
  )
  WHERE (status = 'confirmed');

Read the migration (opens in a new tab)

• The range is half-open, [start, end), so 10:00 to 11:00 and 11:00 to 12:00 do not overlap.
• The constraint is partial, so a cancelled reservation does not block its slot.
• The service catches the violation (SQLSTATE 23P01, the PostgreSQL error code for an exclusion violation) and the API answers 409 That slot was just taken. A 409 is the HTTP status for a conflict, and it means only that. Every other refusal has its own status.

I rejected the check-then-insert flow, the application checking first and hoping. The rule would live in application code, and any new code path would have to remember it. In the constraint, it lives next to the data and holds for every writer.

Two smaller decisions follow the same habit of choosing the failure I can live with:

• Polling, not real time. The grid refreshes every few seconds. It is simpler, it survives free-tier hosting, and a lost race is already handled honestly. Server-sent events would be the next step.
• A reminder is marked as sent before it is sent. If the process dies in between, one email is lost, instead of one being sent twice.

The evidence

• A test fires 20 simultaneous requests from 20 different users at one slot, against a real PostgreSQL. Exactly one gets a 201 and nineteen get a 409. Read the test (opens in a new tab).
• An end-to-end test, written with Playwright, which drives real browsers, races two of them for the same cell. One wins, the other is told so and sees the grid refresh. Read the test (opens in a new tab).
• A clean clone of the repository gives 224 passing backend tests at 94% line coverage and 26 passing Vitest tests (run on 2026-10-26). How to run them (opens in a new tab).
• The court grid costs one call per day, GET /availability?date=, which returns every court with all its slots in four queries, however many courts there are. Read the service (opens in a new tab).

The live demo (opens in a new tab) is in Spanish and has 10 courts, which is the demo data and not a limit. The first load can take a few seconds because the free-tier database suspends when idle.

What I would change

• There is no password reset and no email verification.
• A new backend error message shows a generic line in the interface until I add its translation.
• Polling is fine at this size. Server-sent events would make the grid feel live.
• The rate limiter is in memory, so it is only correct for one API process. Running several copies of the API would need a shared store, and that is the first thing I would change.