Prevent double bookings in a booking app: one overlap rule and a PostgreSQL constraint

Double bookings damage trust quickly in any app that sells time: lessons, appointments, rooms. I work on the React Native app for Drivio at Lunova, and working on its lesson diary showed me how much thought a clash check needs.

This guide covers how to prevent double bookings in two layers: a clash check in the app, which is for people, and a rule in the database, which is for correctness. Every piece of code here is generic, and the SQL was tested on PostgreSQL 17.

Key takeaways

• Treat bookings as half-open ranges. A booking includes its start time but not its end time.
• Two ranges overlap when each starts before the other ends. That’s the whole rule.
• Test identical and fully contained bookings explicitly. They’re the easiest cases to leave out.
• A warning in the app is for people. A rule in the database is for correctness. You need both.
• If users may book anyway, record that choice in the data, so a hard rule can respect it.

The overlap rule in one line

Most overlap bugs come from listing every way two bookings can collide: starts inside, ends inside, covers completely. It’s easy to miss one. The short version treats bookings as half-open intervals, which overlap only if each starts before the other ends:

type Range = { start: number; end: number } // epoch milliseconds

export function overlaps(a: Range, b: Range): boolean {
  return a.start < b.end && b.start < a.end
}

Because the end is excluded, a lesson from 09:00 to 10:00 and another from 10:00 to 11:00 don’t clash. Back-to-back lessons are normal for instructors, so that matters as much as catching real clashes. Edsger Dijkstra made the case for this convention in “Why numbering should start at zero”: with the lower bound included and the upper bound excluded, adjacent ranges meet where “the upper bound of the one equals the lower bound of the other”.

Decide which bookings to compare

The rule is one line. Deciding what to compare it against takes more thought. In a typical booking app, leave out:

• Bookings that aren’t currently active (cancelled, completed, no-shows) or are already over.
• The booking being edited, so moving a lesson doesn’t clash with itself.
• Whatever your client says may overlap, such as two breaks, or long all-day events.

Travel time is worth adding too. Moving an event’s start earlier by the time it takes to get there makes that time count as busy.

Test the overlap rule with ten cases

The rule is small enough to test thoroughly. Against an existing booking from 09:00 to 11:00, these ten cases cover the main relationships between two ranges:

// range() turns two clock times on the same day into a Range
const existing = range('09:00', '11:00')

test.each([
  ['11:00', '12:00', false], // starts as the other ends
  ['07:00', '09:00', false], // ends as the other starts
  ['12:00', '13:00', false], // clear gap
  ['10:00', '12:00', true],  // overlaps the end
  ['08:00', '10:00', true],  // overlaps the start
  ['09:30', '10:30', true],  // inside
  ['08:00', '12:00', true],  // covers
  ['09:00', '10:00', true],  // same start, ends earlier
  ['10:00', '11:00', true],  // same end, starts later
  ['09:00', '11:00', true],  // identical
])('%s to %s clashes: %s', (start, end, expected) => {
  expect(overlaps(range(start, end), existing)).toBe(expected)
})

The identical and strictly-inside cases are the ones most often missing. The rule handles them today, but they’re exactly what a future refactor could break, so each is worth a line.

Warn, but let people decide: the soft block

Sometimes an instructor wants to double-book on purpose. A soft block allows it without making it easy by accident: the first tap warns and relabels the button “Book anyway”, and the second tap books. There’s a walkthrough in what testing a lesson-booking calendar taught us.

If you allow deliberate clashes, keep them visible afterwards, for example in a list in the diary or a count on a badge, so they don’t get forgotten.

Check on the server, too

The app only knows about the bookings it has loaded. Before saving, ask the server whether the slot is still free. If it isn’t, stop, say so plainly and refresh the diary so the person can choose again. Decide in advance what should happen if the check itself fails, because that’s a product decision, not just an error.

The race condition between checking and saving

A check followed by a save has a weakness. If two requests arrive at almost the same moment, say an instructor on a phone and an admin on a laptop, both can pass the check before either has saved. Each saw a free slot. Together they made a double booking.

Running the check inside the database doesn’t fix this on its own. The PostgreSQL documentation says “Read Committed is the default isolation level”, and that a query in it “sees only data committed before the query began”. Two overlapping transactions can each see no clash, and both insert.

The reliable fix is to make the rule part of the table itself.

Prevent double bookings with a PostgreSQL exclusion constraint

PostgreSQL has a feature built for this. Its range types documentation says “UNIQUE is a natural constraint for scalar values” but “usually unsuitable for range types”, and that “an exclusion constraint is often more appropriate”. With the btree_gist extension, you can limit it to rows for the same instructor:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  instructor_id bigint      NOT NULL,
  starts_at     timestamptz NOT NULL,
  ends_at       timestamptz NOT NULL CHECK (ends_at > starts_at),
  status        text        NOT NULL DEFAULT 'booked',
  allow_overlap boolean     NOT NULL DEFAULT false,
  -- '[)': the start is included, the end is not
  during        tstzrange GENERATED ALWAYS AS (tstzrange(starts_at, ends_at, '[)')) STORED,

  -- No two booked lessons for the same instructor may overlap,
  -- unless someone deliberately chose to allow it.
  CONSTRAINT no_instructor_overlap
    EXCLUDE USING gist (instructor_id WITH =, during WITH &&)
    WHERE (status = 'booked' AND NOT allow_overlap)
);

• && is PostgreSQL’s range overlap operator.
• '[)' includes the start and excludes the end, so back-to-back bookings stay allowed, matching the app’s rule. It’s also what PostgreSQL assumes when the third argument is left out.
• tstzrange holds instants, so the rule works whatever time zone a booking was made from.
• The WHERE clause limits the rule to booked rows, so cancelled bookings free their slot.
• allow_overlap is how “Book anyway” survives. When someone confirms a deliberate clash, set it, and that booking is left out of the rule on purpose.

We tested this on PostgreSQL 17. An overlapping insert for the same instructor fails with error 23P01. Back-to-back bookings, another instructor’s booking, a cancelled booking and an allow_overlap booking all go through. When two transactions tried to book the same slot at once, the second waited for the first to commit and then failed, leaving one booking.

Store the end time rather than a duration if you can. A generated column “can only use immutable functions”, and adding an interval to a timestamptz isn’t one: PostgreSQL rejects it with “generation expression is not immutable”. If you store a duration, keep the range in sync with a trigger instead.

Two practical notes. PostgreSQL won’t add the constraint to an existing table while booked rows already overlap, so find those first, then fix them or mark them with allow_overlap. And restoring a cancelled booking into a slot that has since been taken will fail, so the app needs a message for that too.

Turn error 23P01 into a helpful message

PostgreSQL reports this with the code 23P01, exclusion_violation. Supabase’s REST API is built on PostgREST, which forwards errors coming from PostgreSQL with their code. In a Supabase app, handling it looks like this:

const { error } = await supabase.from('bookings').insert(row)

if (error?.code === '23P01') {
  showNotice('That slot has just been taken. Pick another time.')
  return
}
if (error) throw error

Map database errors to messages in one place, and reuse the message the app already shows when its own check finds a clash. The person sees one consistent behaviour, and the database guarantees it’s true.

A checklist to prevent double bookings

1. Store bookings as instants, and as ranges where the database can use them.
2. Use one overlap function, built on start < otherEnd && otherStart < end.
3. Unit test it with all ten cases, including identical and contained bookings.
4. Decide with the client whether double booking is allowed, and record the choice in the data.
5. Enforce the rule in the database with an exclusion constraint.
6. Map the constraint’s error to the message the app already shows.

Bookings you can trust

Double bookings are far easier to prevent than to clean up. If time zones are your next worry, read why a 09:00 lesson showed at 13:30 in a React Native app. Lunova Labs designs booking flows like this as part of iOS and Android app development, with the rules enforced everywhere they need to hold.