# Postgres Agents Schema Reviewer

> Use when reviewing a PostgreSQL schema or DDL design before it ships, or auditing an existing schema for structural problems. Prevents shipping tables with no primary key, un-indexed foreign keys, multi-tenant tables with no RLS strategy, SERIAL where IDENTITY belongs, and timestamp instead of timestamptz for event times. Covers a deterministic schema review checklist (snake_case naming, IDENTITY vs SERIAL, missing PK, FK indexing, redundant indexes, RLS coverage, jsonb vs CSV-in-text, timestamptz, normalization), severity grading, cite-sibling-skill per finding, GOOD / NEEDS-WORK / POOR verdict. Keywords: schema review, DDL review, database design review, table design, naming convention, missing primary key, foreign key index, RLS coverage, normalization, IDENTITY vs serial, review my schema, is this schema good, audit database design, schema checklist

- Skill: `impertio-studio/postgres-agents-schema-reviewer` (Agent Skill, multi-file: 4 files)
- Install (CLI): `npx skillmds@latest add impertio-studio/postgres-agents-schema-reviewer`
- Raw SKILL.md: https://api.skillmd.com/api/skills/impertio-studio/postgres-agents-schema-reviewer/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: MIT
- Author: Impertio-Studio (https://skillmd.com/u/impertio-studio)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/impertio-studio/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](references/methods.md) : full 16-check table, severity rubric, verdict rules, report schema
- [references/examples.md](references/examples.md) : complete worked schema reviews, before/after DDL per check, version-annotated
- [references/anti-patterns.md](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