vyndr/specs/snapshot-retention.md

# SNAPSHOT RETENTION + BACKTEST HARNESS — Phase 2

**(a) is a REPORT of what exists. (b) is a DESIGN PROPOSAL — nothing built yet.**
Data pulled live from Supabase 2026-07-20 ~02:40 UTC.

---

# (a) WHAT REPLAYABLE HISTORY ACTUALLY EXISTS

## The one-line answer

**`ledger_entries` is the only store of model history that exists. It holds 6 days
of graded props, and it does NOT contain the model's inputs — so today we can
score the grades we emitted, but we CANNOT test whether a different model would
have done better.** That second thing is what a backtest harness is for.

## Every other candidate store is empty

Row counts across the entire `public` schema:

| Table | Rows |
|---|---|
| `player_id_map` (lookup, not history) | 9,926 |
| **`ledger_entries`** | **643** |
| `grade_history` | **0** |
| `line_snapshots` | **0** |
| `historical_props` | **0** |
| `closing_lines` | **0** |
| `resolution_results` | **0** |
| `accuracy_tracking` | **0** |
| `model_predictions_extended` | **0** |
| `engine1_weights` | **0** |
| `prediction_registry`, `outcomes`, `line_history`, `joint_outcomes`, `prop_correlations`, +25 more | **0** |

**A data warehouse was designed and never filled.** 38 empty tables. `grade_history`
even has the right shape (projection, modeled_prob, implied_prob, factors) but its
only writer is `gradingOrchestrator.persistGrade`, reachable solely via the n8n
`POST /api/grading/pipeline` route, which has never run in production.

Redis holds no history either: `snapshot:{sport}:latest|previous` is **two
generations at 24h TTL**, the intraday line `history[]` rides inside that same
expiring blob, and `outcomes:{sport}:log` (30d, cap 1000) carries **no odds, no
confidence, no projection**.

## What `ledger_entries` gives us (public model rows, `user_id IS NULL`)

**640 rows · 6 distinct game days · 2 sports · 215 players**

| | |
|---|---|
| Date span | 2026-07-11 → 2026-07-19 |
| Distinct game days | **6** (Jul 11, 12, 16, 17, 18, 19) — **Jul 13/14/15 are missing entirely** |
| Settled | 470 |
| Settled **with odds** (the usable backtest set) | **465** |
| With `locked_odds` | 635 (99.2 %) |
| With `closing_odds` | 636 — but see CLV below |
| With `confidence` | 640 |
| With usable projection (`model_value > 0`) | 585 |

Per day (graded / settled):

| Date | MLB | WNBA |
|---|---|---|
| 07-11 | 74 / 74 | — |
| 07-12 | 54 / 52 | — |
| 07-16 | 48 / 48 | 32 / 32 |
| 07-17 | **86 / 57** | 70 / 70 |
| 07-18 | **103 / 75** | 62 / 62 |
| 07-19 | 22 / 0 | 89 / 0 (tonight, unsettled) |

### Fields available per row
`sport, player_key, player_name, stat, line, side, locked_odds, book, grade,
edge, confidence, model_value (projection), graded_at, game_id, game_date,
closing_line, closing_odds, clv, clv_result, outcome, actual_value, settled_at,
revised_from_grade, team, opponent`

### 🔴 What is MISSING — and why it blocks real backtesting

1. **No feature vectors.** Nothing stores `l5_avg`, `l20_avg`, `opp_rank_stat`,
   `rest_days`, consistency, trap composite — the *inputs* the grade was computed
   from. **Without inputs you cannot replay a counterfactual model.** You can ask
   "did our B grades hit?" but not "would this new metric have graded it better?",
   which is exactly the question the metrics-engine north star requires.
2. **No `p_win` / `ev_pct` / `fair_odds`.** They only started existing in
   production tonight and are on no ledger column, so EV calibration has a
   sample of zero.
3. **No `grade_11`.** Only the 4-letter collapse is stored, so the C−/C/C+/B−
   distinction — the entire live range — is unrecoverable from history.
4. **No model version.** Nothing records which engine produced a row. Tonight's
   changes mean rows before/after are from different models and are silently
   mixed in the same table.
5. **CLV is unusable** — C4: `closing_line == locked_line` on ~95 % of rows, so
   `clv` is structurally ~0.
6. **Settlement gaps:** Jul 17 MLB 86 graded / 57 settled, Jul 18 103/75.
   ~28-29 rows/day never settle. Cause not yet diagnosed → feeds the Phase 2
   settlement-correctness audit. **0 pushes across all 470 settled rows** is also
   suspicious for a stat like hits and needs that audit.

## VERDICT

**We cannot meaningfully backtest yet, and this was the expected answer.**

- 465 settled-with-odds rows over **6 days**, 2 sports, one model era, no inputs.
- That is enough for a *descriptive* record (hit rate, ROI by band — already
  produced in `gate-simulation.md`) and **not remotely enough to validate a
  model change**, which needs input replay, multiple model versions, and enough
  independent games to clear noise.
- Under the n≥20 discipline, per-bucket claims are already thin; per-bucket
  *and* per-model-version would be vapour.

**Therefore SNAPSHOT RETENTION IS PRIORITY ZERO.** Every night without it is a
night of history we can never get back. The harness is worth little until the
store beneath it has been compounding for weeks.

---

# (b) DESIGN — the snapshot retention store (PROPOSAL, not built)

## Principle

**Capture the model's INPUTS and OUTPUTS at lock time, immutably, so any future
model can be replayed against the exact conditions the live model faced.** The
ledger records what we *claimed*; retention records *why we claimed it*.

## Where it lives: Postgres (Supabase), not Redis

Redis is a cache with TTLs — the thing that already lost us history twice. This is
permanent, queryable, joinable, and backed up by the job we just closed.

## Schema (proposed): `model_snapshots`

One row per (graded prop × snapshot cycle). **Append-only.** A re-grade at the next
cycle writes a NEW row — that is the point: it lets us ask whether the 14:00 read
or the 22:00 read was better.

```sql
create table model_snapshots (
  id              bigserial primary key,
  -- provenance: which run produced this, and with which model
  snapshot_id     uuid        not null,   -- one per runSnapshot() call
  captured_at     timestamptz not null,   -- grade LOCK time
  cycle_hour_utc  smallint,               -- 14/19/22/1/3, or null for intraday
  model_version   text        not null,   -- e.g. 'engine1@2026-07-20'
  code_sha        text,                   -- git sha at runtime

  -- identity (matches the ledger's natural key so outcomes can be stamped)
  sport           text not null,
  game_id         text not null,
  game_date       date not null,
  player_key      text not null,
  player_name     text not null,
  team            text,
  opponent        text,
  stat            text not null,
  line            numeric not null,
  side            text not null,

  -- MARKET (real book numbers, never model output)
  book            text,
  book_odds       integer,
  over_odds       integer,
  under_odds      integer,
  fair_odds       integer,      -- de-vigged
  fair_prob       numeric,
  overround       numeric,
  devig_method    text,

  -- MODEL OUTPUT
  grade           text,         -- 4-letter
  grade_11        text,         -- pre-collapse (C-, C, C+, B- ...)
  confidence      numeric,
  confidence_basis text,
  p_win           numeric,      -- the real probability
  ev_pct          numeric,
  projection      numeric,
  edge_pct        numeric,
  takeable        boolean,
  value           boolean,
  refused         boolean default false,
  refusal_reason  text,         -- juiced_no_edge / insufficient_data / ...

  -- THE COUNTERFACTUAL ENABLER
  features        jsonb not null,   -- the full feature vector at lock time
  archetype       text,

  -- OUTCOME (stamped later by the settle pass; null until then)
  outcome         text,         -- hit / miss / push
  actual_value    numeric,
  settled_at      timestamptz
);

create index on model_snapshots (game_date, sport);
create index on model_snapshots (player_key, stat, game_date);
create index on model_snapshots (model_version, game_date);
create unique index on model_snapshots (snapshot_id, player_key, stat, line, side);
```

### Why each unusual choice

- **`features jsonb`** — the single most important column. Schema-free so adding a
  feature never needs a migration, and a future model can be replayed on the exact
  inputs. Without this the harness can only grade our own homework.
- **`model_version` + `code_sha`** — a backtest that mixes model eras is worthless.
  Tonight proved the risk: pre/post grade-fix rows are already mixed in the ledger
  with nothing to tell them apart.
- **`grade_11`** — the 4-letter collapse throws away the live range (C−…B−). Store
  both or lose the resolution that matters.
- **`refused` + `refusal_reason`** — **refusals are training data.** The ledger
  drops them entirely, so we currently cannot ask "was the gate right to refuse
  these?" That question is unanswerable today and shouldn't stay that way.
- **Row per cycle, not per prop** — enables intraday calibration (is the early
  read or the late read sharper?) and directly serves the grade-lock/CLV arc.
- **Outcome stamped onto EVERY cycle row** for the same natural key, so each
  cycle's prediction is scored against the same truth.

## Where it's written

`snapshotService.runSnapshot` — the existing normalization chokepoint, the same
place `recordPipelineGrades` already writes the ledger. Best-effort and
non-blocking: **a retention failure must never break a snapshot** (same contract
as the ledger write). Also written on refusals, which the ledger path skips.

Settlement stamps outcomes in the existing settle pass, keyed on
`(player_key, stat, line, side, game_id, game_date)`.

## Volume + cost (the constraint that shapes retention)

~160 graded props/day × ~5 cycles ≈ **800 rows/day ≈ 292k rows/year**. At ~1-2 KB
of features per row that is **roughly 300-600 MB/year — which would exceed the
Supabase free tier (500 MB)** on its own. Proposal:

- Keep **full `features`** for **90 days** (well past any calibration window).
- After 90 days, null the `features` blob but keep every scalar column forever —
  the descriptive record stays permanent; only counterfactual replay ages out.
- Revisit before the 90-day boundary with real measured row sizes rather than this
  estimate. **Zero out-of-pocket still governs.**

## What it unlocks

1. **Backtest harness** — replay any model over stored inputs; hit rate, ROI,
   Brier, CLV by sport/tier/odds band.
2. **Calibration by odds band** (Phase 4) — needs `p_win` + outcome, both here.
3. **Metrics-engine validation** — the north star's "does this metric predict
   better?" gate is only answerable with stored inputs.
4. **Intraday sharpness** — which cycle's read is best.
5. **Refusal audit** — were the gates right?

## Sequencing

1. Ship retention **first** (priority zero — history compounds from tonight).
2. Let it accumulate while Phase 3 work proceeds.
3. Build the harness against it; its honest first output — *"6 days, one model
   version, cannot validate"* — is expected and correct.
4. Only after weeks of accumulation do calibration and metric validation mean
   anything.

## Open questions for Kev before building

1. **90-day feature retention** — right call, or keep everything and pay?
2. **Backfill?** We could seed `model_snapshots` from the 640 existing ledger rows
   (scalars only, `features` null, `model_version: 'pre-retention'`). Honest as
   long as the null features are explicit. Worth it, or start clean tonight?
3. **Refusals** — confirm we store them. It doubles-ish the row count but makes
   the gate auditable, and I think it's the difference between a record and a
   dataset.