Trishnangshu Goswami
Back to writing

Doctor Scheduling with Recurring Rules and Timezone Traps

September 1, 2025·Trishnangshu Goswami
BackendPostgreSQLSystem Design

A doctor sets their availability as "Monday and Wednesday, 10 AM to 2 PM IST." The system needs to generate individual 45-minute slots, handle exceptions (vacations, one-off changes), let patients book those slots with payment locking, prevent double-booking under concurrent load, and store everything in UTC while displaying in IST.

This is the scheduling engine behind our telemedicine platform. Three database tables, RRule-based patterns, lazy slot generation, and FOR UPDATE SKIP LOCKED for race-safe booking.

The Three-Table Model

Table 1: Availability Patterns

Recurring schedules are stored as RRule components:

CREATE TABLE doctor_availability_patterns (
  id UUID PRIMARY KEY,
  doctor_id UUID REFERENCES users(id),
  freq VARCHAR(10),        -- 'WEEKLY' or 'DAILY'
  interval INTEGER,        -- usually 1
  by_weekday TEXT[],       -- ['MO', 'WE', 'FR']
  start_date DATE,
  start_time TIME,         -- '10:00:00'
  end_time TIME,           -- '14:00:00'
);

A doctor who works Monday/Wednesday/Friday 10 AM–2 PM has one row. The RRule fields (freq, by_weekday, interval) define the recurrence, and start_time/end_time define the daily window. Session duration (45 min default) and break duration (10 min default) are stored on the doctors table itself, since they apply globally to a doctor's schedule, not per-pattern:

SELECT COALESCE(session_duration_minutes, 45) as session_duration,
       COALESCE(break_between_sessions_minutes, 10) as break_duration
FROM doctors WHERE id = $1;

We cap at 2 patterns per doctor per day. Overlap validation runs on creation — if two patterns would produce overlapping time windows on the same weekday, the second is rejected.

Table 2: Availability Exceptions

Exceptions override the pattern for specific dates:

CREATE TABLE doctor_availability_exceptions (
  id UUID PRIMARY KEY,
  doctor_id UUID REFERENCES users(id),
  exception_date DATE,
  operation VARCHAR(10),   -- 'delete' or 'update'
  start_time TIME,         -- for 'update' operations
  end_time TIME
);

A delete exception removes all availability on a date (vacation day). An update exception replaces the time window (working different hours on a specific day). The exception system is intentionally simple — no partial slot removal, no complex override logic.

Table 3: Materialized Slots

When a patient views available times, we don't compute slots on-the-fly from patterns. Instead, slots are lazily generated and materialized:

CREATE TABLE doctor_slots (
  id UUID PRIMARY KEY,
  doctor_id UUID REFERENCES users(id),
  slot_date DATE,
  start_time TIMESTAMPTZ,
  end_time TIMESTAMPTZ,
  status VARCHAR(20) DEFAULT 'available',
  is_booked BOOLEAN DEFAULT false,
  locked_at TIMESTAMPTZ,
  locked_by UUID
);

status tracks the slot lifecycle: available → pending_payment → booked. is_booked is a denormalized boolean that duplicates information from status — it exists for simpler query patterns (WHERE is_booked = false is more readable than WHERE status = 'available').

RRule Expansion

Given a date range query ("show me available slots for next week"), the system first expands the doctor's patterns into concrete dates using the rrule library:

const getDoctorAvailability = (pattern, startDate, endDate) => {
  const rule = new RRule({
    freq: RRule[pattern.freq],     // RRule.WEEKLY
    interval: pattern.interval,
    byweekday: pattern.by_weekday.map(d => RRule[d]), // [RRule.MO, RRule.WE]
    dtstart: forceMidnightUTC(pattern.start_date),
    until: endDate,
  });

  return rule.between(startDate, endDate);
};

The forceMidnightUTC call is critical. RRule's dtstart anchors the recurrence — if it's set to an IST timestamp, the pattern can generate dates that cross UTC day boundaries incorrectly. By forcing midnight UTC, we ensure date calculations are clean and then apply timezone offsets during slot generation.

Lazy Slot Generation

Slots aren't pre-generated for all future dates. They're created on-demand when a patient queries a doctor's availability:

const generateSlots = async (doctorId, date) => {
  // Check if slots already exist for this date
  const existing = await getSlotsByDate(doctorId, date);
  if (existing.length > 0) return existing;

  // Get the doctor's pattern for this weekday
  const pattern = await getPatternForDate(doctorId, date);
  if (!pattern) return [];

  // Check for exceptions
  const exception = await getException(doctorId, date);
  if (exception?.operation === 'delete') return [];

  const startTime = exception?.start_time || pattern.start_time;
  const endTime = exception?.end_time || pattern.end_time;

  // Get session config from doctor's profile
  const { session_duration, break_duration } = await getDoctorSessionConfig(doctorId);

  // Generate slots in IST, store in UTC
  const slots = [];
  let current = fromZonedTime(`${date} ${startTime}`, 'Asia/Kolkata');
  const end = fromZonedTime(`${date} ${endTime}`, 'Asia/Kolkata');

  while (current < end) {
    const slotEnd = addMinutes(current, session_duration);
    if (slotEnd > end) break;

    slots.push({
      doctor_id: doctorId,
      slot_date: date,
      start_time: current,    // UTC TIMESTAMPTZ
      end_time: slotEnd,      // UTC TIMESTAMPTZ
      status: 'available',
    });

    current = addMinutes(slotEnd, break_duration);
  }

  await insertSlots(slots);
  return slots;
};

The timezone conversion is the subtle part. The doctor sets "10 AM IST" in the UI. fromZonedTime('2025-09-01 10:00', 'Asia/Kolkata') converts that to 2025-09-01T04:30:00Z (UTC). The slot is stored in UTC. When displayed to the patient, formatInTimeZone(slot.start_time, 'Asia/Kolkata', 'h:mm a') converts back to "10:00 AM."

For a 10 AM–2 PM window with 45-minute sessions and 10-minute breaks, this generates:

  • 10:00–10:45
  • 10:55–11:40
  • 11:50–12:35
  • 12:45–1:30

Four slots. The last possible slot must end before the window closes — partial sessions aren't created.

The Lazy Generation Trade-off

Lazy generation means the doctor can change their pattern and future (ungenerated) dates automatically pick up the change. But already-generated slots are immutable — changing a pattern from "10 AM–2 PM" to "9 AM–1 PM" won't regenerate already-materialized slots. This is intentional: if a patient is looking at existing slots, yanking them away mid-session would be confusing. Exceptions are the correct mechanism for modifying specific dates.

Booking with FOR UPDATE SKIP LOCKED

The booking flow has a race condition: two patients viewing the same slot can both click "Book." We solve this at the database level:

const lockSlot = async (slotId, userId) => {
  const result = await knex.raw(`
    UPDATE doctor_slots
    SET status = 'pending_payment',
        locked_by = ?,
        locked_at = NOW()
    WHERE id = ?
      AND status = 'available'
      AND start_time > NOW() + INTERVAL '30 minutes'
    RETURNING *
  `, [userId, slotId]);

  if (result.rows.length === 0) {
    throw new BadRequestError('Slot is no longer available');
  }

  return result.rows[0];
};

The FOR UPDATE SKIP LOCKED pattern (used in the slot query that precedes this update) ensures that if two transactions try to lock the same row simultaneously, one succeeds and the other skips it rather than blocking. Combined with the WHERE status = 'available' check in the UPDATE, only one booking can win.

The 30 minutes buffer prevents booking slots that are about to start — giving both patient and doctor time to prepare.

Payment Locking

After a slot moves to pending_payment, the patient has 10 minutes to complete payment:

// Cron: runs every minute
const releaseExpiredLocks = async () => {
  await knex('doctor_slots')
    .where('status', 'pending_payment')
    .where('locked_at', '<', knex.raw("NOW() - INTERVAL '10 minutes'"))
    .update({
      status: 'available',
      locked_by: null,
      locked_at: null,
      is_booked: false,
    });
};

A cron job runs every minute to release expired locks. If the patient abandons the payment flow, the slot returns to available within 11 minutes (10-minute timeout + up to 1-minute cron interval).

A second cron runs every 10 minutes for stuck payments — slots that moved past payment initiation but neither succeeded nor failed:

const expireStuckPayments = async () => {
  await knex('appointments')
    .where('status', 'pending')
    .where('created_at', '<', knex.raw("NOW() - INTERVAL '30 minutes'"))
    .update({ status: 'expired' });
};

Double-Booking Prevention

Beyond FOR UPDATE SKIP LOCKED, we have a database-level safety net:

CREATE UNIQUE INDEX appointments_slot_unique
ON appointments (slot_id)
WHERE status IN ('pending', 'confirmed');

This partial unique index ensures that at most one non-cancelled appointment exists per slot. Even if the application logic has a bug, the database will reject a duplicate appointment with a unique constraint violation. Belt and suspenders.

Timezone Handling

India doesn't observe DST, which simplifies things enormously. UTC+5:30 year-round means no "spring forward" edge cases where a 2 AM slot would disappear.

But midnight boundaries are still tricky. A slot at "11:30 PM IST" converts to "6:00 PM UTC" — same day. A slot at "12:30 AM IST" converts to "7:00 PM UTC" — previous day in UTC. The fromZonedTime / formatInTimeZone pair from date-fns-tz handles this correctly, but it means we can never compare raw dates without timezone context.

The golden rule: store in UTC TIMESTAMPTZ, convert at the boundary (input from user → UTC for storage, UTC from storage → IST for display). No intermediate conversions. No storing IST strings in the database.

import { fromZonedTime, formatInTimeZone } from 'date-fns-tz';

const TIMEZONE = 'Asia/Kolkata';

// IST input → UTC for storage
const utcTime = fromZonedTime('2025-09-01 10:00', TIMEZONE);

// UTC from DB → IST for display
const display = formatInTimeZone(utcTime, TIMEZONE, 'h:mm a');
// → "10:00 AM"

Cron Jobs

Three cron jobs maintain slot and appointment consistency:

  • Interval — Job — Purpose
  • Every 1 minute — Release expired slot locks — Unlock pending_payment slots older than 10 min
  • Every 10 minutes — Expire stuck payments — Mark pending appointments older than 30 min as expired
  • Every 5 minutes — Complete past sessions — Mark confirmed appointments whose end time has passed as completed

These run inside the Node.js process via node-cron. For a single-instance deployment, this works. For multi-instance, we'd need leader election or move to an external scheduler to prevent duplicate execution.

What I'd Change

Add a unique index on `doctor_slots`. The appointments table has a partial unique index for double-booking prevention, but the doctor_slots table relies purely on application logic. A composite unique index on (doctor_id, slot_date, start_time) would prevent duplicate slot generation at the database level.

Decouple slot generation from patient queries. Lazy generation tied to API requests means the first patient to view a date pays a latency penalty. A background job that pre-generates slots for the next 7 days would make queries consistently fast.

Simplify exception matching. The current exception system matches on date + time window with multiple normalization strategies (HH:mm parsing, 12h/24h conversion, range containment). A simpler model — exceptions keyed only by date with a full replacement window — would eliminate the multi-layer matching logic.

The scheduling engine handles the full lifecycle from pattern definition through slot generation, concurrent booking, payment locking, and session completion. FOR UPDATE SKIP LOCKED and the partial unique index are the two mechanisms that keep it correct under concurrent load — everything else is convenience.