--- title: Fix the Gamelogs Generator's gsis-only Stat-Row Drop type: task status: completed description: >- generate-player-gamelogs dropped play-stat rows keyed only by gsis_player_id, under-counting stats across every season. Fixed by resolving identity before grouping (league ed3a69fde), deployed, and 277 games plus 3100 player-seasons regenerated. base_uri: user:task/league/fix-gamelogs-generator-gsis-only-stat-row-drop.md created_at: '2026-07-30T06:11:06.120Z' entity_id: 2cfb5ffd-e3b0-4966-88b7-47ca23451724 finished_at: '2026-08-04T06:08:06.092Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: High public_read: true relations: - relates [[██████████████████████████-███-████████-█████████]] - relates [[user:task/league/drop-smart-player-id-column.md]] - relates [[███████████████████████-███████████-███████-███████]] - relates [[████████████████████████-███-████-████-██████████████]] started_at: '2026-08-04T02:40:59.864Z' tags: - user:tag/league-xo-football.md updated_at: '2026-08-04T16:58:45.565Z' visibility_analyzed_at: '2026-11-05T23:49:23.534Z' --- # Fix the Gamelogs Generator's gsis-only Stat-Row Drop ## Outcome Shipped, deployed, and the affected data regenerated. Both hosts run league `6730dc39b`. `generate-player-gamelogs.mjs` made two passes over play stats — one grouped by `smart_player_id`, one by `gsis_player_id` — and the second pass skipped any group whose player was already seen in the first. The skip avoided a duplicate gamelog but discarded the group's stats rather than merging them, so a row carrying only a `gsis_player_id`, for a player with any other row in the same game, was never counted. Resolving identity BEFORE grouping removes both the second pass and its exclusion list, because one player is then one group by construction. ## What unblocked it The task was blocked on choosing between the two identifiers where they resolve to different people. **That framing was wrong.** `smart_player_id` is not a second identifier — it is a UUID template with the player's own `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`, so the column holds no independent identity information. The conflicts were one column contradicting itself, and 36 `player` rows holding another player's encoding were the whole structural defect. ## The resolver `libs-server/resolve-play-stat-player.mjs` resolves one row to one pid, in tiers: `decoded_self`, then feed `name`, then draft-year `era`, then `position` plausibility, then abstain and log. Attributing a stat to the wrong player is worse than not counting it. `player_name` is the arbiter because it is the one column in the chain no local process has rewritten. Measured against 400,000 rows whose identity is not in doubt, normalized first-initial-plus-surname matching is right **99.58%** of the time; against an independent position-versus-stat oracle it agrees on 1,273 of 1,283 decided conflicts, and all ten disagreements are trick plays or corrupt rows. **Do not use `nfl_plays._pid` to validate any of this.** It is a re-derivation of `nfl_play_stats.gsis_player_id` and never reads `smart_player_id` at all, so it agrees with the gsis side 336 times and the smart side exactly zero times — a tautology, not evidence. ## Validation The honest oracle is the pre-fix generator against the post-fix generator over the same games. Diffing the new generator against production instead conflates the fix with years of accumulated staleness, because the generator upserts and never deletes. Over all 277 affected games: **21,634 unchanged, 291 changed, 0 added, 100 removed.** Net stat movement is positive on every field. All 100 removals are explained — 241 play-stat rows reassigned by feed name, 2 abstentions on tackle rows. `E.Smith` moves off Darnay Scott onto Emmitt Smith; `C.Portis` off Mike Anderson onto Clinton Portis; six Justin Fargas rushes off a linebacker. The canonical case, Davante Adams on esbid 2016091813, goes from `fumbles_lost` 0 to 1. ## Blast radius 277 games, 781 dropped rows on stat_ids the calculator acts on, spanning every era. The acting-stat_id set was derived by probing `calculateStatsFromPlayStats` rather than transcribed, which is why it is larger than the 95/499 figure measured earlier — it includes `passing_attempts`, two-point conversions and extra points. 2002-2003 supplies 210 of the games; four 2021 PRE games supply 354 of the rows. Applied to production: 3,503 gamelogs changed, 1,522 added, across 3,100 player-seasons. Those seasons' `player_seasonlogs` were regenerated. ## The position hold-back Regenerating would have rewritten `player_gamelogs.pos` on 1,183 rows with no correction in any of them. Neither `player.primary_position` (43 distinct values) nor `player_gamelogs.pos` (39) has a controlled vocabulary, so `OLB` and `LB`, `T`/`G`/`C` and `OL`, `SS`/`FS`/`CB` and `DB` all name the same position under different importers' spellings — plus 111 `K`→`P` and 87 `P`→`K` flips in opposite directions on one run. `pos` is now held out of the UPDATE half of the upsert, the same treatment `active` already gets and for the same reason: this script is not the writer of record. The one-off helper is now `libs-server/merge-columns-on-conflict.mjs`, shared with `scripts/process-player-seasonlogs.mjs`, which had the identical churn and had to be fixed before the seasonlog regeneration could run. The vocabulary itself is [[██████████████████████████-███-████████-█████████]], whose last step removes `pos` from the hold-back list. ## Residuals, measured after regeneration Neither moves, and neither can be moved by this task, because the generator never deletes. - **25 gamelog rows claim a `fumbles_lost` the play stats do not attribute to that player** (17 REG, 7 PRE, 1 POST). - **2,951 gamelog rows across 319 players attribute a game to a player drafted after that season.** Up 2 from the pre-regeneration 2,949, because regeneration added rows for players the separate name-matching defect in `scripts/backfill-play-stats-gsisid.mjs` mis-resolved. That defect is the dominant cause and is filed separately. Both are carried by [[███████████████████████-███████████-███████-███████]], along with the 664 gamelogs whose stats no valid play stat supports and the 223 same-name `player` pairs sharing a copied `date_of_birth`. ## Do not merge the duplicate-looking player pairs Every same-name pair sharing a `date_of_birth` that was inspected turned out to be two distinct humans — usually father and son — disagreeing on `esb_player_id`, `gsis_player_id`, `gsis_it_player_id` and `pfr_player_id`. The shared `date_of_birth` is the corruption, not the evidence: the younger row inherited the elder's through a stolen `smart_player_id` link, which is also how the elder's career ended up on the younger pid. ## Shipped | commit | what | | --- | --- | | `818aaaed5` | wire the dead `collector` parameter | | `ed3a69fde` | resolve play-stat identity before grouping; `libs-server/resolve-play-stat-player.mjs` plus 19 tests | | `a966d7fbd` | clear 36 stolen `smart_player_id` encodings; guard `update-player-gsispid.mjs` | | `e2437eca8` | hold `player_gamelogs.pos` out of the upsert merge | | `6730dc39b` | share the hold-back helper; apply it to `player_seasonlogs` | ## Plan - [x] Wire the dead `collector` parameter - [x] Measure the blast radius - [x] Re-verify every premise against production before building on it - [x] Establish the resolution rule and validate it against an independent oracle - [x] Write `libs-server/resolve-play-stat-player.mjs` with a spec - [x] Rewrite the generator to one resolved-pid pass - [x] Diff pre-fix against post-fix output; explain every delta - [x] Ship and deploy - [x] Hold `pos` out of the upsert merge so regeneration causes no vocabulary churn - [x] Regenerate all 277 affected games - [x] Regenerate `player_seasonlogs` for the 3,100 touched player-seasons - [x] Re-run the fumble oracle and state the residual - [x] Spin out the position vocabulary, the `smart_player_id` drop, and the deletion gap