--- title: Build the football unit tag store type: task status: completed description: >- Core of one append-only league store tagging games, team-games, player-games and plays with reviewed tags (reason, evidence plays), a closed registry seeded with qb, a review CLI and a game-sample tags filter at team-game and player-game grain. base_uri: user:task/league/build-football-unit-tag-store.md created_at: '2026-10-19T15:29:43.809Z' entity_id: 16b3e31b-18f2-4a1f-902d-1f1174e020aa finished_at: '2026-10-19T16:42:14.651Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: High public_read: true relations: - relates [[█████████████████████████-████-█████████-███-█████-██████████]] started_at: '2026-10-19T15:38:51.705Z' tags: - user:tag/league-xo-football.md - user:tag/nfl-betting.md updated_at: '2026-10-19T17:05:25.097Z' visibility_analyzed_at: '2026-11-06T00:17:41.103Z' --- # Build the football unit tag store ## Overview - One store for tagging what happened in football units. Code will derive most tags later; this task ships reviewed tags, written by a person or agent with a reason and optional evidence plays. - The game sample engine filters on tags, so a profile can pull a curated sample ("games this quarterback started") without hand-listing esbids. - Impact: profiling and research stop writing each kept and dropped game out by hand per profile. A reviewed classification is written once and every later sample reuses it. - Ordering: this is the core only, used and refined before it grows. Derived writers (availability and exits, staff, regime, script), more unit types (drive, span) and the play-grain filter follow as their own tasks when a real use needs them. ## Background - Research 2026-10-19 (session repl-3f0113be) mapped the stores and the curation asks: - About 60% of real kept and dropped reasons in `text/nfl/season/2026/*-offense-reads.md` turn on the passer, the play caller, the line and receiver room, and teammate availability. The rest are mostly opponent unit, game script or a span of a game, and sample contamination. - `selection_tag` (████████████ SQLite) is the only append-only tag store with a registry and provenance, and it tags bets, not football units. - `nfl_team_classification_gamelogs` overwrites in place, and the engine never reads it. `nfl_play_film_observations` has 0 rows. - League conventions (`repository/active/league/docs/guides/schema.md:15-28`): DDL goes in `private/db/adhoc/-.sql`, applied with `yarn db:exec`, then `yarn export:schema` and `node db/tools/generate-schema-types.mjs`. A new table registers in `db/table-domain-manifest.json`, `db/tools/generate-reader-role-grants.mjs`, `scripts/dev-fixture-projection.json` and `scripts/postgres-backup.sh`. - The closest code precedent is the film labels registry `libs-shared/film-observation-labels.mjs:6-85` (label declarations plus a validator returning an error string or null). - The engine's game-grain set-valued metrics (`active_teammates` at `libs-shared/game-sample/game-sample-catalogue.mjs:634-646`, loader `libs-server/game-sample/load-game-sample-frames.mjs:656-691`; `offensive_line_starters` at `:839`) are the fit for a `tags` metric. Opponent reads go through `team_row(esbid, opponent)` in `read-game-sample-fields.mjs:266-283`. ### Assumptions - The team frame is cached for 24 hours (5 minutes for the current season) at `load-game-sample-frames.mjs:36-37`, so tags load per request beside the on-field map (`get-game-sample.mjs:421-448`), never onto the cached frame (checked: read both, 2026-10-19). - Set-valued fields are refused outside the `this_game` window (`game-sample-catalogue.mjs:1501-1507`), which is the window a tag filter needs (checked: read the refusal). - The set filter returns false for a null value under both include and exclude (`get-game-sample.mjs:133-142`), so an untagged unit must read as an empty list for an exclude to keep it (checked: read the filter, 2026-10-19). - The schema auditor fails `derivation_version` and `tagged_at`, and passes `rule_version`, `created_at` and every `unit_*`, `tag_*`, `subject_pid`, `tag_origin`, `reviewed_by`, `review_reason` and `evidence_play_list` name (checked: `db/tools/audit-schema-conformance.mjs` on the tracked schema plus a synthetic table via process substitution, with controls `xyz_cnt` and `unit_drive_seq` firing, 2026-10-19). Re-run on the real adhoc file in the Store step. - No table named `football_unit*` or `*unit_tag*` exists (checked: `base db query league "select table_name from information_schema.tables where table_name like '%football_unit%' or table_name like '%unit_tag%'"` returned 0 rows, 2026-10-19). - `play_id` order is not chronological: in 2024-2025 it breaks quarter order 617 times across 110,393 plays, while `sequence` breaks it 0 times (checked by the plan review, 2026-10-19). A later span unit keys on `sequence`, never a `play_id` range. - Reviewed tags cannot be re-derived, so the table must be in the backup set (checked: `scripts/postgres-backup.sh` lists it in `db_user_tables`, 2026-10-19). ## Design - **Table `nfl_football_unit_tags`**, append-only. One row per tag write: - `football_unit_tag_id` uuid primary key. - The unit: `unit_type` (`game`, `team_game`, `player_game`, `play`) and its key columns `unit_esbid` (every type), `unit_nfl_team` varchar(5) (team_game), `unit_pid` (player_game) and `unit_play_id` (play). A CHECK per unit type requires exactly its key columns. A player_game carries no team, since the pid and esbid already fix it. - The tag: `tag_kind` and `tag_value` (varchar(64), the film label width), and an optional `subject_pid` when the tag names a player. - Origin: `tag_origin` (`derived` or `reviewed`). A derived row must carry `rule_version`; a reviewed row must carry `reviewed_by` and `review_reason` (CHECKs). A reviewed row may carry `evidence_play_list`, a jsonb list of `{esbid, play_id}` pairs, the shape `███████████████████████████████` documents for `evidence_plays`. - `tag_state` (`added` or `removed`, CHECK) and `created_at` stamped by the database. - One index on the resolution key `(unit_esbid, unit_type, tag_kind, created_at desc)`. - Not a `_history` table in the schema standard's sense: that shape is for temporal feeds whose observation instant is in the unique key (`guideline/nfl/league/database-schema-standards.md` § Time-series layer), and these rows are rulings. No `_index` table; the current-tag read goes through the one index. - **Current tags** are resolved in one place: per unit, kind, value and subject, the latest reviewed row wins if one exists, otherwise the latest derived row. No deriver ships here, but the rule is fixed now so the first deriver cannot override a review. - **Registry** `libs-shared/football-unit-tag-registry.mjs`: each kind declares its unit types, whether a unit holds one value or many, whether it requires a subject, its origin (derived or reviewed), and its closed values. A validator rejects anything off the registry. Seeded with the one kind the first real use writes: - `qb` on team_game, one value per unit, subject required, reviewed for now: `starter` (the subject started and played the game), `backup-start` (the subject started in place of the regular), `starter-exited` (the subject started and left, with the reason in `review_reason`). - Every other kind is added when its first writer or review needs it (Notes lists the ones already settled). - **Library** `libs-server/football-unit-tags/`: record a reviewed tag (add or remove) after registry validation, and read current tags for a set of units. No derived-sync function until the first deriver. - **CLI** `scripts/football-unit-tags.mjs`: `add`, `remove` and `list`, reviewed origin only, `--reason` required, `--evidence_play esbid:play_id` repeatable. A single-value kind refuses a second value until the first is removed, and a removal refuses a tag the unit does not carry. - **Engine filter**: a set-valued `tags` metric at team_game and player_game grain, `this_game` window, whose values are `kind:value` strings, plus `kind:value@` for a subject-bearing tag. - team_game reads tags on the team-game and on its game. The opponent perspective reads the opponent team-game. - player_game reads tags on the player-game. - An untagged unit reads `[]`, never null, so exclude keeps it. - Tags load per request when a field reads them (`load-game-sample-unit-tags.mjs`, passed to the accessors as a context argument), and the expression check validates values against the registry. - Each registry value declares `is_known_before_kickoff`. A tags predicate is pre-kickoff only when every value it lists is, so comparables accepts `qb:backup-start` and refuses `qb:starter-exited`. - **Not in this task**: - Derived writers, the drive and span unit types, the play-grain filter (no play-grain set-valued metric exists yet, and the play frame has its own cache at `load-game-sample-play-frame.mjs:24-25`), a kept and dropped manifest on samples, and the review pass over the offense-reads lists. - `nfl_team_classification_gamelogs` already holds team-game `script_path`, volume-shock and `impact_injury_list` columns that overlap the deferred `script` and `availability` kinds. The first deriver task for either kind decides whether that table feeds the tags or is retired, so two parallel stores do not stand. - Folding `nfl_play_film_observations` (0 rows) into this store is decided with [[█████████████████████████-████-█████████-███-█████-██████████]], which writes that table today. - **Knowledge Base Impact**: a query-trap sheet for the new table (current-tag rule, never read raw rows as current, untagged is not "no event" before 2024), a short section in [[user:guideline/nfl/betting/build-a-game-sample.md]] naming the `tags` filter and the review CLI, and a line in `task/league/ABOUT.md`. No scratch slug is needed. A single task holds it; no orchestrator. ## Notes - Tags record what happened, never a value: no rate, tier or number for an absence. That is the line the failed positional prior crossed ([[█████████████-█████████████-██████████-███████-████████]]). - Kinds settled 2026-10-19 but deferred to their writers: `availability`, `regular`, `unit-state`, `staff`, `regime`, `script`, `sample-quality`, `role`, `opponent-scheme`, `environment`, `film`. - The `availability` taxonomy settled 2026-10-19, for its deriver task: when it happened (pregame out, reserve list, late scratch, healthy scratch, exit ruled out, exit no return, exit then returned, exit unannounced, limited) crossed with why (injury, illness, suspension, coach decision, rest, ejection, unknown), plus modifiers (lopsided at exit, first game back, unit depleted). Planned rest and a backup's demotion are their own labels, not disruptions. A regular is a snap-share regular going in (QB and OL 0.70, WR and DB 0.60, DL, LB and TE 0.50, RB 0.40), with the norm frozen across an absence spell. Hard is absences, ruled out and no return; soft is returned, limited and unannounced. The exit play comes from `nfl_snaps`, not the injury note's play. - Games before 2024 have no injury events, so a missing availability tag there will mean unknown, not undisrupted. ## Plan ### Store - [x] **Create the table** - File: a private ad hoc DDL file (`-- STATUS: PENDING`, no BEGIN/COMMIT, uppercase `CREATE TABLE` for the auditor) - Purpose: the append-only store with its CHECKs and the resolution index. - Then `yarn db:exec`, `yarn export:schema`, `node db/tools/generate-schema-types.mjs`, and register the table in `db/table-domain-manifest.json`, `db/tools/generate-reader-role-grants.mjs`, `scripts/dev-fixture-projection.json` and `scripts/postgres-backup.sh`. - Verify: the auditor run on schema plus adhoc with `--table nfl_football_unit_tags` is clean; `yarn verify` passes; `grep nfl_football_unit_tags scripts/postgres-backup.sh` matches; inserts are refused for a team_game row missing `unit_nfl_team`, a reviewed row missing `review_reason`, and a `tag_state` of `deleted`. - [x] **Registry and validator** - File: `repository/active/league/libs-shared/football-unit-tag-registry.mjs` - Purpose: the closed vocabulary, seeded with `qb`. - Verify: `yarn test:isolated test/libs-shared.football-unit-tag-registry.spec.mjs` covers an accepted tag, an unknown kind, an unknown value, a wrong unit type and a missing required subject. - [x] **Library** - Files: `repository/active/league/libs-server/football-unit-tags/` (record reviewed, read current) - Purpose: append-only writes and the single current-tag rule. - Verify: `yarn test:isolated test/libs-server.football-unit-tags.spec.mjs` shows a removal hides an earlier add, a reviewed removal beats a later derived row inserted directly, and an off-registry write is refused before insert. - [x] **Review CLI** - File: `repository/active/league/scripts/football-unit-tags.mjs` (yargs, `.strict()`, `is_main` guard) - Purpose: tag and untag units during profiling and research, and list a unit's current tags with their reasons. - Verify: `yarn test:isolated test/scripts.football-unit-tags.spec.mjs` covers add, remove, list and a refused off-registry tag. ### Engine filter - [x] **`tags` metric at team_game and player_game grain** - Files: `libs-shared/game-sample/game-sample-catalogue.mjs`, `libs-shared/game-sample/game-sample-expression.mjs`, `libs-server/game-sample/game-sample-metric-values.mjs`, a new `libs-server/game-sample/load-game-sample-unit-tags.mjs`, `libs-server/game-sample/get-game-sample.mjs`, `libs-server/game-sample/read-game-sample-fields.mjs` (all under `repository/active/league/`) - Purpose: include and exclude on tags, including the opponent perspective, loaded per request. - Verify: `test/libs-shared.game-sample-fields.spec.mjs` accessor-key parity passes; `test/libs-server.game-sample.spec.mjs` gains cases for a team_game exclude that keeps an untagged game, an opponent read, a subject-bearing value, and a tag written after the frame was cached appearing on the next call. ### Knowledge Base Updates - [x] **Query-trap sheet for the table** under `guideline/nfl/league/query-traps/`. Verify: `base entity get` resolves it and `grep` finds it in the query-traps ABOUT. - [x] **`build-a-game-sample.md` section** on the `tags` filter and the review CLI. Verify: the section names the CLI and one worked `--where` on `tags`. - [x] **`task/league/ABOUT.md` line** for this task. Verify: `grep build-football-unit-tag-store task/league/ABOUT.md` matches. ### Testing & Validation - [x] **Real use on a live profile's reasons**: from `text/nfl/season/2026/washington-commanders-offense-reads.md:53`, tag Washington's weeks 3-4 team-games `qb:backup-start` with subject Mariota and week 2 `qb:starter-exited` with subject Daniels, then run `query-game-sample.mjs` at team_game grain for Washington 2026 excluding `qb:backup-start`. - Verify: the sample drops exactly weeks 3 and 4 and keeps every untagged game; including `qb:starter-exited@` returns week 2 alone. Failure mode caught: an exclude that drops untagged games, or a filter that reads the cached frame instead of the store.