---
title: Drop smart_player_id from player and nfl_play_stats
type: task
status: planned
description: >-
  smart_player_id is a UUID template with a gsis_player_id hex-embedded in it, so it re-encodes
  another column and can contradict it: that is the sole source of the false identity conflicts
  behind the gamelogs stat-row drop. Sweeps the consumers and drops both columns.
base_uri: user:task/league/drop-smart-player-id-column.md
created_at: '2026-08-04T06:06:43.421Z'
entity_id: fdd7285b-bba2-4816-bd8e-673a25bfeebf
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[user:task/league/fix-gamelogs-generator-gsis-only-stat-row-drop.md]]
  - blocked_by [[████████████████████████-███-████-████-██████████████]]
  - relates [[███████████████████████-███████████-███████-███████]]
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-09-02T19:26:48.082Z'
visibility_analyzed_at: '2026-11-05T23:49:11.478Z'
---

# Drop `smart_player_id` from `player` and `nfl_play_stats`

## Why

`smart_player_id` is not an identifier. It is a UUID template with a
`gsis_player_id` hex-embedded at the offset `decode_id` reads
(`scripts/import-plays-nfl-v1.mjs`): 10,483 of the 10,897 `player` rows carrying one
decode to that same row's OWN `gsis_player_id`. The column holds no identity
information that `gsis_player_id` does not already hold.

Because it re-encodes another column, it can DISAGREE with it. That is not a
tie between two opinions — it is one column contradicting itself, and it is the
sole generator of the false conflicts that cost
[[user:task/league/fix-gamelogs-generator-gsis-only-stat-row-drop.md]] its entire
investigation:

- 5,224 valid play-stat rows across 1,237 pid pairs had the two identifiers
  resolving to different people. After clearing 36 stolen encodings (league
  `a966d7fbd`) that fell to 1,629 across 1,212 pairs — but only because the
  underlying column was made to agree with itself, not because the ambiguity was
  real.
- 236,572 valid rows carry a fabricated value across 4,301 distinct encodings
  that decode to a gsis id no player row owns.
- The theft mechanism attributed one player's whole career to another: the
  2000-2004 Tyrone Wheatley Sr. play stats carry an encoding of HIS gsis id while
  his son's `player` row held that value, so the generator wrote the father's
  rushing seasons onto the son's pid.

Nothing reads it for identity any more. `libs-server/resolve-play-stat-player.mjs`
already treats a non-resolving value as absent and resolves `decoded_self`
structurally. Deleting the column removes the whole class.

## Scope

- `player.smart_player_id` — `varchar(36)`
- `nfl_play_stats.smart_player_id`

Consumers to sweep before the drop. This list is a starting point, not a survey:

- `libs-server/resolve-play-stat-player.mjs` — the resolver's `decoded_self`,
  `smart_only` and conflict tiers all collapse once the column is gone
- `scripts/generate-player-gamelogs.mjs` — `players_by_smart_player_id`, the
  `smart_player_ids` load, `patch_play_stats_from_role_pid`, and
  `generate_snap_based_gamelogs`, which selects `player.smart_player_id` and keys
  a historical-team map on it
- `scripts/update-player-gsispid.mjs` — the sole writer; retire it or strip the
  column from it
- `scripts/import-plays-nfl-v1.mjs` — `decode_id` and the import write path
- `db/adhoc/2026-08-04-clear-stolen-smart-player-id-encodings.sql` documents the
  encoding scheme and should be read before touching any of the above

`pid` is allowlisted by the conformance ratchet, so naming is not a blocker.

## Sequencing

Blocked by nothing, but do NOT run it concurrently with
[[████████████████████████-███-████-████-██████████████]], whose Pass-0
bridge keys on `smart_player_id` to recover `gsis_player_id` — which is a
re-encoding of the value it is recovering, so that bridge is close to a no-op by
construction and is being re-planned. Coordinate before either ships.

## Plan

- [ ] Survey every consumer of both columns; classify each as identity-reading,
      structural, or incidental
- [ ] Confirm no read path loses information the gsis side does not already carry
- [ ] Remove the resolver tiers the column exists to arbitrate, and re-run the
      gamelog before/after harness to show the output is unchanged
- [ ] Retire or strip `scripts/update-player-gsispid.mjs`
- [ ] Author the DDL, apply, `yarn export:schema`, and land the sweep in one commit
- [ ] Sweep `docs/`, `server/crontab-*`, and the user-base `text/league/` and
      `guideline/nfl/` trees for the old name