git:20260827.f961daa to git:20261105.39988c5

16 added, 39 removed. Audit A to A.

---

name: database

- description: Design, change, query, migrate, and validate production data stores using the actual database engine, version, workload, and operational constraints. Use for relational, document, key-value, schema, query, transaction, migration, backfill, pooling, backup, recovery, privacy, and multitenancy work.

+ description: Inspects and changes SQL schemas, queries, indexes, migrations, ORM data access, and MongoDB documents, queries, indexes, and driver pools. Use for PostgreSQL, Prisma, EF Core, Doctrine, or SQLAlchemy query correctness, N+1, pagination, transactions, tracking, migration rollout, or MongoDB aggregation and schema design. Redis and HTTP caching belong to caching.

---

- # Database

- Preserve data integrity first. Prefer the simplest schema and storage model that satisfies confirmed access, consistency, scale, privacy, and recovery requirements.

- ## Start from the actual system

- 1. Identify the exact database product and version, extensions, topology, replication mode, driver, ORM or query builder, migration framework, connection pool, and deployment process. Use official documentation for those versions.

- 2. Establish the required invariants, read and write access patterns, data volume and distribution, concurrency, latency and throughput targets, consistency model, retention, privacy, tenant isolation, and recovery objectives before choosing a design.

- 3. Inspect the current schema, constraints, migrations, generated SQL, representative query plans, statistics, production-like data shape, tests, backup configuration, and supported application versions.

- 4. Preserve existing public behavior, persisted formats, compatibility promises, and requested scope. Do not replace the database, ORM, schema style, or migration system without a demonstrated requirement.

- ## Make integrity explicit

- 1. Represent stable business invariants with database types and constraints when the engine can enforce them. Prefer primary keys, foreign keys, unique, check, not-null, and exclusion constraints over application-only assumptions.

- 2. Keep one authoritative representation of mutable facts. Normalize by default where duplication would require coordinated writes. Denormalize only for a measured access path with explicit ownership, synchronization, repair, and validation.

- 3. Use transactions for the smallest unit that must commit atomically. Select an isolation level from the anomalies the operation must prevent, not from habit.

- 4. Make retries and repeated jobs safe through idempotent operations, stable operation identifiers, uniqueness constraints, compare-and-set semantics, or durable progress checkpoints.

- 5. Parameterize values and identifiers through supported APIs. Apply least privilege, tenant isolation, encryption, retention, and deletion requirements at every data boundary.

- ## Load conditional guidance

- 1. Read [references/modeling.md](references/modeling.md) when choosing a data store, designing schemas, constraints, indexes, queries, pagination, denormalization, privacy, or multitenancy.

- 2. Read [references/operations.md](references/operations.md) when working with transactions, locking, consistency, migrations, backfills, pooling, backups, recovery, or database tests.

- 3. Do not load either reference for a trivial query or mapping change whose behavior is fully determined by adjacent code and existing tests.

- ## Change data safely

+ 1. Identify the requested result and authorized database, schemas, environment, and role. For review-only work, inspect and report without applying changes. A reachable connection or generated command does not authorize execution.

+ 2. Read the affected schema, migration history, query, and callers before editing. Establish engine, ORM, driver, migration-runner, and runtime versions from project metadata and the actual connection when available. Resolve version conflicts before selecting commands. Preserve the existing provider and major version unless their change is requested.

+ 3. Load only the relevant procedures.

- 1. Treat schema and data changes as deployment workflows, not isolated SQL statements. Account for old and new application versions running concurrently.

- 2. Prefer expand, migrate, verify, and contract across separate deployable steps. Do not remove or reinterpret data while any supported reader or writer still depends on the old representation.

- 3. Make every migration reversible when the original state can be recovered safely. If an operation is inherently destructive, declare the exact point of irreversibility, preconditions, backup or copy, verification, rollback boundary, and roll-forward recovery path.

- 4. Keep backfills bounded, resumable, idempotent, observable, throttled, and compatible with live writes. Avoid long transactions and full-table updates on production-sized data.

- 5. Do not add an index, partition, cache, replica, shard, or duplicate field without a measured query or operational need and a stated maintenance cost.

+ - Read [PostgreSQL](references/postgres.md) for schema exploration, SQL semantics, plans, or index decisions.

+ - Read [Prisma](references/prisma.md) for Prisma setup, commands, generation, or client code. Its command list applies to version 7 only.

+ - Read [ORM review](references/orm-review.md) for generated SQL, loading, pagination, unit-of-work behavior, or concurrency. Read only the matching provider paragraphs.

+ - Read [Migrations](references/migrations.md) before authoring or applying schema or data migrations, including ORM-generated migrations.

+ - Read [PostgreSQL extensions](references/postgres-extensions.md) only for explicitly selected PostGIS, pgvector, TimescaleDB, or hybrid-search work.

+ - Read [MongoDB](references/mongodb.md) for document design, query meaning, indexes, aggregation, driver lifecycle, or pool diagnosis. The SQL-specific steps below apply to MongoDB by analogy: collections instead of tables, the aggregation plan instead of EXPLAIN, and the driver's emitted commands instead of generated SQL.

- ## Validate with the real semantics

+ 4. Define the result contract before changing SQL. Establish row grain, join keys, tenant and soft-delete filters, NULL behavior, ordering, page/count consistency, write invariants, and side effects. Resolve material ambiguity from schema and callers, then ask only for semantics the evidence cannot establish. Keep unresolved assumptions visible until settled.

+ 5. Capture the evidence appropriate to the task. For correctness, use representative expected identities and values. For ORM changes, inspect emitted SQL and round trips. For index changes, inspect physical indexes and the actual plan with representative parameters. A valid plan, generated client, migration success, or matching row count alone does not establish semantic equivalence.

+ 6. Make the smallest change that preserves that contract. Parameterize values and safely quote or allowlist identifiers. Do not repair connection problems with schema resets, suppress translation failures by loading unbounded rows, or replace database constraints with application checks.

+ 7. Verify the changed behavior through the real caller on an authorized fixture or representative copy. Check relevant empty, NULL, duplicate, tied-order, missing-relation, access-isolation, and concurrent-write cases. For a bug, reproduce the failure before the fix when practical. Check rollback and interrupted retry when the change introduces those paths. Do not weaken expected results to match generated SQL.

+ 8. Review the complete final change, including generated SQL and migration artifacts. Report what was changed or found, the checks actually executed, and any remaining execution or rollout requirement. Scope performance claims to measured data, parameters, plans, and environment. Source-only review establishes source findings, not runtime speedups or deployed schema state.

- 1. Test against the same database engine and compatible version used in production when constraints, types, transactions, locking, query planning, or migrations matter. In-memory substitutes do not prove production behavior.

- 2. Verify schema creation from an empty database and upgrades from every supported starting version. Test rollback or the documented recovery path at the irreversibility boundary.

- 3. Exercise constraints, invalid data, duplicate and concurrent writes, transaction retries, partial failure, tenant separation, pagination stability, pool exhaustion, and cancellation where relevant.

- 4. Capture query text, representative parameters, plan, actual row counts, timing, buffers or I/O, and data distribution before and after performance changes. An estimated plan alone is not proof.

- 5. Validate backups by restoring them into an isolated environment and checking application-visible invariants. Replication without tested restore is not a backup strategy.

- 6. Review the final migration and schema diff for unintended drops, implicit casts, lock scope, default rewrites, data loss, missing constraints, redundant indexes, unbounded work, and version-specific syntax.

+ If a check exposes a violated invariant, restate that specific invariant in the working context, correct the change, and rerun the affected check. Do not add unrelated global rules or continue from a failed migration as if it succeeded.