database-engineering

Database Engineering

The schema outlives the code, the framework, and usually the company. Application bugs are fixed with a deploy; data-model mistakes are fixed with a migration project, and data loss is fixed with nothing at all.

---

The five questions that open every review

1. What are the constraints? Not in the ORM — in the database. SELECT against the catalogue, not the model file.
2. Which foreign keys are unindexed? Almost always the top performance finding, and it never shows up in development.
3. What does the hottest query actually do? EXPLAIN ANALYZE, not intuition.
4. When was a backup last restored? Not "do backups exist" — restored.
5. What happens to a row when the user asks to be deleted? If nobody can answer, that is a compliance finding as well as a design one.

---

Schema design

Constraints belong in the database. The ORM is not the only writer — a migration script, an admin tool, an analytics job, a psql session or a future service will write directly. A rule enforced only in application code is a rule that will be broken.

• Primary key on every table.
• Foreign keys with an explicit ON DELETE — CASCADE, RESTRICT or SET NULL chosen per relationship. Copying CASCADE everywhere is how a user deletion silently removes audit records.
• NOT NULL wherever the business says required. Nullable-by-default columns push the check into every consumer, forever.
• UNIQUE on what is genuinely unique, including composite uniqueness.
• CHECK for invariants: non-negative amounts, valid ranges, valid enum values, end_date > start_date.
• Enums as a database type or a lookup table with a foreign key — not a free string column with a comment.

Types matter more than they look

Data	Use	Never
Money	NUMERIC(19,4) or integer minor units	Float. Ever
Timestamps	timestamptz, stored UTC	Naive local time
Identifiers	UUIDv7 / ULID for public ids	Sequential integers exposed publicly — they leak volume and enable enumeration
Text	text with a CHECK on length	Arbitrary varchar(255) chosen by habit
JSON	jsonb, for genuinely schemaless data only	JSON as an escape from schema design
Booleans	boolean NOT NULL DEFAULT false	Nullable three-state booleans