---
title: Derive NFL drives and drive classes
type: task
status: completed
description: >-
  A derived nfl_drives table with one canonical drive outcome and drive character classes, fixing
  the drive_result, drive_end_transition and series_result disagreements and repointing their
  readers.
base_uri: user:task/league/derive-nfl-drives-and-drive-classes.md
created_at: '2026-10-20T01:11:45.652Z'
entity_id: ade0ee05-80c3-47e1-b235-644b06bb9775
finished_at: '2026-10-20T04:55:15.314Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: High
public_read: true
relations:
  - subtask_of [[█████████████-██████████████-████-████-███-█████-███-██████████████-███████████]]
  - blocked_by [[user:task/league/build-nfl-classification-registry.md]]
started_at: '2026-10-20T02:50:47.348Z'
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-10-20T04:55:15.314Z'
visibility_analyzed_at: '2026-11-05T23:49:08.047Z'
---

# Implementation Plan: Derive NFL drives and drive classes

## Overview

- A derived `nfl_drives` table holds one row per drive with one canonical outcome and the drive's character classes, so reviews, game samples and film can address a drive directly.
- Impact: fixes the drive-outcome defects (title-case `drive_result` free text against the `series_result` enum and `drive_end_transition`, a merged "Turnover", "Opp touchdown" spread over null, punt and interception endings) and gives the per-game review its middle grain.
- Ordering: after [[user:task/league/build-nfl-classification-registry.md]]; feeds the per-game review pilot.

## Background

- `nfl_plays` carries per-play drive columns (`drive_sequence`, `drive_result`, `drive_start_transition`, `drive_end_transition`, `drive_play_count`, `drive_yards_gained`, `drive_top`, start and end yard line, quarter and clock) and `series_result`; no drive table exists (checked: `rg -n 'CREATE TABLE.*drive' db/schema.postgres.sql` finds only `dvoa_team_drive_seasonlogs`).
- Observed 2025 REG, live plays, distinct drives by `drive_result` and `drive_end_transition` (query below): Punt/PUNT 1910, Touchdown/TOUCHDOWN 1328, Field goal/FIELD_GOAL 930, Turnover on downs/DOWNS 360, Turnover/INTERCEPTION 351, End of half/END_GAME 217, Turnover/FUMBLE 199, End of half/END_HALF 181, Missed field goal/MISSED_FG 134, Opp touchdown/null 62, Opp touchdown/INTERCEPTION 28, Touchdown/FUMBLE 8, plus smaller mixed pairs. Baseline query: `select drive_result, drive_end_transition, count(distinct esbid::text||'-'||drive_sequence::text) from nfl_plays where season_year=2025 and season_type='REG' and play_classification='live_play' group by 1,2`.
- `libs-server/game-sample/load-game-sample-frames.mjs:91` hard-codes `drive_result = 'Touchdown'`.

### Assumptions

- `drive_sequence` is unique per team drive within a game and stable across re-imports (unchecked; verify with a duplicate query before the table is built).
- `drive_end_transition` is the more precise source and `drive_result` the coarser one, so the canonical outcome derives from the transition with the result as a fallback (unchecked for the null-transition rows).
- Source columns stay: the drive columns are imported data, and [[user:guideline/nfl/league/preserve-unique-data.md]] forbids dropping them; the table is derived and re-derivable from `nfl_plays`.

## Design

- `nfl_drives`: key (`esbid`, `drive_sequence`), possession and defense team, start and end play, quarter and clock, start yard line and yards to the end zone, the feed's play count and the scrimmage play count, yards, time of possession, `drive_outcome` (registry class), `is_defensive_score_after` (the opponent's touchdown on the change of possession), `drive_character_list` (registry class, multi-valued), `classifier_version`, `input_watermark`. Every season the feed carries drive columns, 2000 on, regular season and postseason.
- **Drive identity (operator, 2026-10-19):** a key is a drive when it holds a live play that is not a kickoff or a try. A kickoff-only key is a drive only when its start and end plays are its own. A key with no snap and no drive record is not a drive. Drive values are read from rows other than kickoffs, because a kickoff names the kicking team, and from 2025 the post-score kickoff carries the next drive's record.
- **Outcome:** read from `drive_end_transition`, with `drive_result` as the fallback where no transition was written (mostly end of half, 2001-2002). Blocked punts read as `punt`, blocked field goals as `missed_field_goal`, `FUMBLE_SAFETY` as `safety`.
- **Character cut points (operator, 2026-10-19):** in the registry entry, reusing the shared explosive bar, two-minute bar and competitive win-probability bounds.
- **Derivation:** `libs-shared/nfl-classification/classify-nfl-drive.mjs` (rules), `libs-server/derive-nfl-drives.mjs` (loader and writer, a game whole or not at all), `jobs/derive-changed-nfl-drives.mjs` (job type 188, worker-1 hourly). The job fails when a completed game has lacked drive columns for three days.
- **Readers:** the game-sample red-zone touchdown reads `nfl_drives`. The plays view gains `play_drive_outcome`. The unused `drive_result` data-view param is retired (operator, 2026-10-19). A joined `drive_outcome` data-view param would cross 14 param query paths and is left out.
- **Knowledge Base Impact:** [[███████████████████████████████-█████████-█████████]], routed from the query-traps ABOUT. The drive classes are in [[███████████████████████████-██████████████-███████████]].

## Plan

- [x] **Uniqueness and mapping check.** The key holds. The defects sit in kickoff rows and are recorded in the findings below.
- [x] **Registry entries** for `drive_outcome` and `drive_character`, with operator-confirmed cut points. The registry spec and the vocabulary gate cover both.
- [x] **Table and derivation.** A fixture spec covers each outcome, a fumble touchdown, a pick-six, an end-of-game kneel, the kickoff identity rules and each cut point at its edge.
- [x] **Backfill every season, 2000 on.** Every key with a scrimmage play has exactly one row in every season, and no outcome is null. The exception is a game still pending its drive columns.
- [x] **Repoint readers.** `git grep "drive_result = 'Touchdown'" -- libs-server` finds nothing, and the same pattern matches the query-trap sheet as a control. The league suite passes, apart from two environment failures: no `dist/` in a worktree, and a clock-dependent Sleeper spec.
- [x] **Query-trap entry** for drives, routed from the query-traps ABOUT.
- [x] **Deploy and first sweep.** Both hosts run league 59478ba71, and the worker crontab carries the job. A production run swept 29 games and rewrote 7 with newer plays. One game, still within its ceiling, is pending its drive columns.