--- title: Diagnose the single-week team flips in player_gamelogs type: task status: draft description: >- Investigation brief for the single-week team transpositions in player_gamelogs (2023: 310, 2024: 197, 2025: 0) that make a betting column select the opposing side. Promoted from a scratch brief whose session is gone; confirm relevance first, since the related repair tasks are Completed. base_uri: user:task/league/diagnose-gamelog-team-flips.md created_at: '2026-10-14T21:36:20.629Z' entity_id: 9b07560d-420b-45d5-9fc8-8267a2ef4086 owner_identity_uri: █████████████████████████ priority: Medium public_read: true relations: - relates [[user:task/league/fix-historical-gamelog-team-data-inconsistency.md]] - relates [[███████████████████████-████████-███████-████-████████]] - relates [[███████████████████████-███████-████-████-██████████████]] tags: - user:tag/league-xo-football.md updated_at: '2026-10-14T21:36:20.629Z' visibility_analyzed_at: '2026-11-05T23:49:09.758Z' --- ## Plan - [ ] Confirm whether the 2023/2024 transposed rows are still present and still matter (2025 already reads zero flips); if they are already repaired, retire this task. - [ ] Otherwise work the diagnosis brief below: establish the transposition cause and the affected player-weeks. - [ ] Deliver the repair or the backfill the brief calls for, with the four deliverables it names. ## Context Promoted 2026-10-14 from `scratch/league/diagnose-gamelog-team-flips/brief.md` (investigation brief; the owning session is gone, and no analysis output was ever produced). Status is Draft because the three related repair tasks are Completed and 2025 already reports zero flips, so relevance must be confirmed before the work is run. Repository: `repository/active/league`. Production is reachable read-only through the `mcp__db__query_league` MCP tool. ## The observation A player sits on team A all season, then exactly ONE week reads team B, then returns to A. Counts over REG: - 2023: **310** flips - 2024: **197** flips - 2025: **ZERO** In 99% of them — 307 of 310, and 194 of 197 — `player_gamelogs.opponent_nfl_team` holds the team he is on in every surrounding week. The two columns read as though they were transposed. Worked example: `AARO-BANK-005736`, 2024 week 12. The game is Green Bay against San Francisco. He is a San Francisco player in every other week of the season. His row says `nfl_team = GB, opponent_nfl_team = SF`. ## Why it matters A data-view betting column picks which side of a spread to render by matching the player's week team, so a transposed row selects the OPPOSING side — roughly 500 player-weeks across 2023 and 2024. It also inflates the apparent count of mid-season trades in those seasons: most of what reads as a trade is this artifact. ## What has already been ruled out — do not redo this - `nfl_plays_player.is_offense_play` and `is_defense_play` are NULL on these rows, so the obvious side-of-ball oracle is empty exactly where it is needed. - The stat-role pids (`ball_carrier_pid`, `target_pid`, `passer_pid`) locate only **2 of 507** flipped player-weeks. Both of those AGREE with the gamelog `nfl_team`, which points AWAY from the transposition theory. n=2 is not a finding in either direction. - `player_gamelogs` is exactly one row per `(pid, year, week)` across 2023-2025 — zero duplicates, so this is not a fan-out. **Nobody has proven the direction of the error.** Do not assert which column is wrong without evidence you can show. A confident wrong answer here would drive a backfill that corrupts 500 rows which are currently only suspect. ## What the operator wants from you Root cause, in the generator. Their words: game logs are **mainly derived and generated**, so the fault should be findable in the code that writes them rather than being irreducible source noise. That the flips stop dead at 2025 suggests a generator or an upstream feed that changed — find what changed and when. Entry points to start from, not an exhaustive list: - `scripts/generate-player-gamelogs.mjs` - `scripts/process-stats-for-week.mjs` - `scripts/audit-player-gamelogs.mjs` (may already encode expectations worth reading) - whatever writes `nfl_team` / `opponent_nfl_team` on the insert path, including any import script under `scripts/` or `jobs/` Useful angles: does the writer derive the team from the game's home/away pair and pick a side by some rule that can inverse? Is there a fallback that fires when a player's roster team is unknown for that week? Does `git log` on the writer show a change landing between the 2024 and 2025 seasons? Are the flips clustered by source, by game, by week, or by a particular import run? Deliverables, in your response and your thread timeline: 1. The mechanism, named in code, with the line that produces the inversion — or a clear statement that you could not find one and what you ruled out. 2. Evidence for WHICH column is wrong, or an explicit "still unproven" with the best available discriminator named. An outside source (a published box score or roster for a handful of the flipped weeks) is legitimate evidence if you can reach one. 3. Whether the defect is still live in the writer today or was fixed by something that landed before the 2025 season. 4. What a repair would look like and what it would touch — do NOT run one. ## Constraints - **Do not write a backfill, and do not modify production data.** Diagnosis only. A repair is an operator decision that has not been made. - Do not create entities to hold your analysis, and do not open GitHub issues. Report in your response; the thread timeline is the durable record. If you find a bug worth memorializing, an observation on an existing local entity is the route — ask first. - The working tree is shared with a dozen live sessions. Attribute uncommitted changes before acting on them and never revert work you did not author. If you commit anything, use an explicit pathspec and check `git status` for that path first. - Tests: `yarn test:isolated `, on this host only, never from a worktree. Read `docs/guides/test.md` first — it documents four ways the suite hangs rather than failing. - Read `docs/guides/data.md` before touching a data-view golden and `docs/guides/scripts.md` before touching anything under `scripts/` or `jobs/`. - Never put a secret value as a literal in any command. ## Related in-flight work I am `█████████████████████████████████████████`, working `█████████████████████████████████████████████████████████████████████████████████`. I own the data-view team-attribution path — `libs-server/data-views/team-stats-from-plays-wrap.mjs`, `add-team-stats-play-by-play-with-statement.mjs`, the `player_week_teams` bridge, and `team-stats-from-plays-column-definitions.mjs`. Message me before editing any of those. The consuming column is fine as written; it faithfully renders whatever the gamelog says, which is why this is a data question and not a data-view one. Two other sessions are live on adjacent ground: one owns the betting `selection_metric_line` row axis, and one owns live-market contamination in the betting dedup. Neither touches the gamelog writers. --- Dispatched by: █████████████████████████████████████████ ## Key locations - Source brief: `scratch/league/diagnose-gamelog-team-flips/brief.md` (being retired; content is above). - Related, all Completed: `task/league/fix-historical-gamelog-team-data-inconsistency.md`, `█████████████████████████████████████████████████`, `███████████████████████████████████████████████████` - Session `diagnose-gamelog-team-flips`, thread `86cfc397`; dispatched by `repl-33d1b74a`.