Test run history retention

Progress

- [ ] Decision: pick retention model (rolling-window column vs. append-only
      log table) and retention size (X runs / Y days / both)
- [ ] Schema change (new column or new table + migration for the existing
      `state/test_run_history.sqlite`)
- [ ] Write path: record each run's result into the retained history, not
      just the current-state counters
- [ ] Retention/pruning: enforce the X-run or Y-day cutoff so the table
      can't grow unboundedly
- [ ] Expose the retained history (at minimum a `getHistory($testName)`
      method; optionally surface it in `printRunSummary()`'s report)
- [ ] Unit tests mirroring `TestRunHistoryStoreTest.php`'s existing style
      (schema migration, write, prune, read)
- [ ] Full-suite dry run against a scratch copy of the real
      `state/test_run_history.sqlite`, same as done for the recovery-window
      change

Not started — this file is a plan only, written per user request after estimating the effort in conversation.

Context

tests/Support/TestRunHistoryStore.php (test_run_results table) is a snapshot table, not a log: one row per test name, PRIMARY KEY test_name, upserted on every run. It currently tracks, per test, only:

• last_status, last_run_at, last_changed_at

• consecutive_fail_count / consecutive_pass_count (current streak only)

• first_failed_at (start of the current fail streak, cleared on recovery)

• recovered_at (start of the current pass streak, cleared if it fails again)

This is enough to classify each run (new_failure, long_standing_failure, recovered, recently_recovered — see the recovery-highlight-window work in this same file) but it cannot answer "what did this test do 3 days ago" or "how often has this test flaked over the last N runs" — once a streak ends, everything about it is gone except the counters.

The user asked how much effort it would take to retain real history (X runs or Y days) instead. This plan captures that estimate so it can be picked up later without re-deriving it.

Options

Option A — rolling window column (bounded, no new table)

Store the last N {run_at, status} pairs as a JSON array in the existing test_run_results row, shifting out the oldest entry each time a new one is appended.

• Row count never grows past the current 605 (one per test) — no pruning job needed beyond capping the JSON array length.

• Supports "last X runs" cleanly. Does not cleanly support "last Y days" (a day cutoff would have to look inside the JSON blob).

• Querying requires app-level JSON decode; no real SQL filtering across tests (e.g. no cheap "which tests failed more than twice in the last 10 runs" query).

• Estimate: ~1–1.5 hours.

Option B — append-only log table (proper history)

New table, e.g.:

CREATE TABLE test_run_log (
    test_name TEXT NOT NULL,
    run_at TEXT NOT NULL,
    status TEXT NOT NULL
);
CREATE INDEX idx_test_run_log_test_name_run_at ON test_run_log (test_name, run_at);

One row inserted per test per run, alongside the existing test_run_results upsert (that summary table stays as-is and keeps driving the fast classification logic — no need to touch it).

Breakdown:

1. New table + migration for the existing DB file — reuse the same CREATE TABLE IF NOT EXISTS / PRAGMA table_info guard pattern already used for the consecutive_pass_count/recovered_at migration. ~15 min.

2. Write path: one INSERT per test per run, wrapped in a transaction (605 inserts/run is trivial for SQLite, but batching avoids 605 separate fsyncs). ~20 min.

3. Retention/pruning — pick one or both:

   • Count-based: keep only the last X rows per test_name. Needs ROW_NUMBER() OVER (PARTITION BY test_name ORDER BY run_at DESC) (SQLite ≥ 3.25, confirm the PHP build's bundled SQLite supports window functions) and delete anything ranked past X.

   • Day-based: DELETE FROM test_run_log WHERE run_at < :cutoff. Trivial.

   • Whichever is chosen, run it every full-suite run (same place pruneStaleEntries() runs today).

   ~30 min, plus the decision above.

4. A way to actually use the data — right now nothing would read it. At minimum a getHistory(string $testName, int $limit): array method. Anything shown in the CLI report (e.g. a flakiness indicator, a per-test timeline) is a real UX decision, not just plumbing — budget separately. ~30–90 min depending on how much is surfaced.

5. Tests mirroring the existing TestRunHistoryStoreTest.php style (insert, prune-by-count, prune-by-day, read). ~30–45 min.

Estimate: ~2–3 hours, not counting open-ended report/UX work in step 4.

Storage growth (Option B only — Option A is bounded by construction)

This environment's history shows very frequent runs — the "76 runs since 2026-09-25" counters observed in conversation imply roughly one full-suite run every 15–20 minutes. At 605 tests/run that's ~45–60k rows/day. A 30-day retention window would land around 1.3–1.8M rows — likely low tens of MB on SQLite, not alarming, but a hard row-count cap is worth adding regardless of the day cutoff, in case run frequency increases later.

Recommendation

Unless calendar-day queries ("show me what happened last week") are actually wanted, Option A (rolling window column) is cheaper, simpler, and structurally can't grow unboundedly. Option B is the right call only if day-based retention or cross-test SQL queries over history are genuinely needed.

Open questions for whoever picks this up

• Runs, days, or both? (Changes which option applies and the pruning SQL.)

• If days: what's Y? If runs: what's X?

• Does the report (printRunSummary()) need to display anything from this history, or is "retain it for later querying" enough on its own?