Spec to Schema: Designing PostgreSQL Tables with AI

In Part 3 the AI interviewed us and wrote a one-page spec whose data sketch names five entities and zero column types. Today that sketch becomes real PostgreSQL DDL — and along the way we catch the AI making two classic mistakes, and discover one rule it could never have known without us. That rule is the thesis of this whole series.

Prompt with requirements, not table lists

The instinct, when asking an AI for a schema, is to dictate tables and columns — at which point you're just typing DDL slowly, with extra steps. The better move is to state requirements and invariants and let the model do the translation it's genuinely good at. Here's the actual prompt, verbatim from the part-04 commit:

Turn docs/spec.md's data-model sketch into PostgreSQL 18 DDL.
Requirements: identity primary keys, timestamptz for every timestamp,
a uniqueness rule so two active appointments can never share a slot
but a cancelled slot can be re-booked, and the confirmation code must
be unique. For every non-obvious decision add one sentence saying
WHY. Do not create tables the spec doesn't need.

Read what's in there. Not one column name — but four hard requirements: identity primary keys, timestamptz everywhere, uniqueness for the confirmation code, and one genuinely subtle business rule about cancelled slots that we'll spend a whole section on. Plus two guard rails from lessons already learned: "say WHY" (so the schema arrives with its reasoning attached, reviewable), and "no tables the spec doesn't need" (Part 3 taught us the AI's appetite for scope).

The DDL

The full result is docs/schema.md in the companion repo; here's the heart of it — the two tables that carry the business:

CREATE TABLE patient (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name    varchar(200) NOT NULL,
    phone        varchar(30)  NOT NULL,
    email        varchar(200),
    created_at   timestamptz  NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ix_patient_phone ON patient (phone);

CREATE TABLE appointment (
    id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id        bigint       NOT NULL REFERENCES patient(id),
    starts_at         timestamptz  NOT NULL,
    status            varchar(20)  NOT NULL DEFAULT 'Booked',
    confirmation_code varchar(6)   NOT NULL,
    created_at        timestamptz  NOT NULL DEFAULT now()
);

CREATE INDEX ix_appointment_patient  ON appointment (patient_id);
CREATE UNIQUE INDEX ix_appointment_slot ON appointment (starts_at)
    WHERE status NOT IN ('Cancelled', 'NoShow');
CREATE UNIQUE INDEX ix_appointment_code ON appointment (confirmation_code);

Two more tables complete the set: queue_entry (created at kiosk check-in, one per appointment, with checked_in_at and called_at) and chat_message (sender, body, sent time). A patient is created the first time a phone number books — no login, ever; the six-character confirmation code is the patient's only credential, which is exactly why it gets a unique index of its own.

The index the AI would never suggest

Look again at the middle index — it deserves its own section:

CREATE UNIQUE INDEX ix_appointment_slot ON appointment (starts_at)
    WHERE status NOT IN ('Cancelled', 'NoShow');

That's a partial unique index: uniqueness enforced only over rows matching the WHERE clause. Left to itself, the AI proposed the obvious thing — a plain UNIQUE on starts_at. Perfectly reasonable, and quietly catastrophic: once any appointment ever existed at 10:15, no one could ever book 10:15 again — a cancelled appointment would block its own slot forever. The business rule is that two active appointments can't share a slot, but a cancelled or no-show one frees it. The partial index says exactly that, in one statement, enforced by the database itself no matter what application code does.

Here's the part to sit with: that rule had to come from us. It's in the prompt above — "a cancelled slot can be re-booked" — because no amount of model intelligence could have derived how our clinic treats cancelled slots. The AI knew the syntax instantly once the rule was stated; it could never have known the rule. AI knows syntax. You know the rules. That's the division of labour this whole series runs on, and this index is its cleanest exhibit. (It also pays off twice: in Part 6, catching this index's violation is how the booking code survives two patients racing for the same slot.)

What the schema reviewer caught

So the AI nailed the partial index once told the rule. It also, in the same breath, made two of the oldest mistakes in the book.

If the timestamp-versus-timestamptz distinction is new to you, our translation table on PostgreSQL data types covers it properly — the short version is that timestamptz stores an unambiguous instant while timestamp stores a wall-clock reading with no idea where the wall is. And if indexes themselves feel fuzzy, our indexes explainer is the prerequisite: this part leans on it twice.

Every decision, defended

Because the prompt demanded a WHY for every non-obvious choice, the schema arrived with its reasoning attached — which made reviewing it a conversation instead of an archaeology dig. The decisions worth keeping:

To read the design document exactly as this part left it — DDL, reasoning and all — check out the tag:

git checkout part-04

As always in the companion repo, the commit message contains the full prompt, and the mistakes are recorded right alongside it.

We have a spec and a schema, and still not one line of C#. That changes now: in Part 5 the AI scaffolds the Blazor Server solution, swaps SQLite for PostgreSQL, and turns this DDL into EF Core entities and a first migration — under the rule that makes AI scaffolding safe: never ship a file you haven't read.