Prevent Double Bookings with PostgreSQL Exclusion Constraints

Define the capacity being scheduled

First decide what a reservation consumes: one staff member, a room, equipment, or several resources together. A candidate is valid only when every required resource is eligible and free for the full interval.

Opening hours are usually a business-level outer boundary. Staff working hours narrow that boundary; service eligibility determines which staff are candidates. Split shifts and overnight windows need explicit normalization rather than assumptions based on one start/end pair.

Build the candidate interval

For each eligible resource, intersect business opening windows with working schedules. Remove breaks, time off, holidays, and other schedule exceptions. For a service duration d and buffers before/after, reserve [start − before, start + d + after) when checking collisions, while showing the customer the service's actual start and end.

Generate start times on a declared increment, such as every 15 minutes, only if that increment matches the business rules. A 15-minute grid does not imply every appointment lasts 15 minutes.

Account for existing reservations

Use half-open intervals [start, end) so one appointment ending at 10:00 can be followed by another beginning at 10:00 when there is no buffer. Overlap exists when candidateStart < bookedEnd and candidateEnd > bookedStart. Decide which statuses consume capacity; a cancelled appointment normally should not, while a pending hold may consume it for a limited time.

• Exclude inactive staff and services.
• Filter by location and all resource requirements.
• Apply buffers consistently to candidate and existing intervals.
• Cap result windows and paginate for large calendars.

Resolve time zones before comparing

Store appointment instants in a consistent absolute representation such as UTC. Store the business's IANA zone for recurring local opening hours and display. Convert local schedule boundaries into instants for the requested date before comparing intervals.

Daylight-saving transitions create nonexistent and repeated local times. Decide whether to skip, shift, or disambiguate them; test both transition directions. A customer's device zone should not silently redefine the business's schedule.

Treat a displayed slot as a short-lived proposal

The availability response can be stale by the time someone clicks. On confirmation, re-check the current rules and reserve the required resource in one conflict-safe database operation. Depending on the model, this may use an exclusion constraint, transaction with locking, serializable operation, or short-lived hold. A read-then-write in separate unprotected requests can double-book under concurrency.

Use a scoped idempotency key for retries, and enforce its uniqueness in the database if simultaneous retries must collapse to one result. A read-before-insert check backed only by a non-unique index is not enough to guarantee that behavior. Tell the customer when a slot was taken and offer refreshed alternatives rather than returning a generic server error.

A compact interval check

Generic TypeScript sketch; it illustrates interval arithmetic only and is not production booking code:

• type Interval = { start: number; end: number };
• const overlaps = (a: Interval, b: Interval) => a.start < b.end && a.end > b.start;
• // Convert local schedule boundaries to instants before calling overlaps.

Test the edges, not only the happy path

Test adjacent appointments, buffers, variable durations, split shifts, time off, a multi-resource booking, cancellations, reschedules, DST gaps/repeats, concurrent confirmation, duplicate retries, and an admin edit racing a customer booking. Log decisions with stable identifiers, not customer details.

Let PostgreSQL reject the losing reservation

This illustrative single-staff pattern uses half-open effective intervals. Adapt table names, resource keys, and the status predicate to your own schema and capacity rules; it is not a drop-in migration.

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE appointments
  ADD CONSTRAINT no_overlapping_active_staff_appointments
  EXCLUDE USING gist (
    staff_id WITH =,
    tstzrange(effective_starts_at, effective_ends_at, '[)') WITH &&
  )
  WHERE (status NOT IN (
    'CANCELLED_BY_CLIENT',
    'CANCELLED_BY_ADMIN',
    'RESCHEDULED'
  ));

• The [) range includes its start and excludes its end, so back-to-back appointments can meet at the boundary. Store buffer-adjusted effective times if setup or cleanup must reserve capacity.
• The partial predicate defines which statuses consume capacity. Review it against the real lifecycle; a cancelled booking should not block time, while a temporary hold may need an expiry rule.
• Catch PostgreSQL SQLSTATE 23P01 at the server boundary, return a conflict response such as HTTP 409, and refresh available slots. Do not rely on the calendar query to prevent the race.
• Use a database UNIQUE constraint on a scoped idempotency key when concurrent retries must return one booking. A plain non-unique index only speeds up lookup.