Database Design
Start from invariants and access patterns; a schema is an operational contract.
Design sequence
- Identify entities, ownership, lifecycle, source of truth, cardinality, and
deletion/retention rules.
- Write invariants and transaction boundaries before choosing tables or
collections.
- Map real reads, writes, filters, sorts, joins, uniqueness, and expected
scale. Add indexes for measured access paths, not guesses.
- Decide consistency, isolation, idempotency, concurrency, and failure
recovery at each boundary.
- Design an expand-migrate-contract migration: backward-compatible expand,
backfill/dual-write when needed, cutover, verification, and cleanup.
- Protect sensitive data with least privilege, encryption, minimization,
auditability, and retention limits.
Read schema-checklist.md. Include rollback,
backup/restore, lock duration, online migration behavior, and observability in
the plan. Treat production data as irreplaceable unless a tested recovery path
proves otherwise.
Evidence
Use representative query plans, migration rehearsal on a safe fixture, invariant
tests, concurrency checks, and restore evidence. A migration that applies once
is not proven safe to roll forward, retry, or roll back.
Completion condition
The model's invariants, access paths, migration lifecycle, privacy boundary,
recovery path, and proof commands are explicit.
1---2name: database-design3description: Designs or reviews relational, document, key-value, graph, or search data models with ownership, invariants, access paths, migrations, consistency, retention, privacy, and recovery behavior. Use for schema changes, new data stores, indexes, migrations, persistence boundaries, or query-driven design. Not for a query-only bug without a model change or for API contract design.4---56# Database Design78Start from invariants and access patterns; a schema is an operational contract.910## Design sequence11121. Identify entities, ownership, lifecycle, source of truth, cardinality, and13 deletion/retention rules.142. Write invariants and transaction boundaries before choosing tables or15 collections.163. Map real reads, writes, filters, sorts, joins, uniqueness, and expected17 scale. Add indexes for measured access paths, not guesses.184. Decide consistency, isolation, idempotency, concurrency, and failure19 recovery at each boundary.205. Design an expand-migrate-contract migration: backward-compatible expand,21 backfill/dual-write when needed, cutover, verification, and cleanup.226. Protect sensitive data with least privilege, encryption, minimization,23 auditability, and retention limits.2425Read [schema-checklist.md](references/schema-checklist.md). Include rollback,26backup/restore, lock duration, online migration behavior, and observability in27the plan. Treat production data as irreplaceable unless a tested recovery path28proves otherwise.2930## Evidence3132Use representative query plans, migration rehearsal on a safe fixture, invariant33tests, concurrency checks, and restore evidence. A migration that applies once34is not proven safe to roll forward, retry, or roll back.3536## Completion condition3738The model's invariants, access paths, migration lifecycle, privacy boundary,39recovery path, and proof commands are explicit.
thiientv