# AGENTS.md: PostgreSQL work

## Migrations
- Every change is a new migration file in the repo's tool (`migrations/`, Flyway, sqitch, Prisma, etc.). Never edit an applied migration.
- Make migrations safe on live tables:
  - `CREATE INDEX CONCURRENTLY` (outside a transaction) for big tables.
  - Add columns nullable or with a constant default; backfill in batches; then add `NOT NULL` (via a `CHECK ... NOT VALID` + `VALIDATE CONSTRAINT`).
  - Rename or drop in two deploys: stop using it first, remove later.
  - Set `lock_timeout` (e.g. `SET lock_timeout = '5s'`) in migrations that take locks.

## Schema
- Primary keys `bigint generated always as identity` or `uuid` (v7 if available); be consistent with the existing tables.
- `timestamptz` for time, `text` over `varchar(n)`, `numeric` for money, `jsonb` only for truly unstructured data.
- Enforce rules in the database: `NOT NULL`, `UNIQUE`, `CHECK`, foreign keys with an explicit `ON DELETE`.
- Index every foreign key column used in joins.

## Queries
- Parameterized queries only. Check new queries with `EXPLAIN (ANALYZE, BUFFERS)` on realistic data and paste the plan in the PR.
- Paginate with keyset (`WHERE id > $1 ORDER BY id LIMIT 50`) instead of large `OFFSET`.
- No `SELECT *` in application code.

## Never
Run `DROP`, `TRUNCATE` or an unbounded `DELETE`/`UPDATE` against a shared database without explicit approval.