Invariant register
Make the database defend the truths the product depends on
Application checks improve experience. Database constraints and controlled migrations protect state when jobs, scripts, retries or future code bypass that experience.
Identity and keys
Define stable records independently from labels users may edit.
- Question
- What uniquely identifies each entity?
- Failure
- Emails or names silently become permanent foreign keys
- Proof
- Primary, unique and foreign-key constraints
Business invariants
Express states that must never coexist or disappear.
- Question
- Which facts must remain true after every write?
- Failure
- Duplicate subscriptions or orphaned records
- Proof
- Constraints plus transactional negative tests
Tenant and ownership
Bind every private row to the correct security boundary.
- Question
- Which tenant owns this record and relationship?
- Failure
- Queries omit tenant scope on one path
- Proof
- Policy, query and cross-tenant tests
Migration path
Change structure without assuming an empty or motionless database.
- Question
- Can old and new code coexist during rollout?
- Failure
- One destructive migration blocks or discards live data
- Proof
- Staged expand, backfill, verify and contract plan
Restore reality
Verify that the stored state can be reconstructed within the product need.
- Question
- What is backed up and how recently?
- Failure
- A backup exists but restore has never been attempted
- Proof
- Timed restore with integrity checks
Operating principle
Start with data language before choosing tables
Generated schemas often mirror the current screens: one table per form, loosely typed status strings and relationships implied by names. That is fast, but it hides the product rules needed when concurrency, retries and historical reporting arrive.
Write a short glossary of entities, ownership, lifecycle and invariants. Then design storage and migrations that preserve those facts under realistic failure and growth.
- Separate stable identity from mutable labels
- Use transactions for multi-step invariants
- Version schema changes with application changes
- Test restore before declaring recoverability
Applied example
Failure example: duplicate paid membership
Two payment webhooks arrive nearly together. The generated handler checks for an existing membership and then inserts, but the table has no uniqueness constraint.
- Both requests read the same empty state
- Both inserts succeed
- The interface-level check was not atomic
- A unique constraint and idempotent transaction would make the invariant durable
Plain answers
Questions to resolve before shipping
Should every rule become a database constraint?+
No. Use constraints for durable truths the database can evaluate reliably; keep contextual workflows in application logic and test both layers.
Is an ORM schema enough documentation?+
It documents structure, but usually not business meaning, ownership, retention, migration assumptions or recovery objectives.
Can I redesign the schema after launch?+
Yes, but use staged migrations, compatibility windows, verified backfills and rollback or forward-fix procedures appropriate to the consequence.