Clinic Booking System

Overview#

Every service business with appointments has the same core problem: show customers the times that are really free, and never let two of them get the same one. Clinics, salons, tutors and repair shops all need it, and most get it subtly wrong at the edges: time zones, cancellations, two people clicking at once.

This concept is a booking system for a fictional clinic with four staff and six services. The live demo covers the public booking flow, customer self-service (cancel and reschedule) and an admin day calendar with drag-and-drop. A dev panel next to it prints the HTTP request and the PostgreSQL statements behind every click, so you can see the backend behaviour, not just the screens.

I later used the same approach in a real booking system for a dental clinic in Lahore. Its booking widget follows the same flow: pick a date, see only the free times, with closed days greyed out and a "Next available" shortcut.

My role#

I did all of it: the data model, the slot algorithm, the API design, the demo interface and this write-up. It is my own concept project, not client work. The clinic, staff and customers are made up.

The problem#

Booking looks like a form plus a calendar. The hard parts are elsewhere:

• Availability is derived, not stored. A free slot is working hours, minus breaks and days off, minus existing appointments, minus "too soon". It has to be recalculated on every request because any of those inputs can change.
• Double booking is a race condition. Checking "is this slot free?" and then inserting leaves a gap. Two requests can both pass the check and both insert. A UI that hides taken slots does not close that gap.
• Time zones. The clinic works in its own zone, the visitor may be somewhere else, and the server should store neither: only UTC instants.
• Changes must not lose data. A reschedule that fails halfway must leave the original booking in place.

Approach & architecture#

The database is the judge. In the production design an appointment stores its time as a tstzrange, and one constraint forbids overlaps per staff member. The btree_gist extension lets a GiST index combine plain equality (staff_id) with range overlap (&&). The partial WHERE clause means cancelled rows never block anything.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE appointments (
  id          bigserial PRIMARY KEY,
  staff_id    int  NOT NULL REFERENCES staff(id),
  service_id  int  NOT NULL REFERENCES services(id),
  customer    text NOT NULL,
  email       citext NOT NULL,
  during      tstzrange NOT NULL,
  status      text NOT NULL DEFAULT 'confirmed',
  CONSTRAINT appointments_no_overlap
    EXCLUDE USING gist (staff_id WITH =, during WITH &&)
    WHERE (status <> 'cancelled')
);

So the booking service does not check first. It inserts and translates the error. When two transactions insert overlapping rows at the same time, the second one waits on the index entry of the first, then fails with 23P01 once the first commits. The API maps that to 409 Conflict with a "that slot was just taken" message.

The demo cannot run PostgreSQL in the browser, so booking.ts reimplements the same rule with the same half-open range semantics and throws the same error code. The API layer in api.ts does not know the difference:

export function conflictFor(db: Db, staffId: number, start: number, end: number, ignoreId?: number) {
  return db.appointments.find(
    (a) => a.id !== ignoreId && a.staffId === staffId && a.status !== "cancelled" && overlaps(start, end, a.start, a.end),
  );
}
// [start, end) ranges: 10:00–10:30 and 10:30–11:00 touch but do not overlap
export const overlaps = (aStart: number, aEnd: number, bStart: number, bEnd: number) => aStart < bEnd && bStart < aEnd;

Slots are computed in clinic time, stored in UTC. Working hours are minutes from midnight in Asia/Karachi. For each candidate start, zonedToUtc converts the clinic-local time to a UTC instant using Intl.DateTimeFormat offsets (two passes, so zones with daylight saving also work). The visitor's chosen zone only affects formatting. With "anyone available", each start time is offered once and given to the least busy qualified staff member that day.

Rescheduling is one transaction. Lock the row with SELECT … FOR UPDATE, update its range, commit. If the new range overlaps another booking, the update fails, the transaction rolls back and the old time stays booked. That is what you see when you drop a booking onto another one in the admin calendar: it snaps back and the dev panel shows ROLLBACK.

Key challenges#

• Not trusting the UI. It is tempting to rely on the slot list, because taken slots are hidden. The race button in the demo shows why that is not enough: two requests for the same slot both pass validation, and only the constraint stops the second.
• Half-open ranges. Back-to-back appointments must be allowed. Using [) bounds everywhere (in the SQL and in the TypeScript) means 10:30 can end one booking and start the next.
• Time zone display without time zone storage. I never store a local time. The trickiest part was converting "09:00 on 12 November in the clinic" to UTC without a date library; I wrote it against Intl and kept it small enough to read in the source viewer.
• Validation in one place. The booking DTO is a zod schema. The form validates with it before sending, and the API validates with it again, so the error messages are identical on both sides. In NestJS this would be a DTO class with class-validator.
• Drag-and-drop with a keyboard alternative. Calendar blocks use pointer events with pointer capture and snap to 15 minutes. Focused blocks also move with the arrow keys, and the detail panel has buttons and a staff selector, so nothing depends on a mouse.

Tech stack#

• Demo: TypeScript, React and Next.js, zod for the booking DTO, Tailwind CSS. Logic is in framework-free modules (booking.ts for the domain, api.ts for the controller and service layer). State is saved in localStorage.
• Production design: NestJS with an availability service, a bookings module and a roles guard; PostgreSQL with tstzrange, btree_gist and the exclusion constraint above; Next.js for server-rendered booking pages and a client-side admin calendar.

Results#

What the demo does today:

• Calculates free slots from weekly hours, breaks, days off, existing bookings and a one-hour lead time, for one staff member or "anyone".
• Shows the same slots in the clinic's zone, your browser's zone, London or New York.
• Creates bookings, cancels them (the slot is free again straight away) and reschedules them atomically.
• Simulates two concurrent bookings for one slot: exactly one is written, the other gets 409.
• Admin calendar with drag-and-drop between times and staff, keyboard moves, day-off toggles and a 403 for customers.
• Prints the request, status and SQL for every action in the dev panel, plus the full schema.

I have not run load tests or measured anything against a real database for this concept. It is a concept build without real users.

Try the demo#

Open the booking system demo. Things to try:

1. Pick "Scale & polish", choose a slot, fill in your details, then press simulate 2 people booking this slot at once and read the interleaved transactions in the dev panel.
2. Change "Show times in" to New York and compare the slot labels with the clinic times under them.
3. Switch the role to admin, open the calendar and drag one booking onto another. It snaps back and the panel shows 23P01 and ROLLBACK.
4. Cancel a booking under My bookings and watch its slot reappear in the picker.

What I would do next#

• Run the real NestJS service against PostgreSQL in Docker and test the race with parallel Supertest requests.
• Add signed manage links and confirmation emails sent from a queue after commit.
• Add a week view and recurring blocks (holidays, training days) for the admin calendar.
• Support buffers between services, such as cleaning time after a treatment.