CFB-2b promotion spec — surface the walk-forward table on /cfb/performance

Status: SPEC ONLY — not applied. Ground rule for this task forbids editing production files or the route; this is the precise plan for the owner to apply.

What exists today

_compute_cfb_perf_data() ([redacted]) reads cfb_predictions JOIN cfb_games WHERE settled_at IS NOT NULL — the 863-row season-2024 retrodiction (all created_at in a 16-second window 2026-04-27, 0 pregame rows, model trained on the season it was scoring). The route already labels this honestly (the dataset block: "type": "backtest", "Not a live prediction record.") — that labeling logic should NOT be removed; it stays correct for whatever legacy data remains in cfb_predictions.

This task built a genuinely walk-forwardwalk-forwardEvaluating week by week using only what was knowable before each week, mimicking how the model would actually have been used at the time. alternative: backtests/cfb_eval/walkforward_regen.py → cfb_predictions_walkforward table, in a separate sqlite file (backtests/cfb_eval/cfb_walkforward.db, not [redacted]) per the "no production-file edits" ground rule for this task. 4,332 rows, 2021-2025, leak-verified (see walkforward_regen_results.json).

Recommended production change (two parts)

Part 1 — promote the regen script into scripts/, targeting the production db

Copy backtests/cfb_eval/walkforward_regen.py's logic into a new scripts/regen_cfb_walkforward.py, with two changes:

1. OUT_DB → [redacted] (the production db get_db_connection() already points at), so the Flask route can query it with the existing connection helper — no cross-database ATTACH needed.
2. Table name stays cfb_predictions_walkforward (distinct from cfb_predictions — never overwrite the legacy retrodiction rows; both tables coexist).

Run it once by hand to backfill 2021-2025, then wire it into the existing weekly CFB retrain/predict cadence (wherever CFBPredictionEngine.train_models() + generate_predictions() are currently invoked on a schedule — grep for the cron/scheduler entry point before adding a second one). Because this script retrains 5 fresh models per run (one per test season) it is NOT meant to run every night — re-run it only when: (a) a new season's worth of games completes (so the following season can be added as a new walk-forward test season), or (b) the production CFBPredictionEngine's feature set / hyperparameters change materially. A monthly or per-season cron is sufficient; do not add [redacted] entries per this task's ground rules — spec only.

Part 2 — _compute_cfb_perf_data() reads BOTH, walk-forward as the default view

--- a/[redacted]
+++ b/[redacted]
@@ def _compute_cfb_perf_data() -> Dict:
     conn = get_db_connection()
     cursor = conn.cursor()
+
+    # Walk-forward (honest) table -- see backtests/cfb_eval/walkforward_regen.py
+    # / scripts/regen_cfb_walkforward.py. Every row here is provably pregame:
+    # both model training and rolling-feature history for a season-S game are
+    # built exclusively from seasons < S (see that script's leak-free
+    # verification section). This becomes the DEFAULT view.
+    cursor.execute('''
+        SELECT * FROM cfb_predictions_walkforward
+        ORDER BY game_date DESC
+    ''')
+    walkforward_settled = [dict(r) for r in cursor.fetchall()]
+
+    cursor.execute('''
+        SELECT season,
+               COUNT(*) AS n,
+               AVG(CASE WHEN moneyline_correct_platt = 1 THEN 1.0 ELSE 0 END) AS ml_acc,
+               AVG(CASE WHEN spread_correct = 1 THEN 1.0 ELSE 0 END) AS sp_acc,
+               AVG(CASE WHEN total_correct = 1 THEN 1.0 ELSE 0 END) AS tot_acc,
+               AVG(home_win_prob_platt) AS conf
+        FROM cfb_predictions_walkforward
+        GROUP BY season ORDER BY season DESC
+    ''')
+    walkforward_by_season = [dict(r) for r in cursor.fetchall()]
+
+    # Market baseline for the SAME rows, for the honest "did we beat the
+    # market" framing the retrodiction never had -- pull from
+    # walkforward_regen_results.json (or recompute inline; that JSON is a
+    # build artifact, not something the live route should parse from disk on
+    # every request -- persist its summary into a small
+    # `cfb_walkforward_summary` table instead, written once per regen run,
+    # and read that here. Left as a follow-up detail for whoever applies
+    # this -- not spec'd to the byte since it's a small reporting nicety,
+    # not the core honesty fix.)

     cursor.execute('''
         SELECT p.*, g.spread_line, g.total_line
         FROM cfb_predictions p
         JOIN cfb_games g ON g.game_id = p.game_id
         WHERE p.settled_at IS NOT NULL
         ORDER BY p.game_date DESC
     ''')
     settled = [dict(r) for r in cursor.fetchall()]
     ... # existing retrodiction/by_season/dataset-provenance code UNCHANGED

     page_data = {
-        'overall': _settled_summary(),
-        'by_season': by_season,
-        'settled_predictions': [_serialize_prediction(p) for p in settled[:100]],
-        'dataset': dataset,
+        'overall': _settled_summary(),
+        'by_season': by_season,
+        'settled_predictions': [_serialize_prediction(p) for p in settled[:100]],
+        'dataset': dataset,
+        'walkforward': {
+            'by_season': walkforward_by_season,
+            'settled_predictions': [_serialize_prediction(p) for p in walkforward_settled[:100]],
+            'n_total': len(walkforward_settled),
+            'label': (f"{len(walkforward_settled)} predictions, seasons "
+                      f"2021-2025, walk-forward: each season's model trained "
+                      f"and every rolling feature computed using ONLY strictly "
+                      f"prior seasons (verified leak-free, see "
+                      f"backtests/cfb_eval/walkforward_regen.py). Judged "
+                      f"against devigged closing lines, not raw accuracy in "
+                      f"isolation -- see market comparison below."),
+        },
     }
     ram_cache.set('cfb_performance_page', page_data, ttl=1800)
     return page_data

Part 3 — template (templates/cfb/performance.html)

Add a walkforward section ABOVE the existing retrodiction block (make it the primary content), with the existing retrodiction table demoted to a collapsed "legacy retrodiction (not walk-forward, kept for reference)" section — do not delete it outright without checking nothing else links to dataset/by_season/settled_predictions at the top level (the JSON API consumer cfb_perf_full_json() returns the same dict; a downstream script/dashboard could depend on those exact keys still being present, hence ADD walkforward as a new key rather than replacing by_season etc. in place).

What this deliberately does NOT do

• Does not delete cfb_predictions or stop writing to it. The existing generate_predictions() / settle_predictions() production flow (used for the live upcoming-games page, /cfb/) is untouched — this is additive.
• Does not change CFBPredictionEngine.predict_game's history_seasons = range(season - train_window, season + 1) bug. That is a separate, smaller fix (drop the + 1) the owner may want to apply to the LIVE serving path independent of this reporting change — flagged here, not spec'd (touches the model's live inference, a different risk profile than a reporting-only route change; the walk-forward harness sidesteps it entirely by never calling predict_game, per this script's docstring).
• Does not add a market-baseline column to the live /cfb/ page — this spec is scoped to /cfb/performance only, matching the task.

Verification before applying

1. python3 backtests/cfb_eval/walkforward_regen.py (or the promoted scripts/regen_cfb_walkforward.py) runs clean, leak-verification prints ALL FOLDS PASS, persisted row count matches in-memory count (no silent write-race truncation — see the note in that script about a competing writer observed on this filesystem during development; if the promoted script also sees this, the retry-with-count-check pattern in walkforward_regen.py's final persist step should be kept, not dropped).
2. Confirm cfb_predictions_walkforward has exactly 5 distinct season values with counts matching cfb_games' per-season completed count (2021: 849, 2022: 854, 2023: 868, 2024: 873, 2025: 888 as of this run).
3. Smoke-test /cfb/performance (200) and /cfb/api/performance/full (valid JSON, walkforward key present) after applying.
4. kill -HUP $(cat logs/[redacted].pid) to deploy (per house rule — commits ≠ deploy; templates load live, routes need the reload).
5. pytest scripts/unit if any test touches this route.