--- title: Enforce the nfl_plays team-attribution invariants type: task status: completed description: >- Make offense_nfl_team and defense_nfl_team enforced derivations of possession plus the game: two CHECK constraints, a single writer, enrichment ownership so a wrong value self-heals, and an audible failure path. base_uri: user:task/league/enforce-plays-team-attribution-invariants.md created_at: '2026-09-10T23:35:45.915Z' entity_id: 4478546e-415c-4253-bc01-5a14d43faa1e finished_at: '2026-09-11T01:17:28.043Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: High public_read: true relations: - follows [[user:task/league/repair-plays-offense-defense-swap.md]] tags: - user:tag/league-xo-football.md - user:tag/software-task.md updated_at: '2026-09-11T01:17:28.043Z' visibility_analyzed_at: '2026-11-05T23:49:11.832Z' --- # Implementation Plan: Enforce the nfl_plays team-attribution invariants ## Overview - Make `offense_nfl_team` and `defense_nfl_team` enforced derivations of `possession_nfl_team` plus the game, instead of independently-writable vendor copies. - Three columns store two facts, four writers write them, and nothing arbitrates. Every defect in this area traces to that. - Impact: the class of defect repaired by `task/league/repair-plays-offense-defense-swap.md` becomes impossible to re-mint, rather than merely repaired and watched. The data check stops being the only thing standing between a vendor feed and a silently wrong team attribution. - Ordering: follows the repair task, because a CHECK constraint cannot be validated while violations remain. Precedes the consumer reconciliation and the JAX repair, both of which assume the invariant holds. ## Background Measured against `league_production` on 2026-09-10 unless stated otherwise. - `enrich_team_assignments` derives `offense_nfl_team = possession_nfl_team` and `defense_nfl_team` as the game's other team. It is the correct derivation and it is already written. - It reaches only two of four importers. `enrich_plays` is called at `scripts/import-plays-nfl-v1.mjs:516` and `scripts/process-plays.mjs:235`. `scripts/import-plays-nflfastr.mjs` and `scripts/import-plays-sportradar.mjs` never call it and assign the columns directly from vendor fields (`import-plays-nflfastr.mjs:355-357`, `import-plays-sportradar.mjs:296-309`). The task `integrate-play-enrichment-into-import-pipeline` is marked Completed but covered two of four. - A wrong non-null value is STICKY. `libs-server/update-play.mjs:194-212` skips any field that already holds a value unless `overwrite_existing` is set or the field is named in `overwrite_fields`. The team columns are absent from `role_clearable_fields` in `scripts/process-plays.mjs:97-126`, so a default `process-plays` run computes the correct `defense_nfl_team` and silently discards it. Only the global `--force` lands it, which would overwrite every other enriched column at the same time. - A NULL, by contrast, is filled: the guard at `update-play.mjs:194` is false when the existing value is null. This is why the collision guard in `import-plays-nflfastr.mjs` stores NULL rather than the bad pair — it converts a permanent wrong value into one that self-heals. - `scripts/import-plays-nfl-v1.mjs` bypasses `update_play` entirely, persisting through `upsert_plays` (`libs-server/upsert-plays.mjs:128-140`), a blanket merge with no overwrite guard and no changelog. It is the writer that changes these columns most often, and it is the reason `play_changelog` holds zero rows for any of the three across all 4.6M entries. - All three columns are in `SPORTRADAR_PROTECTED_FIELDS` (`libs-server/sportradar/sportradar-protected-fields.mjs:98,130,140`). Sportradar may fill a NULL but can never correct a value, regardless of flags. - Every phase in `libs-server/play-enrichment/index.mjs` is individually try/caught and reports only through `debug()`, which is unset on deployed hosts. The module's own comment records a silent 6,027-play preseason failure that went unnoticed exactly this way. ### Assumptions - The two proposed CHECK constraints hold over all history once the repair task lands. Checked: the repaired projection returns 0 violations over 1,399,866 rows carrying an offense. - `nfl_plays` is partitioned and a CHECK constraint on the parent propagates to partitions, so `NOT VALID` then `VALIDATE CONSTRAINT` avoids a long exclusive lock. CHECKED 2026-09-10 by rehearsal on a throwaway partitioned table against the Postgres 16 test container, mirroring production's 27 yearly partitions on `RANGE (season_year)`. The propagation half holds and the lock half does NOT, in a way that changes the design — see below. - `ADD CONSTRAINT ... CHECK ... NOT VALID` on the parent succeeds and recurses: the constraint lands on the parent and on every partition, all with `convalidated = false`. - A `NOT VALID` constraint still REJECTS a new violating row immediately. It is protective from the moment it lands, before validation. - `VALIDATE CONSTRAINT` on the parent recurses and validates every partition, and it fails naming the offending partition if any row still violates. - **`ADD CONSTRAINT ... NOT VALID` takes `ACCESS EXCLUSIVE` on the parent AND on every partition.** `NOT VALID` buys the absence of a table SCAN, not the absence of an exclusive lock. The lock is brief because nothing is scanned, but it blocks every reader on all 27 partitions while held, so `lock_timeout` is load-bearing rather than decorative. - **`VALIDATE CONSTRAINT` in its OWN transaction takes only `ShareUpdateExclusive`** on parent and partitions — which is the whole point of the split. - **Therefore the two statements must ship as two SEPARATE adhoc files.** `db-exec.sh` runs a file as `--single-transaction`, so putting `ADD NOT VALID` and `VALIDATE` in one file holds the `ACCESS EXCLUSIVE` lock from the first statement across the full scan of 1.4M rows in the second, which is strictly worse than a plain validated `ADD` and defeats the split entirely. Rehearsed and observed directly: within one transaction the session held `AccessExclusiveLock` on parent and every partition through the `VALIDATE`. - Both files still need the `SET lock_timeout = '30s'; SET statement_timeout = 0;` prologue that `docs/guides/schema.md` requires — the server's `statement_timeout` is 40s with no role or database override, and a validating scan of `nfl_plays` will exceed it. - Adding the team columns to the enrichment-owned set does not clobber a legitimate vendor value anywhere in the table. UNCHECKED and the riskiest item here — it changes who wins a conflict for every play in the table. Gated behind a measured dry-run diff below. - Sportradar's derivation (`pos_team === home ? away : home`) is equivalent to enrichment's, so removing its direct assignment changes no value. Checked by reading `import-plays-sportradar.mjs:296-309`; to be confirmed by the dry-run diff. - `possession_nfl_team` is NULL on `NOPL` administrative markers by convention, so the offense-equals-possession constraint must exempt a NULL possession. Checked: the constraint as written is null-permissive on either side. ## Design Keep the columns. Remove the ability to diverge. **Constraints.** Two table-local CHECK constraints, needing no join, both null-permissive: - `nfl_plays_offense_matches_possession` — offense equals possession where both are non-null. - `nfl_plays_defense_differs_from_offense` — defense and offense name different teams where both are non-null. Added `NOT VALID`, then validated in a separate statement. These two would have made both repaired sub-classes impossible. They cannot express "defense is the game's other team", which needs a join; that half stays with the data check. **Single writer.** Importers write `possession_nfl_team` only. `enrich_team_assignments` owns the derived pair. This deletes the vendor-copy failure mode at its root rather than guarding it, which means the collision guard added to `import-plays-nflfastr.mjs` by the repair task is removed here — a guard against a value the importer no longer writes is transitional cruft. **Ownership.** The two derived columns join the enrichment-owned set so `process-plays` can correct them without a global `--force`. This is what makes the repair durable rather than one-shot, and it is the change that needs the dry-run diff. **Audibility.** Team-assignment enrichment failure emits a signal with a resolve on the clean branch, per the repo rule that a `debug()` line is not a report. The `upsert_plays` changelog gap is named here and deliberately NOT fixed: it is a property of the nfl-v1 persist path affecting every column, not just these three, and it deserves its own task rather than a partial fix smuggled into this one. ### Knowledge Base Impact - `docs/guides/schema.md` — the constraint pair and the reason a derived column gets one. - `docs/guides/data.md` — the single-writer rule for the derived pair, so a session adding an importer finds it before writing the columns. - Candidate guideline: a derived column needs a single writer and a constraint, not a comment. This is the third instance of the class in this repo. - No new tag. No scratch slug needed for this task. ## Notes - Order matters: constraints must be added after the repair lands, and the ownership change must not ship before the dry-run diff is read. - The dry-run diff is the gate on this task. If it shows changes beyond the known defect population, stop and report rather than proceeding. - Removing the nflfastr collision guard is only safe once that importer no longer writes `defense_nfl_team` at all. Both changes ship together or neither does. ## Plan ### Core Implementation - [x] **Confirm the constraint premise on the partitioned table** — settled 2026-09-10, see Assumptions. 27 yearly partitions on `RANGE (season_year)`, 2000 through 2026. `ADD ... NOT VALID` takes `ACCESS EXCLUSIVE` on parent and all 27 partitions; `VALIDATE CONSTRAINT` in its own transaction takes only `ShareUpdateExclusive`. The DDL is therefore TWO files, not one. - [ ] **Author the constraint DDL as two files** - File: `db/adhoc/-add-plays-team-attribution-constraints.sql`, then `db/adhoc/-validate-plays-team-attribution-constraints.sql` - Purpose: convert the documented invariant into one the database refuses to break, without holding an exclusive lock across the validating scan. - Changes: the first file adds both CHECK constraints `NOT VALID`; the second runs `VALIDATE CONSTRAINT` for each and carries a post-condition that raises if either is still `NOT VALID`. Both carry the `lock_timeout` / `statement_timeout` prologue. - Verify: run against a scratch copy first and prove each constraint REJECTS a deliberately violating row; a constraint that cannot reject is the vacuous-green failure. Separately assert the two files are not merged — one transaction holding `ACCESS EXCLUSIVE` across the scan is the failure this split exists to prevent. - [x] **Measure the ownership change before making it** — run 2026-09-11 as an EXACT whole-population diff rather than a sampled dry run, which is the stronger form: compute enrichment's derivation for every play and compare it to what is stored. **Zero rows would change**, over the 1,386,832 plays enrichment would touch. The control is what makes that a reading: the same diff against a deliberately inverted derivation reports all 1,386,832, and a variant asking whether offense equals the home team reports 695,590. The gate passes cleanly. - [x] **Make the derived team columns enrichment-owned** — done. The set is renamed `enrichment_owned_fields` because it no longer holds only role columns, and the two null semantics it now carries are documented on it. Four specs in `test/update-play.spec.mjs` pin it: a wrong defense is corrected without `overwrite_existing`; the SAME call with ownership removed leaves it alone (the control — the two readings must differ); an unrelated non-null column is untouched in the same pass; and ownership outranks the protected-field blocklist, which it must, since all three team columns sit on `SPORTRADAR_PROTECTED_FIELDS`. - [x] **Wire enrichment into the nflfastr importer and stop it writing the derived pair** — done, with one deviation from the plan's letter. This importer UPDATES existing rows one at a time through `update_play` rather than building inserts, so calling `enrich_plays` wholesale as nfl-v1 does would not fit its shape. It calls `enrich_team_assignments` — the same single writer, one layer down — in the play handler where the resolved game is in scope. The vendor `defteam` read is gone entirely, along with the collision guard, `impossible_team_pair_count` and its `=== SUMMARY ===` field. `build_nflfastr_game_resolver` gained a `get_game` accessor, which cannot miss for an esbid the resolver itself produced. - The pair must move WITH possession rather than wait for the next process-plays run, and that is now a hard requirement rather than a preference: `nfl_plays_offense_matches_possession` rejects any row whose offense disagrees with its possession, so writing possession alone over a stale offense would fail the UPDATE. - [x] **Wire enrichment into the sportradar importer and stop it writing the derived pair** — done. Its inline `pos_team === home ? away : home` is replaced by a call to the shared derivation. The pair still lands on `mapped`, because this file READS it further down for play-match criteria and per-stat attribution; what changed is who computes it, which is the actual single-writer claim. - The equivalence assumption held — the formula was identical, which is exactly why it should not have existed twice. - The call sits in a module-level `derive_team_pair` helper rather than inline, and that shape is dictated by a gate: `████████████████████████████████` walks the mapping functions' ASTs and treats every object-literal key inside one as a static WRITE TARGET, so an inline `{ esbid, ... }` argument registered `esbid` as a column the mapper claims to write and never does. `play-importer-write-keys.spec.mjs` caught it. The gate was right; the fix is keeping the literal out of the scanned function. - [x] **Make team-assignment enrichment failure audible** — done. `enrich_team_assignments` now returns `{ plays, enriched_count, skipped_count }` rather than only logging its counts, because a phase that must REPORT cannot keep its numbers in a `debug()` line. `enrich_plays` raises a `data_integrity` signal on a throw or on a skip share at or above 0.50, with a `resolve_signal` on the clean branch, both on a pinned dedup key. - Threshold calibrated on the legitimate skip population rather than picked round: a skip is correct on a play with no possession, and the worst per-game null-possession share across all 8,291 games carrying more than 20 plays is 0.1978, mean 0.0720, with ZERO games above 0.25. The failure shape is the opposite — a `games_map` without team roles skips every play, reading 1.0. - `test/play-enrichment-team-assignment-signal.spec.mjs` INJECTS the signal transport, since `emit_signal` no-ops outside production and a spec without the seam would pass with the call site deleted. Three cases that must read differently from one another: the retired `h`/`v` games map emits and does not resolve; a clean run resolves and does not emit; and one possessionless play in four resolves rather than crying wolf. ### Knowledge Base Updates - [x] **Document the constraint pair and the single-writer rule** — `docs/guides/schema.md` gains a section on why a derived column needs a constraint and a single writer, both constraint names with their reasons, and the two-file `NOT VALID` / `VALIDATE` rule with the lock readings behind it. `docs/guides/data.md` gains the single-writer rule for these columns, the three consequences an importer author hits, and the measured disagreement between the two team surfaces. - The `data.md` half was swept into a concurrent session's commit `7615a60b3`, which committed that path while my edits were in the working tree. The content is intact and committed, under their message rather than mine. ### Testing & Validation - [x] **Population-level validation that the invariant holds and the constraint can fail** — both halves run. All four violation arms read zero table-wide after the repair, and the check grades 27 seasons over 1,399,881 rows with no floor breach. Each constraint was proven to REJECT its own violating INSERT during the rehearsal, and the null-permissive NOPL shape was proven to be ACCEPTED in the same run, so the two readings differ. - [x] **Confirm the existing data check still grades and still can go red** — the check's own query grades 27 seasons over 1,399,881 rows, smallest season 1,203 against a 800 floor, zero violations. - **Its calibration prose is now STALE in one respect and should be corrected by whoever next touches the registry.** It describes the fourth arm (possession set, defense null) as existing because the nflfastr collision guard converts the vendor defect into an absence. That guard is gone — the importer no longer writes `defense_nfl_team` from a vendor field at all — so the arm's stated justification no longer holds even though the arm itself is still worth keeping as a cheap invariant. Not edited here: `db/checks/registry.mjs` had uncommitted sibling work in the tree throughout this session. - [x] **Full suite** — `yarn test:isolated` 8,643 passing / 0 failing, `yarn lint` clean (4 pre-existing warnings in untouched files), `yarn check:types` green. - **`yarn check:cluster` is RED and it is NOT this work.** It fails on `api-response-shapes` reporting BLIND plus three data-view gates running with no negative control (`data-view-sql-validity`, `saved-view-param-coverage`, `data-view-url-param-coverage`). 29 gates ran: 18 OK, 7 with findings, 1 blind, 3 skipped. None of those gates' subjects are files this work touches, and several data-view files had uncommitted sibling work in the tree at the time. Stated as observed rather than claimed green — I did not prove it was red beforehand.