Storing Test History in SQLite for Flake Analysis

Root cause #

Flakiness is a property of a distribution over runs, so any useful statement about it requires aggregation across many executions. A per-run artifact contains one sample. Answering “what is this test’s rate over thirty days” from artifacts means downloading, parsing and joining hundreds of files, which is slow enough that people do it once, paste the output into a document, and then reason from a stale snapshot for months.

The second problem is that the interesting questions are correlations, not counts. Did the rate change after a dependency upgrade? Do failures concentrate on one runner image, one time of day, one shard? Each of these is a join between test results and run metadata, and joins are precisely what a directory of JSON does not do.

Teams often skip a store because they assume it means operating a database. It does not. A test suite producing a thousand results per run, twenty runs a day, generates around seven million rows a year — comfortably within SQLite’s range on a single file that can live in an artifact store or a small volume. The operational cost is close to zero, and the analytical difference is the difference between having flakiness data and being able to use it.

Step-by-step fix #

1. Define a schema that supports the questions #

Two tables cover almost everything: one row per run with its metadata, one row per test result. The metadata columns are what make correlation possible later.

-- schema.sql
-- Trade-off: recording run metadata costs a few columns and is what turns the
-- store from a counter into something that can explain a change.
CREATE TABLE IF NOT EXISTS runs (
  id            TEXT PRIMARY KEY,       -- CI run id
  started_at    TEXT NOT NULL,          -- ISO 8601
  branch        TEXT NOT NULL,
  commit_sha    TEXT NOT NULL,
  runner_image  TEXT,                   -- digest, for drift correlation
  workers       INTEGER,
  cpu_count     INTEGER
);

CREATE TABLE IF NOT EXISTS results (
  run_id      TEXT NOT NULL REFERENCES runs(id),
  test_id     TEXT NOT NULL,            -- stable: file::title
  file        TEXT NOT NULL,
  status      TEXT NOT NULL,            -- passed | failed | flaky | skipped
  attempts    INTEGER NOT NULL DEFAULT 1,
  duration_ms INTEGER,
  error_class TEXT,                     -- wait | infra | assertion | unknown
  shard       INTEGER
);

CREATE INDEX IF NOT EXISTS idx_results_test ON results(test_id);
CREATE INDEX IF NOT EXISTS idx_runs_started ON runs(started_at);

The test_id must be stable across runs — file path plus title works, and it is worth normalising titles that contain dynamic data, or every parameterised test becomes a thousand distinct identifiers.

2. Ingest from the runner’s structured output #

Ingestion is a short script run after the suite, reading the same JSON report the retry accounting uses.

// scripts/ingest.js
// Trade-off: ingesting synchronously at the end of a run adds a second or two
// per pipeline; batching it later loses data whenever a run is cancelled.
import Database from 'better-sqlite3';

const db = new Database('history.db');
db.exec(readFileSync('schema.sql', 'utf8'));

const insertRun = db.prepare(`INSERT OR REPLACE INTO runs
  VALUES (@id, @started_at, @branch, @commit_sha, @runner_image, @workers, @cpu_count)`);
const insertResult = db.prepare(`INSERT INTO results
  VALUES (@run_id, @test_id, @file, @status, @attempts, @duration_ms, @error_class, @shard)`);

const tx = db.transaction((run, results) => {
  insertRun.run(run);
  for (const r of results) insertResult.run(r);
});

tx(runMetadata(), flattenResults(report));    // one transaction, all-or-nothing

3. Ask the questions in SQL #

Once the data is in, each analysis is a query rather than a script — which is what makes people actually run them.

-- Flake rate per test over 30 days, worst first
-- Trade-off: counting 'flaky' separately from 'failed' distinguishes rescued
-- failures from hard failures; merging them hides which is which.
SELECT r.test_id,
       COUNT(*)                                             AS executions,
       SUM(r.status = 'flaky')                              AS rescued,
       SUM(r.status = 'failed')                             AS failed,
       ROUND(100.0 * SUM(r.status IN ('flaky','failed')) / COUNT(*), 2) AS rate_pct
FROM results r
JOIN runs ON runs.id = r.run_id
WHERE runs.started_at > datetime('now', '-30 days')
GROUP BY r.test_id
HAVING rate_pct > 1
ORDER BY rate_pct DESC
LIMIT 20;

-- Did a runner image change coincide with a rate change?
SELECT runs.runner_image,
       ROUND(100.0 * SUM(results.status IN ('flaky','failed')) / COUNT(*), 2) AS rate_pct,
       COUNT(DISTINCT runs.id) AS runs
FROM results JOIN runs ON runs.id = results.run_id
WHERE runs.started_at > datetime('now', '-60 days')
GROUP BY runs.runner_image
ORDER BY runs DESC;

4. Keep the database where the pipeline can reach it #

The simplest arrangement that works: store the file in an artifact or object store, download it at the start of the ingest step, append, and upload. Concurrency is the one real constraint — parallel pipelines appending to the same file will conflict.

# Trade-off: append-then-upload is trivially simple and serialises ingestion;
# for high pipeline concurrency, write per-run files and merge on a schedule.
aws s3 cp s3://ci-artifacts/flakiness/history.db . || true
node scripts/ingest.js
aws s3 cp history.db s3://ci-artifacts/flakiness/history.db

5. Prune with a retention policy #

Ninety days of detail answers every practical question; older data belongs in a rolled-up summary table rather than as individual rows.

-- Trade-off: aggregating loses per-run detail and keeps the file small enough
-- to download in a pipeline step, which matters more for daily usability.
INSERT INTO monthly_summary
SELECT strftime('%Y-%m', runs.started_at) AS month, results.test_id,
       COUNT(*), SUM(results.status IN ('flaky','failed'))
FROM results JOIN runs ON runs.id = results.run_id
WHERE runs.started_at < datetime('now', '-90 days')
GROUP BY month, results.test_id;

DELETE FROM results WHERE run_id IN (
  SELECT id FROM runs WHERE started_at < datetime('now', '-90 days'));

6. Publish the standing queries #

A store nobody queries is an artifact directory with extra steps. Run the three or four standing queries on a schedule and publish the output where the team already looks, using the reporting mechanisms in Reliability Dashboards for QA Teams.

Pitfalls #

• Unstable test identifiers. Titles containing timestamps or ids fragment the history. Mitigation: normalise dynamic segments before storing.
• Not recording run metadata. Correlation questions become unanswerable. Mitigation: capture image, workers, branch and commit per run.
• Concurrent writes to one file. Parallel pipelines corrupt or block each other. Mitigation: serialise ingestion, or write per-run files and merge.
• Merging rescued and hard failures. The retry signal is lost. Mitigation: keep flaky distinct from failed.
• Unbounded growth. The file becomes too large to move around in a pipeline step. Mitigation: prune to a rolled-up summary after ninety days.
• Ingesting only failures. Without the denominator, no rate can be computed. Mitigation: store every result, including passes.
• A store with no standing queries. The data is retained and never used. Mitigation: publish a scheduled report.

Reliability targets #

Frequently Asked Questions #

Q: Why SQLite rather than a hosted database? A: Because the workload is tiny and the operational cost of a service is not. A few million rows, one writer, read-mostly queries — SQLite handles that comfortably in a file you can copy, version and inspect locally. Move to a hosted database when you genuinely need concurrent writers or a shared dashboard backend, not before.

Q: How do I handle parameterised tests that generate many identifiers? A: Normalise the title before storing: replace numbers, ids and dates in the test name with placeholders so the family aggregates into one identifier, and keep the full title in a separate column for display. Without this, a data-driven suite produces thousands of one-execution identifiers and every rate is meaningless.

Q: Should pull-request runs be ingested as well as trunk runs? A: Store both, and filter by branch in the queries. Pull-request data is noisy — it includes work in progress and half-finished tests — but it is also the earliest signal that a change introduces instability. Keeping the branch column means you can ask either question later.

Q: What is the first query worth running? A: Rate per test over thirty days, sorted descending, limited to twenty. It almost always shows that a handful of tests account for most of the instability, which turns an amorphous quality problem into a short, ranked worklist — the input the triage process in Flaky Test Triage & Ownership needs.

Related guides #

• Historical Flakiness Tracking & Analytics
• Calculating Flake Rate from CI History
• Correlating Flakiness with CI Runner Load