Booking / Reservations

Design the schema for a booking system (hotel rooms, restaurant tables, or meeting rooms — pick one). Holds expire after a TTL (Time-To-Live) if the user does not check out. Prevent any two confirmed bookings from overlapping for the same resource.

Clarifying questions

Are resources interchangeable within a type (any king room) or specifically chosen?
What's the hold TTL — 10 minutes or 24 hours? Drives the cleanup strategy.
Do we support partial cancellations and modifications, or only full cancel + rebook?
Is overbooking ever allowed (airline-style) or strictly forbidden?
Do we need timezone-aware availability or is everything in venue-local time?

Core requirements

Reserve a resource for a time range with no overlapping confirmed bookings.
Hold a slot during checkout with automatic expiry if payment doesn't complete.
Look up availability for a resource over a date range in O(log n).
Allow safe cancellation that releases the slot atomically.
Support concurrent booking attempts without race-condition double-books.
Audit who booked what, when, and any modifications.

Canonical schema (PostgreSQL)

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE resources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  kind TEXT NOT NULL,
  name TEXT NOT NULL,
  capacity INTEGER NOT NULL DEFAULT 1,
  timezone TEXT NOT NULL DEFAULT 'UTC',
  active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE bookings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_id UUID NOT NULL REFERENCES resources(id),
  user_id UUID NOT NULL,
  during TSTZRANGE NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('held', 'confirmed', 'cancelled', 'completed')),
  hold_expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  cancelled_at TIMESTAMPTZ,
  CONSTRAINT no_overlap_confirmed EXCLUDE USING GIST (
    resource_id WITH =,
    during WITH &&
  ) WHERE (status IN ('held', 'confirmed'))
);

CREATE TABLE booking_events (
  id BIGSERIAL PRIMARY KEY,
  booking_id UUID NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  actor_id UUID,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE blackouts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_id UUID NOT NULL REFERENCES resources(id) ON DELETE CASCADE,
  during TSTZRANGE NOT NULL,
  reason TEXT
);

Key indexes

CREATE INDEX idx_bookings_resource_during ON bookings USING GIST (resource_id, during) WHERE status IN ('held', 'confirmed')

Backs the exclusion constraint and answers 'is this slot free?' in one index lookup.

CREATE INDEX idx_bookings_user_during ON bookings(user_id, lower(during) DESC)

User's 'my upcoming bookings' page paginates by start time descending.

CREATE INDEX idx_bookings_held_expiry ON bookings(hold_expires_at) WHERE status = 'held'

The expiry sweeper scans only live holds — partial index keeps the job O(active holds).

CREATE INDEX idx_blackouts_resource_during ON blackouts USING GIST (resource_id, during)

Availability queries union bookings and blackouts; GIST on the range makes both sides fast.

Key decisions to defend

TSTZRANGE plus EXCLUDE USING GIST is the right primitive — it gives you 'no two overlapping rows' as a database invariant rather than an app-level race.
Holds and confirmed bookings share the bookings table and the same constraint — a held slot is already reserved against the exclusion, so no race window exists between hold-expiry and confirmation.
Hold expiry is a sweeper job hitting a partial index, not a TTL column the app must check on every read — the constraint stays authoritative.
booking_events is append-only audit; the bookings row is mutated for current state. Mixing both lets you reconstruct history without log-scanning for the common case.
Resource capacity > 1 (a 6-top with two 3-person parties) is intentionally not modeled by the exclusion constraint — that case needs slot inventory, and conflating it with single-resource booking breaks the invariant.

Likely follow-ups

· Support overbooking by a percentage — what changes about the exclusion constraint?
· Resources have variable capacity (a 6-top fits one 6 or two 3s) — redesign.
· Add recurring bookings ('every Tuesday 2pm for 6 weeks') — store expanded or pattern?
· Cross-timezone display: store in UTC or local? What about DST transitions?

Dive deeper

The point of this question is whether you reach for the right Postgres primitive. EXCLUDE USING GIST on a tstzrange turns 'no double-booking' from a concurrency bug waiting to happen into a database invariant. Weak candidates write SELECT-then-INSERT and try to defend it with SERIALIZABLE. The followup on capacity > 1 tests whether you know when the exclusion model breaks and you need to switch to inventory accounting.

All database design prompts