---
title: Reconstruct team attribution for the JAX home games 2001-2015
type: task
status: in_progress
description: >-
  150 JAX home games attribute nearly every play to the visitor: roughly 11,300 nfl_plays rows and
  42,000 nfl_play_stats rows. The value is lost rather than transposed, so it is reconstructed from
  the surviving player-identity columns.
base_uri: user:task/league/repair-jax-home-game-possession-attribution.md
created_at: '2026-09-10T23:37:46.138Z'
entity_id: 106d7c7d-5e02-4b62-af68-bc87ad1bf818
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: High
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-11T14:18:55.493Z'
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-10-10T01:51:22.942Z'
visibility_analyzed_at: '2026-11-05T23:50:06.564Z'
---

# Implementation Plan: Reconstruct team attribution for the JAX home games 2001-2015

## Overview

- 150 games attribute nearly every play to the visiting team. Roughly 11,300 plays in `nfl_plays` and 42,000 rows in `nfl_play_stats` name the wrong team, silently.
- The original value is LOST rather than transposed, so this is reconstruction from an independent oracle, not a flip. The oracle exists: the player-identity columns in the same rows survived intact.
- Impact: fifteen seasons of Jacksonville offensive production currently sit under their opponents. Every team stat, snap count, gamelog and published export over those games is wrong, and no query comparing the three team columns can see it because they agree with each other.
- Ordering: follows the constraint task, so the repair cannot be re-minted by the next import. Precedes the re-export.

## Background

Measured against `league_production` on 2026-09-10.

- Enumerated from the signature, not the name: per-game possession share computed across every game in every season, then filtered to extreme skew. 150 games qualify at a 0.25 home-share cutoff, every one a JAX home game between 2001 and 2015, and zero games elsewhere in any season. The class is real and closed. The earlier reading of 146 used a 0.15 cutoff and dropped four 2002 games; see Assumptions.
- Those games carry 24,881 plays with a non-null possession, of which 916 (3.68%) name JAX. The expected share is roughly half.
- **The value is a constant, not an inversion.** A clean transposition would flip to roughly 50%; 3.68% cannot. Those 916 are the surviving true rows, and the rest were overwritten with the visiting team. There is nothing to invert, so a reconstruction is required.
- The three team columns AGREE with one another on these rows — `offense_nfl_team` equals `possession_nfl_team` on 100% of them and defense is the other team. That agreement is why the `defense_nfl_team = possession_nfl_team` signature returns zero here and why this population is invisible to the data check.
- **The oracle survived.** On a sample from esbid 2015102500, `passer_pid` resolves to `BLAK-BORT-009445` (Blake Bortles) on plays whose `possession_nfl_team` reads BUF. The player-identity columns were not corrupted. Coverage across the 150 games, re-measured 2026-09-10: 26,449 plays total, 24,881 with a non-null possession, and 16,407 (62.0%) carrying `passer_pid` or `ball_carrier_pid`. `play_description` is 100% populated.
- **`nfl_play_stats` carries the same corruption and is NOT an independent oracle.** 84,952 rows in those games hold a non-null `nfl_team`, of which 139 (0.16%) name JAX. It must be repaired too, not consulted.
- `player_gamelogs` reads 18.7% JAX over 13,934 rows in those games — depressed but not flattened, so it is partially affected and needs its own measurement before anything is done to it.
- The two figures above, for `nfl_play_stats` and `player_gamelogs`, were measured over the 146-game set and have NOT been re-measured at 150. Re-run both against the corrected population before designing on them.
- The 2026-07-25 root-cause note on `user:repository/active/league/ABOUT.md` rules out an abbreviation drift because possession reads JAX consistently. It does not. That note is corrected by the consumer-reconciliation task.

### Assumptions

- JAX AWAY games in the same seasons are clean and can anchor which players belong to JAX per season. Checked: the per-game skew scan flags only home games; no JAX away game qualifies in any season.
- Possession is constant within a drive, so identifying one play in a drive settles the drive. Standard football, but the DATA dependency is `drive_sequence` population. CHECKED 2026-09-10 and the fork resolves to DRIVE PROPAGATION: `drive_sequence` is populated on 26,396 of 26,449 plays in the affected games, a 99.80% fill rate, with the worst single game at 98.24% and no game below 90%. Of 3,493 drives, 3,093 carry at least one play with a `passer_pid` or `ball_carrier_pid`. So drive propagation lifts coverage from 62.0% per-play identity to 23,651 of 26,396 plays, or 89.6%, and the `play_description` fallback is left with the 400 identity-free drives rather than with 38% of the table.
- `passer_pid` and `ball_carrier_pid` are correct on these rows. CHECKED 2026-09-11 and widened from one game to the whole population, and it HOLDS with a stated error rate. Over all 8,983 affected-game rows carrying a `passer_pid`, 99.84% resolve to a player named in `play_description`. The control is what makes that a reading rather than a hope: the same check against a deliberately shifted pid reports 72.30%, so the two differ decisively. The 14 failures sit in 2 games in 2003 across 3 drives, and at least one is a defensive lineman (Kindal Moorehead) filed as the passer on B.Leftwich plays — which is exactly the shape the intra-drive contradiction check is there to catch.
- The 2001-2015 boundary is a property of the source and not of the measurement threshold. CHECKED 2026-09-10 and PARTLY REFUTED. The season boundary holds — every qualifying game is a JAX home game between 2001 and 2015 at every cutoff up to 0.30. **The game COUNT does not hold: the class is 150 games, not 146.** The 146 was an artifact of the 0.15 cutoff. Sweeping the home-possession-share cutoff gives 126 games at 0.05, 145 at 0.10, 146 at 0.15, and 150 at 0.20, 0.25 and 0.30, with the first non-JAX game appearing only at 0.35. The four games the 146 misses are all JAX 2002 REG — esbids 2002120806 (CLE), 2002102709 (HOU), 2002111005 (WAS), 2002122203 (TEN) — reading home shares of 0.1615 to 0.1975.
- The class is nonetheless CLOSED, and there is a clean empty band to define it by: the highest affected game reads 0.1975 and the next game of any kind reads 0.3082 (LV/ATL 2008, an ordinary blowout). Define the population at a 0.25 cutoff, which sits in the gap, rather than at 0.15. Every count downstream moves with it: 150 games, 26,449 plays, 24,881 with a non-null possession.
- Downstream derived tables can be regenerated from repaired plays rather than repaired in place. UNCHECKED — depends on each generator being re-runnable for a historical season.

## Design

**Reconstruct per drive, from player identity, anchored on clean games.**

- **Anchor rosters.** Build a per-season JAX player set from games that are NOT in the affected set — JAX away games and every other team's games. This never consults the corrupted population, which is what makes it an independent anchor.
- **Classify each play that has an identified player.** For a play carrying `passer_pid` or `ball_carrier_pid`, the player belongs to one of the game's two teams. That settles the play.
- **Propagate across the drive.** Possession is constant within a drive, so a drive with any classified play is fully determined. This lifts roughly 63% per-play coverage toward near-total, and it is also a consistency check: two plays in one drive that classify to different teams is a contradiction and must be reported rather than resolved by majority.
- **Fall back to `play_description`.** It is 100% populated and is what confirmed the defect originally. Used for drives no identified player reaches.
- **Leave the unresolvable alone.** Any play or drive the oracle cannot settle confidently keeps its current value and is listed. A guessed team is worse than a known-bad one, because it is no longer detectable.

**Repair `nfl_plays`, then `nfl_play_stats`, then regenerate downstream.** The play-stat rows inherit their attribution from the play, so they follow rather than lead.

**The validation is the possession share.** Post-repair, per-game JAX possession share should land near 0.5 across all 150 games. That is a population-level oracle that a spot check cannot substitute for: a repair that fixed one game and missed 149 passes any sample of the game it fixed.

### Knowledge Base Impact

- Scratch slug `scratch/league/jax-possession-reconstruction/` — per-game reconstruction reports, the unresolvable-play list, and before/after possession-share measurements. Owned by this task, retired via the promotion contract at close.
- `user:text/league/` — whether fifteen seasons of a franchise's plays were wrong is a fact about the dataset that outlives this task; record the corrected state and the window, not the investigation.
- The published dataset correction is owned by the re-export task, not this one.

## Notes

- Measure `drive_sequence` fill rate FIRST. It decides whether the design is drive-propagation or per-play, and everything downstream depends on which.
- Widen the `passer_pid` correctness check beyond the single game it was sampled on before building anything on it.
- Do not repair `player_gamelogs` in place. Regenerate it, or leave it and record why.
- This task changes roughly 11,300 plays. It carries the same operator gate as the first repair in this chain: report the reconstruction's coverage, its contradiction count, and a description-verified sample before applying to production.

## Plan

### Measurement and oracle validation

- [x] **Bootstrap the scratch slug** — created at `scratch/league/jax-possession-reconstruction/ABOUT.md`, owned by this task, carrying the state of the reconstruction and the two things the next session must handle. `classify-and-propagate.sql` sits beside it.

- [x] **Confirm the game set is threshold-independent** — run 2026-09-10 and it is NOT. See Assumptions: the class is 150 games at a 0.25 cutoff, four more than the inherited 146. Sweep the cutoff again before building the population, and build it at 0.25.

- [x] **Measure drive_sequence fill rate inside the affected games** — run 2026-09-10. 99.80% fill, worst game 98.24%. The design fork resolves to DRIVE PROPAGATION, expected coverage 89.6% against 62.0% per-play.

- [x] **Widen the player-identity correctness check** — done 2026-09-11 over the full 8,983-row population rather than a sample: 99.84% correct, against a 72.30% shifted-pid control. See Assumptions.

### Core Implementation

- [x] **Build the anchor rosters from clean games** — built as SQL in `scratch/league/jax-possession-reconstruction/classify-and-propagate.sql`, sourced strictly from games outside the affected 150. Not yet promoted to a `scripts/` entry point, which is the right shape only once the `play_description` fallback joins it.

- [x] **Classify and propagate** — all three tiers built; the `play_description` fallback landed as `scratch/league/jax-possession-reconstruction/classify-with-description-fallback.sql` and the applied repair was built from it (league@7a8f16d16). Figures below are the pid-and-drive tiers alone, before the fallback.
  - Classification reaches 16,300 of 16,407 actor-carrying plays (99.35%). Drive propagation settles 23,602 of the 26,396 plays in drives (89.4%).
  - **Contradictions: zero**, and the detector was not idle — 2,885 of the 3,086 verdict drives carry two or more independently classified plays, and 1,520 carry five or more.
  - Projected per-game JAX possession share: mean 0.4550, 131 of 150 games inside 0.35-0.65, against 0.0368 today.
  - **The 15 remaining games were the fallback's job**, and it settled them (zero games below 0.25 after apply). Ten are 2001 games that reconstruct at exactly 0.0% because they carry ZERO `passer_pid` and `ball_carrier_pid` rows; `play_description` is 100% populated on them. The other five are 2002 games reconstructed at roughly 50%.

- [x] **Author the nfl_plays repair** — applied 2026-09-11 as `db/adhoc/2026-09-11-reconstruct-jax-home-game-team-attribution.sql`. 12,774 plays across the 150 games; per-game JAX possession share moved from 0.0368 to 0.5008, range 0.3486 to 0.6138, zero games below 0.25.

- [x] **Repair nfl_play_stats attribution** — applied 2026-09-11 as `db/adhoc/2026-09-11-reconstruct-jax-home-game-play-stat-teams.sql`. 36,101 of 87,031 rows; JAX share moved from 0.44% to 41.92%.

- [x] **Regenerate downstream derived tables** — `player_gamelogs` regenerated 2026-09-11 across all 150 affected games, scoped per `--esbid` rather than per week.
  - Expected value stated in advance: a share near 0.50, mirroring the 0.5008 the plays repair reached. Reading: **0.1963 to 0.4931** over 15,077 rows, per-game range 0.406 to 0.580, games below a 0.25 share 134 to 0, and zero games left with no rows.
  - The prune deleted 17 rows in total across the run and skipped one game as unseparable, both within its documented bounds.
  - Driver and log at `scratch/league/jax-gamelog-regeneration/`.

### Knowledge Base Updates

- [x] **Record the corrected dataset state** — recorded 2026-09-11 as a "Team attribution" section in [[user:text/league/data-quality-and-validation.md]], rather than as a new entity: that doc is already the single entry point for dataset-level data-quality facts. It states the window, the size, the before-and-after shares, the fact that all eight team columns were affected, the replacement definition for a population whose signature is spent, and the two downstream surfaces that are still uncorrected.

- [~] **Run the scratch promotion contract** — CLASSIFIED 2026-09-11, execution deferred and the deferral recorded in the slug's `ABOUT.md`. All four files have a fork assigned. It does not run yet for the reason the contract itself names: this task is still open, and `affected-game-esbids.txt` is a live input for any regeneration, because the possession-skew signature that defined the 150 games is spent. Trigger is this task reaching Completed.
  - Verify: each durable artifact promoted, archived or deleted per the contract; the slug is empty or gone.

### Testing & Validation

- [x] **Population-level validation across all 150 games** — run 2026-09-11 over both surfaces, per-game rather than in aggregate.
  - `nfl_plays`: zero rows with offense equal to defense across the whole table, all 27 seasons, not just the repaired window.
  - `player_gamelogs`: all 150 games measured individually, share range 0.406 to 0.580, none below 0.25, none at zero rows.
  - Failure mode this catches: an aggregate share near 0.50 is also what a repair that fixed half the games perfectly and left the other half untouched would read. The per-game range is what distinguishes them, and a game emptied of rows would read as a clean zero-defect game, which is why the zero-row count is asserted beside the share.

- [ ] **Description-verified sample, spanning seasons**
  - Purpose: `play_description` player identity is what confirmed the defect and is the only fully independent oracle.
  - Verify: a randomised sample across multiple seasons where the repaired team matches the players named in the description.

- [x] **Confirm the existing data check still reads clean** — run 2026-09-11. All four arms of `plays-team-attribution-self-consistency` read **zero violations over 1,386,985 rows across 27 seasons**.
  - Note what a green here does and does not mean, per the check's own calibration: three of its four arms are now enforced as DDL, so on those it detects a dropped constraint rather than a bad row. And it is structurally blind to this task's population — a game where possession itself names the wrong team is internally consistent on all four arms. The oracle for the repair is the per-game share above, not this check.