--- title: Fix the 2025 Sportradar kick-result enum defaults type: task status: planned description: >- Three nfl_plays result enums (field_goal_result, extra_point_result, two_point_result) are written onto non-attempt plays for 2025 only, making any unguarded 2025 kicking query about 97 percent wrong. Find the writer, stop it, and null the spurious rows. base_uri: user:task/league/fix-sportradar-2025-kick-result-enum-defaults.md created_at: '2026-09-05T02:34:58.585Z' entity_id: 01c9b6c2-28bf-4018-aee2-998d6f46f1b2 observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: High public_read: true tags: - user:tag/league-xo-football.md updated_at: '2026-09-21T12:43:52.711Z' visibility_analyzed_at: '2026-11-05T23:49:29.561Z' --- # Implementation Plan: Fix the 2025 Sportradar kick-result enum defaults ## Overview - Three `nfl_plays` result enums are written onto plays that are not the corresponding attempt, for 2025 only. Find the writer, stop it, and repair the rows. - Expected outcome: `field_goal_result`, `extra_point_result` and `two_point_result` are non-null only on their own attempt, in 2025 as they already are in 2024. - Impact: today any 2025 kicking query without an `is_*_attempt` guard is about 97 percent wrong. The defect is invisible to a reader because the value is a legitimate enum member rather than a null or an error. - Ordering: blocks exposing the special-teams column family in the plays view. Nothing else is known to depend on it. ## Background Measured 2026-09-05 against production, `season_type = 'REG'`: ```sql SELECT season_year, is_field_goal_attempt, field_goal_result, count(*) FROM nfl_plays WHERE season_year IN (2024,2025) AND season_type='REG' AND field_goal_result IS NOT NULL GROUP BY 1,2,3 ORDER BY 1,2,3 ``` 2024 is clean: 1,115 non-null values, every one on `is_field_goal_attempt = true` (937 made, 160 missed, 18 blocked). 2025 is not: **42,641 rows with `is_field_goal_attempt = false` carry `field_goal_result = 'missed'`**, plus 1,970 more where the flag is null, against only 1,106 real attempts. The same shape appears on `extra_point_result` (44,496 spurious `missed`) and `two_point_result` (45,659 spurious `failure`). The season shape points at Sportradar, whose play data begins in 2025. `field_goal_result` is listed in `libs-server/sportradar/sportradar-exclusive-fields.mjs`. ### What has been ruled out - `standardize_kick_result` is not the writer. It returns null for a falsy input at `libs-server/play-enum-utils.mjs:75-78`, so the nflfastr path at `scripts/import-plays-nflfastr.mjs:565` cannot produce these values. - The Sportradar field-goal stats mapper is not the writer. It sets `is_field_goal_blocked` unconditionally whenever it runs (`libs-server/sportradar/sportradar-stats-mappers.mjs:206`), and `is_field_goal_blocked` is **null on all 44,611 spurious rows**: ```sql SELECT count(*) AS spurious, count(*) FILTER (WHERE is_field_goal_blocked IS NOT NULL) AS with_blocked_flag FROM nfl_plays WHERE season_year=2025 AND season_type='REG' AND field_goal_result='missed' AND is_field_goal_attempt IS DISTINCT FROM true ``` ### Assumptions - The three enums share one writer, since they share one season boundary and one shape. Unchecked: inferred from the parallel counts, and the diagnosis step below is what settles it. - 2024 rows are correct and need no repair. Checked: the cross-tab above shows every 2024 value on a real attempt. - No consumer currently depends on the spurious values. Unchecked; the enumeration step below is what establishes it. ## Plan ### Diagnosis - [ ] **Find the writer** - Trace every path that assigns the three enums, starting from the Sportradar importer and the generic play-update path in `libs-server/update-play.mjs`. - Verify: reproduce a spurious write against a fixture play that is not a kick attempt, and show the assignment happening. A structural reading of the code does not close this step. - [ ] **Enumerate the consumers before repairing** - Grep for readers of the three columns across `libs-server/`, `libs-shared/`, `app/`, `scripts/` and the data-view column definitions. - Verify: each reader is recorded as either already guarded by an `is_*_attempt` predicate or affected, with a count of affected rows. ### Repair - [ ] **Stop the write** - Fix the assignment so the enum is set only when the corresponding attempt flag is true. - Verify: re-run the importer over one 2025 game and confirm the non-attempt plays in that game come back null, against a control game imported before the fix. - [ ] **Null the spurious rows** - One adhoc statement under `db/adhoc/`, scoped to 2025 and to rows where the attempt flag is not true. - Verify: the cross-tab query in Background returns the 2024 shape for 2025 — every non-null value on a real attempt — and the real-attempt counts (1,106 field goal attempts) are unchanged by the repair. ## Testing & Validation - [ ] **Pin the invariant** - A check asserting that each of the three enums is null wherever its attempt flag is not true, across all seasons. - Verify: the check is shown red against the current 2025 data before the repair runs, and green after. A check that has not been observed failing is not evidence.