Home · About · Services · Industries · Projects · Products · Process · Blog · FAQ · Contact
Clinic appointment scheduling system design: a practical guide Schedule templates, visit types, timed slots versus tokens, double-booking prevention in the database, holds for online booking and time zones. How to design…
A patient books the 10:20 slot online. At the same moment the front desk gives the same slot to someone on the phone. Both get a confirmation. The doctor then leaves early for a surgery, and nobody can quickly say which twelve patients need a call. None of this is a user mistake. It is what happens when scheduling is built as a calendar screen instead of as a set of rules the system enforces.
The short answer: clinic appointment scheduling works when availability comes from per-doctor schedule templates plus dated exceptions, each visit type has its own duration, the database itself refuses overlapping bookings, online bookings hold a slot briefly before confirming, and every change to an appointment is recorded with who made it and why.
We have built a clinic appointment CRM and the Angular front end of a hospital appointment system, and this post covers the design decisions that mattered most. If you want the wider feature list for clinic software, our post on clinic CRM features covers that. This one is about the scheduling engine underneath.
Model availability as templates plus exceptions Do not store a row for every future slot. Pre-generated slots go stale the moment a doctor changes their hours, and someone has to regenerate months of data. Store the rules instead, and calculate free slots when they are requested.
Schedule template: for each doctor, the weekly pattern. Weekday, session start and end in local time, room or chamber, and which visit types are allowed in that session. Effective dates on templates, so a new timetable from next month does not rewrite last month's history. Exceptions: dated overrides such as leave, a conference, a shortened Saturday or an extra evening clinic. An exception either removes time or adds it. Clinic-wide closures for public holidays, entered once rather than per doctor. Resources where they matter: a procedure room, an ultrasound machine or a nurse can be a constraint as real as the doctor. When a doctor's leave is added over a period that already has bookings, the system should not silently delete them. It should list the affected appointments, mark them as needing rescheduling and give the front desk a call list. Without that screen, staff end up working through the day view by hand, and someone gets missed.
Visit types, durations and buffers A fixed 15-minute grid is the most common shortcut and the most common complaint. A new patient consultation, a follow-up, a dressing change and a procedure take different amounts of time, and the schedule should know that. Give each visit type a default duration, allow a per-doctor override, and optionally a buffer after it for cleaning or notes.
Keep a base unit, such as 5 or 10 minutes, and make every duration a multiple of it. Slots then line up, the day view stays readable, and you avoid awkward gaps such as a 7-minute hole nobody can book. Also decide early whether a long visit type may start at any unit or only at certain times, for example procedures only in the first hour of a session.
Timed slots or tokens: support what the clinic actually runs Many outpatient clinics, especially in India, do not run to exact times. Patients book into a session, such as a doctor's morning OPD, and receive a token number. The doctor sees them in order. Forcing timed slots onto a token clinic produces a schedule that is wrong by mid-morning, and staff stop trusting it.
Design for both. A session has a mode: timed, where each appointment has a start and end, or token, where the session has a capacity and each booking takes the next number. An estimated time can still be shown for tokens, based on the average consultation length, as long as it is labelled as an estimate. Token allocation must be atomic, or two bookings will get the same number. In PostgreSQL a single statement does it:
-- Returns the new token, or no row if the session is full
UPDATE opd_session
SET last_token = last_token + 1
WHERE id = $1
AND last_token < capacity
RETURNING last_token;The row lock taken by the UPDATE serialises concurrent bookings for that session, so each caller gets a distinct number and the capacity check cannot be raced.
Let the database prevent double-booking Checking for a free slot in application code and then inserting is a race. Two requests can both see the slot as free and both insert. Locks in the application layer help, but they break as soon as there is a second app server, a background job or a data import. The reliable fix is a constraint the database enforces on every write.
PostgreSQL has exactly the right tool: an exclusion constraint over a time range. It rejects any appointment whose time overlaps another active appointment for the same doctor.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE appointment (
id bigserial PRIMARY KEY,
doctor_id bigint NOT NULL,
patient_id bigint NOT NULL,
visit_type text NOT NULL,
during tstzrange NOT NULL,
status text NOT NULL DEFAULT 'booked'
CHECK (status IN ('held', 'booked', 'arrived', 'in_consultation',
'done', 'cancelled', 'no_show')),
held_until timestamptz,
created_by bigint,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT no_overlap EXCLUDE USING gist (
doctor_id WITH =,
during WITH &&
) WHERE (status NOT IN ('cancelled', 'no_show'))
);A few details make this work in practice. The btree_gist extension lets the plain doctor_id column take part in a GiST index. Ranges default to an inclusive start and exclusive end, so a 10:00 to 10:15 visit does not clash with one at 10:15. The WHERE clause means cancelled and no-show appointments free their time. When a conflict happens, PostgreSQL raises SQLSTATE 23P01 (exclusion_violation), which the API should turn into a clear message such as "That slot was just taken, here are the next three free ones". Add the same kind of constraint per room or device where those are booked.
Online booking: hold, then confirm A patient who picks a slot and then spends two minutes typing their details should not lose it to someone faster, and should not block it forever if they abandon the form. Insert the appointment with status held and a held_until time a few minutes ahead. Because held rows count in the exclusion constraint, the slot is protected. On confirmation, switch the status to booked. A scheduled job running every minute cancels expired holds. That job alone is not enough: the free-slot query should treat holds past held_until as free, and the booking transaction should cancel any expired hold that overlaps the requested time before it inserts. Otherwise the constraint still sees the stale hold and rejects a booking for a slot the patient was shown as free.
Other rules worth deciding before launch rather than after:
How far ahead patients can book, and how close to the start time online booking closes. Whether online patients see every visit type, or only follow-ups and general consultations. Duplicate checks on mobile number before creating a new patient record. Limits on active future bookings per mobile number, to stop one person reserving a whole morning. Rate limiting and a simple bot check on the public booking endpoint. Time zones, reminders and the audit trail Store appointment times as timestamptz, which PostgreSQL keeps in UTC. Store schedule templates as local wall-clock times plus the clinic's IANA time zone name, such as Asia/Kolkata, and convert when generating slots. India has no daylight saving, but a group with a branch in a country that does will otherwise end up with slots an hour out twice a year. In Python, zoneinfo handles this:
from datetime import datetime, timedelta
from zoneinfo import ZoneInfo
def free_slots(day, sessions, booked, minutes, tz="Asia/Kolkata"):
"""sessions: [(time, time)] in clinic local time.
booked: [(start, end)] aware datetimes for this doctor."""
zone, step, slots = ZoneInfo(tz), timedelta(minutes=minutes), []
for open_t, close_t in sessions:
start = datetime.combine(day, open_t, tzinfo=zone)
close = datetime.combine(day, close_t, tzinfo=zone)
while start + step <= close:
end = start + step
if not any(s < end and start < e for s, e in booked):
slots.append(start)
start = end
return slotsReminders should be driven by appointment state, not by a separate list. When an appointment is rescheduled or cancelled, pending reminders for it are cancelled too, otherwise patients turn up for a slot they moved. If you use WhatsApp, business-initiated messages must use templates approved in advance, so get reminder wording approved before go-live.
Finally, keep an appointment history table: every status change, reschedule and cancellation with the user, time and a reason. It settles disputes at the front desk, shows which doctors' sessions overrun, and gives you honest no-show figures instead of guesses. Treat this data as part of the patient record when deciding access rights and retention.
A checklist before you build or buy Availability comes from templates and dated exceptions, not pre-generated slots. Visit types have their own durations, built on a common base unit. Token sessions and timed sessions are both supported if the clinic runs both. Overlaps are rejected by a database constraint, with a helpful message on conflict. Online bookings hold a slot briefly and expire cleanly. Leave and closures produce a list of affected patients to contact. Times are stored in UTC with the clinic's time zone kept alongside the schedule. Reminders follow appointment changes automatically. Every change is logged with who, when and why. If you are evaluating an off-the-shelf product, ask the vendor to show the double-booking case with two browsers at once, and to block a doctor's day that already has patients. How a system handles those two scenarios tells you most of what you need to know about its scheduling engine.
Infigini IT Services · 11 Kanwali Road, Dehradun 248001, India · info@infigini.com · +91 95684 87789