--- title: Fix psr_pid null on sack plays in nfl_plays for 2023+ type: task status: completed description: >- Restore passer attribution on sack rows in nfl_plays.psr_pid for 2023-2025 by extending the psr family stat_ids gate in player-identification-enrichment.mjs to include statId 20 (Pass Sack), then backfilling. Currently ~0% of sack rows have psr_pid set for 2023+ vs ~99% in 2021-2022. base_uri: user:task/league/fix-sack-attribution-nfl-plays.md created_at: '2026-05-31T01:55:02.408Z' entity_id: d7a14574-8343-4758-a8c3-8b2edddc6223 finished_at: '2026-05-31T03:55:27.107Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: Medium public_read: true relations: - extends [[user:task/league/redesign-role-attribution-ownership.md]] - addresses [[user:text/league/data-quality-and-validation.md]] started_at: '2026-05-31T03:22:19.888Z' tags: - user:tag/league-xo-football.md updated_at: '2026-08-22T23:36:01.126Z' visibility_analyzed_at: '2026-11-06T00:17:54.822Z' --- ## Overview - Restore passer attribution on sack plays in `nfl_plays.psr_pid` for 2023, 2024, 2025 (currently ~0% coverage on sack rows; was ~99% in 2021-2022). - Confirmed root cause: the upstream NFL play_stats feed stopped emitting statIds 14/15/16 (pass attempt/completion/yards) on sack rows starting in 2023. statId 20 (Pass Sack, charged to the QB) is still emitted with the QB's gsisId, and `get-play-from-play-stats.mjs` already maps statId 20 to `psr_gsis`. The `owned_single_player_families` gate in `player-identification-enrichment.mjs` for the psr family lists `stat_ids: [14, 15, 16]` only, so on sack rows the family writer sees "no family stats" and NULL-clears both `psr_gsis` and `psr_pid`. - **Impact**: QB sack-rate, pressure-rate, sack-yards-per-attempt, and any join/filter on the passer for sack events become correct for 2023+ once the fix lands and a backfill rerun completes. Affects downstream `player_gamelogs`, `nfl_team_seasonlogs`, data views, and analytics built on `psr_pid` predicates. ~4,300 sack rows across three seasons. - **Ordering**: Follows [[user:task/league/redesign-role-attribution-ownership.md]] — that task installed the family-gate ownership model; this task narrows a gate that was set too tight for the post-2022 upstream feed shape. No downstream task is blocked, but `generate-player-gamelogs.mjs` and `generate-nfl-team-seasonlogs.mjs` should be re-run after backfill since they aggregate psr_pid. ## Background - Data confirmed via `mcp__db__query_league` against `nfl_plays`: | year | sk=true | sk w/ psr_pid | sk w/ psr_gsis | sk w/ qb_pid | | ---- | ------- | ------------- | -------------- | ------------ | | 2021 | 1251 | 1244 | 1244 | 1242 | | 2022 | 1298 | 1297 | 1297 | 1298 | | 2023 | 1410 | 0 | 0 | 1410 | | 2024 | 1314 | 0 | 0 | 1314 | | 2025 | 1364 | 4 | 4 | 1362 | `psr_gsis` and `psr_pid` are both null on 2023+ sack rows — both writes go through the same family writer in lockstep. `qb_pid` (populated by `populate-qb-pid.mjs` via `nfl_snaps`) is set on essentially all sack rows, confirming the QB identity is recoverable. - Play_stats inspection of five 2024 sack rows with `psr_pid IS NULL` (esbids 2024090900/3499, 2024101310/1406, 2024101310/2101, 2024111400/595, ...) confirms statId 20 (`Pass Sack`) is present on every row with the sacked QB's gsisId (B.Purdy, D.Prescott, J.Hurts, etc.). statIds 14/15/16 are absent. statIds 110 and 120 also appear on sack rows but carry the sacker's gsisId, not the QB's — they are not safe to add to the psr family gate without extra discrimination. - Source files: - `repository/active/league/libs-shared/get-play-from-play-stats.mjs:127-129` — maps statId 20 → `psr_gsis = playStat.gsisId` (correct already). - `repository/active/league/libs-server/play-enrichment/player-identification-enrichment.mjs:140` — `{ gsis: 'psr_gsis', pid: 'psr_pid', stat_ids: [14, 15, 16] }`. This is the bug: gate excludes statId 20, so sack-only psr rows are NULL-cleared. - `repository/active/league/scripts/process-plays.mjs` — orchestrator; no change here besides re-invocation for backfill. - `repository/active/league/scripts/populate-qb-pid.mjs` — independent qb_pid pipeline; already correctly identifies the QB via snaps. - Read-only check on adjacent attribution roles (2022-2024): `bc_pid` on RUSH plays — no regression (0-3 nulls/yr); `trg_pid` on completions — no regression (1-14 nulls/yr). Regression is specific to `psr_pid` on sack rows. - Existing oracle `cli/monitoring/check-league-role-pid-residual.sh` is a `gsis-NOT-NULL AND pid-NULL` check. It does NOT catch this bug because the family writer null-clears BOTH columns together. A separate `sk=true AND psr_pid IS NULL` oracle is required. ## Design - **Approach**: extend the psr family's `stat_ids` allow-list in `player-identification-enrichment.mjs` to include statId 20 (`Pass Sack`). Rationale: - statId 20 is charged to the QB on a sack play and `get-play-from-play-stats.mjs` already writes its `gsisId` to `play_data.psr_gsis`. Adding 20 to the gate lets the existing owned writer see the play_data and write through, instead of NULL-clearing. - On non-sack pass plays, statId 20 is absent — no behavior change. - On sack-of-scramble plays (rare), statId 20 still names the QB; this matches our intended `psr_pid` semantic for sack rows. - **Scope discipline**: do NOT add statIds 110 or 120 — those name the sacker, not the QB, and would corrupt `psr_pid` on sacks. Do NOT touch non-sack pass attribution (bc/trg/intp/fuml) — confirmed clean. - **Backfill**: re-process 2023, 2024, 2025 plays through the existing job. Scope is REG + POST only — PRE is excluded for parity with the monitor query (`seas_type IN ('REG','POST')`) and because pre-season sack volume is low and irrelevant to QB pressure analytics. ``` yarn scripts:process-plays --year 2023 --seas-type REG ``` Repeat for each year × seas_type ∈ {REG, POST}. `process-plays.mjs` already iterates all weeks when `--year` is supplied without `--week`. **`--overwrite-existing` is NOT required and MUST NOT be passed.** `psr_gsis` and `psr_pid` are in `role_clearable_fields` (`process-plays.mjs:86-87`); `compute_play_changes` in `libs-server/update-play.mjs:183` reads `if (edit.lhs && !is_owned)` and bypasses the overwrite gate unconditionally for owned fields. The newly non-null payload writes through cleanly. Passing `--overwrite-existing` would gratuitously rewrite every non-owned field across three seasons and flood the changelog. A dry-run (`--dry`) is still recommended as a payload sanity check. - **Downstream re-runs after backfill**: - `scripts/generate-player-gamelogs.mjs` for 2023, 2024, 2025 — QB sack-rate, sack-yards columns are aggregated here. - `scripts/generate-nfl-team-seasonlogs.mjs` for 2023, 2024, 2025 — team sack rates. - Any materialized views derived from `nfl_plays` for those years (verify via `scripts/debug-plays-view.mjs` or schema audit). - **Regression guard**: add `cli/monitoring/check-league-sack-attribution.sh` (or extend `check-league-role-pid-residual.sh`) that runs: ``` SELECT year, seas_type, COUNT(*) FILTER (WHERE sk) AS sk_total, COUNT(*) FILTER (WHERE sk AND psr_pid IS NULL) AS sk_unattributed FROM nfl_plays WHERE year >= EXTRACT(YEAR FROM CURRENT_DATE)::int - 2 AND seas_type IN ('REG','POST') GROUP BY year, seas_type ``` and emits a `pipeline_failure` signal via `signal-emitter` when `sk_unattributed / sk_total > 0.02` (2%) on the current season — calibrated against the 2021-2022 historical baseline of ~99.5% attribution (residual ~0.3-0.5%) with a small operational margin. A 10% threshold would not fire on a recurrence of the exact bug this guards against (which ran at 100% unattributed). For completed prior seasons the monitor uses a tighter `> 0.01` (1%) threshold since the upstream feed for those weeks is frozen. Register in the failure-surfacing inventory per [[user:guideline/surface-pipeline-failures.md]]. Schedule via a `scheduled-command` entity alongside the existing role-pid-residual check. - **Knowledge Base Impact**: - Update `text/league/data-quality-and-validation.md` with a new "Sack attribution gating" subsection under the family-gate ownership model — document that statId 20 is part of the psr family because the post-2022 upstream feed omits 14/15/16 on sacks. Add a `[finding]` observation on this task entity once landed. - Add a relation `extends [[user:task/league/redesign-role-attribution-ownership.md]]` on this task. - Add a new monitoring entity for the regression-guard script under `scheduled-command/` and reference it from the failure-surfacing inventory task referenced in `user:guideline/surface-pipeline-failures.md`. - No new tags. Reuse `user:tag/league-xo-football.md`. No new ABOUT.md updates expected — the change lives in existing directories. - **Scratch tier**: not needed. All artifacts are durable (source edits, monitoring script, KB updates, backfill is a one-shot re-process with no intermediate files worth preserving). - **Orchestrator shape**: not warranted. Bounded single-task scope — one parser gate change, one backfill, one oracle. ## Notes - The owned writer's lockstep `_gsis` + `_pid` write means a play_changelog entry will land for every sack row touched during backfill. That is desirable forensically but will be loud. Run the backfill with `--skip-changelog` only if the volume becomes prohibitive; default keeps the audit trail. - Verification SQL (run before/after backfill): ``` SELECT year, COUNT(*) FILTER (WHERE sk) AS sk_true, COUNT(*) FILTER (WHERE sk AND psr_pid IS NOT NULL) AS sk_with_psr_pid FROM nfl_plays WHERE year BETWEEN 2023 AND 2025 AND seas_type IN ('REG','POST') GROUP BY year ORDER BY year; ``` Post-fix expectation: `sk_with_psr_pid / sk_true >= 0.99` matching 2021-2022 baselines. - Population-level validation: in addition to the aggregate count, sample 20 random post-backfill sack rows per year and confirm `psr_pid` resolves to a player whose `pos == 'QB'` via `players` join. Catches the failure mode where statId 20's gsisId resolves to a non-QB (would indicate upstream feed corruption). - Edge cases to spot-check after backfill: - Sacks on plays charted as `qb_scramble = true` (rare): psr_pid should still match qb_pid. - Safety-on-sack plays: confirm psr_pid set. - 2025 partial-season rows (4 already have psr_pid set). Expected outcome: the backfill re-runs the same enrichment path on these rows and overwrites in place to the same value (no observable change). If post-backfill spot-check finds these 4 rows have different psr_pid than other 2025 sack rows of the same QB, record as an observation but do not block backfill — likely indicates a one-off manual edit or a per-game upstream-feed shape exception, both orthogonal to this fix. - No GitHub issue or PR. Memorialize findings as observations on this task entity per `user-base/CLAUDE.md`. ## Plan ### Core Implementation - [x] **Extend psr family stat_ids to include statId 20 (Pass Sack)** - File: `repository/active/league/libs-server/play-enrichment/player-identification-enrichment.mjs` - Purpose: allow the owned psr writer to recognize sack-only play_stats payloads (2023+ upstream feed) so it writes through `psr_gsis`/`psr_pid` from statId 20 instead of NULL-clearing. - Changes: - Update the psr entry in `owned_single_player_families` from `stat_ids: [14, 15, 16]` to `stat_ids: [14, 15, 16, 20]`. - Add a one-line comment noting statId 20 covers sack rows where the upstream feed omits 14/15/16 starting in 2023. - [x] **Add unit test for sack-row psr attribution** - File: `repository/active/league/test/play-enrichment-role-attribution.spec.mjs` (existing file — append a new `describe` block for sack-row psr attribution). - Purpose: lock in the fix with a regression test using a fixture that mirrors a real 2024 sack play (statId 20 with QB gsisId, no 14/15/16, plus statIds 79/83/110/120 for the sacker). - Changes: - Fixture: one play row with `sk=true`, play_stats array carrying statId 20 → QB gsisId. - Assertion: enriched output has `psr_gsis` and `psr_pid` populated to the QB. - Counter-test: a normal pass-completion play (statIds 14/15/16) still resolves psr correctly and does NOT regress. - Verify: `yarn test --reporter min test/play-enrichment-role-attribution.spec.mjs` passes with the new cases. ### Configuration & Setup - [x] **Backfill 2023, 2024, 2025 plays** - Scope: REG + POST only (PRE excluded — see Design "Backfill" note). - Run (on the worker host where process-plays normally executes): - Dry-run sanity: `yarn scripts:process-plays --year 2023 --seas-type REG --dry` - Apply: `yarn scripts:process-plays --year 2023 --seas-type REG` then `--seas-type POST`; repeat for 2024 and 2025. Do NOT pass `--overwrite-existing` — psr fields are clearable/owned and the gate is bypassed unconditionally for them; the flag would gratuitously rewrite unrelated fields and flood the changelog. - Verify with the post-backfill SQL from Notes; expect `sk_with_psr_pid / sk_true >= 0.99` for 2023 and 2024, and current-trend for 2025. - [x] **Re-run downstream aggregations** - `yarn scripts:generate-player-gamelogs --year 2023` (and 2024, 2025). - `yarn scripts:generate-nfl-team-seasonlogs --year 2023` (and 2024, 2025). - Spot-check QB sack-rate column on ███████████████ view for known high-sack QBs (e.g., R.Wilson 2023, C.J. Stroud 2023) — should now match nflverse reference. ### Knowledge Base Updates - [x] **Document sack-row gating in data-quality-and-validation** - File: `text/league/data-quality-and-validation.md` - Append a "Sack attribution gating" note under the family-gate ownership section: statId 20 is included in the psr family because the upstream NFL play_stats feed stopped emitting statIds 14/15/16 on sack plays starting with the 2023 season. statIds 110/120 are explicitly excluded (sacker, not passer). - [x] **Add task relations** `(no verify -- mechanical step)` - Already set on this task entity at creation: `extends [[user:task/league/redesign-role-attribution-ownership.md]]`, `addresses [[user:text/league/data-quality-and-validation.md]]`. - [x] **Append `[finding]` observation post-implementation** `(no verify -- mechanical step)` - On this task entity, record the verified statId-20-on-sack root cause and the post-backfill `sk_with_psr_pid` rate per year via `base entity observe`. ### Testing & Validation - [x] **Regression guard: sack attribution monitor** - File: `cli/monitoring/check-league-sack-attribution.sh` (new) - Purpose: detect future upstream-feed shape changes that re-break sack attribution before they spread across a full season. - Behavior: query as in the Design section; emit `pipeline_failure` via `signal-emitter` with `kind: pipeline_failure`, `severity: high`, `dedup_key: league-sack-attribution:` when `sk_unattributed / sk_total > 0.02` for the current season (calibrated to the ~99.5% 2021-2022 baseline) or `> 0.01` for completed prior seasons. Scope query to `seas_type IN ('REG','POST')`. - Schedule: add `██████████████████████████████████████████████████` running weekly during the regular season. - Register in the failure-surfacing inventory ([[████████████████████-█████████-███-███████-████████████]]). - [x] **Population-level validation post-backfill** - Sample 20 random sack rows per year (2023, 2024, 2025) and verify `psr_pid` joins to a `players` row with `pos = 'QB'`. Failure mode caught: statId 20's gsisId resolving to a non-QB (would indicate upstream feed corruption distinct from the gating bug). - Confirm no regression on non-sack pass plays: `SELECT COUNT(*) FROM nfl_plays WHERE year IN (2023,2024) AND play_type='PASS' AND sk=false AND psr_pid IS NULL` should match the pre-backfill count.