A database schema design checklist is useful when it changes a decision, not when it becomes a box-ticking exercise at the end of delivery. Reliable digital operations depend on stored facts having durable meaning: an order must be distinguishable from a quote, an approval must retain its actor and time, and a correction must not erase the history that explains why it happened. The checklist below turns those expectations into a review that product, engineering, security, and operations can share.
Use it at three moments: before the first model, before a risky migration, and after production evidence reveals a gap. The amount of detail can vary, but the questions should stay consistent. Start with one representative workflow, walk the normal and difficult paths, and record what the database must prevent, what the service must coordinate, and what support must be able to recover.
For a deeper model, see Edilec's database schema design guide, technical debt checklist, background jobs field guide, and Node.js APIs guide. The checklist connects persistent data to interfaces, asynchronous writes, and maintenance decisions.
Start with the operational promise
Write the business outcome the data supports and the harmful failure you need to prevent. An inventory service may promise that a reserved item is not sold twice; a billing service may promise that one external charge produces one internal entry; an access system may promise that a revoked role cannot authorize a new action. This statement tells the reviewer which invariants, timestamps, identities, and recovery evidence matter. It also prevents a team from optimizing a table before understanding the decision it serves.
Name actor, source, authority, lifecycle, retention, and correction owner. A row can be technically valid and still be business-invalid if it came from an untrusted source or represents a state that should have required approval. Separate the current state from events or history when different consumers need different meanings. If one table is intentionally both, document the rule and test how readers interpret it.
| Checklist item | Question | Evidence |
|---|---|---|
| Business promise | What outcome does the record support? | Journey and harmful failure statement |
| Row grain | What does one row represent? | Example rows with edge cases |
| Authority | Which source may change the fact? | Ownership and write-path map |
| Lifecycle | Which states and transitions are valid? | State table and transition tests |
| Correction | Who can repair and how is it recorded? | Scoped repair procedure and audit record |
Check identity, grain, and relationships
Use a stable local identifier and preserve external references as separate attributes. Define whether an external identifier is unique for all time, within a supplier, or only while active. Write the grain in plain language and test it with duplicate, missing, merged, and split cases. A customer, workspace, account, and legal entity may relate without being interchangeable. If the model collapses them for convenience, future permissions and reporting will inherit the ambiguity. When a type choice affects precision, time zones, or JSON representation, compare the engine's data type documentation with the product's actual examples instead of choosing from habit.
Review cardinality in both directions. One order can have many lines; one line should normally belong to one order. A many-to-many relationship needs a join record with its own meaning, effective time, and uniqueness rule. Use foreign keys where the database can enforce the relationship. The PostgreSQL constraints documentation provides the engine vocabulary, while the local review determines which relationships are mandatory, optional, historical, or staged.
Check constraints and invalid states
List rules that every writer must honor: required values, allowed ranges, unique combinations, valid parent records, and mutually exclusive states. Use constraints for facts the database can prove. Application validation can provide a friendly message, but it is not a reliable control when imports, scripts, administrators, background jobs, and future services also write data. Test the deployed connection and migration settings rather than assuming a constraint in source code is active everywhere.
Be careful with deletion and nullability. A missing value might mean unknown, not applicable, pending, or removed. A soft delete changes uniqueness and query behavior; a cascade can remove evidence or dependent work; a retained record may need to remain addressable without remaining active. For SQLite deployments, verify foreign-key enforcement in the actual runtime because the SQLite foreign-key documentation makes clear that configuration and indexes affect behavior.
| Integrity rule | What to verify | Evidence of readiness |
|---|---|---|
| Identity | Primary and alternate keys cannot collide | Duplicate insert tests |
| Reference | Parent and child lifecycle are compatible | Foreign-key and delete tests |
| State | Impossible combinations are rejected | Check constraints and transition tests |
| Nullability | Missing and inapplicable values differ | Examples and consumer agreement |
| Retention | Delete, archive, and restore preserve policy | Lifecycle and recovery exercise |
Check transaction and concurrency behavior
Run the workflow with two writers, a retrying client, and a delayed dependency. Ask what should happen when both requests read the same version, when a unique insert races, or when an external event arrives twice. PostgreSQL transaction guidance explains the read and write behavior the checklist should test; add the business decision about conflict response, retry boundary, isolation expectation, and user-visible state.
Keep transactions bounded and purposeful. Do not hold locks while waiting for a network call or a human approval. If a workflow spans systems, use a durable state and an operation identifier, then make retries and compensation explicit. Preserve enough evidence to reconcile partial completion. A generic error that leaves the operator unsure whether the business effect happened is a design failure even when the database transaction itself rolled back cleanly.
Check migration safety and reversibility
Classify a change as additive, behavioral, destructive, or data-transforming. Add structures before requiring them, deploy readers and writers that tolerate the transition, backfill in observable batches, compare old and new results, switch behavior, and clean up after a stated window. PostgreSQL table modification guidance describes platform mechanics; your checklist should capture table size, lock risk, index impact, replication lag, write amplification, pause condition, backup evidence, and recovery time.
Do not call a migration reversible because the application can roll back. A data transformation may be lossy, a column removal may be permanent, and a partially completed backfill may require a repair script. Record the rollback boundary, who can stop it, and how the team reconciles rows that crossed the boundary. Test with production-like volume and failure injection. A migration that only succeeds when nobody interrupts it is not ready for a live system.
Check query paths and capacity
List the reads and writes that matter: lookup by external ID, queue by status and due time, history by account, report by period, or search by normalized value. Choose indexes from those paths, cardinality, ordering, and data volume. PostgreSQL index documentation explains the performance trade-off; the review should also ask which indexes slow ingestion, consume storage, or become misleading after a distribution change.
Test the slow and large cases. Include the oldest records, the most active account, a broad export, a low-selectivity filter, and concurrent writes. Set a practical latency or resource threshold with a named action. If a projection or cache is needed, document its source, grain, freshness, rebuild path, and reconciliation signal. A fast query that returns an ambiguous or stale business answer is not operationally successful.
Check access, query safety, and data paths
Review application roles, migrations, reporting users, support tools, exports, replicas, backups, and test fixtures. Sensitive fields may need separate access, retention, encryption, or masking rules. OWASP's SQL Injection Prevention Cheat Sheet supports parameterized query practices; also verify authorization at record, field, search, bulk, and export boundaries. A safe UI is not enough if a direct endpoint accepts another record's identifier.
Make the schema change trail inspectable. Store migration version, actor, reason, affected scope, and result. Review emergency edits for a durable follow-up rather than letting a manual repair become an undocumented branch. Check that logs and analytics do not copy secrets or unnecessary personal data. Retention and deletion tests must include derived stores because removing a column from the primary table does not erase it from every backup or export.
Check recovery and reconciliation
A schema supports reliable operations when a team can identify an affected record, restore the right scope, replay or compensate safely, and prove that dependent systems agree afterward. Test one-record correction, batch repair, restore from backup, reprocessing of a failed event, and deletion under retention policy. Record row counts, checksums, business totals, and unresolved exceptions. A backup that restores tables but not keys, migrations, permissions, or dependent projections is incomplete evidence.
Review signals after launch: constraint failures, deadlocks, lock waits, migration progress, replication lag, failed jobs, correction tickets, duplicate events, and unexplained report differences. Pair each threshold with an owner and action. The goal is not to alert on every database symptom; it is to make the next decision clear. If an exception remains open, show its age and authority so it cannot disappear in an unowned queue.
Turn the checklist into a working review
Run the checklist with the people who own the journey, not only the database expert. Product or policy explains the intended fact, engineering explains implementation and evolution, operations explains recovery and support, security explains access and exposure, and analytics explains downstream meaning. Walk through normal, duplicate, delayed, unauthorized, corrected, archived, and restored cases. Stop when an answer is “the application should handle it” without a test or owner.
Record decisions at the level of evidence. Link a constraint to a case, a migration gate to a query or reconciliation, a permission to a denial test, and a repair path to a rehearsal. Revisit the checklist when a new service writes data, a new tenant or region arrives, a table crosses a capacity threshold, or an incident exposes an assumption. A living review is more valuable than a perfect document that nobody consults during change.
Key takeaways from the schema checklist
- Start with the operational promise, row grain, authority, lifecycle, and correction owner.
- Use constraints and foreign keys for rules every writer must honor, and test the deployed configuration.
- Exercise concurrency, duplicate delivery, delayed dependencies, and partial completion before calling a workflow reliable.
- Classify migrations, measure locks and lag, define the pause and recovery boundary, and rehearse it.
- Choose indexes and projections from real access paths while preserving authoritative data meaning.
- Check permissions, query safety, retention, backups, restore, reconciliation, and support evidence as one operating system.
Schema operations questions
What is the first item on a schema design checklist?
State the grain and authority of the record. Explain what one row represents, which process may change it, which timestamps matter, and which states are valid. Without that foundation, keys, constraints, indexes, and reports can all be technically consistent while describing different facts.
Which schema checks matter most for operations?
Prioritize identity, relationships, invariants, concurrency, migration safety, access, recovery, and reconciliation. The order should reflect consequence: a rule that prevents duplicate financial effects deserves stronger evidence than a convenience field used only by a non-critical report.
Can a checklist replace a database design review?
No. It creates coverage and prompts evidence, but a review must apply the questions to a real journey, representative data, failure cases, and the people responsible for support and recovery. A checkbox without a test, owner, or decision is only a reminder.
Conclusion: review data as an operating system
A reliable database schema design checklist connects data meaning to the decisions a live operation must make. Verify grain and authority, enforce invariants, exercise concurrency, plan migrations, test access and query paths, and rehearse recovery and reconciliation. Used at the right moments, the checklist keeps a schema understandable as volume, services, operators, and business rules change around it.