A Scheduling Service: Timezones, Concurrency & Double-Booking

The beginner version of this build hides a hard distributed-systems problem behind a friendly "pick a slot" UI. Two people loading the same page see the same open Thursday at 2pm. Both click Book. Naively, you write two rows and now you've double-booked a human being who is about to be embarrassed in front of a client. Scheduling looks like CRUD; it's actually concurrency control wearing a calendar costume.

The mental model: one timezone in the database, never two

Store every instant as UTC. A slot is a half-open interval [start, end) of timestamptz columns. The user's wall-clock time — "9am Eastern" — is a presentation concern that belongs at the edges, not in your tables. Convert on the way in, store UTC, convert back on render. The moment you persist "9:00" without a zone, you've created a bug that surfaces twice a year on DST boundaries and never reproduces on your machine.

In Next.js App Router, do the conversion in a Server Component or Server Action where you control the runtime, not in the browser where the user's locale leaks in:

import { TZDate } from "@date-fns/tz";

// host advertises availability in their zone; we persist UTC
function slotToUtc(localISO: string, hostZone: string): Date {
  return new TZDate(localISO, hostZone).withTimeZone("UTC");
}

The HOW: let the database arbitrate, not your code

The race condition is real and an if (slotIsFree) check in TypeScript will not save you — two requests both read "free" before either writes. The fix is to make double-booking impossible at the storage layer so the second writer is rejected no matter how the requests interleave.

Postgres gives you exactly that with an exclusion constraint over a time range. Add the btree_gist extension in a Supabase migration and let the engine enforce non-overlap per host:

create extension if not exists btree_gist;

alter table bookings add constraint no_overlap
  exclude using gist (
    host_id with =,
    tstzrange(starts_at, ends_at) with &&
  );

Now the second concurrent insert fails with a 23P01 exclusion violation. Your Server Action catches that specific code and returns a clean "that slot was just taken — here are the next openings" instead of a 500:

const { error } = await supabase.from("bookings").insert(row);
if (error?.code === "23P01") {
  return { ok: false, reason: "slot_taken" };
}

This is the whole game: the database is the single source of truth about contention, and it holds a row-level lock for the duration of the transaction. No advisory locks, no Redis mutex, no polling.

Pitfalls that bite in production

• DST and "ambiguous" local times. 1:30am happens twice on fall-back and never on spring-forward. Always round-trip through an IANA zone (America/New_York), never a fixed offset like -05:00.
• Optimistic UI lying to users. If you gray out a slot client-side on click, still treat the server's 23P01 as the truth and re-fetch availability. The UI is a hint; the constraint is the law.
• Vercel function retries. A timed-out invocation can re-run your Server Action. Make booking idempotent with a client-generated idempotency_key (unique constraint) so a retry returns the original booking instead of a duplicate.
• Stale availability. Cache the open-slots query with a short revalidate or tag-based invalidation, but never cache the write path. Read can be slightly stale; the write must be exact.

⌨️ Exercise

Add the no_overlap exclusion constraint to your bookings table, then write a script that fires ten concurrent inserts for the same slot using Promise.all. Confirm exactly one succeeds and nine return 23P01. Then add an idempotency_key unique constraint and prove that replaying one successful request returns the same booking row rather than a second one.