--- title: Add coach and play-caller columns and params to data views type: task status: planned description: >- Make coach attribution a play-filter param across every from-plays data-view column, plus three team-grain coach display columns. Successor to the plays-view half; blocked on the generated per-role option-set modules. base_uri: user:task/league/add-coach-play-caller-data-view-columns.md created_at: '2026-09-10T22:13:26.957Z' entity_id: bd7fda95-9e9f-42b7-8fec-10aaec878d7f observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: High public_read: true relations: - follows [[████████████████████-█████-████-██████-█████-████-██████████]] tags: - user:tag/league-xo-football.md updated_at: '2026-11-02T15:47:35.886Z' visibility_analyzed_at: '2026-11-05T23:48:09.904Z' --- # Implementation Plan: Coach and Play-Caller Columns for Data Views ## Overview - Make coach attribution a filter dimension on every from-plays data-view column, and add team-grain coach columns so the dimension is visible and sortable rather than only filterable. - **Impact**: the leverage is a JOIN, not a dataset. Four play-filter params make all ~279 from-plays columns splittable by who was calling plays — "Josh Allen's passing yards under Joe Brady" — without adding a column to the catalog. The three display columns are what make a split legible. - **Ordering**: split out of `████████████████████████████████████████████████████████████`, which ships the plays-view half. Blocked on `████████████████████████████████████████████████████████████` — the operator ruled 2026-09-10 for a generated build step, NOT an endpoint — and follows the `offense_nfl_team` side ruling, which has ISSUED (league 76c17eede, deployed ca88ba220). Precedes the coach-page product item named as Tier 2 in the competitor catalog. ## Background The join, the coverage, the role overlap and the source table are all documented in the plays-view task's Background and are not restated. The three facts that bear on THIS half: **Preseason is uncoverable.** All 1,525 preseason games lack `nflverse_game_id` — 270,959 plays, 18 percent of the corpus. A coach param that INNER-joins therefore silently drops every preseason play from a filtered result, and a team-grain coach column is empty for preseason. Both need a deliberate decision rather than an inherited default. **2026 carries forecast rows.** The table is populated for the entire scheduled 2026 season with opening-day assignments, for games not yet played. Plays-joined paths are unaffected, but a season-grain team column reads those rows directly and will report a full-season play caller off a schedule. This is the one place the forecast rows are load-bearing. **Mid-season change is real.** 4 to 10 teams per season change offensive play caller; 76 team-seasons across 2000-2025 carry an in-season offensive-caller change. A season-grain column must aggregate rather than pick. **`coach_id` is stable.** `█████████████████████████████` is Completed, so `coach_id` is already the deterministic DOB-anchored own-id. Nothing live can move it, which is what makes it safe to persist as a param value. ### Catalog cost The data-view generation catalog is **1,540,015 bytes over 598 columns**, measured 2026-09-10 with: ```bash NODE_ENV=development node -e "import('./libs-server/data-views/generation/build-data-view-generation-catalog.mjs').then(m => { const c = m.build_data_view_generation_catalog(); console.log(JSON.stringify(c).length, c.columns.length) })" ``` `play_filter_param_keys` serializes as a BARE STRING array — 243 entries at roughly 9.8 bytes each on `player_pass_yards_from_plays` — so four coach params cost about 11 KB, under one percent, regardless of how many coaches exist. **That figure is conditional on which registry the keys land in.** `build-data-view-generation-catalog.mjs:296` routes a param to the cheap bare-string list only if it is ABSENT from `common_column_params`; anything in the common registry serializes its full value list into `param_overrides`, which is already 608,792 of the 1,540,015 bytes. The keys must therefore live in `nfl-plays-column-params.mjs` only. This is an assumption with a verify, not a background note. ### Assumptions - **`coach_id` is immutable and safe to persist as a param value.** Checked: `█████████████████████████████` is Completed and the id is DOB-anchored with a format CHECK constraint in `db/schema.postgres.sql`. - **Four coach param keys cost about 11 KB of catalog, provided they are absent from `common_column_params`.** Checked for the bare-string serialization; the registry-membership half is enforced by a checklist verify below. - **Adding registry keys moves every from-plays table-alias hash, unconditionally.** Checked: `get-stats-column-param-key.mjs:51-55` maps every registry key through `serialize_param`, which returns a bare `${key}` for an unset value; the `has_value` filter at lines 23-27 applies only to `scope_param_keys`, which the file's own comment says are NOT registry members. Measured by a review agent at key length 4146 to 4194. The operator has ruled this move acceptable. - **The move busts caches but cannot produce wrong answers.** Reasoned: the hash stays injective, so two distinct param sets still hash distinctly; only previously-cached entries and alias-keyed saved views are invalidated. UNCHECKED by execution. - **No DDL is required.** Checked: existing unique indexes cover both join keys and the name lookup. - **The option-set mechanism is RULED: generated committed modules, not an endpoint.** A script writes per-role frozen arrays into `libs-shared/column-param-option-sets/`, one file per set; the param carries the set NAME and the client dynamically imports one set when its filter opens. Regeneration is manual. The operator's constraint was initial-load payload, not staleness or catalog bytes. Owned by `████████████████████████████████████████`. - **CHECKED: `defense_nfl_team` agrees with `nfl_game_coaches.nfl_team`.** Measured 2026-09-10 and recorded in the plays-view task's Background: exactly co-null with the offensive side at 95,404 with zero asymmetry, and ZERO of 1,149,555 joinable plays have a side team lacking a coach row for that game. One caveat to inherit: 1,781 plays over 23 games carry `offense_nfl_team = defense_nfl_team`, owned by thread 0d3a6526 and not a gate. ## Design ### Four sided params, not three `offense_head_coach`, `defense_head_coach`, `offense_play_caller`, `defense_play_caller`. The head-coach param is SIDED, which an earlier draft got wrong. An unsided `head_coach` key would silently bind to the offensive side, so filtering a defensive stat column by it would return the OFFENSE's head coach with nothing in the name to say so. Every param names its side because every column does. Side selection is declared in the registry, not inferred: offensive params attach on `offense_nfl_team`, defensive on `defense_nfl_team`. ### Filter on `coach_id`, display the name The persisted param VALUE is the immutable `coach_id`. A param value persists into saved views and short URLs, and a param-VALUE rename has NO gate in this repo — both coverage gates are key-anchored. Persisting the id removes that failure class rather than mitigating it. This is deliberately the opposite of the plays-view half, which filters on the NAME. **The reason for that asymmetry is the input control, not persistence** — an earlier revision claimed plays-view `where` clauses do not persist, which is FALSE: `libs-shared/data-views-saved-view-migration.mjs:1194-1196` confirms plays-view ids and clause values persist into saved views and immutable short URLs, and the migration never rewrites a clause value. Both surfaces persist. The plays view takes the name anyway because an opaque id in a free-text box is unusable, while a data-view param is chosen from a picker where the id never has to be typed. **The uniqueness argument DOES carry, and an earlier revision wrongly dismissed it.** "485 of 487 coach names are distinct" counts rows in `nfl_coaches`, not the population a filter returns. Two men are named Jim Mora and two Pete Carmichael, father and son in both cases; the Moras collide inside the head-coach role, so a name-valued head-coach filter returns 6,756 post-2000 plays over 82 games spanning two people. Measured 2026-09-10. On this surface `coach_id` avoids that outright — a second independent reason to persist the id, alongside URL stability. ### Option sets come from generated modules, scoped per role Never one shared 487-entry list: only 184 coaches ever held head coach, 194 ever called offense, 174 ever called defense. A shared list offers roughly 300 dead options on a head-coach facet, which is the junk-values defect `docs/guides/data.md` names. Each param's facet names a generated per-role option-set module. Note the deliberate exclusion: a generated module cannot know the active season, so a head-coach facet offers 184 rather than the 34 who coached in 2025 — correct, just not narrowed to the current view. ### The three display columns go in `game-column-definitions.mjs` `team_head_coach`, `team_offense_play_caller`, `team_defense_play_caller`, modeled on the existing `game_source` in that file — NOT in a new file, and NOT on `team-table-column-definitions.mjs`'s `make_column`. `game_source` already solves the hard problem: a PLAYER-row-grain cell resolves through the `player_year_teams` bridge, a team-family cell through `query_context.team_reference`, plus a `week_reference` predicate that stops week-axis fanout. It carries a documented 42P01 fix from getting exactly this wrong. A fresh file re-derives all of it and will drift from it — and without the player-family branch, rendering a coach column beside a player's stats is impossible, which is the headline use case. `team-table`'s `make_column` is the wrong home for the opposite reason: it is bound to the static `team` VALUES CTE at grain `team`, and a coach is a per-game attribute, so it cannot join there at all. Stating this explicitly so a later reviewer does not force the merge. **Do NOT inherit the `player_nfl_teams` pattern.** It lives in `player-team-column-definition.mjs` — not `game-column-definitions.mjs`, as an earlier draft miscited — and its own comment calls it the last correlated per-row subquery in the family, expensive, measured on production, with only one of its two shapes justified. Take the array-aggregation SEMANTIC (a set, because no single value is correct for a team that changed callers mid-season) without the correlated-subquery shape. ### Knowledge Base Impact - **Observation candidate** on `user:repository/active/league/ABOUT.md`: the 2026 forecast-row behavior, which justifies the season-grain aggregation decision. - **Relations**: `blocked_by` `████████████████████████████████████████████████████████████`; `follows` the plays-view task. - **Bulletin**: three In Progress sessions are tuning against the generation prompt this work grows. Post before the change lands. - **No new tag, no scratch slug.** ## Notes - **`yarn test:data-view-queries` runs ONLY `test/libs-server.data-view-queries.mjs`** (verified in `package.json:25`). The result-equivalence harness is `test/libs-server.data-view-queries-result-equivalence.mjs` and is collected by bare `yarn test:isolated` alone. Name both; neither is reachable by any `.spec.mjs` glob. - **Gate ordering is fix code, then regenerate, then EXPLAIN** (`docs/guides/data.md`). Running the SQL-validity gate before regeneration only re-blesses whatever is there. - **The SQL-validity gate emits the plain and `year_offset` shapes only.** It is silent on every output-aggregator path, which is exactly what these params will carry. A GATE OK says nothing about them; write a throwaway probe over the `(column, period, aggregation)` cross product. - Run the SQL-validity gate from a clean worktree at HEAD — it reads the working tree, and this tree is shared. ## Plan ### Prerequisites - [ ] **Inherit the side ruling and the generated option-set contract** - Purpose: both are owned elsewhere and both change the code written here. - Changes: the side ruling is SETTLED — `offense_nfl_team` canonical, league 76c17eede, deployed ca88ba220. Read the option-set module contract from `████████████████████████████████████████`. - Verify: both are recorded as settled facts with a citation, not as expectations. If the generator has not landed, this task stays blocked rather than shipping a hand-maintained list — a stale list on a SELECT control is unfilterable for an absent coach, which is strictly worse than TEXT. ### Core Implementation - [ ] **Declare the four sided coach params** - File: `libs-shared/nfl-plays-column-params.mjs` - Purpose: make coach attribution a filter dimension on every from-plays column. - Changes: add `offense_head_coach`, `defense_head_coach`, `offense_play_caller`, `defense_play_caller` as SELECT params, each declaring its side team column, its `nfl_game_coaches` role column, and the NAME of its generated per-role option-set module. - Verify: the four keys appear in `play_filter_param_keys` for `player_pass_yards_from_plays`, AND `param_overrides` did NOT grow **as a DELTA measured immediately before and after this change in the same working tree** — never against a hardcoded baseline. What proves the keys stayed out of `common_column_params` is that the before and after totals are equal, whatever their absolute value. - **The 608,792 absolute baseline this step used to cite is RETIRED — do not restore it.** Thread 0e622418's plan reclaims 339,903 bytes from `param_overrides` (the `nfl_week_id` duplication across 27 re-declaring columns, 22.1 percent of the whole data-view catalog). Whichever of the two lands second would fail a fixed-baseline assertion while being entirely correct, and the likely reaction — adjusting the constant until it passes — destroys the only signal the check carries. A delta is immune to that because it measures this change's own contribution. - Related cost facts, corrected 2026-09-10: a play-filter param serializes as a bare NAME and never its values (`build-data-view-generation-catalog.mjs:296-314`), about 20 bytes per column regardless of cardinality. So a coach param is nearly free in play-filter position and would cost roughly 113,000 bytes if it ever landed in `param_overrides` across many columns. That asymmetry, not cardinality, is what this assertion is actually guarding. - [ ] **Apply the params with a side-aware join** - File: `libs-server/apply-play-by-play-column-params-to-query.mjs` - Purpose: the existing `nfl_games_params` branch joins one row per game and cannot express a per-team join. - Changes: add a coach branch reusing the `nfl_games_joined` guard, joining `nfl_game_coaches` once per side with its own guard, emitting `whereIn` against the role's id column. Decide and record whether the coaches join is INNER (drops preseason and null-side plays from a filtered result) or LEFT with an explicit predicate. - Verify: a request carrying a coach param AND `roof` emits exactly ONE `nfl_games` join; a request carrying two offense-side coach params emits exactly ONE `nfl_game_coaches` join. Anchor each assertion on the table ALIAS, and run a separate negative control per call site — a matcher on a bare column name matches the other path's predicate and passes with your call site deleted. - [ ] **Record the alias-hash move rather than preventing it** - Purpose: the operator has ruled the cache bust acceptable; the risk is now an unrecorded change, not the change itself. - Changes: capture the emitted alias for a representative from-plays request before and after, in a worktree at HEAD and in this tree. Do NOT refactor `has_value` across the registry — that widens blast radius into shared machinery for a future benefit and belongs in its own task. - Verify: the aliases DIFFER (confirming the move happened as predicted) and the regex matched a NON-ZERO count in both trees — a zero-match regex reports "identical" and proves nothing. Note `get_data_view_results_query` returns `{ data_view_metadata, query }`, so `String(result)` yields `[object Object]`. - [ ] **Add the three team coach columns** - File: `libs-server/data-views-column-definitions/game-column-definitions.mjs` - Purpose: make the dimension visible and sortable; a filter on something invisible is half a feature. - Changes: add `team_head_coach`, `team_offense_play_caller`, `team_defense_play_caller` modeled on `game_source`'s attach, including its player-family branch. Single coach at week grain; a set aggregated across games at year grain or no axis. Each description states the preseason gap, the 1999 start, and that 2026 reflects scheduled assignments for unplayed games. - Verify: `test/libs-server.data-views-column-coverage.spec.mjs` passes, and a `sort_expressions` entry exists for each — the select-ordinal lookup drops an unresolvable sort in SILENCE rather than failing. - [ ] **Add the client field definitions** - File: `app/core/data-views-fields/game-table-fields.js` - Purpose: the client cannot render a column with no field definition. Use the existing file rather than a new one; it is already wired. - Changes: declare the three fields naming their generated option-set modules, plus header labels. - Verify: the field parity spec passes, including its `select_as` byte-length assertion against the 60-byte budget. ### Testing & Validation - [ ] **Prove the mid-season-change case returns every coach** - Purpose: 76 team-seasons carry an in-season offensive-caller change, and a single-coach team cannot distinguish a correct aggregation from one returning an arbitrary single value. - Changes: add a result-equivalence fixture seeding a team with two different `offense_play_caller_id` values across two games in one season, asserting the year-grain cell contains BOTH. - Verify: the fixture fails at HEAD in a clean worktree before it passes here. List BOTH the week-grain and year-grain columns in `compare_columns` — a subset silently exempts every column left out, and a fixture passing with the aggregation deliberately broken is the finding, not a reassurance. - [ ] **Probe the output-aggregator cross product** - Purpose: the SQL-validity gate emits only the plain and `year_offset` shapes, so it is structurally blind to every path these params will actually carry. - Changes: write a throwaway probe building each `(column, period, aggregation)` combination with a coach param set and `EXPLAIN`ing it. - Verify: every statement EXPLAINs. Assert the probe's own denominator so a matcher that stops matching cannot report full coverage over nothing. - [ ] **Run the param-coverage gates** - Changes: run `check-saved-view-param-coverage.mjs`, `check-data-view-url-param-coverage.mjs`, and `yarn check:cluster`. - Verify: each reports GREEN **and** each has been shown able to report — delete one live rename rule, confirm it reports, restore it. Record explicitly that neither gate can see a param VALUE change, so their green says nothing about the `coach_id` values persisted here; the id's immutability is what covers that, not the gates. - [ ] **Run the SQL-validity gate and the suite, in that order** - Changes: `yarn test:isolated` first; regenerate any golden that fails only after confirming the code is right; then run `db/gates/check-data-view-sql-validity.mjs` from a clean worktree at HEAD. - Verify: suite green and GATE OK. Diff each regenerated fixture at TOKEN level — these are single-line JSON, so a line diff is one unreviewable hunk. A gate finding on a table this cluster never touched is a sibling's in-flight work; check `git status` before attributing it. ### Knowledge Base Updates - [ ] **Post a bulletin before the change lands** - Purpose: three In Progress sessions are tuning against the generation prompt this work grows and against catalog figures it moves; without notice they re-measure a baseline that shifted under them. - Changes: `base bulletin post` naming the catalog delta and the alias-hash move. - Verify: the bulletin is live and names both changes. - [ ] **Record the 2026 forecast-row behavior as an observation** - File: `user:repository/active/league/ABOUT.md` - Changes: `base entity observe`, one positional observation. - Verify: `base entity get` shows it present. - Standing note, not a checklist item: this task proposes NO DDL. If implementation discovers an index is needed, it ships in the same commit as every file here.