Sql Architect

---
name: sql-architect
version: 1.0.1
description: SQL standards — UUID v7 PKs, snake_case, soft delete, forward-only migrations, parameter binding, N+1 prevention, EXPLAIN-driven indexing. PostgreSQL 18 primary; MySQL 9 and SQLite 3.53 noted. Use when designing schemas, writing queries, or auditing a database layer.
---

# SQL Architecture & Database Standards

Targets **PostgreSQL 18** as the primary engine; MySQL 9 and SQLite 3.53 noted where they differ. See [STACK.md](STACK.md) for pinned tool versions.

## 1. Schema design

- **Primary key:** `id UUID PRIMARY KEY DEFAULT uuidv7()` on every table. UUID v7 is sortable, distributed-friendly, and doesn't leak counts via URLs. PG 18 has native `uuidv7()`; on earlier engines use an extension or app-generated UUID v7.
- **Natural identifiers stay UNIQUE:** the surrogate `id` is for joins; domain meaning lives in `UNIQUE` constraints (`email`, `slug`, `iso_code`).
- **Foreign keys:** always declared at the DB level (`REFERENCES other(id) ON DELETE RESTRICT` by default). Never enforce relationships in app code alone.
- **NOT NULL by default.** Make NULL an explicit, justified choice — every nullable column should have a documented reason.
- **CHECK constraints:** push invariants into the DB (`CHECK (price >= 0)`, `CHECK (status IN (...))`). They survive bad code paths.
- **Audit columns (opt-in):** `created_at` + `updated_at` (`timestamptz NOT NULL DEFAULT now()`) on tables whose rows change after creation. Skip on pure lookup tables (`countries`, `currencies`) and event/log tables (where `event_at` alone is enough).
- **Soft delete (default):** `deleted_at timestamptz NULL`. Filter via partial indexes (`CREATE INDEX ... WHERE deleted_at IS NULL`) so queries stay fast. Use **hard delete** only when GDPR/compliance requires it, or for append-only event tables where deletion never makes sense.

## 2. Naming conventions

- **Tables:** `snake_case`, **plural** (`users`, `order_items`).
- **Columns:** `snake_case`, **singular** (`email`, `created_at`).
- **PK:** always `id`.
- **FKs:** `<reftable_singular>_id` (`user_id`, `order_id`).
- **Indexes (PostgreSQL):** `<table>_<cols>_idx`; unique `<table>_<cols>_key`; partial `<table>_<cols>_active_idx` — matches Postgres's own auto-generated names. MySQL and SQLite differ — see [ENGINES.md](ENGINES.md).
- **Constraints:** `<table>_<purpose>_check`, `<table>_<purpose>_fkey`.
- **Sequences:** PG generates them; rename only if necessary, never reference manually — UUID v7 PKs avoid the need.

## 3. Indexing strategy

- **Index every FK column** — JOINs without indexes are silent killers.
- **B-tree** is the default. **GIN** for arrays / JSONB containment. **GiST** for ranges / geometry. **BRIN** for huge append-only tables sorted by time.
- **Composite index column order:** most-selective column first, then by query pattern. The same composite index can serve `WHERE a = ?` and `WHERE a = ? AND b = ?` — but not `WHERE b = ?` alone.
- **Partial indexes** for soft-deleted rows (`... WHERE deleted_at IS NULL`) and other common filters.
- **Don't over-index.** Each index slows writes and consumes RAM. Add one only when `EXPLAIN ANALYZE` proves a real query needs it.
- **Drop unused indexes.** PG: query `pg_stat_user_indexes` periodically; remove any with `idx_scan = 0`.

## 4. Query patterns

- **Parameter binding always.** `WHERE id = $1` — never string-concatenate user input.
- **Explicit columns.** `SELECT id, email, created_at FROM users` — never `SELECT *` in production code (breaks on schema additions, returns extra bytes).
- **`RETURNING` on writes.** `INSERT ... RETURNING id, created_at` saves a round-trip and surfaces server defaults.
- **`ON CONFLICT` for upserts.** `INSERT ... ON CONFLICT (email) DO UPDATE SET ...` — atomic, no race.
- **Pagination = cursor, not OFFSET.** `WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20`. OFFSET is O(N) on every page.
- **CTEs (`WITH`) for readability**, but be aware PG ≤ 11 materializes them (perf cliff). PG 12+ inlines unless `MATERIALIZED` is forced.

## 5. JOINs & N+1 prevention

- **N+1 is the single most common perf bug.** One query that fetches parents followed by N queries for children = always wrong.
- **Detect it:** log every SQL statement in dev with timings; any list endpoint emitting more than 2–3 queries per request is suspect.
- **Fix it:** single JOIN, or batched `WHERE id IN (...)` for the children, or DataLoader-style batchers if behind an API layer.
- **JOIN vs subquery:** different shapes, similar plans in modern PG — let `EXPLAIN ANALYZE` decide. Prefer JOINs when both sides return many rows; correlated subqueries when the inner table is small or you want a scalar.
- **`LEFT JOIN` only when you genuinely need rows from the left without a match.** A `LEFT JOIN` filtered by `WHERE right.x = ?` silently becomes an INNER JOIN — clearer to write it that way.

## 6. Transactions & isolation

- **Wrap related writes in a transaction.** Anything that must succeed or fail together.
- **Isolation default:** `READ COMMITTED` (PG default). Bump to `REPEATABLE READ` for multi-statement reads that need a consistent snapshot, and `SERIALIZABLE` when reads-then-writes need true linearizability — handle `40001` (serialization failure) by retrying.
- **Keep transactions short.** Holding a transaction across a network call or user input is a deadlock waiting to happen.
- **Advisory locks** (`pg_advisory_lock`) for distributed coordination that doesn't fit a row-level lock — singleton workers, leader election, idempotent jobs.
- **Roll back explicitly on error.** Application drivers should bracket every transaction in begin/commit/rollback — never leak open transactions.

## 7. Migrations

- **Forward-only.** No `down` migrations in production. Revert by writing a new forward migration. Down migrations rot fast and lie about what they restore.
- **Idempotent where possible:** `CREATE TABLE IF NOT EXISTS`, `ADD COLUMN IF NOT EXISTS`. Lets a half-applied migration re-run safely.
- **One change per migration.** Easier to review, easier to revert, atomic in CI.
- **Naming:** `NNNNNN_snake_case_description.sql` (e.g. `000023_add_orders_paid_at_index.sql`). Number monotonically; pad enough to last.
- **Large-table changes:** add column as nullable first, backfill in batches (separate migration or async job), then add `NOT NULL` / index. Single `ALTER TABLE ... NOT NULL` on a billion-row table will lock writes for minutes.
- **Tools:** `golang-migrate` (Go) and `alembic` (Python) — both run raw `.sql` files. STACK.md pins versions.

## 8. Security

- **Parameter binding always.** Restated because it's the single most common SQL injection vector.
- **Least-privilege roles.** The app's runtime role can `SELECT/INSERT/UPDATE/DELETE` but should not `CREATE/DROP/ALTER`. Migrations run as a separate, elevated role.
- **Row-Level Security (PG)** for multi-tenant: `ENABLE ROW LEVEL SECURITY` + policies keyed on a session variable holding `tenant_id`. Defense in depth against app-layer mistakes.
- **No secrets in connection strings stored in repo.** Pull from env / secret manager. PG supports `~/.pgpass` for local dev.
- **Encrypted at rest** is a deployment concern, but flag it: enable at the volume / disk layer.

## 9. Performance & EXPLAIN

- **`EXPLAIN ANALYZE` is the only honest perf tool.** Don't optimize without it.
- **Read the plan top-to-bottom.** Look for `Seq Scan` on large tables (missing index?), high `actual rows` vs `plan rows` (stale stats — run `ANALYZE`), nested loops over huge inputs (wrong index).
- **Slow query logging:** `log_min_duration_statement = 200` (ms) in dev. Tighter in prod, but at least log the 99th percentile offenders.
- **Connection pooling.** Postgres pays a real cost per connection. Use `pgbouncer` (transaction-mode for OLTP) in front; in-app pools (e.g. `pgx`, `psycopg`) for finer control.
- **`VACUUM ANALYZE`** runs automatically (autovacuum) — be aware of bloat on heavy `UPDATE`/`DELETE` tables and tune autovacuum thresholds for them.

## 10. JSON columns — when (and when not)

- **Use JSONB only for genuinely schemaless payloads:** raw webhook bodies, opaque third-party event blobs, user-defined opaque config.
- **Never for structured domain data.** If you find yourself querying `data->'order'->>'status'` repeatedly, it's a column, not a JSON field. Normalize.
- **Index with GIN** if you must query JSONB: `CREATE INDEX ... USING GIN (data jsonb_path_ops)` for containment queries.

## 11. Engine-specific guidance

PostgreSQL 18 is the primary target. Per-engine references are split out to keep this skill focused:

- **PostgreSQL-specific types and features** (timestamptz, text, citext, generated columns, partitioning, RLS, native `uuidv7()`, etc.) — see [POSTGRES.md](POSTGRES.md).
- **MySQL 9 and SQLite 3.53 notes** (charset, primary key strategies, JSON support, concurrency caveats) — see [ENGINES.md](ENGINES.md).