--- title: Derive nfl_games closing lines from market observations type: task status: completed description: >- The four nfl_games betting-line columns freeze at first nflverse publish and only become closing lines via a manual post-season sweep. Moves ownership to a derivation over prop_market_selection_observations, closing line only. base_uri: user:task/league/derive-nfl-game-closing-lines-from-market-observations.md created_at: '2026-09-24T22:53:42.887Z' entity_id: 9cfd84a8-db59-49ea-9d9f-1e8437745156 finished_at: '2026-09-25T06:18:16.999Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ public_read: true started_at: '2026-09-24T23:05:56.824Z' tags: - user:tag/league-xo-football.md - user:tag/nfl-betting.md updated_at: '2026-09-25T06:18:16.999Z' visibility_analyzed_at: '2026-11-06T00:17:49.744Z' --- `nfl_games.spread_line`, `total_line`, `home_moneyline` and `away_moneyline` are written once by `scripts/import-nfl-games-nflverse-nfldata.mjs` at first nflverse publish, roughly four weeks before kickoff, and never move again during the season. Measured 2026-09-24, 62 of the 64 priced 2026 REG games disagreed with the feed the importer itself reads, by a mean 1.72 points of spread and 1.62 of total, with four games carrying the wrong favorite. The columns only become closing lines when somebody remembers to run the importer with `--overwrite_existing` after the season. This task moves ownership of those four columns off the nflverse importer and onto a derivation over `prop_market_selection_observations`, the observation-grain series the prop-market grain redesign landed on 2026-09-24. ## Scope The CLOSING line only. `FIRST_OBSERVED` / opening lines are explicitly out of scope: a polling series cannot tell the market's open from the first price we happened to see, and the honest version of that needs prospective `is_open` transition capture rather than a derivation. ## Why the columns stay on nfl_games `libs-server/apply-play-by-play-column-params-to-query.mjs:96` resolves plays-view column params by NAME against `nfl_games` -- `total_line` is a live `BETTING_MARKETS` filter param. Moving the columns breaks that param or forces a second join path, and the close is exactly 1:1 with the game row, so a separate table keyed on `esbid` is the same row with a join tax. The fix is to change the OWNER and the WRITE MODE, not the address. ## Measurements this plan rests on All taken 2026-09-24 against production. - **A derived close is tight, but only across books.** Best-across-books last pre-kickoff observation over all 32 games of 2026 weeks 1-2: median 19.4 minutes before kickoff, worst 67. Per book individually it averages 16 to 43 hours out, maxima six days, because books stop refreshing at wildly different times. The close MUST be a cross-book consensus at the latest available instant, never one book's last price. - **`is_live` cannot define the close.** Null on 13.7M of 14.5M `prop_market_observations` rows. Use `observed_at <= nfl_games.kickoff_at`, which exists as a real `timestamptz`. - **Only four books post a single main market.** CAESARS, DRAFTKINGS, FANATICS and FANDUEL. BETMGM, BETRIVERS and PINNACLE file their whole alternate ladder under `GAME_MATCHUP_SPREAD` / `GAME_MATCHUP_TOTAL` -- BetMGM carried 41 spread markets spanning 60 distinct lines on one game -- so a median that includes them is a median of a ladder. - **Nickname is the only robust side key, and it is clean.** `selection_type` is empty on game lines (4 populated rows against 104,535 since 2026-09-01), so only free-text `selection_name` identifies a side, and it varies per book (`CHI Bears`, `Chicago Bears`, `Chicago Bears +4.5`). Matching on the 32 unique NFL nicknames resolved 207 of 207 genuine team names across 2026 REG with ZERO ambiguous matches. City matching must not be used -- it breaks on two-token cities exactly as the ████████████ trap sheet records. - **`source_selection_id` is line-scoped on DraftKings.** A new id appears per line move, so `distinct on (source_selection_id)` returns one row per historical rung. Collapse on the market's own latest `observed_at`. ## Plan ### Phase 1 -- DDL and schema export Add provenance columns to `nfl_games`, nullable: - `closing_line_observed_at timestamptz` - `closing_line_book_count integer` - `closing_line_computed_at timestamptz` Provenance is the point. The reason the current columns could be wrong for a whole season undetected is that the value carried no statement of what it was; with these, a thin or stale close is self-reporting. DDL, `yarn export:schema` and the consuming code ship in ONE commit per `docs/guides/schema.md`. ### Phase 2 -- the derivation script `scripts/calculate-nfl-game-closing-lines.mjs`, taking `--season_year`, `--week`, `--esbid` and `--dry-run`. For each target game where `kickoff_at` has passed and the game is final: 1. Select main markets (`GAME_MATCHUP_SPREAD`, `GAME_MATCHUP_TOTAL`, `GAME_MATCHUP_MONEYLINE`) for the four clean books only. 2. Per market, take the latest `observed_at <= kickoff_at`; take the rows at that instant. 3. Resolve each selection to home or away by nickname against `home_nfl_team` / `away_nfl_team`. A market that does not resolve to exactly one home and one away selection is SKIPPED and counted, never guessed. 4. Median across books: `total_line` directly, `spread_line` as the home-relative value (negate the book's home price -- the column is home-relative and a favorite is POSITIVE here, opposite the book), and both moneylines. 5. Write the four columns plus the three provenance columns, overwriting. Emit a summary line and a signal on failure per [[user:guideline/surface-pipeline-failures.md]]. ### Phase 3 -- remove the nflverse ownership Delete `spread_line`, `total_line`, `away_moneyline` and `home_moneyline` from the `format_game` payload in `scripts/import-nfl-games-nflverse-nfldata.mjs`, with a comment naming the new owner. Retire the manual `--overwrite_existing` sweep as the repair mechanism for these columns, and drop the line columns from the close-out guidance. Unplayed games then carry NULL rather than a look-ahead price. That is the intended behavior: a null is honest and forces a reader to the live path, where a stale number does not. ### Phase 4 -- verify, then backfill - Negative control first: run against 2026 week 3 (unplayed) and require zero writes. - Run 2026 weeks 1-2 and diff the derived close against the current stored values AND against the nflverse feed. The derived value should track the feed closely on settled games; explain every game where it does not. - Spot-check a repaired historical season (2024) where the stored value is a real swept closing line -- that is the known-positive control, and agreement there is the evidence the derivation is right. - Then backfill every season the observation series covers (2023-10-18 onward). Seasons before that keep their swept values; the script must not null a column it cannot derive. ### Phase 5 -- schedule A crontab entry on `crontab-main`, after the games importer, in-season only. Instrument per [[user:guideline/background-job.md]] -- output oracle distinct from exit code, signal on death. ## Out of scope, worth their own tasks - **BETMGM, BETRIVERS and PINNACLE alt-ladder misclassification.** Their importers file alternate rungs under the main market type. This is an importer bug, not something the read path should route around; until it is fixed those three books are unusable for a main-line consensus, which costs this derivation most of its book depth. - **Player names misfiled as `GAME_MATCHUP_MONEYLINE` on Caesars.** Four distinct names in 2026 REG (`Bijan Robinson`, `Drake London`, `Jalen Coker`, `Tetairoa McMillan`). The nickname resolver rejects them correctly, so they are inert here, but the classification is wrong. - **A live as-of read path**, `nfl_game_line_as_of(esbid, at)`, for unplayed games and point-in-time backtests. This task deliberately leaves unplayed games NULL; the live path is what readers should use instead, and it is the natural successor. ## Current state, 2026-09-24 Landed in league `80b639594`, with the applied DDL in the private repository. Done: - Provenance DDL applied against `league_production` and exported. - `libs-server/nfl-game-closing-line.mjs` and `scripts/calculate-nfl-game-closing-lines.mjs`, with `job_types` `CALCULATE_NFL_GAME_CLOSING_LINES` = 161 and `nfl_team_nicknames` added to `libs-shared/constants/nfl-teams-constants.mjs`. - The four line columns removed from the nflverse importer's `format_game`. - Verified: negative control on unplayed 2026 week 3 writes zero while week 1 writes 16, so the zero is not a broken query. Accuracy against the nflverse closing line on 2026 weeks 1-2 is 0.22 mean on spread and 0.19 on total with zero favorite inversions, against 1.30 and 1.06 for the values that were stored. The 2024 known-positive control over all 272 games, whose stored values are real swept closing lines, lands 0.22 and 0.28 with no outliers and two favorite inversions, both pick'em games straddling zero. - Backfilled 2026 REG weeks 1-2: 32 of 32 games, 4 and 6 books, a mean 20.3 and 17.7 minutes before kickoff. A defect caught during verification and fixed: the derivation took `season_year` and `week` with no `season_type`, so a week 1-2 run pulled preseason `2026082351` and returned 33 games for a 32-game scope. `season_type` now defaults to `REG`. ### Remaining Nothing blocking. Phase 5 and the deploy both closed 2026-09-25; the spec landed with the crontab entry in `93ac0240e`. The follow-ups below are optional and none of them gate anything: - **Re-measure Pinnacle against the six-book consensus.** Excluded today because its collapsed board makes the main-rung rule pick non-spread products -- 1.14 mean error against BetMGM's 0.28. A sibling landed Pinnacle side derivation in `17782badd` and `5ce704c47`, so re-admitting it is a real question with a measured gate. - **Fix the importer misclassification directly.** BetMGM, BetRivers and Pinnacle file alternate rungs under the main market type. The balanced-odds rule recovers BetMGM and BetRivers accurately, but fixing the importer would let the derivation stop inferring. - **The overwrite footgun stands.** `--season_year 2023` in a non-dry run would replace that season's swept closing lines with the thin derivation, and nothing stops it. A minimum-book-count guard would close it; not built, because it competes with wanting a one-book line on an upcoming game. ### Deployed 2026-09-25 The hourly entry is live on the main host and verified across four consecutive runs at 02:50Z, 03:50Z, 04:50Z and beyond -- each `games_derived: 116`, `games_skipped_thin: 0`, `games_cleared: 0`, recomputing all 116 rows, with `market_line_computed_at` advancing every hour. Re-check it by confirming the entry in the main host's crontab and reading the tail of the job's log. The `=== SUMMARY ===` line is the oracle, and the failure mode looks like success: `games_derived: 0` exits zero. Freshness is the check that proves the SCHEDULE works rather than that the file is installed -- sample `max(market_line_computed_at)` twice an hour apart, because a value read once cannot be distinguished from one that never moves. **The code and the cron shipped separately, and that gap is the lesson.** A sibling session deployed the code to the league checkout on the production host at 21:41 EDT on 2026-09-24 but never ran `load:crontab:main`, so the script sat on the host for five hours with nothing invoking it. No deploy oracle sees that state -- the code is present and every API check is green -- and only `crontab -l | grep` finds it. Note also that `preflight:deploy` cannot pass from the shared league checkout during active hours: gate (1) requires `HEAD == origin/master` and siblings commit faster than ████████ pushes, so the range grew from 32 to 44 commits in 45 minutes while waiting. Deploy from a throwaway worktree pinned to `origin/master`, which satisfies the ahead, behind and dirty-tree gates by construction. ### Both operator decisions resolved, 2026-09-24 **Upcoming games carry the live line.** Landed in league `cc27e37ff`, DDL in private `4829e9a`. Every read is bounded at `least(kickoff_at, now())`, so a played game's line is frozen at its close by construction and an upcoming game's tracks the board. Whether a row holds a close or a live line is derived from `kickoff_at <= now()` and never stored, so there is no has-it-kicked-off branch in the code and nothing to drift when a game is flexed. Provenance columns renamed `closing_line_*` to `market_line_*`, because the old name asserted something false on an upcoming row. A consequence that had to be handled: 23 unplayed 2026 rows held a look-ahead line the retired importer wrote, with no provenance and nothing left to refresh it -- the original defect surviving its own fix. Those are cleared. A PLAYED game is never cleared, because its stored value may be a legitimate swept closing line from before the observation series: 107 of the 272 games in 2023 are exactly that. Controlled both ways -- 2019 and 2023 clear zero while 2026 clears 23. **Invariant now verified over all 272 rows of 2026**: a line is populated if and only if it was derived and carries provenance. 116 populated, 116 derived, zero holding a line without it. The PHI at CHI row that started this reads -4.5 / 41.5 / PHI -235 from 6 books. **Past seasons: do NOT re-derive.** Measured deviation of the derivation against the stored swept values: | season | games | spread mean / p90 / max | total mean / p90 / max | large deviations | | ------ | ----- | ----------------------- | ---------------------- | ---------------- | | 2023 | 165 of 272 | 0.44 / 1.0 / 5.0 | 0.57 / 1.5 / 4.0 | 7, all single-book | | 2024 | 272 | 0.22 / 0.5 / 2.0 | 0.28 / 0.5 / 1.5 | 0 | | 2025 | 272 | 0.29 / 1.0 / 3.0 | 0.34 / 1.0 / 2.0 | 3, all pick'em | 2024 and 2025 sit below the market's own 0.5 tick, and for scale the DST study moved r from 0.393 to 0.389 for a 0.5-point Friday-versus-close substitution -- so the deviation is smaller than an effect anyone has been able to measure. Every large 2025 deviation is a pick'em spread where the two disagree on which side is favored by a point and a half. 2023 looks worse and the reason inverts the question: the observation series begins 2023-10-18, so only 165 games are covered and all seven large deviations are `books=1`. That is OUR thinness, not their error. Re-deriving 2023 would replace a real swept closing line with a single book's price. So the stored values for 2024 and 2025 are good closing lines and rewriting them buys nothing while costing the reproducibility of every backtest already run on them. **Footgun this leaves.** `--season_year 2023` in a non-dry run would overwrite that season's swept lines with the thin derivation, and nothing stops it. A minimum-book-count guard on overwriting an existing line would close it; not built, because the honest fix competes with wanting a one-book line on an upcoming game where one book is all there is.