Database Design

Database Design

The schema outlives every framework, every rewrite, and every AI model that touches the codebase. Code bugs ship and get fixed; schema mistakes and corrupted data are forever. Design the schema as if the application code were hostile — because one day, some version of it will be.

Principle: the database defends its own invariants

Any rule the business cannot tolerate being violated goes in the schema, not only in application code:

• NOT NULL by default; nullable is an explicit decision meaning "absence is a valid state" (and every reader must handle it).
• FOREIGN KEY for every reference. UNIQUE for every natural key (mobile number, invoice number per tenant, SKU per account). CHECK for domains (quantity >= 0, status in allowed set, rate between 0 and 100).
• Application-level checks are UX; database constraints are truth. You need both, but only one of them holds under race conditions, bad deploys, and manual fixes.

Modeling rules that prevent the classic disasters

• Money and quantity are exact decimals (NUMERIC), never float — no exceptions, including "it's just a percentage". Store currency explicitly if there could ever be more than one.
• Timestamps: store UTC (timestamptz), render in local zone. Name columns *_at. Every mutable table gets created_at/updated_at.
• IDs: surrogate primary key (UUID or bigint — pick per project and stay consistent); enforce natural keys with UNIQUE constraints, don't make them the PK. Never expose sequential IDs where enumeration leaks business volume (invoice counts, user counts) unless numbering is a product requirement.
• State machines as data: a status column needs its legal transitions written down (in code comments/docs) and enforced in exactly one service function. If history matters, an append-only events/audit table beats overwriting.
• Financial and inventory data is append-only at heart: model corrections as new compensating rows (credit notes, stock adjustments with reasons), not UPDATEs that destroy what happened. If a regulator, accountant, or angry customer could ask "what was it before?", keep the before.
• Soft delete vs hard delete is a product decision — but referencing rows must never dangle either way; decide ON DELETE behavior per FK deliberately (RESTRICT is the safe default).
• Multi-tenant: account_id NOT NULL on every tenant table, composite indexes leading with it, and row-level security or a mandatory scoping mechanism so a missing WHERE clause fails closed, not open.

Normalization: the practical rule

Normalize until it hurts (one fact, one place — eliminate update anomalies), then denormalize only with a named owner for the copy and its update path. Storing line_total = qty × rate is fine for immutable invoice lines (they're history, frozen at sale time — that's not denormalization, that's a snapshot). Storing a customer's "current balance" is a cache — it needs either a recomputation job or transactional dual-writes, and a way to audit drift.

Concurrency: assume two requests arrive at once

• Any read-modify-write (stock decrement, balance update, counter, "next invoice number") needs an explicit strategy: atomic single UPDATE (SET stock = stock - :n WHERE stock >= :n), SELECT ... FOR UPDATE, or a serializable/retry pattern. "We'll be small" doesn't prevent double-submits from one user's double-click.
• Uniqueness under concurrency is a constraint + handled violation, not check-then-insert.
• Multi-row financial writes (invoice + lines + stock + ledger) are one transaction, all or nothing — and keep transactions short; never call external APIs inside one.

Indexes and queries

• Index every FK, every column in frequent WHERE/ORDER BY, and tenant-scoped composites (account_id, x). But each index taxes every write — add them for observed query shapes, not superstition.
• Before shipping a list endpoint: it must paginate (keyset for large/growing sets), and its query must not be N+1 (verify by looking at emitted SQL once, not by assuming the ORM is smart).
• EXPLAIN any query you're about to optimize; never index-guess.

Migration discipline

• Migrations are code-reviewed, hand-verified artifacts — whether hand-written or generated, read every line and know its lock behavior before it touches production data.
• Additive first; destructive only after code no longer reads the old shape (see deployment-safety: expand → migrate → contract).
• Every migration answers: is it idempotent/re-runnable? does it run safely while old code is live? what's the down-path or restore plan? does the backfill batch (not one giant UPDATE locking the table)?

When someone says "let's use NoSQL / JSONB for this"

JSONB/document columns are right for genuinely schemaless payloads you don't query relationally (provider webhook dumps, user-defined custom fields). They are wrong for anything with relationships, constraints, or reporting needs — that's just a schema you've decided not to enforce. Default to relational; earn your way out.