Setting the file. One moment.
Subchapter 4.2
references/examples.mdMarkdown10 KBView on GitHub
These are filled-out examples showing the same decision at two levels of detail. Use these as reference when drafting ADRs — never leave placeholder text in a real ADR.
---
status: accepted
date: 2025-06-15
decision-makers: Sarah Chen, Joel
---
# Use SQLite for local development database
## Context and Problem Statement
Our integration tests require a database but currently hit a shared PostgreSQL instance, causing flaky tests from concurrent writes and slow CI (3+ minute setup per run). We need a fast, isolated database for local dev and CI that doesn't require infrastructure provisioning.
## Decision
Use SQLite (via better-sqlite3) for local development and CI test runs. Production remains on PostgreSQL. We'll use a thin data-access layer that abstracts the database engine, tested against both SQLite and PostgreSQL in CI.
Non-goals: we are NOT migrating production to SQLite or building a full ORM abstraction.
## Consequences
- Good, because CI setup drops from 3+ minutes to ~2 seconds (no DB provisioning)
- Good, because tests are fully isolated — no shared state between runs
- Good, because developers can run the full test suite offline
- Bad, because we must maintain compatibility between SQLite and PostgreSQL SQL dialects
- Bad, because some PostgreSQL-specific features (JSONB operators, array columns) can't be tested locally
## Implementation Plan
- **Affected paths**: `src/db/client.ts` (new abstraction layer), `src/db/sqlite-client.ts` (new), `src/db/pg-client.ts` (refactored from current inline usage), `tests/setup.ts`, `package.json`
- **Dependencies**: add `better-sqlite3@11.x` and `@types/better-sqlite3@7.x` as devDependencies; no production dependency changes
- **Patterns to follow**: existing repository pattern in `src/db/repositories/` — all queries go through repository methods, never raw SQL in business logic
- **Patterns to avoid**: do not import `better-sqlite3` or `pg` directly outside `src/db/`; do not use PostgreSQL-specific SQL (JSONB operators, `ANY()`, array literals) in shared queries
### Verification
- [ ] `npm test` passes with `DB_ENGINE=sqlite` (default for test env)
- [ ] `npm test` passes with `DB_ENGINE=postgres` against a real PostgreSQL instance
- [ ] No imports of `better-sqlite3` or `pg` outside `src/db/`
- [ ] CI pipeline total time under 90 seconds (was 5+ minutes)
- [ ] `src/db/client.ts` exports a unified interface used by all repositories
## Alternatives Considered
- Docker PostgreSQL per CI run: Reliable parity, but adds 90s+ startup and requires Docker-in-Docker on CI.
- In-memory PostgreSQL (pg-mem): Good API compatibility, but incomplete support for our schema (triggers, CTEs) and unmaintained.
## More Information
- Follow-up: create weekly CI job running full suite against real PostgreSQL (#348)
- Revisit trigger: if dialect-drift bugs exceed 2 per quarter, reconsider Docker PostgreSQL approachThe same decision with full options analysis:
---
status: accepted
date: 2025-06-15
decision-makers: Sarah Chen, Joel
consulted: Alex (DBA), Platform team
informed: Frontend team, QA
---
# Use SQLite for local development database
## Context and Problem Statement
Our integration tests require a database but currently hit a shared PostgreSQL instance. This causes two problems:
1. Flaky tests from concurrent writes (multiple developers and CI jobs sharing one DB)
2. Slow CI — each run spends 3+ minutes provisioning and seeding the database
How can we provide a fast, isolated database for local development and CI without sacrificing confidence in production compatibility?
Related: [