Run an AI Schema-Design Review Before the DDL Ships

Tier: Foundation · Workflow area: Schema & Change Management · ~30 minutes

A schema review is pattern-matching against a large catalog of known mistakes, which is precisely what LLMs are good at. This playbook makes AI the always-available first reviewer on every table that ships — with the human as the ruling reviewer, because half of what looks like an anti-pattern is a deliberate trade-off the model cannot know about.

When to use

• Every new table headed to productionthe review costs ten minutes; renaming a column or changing a key type after launch costs a migration project (pb-10).
• App-team DDL crossing your deskORM-generated migrations deserve a database-eyed review, and AI gives you a thorough first pass even on a busy day.
• Before the first big data loadthe last cheap moment to fix types, keys and partitioning is before the table has data.

When NOT to use

• As the final authoritythe memo advises; the human who knows the access patterns and the roadmap decides. An AI-approved schema nobody senior read is an unreviewed schema.
• For pure style warsnaming conventions and plural-vs-singular debates burn review credibility. Configure the prompt to skip style unless it causes real ambiguity.
• Retro-reviewing stable production tables without a drivera working schema with data in it is only worth reviewing when something (scale, new workload, migration) creates a decision.

The recipe

1. 1.

   Collect the DDL and the workload truth. A schema is only right relative to its queries. Gather: the full proposed DDL (tables, constraints, indexes), the expected top queries or API access patterns, growth estimates (rows/day, retention), and write/read mix.

2. 2.

   Prompt for a structured review memo with severity levels.

   Copy

   You are a senior database engineer reviewing proposed PostgreSQL 16
   DDL before it ships. Below: the DDL, the expected access patterns,
   growth estimates and write/read mix.
   
   Produce a REVIEW MEMO:
   For each finding: [BLOCKER] / [WARN] / [CONSIDER], the table.column,
   the issue, why it bites AT SCALE (use my growth numbers), and the
   concrete fix as DDL.
   
   Review, in order:
   1. KEYS — PK choice and type (int vs bigint vs UUID trade-offs for
      this insert rate), FK coverage, natural-key uniqueness gaps.
   2. TYPES — money in float [BLOCKER], timestamps without time zone,
      varchar(255) reflexes, text vs enum vs lookup table.
   3. NULLABILITY — columns nullable by omission vs by meaning;
      NOT NULL + DEFAULT opportunities.
   4. INDEXES — do the proposed indexes serve the stated queries?
      key order, missing FK indexes, redundant/overlapping indexes.
   5. ANTI-PATTERNS — EAV, JSON-as-schema-dodge, wide rows, soft-delete
      columns without partial-index support, missing created/updated
      audit columns, polymorphic FKs.
   6. SCALE — partitioning candidacy against my retention numbers,
      fill-factor for update-heavy tables.
   Skip naming style. End with: what is GOOD about this design.
   

   The severity split keeps the memo actionable — blockers stop the ship, warns get a decision, considers get a line in the ticket.

3. 3.

   Verify the load-bearing claims empirically. Two spot-checks catch most model overreach. First, index-vs-query claims — build the schema empty and check the planner's intent with realistic row counts faked via stats, or simply load a synthetic million rows:

   Copy

   CREATE TABLE orders_review AS TABLE orders WITH NO DATA;
   INSERT INTO orders_review
   SELECT gen.i, (gen.i % 50000) + 1, now() - (gen.i % 365) * interval '1 day',
          (ARRAY['new','paid','shipped','refunded'])[1 + gen.i % 4]
   FROM generate_series(1, 1_000_000) AS gen(i);
   ANALYZE orders_review;
   EXPLAIN SELECT * FROM orders_review
   WHERE customer_id = 42 ORDER BY ordered_at DESC LIMIT 20;
   

   Second, type claims — confirm version-specific advice (identity columns vs serial, UUID v7 availability, enum alteration behavior) against the docs for your engine version.

4. 4.

   Rule on every finding in writing. The human pass over the memo produces the actual review record:

   Copy

   [BLOCKER] amount NUMERIC not FLOAT        -> ACCEPTED, DDL fixed
   [WARN] status as text, no constraint      -> ACCEPTED, CHECK constraint added
   [WARN] UUID v4 PK fragments index at
          this insert rate                   -> REJECTED — multi-region write
                                               requirement, fragmentation
                                               priced in; noted in ADR-114
   [CONSIDER] partition by month             -> DEFERRED — revisit at 100M rows,
                                               calendar reminder set
   

   Rejected findings with reasons are the most important rows: they document deliberate trade-offs so the next reviewer (or the next AI pass) does not re-litigate them.

5. 5.

   Ship the ruled memo with the DDL. The migration ticket carries the DDL, the memo, and the rulings. Findings that changed the design are fixed pre-merge — this is the entire point: the fix happened while it was still a text edit.

Make it yours

• SQL Serveradd clustered-index choice to the review list explicitly (the PK-as-clustered-key default is a real design decision at scale), plus datetime2 vs datetime, and NVARCHAR width reflexes; ask the model to reason about page splits for the proposed key against your insert pattern.
• MySQLhave the review cover storage engine confirmation, PK size (secondary indexes carry the PK), utf8mb4 charset/collation correctness, and the implicit-rounding behaviors of older date/decimal defaults; ask specifically about invisible primary keys if your version enforces them.
• Oraclereview sequence/identity strategy, VARCHAR2 byte-vs-char semantics for multilingual data, and the empty-string-is-NULL behavior anywhere the app distinguishes them; partitioning advice is richer here, so give the model your edition and licensing reality.

Quality check

• Blockers are actually blockinga memo whose [BLOCKER] items shipped anyway teaches everyone to ignore the review. Severity must have teeth.
• Every ruling is writtenaccepted, rejected-with-reason, or deferred-with-trigger. An unruled finding is a review that half-happened.
• Scale claims used your numbers"this will be slow at scale" only counts when the memo multiplied your rows/day by your retention. Generic scale fear is noise.
• The model reviewed the workload, not just the DDLa schema review without access patterns is a style review. If you could not supply the queries, the finding quality tells you.