--- title: Finish week-scoped data-view column migration type: task status: completed description: >- Migrate the remaining week-scoped columns (dfs ownership, dfs salary alias, game_opponent, team game props) onto the shared week-scope resolution landed in league fc4a84ca0, add the CI gate for the class, and fix team-prop team attribution. base_uri: user:task/league/data-views/finish-week-scoped-column-migration.md created_at: '2026-09-01T02:13:29.451Z' entity_id: 49196f72-472c-4b51-8a2e-e730c09d7d35 finished_at: '2026-09-01T19:34:07.681Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: High public_read: true tags: - user:tag/league-xo-football.md - user:tag/software-task.md updated_at: '2026-09-01T22:50:12.849Z' visibility_analyzed_at: '2026-11-05T23:48:48.965Z' --- Finish migrating the remaining week-scoped data-view columns onto the shared week-scope resolution, and add the gate that catches the class. ## Context A fact keyed by `(subject, year, week)` reaches a cell that may or may not carry a week axis. Three decisions have to agree — how many weeks the CTE spans, whether the join correlates on week, and what the table alias hashes — and each way they drift is silent: - CTE pinned to one week under a week axis: that week's value is broadcast onto every week row. - CTE spanning N weeks with an uncorrelated join: the week cell fans out into one row per week. - Alias coarser than the CTE contents: two columns differing only in their week list collapse onto one join group and one renders the other's data. Reported live on `/u/47a2d459080c3bc61eb4e850dba1164e`, where every player's 2024 prop line was identical across weeks 1 and 2. League `fc4a84ca0` landed the shared resolution (`libs-server/data-views/week-scoped-cte.mjs`) and migrated the six player game-prop columns, plus a separate fix for two columns whose sort was silently dropped. That commit is the worked example for everything below — read it first. Five column families were surveyed; this task carries the four that were not migrated, and the gate. Not yet deployed. `yarn deploy:all` is human-gated and the operator decides when. ## Assumptions - The `week_split` signal comes from `data_view_options.week_reference`, never from `row_axes`. A column declared at the base `player` grain is handed an EMPTY `row_axes` even under a week-axis request, because `group_tables_by_supported_row_axes` intersects the request axes with those of the identity `source.grain` names. - A week-scoped CTE must be unique per `(subject, year, week)`. Nothing in SQL generation can verify that; the oracle is an executed row-count-invariance fixture per source. - Week filtering goes through `nfl_week_id IN (...)`, never a decomposed `(season_year, season_type, week)` triple. The triple cannot express a list, and POST week 1 collides with REG week 1 — production holds 2024 playoff markets on `week = 1` beside the September ones. - Alias hashing the FULL requested week list is correct even where the CTE is pinned. `row_axes` is a request property, so no two columns in one query can disagree about `week_split`; a finer alias costs a join, a coarser one renders wrong data. ## Plan ### 1. `player_dfs_ownership_percentage` Same collapse as the game props: `get_params` takes the first week id, and the attach joins on pid alone even though the CTE already projects `season_year` and `week`. Route the week list and the join predicate through `week-scoped-cte.mjs`, and move the alias onto `week_scope_alias_key`. Verify: a result-equivalence fixture seeding two weeks with different ownership under `row_axes: ["year","week"]`, shown red before the change. ### 2. `player_dfs_salary` alias Its attach already correlates correctly — this is the residual half. `generate_table_alias` hashes `get_params`, which collapses the week list to its first entry, so two salary columns naming `[w1,w2]` and `[w1,w3]` hash identically and collapse into one join group. Move the week component onto `week_scope_alias_key`. Verify: an alias-separation fixture asserting two salary columns on different week lists emit two distinct aliases and two joins. Read `docs/create-data-view-test.md` on why that fixture proves a necessary and not a sufficient condition. ### 3. `game_opponent` Its CTE spans the full week list and correlates `season_year`, but not `week`. Under a week axis a team with games in weeks 1 and 2 matches both CTE rows in every week cell, so rows duplicate and the opponent is wrong. Add the week predicate when `week_split`, leaving the `year_offset` correlation path alone. Verify: a result-equivalence fixture seeding one team with two weeks' games, asserting one row per week carrying that week's opponent. `game-opponent-range-year-offset-fanout-result-equivalence.json` must stay green — it pins the legitimate multi-year fanout. ### 4. Team game props — the WEEK half `team_betting_market_with` / `_join` and `team_game_implied_team_total_with` / `_join` still pin a single week and join without correlation. Migrate the week scope exactly as the player game props were. Deliberately scoped to the week half. These columns also join on `player.current_nfl_team`, which is the wrong team for any past-season row; that is a separate defect with a different oracle and it is item 6, not this one. Verify: a result-equivalence fixture per column family, two weeks, distinct lines. ### 5. The gate A CI-runnable spec asserting that every column whose source is week-scoped emits a join predicate referencing the week reference under a two-week `["year","week"]` request. Needs no betting data, so it runs in CI where the data oracle cannot. Declare the week-scoped set explicitly rather than sniffing it — a sniffed set silently excludes a column that stops matching, which is the failure mode the gate exists to prevent. Verify: the spec must fail on a column with the predicate removed. A green never shown able to go red is not evidence. ### 6. Team game-prop team attribution (sequenced last) `player.current_nfl_team` is the CURRENT team, so a 2024 row shows the line of the team the player plays for today. Route through `player_year_teams` the way `game_opponent` does. This changes historical values from wrong to right, so it needs its own oracle and its own commit — do not fold it into item 4. Verify: a result-equivalence fixture seeding a player who changed teams between two seasons, asserting each year's row carries that year's team's line. ### 7. Production data oracle Extend the weekly data check that already grades `player_game_prop_column_resolution` with a unit asserting week 1 and week 2 lines differ where the base tables say they differ. CI holds no betting data, so this question is only answerable in production. ## Sequencing Items 1-3 together (one commit, they are three small mechanical migrations of a landed pattern), then 4, then 5, then 6, then 7. Regenerate golden fixtures inside the commit that changes behavior, never as a separate sweep. Restore compact JSON formatting after `data-view-test-cli.mjs --update`: it re-serializes the whole fixture and buries the real change. Extract the new `expected_query`, restore the file from git, substitute that one value. ## State of item 4 (uncommitted, reviewed 2026-09-01) Thread `e92a5053` died mid-item-4 and left the work uncommitted. **Reviewed statically and judged a sound partial — finish it, do not discard it.** What the review established: - The framework does pass `data_view_options` into both builders (`get-data-view-results.mjs:1336` for `with_func`, `:1447` for `join_func`), so the added `correlate_week_scoped_cte` calls are live rather than silently inert against a `{}` default. - The alias half of item 4 needs no work. `betting_markets_table_alias`'s `market_grain === 'game'` branch already covers team game props, because `resolve_market_grain` returns `game` for `is_team_game_prop`. That landed in `fc4a84ca0`. - The CTE projects `nfl_games.season_year as year, nfl_games.week as week`, matching the player path exactly, so the week semantics `correlate_week_scoped_cte` compares against `week_reference` are the same ones. - The join's new parenthesization of the home/away OR is necessary, not cosmetic. Without it the week predicate binds to the OR's right arm alone and the away-team branch matches every week. **The reported failure of `team-game-implied-team-total-from-betting-markets.json` is a stale golden, not a defect.** Its `expected_query` still pins `"nfl_games"."week" = 7 and "nfl_games"."season_type" = 'REG'`, the exact predicate form the migration deliberately replaces with `nfl_week_id IN (...)`. Regenerate it inside the item-4 commit. The two untracked result-equivalence fixtures are well-formed and worth keeping either way: oracles derived from the seed rather than from output, distinct values per week, isolated on `primary_position: 'MLB'`. One judgment call left open. The player path added a `DISTINCT ON (pid, year, week)` dedup that the team path does not have. The duplicate-market fanout risk is unchanged by this migration rather than introduced by it, so leaving it is consistent with item 4 being scoped to the week half — but that is an untested assumption about the team tables, not a verified one. Nothing here was executed. See the environment note below. ## Environment: the league suite does not run from base-container Sessions in `base-container` cannot verify any of this work, and the failure is not obvious until you try: - No `docker` binary and no docker socket, so `yarn test:db:up` cannot start the Postgres 16 test container, and nothing is listening on `:5433`. - `repository/active/league/node_modules` is a symlink to `/Users/trashman/node-modules/league/node_modules`, a HOST path. Only `/Users/trashman/user-base` is bind-mounted, so the target does not exist container-side and `yarn` dies with `Couldn't find the node_modules state file`. - The `base` CLI is itself broken here (unresolvable `js-yaml` and `nanocurrency-web` imports, and the hostname matches no machine-registry entry), so `base entity observe` and `base thread create` are unavailable too. Items 4 through 7 all require executing fixtures red-first. Do them from a session on the macbook host. ## Out of scope - `year_offset` on any week-scoped source. Unsupported today; the helper should refuse an offset it cannot honor rather than silently returning base-year values, but that is its own decision. - Adding `requires_week` to the betting sources. It would refuse a season-long view carrying a prop column, which ships today.