ADR-0021: EntityRepository — Persistence Abstraction for Worker Services

• Status: Accepted
• Date: 2026-07-04
• Deciders: User + agent grilling session
• Issues: #352

Context

Worker services currently receive D1Database directly and call db.prepare(sql).bind(...params).first<T>()/.all<T>()/.run() inline. This creates three problems:

1. Untestable without SQL-string mocking — tests use mockD1.ts which matches exact SQL strings. A single whitespace change breaks every mock. This is brittle and makes refactoring SQL painful.
2. No scope abstraction — ADR-0016 introduced ScopeFilter for RBAC-scoped queries, but there's no standard place to inject it. Each service would need to check scope.type and append WHERE clauses individually — easy to forget.
3. Boilerplate duplication — every service writes the same SELECT cols FROM table WHERE id = ?1 pattern. A repository eliminates this.

The frontend already solved these same problems via StorageBackend interface (query/exec/transaction/onChange/close) with SQLiteStore and createMockStore/createEmptyMockStore test helpers.

Decision

Introduce EntityRepository<T> — a generic persistence interface for CRUD entities in the worker layer. Two implementations: D1EntityRepo (production, D1) and MemoryEntityRepo (tests, in-memory Map).

Interface

interface EntityRepository<T extends Record<string, unknown>> {
  findById(id: string): Promise<T | null>;
  list(filter?: QueryFilter, options?: ListOptions): Promise<{ items: T[]; cursor: string | null }>;
  create(id: string, input: Partial<T>): Promise<T>;
  update(id: string, changes: Partial<T>): Promise<T>;
  softDelete(id: string): Promise<void>;
}

Key design rules

Decision	Choice	Rationale
Not-found	findById returns T | null. update/softDelete throw EntityNotFoundError	findById = query (optional). Update/delete = command (must exist). Matches first<T>() return.
ID generation	Caller supplies ID via crypto.randomUUID()	Event-sourced systems need ID before write. Service retains control.
Create return	Returns the full entity (INSERT ... RETURNING *)	D1 supports RETURNING natively. Gives back server-set fields (createdAt).
Update style	PATCH — Partial<T>, only changed fields	Matches HTTP PATCH. Existing studentToUpdateParams() already does this.
Soft delete	softDelete(id) — sets status to 'DELETED'. Justification is entity-specific (handled by service).	Keeps interface clean. Justification columns vary per entity.
Scope	Constructor-injected (new D1EntityRepo(db, "students", cols, scope?))	Baked into the instance — can't be forgotten. Route handler creates repo once per request.
Row mapping	Thin repo — returns raw Record<string, unknown>. Service calls rowToStudent() etc.	Existing mappers (studentMapper.ts) are already well-tested. Minimizes migration change.
Pagination	Cursor-based (cursor?: string, returns { items, cursor })	Follows ADR-0013 (ISO 8601 updated_at cursor, descending).
Error handling	EntityNotFoundError extends Error. Validation stays in service layer.	Simple. Matches existing HttpError 404 pattern in handlers.

D1EntityRepo adapter

class D1EntityRepo<T extends Record<string, unknown>> implements EntityRepository<T> {
  constructor(
    private db: D1Database,
    private tableName: string,
    private selectColumns: string[],
    private options?: { scope?: ScopeFilter; idColumn?: string; statusColumn?: string },
  ) {}

  // SQL generation is internal:
  // - SELECT {selectColumns} FROM {tableName} WHERE {idColumn} = ?1
  // - scope WHERE clause appended to list() automatically
  // - softDelete uses {statusColumn} = 'DELETED'
  // - update generates dynamic SET clause from Partial<T> keys
}

MemoryEntityRepo adapter

class MemoryEntityRepo<T extends { id: string }> implements EntityRepository<T> {
  private store = new Map<string, T>();

  // findAll() applies scope filter in-memory
  // create/update/softDelete operate on the Map
  // Useful for unit tests — no D1 mock needed
}

Consequences

• Positive: Service tests switch from mockD1.ts (brittle SQL-string keyed) to MemoryEntityRepo (in-memory, deterministic, fast).
• Positive: Scope is enforced at the repository level — a developer can't accidentally return unscoped results.
• Positive: Boilerplate for CRUD operations drops to ~10 lines per entity (column list + constructor call).
• Negative: Every service that wants to use the repo needs its signature changed from (db: D1Database, ...) to (repo: EntityRepository<T>, ...) — migration per I6/I7.
• Negative: Dynamic SQL generation in D1EntityRepo.update() adds complexity (handling undefined vs null, column name safety).

Migration Plan

1. Phase 1 (I5): Define interface + both adapters in workers/src/storage/repository.ts. Wire studentService as first consumer. Student tests switch to MemoryEntityRepo.
2. Phase 2 (I6): Migrate remaining 4 services (user, class, role, classSlot) to EntityRepository<T>.
3. Phase 3 (I7): Extract inline D1 queries from route handlers into service modules using the repo.

Alternatives Considered

Alternative	Rejected because
Keep D1Database everywhere	MockD1 is brittle. No scope abstraction. Duplicate boilerplate.
Thick repo with mapper injection	Existing studentMapper.ts works fine — no need to reabstract what's already abstracted.
Result type (RepoResult<T>)	Over-engineered for this codebase. T | null + EntityNotFoundError covers all cases.
Scope as list() parameter	Easy to forget — constructor injection makes it mandatory.

References

• ADR-0013 — Pagination strategy (cursor-based)
• ADR-0016 — RBAC Scope System (ScopeFilter type)
• Issue #352 (I5: Design EntityRepository interface)
• Frontend StorageBackend interface at app/src/storage/types.ts
• EventStore interface at workers/src/events/eventStore.ts (existing abstraction pattern)