Postgres Schema Design skill for Claude Code

A schema is done well when invalid data cannot be written, every column’s type says exactly what it holds, the common queries need no gymnastics, and the next migration is an additive change rather than a rewrite. Constraints are documentation the database enforces; put rules there first and in application code second.

Workflow

- [ ] 1. List the entities, their identifiers, and the queries that will hit them
- [ ] 2. Choose keys
- [ ] 3. Choose a type for every column (table below)
- [ ] 4. Add constraints: NOT NULL, CHECK, UNIQUE, FK with explicit ON DELETE
- [ ] 5. Decide normalization vs. denormalization per relationship
- [ ] 6. Decide what (if anything) goes in JSONB
- [ ] 7. Add indexes for FKs and known access paths
- [ ] 8. Review against the checklist and write the review note

1. Start from access patterns

Before writing DDL, write down:

• The 5-10 most frequent queries (filters, sorts, joins) and the write paths.
• Expected row counts at 1 year and growth rate. A 50k-row table and a 500M-row table get different designs (partitioning, BRIN, archiving).
• Who deletes data and what must happen to children (cascade, block, orphan-safe).
• Tenancy: if the app is multi-tenant, tenant_id goes on every tenant-owned table now, not later.

If you are reviewing an existing schema, inspect it first:

\d+ orders                                   -- psql: columns, types, constraints, indexes
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint WHERE conrelid = 'orders'::regclass;

2. Keys

Situation	Primary key
Internal table, single database	bigint GENERATED ALWAYS AS IDENTITY
IDs exposed in URLs, generated client-side, or merged across systems	uuid, version 7 (time-ordered)
Pure join table	composite PK (left_id, right_id)
Natural key that is truly immutable (ISO country code)	the natural key

• Use identity columns, not serial (identity is the SQL-standard replacement and avoids sequence ownership and permission quirks).
• Prefer UUIDv7 over UUIDv4 for primary keys: v4 is random and scatters inserts across the B-tree, inflating WAL and cache misses. PostgreSQL 18 ships uuidv7(); on 17 and earlier generate v7 in the application or with an extension.
• Never use int (4-byte) for a PK on a table that can grow; exhausting 2^31 forces a painful rewrite.
• Keep a surrogate PK even when a natural key exists, and enforce the natural key with UNIQUE. Emails, SKUs, and usernames change.

3. Types

Data	Use	Avoid
Text	text (+ CHECK (char_length(x) <= n) if a limit is a business rule)	varchar(255) by habit, char(n)
Timestamps	timestamptz	timestamp without time zone for real events
Calendar date (birthday, due date)	date	timestamptz at midnight
Money	numeric(19,4) or bigint minor units + currency column	money, float, real, double precision
Counts, quantities	integer / bigint	numeric without need
Flags	boolean NOT NULL DEFAULT false	smallint 0/1, nullable booleans
Fixed small set of states	text + CHECK (status IN (...)) or a lookup table	native ENUM when values will be removed or reordered
Ranges (validity windows, bookings)	tstzrange, daterange	two columns without a check
IP addresses	inet / cidr	text
Semi-structured, schemaless payload	jsonb	json (unless key order/duplicates must be preserved)
Binary blobs > ~1 MB	object storage + URL/key column	bytea for user uploads

Enum guidance: ALTER TYPE ... ADD VALUE is cheap, but removing or renaming a value requires recreating the type. If the set changes with product decisions, use a CHECK constraint (alterable) or a lookup table with an FK (lets you attach metadata like display labels and sort order).

4. Constraints

• NOT NULL by default. Each nullable column needs a reason: “unknown” is different from “not applicable”, and NULL means unknown in SQL three-valued logic.
• CHECK for invariants that live on one row:

CREATE TABLE orders (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id     bigint NOT NULL REFERENCES tenants (id),
  customer_id   bigint NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,
  status        text   NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  subtotal_cents bigint NOT NULL CHECK (subtotal_cents >= 0),
  currency      char(3) NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
  placed_at     timestamptz NOT NULL DEFAULT now(),
  shipped_at    timestamptz,
  CONSTRAINT orders_shipped_after_placed CHECK (shipped_at IS NULL OR shipped_at >= placed_at)
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_tenant_placed_idx ON orders (tenant_id, placed_at DESC);

• UNIQUE for business keys, including scoped ones: UNIQUE (tenant_id, slug). Case-insensitive uniqueness: CREATE UNIQUE INDEX ON users (lower(email)); (or a nondeterministic ICU collation if the team already uses them).
• UNIQUE treats NULLs as distinct. If “only one NULL allowed” is the rule, use UNIQUE NULLS NOT DISTINCT (PG15+).
• Soft-delete-aware uniqueness: CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL;
• Foreign keys always state ON DELETE:

Child relationship	ON DELETE
Child is part of the parent (order_items of order)	CASCADE
Child is an independent record that references parent (orders of customer)	RESTRICT (default NO ACTION is similar)
Reference is optional and may outlive the parent	SET NULL on a nullable column

• Postgres does not index FK columns automatically. Index every FK column used in joins or parent deletes; an unindexed FK makes DELETE on the parent scan the child.
• No overlapping intervals (bookings, price validity): exclusion constraint.

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE room_bookings
  ADD CONSTRAINT no_double_booking
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

5. Normalization tradeoffs

Default to third normal form. Denormalize only with a named reason and a mechanism that keeps the copy correct.

Situation	Decision
Value describes the parent entity (customer name)	Keep it on the parent; join
Value is a historical fact at the time of an event (price paid, shipping address used)	Copy it onto the event row; it is not duplication, it is history
Expensive aggregate read far more than written (comment count)	Denormalized counter updated in the same transaction, or a materialized view refreshed on a schedule
Many-to-many	Join table with composite PK and FKs; never comma-separated IDs or arrays of FKs
Optional 1:1 attributes for a minority of rows	Separate table keyed by the parent PK
Polymorphic association (“comments on posts or photos”)	One nullable FK column per target + CHECK (num_nonnulls(post_id, photo_id) = 1); avoid (target_type, target_id) which cannot have an FK

6. JSONB: when and how

Use JSONB when the shape is genuinely variable or owned by someone else:

• Third-party webhook payloads and API responses kept for audit or replay.
• User-defined custom fields and per-tenant settings.
• Sparse attributes that differ per product category, rarely filtered.

Do not use JSONB for:

• Anything you join on, enforce uniqueness on, or reference by FK.
• Fields filtered or sorted in hot queries (promote them to real columns).
• Data with a stable known shape “to avoid migrations”. Migrations are cheap; untyped data with three spellings of createdAt is not.

Rules when you do use it:

ALTER TABLE products ADD COLUMN attrs jsonb NOT NULL DEFAULT '{}'
  CHECK (jsonb_typeof(attrs) = 'object');
-- Containment queries (@>) on the whole document:
CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
-- One hot key: expression index instead of a full GIN
CREATE INDEX products_attrs_brand_idx ON products ((attrs ->> 'brand'));

• ->> returns text; cast before comparing numbers: (attrs ->> 'weight')::numeric.
• Updating one key rewrites the whole value (TOAST). Large JSONB documents updated often are a write-amplification trap; split them out.
• Validate shape in the application (Zod/Pydantic) at write time; add CHECKs for keys that must exist.

7. Naming conventions

• snake_case, lowercase, no quoted identifiers. Quoted mixed-case names ("userId") force quotes forever.
• Pick plural or singular table names once and keep it; the codebase’s existing choice wins.
• PK id; FK <referenced_singular>_id (customer_id).
• Timestamps end in _at (created_at), dates in _on or _date, booleans start with is_/has_.
• Name constraints and indexes explicitly: <table>_<cols>_idx, <table>_<cols>_key, <table>_<rule>_check. Auto-generated names make migrations and error messages opaque.
• Avoid reserved words as column names (user, order, group).

8. Standard columns

• created_at timestamptz NOT NULL DEFAULT now() on every table.
• updated_at only if something maintains it (trigger or ORM). A stale updated_at is worse than none.
• Soft delete (deleted_at) only when the product needs undo or retention; it infects every query and unique constraint. Prefer an archive table for compliance retention.

Gotchas

• timestamp (without time zone) silently drops the offset; one server in a different TZ corrupts ordering. Use timestamptz and store UTC.
• now() is the transaction start time; every row inserted in one transaction gets the same value. Use clock_timestamp() when you need wall-clock per row.
• NOT IN (subquery) returns no rows if the subquery yields a NULL. A nullable column that “should never be NULL” will eventually bite this way.
• Adding NOT NULL to a large existing table scans it under an exclusive lock; plan it as a migration (see schema-migrations).
• Generated columns: PG18 adds VIRTUAL (computed on read, the new default) alongside STORED. Only STORED columns can be indexed; specify the kind explicitly.
• Partitioning is not a default. Consider it past roughly 100M rows or when you must drop old data by time; it complicates unique constraints (partition key must be included).

Verification

1. Run the DDL against a scratch database (createdb scratch && psql scratch -f schema.sql).
2. Try to insert invalid rows for each constraint and confirm each is rejected.
3. Run EXPLAIN on the queries from step 1 with representative data to confirm the indexes are usable.
4. Check FK columns lacking indexes:

SELECT c.conrelid::regclass AS tbl, a.attname AS fk_column
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]);

Output: schema review note

## Schema review: orders, order_items

**Blocking**
- `orders.total` is `double precision` -> `bigint` cents + `currency char(3)`.
- `order_items.order_id` FK has no index; parent deletes will seq-scan.

**Should fix**
- `status` is a native ENUM; product plans to retire `on_hold` -> CHECK constraint.
- `metadata jsonb` holds `customer_email`, filtered in the admin search -> real column.

**Fine as is**
- UUIDv7 PKs (IDs are public in URLs).

**Assumptions**: ~2M orders/year, single region, no partitioning needed yet.