Postgres Agents Schema Reviewer

postgres-agents-schema-reviewer

Quick Reference :

This skill turns Claude into a schema design reviewer. It is an orchestration skill, not a DDL syntax reference: it defines a fixed 16-point checklist, a severity grading scheme, and which sibling skill to cite for each finding. Run every check against every table. Never approve a schema by inspection alone.

Review workflow :
  1. PARSE   -- collect every CREATE TABLE, column, constraint, index;
               build the foreign-key graph across ALL tables first
  2. CHECK   -- run all 16 checks (S-1..S-16) against every table
  3. GRADE   -- assign each hit a severity; mark choices that may be deliberate
  4. REPORT  -- emit structured findings + one overall verdict

Severity grades :

• CRITICAL : a structural defect that corrupts data or breaks tenancy. Verdict cannot be GOOD.
• WARNING : a design flaw that bites at scale or in maintenance.
• INFO : style or context-dependent; the author decides.

Verdict : GOOD (no CRITICAL, no WARNING) / NEEDS-WORK (WARNING only) / POOR (any CRITICAL).

ALWAYS build the FK graph before grading: an un-indexed FK or a missing-PK is invisible when one table is read in isolation. NEVER hard-fail a deliberate denormalization or a flagged choice without first asking the author for the rationale. NEVER spend the review on cosmetic naming while a CRITICAL is unaddressed.

When To Use This Skill :

ALWAYS use this skill when :

• A user asks "review this schema", "is this database design good", or pastes DDL for feedback
• Auditing an existing schema, a migration, or a PR that adds or alters tables
• Another agent produced CREATE TABLE DDL and it must be checked before it ships

NEVER use this skill for :

• Reviewing SQL queries (use postgres-agents-query-reviewer)
• Reviewing a migration's locking and rollout safety (use postgres-agents-migration-reviewer)
• Writing new DDL from scratch (use postgres-core-schema-design or the relevant postgres-syntax-* skill)

Decision Trees :

Grading a finding :

A check fired. What severity?
  |
  |-- Does it corrupt data integrity or break tenant isolation
  |   (no PK -> no row identity; multi-tenant table with no RLS)?
  |     YES --> CRITICAL. Verdict becomes POOR.
  |
  |-- Does it degrade performance or maintainability at scale
  |   (un-indexed FK, redundant index, normalization defect)?
  |     YES --> WARNING.
  |
  |-- Is the schema correct and workable, just not ideal style?
        YES --> INFO. The author decides.

Is this a defect or a deliberate choice? :

A check fired on something that COULD be intentional
(denormalization, a wide jsonb column, a missing NOT NULL)?
  |
  |-- Is there a comment / doc / commit message stating the intent?
  |     YES --> report as INFO "intentional, documented". Do not escalate.
  |
  |-- No stated rationale?
        --> report the finding AND ask the author:
            "Is this deliberate? If so, document why."
            Do NOT silently hard-fail it.

Single table vs the whole schema :

About to grade S-1 (PK), S-3 (FK index), S-6 (redundant index), S-10 (normalization)?
  |-- These need the WHOLE schema. A FK points at another table;
  |   a redundant index needs the sibling index; a 3NF violation
  |   needs the dependency graph.
  |   --> Refuse to grade them until every CREATE TABLE is parsed.
  |-- S-4, S-5, S-9, S-12, S-14 are per-column and per-table.
        --> Safe to grade table-by-table.

The Review Checklist :

Run every check. Each check has a trigger, a rule, a severity, and a cite-skill. Full per-check rationale and DDL examples are in references/methods.md and references/examples.md.

S-1 : Table with no primary key

ALWAYS flag a CREATE TABLE with no PRIMARY KEY and no promotable unique key. NEVER assume "the app handles identity". Severity : CRITICAL. Cite : postgres-core-schema-design. WHY : without a PK a row has no stable identity; UPDATE/DELETE by row, logical replication, and most ORMs all break. Fix : add id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY or a real natural key.

S-2 : Multi-tenant table with no RLS or tenant-scoping strategy

ALWAYS flag a table holding rows for multiple tenants (tenant_id, org_id, account_id column) that has neither RLS enabled nor a documented scoping plan. Severity : CRITICAL. Cite : postgres-core-rls-policies. WHY : every query must remember the tenant filter by hand; one forgotten WHERE tenant_id = ? leaks another tenant's data. Fix : ENABLE ROW LEVEL SECURITY + a tenant policy, or document the enforced scoping layer.

S-3 : Foreign-key column with no index

ALWAYS flag a column with a REFERENCES clause that has no covering index. NEVER assume the FK creates one: it does not. Severity : WARNING. Cite : postgres-impl-indexing-strategy. WHY : joins on the FK and parent DELETE/key-UPDATE (cascade enforcement) seqscan the child table. Fix : CREATE INDEX ON child (parent_id).

S-4 : SERIAL instead of GENERATED AS IDENTITY

ALWAYS flag serial / bigserial columns. NEVER treat them as equivalent to identity columns. Severity : WARNING. Cite : postgres-syntax-dml-ddl. WHY : serial exposes a detached sequence with its own ownership and GRANT quirks, and ALTER TYPE breaks the dependency. Fix : id bigint GENERATED ALWAYS AS IDENTITY (v10+, SQL-standard).

S-5 : Non-snake_case / camelCase identifiers

ALWAYS flag camelCase or PascalCase table and column names. Severity : WARNING. Cite : postgres-core-schema-design. WHY : PostgreSQL folds unquoted identifiers to lower case, so a camelCase name must be double-quoted in EVERY query forever; one missed quote is a silent wrong identifier. Fix : snake_case everywhere.

S-6 : Redundant index

ALWAYS flag an index whose leading columns are already the prefix of another index on the same table. Severity : WARNING. Cite : postgres-impl-schema-archaeology. WHY : an index on (a) is fully served by an existing index on (a, b); the extra index only adds write cost and bloat. Fix : drop the redundant index.

S-7 : RLS enabled but FORCE missing where the owner runs DML

ALWAYS flag a table with ENABLE ROW LEVEL SECURITY whose owning role also runs application DML, when FORCE ROW LEVEL SECURITY is absent. Severity : WARNING. Cite : postgres-core-rls-policies. WHY : "Table owners normally bypass row security"; if the app connects as the owner, every policy is silently skipped. Fix : ALTER TABLE t FORCE ROW LEVEL SECURITY, or run the app as a non-owner role.

S-8 : Structured data stored as text / CSV

ALWAYS flag a text column holding delimited or structured data (CSV, key=value lists, JSON-in-text). Severity : WARNING. Cite : postgres-syntax-jsonb. WHY : the database cannot index, constrain, or query the inner fields; every read parses strings in the app. Fix : a jsonb column with a GIN index, a real child table, or a typed array.

S-9 : timestamp without time zone for event times

ALWAYS flag timestamp (alias timestamp without time zone) used for a moment in time (created_at, occurred_at). Severity : WARNING. Cite : postgres-core-schema-design. WHY : timestamp stores no offset, so the same value means different instants in different sessions; DST and multi-region break it. Fix : timestamptz.

S-10 : Normalization violation

ALWAYS flag a clear 1NF/2NF/3NF violation: a repeating group (phone1, phone2, phone3), a partial dependency, or a transitive dependency. Severity : WARNING. Cite : postgres-core-schema-design. WHY : repeating groups and redundant columns cause update anomalies and inconsistent data. Fix : extract the offending columns into their own table. Deliberate denormalization is acceptable ONLY when documented with its reason.

S-11 : Logically required column left nullable

ALWAYS flag a column that is logically mandatory (email on a users table, a FK that every row must have) declared without NOT NULL. Severity : WARNING. Cite : postgres-core-schema-design. WHY : nullable required columns let half-built rows exist; every consumer must defend against a NULL that should be impossible. Fix : add NOT NULL, with a DEFAULT if one is sensible.

S-12 : money type

ALWAYS flag the money type. Severity : INFO. Cite : postgres-core-schema-design. WHY : money has a locale-dependent format and fixed fractional precision tied to lc_monetary. Fix : numeric(precision, scale) plus an explicit currency column.

S-13 : Application tables in the public schema

ALWAYS flag a production application that puts all its tables in public. Severity : INFO. Cite : postgres-core-schema-design. WHY : public is shared and historically broadly privileged; a dedicated schema gives a clean privilege boundary. Fix : a named schema (app, per-tenant, etc.).

S-14 : char(n) instead of text or varchar

ALWAYS flag char(n) (blank-padded character(n)). Severity : INFO. Cite : postgres-core-schema-design. WHY : char(n) pads with trailing spaces and gives no performance benefit over text / varchar in PostgreSQL. Fix : text, or varchar(n) when a hard length cap is intended.

S-15 : Nullable boolean used as an undocumented tri-state

ALWAYS flag a nullable boolean where NULL silently carries a third meaning. Severity : INFO. Cite : postgres-core-schema-design. WHY : "unknown / not-applicable / pending" hidden in a NULL boolean is invisible to every reader. Fix : document the NULL meaning, or use an explicit enum / text status with a CHECK.

S-16 : Missing CHECK constraints for known invariants

ALWAYS flag a column with an obvious domain rule (price >= 0, status IN (...), end_date >= start_date) and no CHECK enforcing it. Severity : INFO. Cite : postgres-core-schema-design. WHY : an invariant only in application code is not enforced for other writers, imports, or manual fixes. Fix : add the CHECK constraint.

Output Format :

Emit findings as a list, then one verdict line. Per finding :

[SEVERITY] <schema.table[.column]>
  Problem : <what is structurally wrong>
  Fix     : <concrete DDL change>
  Cite    : <sibling skill>
  Note    : <only if the choice may be deliberate -- ask for rationale>

End with exactly one verdict :

Verdict : NEEDS-WORK   (0 CRITICAL, 3 WARNING, 2 INFO)

GOOD requires zero CRITICAL and zero WARNING. Any CRITICAL forces POOR. See references/methods.md for the full report schema.

Anti-Patterns :

(Reviewer anti-patterns. Full cause + symptom + fix in references/anti-patterns.md)

• Bikeshedding : debating cosmetic naming while a missing PK (S-1) goes unmentioned
• Denormalization-by-ambush : flagging S-10 as a defect without asking if it is deliberate
• Table-in-isolation : grading FK / PK / redundant-index checks without the full FK graph
• Severity inflation : grading a char(n) INFO as CRITICAL, or a missing PK as INFO
• Citing nothing : reporting a finding without the sibling skill that fixes it
• Rubber-stamping : declaring a schema GOOD without running all 16 checks

Reference Links :

• references/methods.md : full 16-check table, severity rubric, verdict rules, report schema
• references/examples.md : complete worked schema reviews, before/after DDL per check, version-annotated
• references/anti-patterns.md : reviewer anti-patterns with cause + symptom + fix

See Also :

• postgres-agents-query-reviewer : reviews SQL queries for correctness and performance
• postgres-agents-migration-reviewer : reviews migration safety and locking
• postgres-core-schema-design : naming, types, normalization, schema layout reference
• postgres-core-rls-policies : RLS policy design, FORCE ROW LEVEL SECURITY, tenant isolation
• postgres-impl-indexing-strategy : index access methods, FK indexing, multicolumn indexes
• Vooronderzoek section : §4 (Indexing), §12 (RLS), §19 (Anti-Patterns), §22 (conventions)
• Official docs : https://www.postgresql.org/docs/17/ddl-rowsecurity.html