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.
Asked at: Stripe Interview Guide
Asked at: Airbnb Interview Guide
Asked at: Atlassian Interview Guide
Asked at: Coinbase Interview Guide