# 01 — Choosing the Engine & Modeling the Data

## Engine selection

### Rule: Postgres is the default. Deviation requires a written justification.
Postgres circa 2026 covers relational, document (JSONB), key-value (UNLOGGED
tables, hstore), pub/sub (LISTEN/NOTIFY), job queues (SKIP LOCKED),
time-series (native partitioning, BRIN; TimescaleDB extension), full-text
search, and vectors (pgvector). One engine means one backup story, one
security model, one operational skillset, and real transactions across all of
it. Every additional datastore multiplies failure modes and removes
cross-store transactional consistency.

A second engine is justified only when a concrete, measured requirement
exceeds what Postgres does, not when a category label sounds appealing.

### Rule: Know the genuine win conditions for each alternative.
- **Document DB (MongoDB, DynamoDB doc mode):** wins only when the access
  pattern is truly aggregate-oriented (always read/write whole documents, no
  cross-document joins or transactions) AND scale exceeds a single Postgres
  primary. Schema flexibility alone is not a reason — JSONB gives you that.
- **Key-value (DynamoDB, Redis-as-store):** wins for known-key lookups at
  extreme throughput with single-digit-ms SLOs and simple access patterns
  designed up front. DynamoDB punishes access patterns you didn't model.
- **Columnar/OLAP (ClickHouse, BigQuery, DuckDB):** wins for analytical scans
  over billions of rows. Do not run analytics on the OLTP primary; do not run
  OLTP on a columnar store (no fast point updates). DuckDB for embedded/local
  analytics over files.
- **Time-series (TimescaleDB, ClickHouse, InfluxDB):** wins at high ingest
  rates (>~100k rows/s sustained) with time-bucketed queries, retention, and
  downsampling. Below that, partitioned Postgres tables with BRIN indexes are
  fine — prefer TimescaleDB (stays in Postgres) over a separate system.
- **Dedicated vector DB:** see `07-vector-and-ai.md`. Short version: pgvector
  until >~10–50M vectors or hard multi-tenant isolation/recall requirements.
- **Redis (or Valkey, the BSD-licensed fork):** cache, ephemeral state, rate
  limiting, leaderboards, streams. Not a system of record. See
  `05-reliability-and-scale.md`.
- **Graph DB:** wins only for deep variable-length traversals (4+ hops) as the
  core workload. Friend-of-friend (≤2–3 hops) is a recursive CTE in Postgres.

### Rule: Never introduce a datastore to dodge learning the current one.
"Postgres is slow" is almost always a missing index, a missing partition, an
N+1, or bloat — audit those (files 03, 05) before proposing migration.

## Normalization vs pragmatic denormalization

### Rule: Model in 3NF first; denormalize only with a measured reason.
Start normalized: every fact stored once, updates touch one row, constraints
enforceable. Denormalization is an optimization with a maintenance contract —
every duplicated value needs a documented owner (trigger, application code, or
batch job) that keeps it consistent, and an audit query that detects drift.

Acceptable denormalizations:
- Cached aggregates (`order.total_cents`, `post.comment_count`) maintained by
  trigger or transactionally in the same write path. Always keep the source
  rows so the value is recomputable.
- Snapshot copies where history must not change: `order_items.unit_price_cents`
  copied from `products.price_cents` at order time. This is not denormalization
  — it is correct temporal modeling. Never join to current price for an old order.
- Read-model tables/materialized views for reporting, refreshed on a schedule.

```sql
-- BAD: customer name duplicated onto orders "to avoid a join"
CREATE TABLE orders (id ..., customer_name text, customer_email text, ...);

-- GOOD: join for mutable facts, snapshot immutable-at-time facts
CREATE TABLE orders (id ..., customer_id bigint NOT NULL REFERENCES customers(id),
                     shipping_address jsonb NOT NULL, ...); -- address frozen at order time
```

### Rule: Arrays and JSONB do not exempt you from first normal form for relational data.
If you ever need to query, join, constrain, or update an element individually,
it is a child table, not an array column.

## JSONB usage rules

### Rule: JSONB is for data whose shape you don't control or don't query relationally.
Legitimate: external API payloads, webhook bodies, user-defined custom fields,
sparse per-type attributes, raw event capture. Illegitimate: fields your own
application defines and queries — those are columns.

Hard rules:
- Any JSONB key used in a WHERE clause, JOIN, or ORDER BY of a hot query gets
  promoted to a real (or generated) column:
  ```sql
  ALTER TABLE events ADD COLUMN user_id bigint
    GENERATED ALWAYS AS ((payload->>'user_id')::bigint) STORED;
  CREATE INDEX ON events (user_id);
  ```
- Index JSONB with GIN only for containment (`@>`) / existence queries; use
  `jsonb_path_ops` opclass for `@>`-only workloads (smaller, faster).
- Validate shape with CHECK constraints where structure is required:
  `CHECK (jsonb_typeof(payload->'items') = 'array')`.
- Never store money, foreign keys, or status enums inside JSONB.
- JSONB columns are updated by full-value rewrite; high-frequency partial
  updates to large documents cause write amplification and bloat — split hot
  mutable fields out.

## Primary key strategy

### Rule: Surrogate keys only. Never natural keys.
Emails change, SSNs are PII and get corrected, slugs get renamed, "unique"
business codes collide after the next acquisition. Natural keys also cascade
into every child table and index. Enforce natural uniqueness with a UNIQUE
constraint, not the PK.

```sql
-- BAD
CREATE TABLE users (email text PRIMARY KEY, ...);
-- GOOD
CREATE TABLE users (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email citext NOT NULL UNIQUE,
  ...);
```

### Rule: bigint identity by default; UUIDv7 when IDs leave the system.
- `bigint GENERATED ALWAYS AS IDENTITY`: 8 bytes, perfectly ordered (dense
  btree, good locality), human-debuggable. Default for internal tables.
  Never `serial` (legacy, weaker permissions semantics); never `int` (you will
  hit 2^31 at the worst time).
- **UUIDv7** when: IDs are generated client-side/offline, exposed in URLs or
  APIs (avoids enumeration and count leakage), or rows merge across regions.
  UUIDv7 is time-ordered — it avoids UUIDv4's random-insert btree thrashing
  and WAL bloat. Postgres 18+: native `uuidv7()`; earlier: generate in app or
  via extension. **Never UUIDv4 as PK on a high-insert table.**
- Hybrid pattern is fine: bigint PK internally + `public_id uuid NOT NULL
  UNIQUE DEFAULT uuidv7()` for the API surface.
- Note: UUIDv7 encodes creation time — if creation time is sensitive, that is
  an information leak; use opaque random external IDs in that rare case.

### Rule: Composite PKs only on pure join/child tables.
`(parent_id, child_id)` on a join table is correct and gives you the FK index
for free in one direction (add the reverse-order index explicitly). Anything
with its own lifecycle gets a surrogate key.

## Soft delete

### Rule: Don't default to soft delete. Choose per table, and pay the full cost if you do.
Soft delete (`deleted_at timestamptz`) costs: every query must filter it
(ORMs forget; raw SQL forgets more), unique constraints break (deleted row
still holds the email), FKs still point at "deleted" rows, indexes bloat with
dead-to-the-business rows, and GDPR deletion still requires real deletion.

If you soft delete, all of the following are mandatory:
```sql
ALTER TABLE users ADD COLUMN deleted_at timestamptz; -- NULL = live
-- uniqueness only among live rows:
CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL;
-- hot-path indexes are partial:
CREATE INDEX users_org_live ON users (org_id) WHERE deleted_at IS NULL;
```
- Filtering is enforced centrally (ORM default scope / view / RLS policy), not
  per query.
- A purge job hard-deletes after the retention window.
- Decide FK behavior explicitly: does deleting a user soft-delete their posts?

Alternatives that are usually better: hard delete + audit/history table
(below); move rows to an `archived_*` table; status column when "deleted" is
really a business state (`cancelled`, `disabled`) — don't conflate the two.

## Audit & history tables (temporal patterns)

### Rule: When "who changed what, when" is a requirement, use an append-only audit table written by trigger.
Application-level audit logging misses ad hoc fixes, backfills, and other code
paths. Triggers don't.

```sql
CREATE TABLE audit_log (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name  text NOT NULL,
  row_pk      text NOT NULL,
  action      text NOT NULL CHECK (action IN ('I','U','D')),
  old_row     jsonb,
  new_row     jsonb,
  actor       text NOT NULL DEFAULT current_setting('app.actor', true),
  at          timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (at);
```
- App sets `SET LOCAL app.actor = '<user-id>'` per transaction so the trigger
  can attribute changes.
- Partition by month; retention = drop old partitions (file 05).
- No UPDATE/DELETE grants on audit tables for the app role.

### Rule: For "what did this row look like at time T" queries, use a history table (SCD2-style), not audit-log archaeology.
`valid_from`/`valid_to` ranges with an exclusion constraint
(`EXCLUDE USING gist (id WITH =, validity WITH &&)`) guarantee non-overlap.
Use trigger-maintained history tables or a temporal extension; query with
`WHERE id = $1 AND validity @> $2::timestamptz`. Reserve full event sourcing
for domains that genuinely replay events — it is an architecture, not a table
pattern.

## Ledgers: money and consumable balances

### Rule: The balance is derived from append-only entries, never a column you UPDATE.
A mutable `accounts.balance` records the result of every past write and the
reason for none of them. It drifts silently when one side of a transfer fails,
and a retried `balance = balance - $1` is indistinguishable from a second
payment (file 04: counters are not idempotent). Model the movement instead:

```sql
CREATE TABLE journals (              -- one row per movement
  id          uuid PRIMARY KEY,
  external_id text UNIQUE,           -- the operation this posts for; a retry conflicts
  reason      text NOT NULL,
  posted_at   timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE ledger_entries (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  journal_id  uuid   NOT NULL REFERENCES journals(id),
  account_id  bigint NOT NULL REFERENCES accounts(id),
  amount      bigint NOT NULL,       -- signed minor units: negative debit, positive credit
  currency    text   NOT NULL CHECK (currency ~ '^[A-Z]{3}$')
);
CREATE INDEX ledger_balance ON ledger_entries (account_id, currency) INCLUDE (amount);
```
- **Every movement posts at least two entries summing to zero**, in one
  transaction, under one `journal_id`. That is the whole point: a one-sided
  write becomes an invariant the database rejects, rather than drift you learn
  about months later from a customer.
- Nothing UPDATEs or DELETEs an entry. A mistake is corrected by posting the
  reversing entry, which preserves the history of the mistake.
- `external_id UNIQUE` on the journal, not the entry, is the idempotency key —
  one movement, one external operation, however many legs. NULL repeats freely,
  so internal journals need no synthetic value.
- Balance = `SUM(amount)`. When that gets slow, add a rollup (or a per-period
  closing balance and sum only entries since). The rollup is a **cache** — a job
  that re-derives it from the entries and compares is the cheapest correctness
  alarm you will ever own; a rollup that cannot be re-derived is just the
  mutable column again.

### Rule: Enforce sum-zero in the database, not in the service that writes it.
`CHECK` cannot span rows, so the invariant needs a constraint that runs once the
whole journal is written. In Postgres that is a deferred constraint trigger —
per the [CREATE TRIGGER
reference](https://www.postgresql.org/docs/current/sql-createtrigger.html), a
constraint trigger may only be `AFTER` and `FOR EACH ROW`, and
`DEFERRABLE INITIALLY DEFERRED` moves the check from end-of-statement to
end-of-transaction:

```sql
CREATE CONSTRAINT TRIGGER ledger_balanced
  AFTER INSERT ON ledger_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION assert_journal_sums_to_zero();
-- the function: SUM(amount) per (journal_id, currency) must be 0, else RAISE
```
Enforced in application code only, the invariant holds for the code paths that
remembered it. Group by **currency**: a journal netting to zero across two
currencies is two unbalanced journals: cross-currency movement posts through an
explicit FX account so both sides balance in their own unit.

## Multi-tenancy

### Rule: Default to shared tables with `tenant_id` + Row-Level Security.
Scales to millions of tenants, one migration path, normal pooling.
Requirements (all mandatory — partial implementation is a CRITICAL audit
finding):
```sql
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- applies to table owner too
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::bigint);
```
- `tenant_id bigint NOT NULL` on every tenant-scoped table, FK to tenants.
- App sets `SET LOCAL app.tenant_id = ...` at transaction start (SET LOCAL,
  not SET — pooled connections leak session settings; see file 04).
- App connects as a non-owner, non-BYPASSRLS role.
- Composite indexes lead with `tenant_id`: `(tenant_id, created_at)`, etc.
- Wrap `current_setting` policies so the planner inlines them; benchmark RLS
  overhead on hot queries (usually negligible with correct indexes).

### Rule: Schema-per-tenant only for few (≲100), large, compliance-isolated tenants.
Per-schema gives stronger isolation, per-tenant restore, and per-tenant
customization — at the cost of N× migrations, catalog bloat at scale,
connection/search_path management, and painful cross-tenant analytics. Past a
few hundred schemas, migrations and `pg_dump` become operational hazards.
Database-per-tenant is the same tradeoff, stronger and more expensive —
reserve for regulated enterprise tenants. Hybrid (RLS for the long tail,
dedicated DB for whale tenants) is a legitimate end state; design IDs and
migrations so a tenant can be extracted.

### Rule: Tenant isolation is tested, not assumed.
Ship an automated test that sets tenant A's context and asserts zero rows
from tenant B across every tenant-scoped table. Missing isolation tests on a
multi-tenant system: HIGH.

## Modeling hygiene (defaults unless justified)

- `timestamptz`, never `timestamp` (naive timestamps corrupt across zones).
  Store UTC; convert at the edge.
- Money: `bigint` minor units (cents) or `numeric(19,4)`. Never `float`/`real`
  for money or anything summed.
- Text: `text` with CHECK length limits where needed; `varchar(255)` is cargo
  cult. `citext` for case-insensitive uniqueness (emails, usernames).
- Enum-like states: `text` + CHECK constraint, or a lookup table. Native
  `ENUM` types complicate value removal/rename in migrations.
- Every table: `created_at timestamptz NOT NULL DEFAULT now()`; `updated_at`
  maintained by trigger if used.
- `NOT NULL` by default; a nullable column is a decision with a meaning
  ("unknown" vs "not applicable") — document it.
- Every FK column gets an index (Postgres does not create one automatically);
  every FK declares `ON DELETE` behavior explicitly (RESTRICT default;
  CASCADE only when the child is meaningless without the parent).
- Booleans that will grow a third state are status columns; model them as
  such the first time.
- **Absence is `NULL` — or, in a document/graph store, an omitted property — never
  an in-band value.** A `-1`, `0`, `9999-12-31` or `""` standing in for "unknown"
  is a value the engine will happily compare, sort, index and aggregate as if it
  were real. `NULL` is the one encoding every engine already knows to exclude from
  `<`/`>` and from `AVG`; a sentinel pushes that filtering onto every read, and the
  reads that forget fail **silently and directionally** — a sentinel loses every
  `<` against a real value and wins every `>`, so an ordering predicate flips one
  way or the other depending on which operand is missing. Document and
  schemaless stores make this worse, not better: they have native property absence,
  so writing a sentinel *discards* information the store would have kept for free,
  and there is no column definition left for a reviewer to notice it in.
- If a sentinel is genuinely forced (a fixed-width wire format, an upstream export
  that cannot express absence), it is **per field with its domain written down**,
  enforced (`CHECK (col >= 0 OR col = -1)` and a comment saying which is which),
  and filtered **centrally on the read path** — one view, one repository method —
  not re-derived in each of the queries that happen to remember. One sentinel
  constant applied uniformly across a set of fields with *different* domains is the
  common shape and the unrecoverable one: on a field where the producer also emits
  that value legitimately, a stored sentinel is ambiguous forever. Producer-side
  detail: `sota-python` rules/02 §2a.

## Audit checklist

- [ ] Engine choice justified; no second datastore without a written reason
      Postgres can't satisfy; no analytics workload on the OLTP primary.
- [ ] No natural primary keys; no UUIDv4 PKs on high-insert tables; no `int`
      PKs on growing tables; exposed IDs are non-enumerable (UUIDv7/public_id).
- [ ] No mutable business facts duplicated without a documented sync mechanism;
      temporal snapshots (prices, addresses) frozen at event time.
- [ ] No JSONB keys used in hot WHERE/JOIN/ORDER BY without a generated column
      + index; no money/FKs/status inside JSONB.
- [ ] Soft-delete tables have partial unique + partial hot-path indexes,
      centralized filtering, and a purge job; "deleted" is not conflated with
      business states.
- [ ] Audit/history requirements met by trigger-based append-only tables with
      actor attribution and partitioned retention — not ad hoc app logging.
- [ ] Money and consumable balances: append-only entries, at least two per
      movement summing to zero, with sum-zero enforced by a **deferred DB
      constraint** rather than app code; balance derived (or a rollup that a job
      re-derives and compares); corrections posted as reversing entries. A
      mutable `balance` column UPDATEd in place on a money path is CRITICAL —
      `grep -rn "balance *= *balance\|SET balance" migrations/ src/`.
- [ ] Multi-tenant: RLS enabled AND forced on every tenant-scoped table,
      SET LOCAL tenant context, non-BYPASSRLS app role, tenant_id-leading
      indexes, automated cross-tenant isolation test.
- [ ] Types: timestamptz everywhere, no float money, FK columns indexed,
      ON DELETE explicit, NOT NULL default, citext for case-insensitive
      unique text.
- [ ] Absence modeled as `NULL`/omitted property, not an in-band sentinel
      (`-1`, `0`, `9999-12-31`, `""`). Where one is forced: per-field domain
      documented, a CHECK constraint distinguishing sentinel from legitimate
      value, and read-path filtering centralized rather than repeated per query.
      Check the queries that **order or compare** the column first — that is
      where a sentinel changes an answer instead of just looking odd.

# 02 — Schema Evolution & Migrations

## Core invariants

### Rule: A migration must never break the currently deployed application.
During any deploy there is a window where old code runs against the new schema
(and, with rollbacks, new code against the old schema). Every migration must
be compatible with both adjacent application versions. This forces
**expand/contract**:

1. **Expand** — additive, backward-compatible change (new column, new table,
   new index, new nullable constraint). Deploy.
2. **Migrate code** — application writes both/new, reads with fallback. Deploy.
3. **Backfill** — batched data migration, separate from DDL.
4. **Contract** — remove the old column/table/code path. Separate deploy,
   only after verifying nothing reads the old shape.

Each phase is its own migration + deploy. Collapsing them into one "rename
column" migration is a HIGH audit finding regardless of table size — table
size changes; the pattern shouldn't.

```sql
-- BAD: breaks old code instantly, rewrites nothing but kills every in-flight query plan
ALTER TABLE users RENAME COLUMN username TO handle;

-- GOOD: expand/contract rename
-- M1 (expand):    ALTER TABLE users ADD COLUMN handle text;
--                 + trigger or dual-write in app keeping both in sync
-- M2 (backfill):  batched UPDATE ... WHERE handle IS NULL
-- M3 (validate):  ALTER TABLE users ADD CONSTRAINT handle_nn CHECK (handle IS NOT NULL) NOT VALID;
--                 ALTER TABLE users VALIDATE CONSTRAINT handle_nn;
-- M4 (contract):  ALTER TABLE users DROP COLUMN username;   -- after code stops reading it
```

### Rule: Reversible, or the irreversibility is documented in the migration itself.
Every migration has a `down`/rollback, OR an explicit comment: why it cannot
be reversed and what the recovery plan is (restore from PITR, re-run backfill,
etc.). `down` methods that were never run are theater — what actually matters:
- Destructive migrations (DROP COLUMN/TABLE, lossy type change) are deployed
  only after a release where nothing references the object, so "rollback" of
  the app never needs the schema rolled back.
- Data-destroying migrations note the backup/PITR point to restore from.

### Rule: Migrations are immutable once merged.
Never edit an applied migration; write a new one. Checksums (Flyway/most tools
enforce this) catch drift. Editing history breaks every environment that
already ran it.

### Rule: One logical change per migration; DDL and large data changes never share a transaction.
Migration tools wrap files in a transaction (good for atomic DDL in Postgres),
but a million-row UPDATE inside that transaction holds locks and bloats for
its whole duration. DDL migration files stay tiny; backfills are separate,
batched, and non-transactional as a whole (see Backfills).

## Lock-aware DDL on hot tables

### Rule: Know which DDL blocks, and never let it queue behind traffic.
Postgres lock facts that decide everything:
- Any `ALTER TABLE` takes ACCESS EXCLUSIVE — it blocks all reads/writes AND,
  worse, **queues behind any long-running query**, and everything else queues
  behind it. A 5ms ALTER behind a 10-minute report = 10 minutes of full outage.
- Therefore every DDL migration sets a lock timeout and retries:
```sql
SET lock_timeout = '3s';
SET statement_timeout = '15s';  -- belt and suspenders for the DDL itself
-- migration runner retries on lock_timeout failure (with backoff)
```
Missing `lock_timeout` in migrations on a production system: HIGH.

### Rule: Fast vs rewrite — know the table per operation (Postgres 14+).
**Metadata-only (fast, still needs lock_timeout):** ADD COLUMN (nullable, or
NOT NULL with constant default — default stored lazily since PG11), DROP
COLUMN, SET/DROP DEFAULT, most type widenings that are binary-coercible
(`varchar(50)→text`, `varchar(n)→varchar(m>n)`), `numeric` precision increase.

**Full table rewrite or scan (dangerous on hot tables — use the safe pattern):**
- `ALTER COLUMN TYPE` (non-coercible, e.g. `int→bigint`): rewrites table +
  indexes. Safe pattern: new column → dual-write trigger → backfill → swap
  names in one fast transaction → drop old.
- `SET NOT NULL` (scans): add `CHECK (col IS NOT NULL) NOT VALID`, then
  `VALIDATE CONSTRAINT` (takes only SHARE UPDATE EXCLUSIVE), then `SET NOT
  NULL` (PG12+ uses the validated check to skip the scan), then drop the check.
- `ADD FOREIGN KEY` / `ADD CHECK`: always `NOT VALID` first, `VALIDATE
  CONSTRAINT` in a later migration.
- `ADD COLUMN ... DEFAULT <volatile fn>` (e.g. `uuidv7()`): rewrites. Add
  nullable, backfill, then set default for new rows.

### Rule: Indexes on live tables: CONCURRENTLY, outside a transaction, check for invalid leftovers.
```sql
-- BAD on any table with traffic: blocks writes for the whole build
CREATE INDEX orders_user_idx ON orders (user_id);

-- GOOD
CREATE INDEX CONCURRENTLY orders_user_idx ON orders (user_id);
DROP INDEX CONCURRENTLY old_idx;
```
- CONCURRENTLY cannot run inside a transaction — mark the migration
  non-transactional (`disable_ddl_transaction!`, `-- migrate:no-transaction`,
  `atomic = False`, tool-equivalent).
- A failed CONCURRENTLY build leaves an INVALID index that still costs write
  overhead: detect (`pg_index.indisvalid = false`) and drop+rebuild. Make the
  migration idempotent: drop invalid index if present, then create.
- Unique constraints on live tables: `CREATE UNIQUE INDEX CONCURRENTLY` then
  `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX ...`.

### Worked example: `int` → `bigint` PK on a hot table (the full sequence)
The most common forced rewrite. Memorize the shape; it generalizes to any
type change.
```sql
-- M1 (expand): new column, synced going forward
ALTER TABLE orders ADD COLUMN id_new bigint;            -- metadata-only
CREATE OR REPLACE FUNCTION orders_sync_id() RETURNS trigger AS $$
BEGIN NEW.id_new := NEW.id; RETURN NEW; END $$ LANGUAGE plpgsql;
CREATE TRIGGER orders_sync_id BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_sync_id();

-- M2 (backfill, script not migration): batched
UPDATE orders SET id_new = id
WHERE id > $last AND id <= $last + 10000 AND id_new IS NULL;

-- M3: enforce + index, all lock-friendly
ALTER TABLE orders ADD CONSTRAINT id_new_nn CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT id_new_nn;
CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_key ON orders (id_new);

-- M4 (swap, one fast transaction with lock_timeout + retry):
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_new_key;
ALTER TABLE orders ALTER COLUMN id_new SET NOT NULL;   -- uses validated check
ALTER TABLE orders RENAME COLUMN id TO id_old;
ALTER TABLE orders RENAME COLUMN id_new TO id;
ALTER TABLE orders ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY
  (START WITH <max_id + safety_gap>);
DROP TRIGGER orders_sync_id ON orders;
COMMIT;
-- M5 (contract, after soak): drop id_old; repeat pattern for every FK column
-- referencing orders.id (each FK column is its own expand/contract cycle).
```
Sequence/identity restart value must exceed max(id) plus headroom for rows
inserted during the swap window. FKs referencing the column make this a
multi-table project — schedule it before the int is 50% exhausted (alert on
sequence exhaustion: `last_value / 2147483647`).

## Backfills

### Rule: Backfills are batched, keyset-driven, throttled, and resumable.
```sql
-- BAD: one statement, hours of lock/bloat/replication lag
UPDATE events SET tenant_id = u.tenant_id FROM users u WHERE events.user_id = u.id;

-- GOOD: driver loop (app/script), each batch its own transaction
UPDATE events SET tenant_id = u.tenant_id
FROM users u
WHERE events.user_id = u.id
  AND events.id > $last_id AND events.id <= $last_id + 10000
  AND events.tenant_id IS NULL;          -- idempotent: re-runnable from anywhere
-- commit; record $last_id; sleep adaptively (watch replication lag); repeat
```
- Batch by PK range (keyset), not OFFSET.
- Idempotent predicate so a crashed backfill resumes safely.
- Monitor replica lag and bloat during the run; pause when lag grows.
- Run as a script/job with progress logging — not as a "migration" that a
  deploy pipeline times out on.

## Zero-downtime cheat sheet

| Change | Safe pattern |
|---|---|
| Add column | ADD COLUMN nullable or with constant default |
| Drop column | Stop reading in code → deploy → DROP COLUMN (lock_timeout) |
| Rename column/table | Expand/contract dual-write (never RENAME on hot path); views can bridge reads |
| Change type | New column + trigger dual-write + backfill + swap |
| Add NOT NULL | CHECK NOT VALID → VALIDATE → SET NOT NULL |
| Add FK/CHECK | NOT VALID → VALIDATE CONSTRAINT |
| Add index | CREATE INDEX CONCURRENTLY (non-transactional migration) |
| Add unique | UNIQUE INDEX CONCURRENTLY → ADD CONSTRAINT USING INDEX |
| Drop default / change default | Safe, metadata only |
| Partitioning an existing table | New partitioned table + dual-write + backfill + swap, or pg_partman/logical replication route |
| New required column on insert-heavy table | Add nullable + app writes it → backfill → NOT NULL via check pattern |

## Migration tooling & testing

### Rule: Migrations live in version control, run automatically, and exactly one tool owns the schema.
- One migration framework (Flyway, dbmate, Alembic, Rails/AR, Prisma, golang-
  migrate, Atlas...) — never hand-applied DDL in prod. Any schema drift
  between environments is a HIGH finding; verify with a schema diff (e.g.
  `migra`, `atlas schema diff`) in CI.
- Migrations run in the deploy pipeline before (expand) or after (contract)
  the code rollout — the ordering is part of the migration's design notes.
- A linter in CI (e.g. squawk for Postgres) catches blocking DDL patterns
  automatically.

### Rule: Test migrations against realistic data, both directions, before prod.
Minimum bar:
- CI applies all migrations to a clean DB **and** to a schema snapshot of
  production (structure + statistically similar volume for hot tables).
- Time the migration against prod-sized data; anything that scales with table
  size gets reviewed for the lock-aware pattern above.
- Staging runs the migration against a recent prod restore (this doubles as
  your backup-restore test — file 05).
- For risky migrations: rehearse the rollback path explicitly.

### Rule: Migrations are forward-compatible with concurrent deploys.
If two app instances deploy at once, or a migration runs while old pods serve
traffic, nothing may corrupt. Migration lock (tools provide one — e.g.
advisory-lock based) prevents concurrent migration runs; verify it's enabled.

### Rule: Seed/reference data changes are migrations too — and idempotent.
`INSERT ... ON CONFLICT DO UPDATE` for lookup rows. Never assume an empty
table; never duplicate rows on re-run.

### Rule: Deploy ordering is part of the migration's contract — write it down.
- **Expand migrations** run before the code that uses them ships.
- **Contract migrations** run after the code that stopped using the old shape
  is fully rolled out everywhere (all regions, all canaries, mobile clients
  if they query directly via an API contract change).
- If a migration and a code change must ship together atomically, the design
  is wrong — split it until each step is independently safe.
- Feature flags don't change this: schema must support both flag states.

## MySQL differences (when the project is MySQL/MariaDB)

The expand/contract and lock-timeout doctrines are identical; mechanics differ:
- No transactional DDL: a failed multi-statement migration leaves a half-
  applied state — one DDL statement per migration file, idempotent re-runs.
- `ALGORITHM=INSTANT` (8.0+) covers ADD/DROP COLUMN and more; always specify
  `ALGORITHM=INSTANT|INPLACE, LOCK=NONE` explicitly so the migration **fails
  loudly** instead of silently rewriting the table.
- For real rewrites on hot tables use `gh-ost` or `pt-online-schema-change`
  (shadow-table + trailing changelog), not naive ALTER.
- Adding an index is online (INPLACE) but still I/O-heavy — off-peak.
- Foreign keys + gh-ost don't mix well; check tool constraints first.

## ORM-specific traps

- **Prisma/Django/Rails auto-generated migrations:** review the generated SQL,
  not the DSL. Auto-generated `ALTER COLUMN TYPE`, implicit index drops on
  unique changes, and non-CONCURRENT index creation are common. Print SQL
  (`prisma migrate diff`, `sqlmigrate`, `rails db:migrate:status` + manual
  inspection) in code review.
- **Django:** set `atomic = False` for CONCURRENTLY; beware `AlterField`
  silently rewriting; use `SeparateDatabaseAndState` for swap tricks.
- **Rails:** `strong_migrations` gem in every Rails project, not optional.
- **Alembic:** autogenerate misses CHECK constraints and some index changes —
  diff against the real schema periodically.

## Audit checklist

- [ ] No migration breaks the previously deployed app version; renames/type
      changes/drops follow expand → migrate code → backfill → contract across
      separate deploys.
- [ ] Every migration reversible or carries an explicit irreversibility note
      with recovery plan; applied migrations never edited.
- [ ] All DDL migrations set `lock_timeout` (and the runner retries); no
      ACCESS EXCLUSIVE operation can queue behind long queries unbounded.
- [ ] No table-rewrite DDL (type change, volatile default, naive SET NOT
      NULL) on hot tables; NOT VALID → VALIDATE used for new constraints.
- [ ] All index builds on live tables use CONCURRENTLY in non-transactional
      migrations; no INVALID indexes present in the catalog.
- [ ] Backfills batched by keyset, idempotent, resumable, throttled against
      replication lag; never inside the DDL migration's transaction.
- [ ] One migration tool owns the schema; CI detects drift vs prod; migration
      linter (squawk/strong_migrations) wired in.
- [ ] Migrations tested against prod-sized/prod-shaped data and timed; risky
      rollbacks rehearsed; concurrent-migration lock enabled.
- [ ] ORM-generated migrations reviewed as SQL, not as DSL.

# 03 — Query & Index Craft

## Reading EXPLAIN ANALYZE

### Rule: Tune from `EXPLAIN (ANALYZE, BUFFERS)`, never from the query text.
Always include BUFFERS; `shared read` vs `shared hit` tells you whether you're
I/O-bound. What to look for, in priority order:

1. **Estimated vs actual rows off by >10×** → stale/insufficient statistics or
   correlated predicates. Fix: `ANALYZE table`, raise the column's statistics
   target, or `CREATE STATISTICS` (extended stats) on correlated columns.
   Every bad plan starts with a bad estimate — fix estimates before adding
   indexes.
2. **Seq Scan on a large table with a selective filter** → missing index. Seq
   scan on a small table or non-selective predicate (>~5–10% of rows) is
   correct, not a bug.
3. **`Rows Removed by Filter` large on an Index Scan** → index isn't selective
   for this query; needs a composite/partial index matching the full predicate.
4. **Sort with `Sort Method: external merge Disk`** → work_mem too small for
   this sort, or an index should provide the order.
5. **Nested Loop with thousands of iterations of an inner Index Scan** → fine
   if loops are small, disastrous if estimates were wrong (see #1). Hash Join
   expected for large unordered joins.
6. **`Heap Fetches` high on Index Only Scan** → table needs vacuum (visibility
   map stale) — file 05.
7. **`lossy` heap blocks in a Bitmap Heap Scan** → work_mem too small for the
   bitmap.

Use `auto_explain` (log_min_duration) in production to capture real slow plans
— dev-machine plans lie because data volume and cache state differ.
`pg_stat_statements` is the entry point: optimize by total time, not by
single-query latency.

## Index types — pick by operator, not habit

| Type | Use for | Notes |
|---|---|---|
| btree | `=`, `<`, `>`, BETWEEN, ORDER BY, uniqueness | Default. ~99% of indexes. |
| GIN | JSONB `@>`/`?`, arrays `&&`/`@>`, full-text | Slower writes; `fastupdate` batches; `jsonb_path_ops` if only `@>`. |
| GiST | Ranges `&&`, geometry, exclusion constraints, KNN `<->` | The only index for EXCLUDE constraints. |
| BRIN | Huge append-only tables, range filters on naturally ordered cols (`created_at`) | Tiny (MBs for TB tables); useless if physical order ≠ column order. |
| Hash | `=` only | Rarely beats btree; skip unless measured. |
| Partial | `WHERE` clause subsets (live rows, pending jobs) | Query predicate must imply the index predicate verbatim. |
| Covering (`INCLUDE`) | Index-only scans | `(user_id) INCLUDE (email, name)` — payload without widening the key. |
| Expression | `lower(email)`, `(payload->>'k')`, date_trunc | Query must use the exact expression; keeps stats on the expression too. |

```sql
-- Job queue: tiny index over only the rows that matter
CREATE INDEX jobs_pending ON jobs (priority DESC, created_at)
  WHERE status = 'pending';

-- Case-insensitive lookup without citext
CREATE UNIQUE INDEX users_email_lower ON users (lower(email));
-- query MUST write: WHERE lower(email) = lower($1)
```

## Composite index design

### Rule: Column order = equality columns first, then the one range/sort column. ERS.
**E**quality, **R**ange, **S**ort. A btree serves the leftmost prefix; after
the first range/inequality column, remaining columns can't narrow the scan
(they only filter in-index).

```sql
-- Query: WHERE tenant_id = $1 AND status = $2 AND created_at > $3 ORDER BY created_at
-- GOOD: equalities first, range/sort last — one continuous index range
CREATE INDEX ON orders (tenant_id, status, created_at);

-- BAD: range column first — equality columns become in-index filters
CREATE INDEX ON orders (created_at, tenant_id, status);
```
- Among equality columns, order doesn't matter for this query — order them to
  maximize reuse by other queries (most-shared prefix first).
- `(a, b)` makes a separate `(a)` index redundant — drop it. `(b)` alone is
  NOT served (no skip-scan reliance; PG18 adds limited skip scan, don't design
  for it).
- ORDER BY can be served only if the index order matches after all equality
  prefix columns: `(tenant_id, created_at DESC)` serves
  `WHERE tenant_id=$1 ORDER BY created_at DESC LIMIT 20` with zero sort.

### Rule: When indexes hurt — and they always cost something.
Every index taxes every INSERT/UPDATE/DELETE, consumes cache, and blocks HOT
updates if it covers a frequently-updated column (forcing full index-entry
churn). Symptoms of over-indexing: write latency, WAL volume, bloat.
- Audit with `pg_stat_user_indexes.idx_scan = 0` over a representative window
  → drop (after checking it's not a unique/constraint index or replica-only).
- Redundant prefixes (`(a)` next to `(a,b)`) → drop the prefix.
- Don't index low-cardinality columns alone (`status`, booleans) — partial
  index on the rare value instead.
- Bulk loads: drop/recreate non-constraint indexes around the load.

## ORM pitfalls & N+1

### Rule: N+1 is the default ORM behavior; eradicate it explicitly.
```python
# BAD: 1 + N queries (lazy loading per iteration)
for order in Order.objects.filter(user=u):
    print(order.customer.name)

# GOOD: Django
Order.objects.filter(user=u).select_related("customer")          # JOIN
Order.objects.filter(user=u).prefetch_related("items")           # 2nd query, IN (...)
```
```ruby
Order.where(user: u).includes(:customer)   # Rails
```
```ts
prisma.order.findMany({ where: {...}, include: { customer: true } })
```
- Turn on N+1 detection in dev/CI: Rails `strict_loading`, Django
  `django-zeal`/assertNumQueries, Hibernate statistics + `@BatchSize`,
  SQLAlchemy `raiseload('*')` as the default relationship strategy.
- AUDIT: any loop whose body touches a lazy relation or issues a query is a
  MEDIUM (HIGH on hot paths).

### Rule: No `SELECT *` (and no ORM full-entity hydration on hot reads).
`SELECT *` breaks index-only scans, drags TOASTed large columns over the wire,
couples code to column order, and hydrates objects you don't need. Select the
columns the code uses (`only()`, `values_list()`, `select: {...}`, projection
DTOs).

### Rule: Know your ORM's transaction defaults — "implicit" is where the bugs live.
- Autocommit per statement is the typical default — multi-statement business
  operations need an explicit transaction (`transaction.atomic`,
  `prisma.$transaction`, `sequelize.transaction`) or you ship partial writes.
- Inverse trap: frameworks that open a transaction per request and hold it
  across template rendering/external calls — see long-transaction rules,
  file 04.
- `save()` writing every column (not just changed ones) clobbers concurrent
  updates — use changed-field updates or optimistic locking (file 04).
- ORM-generated `IN (...)` lists with thousands of IDs: switch to `= ANY($1)`
  with an array param, or a temp join table.

### Rule: Drop to SQL when the query is the feature.
Reporting, bulk updates (`UPDATE ... FROM`), upserts, window functions,
recursive CTEs: write SQL (with the ORM's raw escape hatch, still
parameterized). An ORM loop of `save()` calls for a bulk update is a MEDIUM
finding: one `UPDATE ... WHERE id = ANY(...)` or `INSERT ... ON CONFLICT`
replaces thousands of round trips.

## Geospatial: the three ways the index is lost

Geometry is one word in the GiST row above and three distinct traps in practice. All
verified against the PostGIS documentation:

- **Pick the type deliberately, and know its units.** `geometry` measures in the **SRID's**
  units — for EPSG:4326 that is *degrees*, so a Cartesian distance over lat/long is
  meaningless. `geography` always measures in **metres** and assumes EPSG:4326. PostGIS's
  own guidance: geographically **compact** data (a city, a county) → `geometry` in a
  projection that suits it; **globally dispersed** data → `geography`. The costs of
  `geography` are real — spherical maths uses trigonometric functions rather than
  Pythagoras, and **fewer functions support it natively**; casting to `geometry` buys
  those functions back at the price of accuracy.
- **`ST_DWithin`, not `ST_Distance`, in `WHERE`.** `ST_DWithin` "includes a bounding box
  comparison that makes use of any indexes that are available"; `ST_Distance` is
  explicitly **non-indexable**, so `WHERE ST_Distance(...) < r` computes a distance for
  every row. Same answer, whole-table scan.
- **A hand-rolled haversine in `WHERE` is the same mistake wearing maths.** Any expression
  the planner cannot match to an index — haversine, a manual bounding box built from
  `cos(lat)`, a distance computed in the `SELECT` and filtered in an outer query — is a
  sequential scan. If you must express it yourself, express it as an **indexable
  predicate** first (`ST_DWithin` or `&&` against an expanded box) and use the exact
  distance only to refine the candidates.
- Mixing SRIDs is an error, not a coercion: `geometry` operations require **both operands
  in the same SRID**. Store the SRID you intend, and constrain it (`geometry(Point,4326)`)
  rather than leaving the column untyped.

**Audit it with `EXPLAIN`, not by reading the SQL** — every trap above produces correct
results and a `Seq Scan`.

## Pagination

### Rule: Keyset (seek) pagination for anything that scrolls deep; OFFSET only for shallow, bounded UIs.
`OFFSET n` reads and discards n rows — page 1000 costs 1000× page 1, and rows
shift between pages under concurrent writes (skipped/duplicated items).

```sql
-- BAD: O(offset), inconsistent under writes
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 20000;

-- GOOD: keyset — O(1) per page, stable, uses (tenant_id, created_at, id) index
SELECT id, created_at, total_cents FROM orders
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)          -- cursor from last row of prev page
ORDER BY created_at DESC, id DESC
LIMIT 20;
```
- Always add the PK as the tiebreaker column — sort keys must be unique or
  pages tear on duplicates.
- Cursor = opaque encoding of the last row's sort key values, not a page number.
- Need total counts? Estimate (`reltuples`, or count with a cap:
  `SELECT count(*) FROM (... LIMIT 1001) t`) — exact counts on big tables are
  a seq scan per page view.

## CTEs, window functions, set-based thinking

### Rule: CTEs are not optimization fences anymore — but know when they materialize.
Since PG12, single-referenced CTEs are inlined. A CTE referenced more than
once, or containing volatile functions, **materializes** (no predicate
pushdown into it). Control explicitly when it matters:
`WITH x AS MATERIALIZED (...)` to cache an expensive subresult used twice;
`AS NOT MATERIALIZED` to force inlining. Data-modifying CTEs
(`WITH deleted AS (DELETE ... RETURNING ...)`) always materialize — they're
the right tool for move-rows-and-log patterns.

### Rule: Window functions over self-joins for per-group rankings/aggregates.
```sql
-- BAD: self-join / correlated subquery per row for "latest order per customer"
SELECT o.* FROM orders o
WHERE o.created_at = (SELECT max(created_at) FROM orders WHERE customer_id = o.customer_id);

-- GOOD: one scan
SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) rn
  FROM orders o) t
WHERE rn = 1;
-- Postgres-specific alternative, often fastest with (customer_id, created_at DESC) index:
SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, created_at DESC;
```
Same applies to running totals, gaps-and-islands, deduplication
(`row_number()` + delete rn>1), and "previous row" (`lag()`).

## Prepared statements & plan management

### Rule: Parameterized always (security, file 06); prepared with eyes open.
- Drivers parameterize by default — never interpolate values, including
  ORDER BY directions and LIMIT (whitelist those).
- Prepared statements skip re-parse/re-plan; after 5 executions Postgres may
  switch to a **generic plan** that ignores parameter values — catastrophic
  for skewed data (e.g. one tenant with 90% of rows). Symptoms: query fast in
  psql, slow in app. Fix: `SET plan_cache_mode = force_custom_plan` for that
  statement/role, or restructure.
- PgBouncer transaction mode breaks session-level prepared statements unless
  PgBouncer ≥1.21 with `max_prepared_statements` set — verify (file 04).

## Workload hygiene

- Set `statement_timeout` per role (e.g. 5–30s app, longer for reporting role)
  so one runaway query can't occupy a connection forever.
- `count(*)` is not free; `EXISTS (SELECT 1 ...)` beats `count(*) > 0`.
- Functions in WHERE on the column side (`WHERE date(created_at) = $1`) defeat
  indexes — rewrite as range predicates (`created_at >= $1 AND created_at < $1
  + interval '1 day'`) or use an expression index.
- Implicit type mismatches (`text_col = 123`, numeric vs int in joins) defeat
  indexes — match types exactly.
- `LIKE '%term%'` can't use btree; use trigram GIN (`pg_trgm`) or full-text
  search.

## Audit checklist

- [ ] Geospatial: `grep -rniE 'ST_Distance\\(|haversine|acos\\(sin\\(' --include='*.sql' --include='*.py' .`
      — a distance in a `WHERE` clause is a sequential scan; the indexable form is
      `ST_DWithin`. Confirm with `EXPLAIN`, since every one of these returns the *right
      answer* while scanning the table.
- [ ] Geospatial columns declare their SRID (`geometry(Point,4326)`), and the
      geometry-vs-geography choice matches the data's extent — degrees-as-distance on a
      `geometry` lat/long column is meaningless, not merely imprecise.

- [ ] Slow-query capture exists (pg_stat_statements + auto_explain); tuning
      evidence is EXPLAIN (ANALYZE, BUFFERS), with estimate-vs-actual checked.
- [ ] Every hot query maps to a named index; composite indexes follow
      equality→range/sort order; ORDER BY+LIMIT paths are sort-free.
- [ ] No unused (`idx_scan=0`), redundant-prefix, or lone low-cardinality
      indexes; partial indexes used for skewed predicates (soft delete, queues).
- [ ] JSONB/array/full-text predicates use GIN with the right opclass;
      append-only time filters considered for BRIN; expression indexes match
      query expressions exactly.
- [ ] No N+1: eager loading explicit, N+1 detection wired into dev/CI; no
      query-in-loop patterns.
- [ ] No `SELECT *` on hot paths; projections used; bulk writes are set-based
      SQL, not ORM save-loops.
- [ ] Pagination is keyset with a unique tiebreaker on all deep/scrolling
      lists; no exact `count(*)` per page on large tables.
- [ ] Multi-referenced expensive CTEs deliberately MATERIALIZED (or not);
      per-group latest/rank uses window functions or DISTINCT ON, not
      correlated subqueries.
- [ ] All SQL parameterized; generic-plan risk assessed for skewed params;
      statement_timeout set per role; no index-defeating expressions or type
      mismatches in hot predicates.

# 04 — Transactions & Concurrency

## Isolation levels — what actually goes wrong

### Rule: Know the anomalies your isolation level permits; default READ COMMITTED is weaker than your intuition.
Postgres READ COMMITTED (the default) takes a fresh snapshot per statement.
Real anomalies you will ship if you don't design for them:

- **Lost update:** two transactions read balance=100, both write
  balance=100−10 → one decrement vanishes. READ COMMITTED allows this.
  Fix: atomic update (`SET balance = balance - 10`), `SELECT ... FOR UPDATE`,
  or optimistic version check — never read-modify-write across statements.
- **Read-check-write races:** "check no row exists, then insert" double-inserts
  under concurrency. Fix: UNIQUE constraint + `ON CONFLICT`, never an
  application-level existence check alone.
- **Write skew (REPEATABLE READ still allows shadows of it; SERIALIZABLE
  doesn't):** two doctors both go off-call because each saw the other on-call.
  Constraints can't express it → SERIALIZABLE or explicit locking on a common
  row.
- REPEATABLE READ in Postgres = snapshot isolation: consistent snapshot per
  transaction, blocks lost updates on the same row (serialization failure
  error 40001 instead), still allows write skew.
- SERIALIZABLE (SSI): true serializability via predicate locking; aborts with
  40001 under contention. Cost: tracking overhead + retry obligation.

### Rule: Choose per workload, not globally.
- Default READ COMMITTED + explicit row locking/constraints/atomic updates
  for OLTP — this is the well-trodden road.
- REPEATABLE READ for multi-statement reads needing one consistent snapshot
  (reports, exports).
- SERIALIZABLE for invariants spanning multiple rows that constraints can't
  enforce (scheduling, budget caps) — only with retry-on-40001 wired in.
  **Using REPEATABLE READ/SERIALIZABLE without 40001 retry logic is a HIGH
  finding: those levels signal conflicts by erroring.**

## Locking

### Rule: Lock rows you're about to update; pick the weakest sufficient mode.
```sql
BEGIN;
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;   -- exclusive row lock
-- compute...
UPDATE accounts SET balance = $2 WHERE id = $1;
COMMIT;
```
- `FOR UPDATE` to update/delete the row; `FOR NO KEY UPDATE` if you won't
  touch key columns (doesn't block others' FK checks — prefer it for balance-
  style updates); `FOR SHARE`/`FOR KEY SHARE` to prevent changes without
  exclusivity.
- Lock in a **consistent global order** (e.g. ascending PK) everywhere:
  `WHERE id IN (...) ORDER BY id FOR UPDATE` — unordered multi-row locking is
  the canonical deadlock factory.
- `FOR UPDATE NOWAIT` to fail fast; `SKIP LOCKED` to take what's available.

### Rule: Job queues in SQL = `FOR UPDATE SKIP LOCKED`. Period.
```sql
WITH job AS (
  SELECT id FROM jobs
  WHERE status = 'pending' AND run_at <= now()
  ORDER BY priority DESC, run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED            -- workers never block each other
)
UPDATE jobs j SET status = 'running', started_at = now(), attempts = attempts + 1
FROM job WHERE j.id = job.id
RETURNING j.*;
```
Pair with: partial index `(priority DESC, run_at) WHERE status='pending'`
(file 03); a reaper for stuck 'running' jobs (worker died mid-lock — lock
vanished with its connection but status says running); `max_attempts` +
dead-letter status; completion in a separate transaction from the work if the
work has external effects (then the work must be idempotent).
Don't hold the job lock for the duration of long work — claim via status
flip and short transaction instead.

### Rule: Advisory locks for app-level mutual exclusion — with discipline.
`pg_advisory_xact_lock(key)` (transaction-scoped — auto-released, prefer it)
vs `pg_advisory_lock` (session-scoped — leaks on connection reuse through a
pool; if you must, guarantee unlock in finally AND pin the session). Uses:
singleton cron/migration runners, per-entity serialization without locking
rows (`pg_advisory_xact_lock(hashtext('invoice:' || $1))`). Key collisions:
derive from a (namespace int, id int) pair or hashtext — document the keyspace.

### Rule: Optimistic vs pessimistic — choose by contention.
- **Optimistic (version column):** low contention, human-edit workflows,
  long "think time" (never hold a DB lock across user think time):
```sql
UPDATE documents SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;   -- rowcount 0 ⇒ conflict ⇒ reload/merge/409
```
  ORMs: Hibernate `@Version`, ActiveRecord `lock_version`, SQLAlchemy
  `version_id_col`. The check must be in the UPDATE's WHERE, not a prior SELECT.
- **Pessimistic (FOR UPDATE):** hot rows, must-succeed operations (inventory,
  balances), short transactions. Retrying optimistic conflicts on a hot row
  livelocks — lock instead.

## Idempotency & retries

### Rule: Every retryable write path is idempotent — enforced by the database.
Queues redeliver, webhooks re-fire, clients re-POST, your own deadlock-retry
re-executes. Patterns:
```sql
-- Natural idempotency via unique key:
INSERT INTO payments (idempotency_key, amount_cents, ...)
VALUES ($1, $2, ...)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;                      -- no row returned ⇒ fetch existing by key
-- State machines: guarded transitions, not blind writes
UPDATE orders SET status = 'shipped', shipped_at = now()
WHERE id = $1 AND status = 'paid'; -- rowcount 0 ⇒ already done or invalid; don't error blindly
```
- Idempotency keys: client-supplied per logical operation, UNIQUE-constrained,
  stored with the response if the caller needs replay semantics.
- `ON CONFLICT DO UPDATE` only when "overwrite with latest" is genuinely the
  semantics; DO NOTHING + read otherwise.
- Counters are not idempotent (`count = count + 1` double-fires on retry) —
  ledger rows with unique keys, aggregate via SUM or maintained rollup.

### Rule: Retry serialization failures and deadlocks at the transaction level, bounded.
Retry **the whole transaction** (fresh BEGIN, re-read everything) on SQLSTATE
40001 (serialization_failure) and 40P01 (deadlock_detected): 3–5 attempts,
exponential backoff + jitter. Never retry inside the failed transaction; never
retry non-transient errors (constraint violations are answers, not glitches).
Deadlocks at low rates are normal in concurrent systems — log them, retry
them; rising rates mean inconsistent lock ordering (fix that, see above).

## Long transactions — the silent killer

### Rule: No transaction outlives ~1s on the OLTP path; nothing slow happens inside BEGIN.
An open transaction pins its snapshot → vacuum cannot remove any dead tuple
newer than it, **database-wide** → bloat, index degradation, Heap Fetches on
index-only scans; plus it holds every lock it acquired, queuing other work,
and stalls hot standby replicas (or forces query cancellation there).

Forbidden inside a transaction: HTTP/API calls, queue publishes (use the
transactional outbox pattern: write an `outbox` row in the txn, deliver from
a poller), emails, file/S3 I/O, user interaction, unbatched loops, `sleep`.

Guardrails — set them, don't just intend them:
```sql
ALTER ROLE app SET idle_in_transaction_session_timeout = '10s';
ALTER ROLE app SET statement_timeout = '15s';
-- monitor: SELECT pid, now()-xact_start, state, query FROM pg_stat_activity
--          WHERE xact_start < now() - interval '1 minute';
```
`idle in transaction` connections in pg_stat_activity = an app bug (leaked
transaction scope), always. Alert on transaction age (file 05).

### Rule: Savepoints/subtransactions are a scalability trap — audit ORMs that spray them.
Each `SAVEPOINT` creates a subtransaction. Past **64 subtransaction IDs per
backend**, Postgres spills to the shared `pg_subtrans` SLRU — under load this
causes sudden, severe, cluster-wide latency collapse (the infamous
subtrans-SLRU contention), worsened by any long transaction. Sources that
look innocent:
- Django `transaction.atomic` nested blocks (each nested level = savepoint);
  SQLAlchemy `begin_nested()`; Rails nested `transaction(requires_new: true)`.
- "Savepoint per statement" error-recovery modes (PgBouncer-adjacent tools,
  some JDBC/ORM retry wrappers, `psqlrc`-style ON_ERROR_ROLLBACK in scripts).
- Exception-swallowing loops inside a transaction (catch → savepoint rollback
  → continue) iterating hundreds of times.
Rules: keep nesting ≤ 2; never savepoint-per-row in a loop (restructure to
batch validation or per-row transactions); monitor with `pg_stat_slru`
(PG13+) for Subtrans pressure.

### Rule: Don't poll the database for work or state changes when LISTEN/NOTIFY or the queue fits.
Tight polling loops (`SELECT ... every 100ms` × N workers) burn connections
and CPU. Use `LISTEN`/`NOTIFY` to wake workers (note: incompatible with
PgBouncer transaction mode — dedicate a direct connection), or back off
polling intervals adaptively. NOTIFY payloads are advisory only — the worker
still claims work via SKIP LOCKED (notification delivery is not transactional
work assignment, and notifications are lost on disconnect).

## Connection pooling

### Rule: A pooler is mandatory; size pools small.
Each Postgres connection is a process (~MBs, scheduler load). Throughput
peaks at low connection counts: start near `cores × 2 + effective spindles`
(often 20–50 active server connections even for large apps) and load-test;
thousands of direct connections is an anti-pattern. App-side pools
(HikariCP, etc.) cap per-instance; with many app instances/serverless, add a
server-side pooler (PgBouncer / pgcat / RDS Proxy / Supavisor).

Sizing math sanity check: required server connections ≈
`peak_tps × avg_txn_duration_s`. 2000 tps × 10ms transactions = 20 busy
connections. If your pool "needs" 500, the real problem is transaction
duration (see above) or queries needing indexes (file 03) — fix that, don't
raise the pool. Oversized pools convert overload into lock contention,
context switching, and memory pressure instead of a clean queue.

### Diagnosing lock waits (keep this query handy)
```sql
SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query,
       now() - blocked.query_start AS waiting
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';
```
The blocking query is the bug more often than the blocked one — look for
`idle in transaction` blockers (leaked scope) and DDL without lock_timeout
(file 02).

### Rule: PgBouncer mode dictates what your code may do.
- **session:** 1 client = 1 server connection until disconnect. Safe for all
  features; pools poorly (idle clients hold servers).
- **transaction (the standard choice):** server connection borrowed per
  transaction. **Breaks anything session-stateful:** session-level advisory
  locks, `SET` (use `SET LOCAL`), session prepared statements (need PgBouncer
  ≥1.21 + `max_prepared_statements`, or disable driver-level preparing),
  `LISTEN`, session-lifetime temp tables, cursors WITH HOLD. Audit any of these used with
  transaction pooling: HIGH.
- **Three distinctions PgBouncer's own matrix draws that a blanket finding gets wrong**
  (verified 2026-09-16, [feature matrix](https://www.pgbouncer.org/features.html)):
  `LISTEN` is *Never* supported in transaction mode but **`NOTIFY` is supported**;
  `PRESERVE/DELETE ROWS` temp tables are *Never* supported but **`ON COMMIT DROP` temp
  tables are**; and *protocol-level* prepared plans (what your driver does) are the ones
  `max_prepared_statements` rescues — SQL-level `PREPARE`/`DEALLOCATE` is session state and
  stays unsupported at any version. Condition the finding on the actual operation and the
  pool configuration, or you will report working code as broken.
- **statement:** breaks multi-statement transactions entirely; niche.

Settings that matter: `default_pool_size` (per user+db!), `max_client_conn`,
`server_idle_timeout`, `query_wait_timeout` (bound the queue — fail fast
beats piling up). Monitor `cl_waiting` and pool saturation. Keep a separate,
small pool/role for migrations and admin so app saturation can't block them.

### Rule: Per-tenant/per-request session state must be SET LOCAL inside the transaction.
With transaction pooling, plain `SET app.tenant_id` leaks to the next
borrower — a tenant-isolation breach (CRITICAL). `SET LOCAL` resets at
COMMIT. Verify RLS context (file 01/06) uses SET LOCAL exclusively.

## Audit checklist

- [ ] No read-modify-write across statements without FOR UPDATE / atomic
      SET expr / optimistic version in the UPDATE's WHERE; no app-level
      uniqueness checks standing in for UNIQUE constraints.
- [ ] REPEATABLE READ / SERIALIZABLE usage paired with whole-transaction
      retry on 40001; deadlock (40P01) retry bounded with backoff; multi-row
      locks acquired in consistent order.
- [ ] DB-backed job queues use FOR UPDATE SKIP LOCKED + partial index +
      stuck-job reaper + attempt caps; no lock held across long work.
- [ ] Advisory locks transaction-scoped (xact variants) or provably released;
      keyspace documented; none session-scoped behind transaction pooling.
- [ ] All retryable writes (webhooks, queue consumers, POSTs) idempotent via
      unique keys / guarded state transitions; no bare counters on retry paths.
- [ ] No network I/O, user waits, or unbatched loops inside transactions;
      outbox pattern for txn-coupled messaging; idle_in_transaction_session_timeout
      and statement_timeout set per role; alerting on old transactions.
- [ ] Pooler present; pool sizes load-tested and small; PgBouncer mode known
      and code audited against its restrictions (SET LOCAL only, prepared
      statements compatible, no session advisory locks/LISTEN in txn mode).
- [ ] Separate admin/migration pool; query_wait_timeout bounds queueing.
- [ ] No deep/looped savepoint usage (nested atomic blocks, per-row error
      recovery); subtransaction SLRU pressure monitored on busy systems.
- [ ] Workers wake via LISTEN/NOTIFY or adaptive backoff (not tight polling),
      and still claim work via SKIP LOCKED, not via notification payloads.

# 05 — Reliability & Scale

## Backups & PITR

### Rule: A backup is restorable, point-in-time, off-box, and rehearsed — or it isn't a backup.
- **PITR** (base backup + continuous WAL archiving — pgBackRest, WAL-G, or
  managed equivalent) is the baseline. Nightly `pg_dump` alone means losing up
  to 24h (RPO=24h) and restoring slowly at scale; it's a supplement (logical,
  per-table recovery), not the strategy.
- State **RPO** (max data loss) and **RTO** (max downtime) explicitly; verify
  the setup achieves them. WAL archiving interval bounds RPO.
- **Restore rehearsal on a schedule**, automated: spin up from backup, run
  integrity checks (row counts on key tables, app smoke test), record time
  (that's your real RTO). Restoring into staging monthly does double duty.
  Untested backups: CRITICAL, no discussion.
- Off-instance and off-account/region storage; retention covers both ops
  recovery (days–weeks of PITR) and compliance (longer, possibly logical).
- **Replication is not backup** — a replica replays your `DROP TABLE`
  instantly. Delayed replicas are a complement, not a substitute.
- Protect backups from the database's own credentials (a compromised DB host
  must not be able to delete its backups — object lock / separate creds).

## Replication & read replicas

### Rule: Async by default; sync only for the durability you'll pay latency for.
- **Async** streaming: primary doesn't wait; replica crash-lag = lost recent
  commits on failover. Fine for read scaling and most HA.
- **Sync** (`synchronous_commit = on` + `synchronous_standby_names`): zero
  data loss on failover, every commit pays a network round trip, and a dead
  sync standby **blocks all commits** unless you have quorum
  (`ANY 1 (a, b)`) — never a single sync standby without quorum.
- Failover: use a battle-tested orchestrator (Patroni, managed-cloud HA);
  hand-rolled failover scripts cause split-brain. Test failover like you test
  restores.

### Rule: Read replicas are eventually consistent — route reads by staleness tolerance, not by load alone.
Read-your-own-writes breaks when a user's next request hits a lagging replica.
- Route to primary: anything read-after-write in the same user flow
  (post-then-show, payment status), auth/session checks.
- Route to replicas: search, browse, analytics, anything tolerating seconds
  of lag.
- Patterns when you must scale read-your-writes: sticky-to-primary for N
  seconds after a write; or track LSN (`pg_current_wal_lsn()` after write,
  wait for `pg_last_wal_replay_lsn() >= lsn` on replica).
- Monitor lag in **bytes and seconds** (`pg_stat_replication.replay_lsn`
  delta, `pg_last_xact_replay_timestamp`); alert before the lag exceeds what
  your routing assumes.
- Long queries on hot-standby replicas conflict with replay
  (`max_standby_streaming_delay` trade-off: cancel queries vs grow lag).
  Run heavy analytics on a dedicated replica with replay delay allowed, or on
  a logical-replica/warehouse.

### Rule: Logical replication is the tool for major-version upgrades, selective sync, and CDC — know its limits.
- Near-zero-downtime **major version upgrades**: logical replica on the new
  version → cutover. (`pg_upgrade --link` is the fast in-place alternative
  with brief downtime; dump/restore is for small DBs only.) Don't camp on an
  EOL major version — that's an audit finding on its own. Minor updates
  matter too: apply the quarterly minor releases within a defined SLA —
  they carry security fixes up to RCE class (the Feb 2026 minors fixed
  arbitrary-code-execution bugs in pgcrypto, CVE-2026-2005, and intarray,
  CVE-2026-2004).
- **CDC** (Debezium-style) for feeding warehouses/search/caches: monitor slot
  lag religiously — an abandoned logical slot pins WAL until the disk fills
  (see vacuum section); set `max_slot_wal_keep_size` as the safety valve.
- Limits: DDL is not replicated (coordinate schema changes manually);
  sequences aren't replicated (resync at cutover); large transactions can
  stall apply.

## Partitioning

### Rule: Partition when lifecycle or scale demands it — not before, and plan it early enough.
Triggers for partitioning: table > ~100GB and growing; time-based retention
("delete data older than X" — partition drop is instant, mass DELETE is a
bloat catastrophe); pruning matches the dominant query predicate; vacuum on
the monolith can't keep up.
- **RANGE on time** for events/logs/audit (with `pg_partman` or equivalent for
  auto-creation — running out of future partitions is a classic outage).
  **LIST/HASH** for tenant or shard-key splits.
- The partition key must appear in hot queries (else every query scans all
  partitions) and must be part of PK/unique constraints (design constraint —
  decide before, not after).
- Default partition: have one as a safety net, monitor it staying empty.
- Retention = `DROP TABLE partition` (instant, no bloat). This alone justifies
  partitioning audit/event tables (file 01).
- Converting a live monolith table is a project (file 02: dual-write + swap);
  partition *at creation* any table you know will be append-heavy.

## Vacuum & bloat

### Rule: Autovacuum is correctly tuned, never disabled, and bloat is measured.
MVCC means every UPDATE/DELETE leaves a dead tuple; vacuum reclaims them.
Failure modes: table/index bloat (queries slow, cache wasted), stale
visibility maps (index-only scans degrade), and at the extreme **wraparound**
forced shutdown.
- Defaults are too lazy for hot tables: lower per-table
  `autovacuum_vacuum_scale_factor` (e.g. 0.01–0.02) on high-churn tables;
  raise `autovacuum_vacuum_cost_limit` / workers globally if vacuum can't
  keep up. Monitor `pg_stat_user_tables.n_dead_tup` vs live, and
  `last_autovacuum` age.
- Things that block vacuum (audit these first when bloat appears): long
  transactions (file 04), abandoned replication slots
  (`pg_replication_slots` where inactive — they pin WAL **and** xmin),
  hot_standby_feedback from long replica queries, prepared transactions
  (`pg_prepared_xacts` should be empty unless you really run 2PC).
- Wraparound: alert on `age(datfrozenxid)` > ~200M before autovacuum's
  emergency mode does it for you.
- Existing bloat: `pg_repack` (online) — not VACUUM FULL (exclusive lock)
  outside a maintenance window. PG19 (beta as of mid-2026) adds a built-in
  `REPACK ... CONCURRENTLY` that replaces pg_repack for this.
- Mass deletes: batch them (file 02) or partition; an unbatched
  multi-million-row DELETE is self-inflicted bloat + replication lag.

## Monitoring — the metric set that matters

### Rule: If these aren't graphed and alerted, the database is unmonitored.
- **Saturation:** connections vs max (+ pooler `cl_waiting`), CPU, disk space
  (% AND days-until-full from growth rate), IOPS vs provisioned.
- **Workload:** p95/p99 query latency via `pg_stat_statements` (top by
  total_exec_time), TPS, cache hit ratio (`blks_hit/(hit+read)` < ~0.99 on
  OLTP = memory pressure), temp file bytes (work_mem pressure).
- **Health:** replication lag (bytes + seconds), oldest transaction age,
  `idle in transaction` count, dead tuple ratios, autovacuum recency,
  `age(datfrozenxid)`, inactive replication slots, deadlock rate, WAL
  generation rate, invalid indexes.
- **Locks:** sessions waiting on locks (`pg_locks` not granted) > N seconds.
- Alert on trends (days-to-disk-full, lag growth), not just thresholds.

Queries worth wiring into dashboards directly:
```sql
-- replication lag per replica (run on primary)
SELECT application_name, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;
-- slots pinning WAL (abandoned slot = disk-full incident in progress)
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;
-- bloat candidates
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000 AND n_dead_tup > n_live_tup * 0.1
ORDER BY n_dead_tup DESC;
-- top queries by total time
SELECT left(query, 80), calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;
```

## Scaling order of operations

### Rule: Exhaust these, in order, before saying "shard": measure → index/query
(file 03) → pooling (file 04) → caching (below) → read replicas → vertical
scaling (a single 2026 box runs Postgres to many TB and 100k+ TPS) →
partitioning → only then sharding (Citus, Vitess-for-MySQL, app-level by
tenant). Sharding costs cross-shard transactions, joins, unique constraints,
and rebalancing forever. Genuine signals you're approaching it: write volume
beyond one primary's I/O after tuning; working set far beyond max RAM;
vacuum/replication permanently behind despite tuning; single-tenant whales
(extract them first — file 01 hybrid tenancy).

## Redis & the caching layer

Everything here applies equally to **Valkey** (BSD-3, Linux Foundation fork;
the default open alternative since Redis moved to AGPLv3 tri-licensing — AWS
ElastiCache and GCP Memorystore ship it). Module gap has mostly closed:
valkey-json and valkey-search are GA (1.0+) and ship in the official
valkey-bundle — only a TimeSeries-equivalent module is still missing.

### Rule: Cache-aside with TTLs is the default pattern; invalidation is designed, not hoped.
```
read:  GET key → hit? return : load from DB → SET key val EX ttl → return
write: write DB (txn commits) → DEL key      -- delete, don't update-in-place
```
- **Always a TTL**, even with explicit invalidation — TTL is the bound on how
  wrong you can be. Jitter TTLs (±10–20%) to avoid synchronized expiry.
- Invalidate by **delete after commit** (update-in-place races with
  concurrent writers; delete is idempotent and converges). Accept the brief
  stale window of read-modify race, or use versioned keys when you can't.
- **Stampede protection** on hot keys: per-key mutex (`SET key 1 NX EX 10`
  around the DB load), probabilistic early refresh, or serve-stale-while-
  revalidate. A hot key expiring under load = thundering herd on the DB.
- Cache **misses** of existence checks too (negative caching, short TTL) when
  lookups of absent keys are common — else absent-key floods bypass the cache.
- Redis is ephemeral here: the app must be correct (just slower) with Redis
  flushed. If a Redis flush corrupts behavior, you've built a database in a
  cache (CRITICAL).
- Set `maxmemory` + `allkeys-lru`/`lfu` for pure caches; `noeviction` only
  for Redis-as-queue/state with explicit capacity planning.

### Rule: The moment Redis holds state you can't lose, it needs the full ops treatment.
Rate-limit counters, sessions you promised to keep, queue/stream contents,
distributed lock state — once any of these matter, configure Redis like a
database, not a cache:
- Persistence: AOF `appendfsync everysec` (RDB snapshots alone lose minutes);
  understand that even AOF loses ~1s on crash — if that's unacceptable, the
  data belongs in Postgres.
- HA: Sentinel or Cluster with tested failover. Failover loses recent
  async-replicated writes — locks and counters may rewind (another reason
  fencing tokens exist, below).
- Memory: `noeviction` for state (an evicting "queue" silently drops jobs);
  capacity alerts; separate the cache instance (LRU, flushable) from the
  state instance (noeviction, persisted) — mixed instances inherit the worst
  constraints of both.
- Big-O discipline: no `KEYS`, no unbounded `SMEMBERS`/`LRANGE 0 -1`, `SCAN`
  for iteration; single-threaded Redis means one O(N) command stalls everyone
  (watch `slowlog`).

### Rule: Distributed locks in Redis — single-instance SET NX with token + fencing; treat Redlock claims skeptically.
```
SET lock:resource <random_token> NX PX 30000
-- release: Lua script — compare token, then DEL (never blind DEL: you'd
--          release someone else's lock after your own expiry)
```
- The TTL-vs-pause problem is fundamental: a GC pause/network blip past the
  TTL means **two holders**. For efficiency locks (avoid duplicate work),
  that's acceptable. For **correctness**, the protected resource must enforce
  **fencing tokens** (monotonic number checked by the resource — e.g. a
  version/`WHERE token >= $n` in the DB) — Kleppmann's critique of Redlock
  stands; Redlock across N nodes still cannot guarantee safety under pauses
  and clock skew without fencing.
- If the source of truth is Postgres anyway, prefer `pg_advisory_xact_lock`
  or SKIP LOCKED (file 04) — locks colocated with the data they protect.

### Rule: Redis Streams (consumer groups) for lightweight queues — with the full loop.
`XADD` → `XREADGROUP` → process → `XACK`, plus: `XAUTOCLAIM` for messages
stuck in another consumer's PEL (crashed worker), `MAXLEN ~` to bound stream
memory, dead-letter after N delivery attempts, idempotent consumers (at-least-
once delivery is the contract; file 04). Pub/Sub is fire-and-forget (drops on
disconnect) — never for anything that must be processed. Postgres SKIP LOCKED
remains the right queue when jobs must commit atomically with data.

## Audit checklist

- [ ] PITR-class backups, off-box/off-account, restore rehearsed on a
      schedule with recorded RTO; RPO/RTO stated; backups protected from DB
      credentials; replication not counted as backup.
- [ ] Sync replication only with quorum; failover orchestrated and tested;
      replica routing respects read-your-own-writes; lag monitored in bytes
      and seconds with alerts below routing assumptions.
- [ ] Retention-bearing big tables partitioned (or a plan exists before
      100GB); future partitions auto-created; partition key in hot predicates
      and unique constraints; retention via partition drop, not mass DELETE.
- [ ] Autovacuum tuned per hot table; dead-tuple ratio, last_autovacuum,
      wraparound age monitored; no inactive replication slots, stale prepared
      xacts, or chronic long transactions pinning xmin; pg_repack (not VACUUM
      FULL) for live de-bloat.
- [ ] The monitoring metric set above graphed + alerted, including
      days-until-disk-full and pooler saturation.
- [ ] Scaling proposals follow the order of operations; no sharding while
      indexes/caching/replicas/vertical headroom remain unexploited.
- [ ] Cache: TTLs with jitter everywhere, delete-after-commit invalidation,
      stampede protection on hot keys, correct-when-flushed property holds,
      maxmemory+eviction policy explicit.
- [ ] Redis locks: NX + token + Lua release + TTL; fencing tokens wherever
      the lock guards correctness; no blind DEL releases; no unfenced Redlock
      protecting correctness-critical resources.
- [ ] Streams consumers: XACK + XAUTOCLAIM + MAXLEN + dead-letter +
      idempotency; no Pub/Sub for must-process messages.
- [ ] Redis holding non-cache state has AOF persistence, tested failover,
      noeviction, and is separated from the LRU cache instance; no
      KEYS/unbounded-range commands in code.
- [ ] Postgres major version supported (not EOL) and quarterly minor updates
      applied within a defined SLA (minors fix RCE-class bugs — e.g. pgcrypto
      CVE-2026-2005); logical/CDC slots monitored with max_slot_wal_keep_size
      set.

# 06 — Security & Compliance

## Roles & least privilege

### Rule: The application role can do exactly what the application does — nothing more.
Separate roles with separate credentials:
- **Owner/migration role:** owns schemas and tables, runs DDL. Used only by
  the migration pipeline (file 02), never by the app at runtime.
- **App role:** `SELECT/INSERT/UPDATE/DELETE` on exactly the tables it uses;
  no DDL, no ownership. Split further when it pays: a read-only role for
  reporting endpoints, a queue-worker role touching only queue tables.
- **Human roles:** individual logins (audit attribution), read-only by
  default, write access time-boxed/break-glass via group role membership.

```sql
-- Baseline hardening (run once per database):
REVOKE ALL ON DATABASE app FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;        -- default-secure in PG15+
CREATE ROLE app_rw LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT USAGE ON SCHEMA app TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;  -- future tables
```
- Without `ALTER DEFAULT PRIVILEGES`, every migration "works in staging,
  permission-denied in prod" — or someone "fixes" it with GRANT ALL. Audit
  for that fix.
- The app connecting as superuser, the table owner, or a BYPASSRLS role:
  CRITICAL (it nullifies RLS and makes SQLi total).
- No `DELETE`/`UPDATE`/`TRUNCATE` grants on append-only tables (audit_log,
  ledgers) for the app role — append-only enforced by grants, not convention.
- Revoke `EXECUTE` on dangerous functions from app roles where present
  (`pg_read_file`, `lo_import`, `dblink`, `COPY ... PROGRAM` is superuser-only
  but verify no `pg_execute_server_program` membership).

### Rule: Per-role guardrails are security controls too.
`statement_timeout`, `idle_in_transaction_session_timeout` (file 04), and
`connection limit` per role bound the blast radius of both bugs and abuse
(a leaked reporting credential shouldn't be able to hold 500 connections).

## Credentials & connection security

### Rule: Database credentials are short-lived, scoped, and never in code or images.
- Connection strings come from a secret manager / workload identity (IAM
  auth, Vault dynamic credentials, cert auth) — not env files in git, not
  baked into images, not in CI logs. Rotation must not require a deploy
  (re-read at connect time).
- One credential per service/role pair; a leaked credential's blast radius =
  that role's grants (see above) — which is why the app role isn't the owner.
- `pg_hba.conf` discipline: explicit `hostssl` lines per network/role; no
  `0.0.0.0/0` rows for write roles; `reject` lines documented. Database
  reachable only from app networks (security groups / private subnets) —
  a publicly listening Postgres is HIGH even with strong auth.
- Audit for credentials in: `docker-compose.yml`, test fixtures, migration
  tool configs, ORM config defaults, and shell history of deploy scripts.

## Audit logging (the security kind)

### Rule: Privileged and write activity is logged in a way the app role can't erase.
- `pgaudit` (or managed equivalent: RDS/Cloud SQL audit flags) for DDL,
  role/grant changes, and writes to sensitive tables; `log_connections` +
  `log_disconnections` for session attribution.
- Logs ship off-host (the DB host compromising its own audit trail must not
  be possible); retention per compliance needs.
- `log_statement = 'all'` is not an audit strategy: it leaks bind-less query
  text (PII), kills performance, and drowns signal. Scope auditing by
  role/object via pgaudit settings.
- Application-level audit tables (file 01) cover business attribution;
  pgaudit covers out-of-band access (psql sessions, compromised creds) — you
  need both.

## Row-Level Security

### Rule: RLS for tenant/ownership isolation enforced in the database; FORCE it; test it.
Full pattern in file 01 (multi-tenancy). Security-specific additions:
- `FORCE ROW LEVEL SECURITY` on every protected table — without it the table
  owner bypasses policies silently.
- Policies must cover **all** commands: a `USING` clause filters
  SELECT/UPDATE/DELETE visibility, but INSERT/UPDATE need `WITH CHECK` or a
  tenant can write rows into another tenant (`CREATE POLICY ... USING (...)
  WITH CHECK (...)`). USING-only policies on writable tables: HIGH.
- Context via `SET LOCAL` only (transaction pooling leaks `SET` — file 04).
  A missing/empty setting must fail closed: `current_setting('app.tenant_id')`
  without the `missing_ok` flag errors — that's the correct default; the
  two-arg form `current_setting(x, true)` returns NULL and the policy must
  then evaluate to false, not true.
- Functions used by policies: `STABLE`, and beware `SECURITY DEFINER`
  functions that read protected tables — they bypass RLS unless they set
  their own context. Views: define with `security_invoker = true` (PG15+) or
  the view owner's privileges bypass RLS under it.
- Automated cross-tenant leak test in CI (file 01) — RLS misconfigurations
  are invisible until breached.

## Encryption

### Rule: In transit — TLS required and verified, both directions.
- Server: `ssl = on`, certificates managed/rotated; `hostssl` rules in
  `pg_hba.conf`, no `host` lines permitting cleartext from app networks;
  `scram-sha-256` auth only (no `md5`, never `trust`/`password`).
- Client: `sslmode=verify-full` — `require` (the common default people stop
  at) does **not** verify the server cert, allowing MITM. `sslmode=require`
  in production connection strings: MEDIUM, HIGH across untrusted networks.
- Redis: TLS + AUTH (`requirepass`/ACLs); Redis bound to a public interface
  without auth is CRITICAL (it's an RCE primitive, not just data exposure).

### Rule: At rest — disk/volume encryption is table stakes; column encryption is for targeted secrets.
- Full-disk/volume encryption (LUKS, EBS/Cloud KMS, TDE-equivalent) protects
  against stolen disks/snapshots — enable always; it does NOT protect against
  SQL-level access.
- **Column-level encryption** (application-side, e.g. AES-GCM via KMS-held
  keys; or pgcrypto with keys NOT stored in the DB) for the small set of
  high-value fields: government IDs, bank/card data (or better: tokenize via
  the payment provider and store only tokens), API keys/OAuth tokens, health
  details. Encrypted columns can't be indexed/searched directly — store a
  separate HMAC/blind-index column when equality lookup is required.
- Key management: keys in KMS/secret manager, rotation procedure documented,
  key-id stored alongside ciphertext for rotation. pgcrypto with the key in a
  table or in the SQL text (it then appears in logs/pg_stat_statements):
  CRITICAL.
- Backups inherit the requirement: encrypted, keys separate from backup
  storage (file 05).

## SQL injection

### Rule: Parameterize everything; injection is structural, not a sanitization problem.
Full injection doctrine lives in the **code-security skill** — defer there
for app-side review. Database-layer obligations:
- All SQL through bind parameters, including in ORMs' raw escape hatches
  (`whereRaw`, `extra()`, `$queryRawUnsafe` — audit these by name).
  Identifiers (column/table names, ORDER BY direction) can't be bound —
  whitelist-map them; never concatenate user input into identifiers.
- Dynamic SQL inside PL/pgSQL: `EXECUTE ... USING $1` + `format()` with
  `%I`/`%L`, never `||` concatenation. `SECURITY DEFINER` functions get extra
  scrutiny: they run with owner privileges and must `SET search_path` to a
  fixed value (search_path hijacking is a real escalation path).
- Defense in depth: least-privilege roles (above) cap what injection can do;
  RLS caps which rows; `statement_timeout` caps exfil-by-batch.
- LIKE inputs: escape `%`/`_` even when parameterized (DoS/filter-bypass, not
  injection, but same review).
- NoSQL/operator injection isn't only a SQL problem — parameterize for every
  engine in use. **Redis/Valkey:** never build `EVAL`/`EVALSHA` Lua bodies or
  `KEYS`/command names from untrusted input; pass user data only as `ARGV`/key
  args (the client builds the command as an arg vector, so values stay inert).
  **Qdrant:** assemble filters from a typed allowlist of fields/operators, never
  by templating user JSON into the filter — a client-controlled `must`/`should`
  is a cross-tenant read if it can overwrite the server's tenant filter
  (server-enforced tenant filter is non-negotiable, rules/07).

## PII handling

### Rule: Know where PII lives; minimize, mask, and control access to it.
- Maintain a PII inventory: which tables/columns hold personal data, lawful
  basis, retention period. In schema terms: `COMMENT ON COLUMN users.dob IS
  'PII: ...'` or a tracked data catalog — auditors and deletion jobs both
  need it. Greppable beats tribal knowledge.
- Don't collect what you don't use; don't copy PII into logs,
  `pg_stat_statements` (bind params keep values out of statement text —
  another reason for parameterization), analytics events, or error trackers.
- **Masking for non-production:** production data never lands in dev/staging
  unmasked. Use anonymized restores (masking step in the restore pipeline —
  e.g. PostgreSQL Anonymizer) or synthetic data. Prod-dump-to-laptop is a
  breach in waiting: HIGH.
- Reporting/BI access goes through views that exclude or mask PII columns
  (`SELECT id, left(email, 1) || '***' ...`), granted to the reporting role
  instead of base-table access.
- Replicas, backups, caches, and search indexes (Elasticsearch, Redis,
  vector stores — file 07) are all PII surfaces: retention and deletion must
  reach them too.

## Retention & deletion (GDPR-style)

### Rule: Deletion is a designed, tested data flow — not a DELETE statement someone runs.
- Per-category retention schedule, enforced by automated jobs: partition
  drops for time-series/audit (file 05), batched deletes (file 02) elsewhere.
  Data with no retention policy is data you keep forever and must defend
  forever.
- **Erasure requests (RTBF):** a single entry point that enumerates every
  location for a subject's data — primary tables, audit/history tables,
  outbox/queue payloads, caches (delete keys), search/vector indexes, logs,
  backups. Track request → completion with a deadline (30 days GDPR).
- Backups: industry-accepted approach is documented backup-expiry windows
  (deleted data ages out of backups within N days) plus re-deletion on
  restore; **crypto-shredding** (per-user encryption keys; destroy the key to
  erase the data everywhere at once, including backups) where strict
  erasure-from-backups is required.
- **Anonymization beats deletion** when aggregates must survive: nulling/
  hashing identifying columns while keeping the row is acceptable only if
  genuinely irreversible (no quasi-identifier re-identification).
- Soft delete is not erasure (file 01): `deleted_at` rows still hold the PII.
  The purge job is the compliance control; verify it exists and runs.
- Audit-log immutability vs erasure tension: keep identity out of audit
  payloads (store IDs, not emails/names) so erasing the referenced row
  suffices.

## Audit checklist

- [ ] Separate migration/app/human roles; app role non-superuser, non-owner,
      NOBYPASSRLS, table-scoped grants only; ALTER DEFAULT PRIVILEGES set; no
      GRANT ALL fixes; append-only tables lack UPDATE/DELETE grants.
- [ ] Per-role connection limits and timeouts; individual (not shared) human
      logins; break-glass write access time-boxed.
- [ ] Credentials from secret manager/workload identity, rotatable without
      deploy, one per service; DB not publicly reachable; pg_hba explicit;
      no secrets in repos/images/CI logs.
- [ ] pgaudit (or equivalent) on DDL/roles/sensitive writes, logs shipped
      off-host; no log_statement='all' in prod; connection logging on.
- [ ] RLS enabled AND forced on protected tables; policies have WITH CHECK,
      fail closed on missing context, use SET LOCAL; security_invoker views;
      SECURITY DEFINER functions pin search_path; cross-tenant leak test in CI.
- [ ] TLS enforced server-side (hostssl, scram-sha-256) and verified
      client-side (verify-full); Redis has TLS+auth and no public binding.
- [ ] Disk encryption on; targeted column encryption (KMS keys, never in-DB,
      never in SQL text) for secrets/regulated fields, with blind indexes
      where lookup is needed; encrypted backups with separated keys.
- [ ] No string-built SQL anywhere (including raw ORM escape hatches and
      PL/pgSQL EXECUTE); identifier whitelist for dynamic ORDER BY/columns.
- [ ] PII inventory exists; no PII in logs/error trackers; non-prod
      environments use masked or synthetic data; BI roles see masking views,
      not base tables.
- [ ] Automated retention jobs per data category; RTBF flow enumerates all
      stores (cache, search, vector, queues, logs) with deadline tracking;
      backup expiry or crypto-shredding documented; soft-deleted rows purged.

# 07 — Vector Search & AI-Era Data

## Engine choice

### Rule: pgvector in your existing Postgres until a measured limit says otherwise.
The Postgres-as-default heuristic (file 01) applies doubly here, because
vector data is almost never standalone — it joins to documents, tenants,
permissions, and metadata you already store. pgvector gives you: transactional
consistency between source rows and embeddings (no sync pipeline), SQL
metadata filtering, RLS-based tenant isolation (file 01/06), one backup story.

Move to a dedicated vector DB (Qdrant, Milvus, Weaviate, Turbopuffer, managed
equivalents) only on concrete triggers:
- **Scale:** beyond ~10–50M vectors per node-class instance, HNSW memory
  (index must fit RAM for good latency) and index build times start to hurt;
  dedicated engines bring quantization tiers, disk-based indexes (DiskANN-
  style), and horizontal sharding as first-class features.
- **Recall/latency SLOs under heavy filtering:** measured pgvector recall@k
  insufficient despite tuning (see filtering below).
- **Operational isolation:** vector index builds/queries are CPU+RAM-heavy;
  if they degrade your OLTP primary and a replica doesn't solve it, isolate.
- Many-tenant vector workloads with per-tenant index isolation requirements.

Document the trigger you hit. "We might need scale later" is not a trigger.
A dedicated vector DB adds: a sync pipeline (and its lag/failure modes),
duplicate authz logic, a second backup/monitoring/security surface.

### Rule: pgvector operational basics — get these right or it "doesn't work".
```sql
CREATE EXTENSION vector;
ALTER TABLE chunks ADD COLUMN embedding vector(1536);  -- dimension fixed per column
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
-- query (operator must match opclass: <=> cosine, <-> L2, <#> inner product):
SELECT id FROM chunks ORDER BY embedding <=> $1 LIMIT 10;
```
- **HNSW** default (better recall/latency, more RAM, slower build); IVFFlat
  only for cheap builds on mostly-static data — and it needs training data
  present before CREATE INDEX, plus periodic re-cluster as data drifts.
- ANN indexes return **approximate** results: tune `hnsw.ef_search` (query-
  time) against a measured recall@k baseline (exact scan on a sample =
  ground truth). Shipping ANN without a recall measurement is shipping an
  unknown correctness budget.
- `ORDER BY embedding <=> $1 LIMIT k` is the index-eligible shape — wrapping
  the distance in a function or adding it to WHERE can silently fall back to
  a seq scan (or error-prone exactness); EXPLAIN it like any query (file 03).
- **Filtered search** is the classic trap: `WHERE tenant_id = $1 ORDER BY
  embedding <=> $2 LIMIT 10` post-filters HNSW candidates — selective filters
  can return < k rows or crater recall. Options: pgvector 0.8+ iterative
  scans (`SET hnsw.iterative_scan = relaxed_order`), partial indexes per
  high-traffic filter value, or partition by tenant. Measure recall under the
  real filters, not unfiltered.
- Embeddings are big (1536 floats ≈ 6KB → TOAST): use `halfvec` (fp16, halves
  storage/RAM, negligible recall loss for most models) and consider
  `SET hnsw.ef_search` per route rather than globally. Vacuum matters —
  dead embedding tuples bloat fast under re-embedding churn (file 05).
- **Patch floor:** run pgvector ≥ 0.8.2 — CVE-2026-3172 (buffer overflow in
  parallel HNSW index builds; memory disclosure from other relations, or a
  server crash) — and prefer ≥ 0.8.4 for the June 2026 HNSW
  vacuum-corruption and distance-calculation fixes. Parallel builds
  (`max_parallel_maintenance_workers`) on an older version hit the CVE path.

### Rule: Quantize before you shard: halfvec → binary-quantized + rescore.
The capacity ladder inside pgvector, each step ~2–10× headroom:
1. `vector` → `halfvec` (fp16): half the storage and index RAM, recall loss
   usually <1%.
2. Binary quantization with rescore: index `bit`-quantized vectors (32×
   smaller), over-fetch candidates, rescore with full-precision distance:
```sql
CREATE INDEX ON chunks USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);
SELECT id FROM (
  SELECT id, embedding FROM chunks
  ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1)
  LIMIT 100                                   -- over-fetch ~5-10× k
) c ORDER BY c.embedding <=> $1 LIMIT 10;     -- exact rescore
```
Binary quantization works well on high-dimensional modern embeddings
(≥1024d); validate recall@k on your golden set before and after, like any
retrieval change.

### Rule: If you do adopt a dedicated vector DB, hold it to database standards.
The vector DB is now a datastore in scope for every other file of this skill:
- Sync: outbox/CDC-driven upserts and deletes (below), reconciliation sweep,
  alerting on sync lag — not best-effort dual writes from request handlers.
- Tenancy: per-tenant filtering enforced server-side (payload filter +
  enforced tenant key, or per-tenant collections); test cross-tenant leakage
  like RLS (file 01).
- Ops: snapshots/backups actually restored in rehearsal; capacity and memory
  monitoring; version upgrade path. Auth enabled — vector DBs ship with auth
  off more often than you'd think; an unauthenticated vector DB with document
  payloads is a data breach, not a search index (CRITICAL).
- Keep the authoritative copy of documents/metadata in Postgres; the vector
  DB stores vectors + the minimal payload needed for filtering. Rebuilding
  the collection from Postgres must always be possible (it's your recovery
  path and your migration path).

## Vector store exposure hardening

### Rule: A dedicated vector DB is locked down like the database it is — auth, network, TLS, tenancy, quotas.
Self-hosted vector DBs ship insecure: Qdrant's own docs state self-deployed
instances "are not secure by default" — no auth, listening on all interfaces.
Researchers keep finding the result in the wild (Legit Security, 2024: ~30
unauthenticated vector DBs leaking PII and private conversations; Orca, 2026:
exposed instances with credentials, medical and biometric data). Treat an
unauthenticated reachable vector DB as an active breach, not a hardening gap.
- **Auth on, always.** Qdrant: set `service.api_key`
  (`QDRANT__SERVICE__API_KEY`); use `read_only_api_key` (v1.7+) for
  query-only consumers; granular per-collection RBAC via JWT
  (`service.jwt_rbac: true`, v1.9+, HS256 signed with the api_key — rotate
  via `alt_api_key`, v1.17+, and note JWTs die with the key they were signed
  by). The admin key is a root credential: secret manager, never in client
  code.
- **Network:** internal-only binding, private subnets/security groups, TLS on
  (an api-key over cleartext is a leaked api-key — Qdrant's docs say exactly
  this). In distributed mode the internal gRPC port (6335) has **no** auth at
  all — it must never be reachable beyond cluster peers.
- **Payload hygiene:** payloads are documents — they carry PII, and people
  paste secrets into them. Classify payload fields in the PII inventory
  (file 06), run them through the same DLP/log-scrubbing rules as any store,
  and never put credentials/API keys in payloads; store the minimal filter
  fields plus an ID back to Postgres.
- **Tenant isolation is server-enforced.** A tenant filter added client-side
  is BOLA for vectors — any caller who omits it reads every tenant. Enforce
  with collection-per-tenant, or a mandatory tenant key the server applies
  (Qdrant: JWT RBAC per-collection `access` claims / payload-bound tokens).
  Cross-tenant leak test it like RLS (file 01).
- **Bound client-supplied search params.** `limit`, `hnsw_ef`/exploration
  factors, `with_payload`/`with_vectors` passed through from user input are a
  resource-exhaustion DoS (and `with_vectors` is exfiltration of your
  embedding space). Cap them server-side in your API layer; never proxy raw
  search bodies to the vector DB.

## Hybrid search

### Rule: Hybrid (lexical + vector) beats either alone; fuse with RRF.
Pure vector search misses exact identifiers, names, codes, and rare terms;
pure lexical misses paraphrase. Default architecture for retrieval quality:

```sql
WITH lexical AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank_cd(tsv, q) DESC) AS r
  FROM chunks, plainto_tsquery('english', $1) q
  WHERE tsv @@ q LIMIT 50
), semantic AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $2) AS r
  FROM chunks ORDER BY embedding <=> $2 LIMIT 50
)
SELECT id, sum(1.0 / (60 + r)) AS rrf_score      -- Reciprocal Rank Fusion, k=60
FROM (SELECT * FROM lexical UNION ALL SELECT * FROM semantic) t
GROUP BY id ORDER BY rrf_score DESC LIMIT 10;
```
- RRF over score blending: lexical and cosine scores live on incomparable
  scales; rank fusion needs no normalization or tuning.
- Maintain `tsv tsvector GENERATED ALWAYS AS (to_tsvector(...)) STORED` + GIN
  index; or BM25-class extensions (e.g. pg_search/ParadeDB) when ts_rank
  quality is insufficient.
- Retrieve generously (50–100 per arm), fuse, then optionally **rerank** the
  top ~50 with a cross-encoder for quality-critical paths — reranking is an
  app-tier concern but the DB must return enough candidates to make it work.
- Evaluate with a golden set (queries → known-relevant docs; recall@k, MRR)
  before and after every retrieval change. Retrieval changes without an eval
  harness are vibes.

## Embedding versioning & lifecycle

### Rule: An embedding is derived data, versioned by (model, dimensions, chunking, preprocessing).
Embeddings from different models — or the same model after a provider
"upgrade" — are **not comparable**. Mixing them in one searchable space is
silent corruption of results.
```sql
CREATE TABLE chunk_embeddings (
  chunk_id    bigint NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
  model       text   NOT NULL,            -- 'text-embedding-4-large@2026-01'
  embedding   halfvec(1536) NOT NULL,
  source_hash text   NOT NULL,            -- hash of embedded text → staleness detection
  created_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (chunk_id, model)
);
CREATE INDEX ON chunk_embeddings USING hnsw (embedding halfvec_cosine_ops)
  WHERE model = 'text-embedding-4-large@2026-01';  -- partial index per active model
```
- Tag every vector with its model identifier **including version**; pin model
  versions in config — never "latest".
- Query embeddings must come from the same model as stored ones; assert this
  in code (config couples the query-encoder and the index/partial-index it
  searches).
- `source_hash` lets a reconciliation job find chunks whose text changed
  after embedding (staleness) and rows missing embeddings — embeddings
  generated asynchronously WILL drift without this.

### Rule: Model migration is dual-write/dual-index, then cutover — never in-place.
1. Backfill new-model embeddings alongside old (batched, rate-limited — this
   is an expensive external-API backfill; file 02 rules apply).
2. Build the new (partial) index; evaluate on the golden set vs old.
3. Cut queries over (config flag, instant rollback available).
4. Drop old rows/index after soak.
In-place overwrite leaves a window where the index mixes models, and no
rollback. Same pattern applies in dedicated vector DBs (new collection →
alias swap).

### Rule: Embedding pipelines follow the outbox/queue rules, not ad hoc syncs.
Source-row write → outbox/queue event (file 04) → embed worker (idempotent:
keyed on (chunk_id, model, source_hash)) → upsert embedding. Failures retry;
a periodic reconciliation sweep catches anything missed. Deleting the source
must delete embeddings everywhere — including a dedicated vector DB if used
(this is also a GDPR surface: file 06; vectors and their payload metadata can
reconstruct PII).

## Cost & capacity notes

- RAG chunk stores grow ~10–100× the source text (chunk overlap + per-chunk
  vectors + index) — capacity-plan the vector table like a real table
  (file 05), not an afterthought.
- HNSW build is parallel (`max_parallel_maintenance_workers`) but still
  hours at 10M+ rows — schedule rebuilds, don't improvise them.
- Quantization (halfvec/binary + rescore) before sharding; it's an order of
  magnitude of headroom for most workloads.

## Audit checklist

- [ ] Vector store choice justified: pgvector default; any dedicated vector
      DB tied to a measured trigger (scale/recall/isolation), with its sync
      pipeline, authz duplication, and backup story accounted for.
- [ ] HNSW (or justified IVFFlat) present — no exact-scan vector queries on
      large tables; operator matches opclass; query shape is index-eligible
      (EXPLAIN-verified); ef_search tuned against measured recall@k.
- [ ] pgvector at or above the CVE-2026-3172 fix (0.8.2; prefer 0.8.4+ for
      HNSW vacuum fixes).
- [ ] Filtered vector queries tested for recall under real filter selectivity;
      iterative scan / partial index / partition mitigation where needed;
      tenant isolation applies to vector queries (RLS or per-tenant index).
- [ ] Hybrid search (lexical + vector, RRF-fused) on user-facing retrieval;
      candidate counts sized for reranking; golden-set eval harness exists
      and gates retrieval changes.
- [ ] Every embedding tagged with pinned model+version; no mixed-model
      search space; query encoder coupled to stored model by config.
- [ ] source_hash (or equivalent) staleness detection + reconciliation job;
      embedding pipeline idempotent, outbox/queue-driven, with retries.
- [ ] Model migrations are dual-index with eval-gated cutover and rollback;
      old vectors dropped only after soak.
- [ ] Quantization ladder (halfvec → binary+rescore) exploited before
      sharding or engine migration; each step recall-validated.
- [ ] Dedicated vector DB (if present): auth on, server-side tenant
      enforcement tested, snapshot/restore rehearsed, sync lag alerted,
      collection rebuildable from Postgres source of truth.
- [ ] Vector DB not publicly reachable; TLS on; admin key in secret manager;
      read-only/scoped keys (or JWT RBAC) for query-only consumers; internal
      cluster ports (e.g. Qdrant 6335) unreachable from outside the cluster.
- [ ] Payload fields classified in the PII inventory; no secrets in payloads;
      tenant isolation enforced server-side (mandatory filter/scoped token or
      collection-per-tenant) with a cross-tenant leak test.
- [ ] Client-supplied search params (limit, ef/hnsw_ef, with_payload,
      with_vectors) capped server-side; no raw search bodies proxied to the
      vector DB.
- [ ] Source deletion cascades to all vector stores; vector data included in
      PII inventory and RTBF flow; capacity plan covers vector growth and
      index RAM.

# 08 — SurrealDB & Multi-Model

Scope: building on or auditing SurrealDB (document + graph + record-link
multi-model). Engine-choice discipline from file 01 still applies: SurrealDB
as primary store is a documented decision, not a default. The security
baseline of file 06 (least privilege, TLS, credentials, PII, retention)
applies in full; this file maps it onto SurrealDB's mechanisms.

## Version & auth model

### Rule: Know which version line you run; the auth model changed at 2.0.
- Current stable line is **3.x** (verify the current release when you pin);
  2.x still receives maintenance releases. Pin and track your line — auth and
  index syntax differ across major versions.
- Since v2.0.0, authentication is defined with **`DEFINE ACCESS`**, which
  replaced the older `DEFINE SCOPE`. Any code, docs, or AI-generated snippets
  using `DEFINE SCOPE`/`scope auth` are pre-2.0 and must not be cargo-culted
  into a 2.x/3.x deployment.
- `DEFINE ACCESS @name ON [ROOT | NAMESPACE | DATABASE] TYPE [JWT | RECORD |
  BEARER]` — `RECORD` access is the end-user path (custom `SIGNUP`/`SIGNIN`
  logic, record users subject to `PERMISSIONS`); `JWT` trusts tokens from an
  external issuer; `BEARER` issues grants for system/record users.

### Rule: Token and session lifetimes are explicit, short, and validated.
- Set `DURATION FOR TOKEN` (short — minutes) and `DURATION FOR SESSION`
  (bounded — hours, not "none") on every access method. Unbounded sessions
  on a record access method: HIGH.
- Use the `AUTHENTICATE` clause for extra checks at auth time (e.g. account
  not disabled, email verified) — `THROW` on failure. Signin logic that only
  checks password equality and never re-checks account state leaves revoked
  users live until token expiry.
- `TYPE JWT` access: pin the algorithm and verification key/issuer per the
  current docs; never accept unsigned or `alg`-confusable tokens. Inside
  permissions, `$token` exposes claims and `$auth` the authenticated record
  user — authorize on `$auth`/record state, not on client-controllable claims
  you don't verify.

## Least-privilege system users

### Rule: The application never connects as root or OWNER — one scoped system user per service.
SurrealDB system users (`DEFINE USER ... ON [ROOT | NAMESPACE | DATABASE]
ROLES OWNER | EDITOR | VIEWER`) are the analogue of file 06's roles:
- The root user from `surreal start --user/--pass` is for bootstrap and
  break-glass only. An app holding root credentials: CRITICAL — root bypasses
  all namespace/database boundaries and table `PERMISSIONS`.
- System users at ROOT/NAMESPACE/DATABASE level **bypass record-level
  `PERMISSIONS`** at their level and below — so services that should be
  subject to per-record authz must authenticate as record users (via
  `DEFINE ACCESS ... TYPE RECORD`), not system users.
- Where a service does need a system user (migrations, admin jobs): scope it
  `ON DATABASE`, lowest sufficient role (`VIEWER` for read-only consumers,
  `EDITOR` for data without IAM rights; `OWNER` only for the migration/
  bootstrap path — it can edit users and access methods). One user per
  service, credentials from the secret manager (see **sota-secrets-management**),
  rotated without redeploy.

## Parameterized SurrealQL

### Rule: All SurrealQL goes through bound $parameters — never string-built queries.
```js
// Correct: value bound server-side
db.query("SELECT * FROM article WHERE status INSIDE $status AND author = $auth.id",
         { status: ["live"] });
// WRONG (SurrealQL injection): "SELECT * FROM article WHERE title = '" + input + "'"
```
- Every SDK supports named `$param` bindings; string interpolation into a
  query is injection, same severity as SQL injection (file 06) — and worse if
  the connection is a system user (full DDL like `REMOVE TABLE` is in-band).
- Protected parameters `$auth`, `$token`, `$session`, `$access` are set by
  the server and cannot be overwritten — build permissions on them.
- Record IDs from user input: bind them too (`type::thing($table, $id)` or a
  bound record id), and whitelist table names — identifiers can't be bound,
  same rule as dynamic SQL identifiers in file 06.

## Schema enforcement

### Rule: SCHEMAFULL + typed fields + ASSERT for anything integrity-critical.
`SCHEMALESS` is acceptable for genuinely open-shaped data (ingest buffers,
flexible metadata) — never for money, auth, tenancy, or anything another rule
depends on.
```surql
DEFINE TABLE order SCHEMAFULL
  PERMISSIONS
    FOR select, update WHERE customer = $auth.id
    FOR create WHERE customer = $auth.id
    FOR delete NONE;
DEFINE FIELD customer  ON order TYPE record<customer>;
DEFINE FIELD total     ON order TYPE decimal ASSERT $value >= 0dec;
DEFINE FIELD status    ON order TYPE string
  ASSERT $value INSIDE ["pending", "paid", "shipped", "cancelled"];
DEFINE FIELD created_at ON order TYPE datetime DEFAULT time::now() READONLY;
```
- `SCHEMAFULL` rejects undefined fields; on `SCHEMALESS` tables, defined
  fields still enforce their `TYPE`/`ASSERT` — so at minimum define and
  constrain the critical fields even on schemaless tables.
- `TYPE record<other_table>` is your referential typing; SurrealDB does not
  enforce cross-record existence like a SQL FK by default — add `ASSERT` /
  application checks or events where orphan links would corrupt logic, and
  audit for dangling record links in reconciliation jobs.
- `READONLY` for immutable fields (created_at, ledger amounts);
  `VALUE`/`DEFAULT` for server-computed fields so clients can't supply them.

## Record-level permissions

### Rule: Deny-by-default is the platform default — keep it that way and write explicit FOR clauses.
- Tables without a `PERMISSIONS` clause default to `PERMISSIONS NONE` for
  record users. Audit for blanket `PERMISSIONS FULL` — it's the SurrealDB
  equivalent of disabling RLS (file 06): HIGH on any table holding user data.
- Write all four verbs deliberately: `FOR select / create / update / delete`,
  each `WHERE`-scoped to `$auth` (ownership/tenancy). A `select` rule without
  matching `create`/`update` rules is the `USING`-without-`WITH CHECK`
  mistake from file 06 — users may write rows they couldn't read.
- Field-level `PERMISSIONS` on `DEFINE FIELD` for column-grade secrets
  (e.g. internal flags, PII fields readable only by the owner).
- Remember the bypass: permissions bind **record users only**; system users
  and root skip them. The cross-tenant leak test from file 01/06 applies —
  authenticate as two record users in CI and prove isolation, exercising the
  shapes that have actually bypassed permissions: graph-edge and reference
  traversals, indexed `ORDER BY` on restricted fields, and `LIVE SELECT`
  results — not just direct `SELECT`s.
- Permission enforcement has a **patch floor** — bypasses recur, so track the
  advisory feed for your line. Known floors: **CVE-2025-11060** (LIVE query
  results exposed unpermitted data; fixed in 2.1.9 / 2.2.8 / 2.3.8 /
  3.0.0-alpha.8); the **3.1.5 batch (June 2026)** — field-level SELECT
  permissions bypassed via graph/reference traversals, indexed-ORDER-BY
  ordering leak on restricted fields, deep-operator-chain DoS, and a High
  arbitrary file read via `DEFINE ANALYZER mapper()` (GHSA-cc8f-fcx3-gpjr);
  the **3.2.0 batch (July 2026)** — High custom-API namespace/database scope
  override (GHSA-848m-r628-vrxw), writes inside a `PERMISSIONS` clause
  bypassing table permissions (GHSA-66r2-5gwj-gxm2), and JWKS SSRF. On 3.x,
  run ≥ 3.1.5 at minimum, ≥ 3.2.0 where custom API routes exist.

## Capabilities hardening

### Rule: Run the server deny-by-default; allow-list only the capabilities you use.
SurrealDB has runtime capability flags; most are denied by default but
**functions are allowed by default**, and denies beat allows at equal
specificity:
```sh
surreal start --deny-all \
  --allow-funcs "array,string,time,math,type,crypto::argon2" \
  --deny-guests          # no unauthenticated queries
# scripting stays denied: embedded JS (--allow-scripting) is an RCE-adjacent
# surface — enable only with a written reason.
# outbound network from queries (--allow-net) stays denied, or allow-list
# exact hosts: --allow-net api.internal:443
```
- `--allow-all` in production: HIGH. `--allow-guests` on a database with any
  non-public data: CRITICAL. `--allow-scripting` without a documented need:
  HIGH.
- `--allow-net` is SSRF-from-the-database; if queries must call out, pin
  exact targets. Flag names and defaults evolve — verify the current
  capabilities page for your version when auditing.
- Query file access is deny-by-default since 3.1.5: with no
  `SURREAL_FILE_ALLOWLIST` configured, every path is denied. Pre-3.1.5 an
  empty allowlist meant *no* restriction — the arbitrary-file-read vector of
  GHSA-cc8f-fcx3-gpjr. Leave the allowlist unset unless queries genuinely
  need files, then pin exact directories.

## Network exposure & TLS

### Rule: Never publicly reachable; TLS everywhere; credentials from the secret manager.
- Bind to private interfaces only; reachable solely from app networks. A
  publicly listening SurrealDB — even with auth — is HIGH (same stance as
  Postgres/Redis in file 06).
- TLS for all client traffic: terminate at the server (verify current cert
  flags in the docs for your version) or front with a TLS proxy on a private
  network; never send root/system credentials or tokens over cleartext.
- Root/system/user credentials, JWT signing keys: secret manager, per-service,
  rotatable — full doctrine in **sota-secrets-management**. No `--pass` values
  in compose files, shell history, or CI logs.

## Indexes & query planner

### Rule: Same discipline as file 03 — every production query has a known index; EXPLAIN it.
- `DEFINE INDEX` types: standard, `UNIQUE` (uniqueness is a constraint here,
  same as file 01 — enforce in the DB, not the app), composite, count
  indexes, `FULLTEXT ANALYZER` (3.x name; `SEARCH ANALYZER` pre-3.0) with
  BM25, and vector indexes (`HNSW` with `M`/`EFC` tuning; `DISKANN` from
  3.1 for larger-than-RAM sets; brute force for small/exact). Vector rules
  from file 07 (recall measurement, model versioning) apply unchanged.
- Build indexes on live tables with `CONCURRENTLY`; monitor via
  `INFO FOR INDEX`.
- Verify usage with `EXPLAIN` / `EXPLAIN FULL` — look for `Iterate Index`
  rather than table scans on hot paths.

## Multi-model modeling

### Rule: Embed what's read together; reference what's shared; use edges for relationships you query both ways.
- **Embed** (nested objects/arrays on the record) data owned by and read with
  the parent: order line items, address snapshots. One read, no joins,
  schema-enforceable via nested `DEFINE FIELD`.
- **Reference** (`TYPE record<t>` links) shared or independently mutated
  data: customer ← orders, product catalogs. Record links traverse without
  explicit joins (`order.customer.name`) — fetch depth deliberately, not `*`
  expansion everywhere.
- **Graph edges** (`RELATE user->purchased->product`, edge tables with their
  own fields/permissions) when the relationship itself carries data or you
  traverse both directions. Edge tables get the same SCHEMAFULL/PERMISSIONS
  treatment as ordinary tables — they're rows, and they leak like rows.
- Don't model everything as a graph because the engine can: the file 01
  modeling questions (access patterns first) still decide the shape.

## Backups

### Rule: A SurrealDB you can't restore is file 06's "backup that isn't" — rehearse both layers.
- Logical: `surreal export` produces a `.surql` script (scope what's included:
  records, accesses, users, functions). Note the emitted `OPTION IMPORT`
  line — it disables events/side effects on import (required for
  `surreal import` on current versions); that's correct for restores, but
  means imports don't re-fire events.
- Storage-engine level: snapshot/back up the underlying datastore per your
  deployment (embedded RocksDB file copies only when consistent, or the
  backing TiKV/FoundationDB cluster's native backup) — verify the supported
  procedure for your storage engine in the current docs.
- Schedule both, encrypt them, store keys separately, and rehearse restores
  with RPO/RTO stated (file 05 discipline). Exports contain your data AND
  your access definitions — treat the files as secrets.

## Audit checklist

- [ ] Version line known and pinned; no pre-2.0 `DEFINE SCOPE` syntax in
      migrations/docs; version meets the advisory patch floor — 3.x: ≥ 3.1.5
      (June 2026 permission-bypass/file-read batch), ≥ 3.2.0 where custom API
      routes exist; 2.x: CVE-2025-11060 LIVE-query fix versions.
- [ ] Auth via `DEFINE ACCESS` (RECORD for end users); `DURATION FOR TOKEN`
      and `FOR SESSION` short and explicit; `AUTHENTICATE` re-checks account
      state; JWT access methods pin algorithm/keys.
- [ ] App never connects as root/OWNER; per-service system users scoped
      `ON DATABASE` with lowest role; services needing record-level authz use
      record users; credentials from secret manager, rotatable.
- [ ] All queries use bound `$params`; no string-built SurrealQL anywhere
      (grep SDK call sites for interpolation); dynamic identifiers whitelisted.
- [ ] Integrity-critical tables SCHEMAFULL with TYPE/ASSERT/READONLY fields;
      record links checked for orphans where it matters; no untyped critical
      fields on SCHEMALESS tables.
- [ ] No `PERMISSIONS FULL` on user-data tables; all four verbs scoped to
      `$auth`; field-level permissions on sensitive fields; cross-tenant leak
      test in CI covers LIVE queries, graph/reference traversals, and indexed
      ORDER BY on restricted fields.
- [ ] Server runs deny-by-default capabilities: no `--allow-all`, no
      `--allow-guests`, scripting denied, functions and outbound net
      allow-listed; flags re-verified against current docs.
- [ ] Not publicly reachable; TLS on all client connections; no credentials
      in compose files/CI logs.
- [ ] Hot-path queries EXPLAIN-verified (`Iterate Index`); uniqueness via
      UNIQUE indexes; index builds on live tables use CONCURRENTLY; vector
      indexes follow file 07 rules.
- [ ] Modeling: embed/reference/edge choices match access patterns; edge
      tables have schema + permissions.
- [ ] Both `surreal export` and storage-engine backups scheduled, encrypted,
      restore-rehearsed; export files handled as secrets.