Appointment Booking Service Low-Level Design: Availability Slots, Conflict Detection, and Reminders

Appointment Booking Service: Overview and Requirements

An appointment booking service allows clients to discover available time slots for a provider and atomically reserve one. The central challenges are preventing double-booking under concurrent requests, modeling flexible provider availability, and delivering reliable reminder notifications close to appointment time.

Functional Requirements

Providers configure recurring availability rules and one-off overrides (blocks and extensions).
Clients query available slots for a provider within a date range.
Clients book a slot, receiving a confirmation with a unique booking reference.
Providers and clients can cancel or reschedule with configurable notice windows.
Reminder notifications are sent via email and push at configurable lead times before each appointment.

Non-Functional Requirements

Slot availability queries must return within 200 ms at the 99th percentile.
Booking must be atomic: two concurrent requests for the same slot must result in exactly one success.
Reminder delivery must achieve at least 99.5% on-time rate within a one-minute window of the scheduled send time.
The system must support 10,000 providers with up to 100 bookings per provider per day.

Data Model

Provider: provider_id, name, timezone, buffer_minutes (gap required between appointments), max_advance_days, cancellation_notice_hours.
AvailabilityRule: rule_id, provider_id, day_of_week (0-6), start_time, end_time, slot_duration_minutes, effective_from, effective_until.
AvailabilityOverride: override_id, provider_id, date, type (block | extend), start_time, end_time.
Appointment: appointment_id, provider_id, client_id, start_at (UTC), end_at (UTC), status (pending | confirmed | cancelled | completed), booking_ref, created_at, updated_at, version.
ReminderJob: job_id, appointment_id, send_at, channel (email | push | sms), status (scheduled | sent | failed), attempts.

The Appointment table carries an optimistic locking version column. All booking writes include a WHERE version = :expected clause to detect concurrent modifications at the database level.

Slot Availability Algorithm

Availability for a given provider and date range is computed on read:

Fetch all AvailabilityRules whose effective range overlaps the query window.
Expand rules into candidate slots: for each covered date, generate slots from start_time to end_time in increments of slot_duration_minutes, adding buffer_minutes between each.
Fetch AvailabilityOverrides for the date range. Block overrides remove candidate slots they overlap. Extend overrides add new candidate slots.
Fetch confirmed Appointments for the provider within the date range. Remove any candidate slot that overlaps an existing appointment including its buffer.
Return the remaining candidate slots, capped at max_advance_days from today.

Results are cached per provider per day with a short TTL of 30 seconds to reduce database pressure while keeping availability fresh during active booking periods.

Atomic Conflict Detection and Booking

The booking flow uses a database-level unique constraint and optimistic locking to prevent double-booking:

A partial unique index on (provider_id, start_at) where status IN (pending, confirmed) ensures no two active appointments share the same slot.
The service attempts INSERT INTO appointments … and catches unique constraint violations, returning a 409 Conflict to the client.
For rescheduling, the old appointment is moved to cancelled and the new slot is inserted in a single database transaction.
A distributed lock keyed on provider_id is held for the duration of the transaction when the database does not support partial unique indexes, providing a fallback serialization point.

Confirmation Workflow

After a successful insert, the booking enters a short pending state. A confirmation event is published to a message queue. A downstream workflow service consumes the event and:

Sends a confirmation email with the booking reference, date, time, and cancellation link.
Creates ReminderJob records for each configured lead time (e.g., 24 hours and 1 hour before the appointment).
Transitions the appointment status from pending to confirmed once confirmation delivery is acknowledged.

Reminder Pipeline

ReminderJobs are processed by a dedicated scheduler that polls for jobs where send_at is within the next scheduling horizon, typically 10 minutes. Jobs are claimed using SELECT FOR UPDATE SKIP LOCKED to distribute work across multiple scheduler instances without contention.

Each claimed job is dispatched to a channel-specific sender (email via SMTP relay, push via APNs/FCM, SMS via carrier gateway).
Failed sends are retried with exponential backoff up to a maximum of three attempts.
If the appointment is cancelled before a reminder fires, the scheduler marks the corresponding ReminderJobs as cancelled during the next poll cycle.

API Design

GET /providers/{id}/slots?from=&to=&duration= — list available slots in the requested window.
POST /appointments — book a slot; body includes provider_id, client_id, start_at, duration_minutes.
GET /appointments/{booking_ref} — retrieve appointment details by booking reference.
DELETE /appointments/{booking_ref} — cancel an appointment, subject to cancellation notice window.
PATCH /appointments/{booking_ref}/reschedule — atomically move to a new slot.
PUT /providers/{id}/availability — create or replace availability rules for a provider.

Scalability Considerations

Slot expansion is CPU-bound and can be parallelized per provider. For high-traffic providers, pre-computed slot caches with invalidation on booking or rule change reduce per-query work. The reminder scheduler scales horizontally since job claiming is lock-free at the application level via SKIP LOCKED. Database sharding by provider_id isolates hot providers and allows the appointment table to grow without cross-shard joins.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you model provider availability slots in an appointment booking system?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Provider availability is stored as a set of recurring or one-off time windows in a slots table keyed by provider_id, start_time, and end_time. Each slot carries a status (open, held, booked) and a version counter for optimistic locking. Recurring schedules are expanded lazily into concrete rows within a rolling horizon (e.g., 90 days) by a background job, keeping the table bounded while still allowing far-future queries.”
}
},
{
“@type”: “Question”,
“name”: “How do you detect and prevent double-booking atomically?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A partial unique index on (provider_id, start_time) WHERE status = 'booked' enforces at the DB level that no two confirmed appointments share the same slot. The booking flow uses a SELECT … FOR UPDATE on the target slot row, verifies status = 'open', then flips it to 'booked' in the same transaction. Any concurrent attempt hits the row lock and either retries or fails fast, eliminating race conditions without application-level distributed locks.”
}
},
{
“@type”: “Question”,
“name”: “What states does a confirmation state machine need for appointment booking?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A minimal state machine uses: PENDING (slot held, payment not yet confirmed), CONFIRMED (payment captured, slot locked), CANCELLED (by patient or provider, slot released), NO_SHOW, and COMPLETED. Transitions are persisted as append-only events in an appointment_events table so the current state can always be replayed. Invalid transitions (e.g., COMPLETED -> CONFIRMED) are rejected at the domain layer before any DB write.”
}
},
{
“@type”: “Question”,
“name”: “How do you build a reliable reminder pipeline for appointments?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Reminders are scheduled as rows in a reminders table with send_at timestamp and a delivered boolean. A polling worker (or cron) selects WHERE send_at <= NOW() AND delivered = false, claims rows with SELECT … FOR UPDATE SKIP LOCKED to prevent fan-out conflicts, dispatches via SMS/email gateway, then marks delivered. Retry backoff handles transient gateway failures. Reminder windows (24 h, 2 h) are computed at booking time and re-calculated on reschedule to stay consistent with the appointment's confirmed time."
}
}
]
}