Zero Double-Bookings, Higher Utilization: A Clinical Scheduling Engine
The Situation
A mid-sized clinic loses 15 to 20 percent of its daily appointment capacity to scheduling failures. Gaps open between appointments because the scheduler doesn't know which rooms are free. Patients get double-booked when two front-desk staff pick the same slot for different patients. Cancellations go unfilled because nobody connects the freed opening with a patient on the waitlist.
The complexity hides in the constraints. Five providers, eight treatment rooms, about 90 appointment slots per day. Each provider works a 9-hour window at 15-minute scheduling increments. Each room has different equipment (ECG machines, ultrasound units, blood pressure monitors) and can only host certain appointment types. Calendar software with manual coordination can't evaluate all of that simultaneously.
Those empty slots cost a mid-sized practice roughly €280K a year in unrealized appointments.
The Cost of Doing Nothing
Manual scheduling in a 5-provider clinic burns 4 to 7 hours of staff time daily. One person mentally cross-referencing provider availability, room equipment, buffer times, and existing bookings while handling patient check-ins. That scheduling labor alone costs roughly €35K to €45K annually.
Double-bookings cost more than wasted time. When two patients show up for the same 9:15 AM slot, one gets rescheduled. Some don't come back. The direct cost is the lost appointment. The hidden cost is trust.
Cancellations that go unfilled are pure loss. A 2:00 PM opening that nobody fills is roughly €80 the clinic can't recover. Without a system connecting freed slots to patients who need them, recovery doesn't happen. The front desk was handling check-ins when the cancellation came in. That slot stays empty.
What I Built
A scheduling engine that computes optimal appointment slots in real time from provider availability, room requirements, equipment constraints, buffer times, and existing bookings. No stored slot tables. No synchronization jobs. Every query reflects current database state.
The engine evaluates five constraint dimensions simultaneously: provider availability windows, existing bookings, room type and equipment compatibility, buffer time between appointments, and overbooking allowances. Each valid slot is scored by a 4-weight quality function that balances patient preference (40%), schedule compactness (35%), room switching penalty (15%), and overbooking priority (10%). The result is a ranked list of appointment options, best first.
The overbooking system was more complex than I expected. A single "allow 1 extra booking" flag per provider isn't enough, because different appointment types have different no-show rates. The engine supports three levels of overbooking rules (global, provider-only, and provider-plus-appointment-type), resolves conflicts by specificity, and generates overbooked slots in a second scoring pass with lower quality scores. Getting the priority hierarchy right took more iteration than the core constraint engine.
Double-booking is prevented at the database level. Two PostgreSQL UNIQUE constraints (one per provider, one per room) guarantee that no two bookings can occupy the same slot. Under a stress test with 10 concurrent booking requests for the same time, exactly one succeeds. The other nine receive a clean error in under a second. No manual intervention required.
When a patient cancels, the system immediately identifies backfill candidates: other patients with the same appointment type who were previously cancelled within the past week, ranked by time proximity. The front desk sees recovery options before they hang up the phone.
System Flow
Data Model
Architecture Layers
The Decision Log
| Decision | Alternative Rejected | Why |
|---|---|---|
| Real-time slot computation from constraints | Pre-generated slot table with synchronization | Stored slots need constant syncing with every booking, cancellation, and availability change. One missed trigger means phantom slots or hidden availability. Computing from live data means the answer is always correct. |
| PostgreSQL UNIQUE constraints for double-booking prevention | Application-level locking or Redis distributed lock | Database constraints handle concurrency at the storage layer. No race conditions to manage in application code. Verified with a 10-request concurrent stress test. |
| Fire-and-forget notification events | Synchronous webhook or message queue | Booking operations must never fail because the notification service is down. The hub client catches all errors with a 5-second timeout. |
| Weighted quality scoring (4 factors) | Simple time-based sorting | Time-only sorting creates scheduling gaps and ignores patient preferences. The scorer balances convenience against clinic efficiency. |
| In-memory availability cache (60s TTL) | Redis cache | Single-process deployment. Redis adds infrastructure with no benefit at this scale. Cache invalidates naturally via TTL. |
| APScheduler in-process for background jobs | Celery with Redis broker | Two hourly jobs (no-show marker, stats calculator). Celery would add three components for work that takes under a second per run. |
Ecosystem Integration
Appointment events (bookings, cancellations, no-shows) flow into a notification hub I built for this exact pattern. The hub accepts an HTTP POST with an event type and payload, then handles channel routing (email, Telegram, in-app) based on rules configured per event type. The scheduling engine doesn't know or care how a patient gets notified. It fires the event and moves on. If the hub is down, the booking still succeeds.
Full breakdown of the notification architecture: www.kingsleyonoh.com/projects/event-driven-notification-hub
Results
Before automation, a 5-provider clinic manages scheduling through calendar software and manual coordination, losing 15 to 20 percent of daily capacity to gaps, double-bookings, and unfilled cancellations.
After deployment:
- Double-bookings eliminated. Database-level constraints prevent two bookings from occupying the same provider or room time slot. The system handles the conflict automatically, regardless of how many requests arrive simultaneously.
- Slot computation responds in under 500 milliseconds. Full scan across 5 providers and 8 rooms with a day of existing bookings. Verified by automated performance benchmark.
- Cancellation recovery is immediate. Backfill candidates appear in the cancellation response, ranked by proximity. The front desk sees recoverable patients before the call ends.
- No-shows are automatically flagged. An hourly background job detects overdue confirmed bookings and marks them. No manual status tracking required.
- 229 tests, 84.5% code coverage. Unit and integration tests for every API endpoint, the optimizer engine, the quality scorer, and a concurrent booking stress test.
The engine went from first commit to production in under three weeks. The database schema shipped as a single Alembic migration and hasn't changed since. No patches, no hotfixes, no schema modifications after initial deployment.