Low-Level Design: Appointment Scheduling System — Availability, Booking, and Calendar Sync

Core Entities

Provider: provider_id, user_id, name, type (DOCTOR, CONSULTANT, TRAINER, THERAPIST), timezone, bio, photo_url, is_active. Service: service_id, provider_id, name, duration_minutes, price, buffer_time_minutes (gap between appointments), max_advance_days (how far in advance can be booked), cancellation_hours (how early must cancel for refund). Availability: availability_id, provider_id, day_of_week (0-6), start_time, end_time, is_active. (Recurring schedule.) AvailabilityOverride: override_id, provider_id, date, start_time, end_time, is_blocked (true = provider unavailable; false = extra availability). Appointment: appointment_id, provider_id, client_id, service_id, start_time, end_time, status (PENDING, CONFIRMED, CANCELLED, COMPLETED, NO_SHOW), notes, meeting_link (for virtual), created_at, cancelled_at, cancellation_reason. CalendarSync: sync_id, provider_id, calendar_type (GOOGLE, OUTLOOK, APPLE), access_token, refresh_token, last_synced_at, external_calendar_id.

Computing Available Slots

class AvailabilityService:
    def get_available_slots(self, provider_id: int, service_id: int,
                            date: date) -> list[TimeSlot]:
        service = self.repo.get_service(service_id)
        provider = self.repo.get_provider(provider_id)

        # Step 1: Get base availability for this day of week
        dow = date.weekday()
        base = self.repo.get_availability(provider_id, dow)
        if not base:
            return []

        # Step 2: Apply overrides for this specific date
        overrides = self.repo.get_overrides(provider_id, date)
        working_hours = self._apply_overrides(base, overrides)

        # Step 3: Get existing appointments for this date
        existing = self.repo.get_appointments_for_date(provider_id, date)

        # Step 4: Generate slots within working hours
        slots = []
        slot_duration = timedelta(minutes=service.duration_minutes + service.buffer_time_minutes)
        for window_start, window_end in working_hours:
            slot_start = window_start
            while slot_start + timedelta(minutes=service.duration_minutes) <= window_end:
                slot_end = slot_start + timedelta(minutes=service.duration_minutes)
                # Check for conflicts with existing appointments
                conflict = any(
                    appt.start_time  slot_start
                    for appt in existing
                    if appt.status not in ('CANCELLED',)
                )
                if not conflict:
                    slots.append(TimeSlot(start=slot_start, end=slot_end))
                slot_start += slot_duration
        return slots

Booking with Race Condition Prevention

Two clients may try to book the same slot simultaneously. Prevention: optimistic locking or pessimistic locking. Pessimistic (SELECT … FOR UPDATE): lock the time range for the provider. Check for conflicts within the lock. Insert if clear. Release lock. Optimistic: include a version number on the provider’s schedule. On insert: verify no appointments overlap the requested slot. If a conflict is found (from a concurrent booking): return SLOT_TAKEN. Client retries or selects another slot. Idempotency: booking requests include an idempotency_key (UUID). If the same request is submitted twice (network retry), return the existing booking. Stored in a separate idempotency table with TTL.

Calendar Integration and Reminders

Google Calendar sync: OAuth 2.0 flow to obtain access_token and refresh_token. On appointment creation: create a Google Calendar event via the Calendar API (events.insert). On appointment cancellation: delete the event (events.delete). Two-way sync: watch for changes on the provider’s Google Calendar (push notifications via webhooks). When an external event is created/deleted, update the provider’s availability in the system. Conflict detection: external calendar events block the corresponding time slots. External events are stored as AvailabilityOverride records with is_blocked=true. Reminders: 48-hour reminder: email + push to both provider and client. 1-hour reminder: push notification. No-show handling: if the provider marks the appointment as NO_SHOW, trigger the no-show policy (e.g., no refund, add no-show flag to client’s record, lock out from same-day booking). Virtual appointments: on confirmation, generate a unique meeting link (Zoom/Google Meet API) and include it in the confirmation email and calendar event. Meeting link stored on the Appointment record.

{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you handle availability overrides like vacations or extra hours?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store recurring weekly availability separately from one-time overrides. Overrides have a specific date and can either block time (is_blocked=true, e.g., vacation) or add extra availability (is_blocked=false, e.g., working on a normally off day). When computing slots for a date, start with the recurring base availability, then apply overrides: blocked overrides remove windows, extra overrides add windows. This keeps the base schedule clean and supports arbitrary date-specific modifications.”}},{“@type”:”Question”,”name”:”How do you prevent double-booking an appointment slot?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use a database transaction with a row-level lock on the provider's schedule (SELECT … FOR UPDATE on the appointments rows for that provider and date). Within the lock, check for any existing appointment that overlaps the requested time range (existing.start_time < requested.end AND existing.end_time > requested.start). If overlap found, return SLOT_TAKEN. If clear, insert the appointment and commit. The lock ensures that two concurrent requests for the same slot cannot both pass the check.”}},{“@type”:”Question”,”name”:”How does two-way Google Calendar sync work for providers?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”On appointment creation: create a Google Calendar event via the Calendar API using the provider's OAuth tokens. Store the Google event ID on the appointment record for future updates/deletions. For incoming changes on Google Calendar: register a push notification webhook (Google Calendar Watch API). When an external event is created/modified on the provider's calendar, the webhook fires and the system creates an AvailabilityOverride to block that time slot, preventing double-booking from both directions.”}},{“@type”:”Question”,”name”:”How do you compute available slots for a service with a buffer time?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Each service has duration_minutes and buffer_time_minutes. When generating slots, the step size between consecutive slot start times is duration + buffer (not just duration). So a 30-minute service with 10 minutes buffer creates slots at :00, :40, 1:20, etc. The buffer prevents back-to-back bookings and gives providers transition time. The slot display to the client shows only the service duration (30 min), not the buffer.”}},{“@type”:”Question”,”name”:”How do you handle time zone differences between providers and clients?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store all appointment times in UTC in the database. Each provider has a configured timezone. When computing and displaying availability to a client: convert from provider UTC windows to the client's local timezone for display. When the client books: convert the selected local time back to UTC for storage. Calendar invites include the correct timezone offset (VTIMEZONE in iCal format). Daylight saving time transitions are handled automatically by using proper IANA timezone identifiers (e.g., "America/New_York") rather than fixed UTC offsets.”}}]}