Adr Skill

ADR Skill

Philosophy

ADRs created with this skill are executable specifications for coding agents. A human approves the decision; an agent implements it. The ADR must contain everything the agent needs to write correct code without asking follow-up questions.

This means:

• Constraints must be explicit and measurable, not vibes
• Decisions must be specific enough to act on ("use PostgreSQL 16 with pgvector" not "use a database")
• Consequences must map to concrete follow-up tasks
• Non-goals must be stated to prevent scope creep
• The ADR must be self-contained — no tribal knowledge assumptions
• The ADR must include an implementation plan — which files to touch, which patterns to follow, which tests to write, and how to verify the decision was implemented correctly

When to Write an ADR

Write an ADR when a decision:

• Changes how the system is built or operated (new dependency, architecture pattern, infrastructure choice, API design)
• Is hard to reverse once code is written against it
• Affects other people or agents who will work in this codebase later
• Has real alternatives that were considered and rejected

Do NOT write an ADR for:

• Routine implementation choices within an established pattern
• Bug fixes or typo corrections
• Decisions already captured in an existing ADR (update it instead)
• Style preferences already covered by linters or formatters

When in doubt: if a future agent working in this codebase would benefit from knowing _why_ this choice was made, write the ADR.

Proactive ADR Triggers (For Agents)

If you are an agent coding in a repo and you encounter any of these situations, stop and propose an ADR before continuing:

• You are about to introduce a new dependency that doesn't already exist in the project
• You are about to create a new architectural pattern (new way of handling errors, new data access layer, new API convention) that other code will need to follow
• You are about to make a choice between two or more real alternatives and the tradeoffs are non-obvious
• You are about to change something that contradicts an existing accepted ADR
• You realize you're writing a long code comment explaining "why" — that reasoning belongs in an ADR

How to propose: Tell the human what decision you've hit, why it matters, and ask if they want to capture it as an ADR. If yes, run the full four-phase workflow. If no, note the decision in a code comment and move on.

Creating an ADR: Four-Phase Workflow

Every ADR goes through four phases. Do not skip phases.

Phase 0: Scan the Codebase

Before asking any questions, gather context from the repo:

1. Find existing ADRs. Check contributing/decisions/, docs/decisions/, adr/, docs/adr/, decisions/ for existing records. Read them. Note:

• Existing conventions (directory, naming, template style)
• Decisions that relate to or constrain the current one
• Any ADRs this new decision might supersede

2. Check the tech stack. Read package.json, go.mod, requirements.txt, Cargo.toml, or equivalent. Note relevant dependencies and versions.

3. Find related code patterns. If the decision involves a specific area (e.g., "how we handle auth"), scan for existing implementations. Identify the specific files, directories, and patterns that will be affected by the decision.

4. Check for ADR references in code. Look for ADR references in comments and docs (see "Code ↔ ADR Linking" below). This reveals which existing decisions govern which parts of the codebase.

5. Note what you found. Carry this context into Phase 1 — it will sharpen your questions and prevent the ADR from contradicting existing decisions.

Phase 1: Capture Intent (Socratic)

Interview the human to understand the decision space. Ask questions one at a time, building on previous answers. Do not dump a list of questions.

Core questions (ask in roughly this order, skip what's already clear from context or Phase 0):

1. What are you deciding? — Get a short, specific title. Push for a verb phrase ("Choose X", "Adopt Y", "Replace Z with W"). 2. Why now? — What broke, what's changing, or what will break if you do nothing? This is the trigger. 3. What constraints exist? — Tech stack, timeline, budget, team size, existing code, compliance. Be concrete. Reference what you found in Phase 0 ("I see you're already using X — does that constrain this?"). 4. What does success look like? — Measurable outcomes. Push past "it works" to specifics (latency, throughput, DX, maintenance burden). 5. What options have you considered? — At least two. For each: what's the core tradeoff? If they only have one option, help them articulate why alternatives were rejected. 6. What's your current lean? — Capture gut intuition early. Often reveals unstated priorities. 7. Who needs to know or approve? — Decision-makers, consulted experts, informed stakeholders. 8. What would an agent need to implement this? — Which files/directories are affected? What existing patterns should it follow? What should it avoid? What tests would prove it's working? This directly feeds the Implementation Plan.

Adaptive follow-ups: Based on answers, probe deeper where the decision is fuzzy. Common follow-ups:

• "What's the worst-case outcome if this decision is wrong?"
• "What would make you revisit this in 6 months?"
• "Is there anything you're explicitly choosing NOT to do?"
• "What prior art or existing patterns in the codebase does this relate to?"
• "I found [existing ADR/pattern] — does this new decision interact with it?"

When to stop: You have enough when you can fill every section of the ADR — including the Implementation Plan — without making things up. If you're guessing at any section, ask another question.

Intent Summary Gate: Before moving to Phase 2, present a structured summary of what you captured and ask the human to confirm or correct it:

Here's what I'm capturing for the ADR:

>

- Title: {title}

- Trigger: {why now}

- Constraints: {list}

- Options: {option 1} vs {option 2} [vs ...]

- Lean: {which option and why}

- Non-goals: {what's explicitly out of scope}

- Related ADRs/code: {what exists that this interacts with}

- Affected files/areas: {where in the codebase this lands}

- Verification: {how we'll know it's implemented correctly}

>

Does this capture your intent? Anything to add or correct?

Do NOT proceed to Phase 2 until the human confirms the summary.

Phase 2: Draft the ADR

1. Choose the ADR directory.

• If one exists (found in Phase 0), use it.
• If none exists, create contributing/decisions/ (if contributing/ exists), docs/decisions/ (MADR default), or adr/ (simpler repos).

2. Choose a filename strategy.

• If existing ADRs use date prefixes (YYYY-MM-DD-...), continue that.
• Otherwise use slug-only filenames (choose-database.md).

3. Choose a template.

• Use assets/templates/adr-simple.md for straightforward decisions (one clear winner, minimal tradeoffs).
• Use assets/templates/adr-madr.md when you need to document multiple options with structured pros/cons/drivers.
• See references/template-variants.md for guidance.

4. Fill every section from the confirmed intent summary. Do not leave placeholder text. Every section should contain real content or be removed (optional sections only).

5. Write the Implementation Plan. This is the most important section for agent-first ADRs. It tells the next agent exactly what to do. See the template for structure.

6. Write Verification criteria as checkboxes. These must be specific enough that an agent can programmatically or manually check each one.

7. Generate the file.

• Preferred: run scripts/new_adr.js (handles directory, naming, and optional index updates).
• If you can't run scripts, copy a template from assets/templates/ and fill it manually.

Phase 3: Review Against Checklist

After drafting, review the ADR against the agent-readiness checklist in references/review-checklist.md.

Present the review as a summary, not a raw checklist dump. Format:

ADR Review

>

✅ Passes: {list what's solid — e.g., "context is self-contained, implementation plan covers affected files, verification criteria are checkable"}

>

⚠️ Gaps found:

>

- {specific gap 1 — e.g., "Implementation Plan doesn't mention test files — which test suite should cover this?"}

- {specific gap 2}

>

Recommendation: {Ship it / Fix the gaps first / Needs more Phase 1 work}

Only surface failures and notable strengths — do not recite every passing checkbox.

If there are gaps, propose specific fixes. Do not just flag problems — offer solutions and ask the human to approve.

Do not finalize until the ADR passes the checklist or the human explicitly accepts the gaps.

Consulting ADRs (Read Workflow)

Agents should read existing ADRs before implementing changes in a codebase that has them. This is not part of the create-an-ADR workflow — it's a standalone operation any agent should do.

When to Consult ADRs

• Before starting work on a feature that touches architecture (auth, data layer, API design, infrastructure)
• When you encounter a pattern in the code and wonder "why is it done this way?"
• Before proposing a change that might contradict an existing decision
• When a human says "check the ADRs" or "there's a decision about this"
• When you find an ADR reference in a code comment

How to Consult ADRs

1. Find the ADR directory. Check contributing/decisions/, docs/decisions/, adr/, docs/adr/, decisions/. Also check for an index file (README.md or index.md).

2. Scan titles and statuses. Read the index or list filenames. Focus on accepted ADRs — these are active decisions.

3. Read relevant ADRs fully. Don't just read the title — read context, decision, consequences, non-goals, AND the Implementation Plan. The Implementation Plan tells you what patterns to follow and what files are governed by this decision.

4. Respect the decisions. If an accepted ADR says "use PostgreSQL," don't propose switching to MongoDB without creating a new ADR that supersedes it. If you find a conflict between what the code does and what the ADR says, flag it to the human.

5. Follow the Implementation Plan. When implementing code in an area governed by an ADR, follow the patterns specified in its Implementation Plan. If the plan says "all new queries go through the data-access layer in src/db/," do that.

6. Reference ADRs in your work. Add ADR references in code comments and PR descriptions (see "Code ↔ ADR Linking" below).

Code ↔ ADR Linking

ADRs should be bidirectionally linked to the code they govern.

ADR → Code (in the Implementation Plan)

The Implementation Plan section names specific files, directories, and patterns:

## Implementation Plan

- **Affected paths**: `src/db/`, `src/config/database.ts`, `tests/integration/`
- **Pattern**: all database queries go through `src/db/client.ts`

Code → ADR (in comments)

When implementing code guided by an ADR, add a comment referencing it:

// ADR: Using better-sqlite3 for test database
// See: docs/decisions/2025-06-15-use-sqlite-for-test-database.md
import Database from 'better-sqlite3';

Keep these lightweight — one comment at the entry point, not on every line. The goal is discoverability: when a future agent reads this code, they can find the reasoning.

Why This Matters

• An agent working in src/db/ can find which ADRs govern that area
• An agent reading an ADR can find the code that implements it
• When an ADR is superseded, the code references make it easy to find all code that needs updating

Other Operations

Update an Existing ADR

1. Identify the intent:

• Accept / reject: change status, add any final context.
• Deprecate: status → deprecated, explain replacement path.
• Supersede: create a new ADR, link both ways (old → new, new → old).
• Add learnings: append to ## More Information with a date stamp. Do not rewrite history.

2. Use scripts/set_adr_status.js for status changes (supports YAML front matter, bullet status, and section status).

Post-Acceptance Lifecycle

After an ADR is accepted:

1. Create implementation tasks. Each item in the Implementation Plan and each follow-up in Consequences should become a trackable task (issue, ticket, or TODO). 2. Reference the ADR in PRs. Link to the ADR in PR descriptions, e.g. "Implements contributing/decisions/2025-06-15-use-sqlite-for-test-database.md." 3. Add code references. Add ADR path comments at key implementation points. 4. Check verification criteria. Once implementation is complete, walk through the Verification checkboxes. Update the ADR with results in ## More Information. 5. Revisit when triggers fire. If the ADR specified revisit conditions ("if X happens, reconsider"), monitor for those conditions.

Index

If the repo has an ADR index/log file (often README.md or index.md in the ADR dir), keep it updated.

Preferred: let scripts/new_adr.js --update-index do it. Otherwise:

• Add a bullet entry for the new ADR.
• Keep ordering consistent (numeric if numbered; date or alpha if slugs).

Bootstrap

When introducing ADRs to a repo that has none:

node /path/to/adr-skill/scripts/bootstrap_adr.js

This creates the directory, an index file, and a filled-out first ADR ("Adopt architecture decision records") with real content explaining why the team is using ADRs. Use --json for machine-readable output. Use --dir to override the directory name.

Categories (Large Projects)

For repos with many ADRs, organize by subdirectory:

docs/decisions/
  backend/
    2025-06-15-use-postgres.md
  frontend/
    2025-06-20-use-react.md
  infrastructure/
    2025-07-01-use-terraform.md

Date prefixes are local to each category. Choose a categorization scheme early (by layer, by domain, by team) and document it in the index.

Resources

scripts/

• scripts/new_adr.js — create a new ADR file from a template, using repo conventions.
• scripts/set_adr_status.js — update an ADR status in-place (YAML front matter or inline). Use --json for machine output.
• scripts/bootstrap_adr.js — create ADR dir, README.md, and initial "Adopt ADRs" decision.

references/

• references/review-checklist.md — agent-readiness checklist for Phase 3 review.
• references/adr-conventions.md — directory, filename, status, and lifecycle conventions.
• references/template-variants.md — when to use simple vs MADR-style templates.
• references/examples.md — filled-out short and long ADR examples with implementation plans.

assets/

• assets/templates/adr-simple.md — lean template for straightforward decisions.
• assets/templates/adr-madr.md — MADR 4.0 template for decisions with multiple options and structured tradeoffs.
• assets/templates/adr-readme.md — default ADR index scaffold used by scripts/bootstrap_adr.js.

Script Usage

From the target repo root:

# Simple ADR
node /path/to/adr-skill/scripts/new_adr.js --title "Choose database" --status proposed

# MADR-style with options
node /path/to/adr-skill/scripts/new_adr.js --title "Choose database" --template madr --status proposed

# With index update
node /path/to/adr-skill/scripts/new_adr.js --title "Choose database" --status proposed --update-index

# Bootstrap a new repo
node /path/to/adr-skill/scripts/bootstrap_adr.js --dir docs/decisions

Notes:

• Scripts auto-detect ADR directory and filename strategy.
• Use --dir and --strategy to override.
• Use --json to emit machine-readable output.

{short title, representative of solved problem and found solution}

Context and Problem Statement

{Describe the context and problem statement. Frame it as a question when possible. Link to relevant issues, tickets, or prior ADRs. Include enough background that someone (or an agent) encountering this for the first time can understand why this decision exists without asking follow-up questions.}

<!-- Optional — remove if not needed -->

Decision Drivers

• {decision driver 1, e.g., a constraint, requirement, or force}
• {decision driver 2}
• …

Considered Options

• {title of option 1}
• {title of option 2}
• {title of option 3}
• …

Decision Outcome

Chosen option: "{title of option 1}", because {justification — reference drivers and tradeoffs}.

Consequences

• Good, because {positive consequence}
• Bad, because {negative consequence}
• Neutral, because {consequence that is neither positive nor negative}
• …

Implementation Plan

{This section tells an agent exactly what to do to implement this decision. Be specific — an agent should be able to start coding from this without asking follow-up questions.}

• Affected paths: {list files and directories that need to change, e.g., src/db/, src/config/database.ts, tests/integration/}
• Dependencies: {packages to add/remove/update, e.g., "add better-sqlite3@11.x, remove pg-mem"}
• Patterns to follow: {reference existing code patterns, e.g., "follow the repository pattern in src/db/repositories/"}
• Patterns to avoid: {what NOT to do, e.g., "do not use raw SQL outside the data-access layer"}
• Configuration: {env vars, config files, feature flags to add/change}
• Migration steps: {if replacing something, what's the migration path? Can it be done incrementally?}

Verification

{Checkboxes an agent can validate after implementation. Each must be specific and testable.}

• [ ] {verification criterion 1, e.g., "npm test passes with SQLite as the test database"}
• [ ] {verification criterion 2, e.g., "no direct pg imports outside src/db/client.ts"}
• [ ] {verification criterion 3, e.g., "CI pipeline completes in under 60 seconds"}
• …

<!-- Optional — remove if not needed -->

Pros and Cons of the Options

{title of option 1}

{Brief description or link to more information}

• Good, because {argument a}
• Good, because {argument b}
• Neutral, because {argument c}
• Bad, because {argument d}
• …

{title of option 2}

{Brief description or link to more information}

• Good, because {argument a}
• Good, because {argument b}
• Neutral, because {argument c}
• Bad, because {argument d}
• …

<!-- Optional — remove if not needed -->

More Information

{Additional context, links to related ADRs, team agreements, implementation notes, or conditions that would trigger revisiting this decision. This section is a catch-all for anything that helps future readers (human or agent) understand the full picture.}

Architecture Decision Records (ADR)

An Architecture Decision Record (ADR) captures an important architecture decision along with its context and consequences.

Conventions

• Directory: {ADR_DIR}
• Naming:
• Use date-prefixed files: YYYY-MM-DD-choose-database.md
• If the repo already uses slug-only names, keep that: choose-database.md
• Status values: proposed, accepted, rejected, deprecated, superseded

Workflow

• Create a new ADR as proposed.
• Discuss and iterate.
• When the team commits: mark it accepted (or rejected).
• If replaced later: create a new ADR and mark the old one superseded with a link.

ADRs

{short title, representative of solved problem and found solution}

Context and Problem Statement

{Why does this decision need to happen now? What constraints exist? Include enough background that someone (or an agent) reading this for the first time can understand without follow-up questions.}

Decision

{What are we choosing to do? Be specific — include scope and non-goals.}

Consequences

• Good, because {positive consequence}
• Bad, because {negative consequence}
• …

Implementation Plan

• Affected paths: {files and directories that change}
• Dependencies: {packages to add/remove/update}
• Patterns to follow: {existing code patterns to match}
• Patterns to avoid: {what NOT to do}

Verification

• [ ] {how to confirm the decision was implemented correctly}
• [ ] {another verification criterion}

<!-- Optional — remove if not needed -->

Alternatives Considered

• {Alternative 1}: {Why it was rejected, in one or two sentences.}
• {Alternative 2}: {Why it was rejected.}

<!-- Optional — remove if not needed -->

More Information

{Related ADRs, PRs, issues, docs, or conditions that would trigger revisiting this decision.}

ADR Conventions (Reference)

Directory

If the repo already has an ADR directory, keep it.

If the repo has no ADR directory, choose based on project size:

• `docs/decisions/` — MADR default, recommended for projects with existing docs/ structure.
• `adr/` — simpler alternative for smaller repos.

Detection order (used by scripts): contributing/decisions/, docs/decisions/, adr/, docs/adr/, docs/adrs/, decisions/.

Filename Conventions

Pattern: YYYY-MM-DD-title-with-dashes.md

• YYYY-MM-DD is the ADR creation date (matches the date frontmatter field).
• Title uses lowercase, dashes, present-tense imperative verb phrase.
• Examples: 2025-06-15-choose-database.md, 2025-07-01-adopt-adrs.md
• Multiple ADRs on the same date are fine — the slug suffix disambiguates them.

If a repo already uses slug-only filenames (no date prefix), follow that convention.

Minimal Sections

At minimum, every ADR must clearly include:

1. Context: why the decision exists now, what constraints/drivers apply. 2. Decision: what is chosen. 3. Consequences: what becomes easier/harder, risks, costs, follow-ups.

For agent-first ADRs, also ensure:

• Constraints are explicit and measurable
• Non-goals are stated
• Follow-up tasks are identified

Status Values

Track status in YAML front matter:

---
status: proposed
date: 2025-06-15
decision-makers: Alice, Bob
---

Common statuses:

YAML Front Matter Fields

The consulted and informed fields follow the RACI model and are useful for audit trails in larger teams.

Mutability

• Prefer appending new information with a date stamp over rewriting existing content.
• If a decision is replaced, create a new ADR and explicitly supersede the old one.
• Status changes and after-action notes are fine to edit in-place.

Categories (Large Projects)

For repos accumulating many ADRs, use subdirectories:

contributing/decisions/   # or docs/decisions/
  backend/
    2025-06-15-use-postgres.md
  frontend/
    2025-06-20-use-react.md
  infrastructure/
    2025-07-01-use-terraform.md

Date prefixes are local to each category. Choose a categorization scheme early (by architectural layer, by domain, by team) and document it in the index.

Alternative: use tags or a flat structure with a searchable index. Subdirectories are simpler and work with all tools.

ADR Examples

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.

Short Version (Simple Template)

---
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 approach

Long Version (MADR Template)

The 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: [Use PostgreSQL for production](2025-05-01-use-postgresql-for-production.md) — this decision must not compromise production database choice.

## Decision Drivers

- CI speed: current 3+ minute DB setup is the bottleneck in our 5-minute pipeline
- Test isolation: zero shared state between parallel test runs
- Production parity: must catch SQL dialect issues before they hit production
- Developer experience: should work offline, no external dependencies for `npm test`
- Maintenance cost: solution should not require a dedicated owner

## Considered Options

- SQLite via better-sqlite3
- Docker PostgreSQL per CI run
- In-memory PostgreSQL (pg-mem)

## Decision Outcome

Chosen option: "SQLite via better-sqlite3", because it eliminates the CI bottleneck (2s vs 3+ min), provides full isolation, works offline, and has minimal maintenance cost. The dialect-drift risk is mitigated by a weekly CI job against real PostgreSQL.

### Consequences

- Good, because CI database setup drops from 3+ minutes to ~2 seconds
- Good, because each test run is fully isolated (file-based DB, no shared state)
- Good, because developers can run the full test suite offline with zero infrastructure
- Bad, because we must maintain a data-access abstraction layer to paper over SQL dialect differences
- Bad, because PostgreSQL-specific features (JSONB operators, array columns, advisory locks) cannot be tested locally
- Neutral, because the abstraction layer adds ~200 lines of code but also makes future DB migrations easier

## Implementation Plan

- **Affected paths**:
  - `src/db/client.ts` — new: unified database interface (DatabaseClient type + factory function)
  - `src/db/sqlite-client.ts` — new: SQLite implementation of DatabaseClient
  - `src/db/pg-client.ts` — refactor: extract current inline pg usage into DatabaseClient implementation
  - `src/db/repositories/*.ts` — update: use DatabaseClient instead of direct pg calls
  - `tests/setup.ts` — update: initialize SQLite by default, read `DB_ENGINE` env var
  - `tests/fixtures/seed.sql` — update: ensure all seed SQL is dialect-neutral
  - `.env.test` — new: `DB_ENGINE=sqlite`
  - `.github/workflows/ci.yml` — update: remove PostgreSQL service container from main CI
  - `package.json` — add devDependencies
- **Dependencies**: add `better-sqlite3@11.x`, `@types/better-sqlite3@7.x` as devDependencies
- **Patterns to follow**:
  - Repository pattern in `src/db/repositories/` — all database access goes through repository methods
  - Use parameterized queries exclusively (no string interpolation)
  - Reference implementation: `src/db/repositories/users.ts` for the expected style
- **Patterns to avoid**:
  - Do NOT import `better-sqlite3` or `pg` directly outside `src/db/`
  - Do NOT use PostgreSQL-specific SQL in shared queries: no `JSONB` operators (`->`, `->>`), no `ANY(ARRAY[...])`, no `ON CONFLICT ... DO UPDATE`
  - Do NOT use SQLite-specific SQL either — keep queries portable
- **Configuration**: `DB_ENGINE` env var (`sqlite` | `postgres`), defaults to `sqlite` in test, `postgres` in production
- **Migration steps**:
  1. Create `DatabaseClient` interface and SQLite implementation
  2. Refactor existing pg code into pg implementation
  3. Update repositories one at a time (each can be a separate PR)
  4. Update test setup last, once all repositories use the abstraction
  5. Remove PostgreSQL service container from CI workflow

### Verification

- [ ] `DB_ENGINE=sqlite npm test` passes (all integration tests)
- [ ] `DB_ENGINE=postgres npm test` passes against a real PostgreSQL 16 instance
- [ ] `grep -r "from 'better-sqlite3'" src/ --include='*.ts' | grep -v 'src/db/'` returns no results
- [ ] `grep -r "from 'pg'" src/ --include='*.ts' | grep -v 'src/db/'` returns no results
- [ ] CI pipeline completes in under 90 seconds (measured on main branch)
- [ ] `src/db/client.ts` exports `DatabaseClient` interface and `createClient()` factory
- [ ] `.env.test` sets `DB_ENGINE=sqlite`
- [ ] Weekly PostgreSQL compatibility CI job exists in `.github/workflows/`

## Pros and Cons of the Options

### SQLite via better-sqlite3

[better-sqlite3](https://github.com/WiseLibs/better-sqlite3) — synchronous SQLite bindings for Node.js.

- Good, because zero infrastructure — just an npm dependency
- Good, because synchronous API makes test setup/teardown trivial
- Good, because file-based DBs enable parallelism (one file per test worker)
- Neutral, because requires a thin abstraction layer (~200 LOC)
- Bad, because SQL dialect differences (no JSONB, different date handling, no arrays)
- Bad, because does not exercise PostgreSQL-specific query plans or extensions

### Docker PostgreSQL per CI run

Spin up a fresh PostgreSQL container for each CI job.

- Good, because perfect production parity — same engine, same version
- Good, because no abstraction layer needed
- Bad, because adds 90+ seconds to every CI run (image pull + startup + healthcheck)
- Bad, because requires Docker-in-Docker on CI, adding complexity and security surface
- Bad, because developers need Docker running locally for `npm test`

### In-memory PostgreSQL (pg-mem)

[pg-mem](https://github.com/oguimbal/pg-mem) — in-memory PostgreSQL emulator for testing.

- Good, because better SQL compatibility than SQLite
- Good, because no infrastructure needed
- Bad, because incomplete support for our schema features (triggers, CTEs, lateral joins)
- Bad, because last published release is 8+ months old — maintenance risk
- Bad, because debugging failures requires understanding pg-mem's emulation quirks

## More Information

- Follow-up task: create data-access abstraction layer — #347
- Follow-up task: set up weekly PostgreSQL CI job — #348
- Related: [Use PostgreSQL for production](2025-05-01-use-postgresql-for-production.md)
- Revisit trigger: if dialect-drift bugs exceed 2 per quarter, reconsider Docker PostgreSQL approach
- Code references: after implementation, key files will have `// ADR: 2025-06-15-use-sqlite-for-test-database` comments at entry points

ADR Review Checklist

Use this checklist in Phase 3 to validate an ADR before finalizing. The goal: could a coding agent read this ADR and start implementing the decision immediately, without asking any clarifying questions?

Agent-Readiness Checks

Context & Problem

• [ ] A reader with no prior context can understand why this decision exists
• [ ] The trigger is clear (what changed, broke, or is about to break)
• [ ] No tribal knowledge is assumed — acronyms are defined, systems are named explicitly
• [ ] Links to relevant issues, PRs, or prior ADRs are included

Decision

• [ ] The decision is specific enough to act on (not "use a better approach" but "use X for Y")
• [ ] Scope is bounded — what's in AND what's out (non-goals)
• [ ] Constraints are explicit and measurable where possible (e.g., "< 200ms p95" not "fast enough")

Consequences

• [ ] Each consequence is concrete and actionable, not aspirational
• [ ] Follow-up tasks are identified (migrations, config changes, documentation, new tests)
• [ ] Risks are stated with mitigation strategies or acceptance rationale
• [ ] No consequence is a disguised restatement of the decision

Implementation Plan

• [ ] Affected files/directories are named explicitly (not "the database code" but "src/db/client.ts")
• [ ] Dependencies to add/remove are specified with version constraints
• [ ] Patterns to follow reference existing code (not abstract descriptions)
• [ ] Patterns to avoid are stated (what NOT to do)
• [ ] Configuration changes are listed (env vars, config files, feature flags)
• [ ] If replacing something, migration steps are described

Verification

• [ ] Criteria are checkboxes, not prose
• [ ] Each criterion is testable — an agent could write a test or run a command to check it
• [ ] Criteria cover both "it works" (functional) and "it's done right" (structural/architectural)
• [ ] No criterion is vague ("it performs well" → "p95 latency < 200ms under 100 concurrent requests")

Options (MADR template)

• [ ] At least two options were genuinely considered (not just "do the thing" vs "do nothing")
• [ ] Each option has real pros AND cons (not a straw-man comparison)
• [ ] The justification for the chosen option references specific drivers or tradeoffs
• [ ] Rejected options explain WHY they were rejected, not just what they are

Meta

• [ ] Status is set correctly (usually proposed for new ADRs)
• [ ] Date is set
• [ ] Decision-makers are listed
• [ ] Title is a verb phrase describing the decision (not the problem)
• [ ] Filename follows repo conventions

Quick Scoring

Count the checked items. This isn't a gate — it's a conversation tool.

• All checked: Ship it.
• 1–3 unchecked: Discuss the gaps with the human. Most can be fixed in a minute.
• 4+ unchecked: The ADR needs more work. Go back to Phase 1 for the fuzzy areas.

Common Failure Modes

Template Variants

This skill ships two templates in assets/templates/.

Simple

File: assets/templates/adr-simple.md

Use this when:

• The decision is straightforward (one clear winner, minimal tradeoffs)
• You mainly need "why, what, consequences, how to implement"
• Alternatives are few and can be dismissed in a sentence each
• Speed matters more than exhaustive comparison

Sections: Context and Problem Statement → Decision → Consequences → Implementation Plan → Verification → Alternatives Considered (optional) → More Information (optional).

MADR (Options-Heavy)

File: assets/templates/adr-madr.md

Use this when:

• You have multiple real options and want to document structured tradeoffs
• You need to capture decision drivers explicitly (what criteria mattered)
• The decision is likely to be revisited and the comparison needs to survive
• Stakeholders need to see the reasoning process, not just the outcome

Sections: Context and Problem Statement → Decision Drivers (optional) → Considered Options → Decision Outcome → Consequences → Implementation Plan → Verification → Pros and Cons of the Options (optional) → More Information (optional).

This template aligns with MADR 4.0 and extends it with agent-first sections.

Both Templates Share

• YAML front matter for metadata (status, date, decision-makers, consulted, informed)
• Implementation Plan — affected paths, dependencies, patterns to follow/avoid, configuration, migration steps. This is what makes the ADR an executable spec for agents.
• Verification as checkboxes — testable criteria an agent can validate after implementation
• Agent-first framing: placeholder text prompts you to be specific, measurable, and self-contained
• "More Information" section for cross-links, follow-ups, and revisit triggers
• "Neutral, because..." as a third argument category alongside Good and Bad

Choosing Between Them

When in doubt, start with Simple. You can always expand to MADR if the discussion reveals more complexity.