---
title: Consolidate the player_gamelogs bridge scans
type: task
status: planned
description: >-
  Four data-view CTEs scan player_gamelogs joined to nfl_games on REG for the same year_range;
  league d624aded0 removed the last semantic difference between the two week-grain ones, leaving
  mechanical duplication of a 27-partition scan on every week-grain view.
base_uri: user:task/league/data-views/consolidate-player-gamelogs-bridge-scans.md
created_at: '2026-08-23T16:11:27.260Z'
entity_id: 9351389c-c5f1-4739-b627-6c68c9d39bb7
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-08-23T16:11:27.260Z'
visibility_analyzed_at: '2026-11-05T23:48:45.880Z'
---

# Consolidate the player_gamelogs bridge scans

## Context

Four data-view CTEs scan `player_gamelogs ⋈ nfl_games` on REG for the same
`year_range`, and league `d624aded0` removed the last semantic difference
between two of them. What is left is mechanical duplication.

| CTE | Builder | Grain | Aggregate |
| --- | --- | --- | --- |
| `player_years_teams` | partition-union, `player-team-bridge-cte.mjs` | pid, year | `array_agg(DISTINCT team_code)` |
| `player_years_weeks_teams` | partition-union, same file | pid, year, week | `array_agg(DISTINCT team_code)` |
| `player_participation_weeks` | partition-union, same file | pid, year, week | `bool_or(is_active)` |
| `player_year_teams` | parent scan, `identity-bridges/player-year-to-team-year.mjs` | pid, year | dominant team |

## Scope

**The week pair is the clear win.** `player_years_weeks_teams` and
`player_participation_weeks` are now byte-identical in FROM, WHERE and GROUP BY
key — they differed only by the `pgl.is_active = TRUE` predicate that
`d624aded0` removed. A week-grain view carrying both materializes the same
27-partition union twice. Collapse them into one CTE projecting both `teams`
and `is_active`.

Registration is the real work, not the SQL: the two are registered by different
subsystems on different triggers — `team-table-column-definitions.mjs`
(`get_player_bridge`, team-identity columns) and `participation-status-cte.mjs`
(`register_participation_status_ctes`, auto-injected for week-grain views). The
merged CTE has to register once when either path needs it. The `columns`
extension point on `player_participation_weeks_cte_sql` already exists for
adding aggregates and is the natural seam.

**The year pair is a bigger change and may not be worth it.**
`player_years_teams` and `player_year_teams` share FROM/WHERE and `GROUP BY pid,
year`, differing only in aggregate, but they live in different modules with
different registration paths (one a column-definition helper, one an identity
bridge with its own `add_cte`/`join_cte` contract). Decide explicitly rather
than doing it by momentum.

## Which builder shape to keep

The parent scan with `season_year IN (...)`. Measured on production over the
default 2000-2026 range, the two shapes are execution-equivalent and the
partition union costs planning time:

| Form | Planning | Execution |
| --- | --- | --- |
| 27-branch partition `UNION ALL` | 112 ms (74.9 ms cold catalog I/O, so ~37 ms warm) | 654 ms |
| Parent scan, `season_year IN (...)` | 8 ms | 689 ms |

Both return the same 48,691 rows. Note the parent scan needs the mirrored
`player_gamelogs.season_year` predicate for partition pruning — see
`docs/data-views-system.md` § Partition pruning.

## Gotchas

- **The week CTE must stay grouped.** The gamelog PK is `(esbid, pid, year)` and
  is not unique on week; 20 production `(pid, year, week)` keys carry two
  gamelogs naming two different teams. Projected row-for-row they fan out the
  player's week row through the LEFT JOIN in `team-table-column-definitions.mjs`.
  `test/data-view-queries/team-code-year-week-axes-dedupes-duplicate-week-gamelogs.json`
  pins this and is mutation-checked.
- **None of the three bridge CTEs uses `withMaterialized`** —
  `team-table-column-definitions.mjs` registers them with plain `query.with()`.
  Keep it that way; the `player_year_teams` fence was the 8.3s defect the
  `█████████████████████████████` cluster fixed.
- **`player_participation_weeks` NULL-ness is load-bearing.** The bye arm of
  `participation_status_select` keys on `player_participation_weeks.pid IS NULL`
  meaning "no gamelog at all". A merged CTE must preserve that row set exactly.

## Verification

Both bridges resolve ROSTER MEMBERSHIP and neither filters `is_active` — that
invariant is the point of `d624aded0` and must survive the consolidation. The
two fixtures added there are the gate; each was proven red at HEAD on the value.

Goldens will move across the week-grain corpus. Regenerate in an isolated
worktree, never the shared tree.

## Plan

- [ ] Merge `player_years_weeks_teams` and `player_participation_weeks` into one CTE projecting `teams` and `is_active`
- [ ] Register it once from both `team-table-column-definitions.mjs` and `participation-status-cte.mjs`
- [ ] Decide, and record, whether the year pair collapses too
- [ ] Move the surviving builders to the parent-scan shape with the mirrored season_year predicate
- [ ] Regenerate week-grain goldens in an isolated worktree; confirm the two `d624aded0` fixtures stay green
- [ ] Full suite against a private database, with a control at HEAD