Design doctor appointment booking (Practo)

Doctors think in rules: 'Mon-Sat 10:00-13:00 at Clinic A, 15-minute slots; Tue/Thu 18:00-20:00 at Clinic B; on leave 20-25 Dec'. Store those rules as the source of truth and materialise slots for a rolling window (say 21 days) with a daily job and on every rule change. Materialised slots make booking simple: a slot is a row that can be claimed with one conditional update, and availability is a range query. Rule edits are the tricky part. When a doctor shortens a session that already has bookings, the generator must not delete booked slots; it marks them as conflicts and creates a task for the clinic to reschedule those patients, who are notified. Generate in the clinic's timezone (India has one, but the platform may expand), and make the generator idempotent by deriving slot IDs from (calendar_id, start_at), so re-running never creates duplicates. Leaves become BLOCKED slots, not deleted ones, keeping history clean. For doctors who take unlimited patients per session, skip slots and use the token model instead.

The platform is not the only way patients book: the clinic receptionist takes phone calls and walk-ins, and some clinics use their own software. If these channels keep separate calendars, double booking is guaranteed. The design must make one inventory the source of truth per doctor-clinic calendar, which is the platform's slot table, and give every channel access to it. The clinic desk app (the platform's own practice software, as Practo does with its clinic product) books through the same API. For clinics using third-party software, a sync integration pushes and pulls bookings with cursors and idempotency keys. Clinics lose internet often, so the desk software works offline from a local copy and syncs later; conflicts are then possible and must be resolved explicitly, not silently overwritten: online bookings win by default and the desk sees the conflict. To reduce conflicts, doctors can reserve a share of slots for walk-ins and phone bookings, exposing only part of the session online.

Many Indian clinics do not run exact appointment times: they run sessions where patients get token numbers and the doctor sees them in order. Supporting only fixed slots misses how most of the market works. In token mode, a session has max_tokens and issued_tokens; booking issues the next token with an atomic increment bounded by the limit (a conditional UPDATE issued = issued + 1 WHERE issued < max, returning the new value). The desk app advances current_token as patients go in. Live status is read far more often than it is written (patients refreshing 'how many before me'), so keep it in Redis and push changes via SSE or poll every 30-60 s. Estimate waiting time from the doctor's moving average time per patient in this session and recent days. Notify patients when they are ~3 tokens away, which lets them wait at home instead of a crowded waiting room. Late arrivals and emergencies break strict order; let the desk re-order, and show patients an estimate, not a promise.

Appointments reveal sensitive facts: a booking with a psychiatrist or an oncologist is health data even without notes. Treat the whole system accordingly. Encrypt data at rest and in transit, encrypt documents per object with KMS keys, and restrict staff access through roles: a clinic receptionist sees today's appointments for their clinic, not patients' histories; doctors see records only for patients who booked with them or consented to share. Log every read of medical records in an append-only audit log and review unusual access. Keep minimum data in notifications: 'Your appointment at 6:30 pm tomorrow' without the specialty. Under India's DPDP Act you need clear consent for each purpose, deletion on request (subject to medical record retention rules), and breach notification. If you integrate with the national health stack (ABDM, with ABHA health IDs), records are shared only through consent artefacts. Analytics and ranking must use de-identified data.

Computing free time from rules minus bookings at read time saves storage but makes booking harder (you must lock a time range, not a row) and search slower. Materialised slots cost ~150M small rows and a generator job, but give simple atomic booking and fast range reads. Choose materialised slots; use time-range constraints only where slot lengths vary.

Booking needs conditional updates, transactions with the appointment row, and range queries by date. Postgres partitioned by calendar handles ~30 bookings/s trivially. A NoSQL store adds nothing here; the scale is moderate.

Some doctors overbook on purpose because of no-shows (often 10-20%). Support it explicitly with a per-session overbook limit, rather than by bypassing constraints, so the system still knows what is promised.

If next_available_at is stale, patients click doctors with no free slots. Update it from booking events within seconds, and verify slots live when the profile opens. At 10x, batch index updates per doctor every few seconds.

Popular doctors' slots open at a fixed time and fill in minutes. Contention is per slot, so database row locks handle it; rate limit per patient and stop showing a slot as available as soon as it is claimed to reduce failed attempts.

Reminders are scheduled jobs in a durable store; when the service recovers it sends overdue reminders only if the appointment is still in the future and not cancelled. Missing a reminder increases no-shows but breaks nothing.

Online booking continues against the platform calendar; the desk works locally. On reconnect, sync resolves conflicts explicitly. Optionally, stop online bookings for today's remaining slots when a clinic is offline for long, to reduce conflicts.

For prepaid video consults, payment confirmation and slot claim are tied by the appointment's idempotency key. If the slot was lost, refund automatically or offer the next slot with the same doctor.