---
title: Fix the two nfl_games upserts whose conflict key can be NULL
type: task
status: draft
description: >-
  scripts/process-nfl-games.mjs and scripts/import-nfl-games-nfl.mjs both build an nfl_games upsert
  whose esbid may be absent and then arbitrate on .onConflict('esbid'). With a NULL arbiter no
  conflict is ever found, so the statement attempts an INSERT and dies on idx_24707_game whenever
  the game already exists.
base_uri: user:task/league/fix-nfl-games-upsert-null-esbid-conflict-key.md
created_at: '2026-09-02T22:52:52.507Z'
entity_id: 8afa15e3-c517-46a6-b2ab-95ff205f8c17
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-02T22:53:09.326Z'
visibility_analyzed_at: '2026-11-05T23:49:25.337Z'
---

# Fix the two nfl_games upserts whose conflict key can be NULL

Found during review of [[███████████████████████-███-█████-████-████████████-███████████]] and confirmed against production 2026-09-02. Neither was in that task's scope.

## The defect

Both scripts upsert into `nfl_games` arbitrating on `esbid`, and both can reach the statement with `esbid` absent from the payload:

- `scripts/process-nfl-games.mjs:40-63` — the `update` object it pushes carries `away_nfl_team`, `home_nfl_team`, `week`, `season_year`, `season_type`, `kickoff_at` and optionally `day`. It never carries `esbid` at all, and line 63 is `.insert(updates).onConflict('esbid').merge()`.
- `scripts/import-nfl-games-nfl.mjs:47` — `...(esbid && { esbid })` omits the key when the source supplies nothing, and line 194 arbitrates on it.

`nfl_games.esbid` is nullable (`integer`, `is_nullable = YES`), and `idx_24707_esbid` is a UNIQUE btree over it. Postgres treats NULLs in a unique index as distinct, so `ON CONFLICT (esbid)` finds no conflicting row for a NULL arbiter value and the statement proceeds as a plain INSERT.

## What actually happens

Not silence, and not a duplicate row. `idx_24707_game` is UNIQUE over `(away_nfl_team, home_nfl_team, week, season_year, season_type)` and is NOT named in the `ON CONFLICT` clause, so its violation is unhandled and raises. For `process-nfl-games.mjs`, whose whole job is re-processing games that already exist, that is every row.

Production carries 0 rows with a NULL `esbid` across 15,622, so nothing has ever landed through this path.

## What to decide

The fix is not simply "add esbid to the payload" for `process-nfl-games.mjs` — it does not have one, and its natural key IS the five columns of `idx_24707_game`. Either arbitrate on that index, or make the statement an UPDATE keyed on those columns rather than an upsert. `import-nfl-games-nfl.mjs` is the different case: it has an `esbid` most of the time and the conditional spread is what makes it absent, so the question there is whether a row with no `esbid` should be written at all.

Check whether either script is on a cron before estimating urgency.