Database Integrity

Schema and migration changes are high risk because deployed data already exists and cannot be re-run. Put durable invariants in constraints when the database can enforce them.

Model and invariants

• Choose types, nullability, defaults, keys, and uniqueness from domain meaning, not sample data.
• Preserve constraints, foreign keys, uniqueness, nullability semantics, transactions, and least privilege.
• Make tenant or ownership boundaries explicit in every access path. In multi-tenant systems a missing tenant scope is a critical finding; where RLS exists, never bypass it with a privileged role to make a query work.

Queries and transactions

• Parameterize every query. Treat ORM convenience as syntax, not proof of correct query count or transaction behavior.
• Inspect plans with representative cardinality before adding indexes; account for write amplification and maintenance cost.
• Keep transactions short; define isolation needs and lock ordering for concurrent writes.
• Avoid N+1 access and unbounded reads; paginate with stable ordering.

Migrations

• Design expand → migrate → contract phases for mixed application versions.
• Separate schema change, backfill, validation, and cleanup when locks or data volume matter. Backfills need batching, idempotent retries, progress tracking, and production-load awareness.
• Make migrations deploy-compatible, observable, and recoverable; define rollback or forward-fix.
• Destructive migrations (drop, truncate, type narrowing, data deletion) require explicit authorization and staging. Never claim one is safe without backup and restore evidence appropriate to the environment.
• Audit-log sensitive data changes (security-logging).

Verification

• Apply the migration from zero and against representative data; run representative queries afterwards.
• Test constraints, concurrent edge cases, upgrade and rollback/forward-fix paths.
• Confirm explain plans and lock impact for performance-sensitive production queries.
• For RLS, tenant scoping, or privilege changes: run a real database check that a cross-tenant or unprivileged access is denied, and show that removing the control makes that test fail.