Making double-booking impossible at the database levelMaking double-booking impossible at the database level

A voice agent can book an appointment while a receptionist is booking the same slot in the dashboard. If your protection is if (slotIsFree) create(), both requests can pass the check at the same moment. The classic race condition.

You can add locks and retries in code, but the sturdier answer is to let the database refuse overlaps.

The constraint

PostgreSQL has exclusion constraints: "no two rows may satisfy this condition together". For appointments, the condition is: same dentist and overlapping time range.

create extension if not exists btree_gist;

alter table appointments
  add constraint no_overlapping_appointments
  exclude using gist (
    dentist_id with =,
    tstzrange(starts_at, ends_at) with &&
  );

&& is the range-overlap operator. Now two concurrent inserts for the same dentist and time cannot both succeed. The second fails with SQLSTATE 23P01 (exclusion violation), no matter how unlucky the timing.

Prisma doesn't model it, and that's fine

Prisma's schema language can't express exclusion constraints, so put the SQL in a migration:

-- prisma/migrations/20260905_no_overlap/migration.sql
create extension if not exists btree_gist;
alter table "Appointment" add constraint no_overlapping_appointments exclude using gist (
  "dentistId" with =,
  tstzrange("startsAt", "endsAt") with &&
);

Prisma keeps working normally; you just handle one new kind of error.

Turn the error into a friendly answer

try {
  return await db.appointment.create({ data });
} catch (err) {
  if (isExclusionViolation(err)) {
    return { ok: false, reason: "That slot was just taken. Want the next one?" };
  }
  throw err;
}

For a voice agent this matters: the tool result becomes a sentence the agent can say out loud, and the caller hears an alternative instead of an error.

Why this beats application checks

• Race-proof: the database serialises conflicting writes for you
• One source of truth: the dashboard, the voice agent and a future mobile app all get the same protection
• Less code: no lock tables, no retry loops

Keep the application-level availability query, because you still want to show free slots. But treat it as a hint. The constraint is the guarantee.