Project 1: Clinic Appointment System

You are building the database behind a small clinic's booking system. Receptionists book patients with doctors, doctors see their day, and the manager wants to know who is busy and who keeps missing appointments.

This project pulls together almost everything in the course: modeling, constraints, joins, aggregates, window functions, a stored procedure, concurrency, indexes, permissions and tests. Do it in order, and do not peek at the reference solution until you have tried each step.

Requirements#

1. The clinic has several doctors. Each belongs to one specialty and charges a consultation fee. A doctor can be marked inactive, and inactive doctors cannot take new bookings.
2. Patients are registered with a name, an optional email (unique when present) and a date of birth.
3. An appointment links one patient to one doctor for a time range, and has a status: booked, completed, cancelled or no_show.
4. A doctor can never have two non-cancelled appointments that overlap in time. This must hold even when two receptionists click "book" at the same instant.
5. An appointment must end after it starts.
6. Reception books through a single operation that validates the input and reports conflicts in plain language.
7. Reporting users can read data but never change it. Reception can book but cannot delete history.
8. The most common lookups (a doctor's day, a patient's history) must stay fast as the table grows.

Step 1: Design on paper#

Before writing SQL, list the entities, the relationships with their cardinalities, and the keys. Decide:

• Is specialty an attribute or its own table? (Requirement 1 hints at the answer.)
• What identifies an appointment: a surrogate key, or (doctor, start time)?
• What type should the times be, and why?

Draw it. Write one sentence per relationship in both directions.

Step 2: Build the schema#

The hard requirement is number 4. A plain UNIQUE (doctor_id, starts_at) stops two bookings starting at the same minute but allows 09:00 to 09:30 to overlap 09:15 to 09:45. You need a constraint that understands time ranges.

PostgreSQL has one: an exclusion constraint. It says "no two rows may have an equal doctor and overlapping time ranges". It needs the btree_gist extension so = can participate in a GiST index.

Write your own schema first. Then compare with this reference.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE specialties (
    specialty_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name         VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE doctors (
    doctor_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name    VARCHAR(100) NOT NULL,
    specialty_id INTEGER NOT NULL REFERENCES specialties (specialty_id),
    consult_fee  NUMERIC(8,2) NOT NULL CHECK (consult_fee > 0),
    active       BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE patients (
    patient_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    email         VARCHAR(255) UNIQUE,
    date_of_birth DATE NOT NULL CHECK (date_of_birth >= DATE '1900-01-01')
);

CREATE TABLE appointments (
    appointment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    doctor_id      INTEGER NOT NULL REFERENCES doctors (doctor_id),
    patient_id     INTEGER NOT NULL REFERENCES patients (patient_id),
    starts_at      TIMESTAMPTZ NOT NULL,
    ends_at        TIMESTAMPTZ NOT NULL,
    status         VARCHAR(12) NOT NULL DEFAULT 'booked'
                   CHECK (status IN ('booked', 'completed', 'cancelled', 'no_show')),
    notes          TEXT,
    CONSTRAINT valid_duration CHECK (ends_at > starts_at),
    CONSTRAINT no_double_booking EXCLUDE USING gist (
        doctor_id WITH =,
        tstzrange(starts_at, ends_at) WITH &&
    ) WHERE (status <> 'cancelled')
);

CREATE INDEX idx_appointments_patient ON appointments (patient_id, starts_at);

Things worth noticing:

• tstzrange(starts_at, ends_at) builds a range that includes the start and excludes the end, so a 09:00 to 09:30 appointment and a 09:30 to 10:00 one do not conflict. Back-to-back slots are legal.
• && is the "overlaps" operator for ranges.
• The WHERE (status <> 'cancelled') makes it a partial constraint. A cancelled slot is free to rebook.
• The exclusion constraint builds a GiST index on (doctor_id, range), which also serves "this doctor's appointments in a time window" queries. The extra index on (patient_id, starts_at) serves patient history.
• The design deliberately uses TIMESTAMPTZ. Clinics have patients who travel, and a booking is an exact instant.

Step 3: Load the seed data#

INSERT INTO specialties (name) VALUES ('General Practice'), ('Cardiology'), ('Dermatology');

INSERT INTO doctors (full_name, specialty_id, consult_fee, active) VALUES
    ('Dr. Anita Desai', 1,  40.00, TRUE),
    ('Dr. Marcus Webb', 1,  45.00, TRUE),
    ('Dr. Lila Ortega', 2, 120.00, TRUE),
    ('Dr. Sam Hughes',  3,  90.00, TRUE),
    ('Dr. Paul Kim',    2, 110.00, FALSE);

INSERT INTO patients (full_name, email, date_of_birth) VALUES
    ('Rohan Mehta',  'rohan@example.com',  '1988-04-12'),
    ('Sara Lopez',   'sara@example.com',   '1975-09-30'),
    ('Tom Becker',   'tom@example.com',    '1992-01-18'),
    ('Nadia Rahman', 'nadia@example.com',  '2001-07-05'),
    ('Li Wei',       'li@example.com',     '1983-11-23'),
    ('Olivia Grant', NULL,                 '1969-02-14'),
    ('Pedro Santos', 'pedro@example.com',  '1995-06-27'),
    ('Fatima Noor',  'fatima@example.com', '1979-12-08');

INSERT INTO appointments (doctor_id, patient_id, starts_at, ends_at, status) VALUES
    (1, 1, '2024-09-02 09:00+00', '2024-09-02 09:30+00', 'completed'),
    (1, 2, '2024-09-02 09:30+00', '2024-09-02 10:00+00', 'completed'),
    (2, 3, '2024-09-02 09:00+00', '2024-09-02 09:30+00', 'completed'),
    (3, 4, '2024-09-02 10:00+00', '2024-09-02 10:45+00', 'completed'),
    (1, 5, '2024-09-03 09:00+00', '2024-09-03 09:30+00', 'no_show'),
    (1, 1, '2024-09-03 11:00+00', '2024-09-03 11:30+00', 'completed'),
    (3, 6, '2024-09-03 10:00+00', '2024-09-03 10:45+00', 'completed'),
    (4, 7, '2024-09-03 14:00+00', '2024-09-03 14:30+00', 'completed'),
    (2, 2, '2024-09-03 09:30+00', '2024-09-03 10:00+00', 'cancelled'),
    (1, 3, '2024-09-04 09:00+00', '2024-09-04 09:30+00', 'completed'),
    (2, 4, '2024-09-04 09:00+00', '2024-09-04 09:30+00', 'no_show'),
    (2, 8, '2024-09-04 10:00+00', '2024-09-04 10:30+00', 'completed'),
    (4, 1, '2024-09-04 14:00+00', '2024-09-04 14:30+00', 'completed'),
    (4, 5, '2024-09-04 14:30+00', '2024-09-04 15:00+00', 'no_show'),
    (1, 6, '2024-09-05 09:00+00', '2024-09-05 09:30+00', 'completed'),
    (3, 7, '2024-09-05 10:00+00', '2024-09-05 10:45+00', 'cancelled'),
    (3, 2, '2024-09-05 11:00+00', '2024-09-05 11:45+00', 'completed'),
    (2, 1, '2024-09-05 09:00+00', '2024-09-05 09:30+00', 'booked'),
    (1, 8, '2024-09-06 09:00+00', '2024-09-06 09:30+00', 'booked'),
    (4, 6, '2024-09-06 14:00+00', '2024-09-06 14:30+00', 'booked'),
    (3, 3, '2024-09-06 10:00+00', '2024-09-06 10:45+00', 'booked');

Dr. Paul Kim is inactive and has no appointments, which will matter for the reports. Set your session to UTC (SET TIME ZONE 'UTC';) so the times print as written.

Prove requirement 4 works. This must fail:

INSERT INTO appointments (doctor_id, patient_id, starts_at, ends_at)
VALUES (1, 4, '2024-09-04 09:15+00', '2024-09-04 09:45+00');
-- ERROR:  conflicting key value violates exclusion constraint "no_double_booking"

And this must succeed, since the cancelled slot is free and a back-to-back booking does not overlap:

INSERT INTO appointments (doctor_id, patient_id, starts_at, ends_at)
VALUES (2, 5, '2024-09-03 09:30+00', '2024-09-03 10:00+00');

Roll that second one back afterward if you want the numbers below to match.

Step 4: Answer the manager's questions#

Write each query yourself. The expected results use the seed data exactly as loaded.

A. A doctor's day. Show Wednesday 4 September: doctor, start time, patient and status, excluding cancelled, sorted by doctor then time.

     doctor      | start |   patient    |  status
-----------------+-------+--------------+-----------
 Dr. Anita Desai | 09:00 | Tom Becker   | completed
 Dr. Marcus Webb | 09:00 | Nadia Rahman | no_show
 Dr. Marcus Webb | 10:00 | Fatima Noor  | completed
 Dr. Sam Hughes  | 14:00 | Rohan Mehta  | completed
 Dr. Sam Hughes  | 14:30 | Li Wei       | no_show

B. Doctor scorecard. For every doctor (including the inactive one), show total appointments, completed, no-shows, and the no-show rate as a percentage of appointments that were completed or no-show. Protect against dividing by zero.

     doctor      | total | completed | no_shows | no_show_pct
-----------------+-------+-----------+----------+-------------
 Dr. Anita Desai |     7 |         5 |        1 |        16.7
 Dr. Marcus Webb |     5 |         2 |        1 |        33.3
 Dr. Lila Ortega |     5 |         3 |        0 |         0.0
 Dr. Sam Hughes  |     4 |         2 |        1 |        33.3
 Dr. Paul Kim    |     0 |         0 |        0 |

A LEFT JOIN from doctors keeps Paul Kim, and NULLIF gives him a NULL rate instead of an error.

C. Revenue. Completed visits times the doctor's fee. Dr. Lila Ortega should lead with 360.00, then Dr. Anita Desai with 200.00, Dr. Sam Hughes 180.00, Dr. Marcus Webb 90.00, Dr. Paul Kim 0.00.

D. Busiest day. Count non-cancelled appointments per date. Wednesday 4 September wins with 5.

E. Patients to follow up. Patients with at least one no-show and no completed visit at all. Only Li Wei qualifies (two no-shows). Use NOT EXISTS.

F. Patient timeline. For Rohan Mehta, list each non-cancelled appointment with the start of his next one, using LEAD. His four appointments chain from 2 Sep to 3 Sep, then 4 Sep, then 5 Sep.

G. Free slots. For Dr. Anita Desai on 4 September, list the free 30-minute slots from 09:00 to 11:00. Generate candidate slots with generate_series(timestamptz '2024-09-04 09:00+00', timestamptz '2024-09-04 11:00+00', interval '30 minutes'), then anti-join against her non-cancelled appointments. You should find four free slots: 09:30, 10:00, 10:30 and 11:00.

Step 5: The booking procedure#

Reception should never write raw INSERTs. Build one procedure that validates, books, and translates database errors into plain language.

Requirements for book_appointment(doctor_id, patient_id, starts_at, minutes):

• The length must be between 10 and 120 minutes.
• The doctor must exist and be active.
• A time conflict raises a readable error that names the doctor.

Reference:

CREATE OR REPLACE PROCEDURE book_appointment(
    p_doctor_id  INTEGER,
    p_patient_id INTEGER,
    p_starts_at  TIMESTAMPTZ,
    p_minutes    INTEGER DEFAULT 30
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_minutes NOT BETWEEN 10 AND 120 THEN
        RAISE EXCEPTION 'appointment length must be between 10 and 120 minutes, got %', p_minutes;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM doctors WHERE doctor_id = p_doctor_id AND active) THEN
        RAISE EXCEPTION 'doctor % does not exist or is not taking appointments', p_doctor_id;
    END IF;

    INSERT INTO appointments (doctor_id, patient_id, starts_at, ends_at)
    VALUES (p_doctor_id, p_patient_id, p_starts_at,
            p_starts_at + make_interval(mins => p_minutes));
EXCEPTION
    WHEN exclusion_violation THEN
        RAISE EXCEPTION 'doctor % is already booked at %', p_doctor_id, p_starts_at;
END;
$$;

Test all four paths: a good booking, a bad length, the inactive doctor (5), and a conflicting time.

The concurrency test#

Requirement 4 says even when two people book at the same instant. Prove it with two psql sessions.

 Session A                                        Session B
 BEGIN;
 CALL book_appointment(1, 4, '2024-09-09 09:00+00');
                                                  BEGIN;
                                                  CALL book_appointment(1, 5, '2024-09-09 09:00+00');
                                                  -- waits...
 COMMIT;
                                                  -- ERROR: doctor 1 is already booked at ...

Session B blocks until A finishes, then fails cleanly. There is no race to handle in application code, because the constraint is enforced by the database. Compare with the check-then-insert approach from Module 7 and write down why that one would let both bookings through.

Step 6: Indexes and plans#

Create a larger test set (a few hundred thousand appointments across 200 doctors using generate_series is enough, with times that avoid overlaps) and run EXPLAIN ANALYZE on:

1. a doctor's appointments for one day
2. a patient's full history, ordered by date
3. the status counts for one week

Confirm each uses an index. Find out which query benefits from the exclusion constraint's index and which from idx_appointments_patient. Drop one index, re-run, and describe what changed.

Step 7: Permissions#

Create three roles and apply least privilege:

• clinic_reporting: read-only on everything.
• clinic_reception: can read doctors and patients, insert and update patients, insert appointments, update appointment status, and cannot delete anything.
• clinic_admin_ops: owns schema changes, used only by migrations.

Hide patients' dates of birth from reporting with a column-level grant or a view. Connect as each role and verify, with a failing statement for each prohibited action.

Step 8: Tests#

Write assertion queries or a pgTAP file for these. Each one should be a test that fails if someone removes the protection.

• Overlapping appointments for one doctor are rejected, and back-to-back ones are accepted.
• A cancelled appointment does not block rebooking its slot.
• A negative fee, an appointment ending before it starts, and an unknown status are rejected.
• book_appointment rejects a 5-minute and a 200-minute appointment.
• Reporting cannot UPDATE, and reception cannot DELETE.

Definition of done#

• Schema created from a clean database by a single migration script
• Seed data loads and all seven reports match the expected output
• Double-booking is impossible, shown in two concurrent sessions
• book_appointment handles all four paths
• Plans for the three hot queries use indexes on realistic data
• Three roles exist and each prohibited action is demonstrated to fail
• Tests pass and run against a fresh disposable database

Practice#

1. Add a rule that a patient cannot have overlapping appointments with different doctors either. Write the second exclusion constraint and the failing test.
2. Add a doctor_schedules table of weekly working hours, and make book_appointment reject times outside them.
3. Add rescheduled_from BIGINT REFERENCES appointments, and write a procedure that cancels one appointment and books its replacement in a single transaction.
4. Build a materialized view of daily utilization per doctor (booked minutes divided by working minutes) with a unique index, and refresh it concurrently.
5. Write the migration that adds appointments.reminder_sent_at to a table with 50 million rows without blocking writes. State each step and its lock.