AI coding rules // PostgreSQL

SQL and PostgreSQL rules for AI coding agents

Agents write SQL that is correct on a toy dataset and wrong or slow on a real one. These rules cover PostgreSQL schema design, queries and migrations, whether the SQL is written by hand or produced through an ORM, and they begin by checking the server major version before using version-specific features.

Schema guidance includes identity or uuid primary keys instead of serial, and indexing foreign key columns yourself, since Postgres does not. Query guidance covers bind parameters, NULL semantics, keyset pagination instead of large offsets, ON CONFLICT upserts and SKIP LOCKED job queues. Migrations on live tables get their own section, starting with a lock_timeout so a blocked DDL statement fails instead of queuing every query behind it.

Folder: rules/sql-postgres/ · sql-postgres.mdc, CLAUDE.md, AGENTS.md

What the PostgreSQL rules tell your agent

Schema designPrimary keys: bigint GENERATED ALWAYS AS IDENTITY or uuid. Do not start new tables on serial.
IndexesPostgres does not index foreign key columns automatically. Index the referencing column when you join on it or delete parent rows.
Transactions and lockingKeep transactions short. A transaction left open blocks vacuum and holds locks; never wait on user input or a network call inside one.
Migrations on live tablesSet SET lock_timeout = '5s'; at the top of migrations so a blocked DDL statement fails instead of queuing every query behind it.
SecurityApplications connect as a role with only the privileges they need. No superuser, no ownership of tables in production.

First rule of each section in sql-postgres.mdc, quoted as shipped.

Mistakes these rules are written to prevent

Always use bind parameters ($1, ?, or named parameters in your driver). Never concatenate input; identifiers from input must be checked against an allow list and quoted with quote_ident/format('%I').
Select the columns you need. SELECT * in application code breaks when columns are added and fetches unused data.
NULL never equals anything. Use IS NULL, IS DISTINCT FROM, and beware NOT IN (subquery): one NULL in the subquery makes it return no rows. Use NOT EXISTS.

Source: “Queries”.

Checks the agent runs before it says done

Example commands from the PostgreSQL file (swap in your repo's own):

Migrate up and down: your migration tool's up, down, then up again
Lint: squawk <migration.sql> or sqlfluff lint (whichever is configured)
Plans: EXPLAIN (ANALYZE, BUFFERS) on seeded data

Run the migration up and down locally, EXPLAIN new queries on seeded data, and run the repo's SQL linter (sqlfluff or squawk) if configured. Report the plan for any query on a large table.

Before and after: SQL: NULL traps and NOT IN correctness review

Prompt card 38 · Stack: SQL (PostgreSQL semantics; most rules apply to any SQL database) · Type: Correctness review · Best model: a frontier reasoning model

What the card is for

Find queries that return wrong results because of three-valued logic: NOT IN with a nullable subquery, = NULL, <> comparisons that skip NULL rows, COUNT(column) vs COUNT(*), outer joins turned into inner joins by a WHERE filter, and aggregates over empty sets.

Worked example from the card

Before

-- Intent: active customers who have not placed an order in 2026, with their 2025 spend.
SELECT c.id, c.email, SUM(o.total_cents) AS spend_2025
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.status <> 'closed'
  AND o.created_at >= '2025-01-01' AND o.created_at < '2026-01-01'
  AND c.id NOT IN (SELECT customer_id FROM orders WHERE created_at >= '2026-01-01')
GROUP BY c.id, c.email;

Schema note: orders.customer_id is nullable (guest checkouts); customers.status is nullable (legacy rows).

Expected AI output (abridged)

1. NOT IN: any 2026 guest order has customer_id NULL, so `c.id NOT IN (..., NULL)` is
   never true. Data: one guest order in 2026 -> the query returns zero rows.
2. c.status <> 'closed' drops customers with status NULL. Data: a legacy customer with
   NULL status and 2025 orders is missing. If NULL means active, use
   c.status IS DISTINCT FROM 'closed'.
3. The WHERE on o.created_at turns the LEFT JOIN into an inner join: customers with no
   2025 orders disappear instead of showing spend 0. Move the date filter into ON.
4. SUM over no matching rows is NULL; COALESCE to 0.

After

SELECT c.id, c.email, COALESCE(SUM(o.total_cents), 0) AS spend_2025
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.created_at >= '2025-01-01' AND o.created_at < '2026-01-01'
WHERE c.status IS DISTINCT FROM 'closed'
  AND NOT EXISTS (
    SELECT 1 FROM orders o26
    WHERE o26.customer_id = c.id AND o26.created_at >= '2026-01-01'
  )
GROUP BY c.id, c.email;

Excerpt from card 38; the prompt itself is in the pack.

Using these rules in Cursor, Claude Code and AGENTS.md tools

Cursor

Put _base.mdc and sql-postgres.mdc in .cursor/rules/. Globs: db/**/*.sql, **/*.sql, migrations/**.

Claude Code

Root CLAUDE.md, base first. Path-scoped variant .claude/rules/sql-postgres.md for **/*.sql, db/**, migrations/**; PostToolUse hook: sqlfluff fix <file> (if configured).

AGENTS.md tools (Codex, Gemini CLI, Aider)

Base then stack in one AGENTS.md. Gemini CLI needs context.fileName; Aider needs --read AGENTS.md. Put this file in db/ or the directory that holds migrations. Agents use the AGENTS.md nearest to the file being edited, so a nested copy overrides a root one for that subtree.

Related stacks

← All stacks