Doctor Appointment Booking System: Build One That Never Double-Books

A doctor appointment booking system needs four parts: slots generated from each doctor's schedule, a database rule that refuses overlapping bookings, reminders, and clear cancellation and no-show handling. In PostgreSQL, one exclusion constraint on provider and time range makes double-booking impossible. SMS reminders cost from $0.0083 a message on Twilio in the US, plus carrier fees.

If you would rather have it built, see how RAITHub would build this below.

This guide is for clinic groups, health founders and developers building patient booking into a product. It is engineering guidance, not legal or medical advice. RAITHub's client work includes a healthcare scheduling app; the code here was written for this post as an illustration, not taken from that or any client project. RAITHub builds web applications and installable PWAs, so "app" here means a booking app that runs in the browser, not a native mobile app.

Buy, build or hire: should I use an existing booking system?

Most single clinics should buy. Practice software already includes online booking, and general schedulers are inexpensive. Build when booking is part of a product you sell, or when your scheduling rules are too specific for any settings screen. Prices checked on 2 October 2026:

Option	Example and published price	Choose this when	Main risk
Practice management software with online booking	Cliniko from $45 a month for one practitioner; Jane from CAD $79 a month on Practice, with free SMS reminders	You run a clinic and want booking, records and billing in one place	Booking rules are limited to what the vendor supports
General scheduling tool or no-code template	Calendly free, or $10 to $16 a seat a month on paid plans	Simple consultations with no clinical data in the booking itself	Its pricing page does not mention a BAA or health data terms; check before patient details go in
Custom build	Quoted per scope	Booking is part of your product, or you need multi-branch, room, equipment or referral rules no tool supports	Concurrency and time-zone bugs if built carelessly; you own maintenance
Hybrid	Bought practice software plus a custom booking front end	The vendor's records and billing work, but its booking page does not fit your patients	Depends on the vendor's API for availability and booking

How do you generate appointment slots from a doctor's schedule?

Store the schedule, not the slots. Each provider has weekly working hours in their clinic's time zone, a slot length and exceptions (leave, training, a closed branch). Slots are computed for the date being viewed, and any slot that overlaps an existing appointment is hidden.

-- Weekly hours, in the clinic's own time zone (IANA name).
CREATE TABLE provider_schedule (
  provider_id  bigint   NOT NULL,
  weekday      smallint NOT NULL CHECK (weekday BETWEEN 0 AND 6), -- 0 = Sunday
  starts       time     NOT NULL,
  ends         time     NOT NULL CHECK (ends > starts),
  slot_minutes int      NOT NULL CHECK (slot_minutes > 0),
  tz           text     NOT NULL,                                  -- e.g. 'America/New_York'
  PRIMARY KEY (provider_id, weekday, starts)
);

-- Free slots for provider $1 on local date $2.
SELECT s AS slot_start,
       s + make_interval(mins => ps.slot_minutes) AS slot_end
FROM provider_schedule ps
CROSS JOIN LATERAL generate_series(
  ($2::date + ps.starts) AT TIME ZONE ps.tz,
  (($2::date + ps.ends) AT TIME ZONE ps.tz) - make_interval(mins => ps.slot_minutes),
  make_interval(mins => ps.slot_minutes)
) AS s
WHERE ps.provider_id = $1
  AND ps.weekday = extract(dow FROM $2::date)
  AND NOT EXISTS (
    SELECT 1 FROM appointment a
    WHERE a.provider_id = ps.provider_id
      AND a.status <> 'cancelled'
      AND a.during && tstzrange(s, s + make_interval(mins => ps.slot_minutes))
  )
ORDER BY s;

Adding a date to a time gives a local timestamp, and AT TIME ZONE turns it into an absolute instant for that clinic. Because the conversion happens per date, a 09:00 shift stays at 09:00 local time on both sides of a daylight-saving change. Leave and exceptions are best stored as blocking appointments with their own status, so the same check hides them.

The slot list is only a display. It can be stale by the time the patient clicks, which is why the next rule must live in the database.

How do you stop two patients booking the same appointment slot?

Let PostgreSQL refuse the second booking. An "is it free?" check followed by an insert leaves a gap in which two requests both see the slot as free. An exclusion constraint closes it: PostgreSQL's documentation shows EXCLUDE USING GIST (room WITH =, during WITH &&) rejecting any overlapping reservation for the same room, using the btree_gist extension to combine equality with range overlap (PostgreSQL range types). A doctor's calendar is the same problem.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE appointment (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  provider_id     bigint NOT NULL,
  patient_id      bigint,                 -- null for leave and blocked time
  during          tstzrange NOT NULL CHECK (NOT isempty(during)),
  status          text NOT NULL CHECK (status IN
                    ('held', 'booked', 'blocked', 'completed', 'no_show', 'cancelled')),
  hold_expires_at timestamptz,
  CONSTRAINT no_double_booking EXCLUDE USING gist (
    provider_id WITH =,
    during      WITH &&
  ) WHERE (status <> 'cancelled')
);

-- Hold the slot for 10 minutes while the patient confirms.
INSERT INTO appointment (provider_id, patient_id, during, status, hold_expires_at)
VALUES ($1, $2, tstzrange($3, $4), 'held', now() + interval '10 minutes');
-- A clash fails with SQLSTATE 23P01 (exclusion_violation):
-- tell the patient the slot has just gone, and show the next free ones.

Five details make it work in practice:

• Half-open ranges. tstzrange($3, $4) defaults to [) bounds, including the start and excluding the end, so a 09:00 to 09:15 slot and a 09:15 to 09:30 slot do not collide.
• Cancelling frees the slot. The constraint ignores cancelled rows, so a cancellation makes the time bookable again in the same transaction.
• Expired holds need a job. A partial constraint cannot reference now(), so a scheduled job flips holds past hold_expires_at to cancelled. Until it runs, they still block the slot.
• Rooms and equipment get their own constraint. If a procedure needs a room, add a booking row for the room with the same pattern, in the same transaction.
• Test it with real concurrency. Fire 20 simultaneous bookings at one slot in an automated test and assert that exactly one succeeds.

The same pattern protects vehicles in the car rental software guide. Rate-limit the booking endpoint too, so a script cannot hold every slot; rate limiting without Redis shows a way that needs no extra infrastructure.

How should appointment reminders work by email, SMS and WhatsApp?

Treat each reminder as a row with a send time, created when the appointment is booked and cancelled when the appointment is. A worker sends what is due. Channel choice is scope: each one adds templates, opt-in handling and costs.

Channel	Published cost	What to plan for
Email	Low; most providers price per thousand messages	Domain authentication so reminders do not land in spam; an unsubscribe for non-essential messages
SMS	From $0.0083 an outbound segment in the US on Twilio, plus carrier fees such as $0.0035 (AT and T) or $0.0045 (T-Mobile) (Twilio US SMS pricing)	Sender registration rules by country, opt-out keywords, and prices that differ by destination
WhatsApp	Charged per delivered template message since 1 July 2025, by category and recipient country; utility templates inside an open customer service window are free (WhatsApp Business Platform pricing)	Pre-approved templates, a Business account, and the right template category for reminders

-- Workers claim due reminders without sending any twice.
SELECT r.id, r.appointment_id, r.channel
FROM reminder r
WHERE r.send_at <= now()
  AND r.sent_at IS NULL
  AND r.cancelled_at IS NULL
ORDER BY r.send_at
LIMIT 100
FOR UPDATE SKIP LOCKED;

Keep reminder text minimal: a time, a place, and a link to confirm or cancel, with no diagnosis or treatment details, because messages are read on lock screens and shared phones. Consent to SMS and messaging, and what a reminder may contain, depend on your country's rules. This is general information; confirm with your adviser.

How do you handle cancellations and no-shows?

Write the policy down first, then encode it. Most clinics need the same few rules:

• A cancellation window. Patients can cancel or reschedule online until, say, 24 hours before; after that, they call. The window is a setting per clinic, not a constant in code.
• Freed slots go to a waitlist. On cancellation, offer the slot to waitlisted patients in order, each with a short hold, using the same held status and constraint.
• No-show as a status, not a deletion. After a grace period, staff mark no_show. The record stays for reporting and for any repeat-no-show policy.
• Fees only with consent. If you charge for late cancellations or no-shows, take the patient's agreement at booking and save the card through your payment provider. Whether you may charge, and how much, depends on local rules and your terms.
• An audit trail. Who booked, moved or cancelled each appointment, and when. The audit log design guide covers the pattern.

How do you handle time zones in a booking system?

Store instants, display local times. PostgreSQL's timestamptz stores an absolute moment and converts on display (PostgreSQL date and time types), so every appointment is unambiguous.

• Schedules belong to the clinic's time zone. Store an IANA name such as Europe/London, never a fixed offset like +01:00, which is wrong for half the year.
• Show the patient both when it matters. For telehealth across borders, show the time in the patient's zone and the clinic's, and put the zone in every reminder.
• Test the transition days. Book slots on the days clocks change, in the test suite, for every zone you serve.

How long does it take to build this yourself?

For a developer comfortable with PostgreSQL: one to three days for the schema, the constraint and the slot query; two to four weeks for a usable system with patient accounts, staff screens, reminders, cancellations and a waitlist. The main risk is not the happy path. It is the bugs that only appear under load or twice a year: two patients booked into one slot, and reminders an hour out after a clock change. Write the concurrency and time-zone tests before launch, not after the first complaint.

Why RAITHub for this

• Health scheduling work. RAITHub's client work includes a healthcare scheduling app.
• Messaging channels, shipped. PadhAI, the AI tutoring platform RAITHub built, runs on the web as a PWA and on WhatsApp and Telegram, so multi-channel delivery is familiar ground.
• Tests that prove the rules. PropDesk, a property management platform with Stripe rent collection and 4 roles, carries 1,024 automated tests. Booking systems get concurrency tests on every slot rule and permission tests per role.
• Roles at scale. Sundor Skin runs 12 staff roles and 88 permission codes on PostgreSQL with row-level security, the shape of a clinic with reception, doctors and managers across branches.
• Your data stays yours. RAITHub signs NDAs and DPAs and works inside your controls. Production and patient data stay in your own covered cloud account; development uses synthetic data.

When you don't need us

• One clinic, standard appointments. Turn on online booking in your practice software. It is already paid for.
• You need a native iOS or Android app. RAITHub builds web apps and PWAs, not native mobile apps.
• You need a certified EHR or regulated medical device software. Buy a certified product; RAITHub does not build or certify one, and has not shipped a regulated health product.
• You want hourly developers in your team. RAITHub works on fixed scope or as a dedicated team, not staff augmentation.

How RAITHub would build this

• Scope: provider schedules with exceptions and slot generation per clinic time zone; booking with short holds and a database-enforced no-double-booking constraint, including rooms where needed; reminders on the channels you choose (email, SMS, WhatsApp), each as its own scope item; cancellation windows, a waitlist and no-show tracking; staff and patient roles with an audit trail.
• Integration, if any: availability and bookings synced with your existing practice software through its API.
• Timeline: a booking MVP on fixed scope in 4 to 6 weeks; a backend or API that serves several branches and integrations in 6 to 12 weeks.
• You receive: automated tests and CI, including the concurrency and time-zone suites; handover docs and runbooks; and full IP under NDA.
• Next step: a free 15-minute technical audit, then a written fixed quote.

Read the healthtech software development guide for the wider design rules, see the healthtech industry page and the API and backend development service, then book the free 15-minute audit with your number of providers, branches, channels and the booking rule your current tool gets wrong.

Last reviewed: 2 October 2026. Vendor prices and documentation checked on 2 October 2026.

Frequently asked questions

How do I prevent double booking in an appointment system?

Enforce it in the database. In PostgreSQL, an exclusion constraint on provider ID and a tstzrange rejects any overlapping active appointment, even when two requests arrive at the same moment. The application catches the error, SQLSTATE 23P01, and offers the next free slots.

What features does a doctor appointment booking system need?

Provider schedules and slot generation, online booking with no double-booking, reminders, cancellation and rescheduling, a waitlist, no-show tracking, staff roles, an audit trail and correct time zones. Rooms, equipment and payments are common additions.

How much do SMS appointment reminders cost?

On Twilio, US outbound SMS starts at $0.0083 a segment plus carrier fees, and prices differ by country. WhatsApp charges per delivered template message by category and country, with utility templates free inside an open customer service window. Email is usually the lowest-cost channel.

Can I use Calendly for doctor appointments?

For simple consultations with no clinical details, it can work. Its pricing page does not mention a BAA or health data terms, so check its current terms before patient information goes into bookings, and confirm with your adviser.

How long does it take to build an appointment booking system?

The core schema, constraint and slot query take a competent developer one to three days. A usable system with patient accounts, staff screens, reminders and cancellations takes two to four weeks; RAITHub's fixed-scope MVP range is 4 to 6 weeks including tests and handover.

Has RAITHub built a healthcare booking system?

RAITHub's client work includes a healthcare scheduling app. It has not shipped a regulated health product, and the code in this guide was written for the guide, not taken from client work.