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