Core Entities
Provider: provider_id, name, timezone, working_hours (JSONB: {“MON”: [“09:00″,”17:00”], …}), slot_duration_minutes (e.g., 30). Appointment: appointment_id, provider_id, client_id, start_time (UTC), end_time (UTC), status (PENDING, CONFIRMED, CANCELLED, COMPLETED, NO_SHOW), created_at. BlockedTime: provider_id, start_time, end_time, reason (VACATION, BREAK, PERSONAL).
Availability Calculation
Given a provider and a date, generate available slots:
def get_available_slots(provider_id, date):
provider = db.get_provider(provider_id)
day_name = date.strftime("%a").upper() # "MON"
work_hours = provider.working_hours.get(day_name)
if not work_hours: return [] # day off
work_start = datetime.combine(date, work_hours[0])
work_end = datetime.combine(date, work_hours[1])
# Generate all possible slots
slots = []
current = work_start
while current + timedelta(minutes=provider.slot_duration) <= work_end:
slots.append(current)
current += timedelta(minutes=provider.slot_duration)
# Remove booked slots
booked = db.get_appointments(provider_id, date,
status=[PENDING, CONFIRMED])
blocked = db.get_blocked_times(provider_id, date)
busy_ranges = [(a.start_time, a.end_time) for a in booked + blocked]
return [s for s in slots if not overlaps_any(s,
s + timedelta(minutes=provider.slot_duration), busy_ranges)]Conflict Prevention
Optimistic locking via database constraint: unique index on (provider_id, start_time) WHERE status IN (PENDING, CONFIRMED). This prevents two bookings at the same slot. On INSERT: if unique constraint violation, return “Slot no longer available” to the client. For database-level overlap prevention (partial overlaps): add a check constraint or use PostgreSQL EXCLUDE USING gist (provider_id WITH =, tsrange(start_time, end_time) WITH &&). This prevents any two overlapping appointments for the same provider.
Timezone Handling
Store all times in UTC in the database. Convert to the provider timezone for availability calculations (working hours are in local time). Convert to the client timezone for display. Use the pytz or dateutil library; never store UTC offsets as fixed integers (DST changes them). The timezone field on Provider stores an IANA timezone name (America/New_York, Asia/Tokyo). When a client in Tokyo books with a provider in New York, do the conversion: display slot time in Tokyo time, store in UTC.
Booking Flow
1. Client requests available slots for a provider on a date — returns list of UTC timestamps. 2. Client selects a slot and submits a booking request. 3. Server checks availability again (race condition: another client may have just booked) using SELECT FOR UPDATE or optimistic locking. 4. Create appointment in PENDING status. 5. If payment required: charge payment method; on success transition to CONFIRMED; on failure cancel. 6. Send confirmation notifications to both provider and client. 7. Schedule reminders.
Reminder System
Schedule reminders at booking time: 24-hour and 1-hour before the appointment. Store in a reminders table: reminder_id, appointment_id, fire_at (UTC), channel (EMAIL, SMS, PUSH), status (SCHEDULED, SENT, FAILED). A worker job runs every minute: SELECT * FROM reminders WHERE fire_at <= NOW() AND status = SCHEDULED LIMIT 100. For each: send via the appropriate channel, update status to SENT. Use FOR UPDATE SKIP LOCKED for concurrent workers (each row processed by exactly one worker). On CANCELLED appointment: UPDATE reminders SET status=CANCELLED WHERE appointment_id=X AND status=SCHEDULED.
Rescheduling and Cancellation
Cancellation policy: store policy on the appointment at booking time (e.g., cancel at least 24 hours before for no fee; less than 24 hours incurs a cancellation fee). On cancellation: check policy, compute refund amount, process refund, update status to CANCELLED, fire cancellation notifications, cancel scheduled reminders. Rescheduling: treat as cancellation + new booking. Issue a credit for the old slot and charge for the new one (or handle as a free reschedule per policy). Providers can block out time at any point — this does not affect existing CONFIRMED appointments but removes those slots from future availability.
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you prevent double-booking in an appointment system?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Two approaches. Pessimistic locking: SELECT FOR UPDATE on the provider’s appointment rows overlapping the requested time slot. Only one transaction holds the lock; others wait and re-check. After acquiring the lock, verify no overlap (start_time requested_start), then insert. Optimistic locking: add a PostgreSQL exclusion constraint: EXCLUDE USING gist (provider_id WITH =, tsrange(start_time, end_time, ‘[)’) WITH &&). Any INSERT or UPDATE that creates an overlapping appointment for the same provider fails with a constraint violation. The client catches the error and returns ‘slot no longer available.’ The exclusion constraint uses a GiST index and is enforced atomically — no application-level locking needed.”
}
},
{
“@type”: “Question”,
“name”: “How do you handle timezones in an appointment booking system?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Store all appointment times as UTC in the database. The provider specifies their working hours in their local timezone (stored as an IANA timezone name like America/New_York). When generating available slots: convert working hours to UTC for that specific date (accounting for DST). When displaying to users: convert UTC times to the user’s local timezone. The key rule: never store UTC offsets as fixed integers (-5, +9) — DST changes them twice per year. Always use IANA timezone names and a timezone library (pytz, Joda-Time, dateutil) for conversions. Edge case: if the provider is in a DST-observing timezone and a recurring appointment was booked before the DST change, the UTC time stays the same but the local time shifts — decide whether to keep the UTC time or the local time constant.”
}
},
{
“@type”: “Question”,
“name”: “How do you implement the reminder scheduling for appointments?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Store reminders in a reminders table: appointment_id, fire_at (UTC), channel (EMAIL/SMS/PUSH), status (SCHEDULED/SENT/CANCELLED). Create reminder records at booking time (e.g., fire_at = start_time – 24h and fire_at = start_time – 1h). A worker job runs every minute: SELECT * FROM reminders WHERE fire_at CONFIRMED (payment succeeded) -> COMPLETED (appointment occurred) / NO_SHOW / CANCELLED. On PENDING creation, hold the slot by inserting the appointment (the exclusion constraint prevents others from booking it). Set a TTL: a background job cancels PENDING appointments older than 15 minutes if payment was not collected. This prevents slots from being held indefinitely by abandoned checkouts. On payment success: transition to CONFIRMED, send confirmation emails. On payment failure: transition to CANCELLED, release the slot, notify the client. The key: create the appointment row BEFORE charging payment (to hold the slot), not after (would have a window where the slot appears available but payment is in progress).”
}
}
]
}
Asked at: Airbnb Interview Guide
Asked at: Stripe Interview Guide
Asked at: Shopify Interview Guide
Asked at: Atlassian Interview Guide