---
title: Close or document the preseason nflverse_game_id gap
type: task
status: draft
description: >-
  Every preseason game lacks an nflverse_game_id, silently excluding 270,959 plays from any
  nflverse-keyed join. Draft pending a plan: establish whether the id is obtainable, what else it
  excludes, and whether the disposition is a backfill or a documented boundary.
base_uri: user:task/league/close-preseason-nflverse-game-id-gap.md
created_at: '2026-09-10T22:51:04.131Z'
entity_id: 282a6afe-14d6-48b5-ab91-c8ee1d88f309
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[████████████████████-█████-████-██████-█████-████-██████████]]
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-09-23T19:48:53.169Z'
visibility_analyzed_at: '2026-11-05T23:48:32.913Z'
---

## Context

Every preseason game in `nfl_games` lacks an `nflverse_game_id`. Measured against production 2026-09-10:

```sql
SELECT g.season_type, count(*) AS games,
       count(*) FILTER (WHERE g.nflverse_game_id IS NULL) AS games_missing_id,
       sum(p.plays) AS plays
FROM nfl_games g
JOIN (SELECT esbid, count(*) AS plays FROM nfl_plays WHERE season_year >= 2000 GROUP BY 1) p
  ON p.esbid = g.esbid
GROUP BY 1 ORDER BY 1
```

| season_type | games | games missing `nflverse_game_id` | plays |
| --- | --- | --- | --- |
| PRE | 1,525 | **1,525** | 270,959 |
| REG | 6,472 | 0 | 1,171,018 |
| POST | 294 | 0 | 53,293 |

It is total: not most preseason games, all of them. Regular season and postseason are complete.

The plays exist — 270,959 of them, 18 percent of the post-2000 corpus — so this is a join-key gap, not a data gap. Anything keyed on `nflverse_game_id` silently excludes preseason.

Found while planning coach and play-caller columns, where it blocks preseason coach attribution entirely (`nfl_game_coaches` is keyed by `nflverse_game_id`). Coach attribution is the first known consumer, not the only possible one — the whole class of nflverse-keyed joins is affected.

## What this task should establish

- **Whether the id is obtainable at all.** nflverse's own play-by-play coverage may simply exclude preseason, in which case there is no id to backfill and the correct outcome is documenting the boundary rather than filling it. Establish this before designing any backfill.
- **Which writer is responsible.** Enumerate from the code that sets `nflverse_game_id`, then go count rows — a scan for producers cannot see a writer that was deleted, and the data a retired writer left is still in the table.
- **What else is silently excluded.** Grep for joins on `nflverse_game_id` across the tree and enumerate which consumers currently drop preseason without saying so. This is the part with reach beyond coaches.
- **Whether preseason coach attribution has a different source.** `nfl_games` already carries `home_coach` / `away_coach` / `home_play_caller` / `away_play_caller`, written by the same importer — check their preseason population, since they are keyed on `esbid` rather than `nflverse_game_id` and may not have the gap.
- **The disposition.** Backfill, alternate key, or a documented and surfaced boundary. If the answer is a boundary, the deliverable is making it visible where a consumer would otherwise read an empty cell as "it did not happen" — the failure `docs/guides/data.md` names.

## Findings, measured 2026-09-10

Three of the five questions above are now answered, and they change the likely disposition. Measured against production; the figures in Context reproduce.

### This is a SOURCE coverage gap, not a join-key gap

The framing in Context — "the plays exist, so this is a join-key gap" — is true of the plays but **false of the coaches**, and the coach consumer is what motivated the task.

- `nfl_game_coaches` covers **exactly** the games that carry an `nflverse_game_id`: 7,548 distinct ids in the coach table, 7,548 games with an id, **zero** orphans in either direction.

```sql
SELECT
 (SELECT count(DISTINCT nflverse_game_id) FROM nfl_game_coaches) AS coach_games,
 (SELECT count(*) FROM nfl_games WHERE nflverse_game_id IS NOT NULL) AS games_with_id,
 (SELECT count(*) FROM nfl_game_coaches c WHERE NOT EXISTS (
    SELECT 1 FROM nfl_games g WHERE g.nflverse_game_id = c.nflverse_game_id)) AS coach_rows_no_game,
 (SELECT count(*) FROM nfl_games g WHERE g.nflverse_game_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM nfl_game_coaches c WHERE c.nflverse_game_id = g.nflverse_game_id)) AS games_with_id_no_coach
```

The zero-orphan result is the load-bearing half. `import-nfl-coaches.mjs:698` takes `nflverse_game_id` **verbatim from the upstream CSV's `game_id` column** — it does not derive or look up the id — and inserts on conflict, so an upstream row for a game we lack would land as an orphan. There are none. Since no preseason game carries an id, no upstream `game_id` can be a preseason one. **The upstream coach source therefore contains no preseason games at all.**

Consequence: backfilling `nflverse_game_id` onto the 1,658 preseason games would yield **zero** additional coach coverage. The id is not what is missing.

### There is no alternate source — the denormalized columns have the same gap

The draft asked whether `nfl_games.home_coach` / `away_coach` / `home_play_caller` / `away_play_caller` escape the gap, since they are keyed on `esbid` rather than `nflverse_game_id`. They do not. All four are **NULL on all 1,658 preseason games**, and on all 20 PRO (Pro Bowl) games:

```sql
SELECT season_type, count(*) games, count(home_coach) home_coach, count(away_coach) away_coach,
       count(home_play_caller) home_pc, count(away_play_caller) away_pc
FROM nfl_games WHERE season_year >= 1999 GROUP BY 1 ORDER BY 1
```

| season_type | games | home_coach | away_coach | home_pc | away_pc |
| --- | --- | --- | --- | --- | --- |
| PRE | 1,658 | **0** | **0** | **0** | **0** |
| PRO | 20 | **0** | **0** | **0** | **0** |
| REG | 7,240 | 7,239 | 7,239 | 6,950 | 6,951 |
| POST | 309 | 309 | 309 | 299 | 299 |

The esbid keying buys nothing, because the same importer fills both shapes from the same source. This kills the cheapest hoped-for route.

### The id gap is wider than preseason

Context segments only the post-2000 played population. Across all of `nfl_games` the id is missing from far more:

| season_type | games | with id | note |
| --- | --- | --- | --- |
| PRE | 1,658 | 0 | total |
| PRO | 20 | 0 | total — Pro Bowl, not in Context at all |
| REG | 13,366 | 7,239 | id begins 1999 |
| POST | 578 | 309 | id begins 1999 |

`nflverse_game_id` exists only from **1999 onward**. The pre-1999 REG and POST games lack it too. That does not affect the coach columns (`nfl_plays` begins in 2000 and coach data begins in 1999, so the joinable window is covered) but it means any plan phrased as "preseason is the gap" is mis-scoped. PRO is a third excluded class nobody has named.

### What this leaves open

- **The remaining live question is the one with reach: what else joins on `nflverse_game_id` and silently drops these classes.** That is untouched by the findings above and is the real reason to keep this task.
- Whether any source anywhere carries preseason coaching assignments. Not investigated. Preseason play-calling is often split among assistants by design, so the upstream absence may be deliberate rather than an oversight.
- **Likely disposition for the coach consumer: a documented boundary, not a backfill.** The findings above remove the backfill option rather than merely disfavoring it. Treat that as a strong prior for whoever plans this, not as a ruling — the operator has not ruled, and the wider-consumer question could still justify populating the id for reasons unrelated to coaches.

## Notes

- Verify every figure above yourself before building on it; they are a planning session's measurements, and a subset cannot exceed its superset.
- `nfl_games.esbid` is unique (15,622 of 15,622, zero null) and is the key preseason rows DO have.
- Related: `████████████████████████████████████████████████████████████` states the preseason exclusion in its column descriptions. If this task fills the gap, those descriptions need revisiting.