Every question worth asking about flakiness is a question about history, and CI throws history away by default. The minimum thing that fixes that is one append-only table and a parser for a file your pipeline already writes.
One table, at attempt granularity
Resist the instinct to store one row per test per run. Store one row per attempt. The distinction between a flake and a failure is precisely the distinction between attempts within a run, so a schema at run granularity has thrown away the flake signal before you write the first query.
CREATE TABLE test_attempts (
id BIGSERIAL PRIMARY KEY,
run_id TEXT NOT NULL, -- CI run identifier
commit_sha TEXT NOT NULL,
branch TEXT NOT NULL,
started_at TIMESTAMPTZ NOT NULL,
test_id TEXT NOT NULL, -- stable: file::class::name
attempt SMALLINT NOT NULL, -- 1 for the first try
status TEXT NOT NULL, -- pass | fail | skip
duration_ms INTEGER,
error_type TEXT, -- exception class
error_sig TEXT, -- normalised signature
http_status SMALLINT, -- provider status, if any
provider TEXT,
model_served TEXT, -- from the response, not the request
passes_of_n TEXT -- e.g. '4/5' for an N-of-K test
);
CREATE INDEX ON test_attempts (test_id, started_at DESC);
CREATE INDEX ON test_attempts (error_sig, started_at DESC);
Four of those columns are the ones teams add later, painfully, and every one answers a question that is otherwise unanswerable:
-
model_served, read from the response. The requested model is in your config and tells you nothing; the served one is what changes underneath you when an alias moves. Without this column you cannot correlate a flakiness spike with a silent model update, which is the correlation you will most want. -
error_sig. The normalised grouping key from deduplicating failures. Computing it at ingest is cheap; recomputing it across a year of history later is not. -
http_status. Separates “the provider returned 429” from “the assertion failed”. These need different people, and without the column they look identical. -
branch. Flakiness on the default branch and flakiness on a feature branch have different blast radii, and the ranking in a flakiness score multiplies by exactly this.
Make the table append-only and never update a row. An attempt is a historical fact, and the moment rows become mutable somebody will “correct” a batch of them and every trend line above will quietly change shape. If a parser bug produced bad rows, delete them by run id and re-ingest from the artifact, which is still available.
test_id must be stable across renames or your history splits in two. If your team renames tests often, allocate a stable identifier explicitly rather than deriving it from the name.
Ingesting from what CI already produces
You do not need instrumentation inside the test process. JUnit XML is already emitted by pytest (--junitxml), Vitest (--reporter=junit --outputFile) and Playwright, and where a runner supports merged reruns the retried attempts appear as <flakyFailure> or <rerunFailure> elements inside the same <testcase> as the final result. That nesting is exactly the attempt structure the table wants.
- Make every job write JUnit XML to a fixed path, including jobs that pass. The passing rows are the denominator of every rate.
- Add one final always-run step that parses the XML, adds the run metadata CI knows (run id, commit, branch), and inserts the rows. Use your CI’s always-run condition —
if: always()in GitHub Actions,when: alwaysin GitLab — or you will only ever collect data from green runs, which is the opposite of useful. - Insert with a unique constraint on
(run_id, test_id, attempt)and an upsert, so a retried ingestion step cannot double-count. - Point a query tool at it. Grafana over Postgres, a scheduled notebook, or a static HTML page generated nightly — the storage is the hard part and the rendering is not.
Three queries that earn their keep
The dashboard is not a wall of charts. Three answers cover almost every reason anyone opens it.
-- 1. The ranked backlog: which tests are worth fixing this week
WITH per_run AS (
SELECT test_id, run_id,
MAX((status = 'pass')::int) AS any_pass,
MAX((status = 'fail')::int) AS any_fail
FROM test_attempts
WHERE started_at >= NOW() - INTERVAL '30 days'
GROUP BY test_id, run_id
)
SELECT test_id,
COUNT(*) AS runs,
SUM(any_pass * any_fail) AS flaky_runs,
ROUND(100.0 * SUM(any_pass * any_fail) / COUNT(*), 2) AS flake_pct
FROM per_run
GROUP BY test_id
HAVING COUNT(*) >= 30 AND SUM(any_pass * any_fail) > 0
ORDER BY flake_pct DESC
LIMIT 20;
-- 2. Is today unusual? Suite-level flake rate per day
SELECT date_trunc('day', started_at) AS day,
COUNT(DISTINCT run_id) AS runs,
COUNT(*) FILTER (WHERE status = 'fail') AS failed_attempts,
COUNT(DISTINCT error_sig) FILTER (WHERE status = 'fail') AS distinct_causes
FROM test_attempts
WHERE started_at >= NOW() - INTERVAL '60 days'
GROUP BY 1 ORDER BY 1 DESC;
-- 3. Did something change underneath us? Flake rate by served model
SELECT model_served,
MIN(started_at) AS first_seen,
COUNT(*) AS attempts,
ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'fail') / COUNT(*), 2) AS fail_pct
FROM test_attempts
WHERE started_at >= NOW() - INTERVAL '90 days'
GROUP BY model_served
ORDER BY first_seen DESC;
The third query is the one that pays for the whole exercise. A new value appearing in model_served on the same day a dozen tests started flaking is a complete diagnosis in one row, arrived at without reading a single stack trace. The second query’s distinct_causes column does similar work: forty failed attempts with one distinct cause is an incident, and four failed attempts with four causes is a bad day for four unrelated tests.
The model_served and provider columns only work if something records what actually served each request, which is awkward when a suite fails over between providers mid-run. Multigrid returns the served model and the upstream provider on every response through one API, so those two columns can be filled from the response the test already has rather than inferred from configuration.
Retention and cost
Attempt granularity means rows accumulate at roughly tests times attempts times runs per day. Work out your own figure before choosing storage: a suite of a few hundred tests on a busy repository produces rows on the order of hundreds of thousands per month, which is nothing for Postgres and is worth knowing rather than discovering.
Keep raw attempts for a bounded window — ninety days is enough for every query above — and roll older data into a daily summary per test. Drop duration_ms and error text from the rollup and keep the counts; nobody has ever needed the exact traceback of a flake from eight months ago, and the counts are what the trend lines read.
What to leave out
- Per-engineer attribution. A dashboard that names who introduced a flaky test changes what people report, not how many flaky tests exist.
- Prompt and completion text. This is a CI database, not a log store: it will contain customer data from your fixtures within a week, and it is not built to hold it. Store a hash and keep the payloads wherever your logging policy already says they go.
- A green/red status widget. Your CI already has one. This dashboard exists for the questions CI cannot answer, and every panel that duplicates CI is a panel that dilutes the three above.
- Alerting on a single flake. Alert on the rate crossing your published tolerance from an acceptable flake rate, or on a previously unseen
error_sig. One flake is not an event.
JUnit XML rerun elements, CI artifact APIs and always-run conditions differ between products and change over time. Check what your runner and CI provider currently emit before writing the parser.
Top comments (0)