---
title: Harden the period-CTE scope emission to a durable end state
type: task
status: completed
description: >-
  The period-CTE scope pushdown (league d262fdb72) left correctness resting on an unenforced
  nfl_plays/nfl_games agreement, a plan sitting on a knife-edge misestimate, and two period builders
  that disagree. Close all three and time the shapes that were never timed.
base_uri: user:task/league/data-views/harden-period-cte-scope-emission-end-state.md
created_at: '2026-08-22T00:37:20.426Z'
entity_id: d5c0d3e1-eb41-4161-82e3-07f93044d7a0
finished_at: '2026-08-22T14:40:52.192Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - >-
    relates
    [[█████████████████████-██████████████-████████-███████████-██████-██████-███-████-██████████]]
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-08-22T14:40:52.192Z'
visibility_analyzed_at: '2026-11-05T23:48:53.472Z'
---

# Harden the period-CTE scope emission to a durable end state

## Context

`league d262fdb72` moved the output-aggregator period CTE's view scope off `nfl_games` and onto the fact relation (`nfl_plays`, plus the cohort alias `pg`), leaving `nfl_games` with only the components the fact table cannot express. That took the Wide Receiver Overview statement from 5.6s to 3.3s on production (16.1s cold, signal 126307) with an identical row set, and it is deployed (`eac6bb44d`).

It also left three things a durable end state should not carry. Each is stated with what was measured, so this task can be executed without re-deriving any of it.

## Plan

- [x] **Enforce the agreement the correctness now rests on.** Dropping the `nfl_games` year and season-type predicates is sound only because a play agrees with its game on `(season_year, season_type, week)`. Measured 2026-08-21 on production: 0 mismatches across all 1,487,212 rows of `nfl_plays`, and 23,858 plays with NO `nfl_games` row at all (2013/2014 PRE, plus 336 in 2020/2021 REG). Nothing enforces the agreement — no foreign key, no CHECK, no importer assertion — so a future importer defect changes data-view NUMBERS rather than raising. Decide where the invariant belongs (a gate in `db/gates/`, a CHECK, or an importer assertion) and land it with a negative control.
- [x] **Stabilize the plan, or accept it explicitly.** The winning hash-join plan and the losing nested-loop plan sit on nearly equal costs, and the estimates are off by three orders of magnitude (3,268 estimated against 1,310,769 actual). A stats refresh or another season of data can flip it back to the 6.3s shape with no code change. Evaluate `CREATE STATISTICS` on `nfl_plays (esbid, offense_nfl_team)` — the per-loop estimate was 4 rows against 74 actual — measure whether it moves the estimate, and either land it or record why not.
- [x] **Resolve the two builders disagreeing.** `build_batched_period_cte` de-duplicates its scope; `build_role_union_period_cte` still emits the duplicate on `nfl_games`. That was deliberate (its arms are pre-aggregated and it was not in the slow path) but it is an inconsistency the next reader may "repair" in the wrong direction. Measure a role-union period CTE both ways and either de-duplicate it or pin the asymmetry with a spec that states why.
- [x] **Time the shapes that were never timed.** The 2,460 aggregator EXPLAINs proved VALIDITY, not timing. `gamelogs`-sourced CTEs now carry the year on `player_gamelogs` and the season type on `nfl_games` — split across two relations where both used to sit together. Sweep the (source, period, aggregation) matrix for wall-clock regressions against the pre-change emitter, and report anything slower.

## Verification

- The invariant gate fails on a deliberately mismatched row and passes on production data.
- Any plan-stability change is judged on `EXPLAIN (ANALYZE, BUFFERS)` of the signal-126307 statement staying under 5000ms with no all-partition `Parallel Append`, measured against a back-to-back control.
- `check-data-view-sql-validity`, the goldens, and the full suite stay green, run from a worktree holding only this task's files — the shared tree had a sibling mid-change on 2026-08-21 and a regeneration there blessed their in-flight output.

## Key locations

- `libs-server/data-views/output-aggregator/build-period-cte.mjs` — both builders
- `libs-server/data-views/physical-season-columns.mjs` — which scope components a relation carries
- `libs-server/data-views/apply-scope-to-query.mjs` — `has_year` / `has_seas_type` / `has_nfl_week_id`
- `test/data-views.output-aggregator.spec.mjs` — pins that the year is on the fact scan and NOT duplicated
- `test/libs-server.physical-season-columns.spec.mjs` — schema-derived map coverage, fact-source registration