Requirements and Scope
An appointment scheduling system allows users (customers) to book time slots with service providers (doctors, barbers, consultants). Core requirements: providers configure their availability (working hours, breaks, buffer time between appointments); customers view available slots and book; double-booking is prevented; both parties receive reminders. Scale: a SaaS scheduling platform like Calendly serves millions of bookings per day. Key challenges: preventing concurrent double-booking, handling time zones correctly, and scaling availability queries across many providers.
Data Model
Provider: provider_id, name, timezone, service_duration_minutes, buffer_minutes (gap between appointments), max_advance_booking_days. ProviderAvailability: availability_id, provider_id, day_of_week (0=Mon, 6=Sun), start_time, end_time (local time, recurring weekly). ProviderException: exception_id, provider_id, date, is_blocked (true=provider unavailable; false=custom hours), custom_start, custom_end. Appointment: appointment_id, provider_id, customer_id, start_time (UTC), end_time (UTC), status (CONFIRMED, CANCELLED, COMPLETED, NO_SHOW), created_at, notes. ReminderLog: log_id, appointment_id, type (EMAIL, SMS), sent_at, status (SENT, FAILED).
Slot Generation and Availability Query
Available slots are generated on-the-fly, not stored. Given provider P and date D: (1) Get P’s weekly availability for the day of week of D. (2) Check ProviderExceptions for date D — override or block the day. (3) Expand into potential slot times: start_time, start_time+duration, start_time+duration+buffer, … until end_time. (4) Subtract booked appointments: SELECT start_time, end_time FROM appointments WHERE provider_id=:p AND DATE(start_time AT TIME ZONE p.timezone)=:d AND status IN (‘CONFIRMED’). (5) Return the remaining slots. Slot conflicts: a slot is unavailable if any booked appointment overlaps with it (including buffer time). A slot [s, e+buffer] conflicts with booking [bs, be] if s bs. Caching: cache the availability for a provider + date for 1 minute. Invalidate when an appointment is booked or cancelled. For high-demand providers (popular doctors): cache aggressively; show “temporarily unavailable” if cache miss during peak load rather than generating slots on every request.
Booking with Conflict Prevention
class BookingService:
def book_appointment(self, provider_id: int, customer_id: int,
start_time: datetime, duration: int,
idempotency_key: str) -> Appointment:
# Idempotency check
if cached := self.idempotency_cache.get(idempotency_key):
return cached
end_time = start_time + timedelta(minutes=duration)
end_with_buffer = end_time + timedelta(
minutes=self.get_provider_buffer(provider_id)
)
with self.db.transaction():
# Lock all appointments for this provider on this day
self.db.execute(
"SELECT pg_advisory_xact_lock(:lock_id)",
lock_id=hash(f"{provider_id}:{start_time.date()}")
)
# Check for conflicts
conflicts = self.repo.find_conflicts(
provider_id, start_time, end_with_buffer
)
if conflicts:
raise SlotUnavailableError()
appt = Appointment(provider_id=provider_id,
customer_id=customer_id,
start_time=start_time,
end_time=end_time,
status=AppointmentStatus.CONFIRMED)
self.repo.save(appt)
self.idempotency_cache.set(idempotency_key, appt, ttl=86400)
return apptPostgreSQL advisory locks (pg_advisory_xact_lock) provide a lightweight per-provider-day lock without row-level contention. The lock key is derived from (provider_id, date) — serializes bookings for the same provider on the same day, while allowing concurrent bookings for different providers.
Reminders and Notifications
Reminder schedule: 24 hours before + 1 hour before the appointment. A scheduled job runs every minute: SELECT a.* FROM appointments a WHERE a.status=’CONFIRMED’ AND a.start_time BETWEEN NOW()+23h AND NOW()+25h AND NOT EXISTS (SELECT 1 FROM reminder_logs rl WHERE rl.appointment_id=a.appointment_id AND rl.type=’EMAIL_24H’). Send reminder, log to reminder_logs. Idempotent: the NOT EXISTS check prevents duplicate sends even if the job runs twice. Timezone display: reminders show the appointment time in the customer’s timezone (not UTC). Store customer_timezone on the appointment at booking time (from the booking request). Time zone conversion: use pytz or dateutil for correct DST-aware conversion. Cancellation window: define a cancellation policy (e.g., free cancellation up to 24 hours before). After the window: cancellation may incur a fee. Check the policy in the CancelAppointment handler before proceeding.
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you handle double-booking when two customers book the same slot simultaneously?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Two customers both see slot 10:00 AM as available and submit bookings at the same moment. Without protection: both succeed, creating a double-booking. Solutions: (1) Database-level serialization with advisory locks: acquire a per-provider-day advisory lock (pg_advisory_xact_lock) before checking conflicts. The second booking blocks until the first commits. After the lock is released, the second transaction re-checks conflicts and finds the slot taken. One booking succeeds; the other gets SlotUnavailableError. (2) Optimistic locking with a version counter: increment a booking_version on the provider record. Include WHERE booking_version=:v in the booking INSERT. If two concurrent bookings try to increment from the same version: only one succeeds (the other sees rows_affected=0 and retries with the updated version). (3) Unique constraint: add a unique index on (provider_id, start_time) in the appointments table. The second concurrent INSERT violates the constraint and fails. Application catches the unique violation and returns a user-friendly error. The unique constraint is the simplest approach for single-instance deployments; advisory locks are more flexible for range-based conflict checking.”
}
},
{
“@type”: “Question”,
“name”: “How does Calendly’s scheduling work from a systems design perspective?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Calendly provides a shareable booking link. When someone visits the link, they see available slots. Architecture: (1) Provider availability: stored as weekly recurring rules (like calendar RRULE). Provider sets working hours once; they recur weekly. Exceptions (vacations, custom hours) override the recurring rules. (2) Slot generation: when a visitor loads the booking page, the server generates available slots for the next N days (configurable). Slots are computed by: expand recurring availability rules for each day, subtract existing appointments (with buffer), return open slots. Cached per provider per day for ~1 minute. (3) Calendar integration: Calendly connects to the provider’s Google Calendar or Outlook. Existing calendar events count as busy (conflicts). The Calendly server polls or receives push notifications for calendar changes and invalidates the slot cache. (4) Booking flow: visitor selects a slot, fills in their name/email, and submits. The booking service atomically creates the appointment (with conflict check) and sends confirmation emails to both parties. (5) Google Calendar event creation: on booking, Calendly creates a Google Calendar event on the provider’s calendar via the Calendar API (with calendar write OAuth scope).”
}
},
{
“@type”: “Question”,
“name”: “How do you handle providers in different time zones from a scheduling UI perspective?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Time zone handling is the hardest part of scheduling UIs. Rules: (1) Store all times in UTC: appointment.start_time is stored as UTC. No timezone is stored on the appointment itself — the provider’s and customer’s timezones are on their profiles. (2) Generate slots in provider timezone: a 9 AM slot means 9 AM in the provider’s local time. Generate availability in provider timezone, then convert to UTC for storage and to visitor timezone for display. (3) Display to visitor in visitor timezone: auto-detect from browser (Intl.DateTimeFormat().resolvedOptions().timeZone) and allow override. Show “All times in America/New_York” with a change link. (4) Confirmation emails: show the appointment time in both the provider’s and the visitor’s timezone to avoid confusion. “Your appointment is at 2:00 PM EST (11:00 AM PST).” (5) DST transitions: a recurring slot at “9 AM” in a DST timezone is 9 AM local time even after the clocks change. When generating slots: expand in the local timezone using a DST-aware library (pytz, dateutil). The UTC equivalent shifts by one hour on the transition date — this is correct and expected.”
}
},
{
“@type”: “Question”,
“name”: “How do you implement group scheduling (finding a time that works for multiple participants)?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Group scheduling: given N participants, find time slots where everyone is available. Algorithm: (1) Fetch free/busy for all participants for the target date range (via their connected calendar APIs or stored availability). (2) For each participant, build their busy intervals list. (3) Union all busy intervals (merge overlapping intervals across all participants). (4) Find gaps in the union that are: >= requested meeting duration, within business hours for all participants (intersection of their working hours), within the scheduling window (not too far in the future). Complexity: O(M log M) where M = total intervals across all participants. Scaling: for 2-5 participants: compute on-the-fly. For 20+ participants: cache each participant’s free/busy separately, merge only when the scheduling request arrives. Suggestion ranking: sort candidate slots by: earliest time (most common preference), middle of the day (avoids early morning and late evening), and fewest close conflicts (to avoid slots near other meetings). Display the top 3-5 suggestions. Allow participants to vote on their preference (Doodle-style polling for async group scheduling).”
}
},
{
“@type”: “Question”,
“name”: “How do you implement buffer time and break time constraints for providers?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Buffer time: a provider needs 15 minutes between appointments (to wrap up, travel to a different room, or prepare). The buffer is logically part of the appointment: the blocked time is [start_time, end_time + buffer_minutes]. Conflict check: a new slot [s, e] conflicts with existing [bs, be] if s bs. Alternatively: set end_time = e + buffer when storing the appointment. Then conflicts are just s bs (no buffer adjustment needed in the query). Break times: providers configure scheduled breaks (lunch 12:00-13:00). Stored as ProviderException with custom_start=null, is_blocked=true, or as a special BREAK availability type. When generating slots: exclude the break window. A slot that would straddle the break boundary is not offered (slot must end before the break starts, or start after it ends). Minimum notice period: providers often require 24-hour advance notice (can’t book a same-day appointment). Enforced in slot generation: exclude slots with start_time < NOW() + min_notice_hours. Maximum advance booking: providers limit booking to 60 days in the future. Enforced in slot generation: exclude slots beyond TODAY + max_advance_days."
}
}
]
}
Asked at: Stripe Interview Guide
Asked at: Airbnb Interview Guide
Asked at: Atlassian Interview Guide
Asked at: Coinbase Interview Guide