Magnus Kaur, full-stack developer in Helsinki

-- Invariant 1: two active bookings for the same resource may never overlap.
--
-- An exclusion constraint is checked by the index itself while the row is being
-- written, under the same locks that protect a unique index. Two concurrent
-- transactions inserting the same slot cannot both pass: the second one blocks
-- until the first commits, then fails with SQLSTATE 23P01.
ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (
    resource_id WITH =,
    tstzrange(start_at, end_at, '[)') WITH &&
  )
  WHERE (cancelled_at IS NULL);

/**
 * The shape of this function is the point of the project. There is no
 * "is the slot free?" query before the insert, because any answer it gave would
 * already be stale by the time the insert ran: another transaction can commit
 * in the gap. Instead the insert is attempted unconditionally and the database
 * is allowed to refuse it.
 */
export async function createBooking(
  db: Database,
  rawInput: unknown,
): Promise<CreateBookingResult> {
  const parsed = createBookingSchema.safeParse(rawInput);
  // … invalid input is rejected here

  const input = parsed.data;

  let created: Booking | null;
  try {
    created = await insertBooking(db, input);
  } catch (error) {
    throw await translateInsertError(db, error, input);
  }

  if (created) {
    return { booking: created, replayed: false };
  }
  // … otherwise this was a retry: return the original booking

/**
 * The conflict target is named explicitly. A bare `ON CONFLICT DO NOTHING`
 * would also swallow the exclusion constraint, turning a genuine double-booking
 * into a silent no-op — exactly the failure this project is about. Naming
 * `(idempotency_key)` keeps overlap violations raising SQLSTATE 23P01.
 */
export async function insertBooking(
  db: SqlExecutor,
  values: NewBooking,
): Promise<Booking | null> {
  const { rows } = await db.query<BookingRow>(
    `
    WITH inserted AS (
      INSERT INTO bookings (resource_id, start_at, end_at, customer_name, idempotency_key)
      VALUES ($1, $2, $3, $4, $5)
      ON CONFLICT (idempotency_key) DO NOTHING
      RETURNING *
    )
    SELECT ${BOOKING_COLUMNS}
    FROM inserted b
    JOIN resources r ON r.id = b.resource_id
    `,
    // … the five values
  );

  return rows.length > 0 ? toBooking(rows[0]) : null;
}

/**
 * These tests are about the interleaving that breaks naive booking code:
 *
 *   request A: is the slot free?  -> yes
 *   request B: is the slot free?  -> yes      (A has not written yet)
 *   request A: insert             -> ok
 *   request B: insert             -> ???      (A's answer is now stale)
 */
it("proves the constraint is what refuses the second write", async () => {
  // Same interleaving with the exclusion constraint removed. If the
  // application code were doing the work, this would still be rejected.
  await db.exec("ALTER TABLE bookings DROP CONSTRAINT bookings_no_overlap");

  await createBooking(db, bookingInput({ ...SLOT, customerName: "Alice" }));
  await createBooking(db, bookingInput({ ...SLOT, customerName: "Bob" }));

  expect(await countActiveBookings(db)).toBe(2);
});