/sota-databases

name: sota-databases
description: >-
  State-of-the-art database engineering rules (2026) for designing, building,
  and auditing data layers. Covers engine selection, schema modeling,
  migrations, query and index craft, transactions and concurrency, reliability
  and scale, security, and vector/AI workloads. Use when designing a new data
  layer, writing or reviewing schemas/migrations/queries, debugging slow or
  contended database workloads, or auditing an existing database for
  correctness, performance, and security. Not for ETL or streaming data
  pipelines — use sota-data-engineering. Trigger keywords: database, SQL,
  Postgres, schema, migration, index, query, ORM, transaction, NoSQL, Redis,
  vector DB, pgvector, replication, partitioning, connection pool, RLS,
  EXPLAIN, deadlock, sharding, caching, SurrealDB, SurrealQL, Qdrant,
  multi-model, graph database.

SOTA Databases

Expert-level rules for the full lifecycle of a data layer: choosing an engine, modeling data, evolving schemas safely, writing efficient queries, handling concurrency, operating reliably at scale, securing data, and supporting vector/AI workloads. Postgres is the reference engine; rules call out where other systems (MySQL, Redis, document/columnar/vector stores) differ.

This skill operates in two modes. Determine the mode from the user's intent, then load the relevant `rules/` files per the index below. Do not load all files preemptively — pick by task.

BUILD mode

Use when designing or implementing: new schemas, migrations, queries, ORM layers, caching, job queues, or database infrastructure.

1. **Engine and model first.** Read `rules/01-choosing-and-modeling.md` before writing any DDL. Default to Postgres unless a rule there says otherwise. 2. **Every schema change is a migration.** Never hand the user raw DDL to run ad hoc; produce migration files following `rules/02-schema-migrations.md` (expand/contract, lock-aware, reversible-or-documented). 3. **Design indexes with the queries, not after.** When writing a query that will run in production, state which index serves it. Follow `rules/03-queries-and-indexes.md`. 4. **State the concurrency story.** For any write path: idempotency, isolation level, locking strategy, retry behavior (`rules/04-transactions-concurrency.md`). 5. **Operational defaults are part of the design.** Pooling, backups, monitoring hooks, and retention are not "later" items (`rules/05-reliability-and-scale.md`, `rules/06-security-and-compliance.md`). 6. Prefer boring, well-trodden patterns. Novelty in the data layer is a cost, not a feature.

AUDIT mode

Use when reviewing an existing schema, migration set, query workload, ORM usage, or database configuration.

Procedure: 1. Inventory: engine + version, schema (tables, indexes, constraints), migration tooling, ORM, pooling setup, backup/replication config. 2. Load the rules files matching what exists (e.g., no vectors → skip 07). 3. Check each rule; report deviations as findings. Verify claims against the actual schema/queries — never report a finding you have not confirmed in the code or DDL.

Severity conventions:

• **CRITICAL** — data loss, corruption, or breach is likely or already

possible: untested/missing backups, SQL injection, unconstrained deletes, missing FK causing orphaned money/auth rows, RLS bypass, plaintext secrets.

• **HIGH** — production incident waiting to happen: non-CONCURRENT index on a

hot table, table rewrite migration without expand/contract, missing unique constraint under concurrent writes, unbounded long transactions, no lock_timeout in migrations, offset pagination on large tables in hot paths.

• **MEDIUM** — correctness or performance debt: N+1 queries, SELECT *, missing

composite index for a known query, soft delete without partial indexes, natural primary keys, missing updated_at/audit trail where required.

• **LOW** — hygiene: naming inconsistencies, missing comments on cryptic

columns, redundant indexes, suboptimal types (e.g., varchar(255) cargo cult).

Finding format (one per finding):

[SEVERITY] <short title>
Where: <file:line | table/column | migration id>
Rule: <rules file + rule heading>
Evidence: <the offending DDL/SQL/code, quoted>
Impact: <what breaks, when, under what load>
Fix: <concrete change — exact SQL/DDL/code where possible>

Order findings by severity. End with a summary table: count per severity, and the top 3 fixes by risk-reduction-per-effort.

Rules index

| File | Read this when... | |------|-------------------| | `rules/01-choosing-and-modeling.md` | Picking an engine (SQL vs NoSQL/KV/columnar/time-series/vector); designing tables; deciding normalization, JSONB usage, primary keys, soft deletes, audit/history tables, ledgers and account balances, multi-tenancy, or **how absence is encoded (`NULL`/omitted property vs an in-band sentinel)**. | | `rules/02-schema-migrations.md` | Writing or reviewing any migration; altering hot tables; planning zero-downtime schema changes; backfills; setting up migration tooling or testing. | | `rules/03-queries-and-indexes.md` | Writing/reviewing queries or ORM code; reading EXPLAIN ANALYZE; choosing index types or composite column order; pagination; N+1 suspicion; CTEs and window functions. | | `rules/04-transactions-concurrency.md` | Anything with concurrent writes: isolation levels, locking (FOR UPDATE, SKIP LOCKED, advisory), job queues, idempotency, deadlocks, long transactions, connection pooling. | | `rules/05-reliability-and-scale.md` | Backups/PITR, replication and read replicas, partitioning, vacuum/bloat, monitoring, capacity planning, sharding decisions, Redis caching patterns and distributed locks. | | `rules/06-security-and-compliance.md` | DB roles and grants, RLS, encryption at rest/in transit, SQL injection surface, PII columns, data retention and GDPR-style deletion. | | `rules/07-vector-and-ai.md` | Embeddings, semantic/hybrid search, pgvector v