---
title: Measure-First Refactor of Data-View From-Plays Rate Types
type: task
status: completed
description: >-
  Replace the strip_outer_sum measure-inference heuristic in the data-views from-plays column system
  with an explicit measure-first contract; fix all silent rate-type drops (weighted opportunity,
  time-to-throw) and COUNT(DISTINCT) bugs (series/drive counts), add distinct-count rate support,
  unify rounding, install a fail-fast invariant, and retire the dead legacy rate-type dispatch.
base_uri: user:task/league/refactor-data-view-from-plays-measure-first-rate-types.md
created_at: '2026-06-17T04:13:56.554Z'
entity_id: 3f4c9826-72a0-4149-a334-55497b770378
finished_at: '2026-06-17T06:15:36.505Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[█████████████████████████-████-████-████-████-███-████-███████████]]
  - relates [[user:task/league/add-per-player-touch-and-opportunity-rate-types.md]]
  - relates [[██████████████████████████████-████-███████]]
started_at: '2026-06-17T05:10:13.724Z'
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-06-20T18:03:52.801Z'
visibility_analyzed_at: '2026-11-05T23:49:58.259Z'
---

# Implementation Plan: Measure-First Refactor of Data-View From-Plays Rate Types

## Overview

- Replace the fragile measure-inference heuristic (`strip_outer_sum`) in the XO.football data-views from-plays column system with an explicit, declarative measure-first contract, so a column declares its per-row measure once and the system derives the season render, every rate form, the advertised rate types, and rounding from that single source of truth.
- Fix the full class of latent rate-type bugs this heuristic causes: rate types silently dropped for wrapped expressions (`player_weighted_opportunity_from_plays`, `player_time_to_throw_from_plays`) and malformed SQL for `COUNT(DISTINCT ...)` measures (`team_series_count_from_plays`, `team_drive_count_from_plays`, and their player-identity variants).
- Add genuine distinct-count rate support so series-per-game and drives-per-game compute correctly, and unify rate-output rounding with season-total rounding.
- Retire the dead legacy rate-type dispatch left behind by the prior output-aggregator migration (`rate_type_column_mapping`, `get_rate_type_sql`, and dead `rate_type` branches inside `main_where`/`main_where_group_by`/`should_use_main_where`/`with_where`), without disturbing the live plain-filter, plays-view, year-offset numerator/denominator, or `with_where` suppression paths those same methods serve.
- Install a permanent fail-fast invariant so a column that advertises any rate type but cannot deliver one throws at module load, making the silent-drop regression class structurally impossible.
- Impact: users selecting per-game (or any rate) on weighted opportunity and series/drive counts get correct values instead of season totals or broken SQL; future column authors get a self-documenting contract and a loud failure instead of a silent wrong number. The data-views rate subsystem reaches a clean long-term state with no dead scaffolding.
- Ordering: follows the completed `██████████████████████████████████████████████` and `add-per-player-touch-and-opportunity-rate-types` work (same subsystem). No downstream task is blocked; this closes out the rate-type correctness debt in that subsystem.

## Background

- The data-views rate engine was migrated to an "output aggregator": `normalize_output_param` rewrites legacy `params.rate_type` into `params.output = {period, aggregation, threshold}` (`libs-server/data-views/normalize-output-param.mjs:21-30`), and the dispatch loop at `libs-server/get-data-view-results.mjs:1845-1874` routes a column through `apply_output_aggregator` only when both `params.output` is present and `column_def.supports_output` is truthy (gate at `:1854`).
- For stats-from-plays columns, `supports_output` and `measure_expr` are auto-derived only when `strip_outer_sum(with_select_string)` returns non-null (`libs-server/data-views-column-definitions/player-stats-from-plays-column-definitions.mjs:92-118`). `strip_outer_sum` (`libs-server/data-views/strip-outer-sum.mjs:25-37`) returns non-null only for a bare `SUM(...)` or `COUNT(...)`; it returns null for `ROUND(SUM(...))`, `AVG(...)`, and arithmetic.
- The same `with_select_string` is also the season-total render (`with_select` returns `[with_select_string AS stat_name]`, `:141-154`). One string serves both the season render and the measure the rate engine must recover — the root design flaw.
- Confirmed bug scope: `player_weighted_opportunity_from_plays` (`...player-stats...:674`, `ROUND(SUM(...),2)`) and `player_time_to_throw_from_plays` (`...player-stats...:1007`, `AVG(...)`) silently drop rate types. `team_series_count_from_plays`/`team_drive_count_from_plays` (`...team-stats...:378-384`, `COUNT(DISTINCT CONCAT(...))`) plus their `force_player_active` player-identity variants (generated by the spec spread at `...team-stats...:409-416`) produce malformed SQL: `strip_outer_sum`'s COUNT branch returns the inner `DISTINCT CONCAT(...)`, which `build_batched_period_cte` then wraps as `SUM(DISTINCT CONCAT(...))` (`build-period-cte.mjs:350-351`) — invalid. Columns with `supported_rate_types: []` (share-stats, AVG/CPOE family) are intentionally safe.
- Four from-plays factories exist with divergent mechanisms: `player-stats-from-plays` and `team-stats-from-plays` use `strip_outer_sum` (both carry the bug); `player-fantasy-points-from-plays` uses `role_attributions`/`measure_total` (no bug); `defensive-player-stats-from-plays` declares `supports_output` explicitly and uses `role_attributions` with explicit `measure_expr: '1'` (no bug). All four still carry the dead legacy rate-type branches and import `get_rate_type_sql` directly (`player-stats...:7,176`; `team-stats...:8,203`; `defensive...:5,100`; `fantasy-points...:625-626`).
- The rate output emitters apply no rounding: `emit-rate-outer-select.mjs:73` (legacy denominator plugins), `aggregator-rate.mjs:176`, `aggregator-count.mjs:135`. Season totals carry baked-in `ROUND` — a presentation inconsistency.
- Dead legacy scaffolding (confirmed via call-graph audit): `rate_type_column_mapping` is permanently `{}` (sole writer removed in commit `66667fc28`; declared at `get-data-view-results.mjs:1559` and param default `:992`; all remaining references are reads yielding `undefined`, including `select-string.mjs:99-100,115-124,238-243,252-255`). `get_rate_type_sql` (`select-string.mjs:63-68`) is reached only through those always-`undefined` guards. `rate_type_tables` is already removed (tombstone comment at `get-data-view-results.mjs:1843`).
- The `main_where`/`with_where`/`should_use_main_where`/`main_where_group_by` methods are LIVE for non-rate work and MUST be preserved: plain filters and the plays-view consume `main_where`/`with_where` (`where-string.mjs:32-36`; `libs-server/plays-view/get-plays-view-results.mjs:222-223`); `should_use_main_where` and `main_where` also gate the LIVE year-offset numerator/denominator assembly (`player-stats...:24-31,170-214`; the correlated-subquery SUM at `select-string.mjs:235`). DEAD and to be removed: the `rate_type_table_name`/`get_rate_type_sql` portions, the `params.rate_type` disjunct in `should_use_main_where`, and the `with_where` `rate_type -> return null` suppression branch (`team-stats...:183-185`) — all unreachable because `normalize_output_param` rewrites `rate_type` to `output` before these run.
- Data-view results are cached in redis keyed on `get_data_view_hash({table_state})` (the user config), NOT on the generated SQL (`api/routes/data-views.mjs:952,978`). Changing SQL output shape therefore does NOT bust existing cache entries; stale (wrong) values would be served until TTL (6h current season / 30d prior). A cache flush on deploy is required.
- The aggregator numerator CTE re-materializes the measure against `nfl_plays`; `build_batched_period_cte` emits `SUM(${m_expr}) AS ${alias}` unconditionally in its measure loop (`build-period-cte.mjs:350-351`). The non-batchable branch in `add_period_cte` (`:480-493`) routes exclusively to `build_role_union_period_cte`, which throws without `role_attributions` (`:157-161,280-282`). Distinct counts therefore cannot be fixed by "making them non-batchable"; the correct fix is a per-measure aggregate selector in the batched loop (see Design).

### Assumptions

- The season-total (no-output) render path stays the plain `with`-CTE path and continues to work when fed a factory-generated `with_select` string; the `with` builder selects the pre-computed string verbatim (`add-player-stats-play-by-play-with-statement.mjs`), so it never calls the deriver at query time. (Checked: `with_select` is produced at construction; the change is generating its string from `measure` instead of accepting it raw.)
- `COUNT(DISTINCT key)` and `SUM(expr)` measures can coexist in one CTE under the same `(pid|team, year)` GROUP BY (both are valid aggregates over the same scan), so distinct-count measures stay batchable; the only change needed is a per-measure aggregate selector, not a batchability exclusion. (Checked: SQL semantics; `compute_group_key` does not include the aggregate, so co-location is already keyed only on scan identity — `measure-batch.mjs:39-66`.)
- Averages and compound ratios have no meaningful per-period rate and stay as raw `with_select_string` carve-outs with `supported_rate_types: []` (no `average` kind invented). (Domain assumption; matches existing `supported_rate_types: []` on every current AVG/ratio column except the mis-configured `time_to_throw`, which gains `[]`.)
- `decimals` defaults to null, meaning no `ROUND` wrapper on either season or rate render; this preserves exact season-total parity for the dozens of bare-`SUM` integer columns (which round nowhere today) and matches today's unrounded rate emit. Only columns that explicitly declare `decimals` (e.g. weighted_opportunity = 2) round. (Checked: surveyed bare-`SUM` columns in both factories emit no `ROUND` today.)
- `year_offset_range`/`has_numerator_denominator` columns use a distinct `with_select` shape (numerator + denominator selects) and already bypass `strip_outer_sum` (`can_auto_derive` requires `!has_numerator_denominator`, `player-stats...:94`); the measure-first migration must leave that branch intact and route only the single-aggregate columns through the deriver. (Checked: factory branch at `:141-154`.)
- The `/api/data-views/debug` endpoint and `yarn debug:data-view` reproduce generated SQL deterministically and bypass redis cache, usable as the regression harness. (Checked: used during diagnosis.)
- No frontend code change is required: the column-config UI reads `supports_output.periods`/`aggregations` and `supported_rate_types`, which the factory continues to emit (now derived from kind). `time_to_throw` losing its rate options is the intended correction; existing saved per-game `time_to_throw` views already render the season average today (the current bug), so the fix introduces no new degradation. (Checked: UI consumes these fields; behavior parity confirmed against current dispatch.)

## Design

- High-level approach: introduce an explicit `measure: { kind, expr, decimals }` field on the RATE-CAPABLE single-aggregate from-plays columns, where `kind` is the closed set `additive` | `distinct_count` (snake_case value literals, distinguishing-property names per write-software). One shared module converts `measure` into all downstream artifacts and enforces rate capability; `strip_outer_sum` is deleted. Non-rate columns (averages, compound ratios, numerator/denominator ratios) are NOT given a `measure` — they keep their existing `with_select_string` verbatim and declare `supported_rate_types: []`. There is intentionally no `average` kind: an average is not summable per period, never reaches the aggregator, and its bespoke season string (`CAST(ROUND(AVG(...)))`, `AVG(x)*100`, `CASE WHEN SUM>0 THEN ROUND(...)`) would be fragile to regenerate — regenerating a string we already have is pure downside (the review's "what should NOT exist" lens).
- Derivation table (kind drives all behavior):
  - `additive`: season render `SUM(expr)` when `decimals` is null (default), else `ROUND(SUM(expr), decimals)`; numerator aggregate `SUM(expr)`; rate render rounds iff `decimals` set; `supports_output` present; full rate set.
  - `distinct_count`: season render `COUNT(DISTINCT expr)` (always bare — integer count, no season ROUND); numerator aggregate `COUNT(DISTINCT expr)`; rate render `ROUND(.../games, decimals)` with `decimals` default 2 (fractional per-game); `supports_output` present; full rate set.
- `decimals` semantics: a single column property, default `null`. When null, neither season nor rate wraps in `ROUND` (matches today's behavior for the dozens of bare-`SUM` integer columns AND today's unrounded rate emit). When set, it rounds the additive season render and both kinds' rate render. This both PRESERVES season parity for bare-`SUM` columns (no spurious `ROUND(...,2)` / integer-to-numeric wire-type change) AND fixes the weighted-opportunity inconsistency (it declares `decimals: 2`, so season stays `ROUND(SUM(CASE...),2)` and its rate now rounds too).
- Column-to-`measure` mapping rules (applied to every RATE-CAPABLE single-aggregate column in both factories — the contract changes for all of them, not only the buggy ones): `SUM(expr)` -> `{additive, expr}` (decimals null); `ROUND(SUM(expr), n)` -> `{additive, expr, decimals: n}` (e.g. weighted_opportunity, strip the outer ROUND into `decimals`); `SUM(CASE ... THEN n ELSE m END)` -> `{additive, expr: the CASE}`; `COUNT(*)` -> `{additive, expr: '1'}`; `COUNT(CASE ... THEN 1 ELSE NULL END)` -> `{additive, expr: the CASE with ELSE 0}`; `COUNT(DISTINCT k)` -> `{distinct_count, expr: k}`. Columns retaining an explicit table-qualified `measure_expr` override today (e.g. `player_receiving_yards_from_plays`) keep that override; the deriver's `measure_expr` is only the default.
- Explicit carve-outs (NOT routed through the deriver — keep raw `with_select_string` + `supported_rate_types: []`): every `AVG(...)` column (`time_to_throw`, `pass_rate_over_expected`, `completion_percentage_over_expected`, `expected_completion_percentage` = `AVG(cp)*100`, `box_defenders` = `CAST(ROUND(AVG(...)))`), every compound-ratio column (`CASE WHEN SUM>0 THEN ROUND(100.0*SUM/...) ELSE 0 END`) — including the two that carry `supported_rate_types: []` WITHOUT `has_numerator_denominator` (`player_successful_passing_play_percentage_from_plays`, `player_successful_rushing_and_receiving_play_percentage_from_plays`), which must be left as carve-outs and not mistaken for rate-capable — and every `has_numerator_denominator`/year-offset ratio column (existing numerator/denominator path, untouched). The ONLY change to these is adding `supported_rate_types: []` where missing — specifically `player_time_to_throw_from_plays`, which currently relies on `strip_outer_sum` returning null and must now declare `[]` explicitly.
- Fail-fast invariant (scoped to the two migrated factories — NOT a global registry sweep): inside `player_stat_from_plays`/`team_stat_from_plays`, a column that advertises any rate type MUST declare a `measure` (`additive`/`distinct_count`); a column left on a raw `with_select_string` MUST pass `supported_rate_types: []`. Violations throw at module load. This catches the `time_to_throw` class structurally (it throws until given either a measure or `[]`). The `defensive`/`fantasy-points` factories use `role_attributions`/explicit `supports_output`, never call `derive_measure`, and are exempt by construction — a global sweep would wrongly throw for them, so the check lives inside the two factories only.
- `create_team_share_stat` columns (8: the rush/target/opportunity/air-yards/receiving-first-down shares and weighted-opportunity-rating) are a SEPARATE factory with their own `with` builder, no `strip_outer_sum` dependency, and no `supports_output` — they are non-rate by construction and entirely untouched by this work (neither migrated nor given a `measure`). The population sweep spec must exempt them explicitly.
- New module (one file, same-directory sibling under `libs-server/data-views/`, `.mjs`, named-parameter destructuring, leading role comment, well under the 200-line soft limit): `measure-contract.mjs` exporting `derive_measure({ stat_name, measure })` returning `{ with_select, measure_expr, aggregate, supports_output, supported_rate_types, decimals }`, plus a private guard `assert_measure_rate_capability` invoked inside `derive_measure`. The factory spreads the returned `aggregate` and `decimals` onto the column-def object so the engine and emit sites can read `column_def.aggregate` / `column_def.decimals`. Consumers are the two measure-first factories only; defensive and fantasy-points hand-construct their column objects and do not call it. `derive_periods_from_rate_types` (currently in `strip-outer-sum.mjs`) moves into this module.
- Distinct-count engine (full wiring — the previously-glossed call chain): (1) `add_period_cte` (`build-period-cte.mjs:440-477`, the sole batchable caller) reads `column_def.aggregate` (default `'sum'`) and passes it to `register_measure` and into `compute_measure_alias`; (2) `compute_measure_alias` (`measure-batch.mjs:68-82`) includes `aggregate` in its hash input so a `sum` and a `count_distinct` of the same `(column_id, expr)` never collide-and-dedup; (3) `register_measure` (`:101-121`) changes the batch map VALUE from a bare `measure_expr` string to an object `{ measure_expr, aggregate }`; (4) `flush` (`:126-141`) emits `{ alias, measure_expr, aggregate }` entries; (5) `build_batched_period_cte`'s loop (`:350-351`) emits `COUNT(DISTINCT ${m_expr}) AS ${alias}` when `aggregate === 'count_distinct'`, else `SUM(${m_expr}) AS ${alias}`; (6) the single-measure wrapper `build_period_cte` (`:127-138`) gains an `aggregate` param defaulting `'sum'` for its one-entry list. No batchability change (distinct-count stays batchable; the earlier "exclude from batching" approach is rejected — it would route into the role_union branch that throws at `:157-161`).
- Unified rounding: `emit_rate_outer_select` (`:73`), `aggregator-rate` emit (`:176`), and `aggregator-count` emit (`:135`) already receive `column_def` as a parameter, so no new threading is needed — read `column_def.decimals` inside each and wrap the division in `ROUND(..., decimals)` only when `decimals` is non-null. `aggregator-count` emits a period-count (qualifying buckets), not the measure value; leave it unwrapped (rounding a count is a no-op at best, wrong at worst) — restrict the rounding change to `emit_rate_outer_select` and `aggregator-rate`.
- Dead-code retirement (one coherent, independently revertable phase): remove `rate_type_column_mapping` (declaration `get-data-view-results.mjs:1559`, param default `:992`, all pass-through args) and its reads in `select-string.mjs` — the `:99-100` lookup, the `:115-124` rate branch in `get_select_expression` (keep the `column_value` return), the `rate_type_table_name` KWARG passed to `column_definition.join_table_name({...})` (`:104-109`) and to `select_func({...})` (`:138-155`) — both must be deleted, not left as undeclared references — the `if (rate_type_table_name)` guard AND its body at `:237-243` (keep the LIVE numerator SUM subquery at `:235`), and the `rate_type_table_name ? ... : null` disjunct in the `group_by` (`:253-255`, keep `column_value`). Remove `get_rate_type_sql` (`:63-68`) and its direct imports/calls in all four factories. Remove the tombstone comment (`:1843`). In `should_use_main_where` drop the `params.rate_type` disjunct (keep the year-offset disjunct) AND remove the inner `params.rate_type` sub-branches in `main_where`/`main_where_group_by` of the same files (they reference the now-removed mapping). Remove the DEAD `with_where` `rate_type`-suppression branch (`team-stats...:183-185`; it is reachable only when `params.rate_type` is non-empty, which `normalize_output_param` no longer permits — it is dead, not live). Preserve the LIVE plain-filter/plays-view `main_where`/`with_where` uses, the LIVE `is_rate` ratio path, and the LIVE numerator/denominator assembly.
- Deliberate non-goals (defer with rationale, no workaround forced): (a) unifying the season-total plain-`with` engine with the rate aggregator engine — both feed from the same `measure`, so separation forces no inference or workaround (validated: the two paths diverge into independent SQL after the `measure` declaration). (b) Folding fantasy-points `role_attributions` into the `measure` contract — its multi-role UNION topology is genuinely different; forcing it would be a wrong abstraction. It receives dead-code removal only. (c) Migrating existing saved `time_to_throw` per-game views — they already render the season average under the current bug, so no migration is owed; the only change is the UI ceasing to offer the option going forward.
- Knowledge Base Impact:
  - Domain doc: `repository/active/league/docs/data-views-system.md` (confirmed present) gains a "Measure-first column contract" section (the `measure: {kind, expr, decimals}` shape, the kind-to-rate-validity table, the fail-fast invariant).
  - ABOUT.md: `repository/active/league/libs-server/data-views/ABOUT.md` (if present) lists `measure-contract.mjs` and notes removal of `strip-outer-sum.mjs`.
  - Observation/guideline candidate: a `[design]` observation on `user:guideline/write/write-software.md` — "declare the measure; derive the presentation — never parse a presentation string to recover intent."
  - Relations: task tagged `user:tag/league-xo-football.md`; `relates` to the two completed rate-type tasks and to `███████████████████████████████████████████`.
  - No tag taxonomy changes; no scratch tier; single task entity (not an orchestrator).

## Notes

- Verification harness: the originating short URL `/u/ff8020017620e22917275c2ec6b81397` is the canonical regression fixture (weighted opportunity per-game). Use `yarn debug:data-view /u/<hash> --beautify` and `mcp__db__query_league` to assert and execute generated SQL.
- Population-level validation: assert across the whole affected dimension — every rate-capable from-plays column emits a divisor in its rate SQL, every AVG/carve-out column advertises no rate types, and every migrated column's season-total SQL is unchanged. A per-column sweep, not a spot-check, so a partial regression (a misclassified `COUNT(*)` or AVG column) cannot masquerade as success.
- The largest correctness risk is misclassifying a column's kind, which silently changes its season render. The exhaustive season-parity spec is the mitigation and is a blocking gate before committing each factory migration.
- Order of execution: Phase A lands the inert infrastructure first (measure-contract module, distinct-count `aggregate` engine wiring, decimals-aware rounding) — all default to today's behavior so each is a safe no-op commit. Phase B then migrates the two factories onto that working infrastructure (player-stats, team-stats, delete `strip_outer_sum`), so no commit ever declares a `distinct_count`/`decimals` the engine can't yet honor. Cleanup (dead-code retirement) lands last. Each step independently testable.
- Match existing style in each touched file. The new module follows existing `libs-server/data-views/` conventions (pure functions, named-parameter destructuring, leading role comment).
- Test DB: the spec suites require Postgres >= 15 (schema uses `NULLS NOT DISTINCT`). If the local default is older, run the throwaway PG16-on-5433 harness and invoke mocha directly with `LEAGUE_DB_HOST`/`LEAGUE_DB_PORT` overrides (note: `yarn test` blanks those, so target a non-default port via direct mocha) — see the league repo CLAUDE.md "Testing" section.

## Plan

### Phase A — Inert Infrastructure (lands first; each step is a no-op until columns opt in)

This ordering is required to avoid a broken intermediate commit: the engine's `aggregate` selector and the emitters' `decimals` reads must exist BEFORE any factory declares a `distinct_count` measure or a `decimals`, otherwise a per-game `series_count` query between commits would emit `SUM(CONCAT(...))` (wrong counts). All three steps default to today's behavior (`aggregate` defaults `'sum'`, `decimals` null = no ROUND) so they change nothing until Phase B opts columns in.

- [x] **Create the measure-contract module**
  - File: `repository/active/league/libs-server/data-views/measure-contract.mjs`
  - Purpose: single source converting an explicit `measure: {kind, expr, decimals}` into all downstream artifacts, with the fail-fast guard inlined, replacing `strip_outer_sum` inference.
  - Changes:
    - Implement `derive_measure({ stat_name, measure })` returning `{ with_select, measure_expr, aggregate, supports_output, supported_rate_types, decimals }` per the Design derivation table for the two kinds `additive` and `distinct_count` only (`aggregate` is `'sum'` or `'count_distinct'`; `decimals` defaults null for additive, 2 for distinct_count rate; additive season is bare `SUM(expr)` unless `decimals` set, distinct_count season is bare `COUNT(DISTINCT expr)`).
    - Inline `assert_measure_rate_capability({ stat_name, measure })` that throws ONLY when `derive_measure` is called with a `kind` outside the closed set or a missing `expr`. The broader invariant (a column passing through these two factories that advertises rate types must declare a `measure`; a column left on a raw `with_select_string` must pass `supported_rate_types: []`) is enforced INSIDE the two migrated factories, NOT as a global registry sweep — `role_attributions`/explicit-`supports_output` columns (defensive, fantasy-points) never call `derive_measure` and are exempt by construction. A global sweep would wrongly throw for them at module load.
    - Move `derive_periods_from_rate_types` here from `strip-outer-sum.mjs`.
  - Verify: `yarn test --reporter min test/data-views.measure-contract.spec.mjs` asserts each kind's `with_select` (bare vs ROUND per `decimals`), `measure_expr`, `aggregate`, `supports_output` presence, that an unknown kind throws, and that the factory-level invariant throws for a raw-`with_select_string` column advertising a rate (negative test).

- [x] **Thread a per-measure aggregate selector through the period CTE builders**
  - Files: `repository/active/league/libs-server/data-views/output-aggregator/build-period-cte.mjs`, `repository/active/league/libs-server/data-views/output-aggregator/measure-batch.mjs`
  - Purpose: emit `COUNT(DISTINCT expr)` numerators for distinct-count measures while keeping them batchable. Lands before any column declares `distinct_count`; defaults to `SUM` so behavior is unchanged.
  - Changes (full call chain — each link is required or `aggregate` silently defaults to sum):
    - `add_period_cte` (`build-period-cte.mjs:440-477`, the sole batchable caller): read `column_def.aggregate` (default `'sum'`) and pass it to both `compute_measure_alias` and `register_measure`.
    - `compute_measure_alias` (`measure-batch.mjs:68-82`): include `aggregate` in the hash input so a `sum` and `count_distinct` of the same `(column_id, expr)` never collide-and-dedup in the batch map.
    - `register_measure` (`measure-batch.mjs:101-121`): change the batch map VALUE from a bare `measure_expr` string to `{ measure_expr, aggregate }`; add the `aggregate` param.
    - `flush` (`measure-batch.mjs:126-141`): update the map-to-entries reconstruction to emit `{ alias, measure_expr, aggregate }` (it currently destructures the value as a bare string — this MUST change atomically with `register_measure`).
    - `build_batched_period_cte` loop (`build-period-cte.mjs:350-351`): emit `COUNT(DISTINCT ${m_expr}) AS ${alias}` when `aggregate === 'count_distinct'`, else `SUM(${m_expr}) AS ${alias}`.
    - `build_period_cte` (`:103-138`): add an `aggregate` param defaulting `'sum'`, carried into its one-entry `measures` list.
    - Do NOT change `is_batchable` (distinct-count stays batchable; the "exclude" approach would route into the role_union branch that throws at `:157-161`).
  - Verify: a spec asserts an additive measure still emits `SUM(...)` (unchanged default) and that a synthetic `count_distinct` measure emits `COUNT(DISTINCT ...)` and that the two coexist in one CTE; `yarn test --reporter min test/data-views.distinct-count-numerator.spec.mjs`.

- [x] **Apply decimals rounding at the value-rate emitters**
  - Files: `repository/active/league/libs-server/data-views/rate-type/emit-rate-outer-select.mjs`, `repository/active/league/libs-server/data-views/output-aggregator/aggregator-rate.mjs`
  - Purpose: make rate-value rounding consistent with season rounding for columns that declare `decimals`. Lands before any column declares `decimals`; null = no ROUND, so behavior is unchanged.
  - Changes: both emit functions already receive `column_def`, so no new threading — read `column_def.decimals` inside each and wrap the division in `ROUND(..., decimals)` ONLY when `decimals` is non-null, at `emit-rate-outer-select.mjs:73` and `aggregator-rate.mjs:176`. Do NOT touch `aggregator-count.mjs:135` — it emits a qualifying-period count, not the measure value, so rounding it is meaningless/wrong.
  - Verify: a spec asserts a synthetic column with `decimals: 2` wraps its rate division in `ROUND(..., 2)` on BOTH emit paths and a `decimals: null` column emits no `ROUND`; `yarn test --reporter min test/data-views.rate-rounding.spec.mjs`.

### Phase B — Column Migration (lands on the working infrastructure from Phase A)

- [x] **Migrate player-stats-from-plays factory to measure-first**
  - File: `repository/active/league/libs-server/data-views-column-definitions/player-stats-from-plays-column-definitions.mjs`
  - Purpose: remove `strip_outer_sum` inference for rate-capable columns; declare measures explicitly; fix `weighted_opportunity` (additive); fix `time_to_throw` by declaring it a non-rate carve-out; preserve the numerator/denominator year-offset path.
  - Changes:
    - Replace the `strip_outer_sum` auto-derive block in `player_stat_from_plays` with `derive_measure(...)` for the rate-capable single-aggregate path; spread the returned `aggregate`/`decimals`/`supports_output`/`supported_rate_types` onto the column-def; leave the `has_numerator_denominator`/`year_offset.length>1` branch (`:141-154`) untouched and do not route it through the deriver.
    - Convert EVERY rate-capable single-aggregate column to declare `measure: {kind, expr, decimals}` per the Design mapping rules: `weighted_opportunity_from_plays` -> `{additive, expr: the CASE, decimals: 2}` (strip the outer ROUND into `decimals`); bare-`SUM` columns -> `{additive, expr}` (no `decimals`); `SUM(CASE...)` -> `{additive, expr: the CASE}`; `COUNT(*)` -> `{additive, expr: '1'}`; `COUNT(CASE...)` -> ELSE-0 CASE. Columns with an existing explicit table-qualified `measure_expr` (e.g. `player_receiving_yards_from_plays`) keep that override.
    - Carve-outs (do NOT route through deriver): add `supported_rate_types: []` to `player_time_to_throw_from_plays` and confirm the other `AVG`/compound-ratio columns already carry `[]`; their `with_select_string` is unchanged.
    - Remove the `strip_outer_sum` import; import `derive_measure`/`derive_periods_from_rate_types` from `measure-contract.mjs`.
  - Verify: `yarn debug:data-view /u/ff8020017620e22917275c2ec6b81397 --beautify` shows `weighted_opportunity_from_plays` divided by `rate_type_total_count` and wrapped in `ROUND(...,2)`; a spec asserts `time_to_throw` exposes no `supports_output`/rate types; the season-parity spec (below) passes for all migrated columns (catching any spurious `ROUND` on bare-`SUM` columns).

- [x] **Migrate team-stats-from-plays factory to measure-first**
  - File: `repository/active/league/libs-server/data-views-column-definitions/team-stats-from-plays-column-definitions.mjs`
  - Purpose: remove `strip_outer_sum`; declare measures explicitly; fix `team_series_count`/`team_drive_count` (distinct_count) and their player-identity variants; preserve the live `is_rate` ratio path.
  - Changes:
    - Replace `auto_inner = is_rate ? null : strip_outer_sum(select_string)` and the auto-derive block with `derive_measure(...)` for rate-capable single-aggregate columns; spread the returned fields onto the column-def; keep the `is_rate` ratio path intact (the `with_where` `rate_type` suppression at `:183-185` is dead and is removed in the cleanup phase, not here).
    - Convert rate-capable single-aggregate columns to `measure: {kind, expr, decimals}`: `team_series_count_from_plays`/`team_drive_count_from_plays` -> `{distinct_count, expr: "CONCAT(esbid,'_',series_seq)"}` (and `drive_seq`); `SUM(...)`/`COUNT(*)`/`SUM(CASE...)` -> `{additive, ...}`. The `force_player_active` player-identity variants inherit kind via the spec spread (`:409-416`).
    - Carve-outs: `AVG(...)` columns (`pass_rate_over_expected`, `completion_percentage_over_expected`) and `is_rate` ratio columns keep their raw string and `supported_rate_types: []`; not routed through the deriver.
    - Remove `strip_outer_sum` import.
  - Verify: a spec builds per-game queries for BOTH `team_series_count_from_plays` AND `player_team_series_count_from_plays`, asserting `COUNT(DISTINCT ...)` over the period plus a divisor; execute via `mcp__db__query_league` and confirm non-null rows; season-parity spec passes.

- [x] **Delete the strip_outer_sum module**
  - File: `repository/active/league/libs-server/data-views/strip-outer-sum.mjs` (delete) and its test (delete)
  - Purpose: remove the dead heuristic now that nothing infers.
  - Changes: `git rm` the module and any `test/*strip-outer-sum*` spec (move any `derive_periods_from_rate_types` test cases into the measure-contract spec).
  - Verify: `grep -rn "strip_outer_sum\|strip-outer-sum" libs-server app test` returns no matches; `yarn lint` passes. (no verify beyond grep+lint — mechanical removal)

### Cleanup: Retire Legacy Rate-Type Dispatch

- [x] **Remove rate_type_column_mapping declaration, threading, and reads**
  - Files: `repository/active/league/libs-server/get-data-view-results.mjs`, `repository/active/league/libs-server/data-views/select-string.mjs`
  - Purpose: delete the permanently-empty mapping and every read that depends on it; remove the tombstone comment.
  - Changes: remove the `rate_type_column_mapping` param default (`get-data-view-results.mjs:992`), the `const rate_type_column_mapping = {}` declaration (`:1559`), and all pass-through arguments; in `select-string.mjs` remove the `rate_type_table_name` lookup (`:99-100`), the `rate_type_table_name` KWARG from the `column_definition.join_table_name({...})` call (`:104-109`) AND the `select_func({...})` call (`:138-155`) so no undeclared reference remains, the `get_select_expression` rate branch (`:115-124`, keeping the `column_value` return), the year-offset-range rate-division `if (rate_type_table_name) {...}` guard AND its body (`:237-243`, keeping the LIVE numerator SUM subquery at `:235`), and the `rate_type_table_name ? ... : null` disjunct in the `group_by` (`:253-255`, keeping `column_value`). Remove the tombstone comment (`get-data-view-results.mjs:1843`).
  - Verify: `grep -rn "rate_type_column_mapping\|rate_type_tables" libs-server` returns no matches; data-views suite green — `yarn test --reporter min test/data-views.*.spec.mjs`.

- [x] **Remove get_rate_type_sql and the dead rate branches in all four factories**
  - Files: `repository/active/league/libs-server/data-views/select-string.mjs` (remove the `get_rate_type_sql` definition `:63-68`), and the dead `rate_type` sub-branches inside `main_where`/`main_where_group_by`/`should_use_main_where`/`with_where` plus the direct `get_rate_type_sql` imports in `player-stats-from-plays`, `team-stats-from-plays`, `defensive-player-stats-from-plays`, and `player-fantasy-points-from-plays`.
  - Purpose: remove the unreachable legacy rate SQL emitter and its callers.
  - Changes: delete the function and the `rate_type`-gated sub-expressions ONLY; in `should_use_main_where` drop the `params.rate_type` disjunct and keep the year-offset disjunct; remove the inner `params.rate_type` sub-branches in `main_where`/`main_where_group_by` (they reference the removed mapping) and the DEAD `with_where` `rate_type`-suppression branch (`team-stats...:183-185`, reachable only when `params.rate_type` is non-empty, which `normalize_output_param` no longer permits). PRESERVE the LIVE numerator/denominator assembly in `main_where`, the LIVE `is_rate` ratio path, and all plain-filter/plays-view `main_where`/`with_where` implementations. Remove now-unused imports.
  - Verify: `grep -rn "get_rate_type_sql" libs-server` returns no matches; `yarn lint`; data-views + plays-view suites green — `yarn test --reporter min test/data-views.*.spec.mjs test/plays-view.*.spec.mjs`.

### Knowledge Base Updates

- [x] **Document the measure-first contract**
  - File: `repository/active/league/docs/data-views-system.md` (confirmed present)
  - Changes: add a "Measure-first column contract" section covering `measure: {kind, expr, decimals}`, the kind-to-rate-validity table, the `aggregate` selector, and the fail-fast invariant.
  - Verify: section renders and cross-links resolve (manual read).

- [x] **Update data-views ABOUT.md**
  - File: `repository/active/league/libs-server/data-views/ABOUT.md` (if present)
  - Changes: list `measure-contract.mjs`; note removal of `strip-outer-sum.mjs`.
  - Verify: file lists the new module (manual read). (no verify beyond read — doc edit)

- [x] **Record the design principle observation**
  - Target: `user:guideline/write/write-software.md`
  - Changes: add `[design] Declare the measure; derive the presentation — never parse a presentation string to recover intent.` via `base entity observe`.
  - Verify: `base entity get user:guideline/write/write-software.md` shows the observation.

### Testing & Validation

- [x] **Regression fixture for per-game weighted opportunity**
  - Changes: add a spec building the weighted-opportunity column from `/u/ff8020017620e22917275c2ec6b81397` and asserting the generated SQL divides by `rate_type_total_count` and wraps in `ROUND(...,2)`.
  - Verify: `yarn test --reporter min test/data-views.weighted-opportunity-per-game.spec.mjs`; live execution via `mcp__db__query_league` returns per-game values below the season totals.

- [x] **Exhaustive season-total parity check (blocking gate for factory migrations)**
  - Changes: add a spec asserting the no-output (season) SQL for EVERY from-plays column in both factories — migrated AND carve-out — is byte-for-byte unchanged pre/post migration. This must include the carve-outs (`AVG(...)`, `CAST(ROUND(AVG(...)))`, compound-ratio `CASE WHEN SUM>0 THEN ROUND(...)`) precisely because deleting `strip_outer_sum` must NOT alter them; capture `COUNT(*)`, `COUNT(CASE)`, `AVG`, `SUM`, `SUM(CASE)`, `ROUND(SUM)`, `COUNT(DISTINCT)` shapes.
  - Verify: `yarn test --reporter min test/data-views.season-parity.spec.mjs`; failure mode caught: a misclassified column kind silently altering the season render, or a carve-out string drifting.

- [x] **Population-level rate-capability sweep**
  - Changes: add a spec iterating every from-plays column definition asserting rate-capable kinds (`additive`/`distinct_count`) emit a divisor when given `output: per_game`, and AVG/compound-ratio carve-out columns (including `time_to_throw`) expose no rate types. Explicitly exempt the 8 `create_team_share_stat` columns (separate factory, non-rate by construction) so the sweep neither false-positives nor silently skips them.
  - Verify: `yarn test --reporter min test/data-views.rate-capability-sweep.spec.mjs`; failure mode caught: a column silently reverting to season-total or a carve-out column re-advertising rates.

- [x] **Full data-views and plays-view suites plus cache flush on deploy**
  - Changes: note in the deploy step that the redis `/data-views/*` cache must be flushed after deploy, because results are keyed on `table_state` hash (not SQL) and would otherwise serve stale wrong values for up to 30 days.
  - Verify: `yarn test --reporter min test/data-views.*.spec.mjs test/plays-view.*.spec.mjs` green; `yarn lint` clean; post-deploy, re-fetch `/u/ff8020017620e22917275c2ec6b81397` and confirm corrected per-game values (cache flushed).