Designing a Reservation and Booking System: Availability Windows, Double-Booking Prevention, and Calendar Synchronization at Scale

Most teams underestimate how hard a reservation system is until they have their first double-booking incident in production. The core of the problem looks simple: store a time slot, mark it as booked, done. The hard part is that bookings are concurrent, time is relative (timezones), external calendars are eventually consistent, and the “simple” schema you picked in week one will not survive six months of feature requests.

This guide covers the full design surface: availability modeling, locking strategies, calendar sync, timezone handling, waitlists, and the scaling patterns that come later.

Availability Window Modeling

The first design decision is how to represent available time. There are three common approaches, each with different tradeoffs.

Slot-based: Pre-generate every possible time slot as a row. Window-based: Store open/closed windows, derive slots at query time. Rule-based: Store recurrence rules (like iCal RRULE), expand them on the fly.

// Slot-based: one row per available slot
interface Slot {
  id: string;
  resourceId: string;
  startsAt: Date;       // stored as UTC
  endsAt: Date;         // stored as UTC
  status: "available" | "held" | "booked" | "blocked";
  version: number;      // for optimistic locking
}

// Window-based: store open windows, generate slots at read time
interface AvailabilityWindow {
  id: string;
  resourceId: string;
  startsAt: Date;
  endsAt: Date;
  slotDurationMinutes: number;
  bufferMinutes: number;       // gap between consecutive bookings
  maxConcurrent: number;       // 1 for exclusive, N for shared resources
  timezone: string;            // "America/New_York"
}

For most SaaS booking systems (appointments, meeting rooms, equipment reservations), the slot-based model is the safer default. It makes locking straightforward and keeps query complexity low. The tradeoff is storage: a calendar with 30-minute slots across business hours generates roughly 16 rows per day per resource. For 1,000 resources over a year, that is about 5.8 million rows, well within PostgreSQL’s comfort zone.

Rule-based availability is powerful for recurring schedules (“every Tuesday, 9am-5pm”) but the expansion logic is a maintenance burden. If you go that route, pre-expand into slots during write operations and store both representations. Never expand rules at query time under load.

Preventing Double-Bookings

This is where most systems have bugs. Two users try to book the same slot concurrently. Both read the slot as “available”. Both write a booking. One of them is wrong.

There are two classic approaches: pessimistic locking (lock the row before reading) and optimistic locking (detect conflicts at write time).

Pessimistic Locking with SELECT FOR UPDATE

PostgreSQL’s SELECT FOR UPDATE blocks other transactions from reading or writing the locked row until the transaction commits.

async function bookSlot(
  slotId: string,
  userId: string,
  db: Pool
): Promise<Booking | null> {
  const client = await db.connect();
  try {
    await client.query("BEGIN");

    // Lock the slot row for the duration of this transaction
    const { rows } = await client.query<Slot>(
      `SELECT * FROM slots WHERE id = $1 AND status = 'available' FOR UPDATE`,
      [slotId]
    );

    if (rows.length === 0) {
      await client.query("ROLLBACK");
      return null; // slot taken or does not exist
    }

    const slot = rows[0];

    await client.query(
      `UPDATE slots SET status = 'booked', version = version + 1 WHERE id = $1`,
      [slot.id]
    );

    const booking = await client.query<Booking>(
      `INSERT INTO bookings (slot_id, user_id, created_at)
       VALUES ($1, $2, NOW()) RETURNING *`,
      [slot.id, userId]
    );

    await client.query("COMMIT");
    return booking.rows[0];
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Pessimistic locking is correct and simple. The cost is contention: under high concurrent load for popular slots, transactions queue up waiting for the lock. For a slot that gets 50 concurrent booking attempts (a flash sale, a popular appointment), this serializes all 50 transactions. Most will get “slot taken” immediately, but the locking overhead is real.

Use pessimistic locking when: booking attempts per slot are low-to-moderate, you cannot tolerate retries on the client, or your infrastructure does not support optimistic locking cleanly.

Optimistic Locking with Version Numbers

Optimistic locking skips the row lock. Instead, it reads a version number, includes it in the update condition, and fails if something else changed the row since the read.

async function bookSlotOptimistic(
  slotId: string,
  userId: string,
  version: number,
  db: Pool
): Promise<Booking | null> {
  const client = await db.connect();
  try {
    await client.query("BEGIN");

    const result = await client.query(
      `UPDATE slots
       SET status = 'booked', version = version + 1
       WHERE id = $1
         AND status = 'available'
         AND version = $2`,
      [slotId, version]
    );

    if (result.rowCount === 0) {
      await client.query("ROLLBACK");
      return null; // conflict: version mismatch or status changed
    }

    const booking = await client.query<Booking>(
      `INSERT INTO bookings (slot_id, user_id, created_at)
       VALUES ($1, $2, NOW()) RETURNING *`,
      [slotId, userId]
    );

    await client.query("COMMIT");
    return booking.rows[0];
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

When the update returns rowCount === 0, the client retries with a fresh read. Optimistic locking is better for read-heavy workloads with low contention. Under high contention (many concurrent attempts for the same slot), the retry rate climbs and you effectively serialize anyway, but through application-level retries instead of database locks.

A practical hybrid: use optimistic locking for the slot status update, but add a unique constraint on (slot_id) in the bookings table as a hard safety net. If optimistic locking has a bug or is bypassed by a code path you missed, the database constraint is the last line of defense.

Short-Lived Holds

For multi-step checkout flows (select slot, fill form, enter payment), you need a “hold” state: the slot is reserved for this user for N minutes while they complete the flow.

interface SlotHold {
  id: string;
  slotId: string;
  userId: string;
  expiresAt: Date;      // NOW() + interval '10 minutes'
  checkoutSessionId: string;
}

Expire holds with a background job or Postgres-native pg_cron. Release expired holds before each availability query. The critical detail: holds must themselves be created with the same locking pattern as bookings. A hold is a tentative booking; it deserves the same concurrency protection.

Timezone Handling

Store everything in UTC. Always. Display in the user’s local timezone at the presentation layer.

import { fromZonedTime, toZonedTime, format } from "date-fns-tz";

function createSlot(
  localStart: string,     // "2026-04-10T09:00:00"
  localEnd: string,       // "2026-04-10T10:00:00"
  timezone: string        // "America/Los_Angeles"
): { startsAt: Date; endsAt: Date } {
  return {
    startsAt: fromZonedTime(localStart, timezone),
    endsAt: fromZonedTime(localEnd, timezone),
  };
}

function displaySlot(
  startsAt: Date,
  userTimezone: string
): string {
  return format(
    toZonedTime(startsAt, userTimezone),
    "EEEE, MMMM d 'at' h:mm a zzz",
    { timeZone: userTimezone }
  );
}

Three timezone bugs you will hit in production:

1. Daylight saving transitions. A “9am every Monday” rule using local time shifts by one hour twice a year. Generate slots with date-fns-tz or luxon, not by adding fixed milliseconds.
2. Resource timezone vs user timezone. A meeting room is in New York. The person booking it is in London. Store the resource’s canonical timezone on the resource record. When displaying confirmation emails, show both.
3. All-day events. These are date strings, not datetimes. 2026-04-10 means different things to a user in Tokyo vs Los Angeles. If you have all-day blocks, store them as date not timestamp.

Calendar Synchronization

External calendar sync is harder than it looks because you are bridging two consistency models: your transactional database and an eventually consistent external service.

iCal (RFC 5545)

iCal is a pull-based format. You expose a URL; external clients poll it. This is simple to implement and covers most use cases (Apple Calendar, Outlook, many booking tools).

import ical from "ical-generator";

async function generateCalendarFeed(
  resourceId: string,
  db: Pool
): Promise<string> {
  const bookings = await db.query<Booking & { slotStartsAt: Date; slotEndsAt: Date }>(
    `SELECT b.*, s.starts_at as "slotStartsAt", s.ends_at as "slotEndsAt"
     FROM bookings b
     JOIN slots s ON s.id = b.slot_id
     WHERE s.resource_id = $1 AND b.status = 'confirmed'
     ORDER BY s.starts_at`,
    [resourceId]
  );

  const cal = ical({ name: "My Booking Calendar" });

  for (const booking of bookings.rows) {
    cal.createEvent({
      id: booking.id,
      start: booking.slotStartsAt,
      end: booking.slotEndsAt,
      summary: `Booking #${booking.id}`,
      description: `Booked by user ${booking.userId}`,
    });
  }

  return cal.toString();
}

The iCal feed URL should include a token scoped to the resource or user, not a public identifier. Rotate the token if access is revoked.

Google Calendar API (Push-Based)

For two-way sync with Google Calendar, the approach is different. You push events to the user’s calendar and listen for changes via webhooks.

import { google } from "googleapis";

async function pushBookingToGoogleCalendar(
  booking: Booking,
  slot: Slot,
  accessToken: string
): Promise<string> {
  const auth = new google.auth.OAuth2();
  auth.setCredentials({ access_token: accessToken });

  const calendar = google.calendar({ version: "v3", auth });

  const event = await calendar.events.insert({
    calendarId: "primary",
    requestBody: {
      summary: "Booking confirmed",
      start: { dateTime: slot.startsAt.toISOString() },
      end: { dateTime: slot.endsAt.toISOString() },
      extendedProperties: {
        private: {
          bookingId: booking.id,
          source: "your-app-name",
        },
      },
    },
  });

  return event.data.id!;
}

Store the googleEventId on your booking record. When a booking is cancelled, call calendar.events.delete. The extendedProperties.private block lets you re-identify your events when processing webhook updates.

The sync consistency problem: Google Calendar webhooks expire every seven days and must be renewed. External events created directly in Google Calendar need to be checked against your availability model before they block a slot on your side. You cannot trust external calendars to be a reliable source of truth for your availability. The correct architecture: your database is authoritative for availability; external calendars are projections. Sync outward (push confirmed bookings to external calendars) and import external busy blocks as read-only blocks in your system.

Locking Strategy Tradeoffs

Strategy	Throughput	Correctness	Retry complexity	Best for
Pessimistic (SELECT FOR UPDATE)	Low under contention	Guaranteed	None (client waits)	Low-volume, critical slots
Optimistic (version check)	High when contention is low	Guaranteed	Client must retry	High-read, low-conflict
Unique constraint only	High	Guaranteed at DB layer	Required, error handling	Simple systems with low volume
Redis SETNX distributed lock	High	Depends on TTL tuning	Moderate	Multi-region, non-RDBMS
Queue-based serialization	Predictable	Guaranteed	Implicit (queue order)	Flash sales, high-demand slots

For most early-stage SaaS booking features, pessimistic locking is the right default. It is correct, easy to audit, and the performance penalty rarely matters until you have thousands of concurrent users.

Waitlists

A waitlist is a queue of unfulfilled booking intents. The design questions: ordered or random promotion? Automatic or manual? How long does a user have to confirm before losing their spot?

interface WaitlistEntry {
  id: string;
  slotId: string;
  userId: string;
  position: number;
  createdAt: Date;
  notifiedAt: Date | null;
  expiresAt: Date | null;   // set when notified, e.g. 30 minutes to confirm
  status: "waiting" | "notified" | "confirmed" | "expired";
}

When a slot opens (cancellation or expired hold), promote the first waitlist entry: set notifiedAt, set expiresAt = NOW() + 30 minutes, send a notification. If the user does not confirm within the window, expire that entry and promote the next. The promotion loop runs in a background job, not synchronously on the cancellation path.

One trap: if you allow waitlist entries for slots that are not yet full (for resources with maxConcurrent > 1), you need to define “full” precisely and recheck it during promotion.

Overbooking Strategies

For some resource types (airline seats, hotel rooms), overbooking is intentional: you sell more slots than you have capacity for, betting on no-shows. This is a policy decision, not a technical one, but it has technical consequences.

The simplest model is a capacity multiplier:

interface Resource {
  id: string;
  capacity: number;                // physical capacity
  overbookingMultiplier: number;   // e.g. 1.1 = 10% overbook
  // effective booking capacity = floor(capacity * overbookingMultiplier)
}

When effective bookings exceed physical capacity, you need a denying/compensation flow ready. If you implement overbooking, make the policy explicit in the booking confirmation and build the compensation path (refund, rebooking offer) before you go live, not after the first incident.

Production Scaling Patterns

Database indexes. The most common query pattern is “what slots are available for resource X in time range Y”. Index on (resource_id, starts_at, status). For multi-resource queries, consider a partial index: WHERE status = 'available'.

Read replicas for availability queries. Availability reads can tolerate slight staleness (a slot appearing available for 200ms after it was booked is acceptable; the booking attempt will fail gracefully). Route availability queries to a read replica. Route booking writes to the primary.

Horizontal partitioning. Partition the slots table by resource or by month. Resources are a natural shard key if they are tenant-scoped (each tenant’s resources on the same shard). Monthly partitioning helps with archiving old data.

Caching availability with short TTLs. Cache the set of available slot IDs per resource with a 5-10 second TTL. This absorbs read traffic for popular resources. On booking, invalidate the cache for that resource. The cache can serve slightly stale data; the database is always the authoritative source at write time.

Idempotency on booking endpoints. Clients retry on network errors. Without idempotency, a retry creates a duplicate booking. Accept an Idempotency-Key header and store it with the booking record. Before processing, check if that key already has a completed result and return it.

async function createBookingIdempotent(
  idempotencyKey: string,
  request: BookingRequest,
  db: Pool
): Promise<Booking> {
  // Check for existing result
  const existing = await db.query<Booking>(
    `SELECT * FROM bookings WHERE idempotency_key = $1`,
    [idempotencyKey]
  );

  if (existing.rows.length > 0) {
    return existing.rows[0]; // return the original result
  }

  // Proceed with booking, store idempotency_key in the INSERT
  return bookSlot(request.slotId, request.userId, idempotencyKey, db);
}

Key Decisions at Each Scale

Scale	Bottleneck	Recommended change
0-10K bookings/month	Correctness	Pessimistic locking, single primary DB
10K-500K bookings/month	Read throughput	Read replicas, short-TTL availability cache
500K-5M bookings/month	Write throughput	Optimistic locking, partitioned tables
5M+ bookings/month	Cross-region latency	Distributed locking (Redis), regional shards

Closing Thought

The correctness problems in booking systems are not subtle: you either prevent double-bookings or you do not. The complexity is in the edges: timezone transitions, expired holds that were not cleaned up, external calendar events that arrived after your availability query, a waitlist promotion that raced with a new booking. Get the locking and the UTC storage right first. Build calendar sync as a projection over your authoritative database, not as a source of truth. Add waitlists and overbooking only when the business needs them, with the compensation flows already built.

Most booking systems that fail in production were not wrong about the core algorithm. They skipped the edge cases because they seemed unlikely. They are not.