--- title: Reconcile the nfl_plays team-column consumers on one canonical column type: task status: completed description: >- Settle the data-view fact source and the plays view on offense_nfl_team, document the kickoff possession inversion on the column comments, and measure the unattributed 2024 kickoff anomaly. base_uri: user:task/league/reconcile-plays-team-column-consumers.md created_at: '2026-09-10T23:36:41.438Z' entity_id: 7034edc5-04ca-4cac-be0e-02df275e812d finished_at: '2026-09-11T02:33:14.210Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: Medium public_read: true relations: - follows [[user:task/league/enforce-plays-team-attribution-invariants.md]] - relates [[user:task/league/repair-plays-offense-defense-swap.md]] started_at: '2026-09-11T02:38:26.358Z' tags: - user:tag/league-xo-football.md - user:tag/software-task.md updated_at: '2026-09-11T23:11:53.416Z' visibility_analyzed_at: '2026-11-05T23:49:56.435Z' --- # Implementation Plan: Reconcile the nfl_plays team-column consumers on one canonical column ## Overview - Two shipped surfaces group team facts by different columns and disagree with each other on any divergent row. Settle them on the column the schema already declares canonical, and document the kickoff convention whose absence is why a whole defect class stayed invisible. - Impact: data-view team rows and plays-view team rows stop being able to disagree. A session reading the column comments learns the kickoff inversion instead of discovering it from a corrupted import. - Ordering: follows the constraint work, and the ordering is load-bearing rather than tidy — see below. ## Background Measured against `league_production` on 2026-09-10. - `libs-server/data-views/measure/fact-source-registry.mjs:74` sets `FACT_SOURCES.plays.team_code_column = 'possession_nfl_team'`. Every team-subject data-view column over the plays source groups by possession. - `libs-server/plays-view/get-plays-view-results.mjs:38` sets `GROUP_BY_COLUMNS.team = ['nfl_plays.offense_nfl_team']`, and `libs-server/data-views/add-team-stats-play-by-play-with-statement.mjs:98` groups by the `TEAM_UNIT_COLUMN` map, which resolves to `offense_nfl_team` for the offensive unit. - The schema comment added by `db/adhoc/2026-09-10-comment-nfl-plays-team-columns.sql` declares `offense_nfl_team` CANONICAL for offensive-team joins. The code does not follow it. - `libs-shared/plays-view-columns.mjs:99-108` carries a comment recording a PRIOR instance of exactly this class, where `play_off_team` read possession while its sibling read defense and the two disagreed on `NOPL` markers. This is the same defect one layer up. - The kickoff convention is documented nowhere on the columns: `possession_nfl_team` is the KICKING team here, while nflfastr calls the receiving team the possessing team. `scripts/import-plays-nflfastr.mjs:70-74` compensates by swapping which vendor field it reads. `libs-server/resolve-snap-gamelog-team.mjs:57` records the consequence in passing. That inversion is why the vendor `defteam` defect spared kickoffs and therefore why it read as an importer that was working. - An unattributed anomaly, folded in here as a measurement rather than a separate task: on `KOFF` plays, `possession_nfl_team` matches `yard_line_side` at 1.000 in every season except 2024, which reads 0.954 (156 rows), plus small dips in 2013 and 2014. `scripts/import-plays-sportradar.mjs` has no kickoff possession swap while nfl-v1 and nflfastr both do, which is a candidate cause and not yet evidence. - Separately surfaced and probably out of scope: 2002 has zero `KOFF` rows carrying both fields and 2003 has 112, against roughly 3,200 in every other season. A coverage gap, not an attribution defect. ### Assumptions - After the constraint task lands, `offense_nfl_team` and `possession_nfl_team` can differ ONLY where possession is NULL, which is the `NOPL` administrative markers. Checked by construction: the offense-matches-possession constraint is violated by any other divergence. - `NOPL` administrative markers do not belong in team aggregates, so moving the data-view fact source from possession to offense changes no legitimate team row. CHECKED 2026-09-11 and **REFUTED. The gate fired and the move must not be made.** The first half holds and the second does not: `NOPL` markers indeed do not belong in team aggregates, and moving the fact source to `offense_nfl_team` is precisely what would PUT them there. - Measured by grouping every play both ways: 85 of 839 team-season rows differ, by 13,056 plays. No team row appears or disappears; 85 of them simply gain plays. - Those 13,056 are exactly the rows carrying an offense with a NULL possession — all `play_type` NOPL, confined to 2023 through 2025, none in any of the other 24 seasons. - **Every one is a TIMEOUT**, and the offense value is the team that had the ball on the preceding play, carried over. It is not a reading about the timeout: esbid 2024110307 play 3896 reads offense WAS on a row whose description is "Timeout #1 by NYG at 01:46". - So grouping by possession drops them out of team aggregates, which is correct, and the data view is already on the right column. It is the PLAYS VIEW — already grouping by `offense_nfl_team` — that counts 13,056 timeouts as team plays, and only for three seasons, which also makes its team totals non-comparable across time. - The 2024 kickoff anomaly is a data question answerable by query without changing code. Checked: the measurement above ran read-only. - `db/adhoc/2026-09-10-comment-nfl-plays-team-columns.sql` is spent and must not be edited; a correction is a new dated file. Checked: `db/adhoc/README.md` states the rule and `db:exec` refuses an applied banner. ## Design **Sequencing is the design.** Before the constraints land, the two columns can diverge arbitrarily and switching the fact source would silently move real team rows between buckets. After they land, divergence is confined to rows with a NULL possession, which no team aggregate should include. The same edit is materially risky in one order and near-inert in the other, so this task follows the constraint task rather than running beside it. **One canonical column — NOT AS PLANNED, and the diff is why.** The design said `fact-source-registry.mjs` moves to `offense_nfl_team` to match the plays view, the team-stats CTE and the schema comment. The before/after diff refused it: that move files 13,056 timeouts under real teams. The schema comment is still right that `offense_nfl_team` is canonical for an offensive-team JOIN, and that is a different question from which column a team AGGREGATE should group by — a `NOPL` marker has an offense and is not a play any team ran. So the surfaces are reconciled the other way or not at all, and which is an operator call. This is exactly the case the plan's own note anticipated: "a team row that moves is either a `NOPL` marker leaking into an aggregate, which is a finding, or the assumption is wrong." It is the first, so the finding is reported rather than proceeded past. **Document the convention that hid the defect.** A new dated adhoc file rewrites the three column comments to add the kickoff inversion, and to drop the sentence saying that where the two columns disagree possession is the correct side — true when it was written, false once the repair lands, and exactly the kind of transient data claim that should never have been in a column definition. **Measure the 2024 kickoff anomaly, then decide.** It gets a query and a finding recorded on this task, not a speculative fix. If it turns out to be the sportradar importer's missing kickoff swap, that becomes its own task with its own repair; if it is something else, the measurement says so. ### Knowledge Base Impact - New dated adhoc file carrying corrected column comments; the 2026-09-10 file stays untouched as history. - `docs/guides/data.md` — which column a team-subject surface groups by, and why there is only one right answer. - `user:repository/active/league/ABOUT.md` — the 2026-07-25 root-cause note asserts the JAX defect is not an abbreviation drift because possession reads JAX consistently. It does not read JAX consistently. Correct the note here, since this is the documentation task in the chain. - No new tag. No scratch slug. ## Notes - The before/after data-view diff is the gate. A team row that moves is either a `NOPL` marker leaking into an aggregate, which is a finding, or the assumption is wrong. - Do not fix the 2024 anomaly in this task even if the cause is obvious. Measure it, record it, and let it be scoped properly. ## Plan ### Core Implementation - [ ] **BLOCKED ON AN OPERATOR DECISION — do not move the plays fact source** - File: `libs-server/data-views/measure/fact-source-registry.mjs` (UNCHANGED) - The gate ran 2026-09-11 and fired. See Assumptions: the move would file 13,056 timeouts under real teams in three seasons. The data view is already on the correct column. - The real defect is the opposite one, and it needs a decision before anyone writes code. Two candidates, different blast radii: 1. **Narrow the plays view** to exclude `play_type = 'NOPL'` from team grouping. Smallest change; leaves a possessionless row carrying an offense, so the next consumer can make the same mistake. 2. **Stop the writer** that sets `offense_nfl_team` on a row with no possession, and clear the 13,056. Addresses the cause; touches the enrichment contract and needs its own repair file. - Until that is settled, the disagreement is DOCUMENTED rather than resolved — `docs/guides/data.md` carries the measurement and the warning. - **RESOLVED-BY [[████████████████████████████-█████-████-███████-████-█████████████]], 2026-09-11 — and by candidate 2, not candidate 1.** The writer was stopped and the 13,056 possessionless rows cleared, which is what made the two columns provably equal; `nfl_plays_offense_matches_possession` was then validated across NULLs, and on that licence `offense_nfl_team` was DROPPED outright in league `cb38ff53f`. The decision this item was blocked on is therefore moot rather than answered: there is no second column to move the fact source to. The plays view and the fact source read `possession_nfl_team` by construction, so the disagreement cannot recur. - The box stays unticked because this task did not do the work; it is recorded here so the reasoning is not lost. - [ ] **Assert the two surfaces agree, as a spec rather than a one-time check** - Deferred with the item above: a spec pinning agreement cannot be written while the two surfaces are known to disagree and the correct bucket is undecided. It lands with whichever fix is chosen. - **REASSIGNED, not resolved.** It is carried as "Pin the team-surface agreement as a spec" in [[████████████████████████████-█████-████-███████-████-█████████████]] and is still OPEN there. The shape changed with the consolidation: after the drop there is one column, so the spec pins that both surfaces read `possession_nfl_team` rather than pinning an equality between two. - [x] **Measure the 2024 kickoff possession anomaly** — run 2026-09-11, and the writer IS attributable. - Reproduced: 2024 reads 0.9535 on `KOFF` rows carrying both fields, 156 mismatches of 3,355, against 1.0000 in every full season except small dips in 2008, 2013, 2014 and 2025. - **All 156 are PRESEASON**, weeks 2 and 3 only, across 17 games. Zero in REG or POST. - The direction is decisive and `play_description` settles it: esbid 2024082552 reads "W.Lutz kicks 65 yards from DEN 35" with `yard_line_side` DEN and `possession_nfl_team` ARI. Possession names the RECEIVING team, against a database convention of the KICKING team. `yard_line_side` is right; possession is wrong. - That is the missing kickoff swap in `scripts/import-plays-sportradar.mjs`, which the plan named as a candidate. Its own `KOFF` comment at line 590 states the convention BACKWARDS — "Database uses receiving team as off, Sportradar uses kicking team" — which is plausibly why the swap was never added. - Because offense is derived from possession, these 156 rows also attribute the kickoff to the receiving team as offense. NOT repaired here, per this task's own instruction; it needs its own task with its own repair file and a fix to that comment. ### Knowledge Base Updates - [x] **Correct and extend the column comments** — applied 2026-09-11 as `db/adhoc/2026-09-11-comment-plays-team-columns-kickoff-convention.sql`. All three comments now carry the kickoff inversion, the retired "where the two disagree, possession is correct" sentence is gone, the JAX caveat is kept, and the NOPL description is corrected to what the rows actually are (timeouts carrying the previous play's offense). The post-condition asserts all three arms and would raise on a rewrite that addressed the wrong column. - File: `db/adhoc/2026-09-11-comment-plays-team-columns-kickoff-convention.sql` - Purpose: the kickoff inversion is the subtlest property of these columns and is currently written down nowhere on them; and the existing comment carries a claim that the repair makes false. - Changes: add the kickoff convention to all three comments; remove the "where the two disagree, possession is correct" sentence; keep the JAX caveat, which stays true until that repair lands. - Verify: post-condition asserting all three comments are present and non-empty, as the 2026-09-10 file does; then read the comments back from `pg_attribute` and confirm the kickoff sentence is present and the stale sentence is gone. - [x] **Record which column a team surface groups by** — `docs/guides/data.md` now carries two sections: the single-writer rule for the derived pair, and the measured DISAGREEMENT between the two team surfaces with the 13,056-timeout finding and an explicit instruction not to repoint either surface before the meaning of a timeout row is decided. Documenting the open disagreement is the honest version while the fix is undecided; a guide asserting a rule the code does not follow is what got this task filed in the first place. - [x] **Correct the refuted root-cause note** — done. A 2026-09-10 `[correction]` observation already refuted the possession-reads-JAX-consistently premise; a 2026-09-11 one adds that the affected set is 150 games rather than the 144 that note recorded. The original text is left in place, per this entity's append-only observation convention. - File: `user:repository/active/league/ABOUT.md` - Purpose: the 2026-07-25 note rules out an abbreviation drift on a premise that measurement refutes, and it is the note a future session would inherit. - Verify: the corrected text names the measurement that refutes it; grep the file for the old claim and confirm it is gone. ### Testing & Validation - [ ] **Population-level validation that no team row moved** - Purpose: distinguishes "the surfaces now agree" from "the surfaces now agree because both are empty". - Verify: per-season team-row counts and a sample of team totals before and after, required to be identical and non-zero across every season carrying plays. State the failure mode: a silently emptied bucket reads as agreement. - **RUN 2026-09-11 under [[████████████████████████████-█████-████-███████-████-█████████████]], after the drop: 839 team-season rows over 27 seasons and 1,386,985 plays, zero empty buckets, smallest season-team bucket 73.** Identical to the 839 measured before the drop, so no bucket moved. The non-zero floor is asserted beside the equality precisely because an emptied bucket reads as agreement. - [ ] **Full suite** - Verify: `yarn test:isolated`, `yarn lint`, `yarn check:cluster` green. - **RUN 2026-09-11 on the landed change: 8326 passing / 0 failing with a summary line present, `yarn lint` 0 errors, `yarn verify` 18 checks exit 0.** Run from a worktree at HEAD rather than the shared tree, which carried a sibling's uncommitted edits at the time.