Core Entities
Provider: provider_id, name, email, type (DOCTOR, THERAPIST, CONSULTANT, TUTOR), timezone, settings (JSONB: buffer_minutes_between_appointments, max_advance_days, min_notice_hours, auto_confirm). Schedule: schedule_id, provider_id, day_of_week (0-6), start_time (TIME), end_time (TIME), is_active. (Regular weekly schedule.) ScheduleOverride: override_id, provider_id, date (DATE), type (UNAVAILABLE, SPECIAL_HOURS), start_time, end_time. (One-off changes: vacations, extended hours.) AppointmentType: type_id, provider_id, name, duration_minutes, is_virtual, price, max_participants (1 for 1:1, N for group). Appointment: appt_id, provider_id, type_id, client_id, start_time (TIMESTAMPTZ), end_time (TIMESTAMPTZ), status (PENDING, CONFIRMED, CANCELLED, COMPLETED, NO_SHOW), notes, created_at, cancelled_at, cancel_reason. WaitlistEntry: entry_id, provider_id, type_id, client_id, requested_date, created_at, notified_at.
Availability Computation
class AvailabilityService:
def get_available_slots(self, provider_id: int, date: datetime.date,
appt_type_id: int) -> list[datetime]:
provider = self.db.get_provider(provider_id)
appt_type = self.db.get_appointment_type(appt_type_id)
duration = appt_type.duration_minutes
buffer = provider.settings.get("buffer_minutes_between_appointments", 0)
slot_size = duration + buffer
# 1. Get regular schedule for this day of week
schedule = self.db.query_one(
"SELECT start_time, end_time FROM schedules "
"WHERE provider_id=%s AND day_of_week=%s AND is_active=true",
provider_id, date.weekday()
)
if not schedule:
return [] # provider not working this day
# 2. Check for overrides on this date
override = self.db.query_one(
"SELECT type, start_time, end_time FROM schedule_overrides "
"WHERE provider_id=%s AND date=%s",
provider_id, date
)
if override and override.type == "UNAVAILABLE":
return []
if override and override.type == "SPECIAL_HOURS":
work_start = datetime.combine(date, override.start_time)
work_end = datetime.combine(date, override.end_time)
else:
work_start = datetime.combine(date, schedule.start_time)
work_end = datetime.combine(date, schedule.end_time)
# 3. Get existing confirmed appointments for this day
booked = self.db.query(
"SELECT start_time, end_time FROM appointments "
"WHERE provider_id=%s AND DATE(start_time)=%s "
"AND status IN ('CONFIRMED', 'PENDING')",
provider_id, date
)
booked_intervals = [(a.start_time, a.end_time) for a in booked]
# 4. Generate candidate slots and filter conflicts
slots = []
cursor = work_start
while cursor + timedelta(minutes=duration) <= work_end:
slot_end = cursor + timedelta(minutes=duration)
# Check if slot overlaps any booked interval (including buffer)
conflict = any(
cursor b_start - timedelta(minutes=buffer)
for b_start, b_end in booked_intervals
)
if not conflict:
slots.append(cursor)
cursor += timedelta(minutes=slot_size)
return slotsBooking with Conflict Prevention
class BookingService:
def book_appointment(self, provider_id: int, client_id: int,
type_id: int, start_time: datetime) -> Appointment:
appt_type = self.db.get_appointment_type(type_id)
end_time = start_time + timedelta(minutes=appt_type.duration_minutes)
provider = self.db.get_provider(provider_id)
buffer = provider.settings.get("buffer_minutes_between_appointments", 0)
with db.transaction():
# Pessimistic lock: lock all appointments for this provider today
self.db.execute(
"SELECT 1 FROM appointments "
"WHERE provider_id = %s AND DATE(start_time) = %s "
"FOR UPDATE",
provider_id, start_time.date()
)
# Check for conflict including buffer time
conflict = self.db.query_one(
"SELECT appt_id FROM appointments "
"WHERE provider_id = %s "
"AND status IN ('CONFIRMED', 'PENDING') "
"AND start_time %s",
provider_id,
end_time + timedelta(minutes=buffer),
start_time - timedelta(minutes=buffer)
)
if conflict:
raise SlotNoLongerAvailable(start_time)
status = "CONFIRMED" if provider.settings.get("auto_confirm")
else "PENDING"
appt = self.db.insert("appointments", {
"provider_id": provider_id,
"client_id": client_id,
"type_id": type_id,
"start_time": start_time,
"end_time": end_time,
"status": status
})
self._send_confirmation(appt)
return apptCancellation, Rescheduling, and Waitlist
Cancellation: update status to CANCELLED, record cancelled_at and cancel_reason. Apply cancellation policy: if cancelled within min_notice_hours, flag as LATE_CANCEL for potential fee. After cancellation: trigger waitlist notification. Query waitlist entries for this provider + appointment type ordered by created_at. Notify first N waitlisted clients (N=3) via email/SMS with a 2-hour claim window. First to book claims the slot. Rescheduling: implemented as cancel + new booking in a single transaction to prevent the slot being taken between steps. Use the same conflict prevention lock. Reminders: background job sends 24h and 1h reminders for all CONFIRMED appointments. Uses the appointment start_time and provider timezone for correct local time in the notification.
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you prevent double-booking in an appointment scheduling system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use pessimistic locking (SELECT FOR UPDATE) within a transaction. When booking, lock all existing appointments for the provider on that date, then check for time overlap: existing.start_time < new_end_time AND existing.end_time > new_start_time. If a conflict exists, throw SlotNoLongerAvailable. Including buffer time: expand the conflict window by buffer_minutes on each side. This serializes concurrent booking attempts for the same provider, preventing the race condition where two users see the same slot available and both book it.”}},{“@type”:”Question”,”name”:”How do you compute available appointment slots?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Three-step process: (1) Get the provider's regular schedule for the requested day of week (start_time, end_time). (2) Check for date-specific overrides (vacation = unavailable, extended hours = different times). (3) Query confirmed/pending appointments, generate candidate slots every (duration + buffer) minutes from work_start to work_end, and filter out any that overlap with existing appointments. Return the list of available start times. Cache the result for 30-60 seconds to reduce DB load during high-traffic slot selection.”}},{“@type”:”Question”,”name”:”How do you handle timezone complexity in a scheduling system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store all appointment times as TIMESTAMPTZ (UTC) in the database. Store the provider's timezone and client's timezone separately. For availability display: convert UTC work hours to the viewer's timezone before rendering slots. For reminders: send at the correct local time by converting the appointment's UTC time to the recipient's timezone. For overnight slots (if a provider works across midnight in one timezone): the UTC time correctly spans two calendar dates – query by UTC timestamp range, not by DATE() function, to avoid missing cross-midnight appointments.”}},{“@type”:”Question”,”name”:”How does the waitlist work when a cancellation occurs?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Waitlist entries store (provider_id, type_id, client_id, requested_date, created_at). When an appointment is cancelled, query the waitlist for matching provider + type ordered by created_at (FIFO). Notify the first 3 waitlisted clients simultaneously via email/SMS with a time-limited claim link (2-hour TTL, implemented as a signed JWT or a Redis key). The first client to click the link and confirm claims the slot using the normal booking flow with conflict prevention. On successful booking, send a "slot claimed" notification to the other notified clients and remove their waitlist entries.”}},{“@type”:”Question”,”name”:”How do you implement buffer time between appointments?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store buffer_minutes_between_appointments on the Provider settings. When computing availability slots: slot_size = duration + buffer. Advance the cursor by slot_size for each candidate slot. When checking for conflicts: expand the existing appointment window by buffer minutes on each side before checking overlap. This means two consecutive appointments always have at least buffer_minutes of gap between them. Buffer time accounts for provider preparation, cleanup, and travel to the next appointment. It is provider-specific and may vary by appointment type.”}}]}
Airbnb system design interviews cover availability and booking systems. See design patterns for Airbnb interview: availability and booking system design.
Lyft system design rounds include scheduling and reservation systems. Review patterns for Lyft interview: scheduling and slot reservation design.
Stripe system design interviews cover transactional reservation systems. See patterns for Stripe interview: transactional booking and reservation design.