Database Design Starts With Invariants

A database schema is more than a storage shape. It is the final shared authority for facts that must remain true across requests, workers, retries, and concurrent users. Good design begins by naming those invariants before choosing table names or an object-relational mapping.

Consider identity, ownership, lifecycle, and uniqueness. Which actor owns a record? Can ownership change? Which states are valid, and which transitions are forbidden? Can two active rows represent the same logical claim? Application validation improves feedback, but constraints protect the invariant when another code path, migration, or concurrent transaction reaches the data.

Data exposure deserves the same precision. Public message content and private moderation records have different audiences and retention rules, so they should not be mixed merely because they share a feature. Row-level security, grants, and server-owned mutation functions can create useful layers, but each layer needs direct tests for allowed and denied behavior. A policy that exists in a migration is not proven until the relevant role exercises it.

Index design follows access patterns. Write down the filters, ordering, pagination cursor, and expected cardinality. Keyset pagination usually gives a more stable path through changing data than large offsets, but its composite ordering must be deterministic. An index should support a known query, not decorate every column preemptively.

Transactions protect multi-step meaning. Rate limits, ownership changes, message creation, and ledger updates should remain atomic when partial completion would violate the domain. Retry behavior and idempotency need explicit keys or constraints rather than hope that duplicate requests are rare.

Finally, treat migrations as production code. Make forward changes reviewable, rehearse them against representative data, define rollback or recovery behavior, and verify permissions after application. A strong schema reduces how many defensive assumptions every caller must repeat. It becomes a compact, enforceable expression of the system's shared truths.