# ADR 033: DuckDB for the AI Review Scorecard

- HTML version: https://robbiepalmer.me/projects/agentic-code-review/adrs/033-duckdb-ai-review-scorecard
- Project: Agentic Code Review (https://robbiepalmer.me/projects/agentic-code-review.md)
- Status: Accepted
- Date: 2026-08-15

# Context

[ADR 032](/projects/agentic-code-review/adrs/032-stateful-ai-code-review)
established an append-only R2 data lake for review runs, finding evidence,
versioned outcomes, and controlled replay records. That evidence is useful only
if the scorecard resolves its lifecycle consistently and makes the metric
definitions reproducible.

The analytical workload is small, private, and asynchronous. It rebuilds facts
from immutable JSON objects, performs relational validation and aggregation,
and publishes portable columnar outputs. It does not need low-latency queries,
concurrent writers, a long-running database service, or a general business
intelligence platform. Introducing one of those systems would add credentials,
operations, migrations, and cost before the review corpus justifies them.

The transformation must also preserve the evaluation contract:

* schema versions and joins fail visibly instead of silently losing records;
* immutable outcome revisions remain auditable while the highest revision is
  selected as the current outcome;
* a `published` terminal record takes precedence over a later `failed` record
  for the same workflow, while other conflicting terminal combinations fail;
* metric denominators match ADR 032 rather than changing with each report;
* model comparisons retain prompt, risk, change-size, repository-area, and time
  dimensions; and
* a fixed input prefix produces identical outputs when rebuilt unchanged.

# Decision

Use **DuckDB as an embedded batch transformation engine** over the AI review R2
export.

A single mise task reads schema-version-2 review-run, finding-evidence,
finding-outcome, and replay-manifest JSON objects from a fixed input prefix. It
validates the input contract, resolves versioned outcomes, calculates the
scorecard facts, and materialises these versioned Parquet marts:

* `finding_latest` contains one row per published finding joined to its highest
  outcome revision, while the build retains the complete outcome history;
* `review_run_fact` measures finding quality, spend, coverage, and response at
  one review-run grain;
* `model_run_fact` records model calls, latency, tokens, cache use, cost, and
  source-attributed accepted-finding efficiency; and
* `pull_request_fact` aggregates the review lifecycle at Pull Request grain.
  Run-scoped dimensions that can change during that lifecycle are retained as
  distinct lists rather than collapsed to an arbitrary representative value.

Each output version also contains a machine-readable manifest with row counts
and SHA-256 checksums. Determinism is part of the interface: input paths and
output rows are ordered, DuckDB uses one execution thread, Parquet settings are
fixed, and generated wall-clock timestamps are excluded.

The scorecard applies the definitions from ADR 032:

| Metric                 | Definition                                                                                 |
| ---------------------- | ------------------------------------------------------------------------------------------ |
| Adjudicated acceptance | `(confirmed-fixed + acknowledged) / (confirmed-fixed + acknowledged + rejected)`           |
| Fix-through            | `confirmed-fixed / published findings`                                                     |
| Noise                  | `(rejected + identified duplicates) / published findings`                                  |
| Cost efficiency        | Total attributable cost per accepted finding                                               |
| Token efficiency       | Accepted findings per million uncached input tokens, with cache-hit rate alongside it      |
| Coverage               | Reviewed hunks divided by total eligible hunks                                             |
| No response            | Findings with `no-observable-response`, or no final outcome, divided by published findings |
| Latency                | Recorded model-call and finding-outcome durations at their available grains                |

Censored outcomes do not enter the adjudicated-acceptance denominator. A model
receives accepted-finding credit only when it appears in that finding's source
provenance. Metrics whose source events are not yet recorded remain absent
rather than being estimated from unrelated timestamps.

DuckDB is not an operational store. Durable Objects remain authoritative for
live Pull Request state, and R2 remains authoritative for immutable analytical
records. Parquet marts are derived artifacts and can always be rebuilt.

# Consequences

## Positive

* The analytical layer is local, reproducible, inexpensive, and requires no
  continuously running service.
* SQL makes grain, joins, revision selection, and denominators inspectable.
* Parquet keeps outputs portable for later notebooks, reports, or migration to
  another query engine.
* Strict validation turns producer/schema drift into an explicit build failure.
* Fixture-based tests can exercise lifecycle revisions and exact metric values
  without using production review data.

## Negative

* The scorecard is refreshed in batches rather than queried in real time.
* A full rebuild will eventually become inefficient if the corpus grows far
  beyond the current single-repository scale.
* JSON schema validation is implemented in the transformation and must evolve
  deliberately with future record versions.
* DuckDB and Parquet versions must be pinned because engine or writer upgrades
  may change output bytes even when rows are logically identical.

## Risks and Mitigations

* **Metric drift:** fixture expected values encode ADR definitions, and changes
  require an ADR or an explicit amendment.
* **Silent data loss:** unknown schema versions, record types, duplicate outcome
  revisions, and evidence or outcomes referencing unpublished findings fail the
  build.
* **Misleading comparisons:** marts retain stratification dimensions; reports
  must not compare models across materially different task mixes without them.
* **Premature platform growth:** dashboards, streaming ingestion, orchestration,
  and a shared warehouse remain out of scope.

# Alternatives

## Query R2 JSON Directly for Each Report

This avoids materialised marts but repeats lifecycle joins and metric logic in
every consumer. Results would be harder to reproduce, validate, and compare
over time.

## PostgreSQL or a Managed Data Warehouse

Either could support larger data volumes and concurrent interactive queries.
The current workload does not need those capabilities, so their operational
surface and ongoing cost are unjustified. Parquet preserves a migration path if
that changes.

## Application-Code Aggregation

TypeScript could traverse every object and emit reports, but relational joins,
windowed revision resolution, and grouped scorecard facts are clearer and more
testable in SQL. Application code remains appropriate for producing the source
events, not for recreating a small analytical engine.

## SQLite

SQLite is already appropriate for the Durable Object's transactional state.
For this offline workload, DuckDB has the better fit because the primary work
is scanning nested analytical records and writing columnar Parquet marts.

# Revisit When

Reconsider this decision when full rebuild time exceeds the practical batch
window, multiple users need concurrent low-latency queries, cross-product data
must be governed centrally, or incremental ingestion becomes materially simpler
than rebuilding immutable inputs. Until then, keep the scorecard a batch
transformation rather than a warehouse platform.

---

Markdown index of this site: https://robbiepalmer.me/llms.txt