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 slotsBooking 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.
See also: Stripe Interview Prep
See also: Shopify Interview Prep
See also: Airbnb Interview Prep