ADR-0001 — Adopt Drizzle ORM for all DB access

Field	Value
Status	🟢 Accepted
Date	2026-02
Deciders	@aphaiboon
Related ADRs	ADR-0002 — Repository Pattern (built on top of Drizzle)
Related incidents	FM-004, FM-005, FM-020, FM-021, FM-026, FM-030, FM-031, FM-032, FM-042 (Drizzle-related migration incidents)

Context

Early CMMD had a mix of raw SQL via pg client and an attempt at Prisma. Two real-world frictions emerged:

1. Schema drift between code and DB. Prisma’s migration model required hand-editing migration files, then re-generating client code. Subtle mismatches caused production errors at runtime (e.g. a column existed in code but not in DB after a partial deploy).
2. TypeScript inference at query boundaries. Both raw SQL and Prisma’s generated types had pain points: raw SQL had no type safety, Prisma’s types didn’t compose well with conditional WHERE clauses or partial selects.

We needed:

• Single source-of-truth schema in TS that generates SQL migrations automatically
• Full TS inference on all queries, including joins and partial selects
• Lightweight runtime — no codegen, no client generation step
• Native Neon/PostgreSQL support (we use Neon for serverless DB)
• Composable query helpers (org-scoping, soft-delete, search filters)

Decision

Drizzle ORM (drizzle-orm + drizzle-kit) for ALL database access.

• Schema files in shared/schema/{domain}.ts (organized by domain — users, organizations, tasks, brain, …)
• drizzle-kit generate invoked via npm run db:gen "description" produces SQL migrations + JSON snapshot
• Migrations apply via npm run db:migrate at Railway deploy time — see Runbook — Database Migration Deploy
• Type inference end-to-end from schema to query results
• Relational queries (db.query.*) used for any joined data
• Composable helpers (withOrgScope, withSoftDelete, withSearch) in server/lib/query-helpers.ts

Alternatives considered

Option A — Prisma

• ✅ More mature ecosystem, better docs
• ✅ Migrations workflow well understood
• ❌ Codegen step adds build complexity and Lambda/Railway deploy size
• ❌ Type composition for partial selects + dynamic where clauses is awkward
• ❌ Schema drift in our early experience (hand-edited migration files vs prisma.schema)
• Rejected: Heavier, less ergonomic for our query patterns (lots of conditional filters, partial selects, multi-tenant scoping)

Option B — Raw SQL via pg + manual TypeScript types

• ✅ No magic, full control
• ✅ Smallest runtime
• ❌ Zero type safety — query results typed by hand, easily drift
• ❌ Manual migration files
• ❌ No composable filters — every query rebuilds WHERE clauses
• Rejected: Hand-typed query maintenance compounds as the codebase grows

Option C — TypeORM

• ✅ Decorator-based, somewhat type-safe
• ❌ Heavy, opinionated; uneven active development
• Rejected: Worst of both worlds — heavy abstraction without Drizzle’s type ergonomics

Option D — Do nothing, stay on raw SQL

• 2–4 hours of typing boilerplate per new table; each migration risks drift
• Not viable past 20 tables

Consequences

Positive

• ✅ End-to-end type safety — schema changes propagate to query call sites at compile time
• ✅ Migrations auto-generated from schema diff — no manual SQL for 90% of changes
• ✅ Single source of truth in shared/schema/
• ✅ Lightweight — no codegen step, no extra deploy size
• ✅ Relational queries reduce JOIN boilerplate
• ✅ Snapshot-based migration integrity catches drift early

Negative / trade-offs

• ⚠️ Less mature than Prisma — occasional rough edges with complex types
• ⚠️ Drizzle Studio UI less polished than Prisma Studio (rarely used)
• ⚠️ Smaller community — rare edge cases require reading source

Risks (mostly materialized)

• 🟡 Snapshot drift if engineers use drizzle-kit directly instead of npm run db:gen — mitigation: wrapper script + CLAUDE.md rule. Surfaced via FM-020, FM-026, FM-030, FM-042.
• 🟡 Migration files become un-rerunnable if not idempotent — mitigation: CI integrity test enforces IF NOT EXISTS from migration 0036+. Surfaced via FM-005.

Implementation plan

• Migrate existing tables to shared/schema/ — 2026-02
• Establish npm run db:gen wrapper enforcing snapshot integrity — 2026-02
• Add CI check: schema drift detection — 2026-03
• Document migration discipline in Database Migrations standard — 2026-03
• Add IF NOT EXISTS integrity test (from migration 0036) — 2026-03

Success metrics

Metric	Baseline (Prisma era)	Current	Target
Hours per new table	2–4	<1	<1
Migration-related incidents per quarter	2–3	0 (since FM-032)	0
Type errors caught at compile (vs runtime)	~60%	~95%	>90%

Review

Next review: Permanent — only revisit if Drizzle development stalls for 2+ quarters or a clearly superior alternative emerges.

References

• Drizzle ORM docs: orm.drizzle.team
• Database Migrations standard — discipline built on this ADR
• Runbook — Database Migration Deploy — operational procedure
• ADR-0002 — Repository Pattern — built on top
• .claude/rules/schema-organization.md, .claude/rules/database-migrations.md