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.