---
title: Conform the Drive Transition Spellings to the Canonical UPPER_SNAKE Form
type: task
status: planned
description: >-
  A second feed writes Title Case drive transition values beside the canonical UPPER_SNAKE ones,
  putting roughly 1,600 plays behind a filter that cannot offer them. Conform the importer and
  backfill.
base_uri: user:task/league/conform-drive-transition-spellings.md
created_at: '2026-09-11T01:01:45.150Z'
entity_id: 49d58137-369a-4f43-a70e-c0da490f091f
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-09-11T01:01:45.150Z'
visibility_analyzed_at: '2026-11-05T23:48:36.437Z'
---

## Overview

`nfl_plays.drive_start_transition` and `nfl_plays.drive_end_transition` each hold TWO spellings of the same vocabulary: a canonical UPPER_SNAKE form carrying hundreds of thousands of rows, and a Title Case form carrying a few hundred, written by a second feed. Under a `SELECT` control the declaration can only offer one of them, so the Title Case rows are unreachable.

Declaring both spellings is the wrong repair — a faceted list showing `Punt` beside `PUNT` as separate entries is the near-duplicate pathology `docs/guides/data.md:37` exists to prevent. The repair is to conform the source.

## Background

Measured against production 2026-09-10 by `libs-server/param-vocabulary-drift.mjs`:

| Column | Title Case | rows | canonical | rows |
| --- | --- | --- | --- | --- |
| `drive_start_transition` | `Punt` | 534 | `PUNT` | 437,470 |
| `drive_start_transition` | `Interception` | 160 | `INTERCEPTION` | 67,758 |
| `drive_start_transition` | `Touchdown` | 153 | — | — |
| `drive_start_transition` | `Field Goal` | 143 | `FIELD_GOAL` | — |
| `drive_start_transition` | `Fumble` | 62 | `FUMBLE` | 44,574 |
| `drive_start_transition` | `Missed FG` | 44 | `MISSED_FG` | 24,889 |
| `drive_start_transition` | `Downs` | 35 | `DOWNS` | 39,898 |
| `drive_start_transition` | `End of Half` | 21 | — | — |
| `drive_start_transition` | `Blocked Punt` | 20 | `BLOCKED_PUNT` | 658 |
| `drive_start_transition` | `Kickoff` | 7 | `KICKOFF` | 557,484 |
| `drive_end_transition` | `Punt` | 533 | `PUNT` | 384,662 |
| `drive_end_transition` | `Field Goal` | 178 | `FIELD_GOAL` | 225,480 |
| `drive_end_transition` | `Touchdown` | 157 | `TOUCHDOWN` | 304,896 |
| `drive_end_transition` | `Interception` | 115 | `INTERCEPTION` | 69,182 |
| `drive_end_transition` | `Missed FG` | 63 | `MISSED_FG` | 39,908 |
| `drive_end_transition` | `Downs` | 61 | `DOWNS` | 58,904 |
| `drive_end_transition` | `End of Game` | 45 | `END_GAME` | 28,786 |
| `drive_end_transition` | `Fumble` | 36 | `FUMBLE` | 38,975 |
| `drive_end_transition` | `Blocked Punt` | 24 | `BLOCKED_PUNT` | 1,573 |
| `drive_end_transition` | `End of Half` | 12 | `END_HALF` | 18,488 |

Each column additionally holds 129 rows carrying an EMPTY STRING, which is a third condition and probably a different defect — an empty transition is an absence and should be NULL.

Two mappings are not mechanical and need settling before any conform runs: `Touchdown` and `End of Half` appear on `drive_start_transition`, where the canonical list declares neither, so their target is a judgement rather than a case fold.

## Why this is parked rather than fixed in the declaration

`db/checks/parked.json` carries both vocabularies as `baselined` against the `param-vocabulary-drift` check, naming this task as owner. The check grades declared options against observed values as an intersection over a union, so it reports these two until the data is conformed — which is the intended behaviour, not a false positive.

## Plan

- [ ] **Identify the writer before conforming anything**
  - Purpose: a conform that does not fix the producer is rewritten on the next import. The Title Case rows name a feed, and the repair belongs in that importer.
  - Changes: none yet. Determine which importer writes the Title Case form, from the code that writes these two columns rather than from the value spelling.
  - Verify: name the script and the line, and state whether it still runs.

- [ ] **Settle the two non-mechanical mappings**
  - Changes: decide the canonical target for `Touchdown` and `End of Half` on `drive_start_transition`, both absent from the declared list.
  - Verify: record the decision and its reasoning in this body.

- [ ] **Fix the importer, then backfill**
  - Changes: normalize at write time in the importer; a dated `db/adhoc/` file for the existing rows.
  - Verify: re-run the `param-vocabulary-drift` check and require the two rows to move from finding to clean, with the parked entries removed in the same change.

- [ ] **Decide the empty-string rows separately**
  - Purpose: 129 rows per column hold an empty string, which is an absence rather than a spelling.
  - Verify: establish whether NULL is right before writing it, per the empty-string precedent in `███████████████████████████████████████████████████████`.