---
title: Repair the silent play-match ambiguity and the duplicate rows behind it
type: task
status: completed
description: >-
  get-play.mjs returns null on an ambiguous play match with no error, so a duplicated game makes the
  charting importer skip it silently -- the defect that hid a three-year charting loss. The same
  null-distinct gap leaves 496,129 excess duplicate-key rows in nfl_play_stats.
base_uri: user:task/league/repair-silent-play-match-ambiguity.md
created_at: '2026-09-03T05:34:52.411Z'
entity_id: 7af07047-d15a-4992-b6b1-a1f175649a5d
finished_at: '2026-09-03T17:59:00.161Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[███████████████████████-██████-████-█████-█████████]]
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-09-03T17:59:00.161Z'
visibility_analyzed_at: '2026-11-06T00:18:01.721Z'
---

# Repair the silent play-match ambiguity and the duplicate rows behind it

## Why this exists

Surfaced by [[███████████████████████-██████-████-█████-█████████]], which found that one game's charting had been missing from the canonical table since 2023 and nothing anywhere reported it. The charting was restored (league `c71b05d78`), but the mechanism that hid it is untouched, and it is not specific to that game.

## Defect one — an ambiguous match is silently a miss

`libs-server/get-play.mjs` ends:

```js
const plays = await query
if (plays.length === 1) {
  return plays[0]
} else {
  return null
}
```

Zero matches and two matches are the same answer. `scripts/import-charted-plays-from-csv.mjs` treats null as "play not matched", logs it to a debug namespace, pushes it onto `playNotMatched`, and continues — so a whole game's charting can fail to land and the run still exits green.

That is exactly what happened: the charting import charted the forked copy of a 2021 game while it was the only copy; a 2023-12-18 re-import created a second copy; from that moment `get-play` matched two rows and returned null, so neither copy could ever be charted again. Nobody found out for three years.

**The distinction is load-bearing and is currently discarded.** "No such play" is a data question. "Two plays answer to this description" is an integrity alarm, and it is the one that should be loud.

Watch for the shape rather than only the instance: a caller that cannot tell an absent row from an ambiguous one will make the same mistake, and `get-play` has other callers.

## Defect two — the unique index does not hold, at scale

`nfl_play_stats` carries `CREATE UNIQUE INDEX idx_24719_play_stat ON public.nfl_play_stats USING btree (esbid, play_id, stat_id, player_name)`. A standard unique index treats NULLs as distinct, so any row with a null `player_name` escapes it entirely.

Measured on production 2026-09-03:

| | |
| --- | --- |
| Total rows | 5,802,974 |
| Rows with null `player_name` | 1,216,352 |
| Duplicate key groups | 296,443 |
| Excess rows above one per key | 496,129 |

This is what made the last 195 backup-only cells unattributable during the forked-esbid close-out: one backup row matched two live rows and there was no way to tell which it belonged to, so they were deliberately left unrestored.

**Do not reach for a blind dedupe.** The prior chain's lesson is that the obvious remediation on a duplicate is unrecoverable and the copies are not interchangeable — there, the copy that looked inferior held the only charting. Establish what distinguishes the duplicates before removing any.

Open question to settle first: is `NULLS NOT DISTINCT` (Postgres 15+, already used elsewhere in this schema) the right end state, or is a null `player_name` itself the defect to fix upstream?

## Also surfaced, smaller

- **2013 preseason cannot be graded.** All 65 of its `nfl_games` rows carry a NULL `status`, so `status like 'FINAL%'` excludes them from `nfl-plays-game-coverage`. 2013 is the only season between 2002 and 2025 that is unstamped; its plays are present and correct. Stamping it would extend coverage by a season.
- **`yarn deploy` runs no preflight** while `yarn deploy:all` does, and the hosts deploy by `git pull` — so deploying an unpushed commit silently redeploys the prior revision while the output reads like success. Documented in `docs/guides/ship.md` under the bare-yarn-deploy bullet, so this is a known gap rather than a discovery. Worth closing by giving the quick path the same gate.

## Not in scope

The `app @mui import ratchet` spec failing on master (211 imports against a budget of 215) belongs to the auction lane, which has a live session on it.

## Verification bar

This repo's rule, and the prior chain was held to it: **a green you have not shown can go red is not evidence.** Every post-condition needs a control that fires a *different* named assertion against an otherwise-green run. Prefer executing the behavior over reading the source.

Production access is read-only, through `psql` over ssh to `league`. A direct database connection from outside is firewalled and stalls as a misleading knex pool timeout. Any production write goes through `yarn db:exec db/adhoc/<file>.sql`, which requires a literal `-- STATUS: PENDING` banner and rewrites it to APPLIED itself. Use `yarn test:isolated`, never `yarn test:local`.