ADR-0002 — Repository Pattern for all DB access

Field	Value
Status	🟢 Accepted
Date	2026-03-23
Deciders	@aphaiboon
Related ADRs	ADR-0001 — Drizzle ORM (built on top)
Related incidents (motivated this ADR)	FM-001, FM-002, FM-012, FM-020, FM-021, FM-027, FM-031, FM-032, FM-047

Context

Between January and March 2026, CMMD shipped six production database incidents caused by inline db.select(...) queries in route handlers. The pattern of failure was consistent:

1. AI-assisted code generation produced a new route handler with an inline query
2. The query missed organizationId in the WHERE clause OR a soft-delete filter
3. The route shipped behind a feature flag that touched a small audience first
4. Once the flag broadened, customer data leaked across orgs OR archived rows resurfaced
5. Postmortem identified the missing filter; we added the filter; the same class of bug recurred 3–6 weeks later in a different route

The root cause was structural, not human. As long as db was importable from any file, any new route was one missed clause away from a multi-tenant violation. Lint rules can flag specific patterns but cannot enforce “every query touches organizationId” — that requires the query to live where the rule can run.

By FM-032 (2026-03-28 — thread_members 42703 outage from a table rename that missed the pgTable() string), the cost of inline queries was clear: ~30 hours of incident response in 90 days, customer trust erosion on two of the six incidents.

Decision

Only repositories may import db. Routes and services must call getDb() from server/repositories/base.ts. All multi-tenant queries are gated by required organizationId parameters via OrgQueryParams.

• New file structure: server/repositories/{domain}.repository.ts, types in server/repositories/types/{domain}.types.ts
• Two method shapes only: CRUD (findById, create, update, delete) and QUERY (query(params): PaginatedResult<T>)
• OrgQueryParams interface — organizationId: number is required, TypeScript-enforced
• paginate() helper for all paginated lists — no hand-rolled pagination
• Routes are thin: validate → call repo → respond. Max ~15 lines per handler
• Background service exception for analytical queries in jobs (max 5 inline queries per file, explicit org scoping, [PREFIX] log tag)
• CI hard gate: scripts/enforce-repository-pattern.sh blocks PRs with direct db imports in routes/services

Alternatives considered

Option A — ESLint rule banning db.select(...).from(table) without .where(eq(table.organizationId, ...))

• ✅ No file restructuring
• ✅ Caught at lint time
• ❌ Regex/AST pattern matching can’t enforce that the eq references the correct organizationId variable
• ❌ Doesn’t catch the rename bug (FM-032) — that’s not a missing WHERE, it’s a wrong table reference
• ❌ Easy to bypass with intermediate variables
• Rejected: Misses the underlying bug class

Option B — Row-Level Security (RLS) at the Postgres layer

• ✅ Defense in depth — even if app code is buggy, RLS blocks cross-org reads
• ✅ Industry-standard for multi-tenant SaaS
• ❌ Doesn’t replace the need for app-level discipline
• ❌ Adds operational complexity at the DB layer
• ❌ Doesn’t catch missing soft-delete filters
• Deferred: Layered on top of repository pattern later (Q3 2026 target). Repository pattern first because it fixes 90%+ of issues at lowest complexity cost.

Option C — Service-only pattern (services import db, routes call services)

• ✅ Lighter than repositories
• ❌ Service interfaces don’t enforce org-scoping — same risk profile
• ❌ Services end up with both data access AND business logic — testability suffers
• Rejected: Doesn’t solve the underlying problem

Option D — Do nothing, keep current discipline + better code review

• Code review caught maybe 60% of issues before the 6 incidents shipped
• AI-assisted code generation increases throughput which decreases per-PR scrutiny
• Not viable. Incident frequency was trending up, not down.

Consequences

Positive

• ✅ TypeScript compile time enforces org-scoping (organizationId: number required, not optional)
• ✅ Single canonical place to enforce soft-delete, pagination, scoping rules
• ✅ Repositories easily unit-testable
• ✅ Drizzle features (relational queries, query helpers, $dynamic()) consolidated in repo files
• ✅ CI hard gate prevents regression
• ✅ Zero incidents in the FM-020–032 class since adoption (2026-03-23 → 2026-05-16)

Negative / trade-offs

• ⚠️ Up-front migration cost: existing inline queries had to be extracted to repos. Completed over ~2 weeks of focused refactor
• ⚠️ More files per domain (repo + types) — slight cognitive overhead for new contributors
• ⚠️ Background jobs occasionally need raw getDb().execute(sql) for analytical queries — scoped exception in CLAUDE.md

Risks

• 🟢 Mitigated: Direct db imports — CI gate blocks them
• 🟡 New engineers writing inline queries before learning the pattern — mitigation: scaffold templates (npm run scaffold <domain>) generate compliant boilerplate
• 🟡 Repository files growing past 400 lines — mitigation: split by sub-domain (e.g. comms-channels.repository.ts, comms-messages.repository.ts)

Implementation plan

• Define getDb(), paginate(), OrgQueryParams, PlatformQueryParams in server/repositories/base.ts — 2026-03-23
• Extract first ~10 high-traffic routes to repository pattern — 2026-03-24
• Build scripts/extract-queries-codemod.ts for inline query inventory — 2026-03-25
• Refactor remaining routes — completed 2026-03-30
• Add scripts/enforce-repository-pattern.sh as CI hard gate — 2026-03-30
• Document discipline in Data Access — Repository Pattern standard — 2026-04
• Background service exception documented — 2026-04

Success metrics

Metric	Baseline (pre-March 2026)	Current (May 2026)	Target
Inline db imports in routes/services	~80	0 (1 exempt)	0
Multi-tenancy incidents per quarter	2–3	0	0
Time-to-add new table (incl. routes)	4–6 hours	1–2 hours (with scaffold)	<2 hours
Routes per file with >15 lines per handler	~40%	<5%	<10%

Review

Next review: Permanent — only revisit if a fundamentally better pattern emerges.

References

• Data Access — Repository Pattern standard — full standard built on this ADR
• ADR-0001 — Drizzle ORM
• Database Migrations standard
• Runbook — Database Migration Deploy
• Incidents — FM index
• .claude/rules/data-access-standard.md, scripts/enforce-repository-pattern.sh