--- title: Redesign role-attribution ownership on nfl_plays type: task status: completed description: >- Make play-enrichment the sole writer for _{gsis,pid} columns on nfl_plays where play_stats exists, with per-family gating and NULL-clear semantics. Eliminates stale X-to-Y reattribution drift. base_uri: user:task/league/redesign-role-attribution-ownership.md created_at: '2026-05-24T23:49:07.128Z' entity_id: 8a87d91c-9b5d-4e89-beeb-58b5589209c6 finished_at: '2026-05-25T01:24:19.664Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: Medium public_read: true relations: - addresses [[user:text/league/data-quality-and-validation.md]] started_at: '2026-05-25T00:09:58.390Z' tags: - user:tag/league-xo-football.md updated_at: '2026-05-25T19:22:48.041Z' visibility_analyzed_at: '2026-11-05T23:49:57.515Z' --- ## Overview Make play-enrichment the sole authoritative writer for `_{gsis,pid}` columns on `nfl_plays` rows where `nfl_play_stats` exists. Per-role-family gating: when a play has play_stats, the family is "owned" — enrichment writes new values or NULL-clears stale ones; when no play_stats exist (live-game window), enrichment is a no-op for that family. Eliminates stale-attribution drift when the NFL retroactively reattributes a statId from player X to player Y. Today the X-side delta sticks because `compute_play_changes` hard-skips NULL writes and enrichment falls back to existing `_gsis` on the play row. Expected outcome: zero `_gsis NOT NULL AND _pid IS NULL` residual on backfilled seasons; zero stale role attribution after upstream reattribution; cross-source counters return to 0 after re-enrichment without `--overwrite-existing`. ## Background ### The bug class The clear-on-stale path is broken at three layers, not one: 1. **`compute_play_changes`** at `libs-server/update-play.mjs:147-149` hard-skips NULL/undefined/empty values. 2. **`build_play_update_object`** at `scripts/process-plays.mjs:48-62` uses a truthy guard (`if (enriched_play[field])`) — even if enrichment produces a NULL, it never reaches `compute_play_changes`. 3. **`ENRICHED_FIELD_NAMES`** at `scripts/process-plays.mjs:24-43` only lists `_pid` columns — moving `_gsis` writes into player-identification-enrichment without expanding this allowlist would silently drop them. The L:9595ad5f tackle fallback at `libs-server/play-enrichment/player-identification-enrichment.mjs:46-69` resolves stale `_pid` from stale `_gsis` (`gsis = play_data[gsis_field] || enriched_play[gsis_field]`) — by design for the missing-play_stats case, but it leaks into the missing-play_stats-for-this-role case too. The `map_player_field` early-return at L54 (`if (enriched_play[pid_field]) return true`) also short-circuits when the `_pid` already has a value — exactly the stale-attribution case the redesign must clear. ### X to Y reattribution scenario NFL retroactively invalidates statId 79 for X, inserts statId 82 for Y on the same play: - `solo_tackle_1_*` should clear to NULL — but stays = X. - `tackle_assist_1_*` should become Y — correctly written. - Result: stale `solo_tackle_1_gsis = X`, `solo_tackle_1_pid = X` permanently. ### Current writers of role columns 1. `scripts/import-plays-nfl-v1.mjs:526-529` — live import, merges from `getPlayFromPlayStats`. 2. `scripts/import-plays-sportradar.mjs:1184-1196` via `libs-server/sportradar/sportradar-stats-mappers.mjs` — writes psr/bc/trg/intp/penalty_player/sack/TFL/forced_fumble/fumble_recovered. By default does NOT overwrite the play_stats-overlap columns (they are NOT in `SPORTRADAR_EXCLUSIVE_FIELDS`). 3. `scripts/process-plays.mjs:158-189` via the enrichment modules — `yardage-stat-enrichment.mjs:88-96` writes `_gsis`; `player-identification-enrichment.mjs` writes `_pid` plus tackle arrays. 4. `scripts/populate-qb-pid.mjs:45-46,85-86` — direct UPDATE for `qb_pid` derived from psr_pid/bc_pid. ### Test coverage gap The enrichment, update-play, and compute_play_changes surfaces currently have zero test coverage. ### Tracking `text/league/data-quality-and-validation.md` `[tackle_fix_design_a]` (L85). Companion `[issue2_partial_tackle_fallback]` (L103) records the 9595ad5f stopgap. ## Notes - **Family-by-family rollout.** Phase A covers the tackle family. Phase B covers single-player play-stats families. Each phase deploys independently with oracle measurement between. - **Sportradar interaction.** Sportradar already yields by accident — its overlap columns are not in `SPORTRADAR_EXCLUSIVE_FIELDS`. The redesign maintains this. Sportradar-exclusive families (TFL, sack, forced_fumble, fumble_recovered) are out of scope; sportradar is the sole writer there and clear semantics are not needed. - **No flag, no orchestrator.** Rollout is per-family code edits. The `clearable_fields` parameter on `compute_play_changes` is a real new capability (NULL writes for opted-in columns), not speculative configurability. ### Family-ownership ruling Two distinct gates, both required: 1. **`has_any_play_stats`** — the play has at least one nfl_play_stats row. When false, the play is in the live-game / not-yet-imported window; enrichment is a no-op for every family. No clears. 2. **`has__stats`** — the play has at least one statId of the family's set. When true, enrichment writes the new value. When false but `has_any_play_stats` is true, the family is owned and stale columns clear to NULL. This was the explicit ruling for the "play has stats, but not for this family" case: clear. The family stat profile is the upstream's complete statement about who occupied that role; absence of statIds means absence of the role. **REVISED 2026-08-22 by `████████████████████████████████████████████████`, which shipped a THIRD state.** The ruling above is an inference about feed completeness, and it broke every time the feed changed shape — the psr and trg statId sets were each widened by one observed symptom four separate times before anyone questioned the inference itself. Where it broke worst was NOPL and CONV: a real pass is thrown, the `play_description` names the passer, and the stat ledger books it outside the passing families, so "clear" deleted roughly five thousand attributions at every game finalize. Gate 2 now reads: when the family's statIds are absent AND the play row already holds a `_gsis`, RESOLVE it to a `_pid` and preserve both, rather than clearing. Clearing survives only where there is nothing to preserve. The state is additive — it never writes NULL over an existing value. It does NOT apply when the family's statIds are present but carry no gsisId; there the feed spoke and named nobody, which is a real retraction. Tackle-slot families keep the two-state behavior. Only the five single-player families carry the third state. ### Penalty family is OUT OF SCOPE `penalty_player_gsis` is NOT produced by `getPlayFromPlayStats` — case 93 at `libs-shared/get-play-from-play-stats.mjs:452-454` is empty. The column is written by `scripts/import-plays-nflfastr.mjs:412` (`play.penalty_player_id`) and `libs-server/sportradar/sportradar-stats-mappers.mjs:356`. The current enrichment for `penalty_player_pid` reads from `enriched_play[gsis_field]` via the L52 OR-fallback — there is no play_stats-derived source. Penalty cannot follow the family-ownership pattern as designed. Removing the OR-fallback would break penalty `_pid` resolution entirely. Penalty is excluded from Phase B owned families. If penalty ownership is needed later, the path forward is to first extend `getPlayFromPlayStats` case 93 to populate `penalty_player_gsis` from the statId 93 row (requires verifying upstream data carries the gsisId on that statId), then a future Phase C can add penalty to the owned set. ### lhs falsy-check on `update-play.mjs:167` `if (edit.lhs)` is a falsy check — `lhs = 0`, `""`, or `false` bypasses the overwrite gate. For role columns this means `lhs = ""` (empty string) would let a NULL through without recording the changelog entry. Mitigation: in `compute_play_changes`, when handling a clearable-field NULL, treat empty-string `lhs` as a non-clear (the column was already empty). Add a test case for this distinction. ### `map_tackle_array` all-null handling `map_tackle_array` at `player-identification-enrichment.mjs:80-101` filters out null pids before writing slots. Under the owned-family design, when `has_tackle_stats` is true but the resolve produces zero pids (all gsis missing player rows), the slot columns must be NULL-written (clear), not skipped. Helper needs a variant that emits explicit NULLs per slot when family is owned and no pids resolve. ## Design ### Mechanism 1: `clearable_fields` on `compute_play_changes` Extend `compute_play_changes` in `libs-server/update-play.mjs` to accept a `clearable_fields: Set` parameter. - L147 NULL-skip becomes: skip NULL unless `prop` is in `clearable_fields` AND `edit.lhs` is truthy (a non-empty existing value). Empty-string `lhs` is treated as "already empty" and produces no changelog. - L167 overwrite gate behavior is preserved; a clearable-field clear is treated like an edit and produces a changelog entry. - Default empty set preserves current behavior for all other callers (sportradar import, manual CLI). ### Mechanism 1b: NULL-aware `build_play_update_object` and expanded `ENRICHED_FIELD_NAMES` In `scripts/process-plays.mjs`: - L24-43 `ENRICHED_FIELD_NAMES` is missing every `_gsis` column. Phase B moves `_gsis` writes into player-identification-enrichment; those writes go nowhere without expanding the allowlist. Add `bc_gsis`, `psr_gsis`, `trg_gsis`, `intp_gsis`, `player_fuml_gsis`, and the tackle `_gsis` columns (`solo_tackle_{1,2,3}_gsis`, `assisted_tackle_{1,2}_gsis`, `tackle_assist_{1,2,3,4}_gsis`) in the corresponding rollout phase. `penalty_player_gsis` is intentionally omitted — see Notes > "Penalty family is OUT OF SCOPE". - L52 truthy guard (`if (enriched_play[field])`) drops NULL writes before they reach `compute_play_changes`. Change to `if (enriched_play[field] !== undefined)` so explicit `null` values flow through. Non-clearable fields with a `null` value are still no-ops downstream because `compute_play_changes` will skip them at the default NULL-skip path. The semantic contract becomes: enrichment expresses "no opinion" by omitting the field (undefined), and "clear" by setting it to null. ### Mechanism 2: per-family ownership in `player-identification-enrichment` In `libs-server/play-enrichment/player-identification-enrichment.mjs`: - Detect `has__stats` per family from `stats_for_play`: - `has_tackle_stats` = any statId in `[79, 80, 82]` - `has_bc_stats` = any statId in `[10, 11]` - `has_psr_stats` = any statId in `[14, 15, 16]` - `has_trg_stats` = any statId in `[21, 22]` - `has_intp_stats` = any statId in `[25, 26]` - `has_fuml_stats` = any statId in `[52, 53, 54, 106]` - Penalty family excluded — see Notes > "Penalty family is OUT OF SCOPE" - When `has__stats` is true, enrichment owns the family: write the new value, or write NULL (clear) when the family's stat rows resolve to no player. - When `has__stats` is false but `has_any_play_stats` is true, the family is still owned (play_stats authoritatively says no role): write NULL to clear stale columns. - When `has_any_play_stats` is false, no-op for every family (live-game window). - Remove the `map_player_field` L52 OR-fallback (`gsis = play_data[gsis_field] || enriched_play[gsis_field]`) for owned families — direct read from `play_data` only. - Remove the `map_player_field` L54 early-return (`if (enriched_play[pid_field]) return true`) for owned families. Today this short-circuit prevents the reattribution clear from landing when the play row already holds X's pid. - Remove `map_tackle_array_from_play_row` usage for owned families. - Add an owned-family variant of `map_tackle_array` that emits explicit NULL writes per slot when family is owned but the resolve produces zero pids (rather than the current early-return on empty). - Emit both `_gsis` and `_pid` writes as a unit (move `_gsis` writes from yardage-stat-enrichment into this module — see Mechanism 3). ### Mechanism 3: move `_gsis` writes into `player-identification-enrichment` `yardage-stat-enrichment.mjs:88-96` currently writes `bc_gsis`/`psr_gsis`/`trg_gsis`/`intp_gsis`/`player_fuml_gsis`. These move into player-identification-enrichment so `{_gsis, _pid}` pairs write/clear in lockstep. yardage-stat-enrichment keeps yardage/td/comp/etc writes. ### Per-family rollout **Phase A: tackle family** (`solo_tackle_{1,2,3}_{gsis,pid}`, `assisted_tackle_{1,2}_{gsis,pid}`, `tackle_assist_{1,2,3,4}_{gsis,pid}`). **Phase B: single-player play-stats families** (`bc_{gsis,pid}`, `psr_{gsis,pid}`, `trg_{gsis,pid}`, `intp_{gsis,pid}`, `player_fuml_{gsis,pid}`, `penalty_player_{gsis,pid}`). Each phase: edit the code so the corresponding family columns are owned by enrichment, deploy, run backfill across 2023+2024+2025 REG/POST, verify oracles return to 0, then proceed to the next phase. ### Test plan Add `test/play-enrichment-role-attribution.spec.mjs` covering: - `compute_play_changes` unit: NULL with prop in `clearable_fields` writes; NULL without it skips (regression guard); NULL when `edit.lhs` is also NULL no-ops. - Enrichment X-to-Y reattribution: play row has `solo_tackle_1_*` = X; new play*stats has statId 82 for Y (no 79); enrichment clears solo_tackle and writes `tackle_assist_1*\*` = Y. - Family-owned NULL-clear: play has stats for some families but not tackle; existing tackle attribution must clear. - Live-game preservation: play has zero play_stats rows; existing attribution must be preserved (no clear). - Sportradar interaction: sportradar wrote `psr_pid` before play_stats landed; play_stats lands with a different passer; enrichment clears and rewrites. ### Knowledge Base Impact - **Entry doc**: `text/league/data-quality-and-validation.md` is the canonical entry. No new domain doc. - **Updates to data-quality-and-validation.md**: - Add `[tackle_fix_design_a_implemented]` observation referencing the merged commits. - Add `## Role-attribution ownership model` body section documenting family-owned semantics, the `clearable_fields` mechanism, and the live-game gate. - Mark `[issue2_partial_tackle_fallback]` as superseded. - **ABOUT.md**: no nearby ABOUT.md changes needed. - **Tags**: none new. Continue `user:tag/league-xo-football.md`. - **Relations**: this task `addresses [[user:text/league/data-quality-and-validation.md]]`. - **Guidelines**: no new guideline; single-domain implementation. ### Working-tier needs Bounded plan, no `scratch/` slug. All artifacts live in the league repo plus this task body plus the data-quality entity observations. ### Orchestrator shape Single bounded task — no orchestrator. Phase A and Phase B are sequential steps within this task body, gated on oracle measurement between them. ### Risks and mitigations - **Live-game write race**: per-family `has__stats` gate is the floor; an empty play_stats array means no-op for every family. - **Changelog volume spike on backfill**: changelog is append-only; one-time cost acceptable. If needed, run backfill with `--skip-changelog`. - **Fallback removal regression**: the 2be33c98 and 9595ad5f fallbacks masked plays with play_stats whose stat rows didn't carry the role's statId. Under the new design those plays NULL-clear, which is the correct behavior (no statId for the role means no player in the role). ## Tasks ### Phase A: Tackle family ownership 1. **[x] Extend `compute_play_changes` with `clearable_fields`** — `libs-server/update-play.mjs` - Add `clearable_fields = new Set()` named parameter to `compute_play_changes` and propagate it through the `update_play` wrapper. - Modify L147 NULL-skip: allow `edit.rhs === null` through when `clearable_fields.has(prop)` AND `edit.lhs` is truthy (treat empty-string `lhs` as already-empty — no clear, no changelog). - **Plan adjustment during implementation:** `clearable_fields` membership also bypasses the overwrite gate at L167 for non-null writes. The original "preserve overwrite gate" wording conflicted with the success criterion "cross-source counters return to 0 without `--overwrite-existing`" for the X-solo→Y-solo same-slot reattribution case (enrichment produces `lhs=X, rhs=Y`; gate would block without `overwrite_existing`). Consistent reading: opting a prop into `clearable_fields` = enrichment owns the column = full write authority. Default empty set still preserves prior skip-existing behavior for sportradar / manual-CLI callers. Test `clearable_fields ownership bypasses overwrite gate (X-to-Y same slot)` locks the semantic. 2. **[x] NULL-aware `build_play_update_object`** — `scripts/process-plays.mjs:48-62` - Change the truthy guard at L52 (`if (enriched_play[field])`) to `if (enriched_play[field] !== undefined)` so enrichment-produced `null` flows downstream. - Add a code comment documenting the semantic contract: undefined = "no opinion", null = "clear". 3. **[x] Add tackle `_gsis` and `_pid` columns to `ENRICHED_FIELD_NAMES`** — `scripts/process-plays.mjs:24-43` - Append the tackle-family `_gsis` columns: `solo_tackle_{1,2,3}_gsis`, `assisted_tackle_{1,2}_gsis`, `tackle_assist_{1,2,3,4}_gsis`. The `_pid` versions are already in the list. 4. **[x] Tackle family ownership in `player-identification-enrichment`** — `libs-server/play-enrichment/player-identification-enrichment.mjs` - Add `has_any_play_stats = Boolean(stats_for_play && stats_for_play.length > 0)`. - Add `has_tackle_stats = stats_for_play?.some(s => [79, 80, 82].includes(s.statId)) ?? false`. - When `has_any_play_stats` is true, tackle family is owned: - When `has_tackle_stats` is true: write `solo_tackle_{1,2,3}_{gsis,pid}` strictly from `play_data.tacklers_solo`; NULL-fill remaining slots up to slot 3. Same for assisted_tackle (slots 1-2) and tackle_assist (slots 1-4). - When `has_tackle_stats` is false: NULL-write all tackle slots (family owned, no role on this play). - When `has_any_play_stats` is false (live-game window): leave all tackle columns untouched. - Remove `map_tackle_array_from_play_row` calls for the three tackle families. - Add an owned-family variant of `map_tackle_array` that emits explicit NULL per slot when family is owned but resolve produces zero pids. Return-value contract for the new variant: returns `true` when family is owned (so `has_player_data` accounting reflects that enrichment ran), regardless of how many slots were written or cleared. 5. **[x] Route tackle `clearable_fields` in `process-plays.mjs`** — `scripts/process-plays.mjs` - Build `tackle_clearable_fields` as a `Set` with explicit string members for all 18 tackle columns. - Pass as `clearable_fields` to the `compute_play_changes` call site (around L210). 6. **[x] Add test fixtures and assertions for Phase A** — `test/play-enrichment-role-attribution.spec.mjs` (11 tests passing) - `compute_play_changes` unit: NULL-clear with truthy lhs writes + changelog; empty-string lhs no-op; null lhs no-op; non-clearable null skipped (regression guard); X-to-Y same-slot overwrite via ownership bypass; sportradar legacy code-path (no clearable_fields) preserves overwrite-block behavior. - Enrichment X-to-Y reattribution: solo_tackle_1 cleared, tackle_assist_1 written. - Family-owned NULL-clear (has play_stats but no tackle statIds). - Live-game preservation (zero play_stats). - Missing-player-cache resolve still NULL-writes \_pid while recording \_gsis. - Happy path: statId 79+82 attribute solo_tackle_1 and tackle_assist_1. 7. **[x] Deploy Phase A** — commit 2b100554 pushed to origin/master; `pm2 deploy production --force` reloaded server process #5. 8. **[x] Backfill 2023+2024+2025 REG/POST tackle** — all 6 (year, seas_type) combinations completed without error. Final tackle residual: **35 across 3 seasons** (2023 REG s1=3; 2024 REG ta1=3, ta2=1; 2024 POST s1=2, ta2=1; 2025 REG s1=11, ta1=9, ta2=5). All 35 trace to **11 distinct gsisids with no row in the `player` table** — pre-existing "missing player rows entirely" residual cause documented in the oracle docstring. The new design correctly captures the upstream-truth gsisid + NULL `_pid`, preserving recoverability. Cleanup tracked in `█████████████████████████████████████████████████████████████████████` (Blocked-by relation on this task). Oracle `cli/monitoring/check-league-role-pid-residual.sh` was extended to monitor solo/assisted/tackle_assist slot families going forward (user-base commit c68db93e7). ### Phase B: Single-player play-stats families 9. **[x] Extend `ENRICHED_FIELD_NAMES` with single-player `_gsis` columns** — appended `bc_gsis`, `psr_gsis`, `trg_gsis`, `intp_gsis`, `player_fuml_gsis`. Penalty excluded. 10. **[x] Per-family detection for bc/psr/trg/intp/fuml** — new `owned_single_player_families` array declares statId sets (bc:[10,11], psr:[14,15,16], trg:[21,22], intp:[25,26], fuml:[52,53,54,106]). New `map_owned_player_field` writes `{_gsis,_pid}` from `play_data` only (no OR-fallback, no early-return when `_pid` already set). Per-family `stats_for_play.some(s => family.stat_ids.includes(s.statId))` gates whether the family's play_data is forwarded; when false, the helper NULL-clears both columns. Penalty stays on the legacy `map_player_field` path because case 93 is empty. 11. **[x] Move `_gsis` writes from `yardage-stat-enrichment.mjs` to `player-identification-enrichment.mjs`** — deleted the 5 `_gsis` writes at the prior L88-96 (`bc_gsis`/`psr_gsis`/`trg_gsis`/`intp_gsis`/`player_fuml_gsis`); pointer comment to player-identification-enrichment in their place. `map_owned_player_field` now writes the corresponding `_gsis` alongside `_pid` in lockstep. 12. **[x] Rename to `role_clearable_fields`, union single-player families** — Set is now 28 columns (18 tackle + 10 single-player). Penalty columns excluded. Phase A tests re-ran clean after the rename. 13. **[x] Extend test coverage to single-player families** — added 8 tests (bc reattribution + overwrite, psr family-owned NULL-clear, trg/intp/fuml gating, sportradar pre-write overwrite, penalty legacy preservation, single-player live-game preservation, `lhs:undefined` defensive guard, yardage-stat no-`_gsis` regression lock). All 18 Phase A+B tests pass. 14. **[x] Deploy Phase B** — commit 01dece89 pushed to origin/master; deploy required first stashing a dirty `yarn.lock` on the league host (pre-existing local `yarn install` artifact, recoverable in stash `phase-b-deploy: stash dirty yarn.lock and untracked core/ logs/`); `pm2 deploy production --force` reloaded server process #5. 15. **[x] Backfill 2023+2024+2025 REG/POST for single-player families** — completed across all 6 (year, seas_type) combinations. Per-week change rate was substantially higher than Phase A (typically 500-1000 plays/week, ~1000-2000 changelog entries each) — expected, since this run cleared stale bc/psr/trg/intp/fuml attribution accumulated over years. Post-backfill residual: **23 single-player + 2 penalty NULL-pid cases**, 100% traceable to 5 additional missing player records (combined with Phase A: 16 distinct missing gsisids). All redesign correctness criteria met; the residual floor is purely missing-player-record data quality, tracked at `█████████████████████████████████████████████████████████████████████`. ### Knowledge-base updates 16. **[x] Updated `text/league/data-quality-and-validation.md`** — added `## Role-attribution ownership model` body section documenting two-gate family ownership (`has_any_play_stats` + `has__stats`), the `clearable_fields` + NULL-aware `build_play_update_object` + expanded `ENRICHED_FIELD_NAMES` mechanism chain, penalty exclusion, and sportradar interaction. Updated the Monitoring-integration line to mention tackle-slot coverage in the oracle. Recorded `[role_attribution_redesign_implemented]` (commits 2b100554 + 01dece89) and `[issue2_partial_tackle_fallback_superseded]` observations. ### Task lifecycle 17. **[x] Task wrap-up** — Phase A+B implementation complete; `[completion]` recorded; status transitioned to Completed.