Case Study 2 — A Complete Capstone: The Healthcare Clinic

A second worked capstone — the healthcare clinic domain — which raises the stakes on integrity, security, and privacy. It shows how the same assembly applies to a domain where correctness and PII handling are paramount.

1. Requirements & questions

A clinic tracks patients, providers, appointments, prescriptions, and visits. Questions: a patient's appointment history? a provider's schedule for a day? which appointments are upcoming/no-show? active prescriptions per patient? appointment volume per provider per month? It must protect patient PII (HIPAA-style obligations — Chapter 32).

2. Data model (ER)

Entities: patient, provider, appointment, visit, prescription, medication, diagnosis. Relationships: patient→appointments and provider→appointments are 1:N; an appointment may produce a visit; a visit has many prescriptions (each referencing a medication) and diagnoses — patient↔medication is effectively M:N through prescriptions. A scheduling constraint: a provider can't have two overlapping appointments (a range/exclusion-constraint candidate — Chapter 16).

3. Schema (DDL) — integrity is critical here

CREATE TABLE patients (
    patient_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    dob        date NOT NULL,
    email      text UNIQUE,                 -- PII
    phone      text,                        -- PII
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE providers (
    provider_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL, specialty text NOT NULL
);
CREATE TABLE appointments (
    appointment_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id  integer NOT NULL REFERENCES patients(patient_id),
    provider_id integer NOT NULL REFERENCES providers(provider_id),
    scheduled_at timestamptz NOT NULL,
    duration_min integer NOT NULL CHECK (duration_min > 0),
    status text NOT NULL DEFAULT 'scheduled'
        CHECK (status IN ('scheduled','completed','cancelled','no_show'))
);
CREATE TABLE prescriptions (
    prescription_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    visit_id    integer NOT NULL REFERENCES visits(visit_id) ON DELETE CASCADE,
    medication_id integer NOT NULL REFERENCES medications(medication_id),
    dosage text NOT NULL, start_date date NOT NULL, end_date date,
    CHECK (end_date IS NULL OR end_date >= start_date)
);

Heavy use of constraints (this domain demands integrity): status domains, positive duration, date ordering, required references. Overlapping-appointment prevention can use a PostGIS-free range exclusion constraint on (provider_id, tstzrange(scheduled_at, scheduled_at + duration)) (Chapter 16) — so the database itself forbids double-booking a provider.

4–5. Data & core queries

Seed realistic patients/providers/appointments; a generator for volume. Core queries:

-- A patient's appointment history
SELECT a.scheduled_at, p.name AS provider, a.status
FROM appointments a JOIN providers p ON p.provider_id=a.provider_id
WHERE a.patient_id = :pid ORDER BY a.scheduled_at DESC;

-- Provider's schedule for a day (half-open range, Ch. 8)
SELECT a.scheduled_at, pt.name AS patient, a.duration_min
FROM appointments a JOIN patients pt ON pt.patient_id=a.patient_id
WHERE a.provider_id = :prov
  AND a.scheduled_at >= :day AND a.scheduled_at < :day + interval '1 day'
ORDER BY a.scheduled_at;

-- No-show rate per provider per month (aggregation)
SELECT p.name, date_trunc('month', a.scheduled_at) AS month,
       COUNT(*) FILTER (WHERE a.status='no_show')::numeric / COUNT(*) AS no_show_rate
FROM appointments a JOIN providers p ON p.provider_id=a.provider_id
GROUP BY p.name, month ORDER BY month;

(Note COUNT(*) FILTER (...) for the conditional rate, and half-open date ranges — Chapters 7–8.)

6–8. Indexes, transactions, views

• Indexes: appointments(patient_id), appointments(provider_id, scheduled_at) (the schedule query), prescriptions(visit_id) — justified and EXPLAIN-verified.
• Transaction: "complete a visit" = mark the appointment completed, create the visit, insert prescriptions and diagnoses — all atomic (Ch. 26).
• Views: an upcoming_appointments view; a patient_summary view that excludes sensitive columns for staff who shouldn't see full PII.

9. Security & privacy (elevated here — Ch. 32)

This domain makes Chapter 32 concrete:

• Least-privilege roles: front-desk staff (scheduling, no clinical data), clinicians (full clinical access), billing (financial only) — each granted the minimum.
• Hide PII with views/column grants: the front-desk role sees a view without diagnoses/full contact details.
• Row-level security could restrict a provider to their own patients.
• Encryption in transit (TLS) required; sensitive fields candidates for column-level encryption.
• Audit logging (trigger-based, Ch. 21) on access to clinical records.
• Right-to-erasure planned: how to delete a patient's PII while preserving anonymized aggregates (interacts with FKs and audit logs — a documented design decision).

10–12. App layer, operations, documentation

• App layer (Ch. 29): a parameterized repository; transactions for multi-step clinical operations; specific error handling.
• Operations (Ch. 38): tested backups (with the extra weight that medical data loss is catastrophic), monitoring.
• Documentation: README, data dictionary, query catalog, and a design-decisions doc emphasizing the integrity and privacy choices (why these constraints, the double-booking exclusion constraint, the role design, the PII/erasure plan, why PostgreSQL).

The result — and why it impresses

A clinic database where the constraints and security model are the stars: the database structurally prevents double-booking, invalid statuses, and unauthorized PII access, and the design-decisions doc reasons about privacy law. For a domain where correctness and confidentiality are non-negotiable, demonstrating that you made integrity and security structural — not hopeful — is exactly the judgment employers in regulated industries want.

Discussion questions

1. Why does this domain warrant more constraints and stricter security than, say, a blog?
2. How does an exclusion constraint prevent provider double-booking, and why is that better than app-code checks?
3. How do least-privilege roles + views enforce that front-desk staff can't see clinical PII?
4. What makes "right to erasure" tricky here (FKs, audit logs), and how would you design for it?
5. ⭐ Compare this capstone's emphasis (integrity/security) to the library capstone's (queries/operations). How does the domain shape what your portfolio piece should foreground?