How I stopped double bookings with one Postgres constraint (no locks, no race conditions)

I am a freelance web developer at the start of my career. To practice real client problems, I built Fade & Co., a booking system for a fictional barbershop. It is a concept project, no real shop uses it. One problem turned out to be more interesting than the UI: how do you make sure two customers can never book the same barber at the same time?

The naive approach and why it breaks

The obvious way is to check availability first and then insert the booking:

1. Query: is this barber free from 10:00 to 10:45?
2. If yes, insert the booking.

This works until two people click "Book" in the same second. Both queries see a free slot, both inserts succeed, and the barber is double booked. The gap between the check and the insert is a race condition. You can shrink it with transactions and locks, but then you are writing and maintaining concurrency code in your app.

Let the database say no

Postgres can enforce "no overlapping ranges for the same barber" itself, with an exclusion constraint. This is the schema from the project:

create extension if not exists btree_gist;

create table bookings (
  id uuid primary key default gen_random_uuid(),
  service_id uuid not null references services(id),
  staff_id uuid not null references staff(id),
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  customer_name text not null,
  customer_email text not null,
  status text not null default 'confirmed'
    check (status in ('confirmed', 'cancelled')),
  check (ends_at > starts_at),
  constraint no_double_booking exclude using gist (
    staff_id with =,
    tstzrange(starts_at, ends_at) with &&
  ) where (status = 'confirmed')
);

Read the constraint like a sentence: no two rows may have the same staff_id and overlapping time ranges (&& is the "overlaps" operator), but only among confirmed bookings. Cancelled bookings free the slot automatically, because they are excluded by the where clause.

btree_gist is needed so that a plain equality check on staff_id can live in the same GiST index as the range check.

The app only handles the error

Now the insert itself is the check. If another booking already holds that time, Postgres rejects the second insert with error code 23P01, and the app turns it into a friendly message:

if (error) {
  if (error.code === "23P01") {
    return {
      ok: false,
      error: "That time was just taken. Please pick another slot.",
    };
  }
  return { ok: false, error: "Something went wrong. Please try again." };
}

Two people clicking at the same moment cannot both win, because the database serializes the conflicting writes. The app still shows only open slots (computed from working hours, service duration and existing bookings), but that is a convenience for the customer, not the safety net. The constraint is the safety net.

Other decisions in the project

• Availability is computed in the shop's time zone, while times are stored as timestamptz.
• Row Level Security is on for every table with no public policies. All data access goes through server-side code, so the public key can never read customer data.
• Input is validated with Zod on the server, and the admin area uses passwordless magic links with an email allow-list.

What I would add next

Email confirmations and reminders, rate limiting on the public booking action, per-barber days off, and online payments. I left them out on purpose to keep the concept focused.

Try it and tell me what is wrong

• Live demo: https://fadeco-booking.vercel.app
• Code: https://github.com/dziminski79-sudo/fadeco-booking

Stack: Next.js (App Router, Server Actions), TypeScript, Tailwind CSS, Supabase (Postgres and Auth), Zod, Vercel.

I would like honest feedback. What feels confusing in the booking flow? What would a real shop owner need that I missed? If you build booking tools for a living, I would also like to hear where this approach breaks down.

I take on small web projects (booking systems, client portals, n8n automations, AI chat assistants). All my portfolio pieces are concept builds, not client work, and the code is public so you can judge it directly: https://damian-dev-portfolio.vercel.app

Comments

[Vlad Tkachenko]

I'd pin the range bounds with a test. tstzrange(starts_at, ends_at) defaults to [), so a 10:00 to 10:45 booking and a 10:45 to 11:30 one don't count as overlapping, which is what a barbershop wants, and the test stops someone "fixing" it to inclusive bounds later.

The where (status = 'confirmed') has a second effect: un-cancelling a booking is an UPDATE, and it can hit 23P01 just like the insert. So a reschedule or un-cancel button in the admin needs the same friendly message.

Where I'd expect this to get harder is a booking that needs two things at once, say a barber and a chair. That's a second exclusion constraint, and both come back as 23P01, so you'd tell them apart by the constraint name in the error message.