Double-Booking Prevention

Double-booking prevention is a solved problem in The Booking Kit. It operates at two levels to guarantee safety even under high concurrent load.

Database-level: EXCLUDE constraint

The Booking Kit uses PostgreSQL’s btree_gist extension with an EXCLUDE USING gist constraint:

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings
ADD CONSTRAINT bookings_no_overlap
EXCLUDE USING gist (
  provider_id WITH =,
  tstzrange(starts_at, ends_at) WITH &&
)
WHERE (status NOT IN ('cancelled', 'rejected'));

This constraint is enforced at the database level — even if your application has a bug, Postgres will reject overlapping bookings with a constraint violation error.

Application-level: Serializable transactions

For additional safety, booking creation runs in SERIALIZABLE isolation with automatic retry:

import { withSerializableRetry } from "@thebookingkit/server";
import { isSlotAvailable, BookingConflictError } from "@thebookingkit/core";

const booking = await withSerializableRetry(async (tx) => {
  // Check availability within the transaction
  const available = isSlotAvailable(rules, overrides, existingBookings, start, end);
  if (!available.available) throw new BookingConflictError("Slot no longer available");

  // Create the booking
  return tx.insert(bookings).values({
    providerId,
    startsAt: start,
    endsAt: end,
    customerEmail,
    customerName,
    status: "pending",
  }).returning();
});

withSerializableRetry(fn, options?)

Wraps a database operation in a SERIALIZABLE transaction. If PostgreSQL detects a serialization conflict (SQLSTATE 40001), the function automatically retries.

Options:

Option	Type	Default	Description
maxRetries	number	3	Maximum retry attempts
baseDelay	number	50	Base delay in ms before first retry
maxDelay	number	1000	Maximum delay in ms

Retry delays use jittered exponential backoff: min(baseDelay * 2^attempt + jitter, maxDelay).

How it works together

1. Two customers try to book the same 2pm slot simultaneously
2. Both enter SERIALIZABLE transactions
3. Both check availability — both see the slot as open
4. Both attempt to insert a booking
5. PostgreSQL detects the conflict:
   • One transaction commits successfully
   • The other gets a serialization error (40001)
6. withSerializableRetry catches the error and retries
7. On retry, the availability check now sees the first booking and rejects

The EXCLUDE constraint is the final safety net — if somehow both transactions pass the application check, the constraint prevents the second insert.

Complete example

Full booking flow with double-booking prevention:

import { withSerializableRetry } from "@thebookingkit/server";
import { isSlotAvailable, BookingConflictError } from "@thebookingkit/core";
import { eq, inArray } from "drizzle-orm";
import { db } from "@/db";
import { bookings, availabilityRules, availabilityOverrides } from "@thebookingkit/db";

async function createBookingWithPrevention(
  providerId: string,
  slotStart: Date,
  slotEnd: Date,
  customerEmail: string,
  customerName: string
) {
  return await withSerializableRetry(
    async (tx) => {
      // Fetch rules, overrides, and existing bookings within the transaction
      const rules = await tx.query.availabilityRules
        .findMany({ where: eq(availabilityRules.providerId, providerId) });

      const overrides = await tx.query.availabilityOverrides
        .findMany({ where: eq(availabilityOverrides.providerId, providerId) });

      const existingBookings = await tx.query.bookings
        .findMany({
          where: and(
            eq(bookings.providerId, providerId),
            inArray(bookings.status, ["confirmed", "pending"])
          ),
        });

      // Check if the slot is available
      const available = isSlotAvailable(
        rules.map(r => ({
          rrule: r.rrule,
          startTime: r.startTime,
          endTime: r.endTime,
          timezone: r.timezone,
          validFrom: r.validFrom,
          validUntil: r.validUntil,
        })),
        overrides.map(o => ({
          date: o.date,
          startTime: o.startTime,
          endTime: o.endTime,
          isUnavailable: o.isUnavailable,
        })),
        existingBookings,
        slotStart,
        slotEnd,
        15, // bufferBefore (15 min)
        15  // bufferAfter (15 min)
      );

      if (!available.available) {
        throw new BookingConflictError(
          `Slot not available: ${available.reason}`
        );
      }

      // Create the booking (this insert is atomic within the SERIALIZABLE transaction)
      const [newBooking] = await tx
        .insert(bookings)
        .values({
          providerId,
          startsAt: slotStart,
          endsAt: slotEnd,
          customerEmail,
          customerName,
          status: "pending",
        })
        .returning();

      return newBooking;
    },
    {
      maxRetries: 3,
      baseDelay: 50,
      maxDelay: 1000,
    }
  );
}

Error handling

import { BookingConflictError, SerializationRetryExhaustedError } from "@thebookingkit/core";

try {
  const booking = await createBookingWithPrevention(
    providerId,
    slotStart,
    slotEnd,
    "customer@example.com",
    "John Doe"
  );
  console.log("Booking created:", booking.id);
} catch (error) {
  if (error instanceof BookingConflictError) {
    // Slot was taken — show "slot no longer available" to customer
    console.error("Slot is no longer available. Please select another time.");
    return { error: "slot_unavailable" };
  }

  if (error instanceof SerializationRetryExhaustedError) {
    // All retries failed — extremely high contention
    console.error("System is very busy. Please try again in a few seconds.");
    return { error: "try_again_later" };
  }

  throw error;
}

Concurrent booking scenario

When two customers try to book simultaneously:

Time    Customer A                          Customer B
0ms     START SERIALIZABLE TX               START SERIALIZABLE TX
50ms    Check availability ✓                Check availability ✓
100ms   INSERT booking ✓                    INSERT booking
150ms   COMMIT                              (blocked waiting for A's tx)
200ms                                       PostgreSQL detects serialization conflict
250ms                                       withSerializableRetry catches error 40001
300ms                                       Retry: Check availability ✗ (A's booking found)
350ms                                       FAIL with BookingConflictError

At no point are two overlapping bookings created:

1. The SERIALIZABLE isolation ensures one transaction commits first
2. The application-level check on retry sees the first booking and rejects
3. The database EXCLUDE constraint serves as a safety net