Database Design

---
name: database-design
description: "Design database schemas and run migrations safely: SQLite vs Postgres, tables, keys, normalization, indexes, constraints, timestamps, soft delete, Prisma or Drizzle (Node) or SQLAlchemy + Alembic (Python), seeding, and never losing data. Use when the user asks for a database, schema, tables, models, migrations, ORM setup or data storage."
---

# 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`):
```prisma
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.