Data Access — Repository Pattern (NON-NEGOTIABLE)

Established 2026-03-23. Six production incidents (FM-020 through FM-032) made this rule what it is. No exceptions for routes or request-handling services.

The 7 rules

1. Only repositories import db

Routes and services use getDb() from server/repositories/base. NEVER import { db } from '../db'.

CI hard gate: scripts/enforce-repository-pattern.sh. Migration complete: 0 route files with direct db import; 1 service file (brain-storage.ts, exempt due to class-based pattern).

Background service exception

Internal background jobs and intelligence services MAY use getDb().execute(sql\…`)` for specialized analytical queries when ALL of:

• (a) Query is not called from any route or request handler — runs only in background job context
• (b) Query includes explicit org scoping (organization_id filter, or userId filter where org isolation is guaranteed by caller)
• (c) File contains 5 or fewer inline queries — beyond that, extract a repository method
• (d) File uses a [PREFIX] log context tag (e.g. [MEMORY-CONSOLIDATOR]) for traceability

This exception does NOT apply to routes, request handlers, or any code in the hot path of an HTTP request.

2. Repository methods use plain-object params

Method parameters are TypeScript interfaces, never Drizzle SQL types. Callers don’t depend on ORM internals.

3. Explicit return types on every repo method

Define Row interfaces (e.g. UserRow, ChatRow). Never rely on Drizzle $inferSelect in the public interface. $inferSelect/$inferInsert are fine inside the repo file only.

4. Two method shapes only

• CRUD methods — findById, create, update, delete
• QUERY methods — query(params): PaginatedResult<T>

If it doesn’t fit either, it goes in a service.

5. Org-scoped repos require organizationId

Use OrgQueryParams — organizationId is required, not optional. Platform-scoped repos (admin tables) use PlatformQueryParams. TypeScript enforces it.

6. Use paginate() helper

All paginated list endpoints use paginate() from server/repositories/base.ts. No hand-rolled pagination.

7. Routes are thin

Validate with Zod → call repository/service → respond. Max ~15 lines per handler.

File structure

server/repositories/
  types/
    base.types.ts        — PaginatedResult, OrgQueryParams, PlatformQueryParams
    {domain}.types.ts    — Per-domain Row interfaces + QueryParams
  base.ts                — getDb(), paginate(), QueryExecutor type
  {domain}.repository.ts — Repository methods (max 400 lines)
  {domain}-{sub}.repository.ts — Sub-domain split if larger
  platform-misc.repository.ts  — Grouped low-frequency platform ops
  index.ts               — Barrel re-exports (every new repo MUST be added here)

Pattern: CRUD method (org-scoped)

async findById(
  id: number,
  organizationId: number,
  tx?: DbTransaction
): Promise<UserRow | null> {
  const db = getDb(tx);
  const [row] = await db.select({...}).from(users)
    .where(and(eq(users.id, id), eq(users.organizationId, organizationId)))
    .limit(1);
  return row ?? null;
}

Pattern: Query method (paginated list)

async query(params: UserQueryParams): Promise<PaginatedResult<UserRow>> {
  const db = getDb();
  const conditions = this.buildConditions(params);
  const page = params.page ?? 1;
  const limit = Math.min(params.limit ?? 50, 200);
  return paginate(
    db.select({...}).from(users).where(conditions).orderBy(...).limit(limit).offset((page-1)*limit),
    db.select({ count: count() }).from(users).where(conditions),
    page, limit
  );
}

private buildConditions(params: UserQueryParams): SQL | undefined {
  const c: SQL[] = [eq(users.organizationId, params.organizationId)];
  if (params.search) c.push(ilike(users.name, '%' + params.search + '%'));
  if (params.status) c.push(eq(users.status, params.status));
  return c.length ? and(...c) : undefined;
}

Pattern: Route handler (thin)

router.get('/users', requireAuth, asyncHandler(async (req, res) => {
  const params = userQuerySchema.parse(req.query);
  const result = await usersRepository.query({ ...params, organizationId: req.organizationId! });
  res.json(result);
}));

Drizzle features to USE

1. Relational queries (db.query.*)

For ANY query involving JOINs or related data. Relations are defined in shared/schema/{domain}.ts. Do NOT manually write leftJoin/innerJoin when a relation exists.

// ✅ relational query (automatic JOINs, type-safe columns)
const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
  columns: { id: true, email: true, firstName: true },
  with: { organizations: { columns: { id: true, name: true } } },
});

// ❌ manual JOIN
const [user] = await db.select({...}).from(users).leftJoin(orgs, eq(...)).where(...);

2. Composable query helpers

Use shared scopes from server/lib/query-helpers.ts — withOrgScope, withSoftDelete, withSearch. Don’t repeat conditions inline.

3. Drizzle-Zod for route validation

import { insertTaskSchema } from '@shared/schema';
const body = insertTaskSchema.pick({ title: true, priority: true }).parse(req.body);

Better than hand-written Zod — stays in sync when schema changes.

4. Type-safe subqueries (single round-trip)

const memberIds = db.select({ id: orgMembers.userId }).from(orgMembers).where(eq(orgMembers.orgId, orgId));
const messages = await db.select().from(msgs).where(inArray(msgs.senderId, memberIds));

5. $dynamic() for filter chains

For queries with optional filters built up conditionally.

CI enforcement

Gate	What it blocks
scripts/enforce-repository-pattern.sh	New direct db imports in routes/services
scripts/enforce-architecture-guardrails.sh	New top-level server/services/*.ts or server/routes/*.ts files
scripts/check-schema-drift.sh	Schema diverging from migration snapshot

All three run as required CI jobs and are wired into ci-complete.

Tooling

# Overview of all inline queries
npx tsx scripts/extract-queries-codemod.ts --scan

# Analyze one route file
npx tsx scripts/extract-queries-codemod.ts --file <path>

# Generate plan for a new domain
npx tsx scripts/extract-queries-codemod.ts --generate

When adding a new table: create the repo + types FIRST, then write routes that call the repo. Never write inline db.* in routes.

Related

• Database Migrations
• ADR-0002 — Repository Pattern
• Incidents — FM-020 to FM-032 in Incidents