Database Standards

Provides: Schema and constraint design decisions, expand/contract migration procedure, query and index review criteria, and transaction safety checks.

Quick Reference

Golden Rule: Prefer explicit database integrity over application-only assumptions

Default Stance:

• Model the invariant, not just the current UI
• Add constraints where the database can enforce truth
• Make migrations incremental, observable, and rollback-aware
• Index for known access patterns, not guesses

Never: hold a lock across a network call, run an unbounded backfill on a live table, or ship a destructive migration without an explicit decision

Design Decisions

Settle these before changing persistence:

Application code changes weekly; data outlives it. A missing constraint becomes thousands of rows that violate an invariant nobody can now enforce.

Constraints

Put the invariant in the database when the database can express it:

• NOT NULL for fields the domain requires.
• UNIQUE for identity claims, including composite keys that express a grain.
• Foreign keys with explicit cascade or restrict behavior, chosen deliberately.
• CHECK constraints for bounded values and cross-field rules.
• Enum types or check-constrained strings rather than free text for closed sets.

Application-level validation is not a substitute. It runs in one code path; the constraint covers every path, including backfills, admin scripts, and the next service that connects to the same database.

Migration Safety

Prefer expand and contract for live systems. Each step deploys independently and is safe to stop at.

1. Expand. Add the new nullable column, table, or index without touching existing code paths.
2. Backfill. Populate in bounded batches, or dual-write from the application while old and new shapes coexist.
3. Verify. Compare counts, check constraint satisfaction, and confirm application reads return the same results from both shapes.
4. Switch. Move reads, then writes, to the new shape.
5. Contract. Remove the old structure in a later, separate change.

Do not combine steps to save a deploy. The value of the pattern is that each step is individually reversible.

Avoid:

• Long-held locks on tables that serve live traffic. Adding an index or a NOT NULL column can lock writes for the duration; check your engine's behavior and use the concurrent variant where available.
• Unbounded UPDATE or DELETE statements across large tables.
• Destructive changes without an explicit decision and a verified backup.
• Migrations that require old and new application code to be incompatible, because that removes the ability to roll back.

Query and Index Review

• Confirm the query matches an indexed access path at the expected data volume, using the engine's query plan rather than inspection.
• Check selectivity. An index on a low-cardinality column that the planner skips is maintenance cost with no benefit.
• Verify the index supports the sort and pagination the query uses, not only the filter.
• Check join cardinality. A join that fans out multiplies rows and quietly changes aggregate results.
• Avoid N+1 access patterns unless the loop is bounded and the bound is known.
• Look for redundant indexes. An index on (a) is usually unnecessary when (a, b) exists.

Transactions:

• Keep them short. A transaction is a lock held over time.
• Never hold a transaction open across an HTTP call, a queue publish, or any other network operation.
• Choose the isolation level deliberately, and know which anomalies the chosen level allows.
• Handle serialization failures and deadlocks with a bounded retry, and make the operation safe to repeat.

Types: Treat time zones, money, and decimal precision as schema concerns. Store timestamps with time zone, store money in a decimal or integer minor-unit type, and never in a float.

Checklist

Before finalizing database work:

• Constraints protect the required invariants
• The migration runs safely at the target table size, without long locks
• Old and new application code both work against the intermediate schema
• Backfill is bounded, resumable, and verifiable
• Indexes match real read and write paths and do not duplicate existing ones
• Transactions are short and handle concurrency failures with a safe retry
• A rollback or forward-fix plan is written down before the change ships
• Destructive steps are separated into their own later change
• Tests cover constraints, migration behavior, and representative queries

See also

• Load standards-security for PII, tenancy, secrets, and audit requirements.
• Load standards-api-design when persistence choices leak into published contracts.
• Load role-site-reliability-engineer for production migration and rollback risk.
• Load role-data-engineer for analytics models, pipelines, and large backfills.
• Load standards-testing for migration, repository, and integration tests.