Core Entities
Provider: provider_id, name, specialty, clinic_id, npi_number, languages[], accepts_new_patients. ProviderSchedule: schedule_id, provider_id, day_of_week, start_time, end_time, slot_duration_minutes, is_active. Appointment: appointment_id, patient_id, provider_id, appointment_type (NEW_PATIENT, FOLLOW_UP, URGENT_CARE, TELEHEALTH), scheduled_start, scheduled_end, status (SCHEDULED, CONFIRMED, COMPLETED, CANCELLED, NO_SHOW), reason_for_visit, notes. Patient: patient_id, name, dob, insurance_member_id, insurance_plan_id, primary_care_provider_id. TimeOff: provider_id, start_datetime, end_datetime, reason (VACATION, CME, SICK).
Slot Availability Computation
Computing available slots: (1) Load the provider recurring schedule for the requested week. (2) Generate all possible slots: for each working day, iterate from start_time to end_time in slot_duration_minutes increments. (3) Remove slots blocked by TimeOff entries that overlap the slot window. (4) Remove slots occupied by existing SCHEDULED or CONFIRMED appointments. (5) Return remaining slots. Caching: cache slot availability per provider per day with a short TTL (30-60 seconds). Invalidate on new booking or cancellation. For high-traffic scenarios, pre-compute slot availability for the next 30 days and cache in Redis as a bitmap (each bit = one slot, 1=available, 0=taken).
Booking with Conflict Prevention
Race condition: two patients simultaneously book the last available slot. Prevention: optimistic locking or database-level serialization. Preferred approach: when inserting an appointment, check for conflicts with a conditional INSERT: INSERT INTO appointments (…) SELECT … WHERE NOT EXISTS (SELECT 1 FROM appointments WHERE provider_id=X AND scheduled_start=T AND status NOT IN (“CANCELLED”)). If the INSERT fails (conflict exists), return “slot no longer available.” This works because the database serializes concurrent INSERTs to the same provider/time slot. Alternative: use a slot_reservations table with a unique constraint on (provider_id, slot_datetime) as a mutex. Reserve the slot, then create the appointment, then release the slot reservation atomically.
Appointment Reminders
Automated reminders reduce no-show rates by 20-30%. Reminder schedule: 72 hours before (email), 24 hours before (SMS + email), 2 hours before (push notification for telehealth). Implementation: a scheduled job runs every 15 minutes, querying appointments in the upcoming reminder window. For each appointment in the window: check if the reminder was already sent (store reminder_sent_at per reminder type). Send via notification service. Mark as sent. Pre-confirmation: send a confirmation request 72 hours before. If the patient does not confirm within 24 hours: release the slot and notify the patient. This allows filling cancelled slots. Patient preferences: respect preferred reminder channel and opt-out settings.
Waitlist Management
When no slots are available, add the patient to a waitlist: (provider_id, patient_id, appointment_type, earliest_desired_date, created_at). When a cancellation occurs: query the waitlist for patients who want to see this provider, with appointment_type matching, and earliest_desired_date on or before the newly available slot date. Notify the first eligible patient by push notification with a time-limited claim window (2 hours). If they do not claim within 2 hours, notify the next patient. Continue until the slot is filled or the waitlist is exhausted. Priority: existing patients over new patients; urgent over routine; FIFO within the same priority tier.
EMR Integration
Electronic Medical Records (EMR) integration syncs appointment data with clinical systems (Epic, Cerner). Standards: HL7 FHIR (modern REST-based) and HL7 v2 (older message-based). On appointment creation: POST /fhir/Appointment to the EMR. On completion: update appointment status and trigger clinical note creation workflow. Patient demographics sync: on new patient registration, search the EMR for existing records (match on name + DOB + insurance ID). If found: link patient_id to EMR patient_id to avoid duplicate records. FHIR resources: Appointment, Patient, Practitioner, Slot, Schedule. Authentication: OAuth 2.0 SMART on FHIR standard. All PHI (protected health information) data must be encrypted in transit (TLS 1.2+) and at rest (AES-256) per HIPAA requirements.
Asked at: Atlassian Interview Guide
Asked at: Shopify Interview Guide
Asked at: Stripe Interview Guide
Asked at: DoorDash Interview Guide