Database SME

Model the domain, then pick the store that fits the access patterns — not the hype. Schema mistakes are the most expensive to undo, so spend the thinking here.

Choosing a store

• Map the access patterns (reads vs writes, query shapes, consistency needs) before choosing.
• Relational by default for anything with relationships and integrity needs. Document/KV/graph only when the access pattern genuinely fits.
• Embedded (SQLite) for local-first / sovereign apps (Nrupal's vault-tracker, zerok-* style): single-file, zero-server, encrypt-at-rest.

Relational design

• Normalize to 3NF first; denormalize deliberately for hot read paths, and document why.
• Constraints are data integrity: NOT NULL, UNIQUE, FK, CHECK. Let the DB enforce invariants.
• Use the right types (timestamps with tz, numeric for money, enums/lookup tables for categories).

Indexing

• Index for your actual query patterns. Composite index column order matters (equality columns first, then range).
• Aim for covering indexes on hot queries; remember every index taxes writes.
• Always EXPLAIN/ANALYZE before and after — measure, don't guess.

Transactions & consistency

• Know your isolation level and what anomalies it permits. Keep transactions short.
• Design writes to be idempotent where retries are possible.
• Be explicit about the consistency vs availability trade-off for distributed stores.

Migrations (safe by default)

• Forward-only; reverse by writing a new migration, never by editing history.
• Backward-compatible deploys via expand/contract: add new → backfill → switch reads → stop writes to old → drop old. No big-bang schema swaps against live traffic.

Performance

• Measure first. Kill N+1 queries. Prefer keyset (cursor) pagination over OFFSET for large sets.
• Connection pooling; bounded result sets; cache deliberately, invalidate honestly.

Security & sovereignty

• Parametrized queries only — never concatenate user input.
• Least-privilege DB users per service; no shared superuser.
• Encryption at rest; for zero-knowledge products, encrypt client-side and store ciphertext so the DB never sees plaintext (user holds the keys).

Backups

• A backup you haven't restored is a hope, not a backup. Test restores on a schedule.

Anti-patterns to refuse

• String-concatenated SQL; indexing everything; OFFSET pagination at scale; editing past migrations; storing money as float; trusting an untested backup.