--- title: Re-derive the 374,000 null prop-market selection endpoints from the observation series type: task status: planned description: >- prop-market-selection-endpoint-coverage is red with 5 findings because the grain backfill copied endpoints from the retired OPEN/CLOSE rows instead of deriving them from the series; 180,040 openings and 194,146 settled closings are recoverable from min/max(observed_at). base_uri: user:task/league/repair-prop-market-selection-endpoint-nulls.md created_at: '2026-09-24T19:54:10.995Z' entity_id: dec29938-5770-4d12-ad74-341fa2a99440 owner_identity_uri: █████████████████████████ public_read: true relations: - blocked_by [[████████████████████████-███-█████-████-██████-██████-███████████]] tags: - user:tag/league-xo-football.md - user:tag/software-task.md updated_at: '2026-09-24T23:00:29.929Z' visibility_analyzed_at: '2026-11-05T23:50:08.761Z' --- ## Problem `prop-market-selection-endpoint-coverage` has been RED since it landed on 2026-09-24, with 5 findings and no disposition: PRIZEPICKS 2025 0.0457, PRIZEPICKS 2024 0.0949, FANDUEL 2023 0.6198, KALSHI 2026 0.8472, PRIZEPICKS 2023 0.8485. The check is correct and the defect is real. It is NOT a capture outage and the importer is not where to look — these values were captured and then lost in DERIVATION. `scripts/backfill-prop-market-selection-snapshots.mjs` populated `opening_` and `closing_` by COPYING the retired OPEN and CLOSE rows rather than by deriving them from the observation series, so a selection whose retired OPEN row was absent got a null endpoint even though its series carries a first observation. That script is RETIRED — league `d4c78e4e2` swept it with the index tables its phase-INDEX pass copied from — so recover it with `git show d4c78e4e2^:scripts/backfill-prop-market-selection-snapshots.mjs`. It is not `scripts/backfill-prop-market-grain.mjs`, which this task and the registry both named and which never touched these columns at all. ## Measured population, 2026-09-24 Split by which endpoint is missing, because the two have different causes and the pooled rate does not say which: - **180,040 selections missing an opening**, led by PRIZEPICKS 2024 (77,086), PRIZEPICKS 2025 (40,394) and FANDUEL 2023 (36,563). - **194,146 SETTLED selections missing a closing**, led by DRAFTKINGS 2024 (82,774), DRAFTKINGS 2025 (73,730) and DRAFTKINGS 2023 (8,852). Two book-seasons carry a null `season_year` and appear in the split as a blank group — FANDUEL 1,767 and PRIZEPICKS 1,178 missing openings. A column-counting parser drops them; see the `NULL_SEASON` gotcha on [[█████████████████████████-████-██████-█████-████████]]. ## Recoverability is confirmed, not assumed Probed 500 PRIZEPICKS 2024 selections carrying a null `opening_observed_at`: - 500 of 500 have an intact observation series (555 observations, 1 to 4 each). - 500 of 500 carry a `selection_metric_line` on their first observation. - **0 of 500 carry an `odds_american`** on the first observation, which is PrizePicks being PrizePicks rather than a second defect — it is a fixed-payout book and stores no American odds. A repair must not treat a null `opening_odds_american` on PRIZEPICKS as a failure to derive. So `opening_observed_at` is `min(observed_at)` over the selection's own series and `closing_observed_at` is `max(observed_at)`, both recoverable for the whole population. That is what makes this a defect rather than an abstention: the registry row records that ZERO of 4,411,191 selections lack a series. ## Scope A derivation repair of roughly 374,000 rows in production. NOT an importer change. A RISE on a CURRENT season would point at live writes and is the one case that does send you to the importer for that book. ## Plan - [ ] Re-measure both arms immediately before writing, and quote them separately — a repair to either alone moves the pooled rate and looks like both were fixed - [x] **Read the derivation and write the pass. DONE: `scripts/backfill-prop-market-selection-endpoints.mjs`** (league `63a393c99`, 7 specs against a real database, dry run by default, `--arm opening|closing|both`, `--source_id` for a pilot). It restates the retired script's phase-SERIES pass; there was nothing to extend, since that script went with the index tables its exact phase-INDEX pass copied from. **Two things it must keep doing, both of which a later simplification would undo.** It passes selection keys as a `VALUES` list, never a key range — the target rows are sparse, so a range tight over `prop_market_selections` spans essentially the whole 35.5M-row observation table, and the predecessor died on `statement_timeout` doing exactly that. And its null guard is per COLUMN rather than per row, so a selection holding an `observed_at` but no odds still receives the odds - [ ] **Run it.** Not yet run against production. `--arm opening` is the clean first pass: 180,040 rows, every one derivable, and it is disjoint from the closing arm - [x] **Size the slice with NO observation series behind it. MEASURED 2026-09-24: it is EMPTY, so the whole population is recoverable by derivation and this check can reach zero.** All 180,040 selections carrying a null `opening_observed_at` derive a real opening from `min(observed_at)`, spanning 2023-10-18 to 2026-09-23, and ZERO of the 4,422,276 rows in `prop_market_selections` lack observation rows. Validated by forcing the same `exists` predicate false on a key that cannot exist, since a corpus-wide zero deciding a repair strategy is the kind that lies. **This CORRECTS an earlier version of this step**, which inherited the retired snapshot backfill's header claim that the series does not reach back over the whole corpus and declared an ungradeable floor on the strength of it. Whether or not that held when written, it does not hold now - [ ] Establish per-book which endpoint VALUE columns are legitimately null (PrizePicks odds is the known case) so the repair does not manufacture a value the book never published - [ ] Rehearse on a bounded slice and reconcile before the full write - [ ] Apply, then re-run `prop-market-selection-endpoint-coverage` and assert it reads green with the unit count unchanged at 14 or more - [ ] Verify the check still DISCRIMINATES after the repair — its non-firing arm must be made to fail on a reintroduced null ## Verification The check must go green because the nulls were filled, not because the population was narrowed. Assert the graded unit count and both arms' absolute counts, never just the absence of findings. ## Origin Found by `repl-ef5faa34` (`███████████████████████████████████`) on 2026-09-24, dispositioning the check shipped the same day by [[█████████████████████████-████-██████-█████-████████]]. That task is at its ceiling and does not own a 374,000-row production write, so the disposition is REPAIR-under-its-own-task rather than park-as-baselined.