Database design

The schema outlives the code. Get keys, constraints and migrations right; everything else can change.

1. Pick the database

• SQLite: local apps, prototypes, single-server small sites, desktop tools. One file, zero setup.
  Enable WAL (PRAGMA journal_mode=WAL) and foreign keys (PRAGMA foreign_keys=ON).
• Postgres: anything multi-user in production, concurrent writes, JSONB, full-text search. Free hosted
  options: Neon, Supabase. Local: Docker postgres:16.
• Write portable SQL through an ORM so you can start on SQLite and move to Postgres.

2. Model the data

1. List the nouns (users, projects, tasks) and relationships (one-to-many, many-to-many).
2. One table per entity, plural snake_case names (users, project_members), snake_case columns.
3. Every table: id primary key (integer identity, or UUID/ULID when ids are public or generated
   client-side), created_at, updated_at (timestamps in UTC, timestamptz in Postgres).
4. One-to-many: foreign key on the "many" side (tasks.project_id). Many-to-many: join table with a
   composite unique key (project_members(project_id, user_id)).
5. Normalize to avoid duplicated facts (store user_id, not the user's name). Denormalize only with a
   measured reason (a counter, a snapshot like an order's price at purchase time).

3. Constraints are free correctness

• NOT NULL by default; nullable only when "unknown" is a real state.
• UNIQUE for natural keys (email, slug). Store emails lowercased or use citext (Postgres).
• Foreign keys with explicit ON DELETE (CASCADE for owned children, RESTRICT or SET NULL otherwise).
• CHECK for ranges and enums (status IN ('todo','doing','done')), or a native enum.
• Money as integer cents (or numeric(12,2)), never float.

4. Indexes

• Primary keys and unique constraints are indexed automatically. Foreign key columns are not (in
  Postgres): index them.
• Index columns used in WHERE, JOIN and ORDER BY of frequent queries. Composite index order: equality
  columns first, then range/sort ((project_id, created_at)).
• Do not index everything; each index slows writes. Check with EXPLAIN (Postgres EXPLAIN ANALYZE).

5. Soft delete and history

• Soft delete (deleted_at timestamptz NULL) when users expect undo or for audit; then every query must
  filter deleted_at IS NULL, and unique constraints need a partial index (WHERE deleted_at IS NULL).
• Otherwise hard delete. For audit trails, an append-only events table beats soft delete everywhere.

6. ORM setup

Prisma (npm i -D prisma, npm i @prisma/client, npx prisma init --datasource-provider sqlite):

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  tasks     Task[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  @@map("users")
}
model Task {
  id     Int    @id @default(autoincrement())
  title  String
  userId Int    @map("user_id")
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  @@index([userId])
  @@map("tasks")
}

Migrate in dev: npx prisma migrate dev --name add_tasks. Production: npx prisma migrate deploy.
Drizzle (npm i drizzle-orm, npm i -D drizzle-kit): schema in TS, npx drizzle-kit generate then
npx drizzle-kit migrate.
Python: SQLAlchemy 2 (pip install sqlalchemy alembic), typed Mapped[...] models,
alembic revision --autogenerate -m "add tasks", then alembic upgrade head. Read autogenerated files.

7. Migrations without losing data

• Never edit a migration that has run anywhere else; add a new one. Commit migrations with code.
• Never use prisma db push, drizzle-kit push or "drop and recreate" against a database with real data.
• Renames: ORMs often generate DROP + ADD (data loss). Read the generated SQL; write a rename instead.
• Breaking changes in steps (expand/contract): add the new nullable column, backfill, switch the code,
  then add NOT NULL / drop the old column in a later migration.
• Back up first: copy the SQLite file, or pg_dump -Fc dbname > backup.dump. Ask the user before any
  destructive migration on real data.

8. Seeding

A seed script (prisma/seed.ts, scripts/seed.py) that is idempotent (upsert by unique key), creates
realistic sample data, and never runs against production by accident (check an env var).

Check before you finish

1. Migration applies cleanly on a fresh database and on a copy of the existing one.
2. Seed runs twice without errors or duplicates.
3. Query the main screens' data and check EXPLAIN uses indexes for the hot queries.
4. Constraints actually reject bad data (try inserting a duplicate email).

Common mistakes

• Storing lists as comma-separated strings; JSON columns for data you filter on.
• Floats for money; local-time timestamps; no foreign keys.
• N+1 queries: fetch relations with include/joins, not one query per row.
• Applying autogenerated migrations without reading them.