Thirty NFL careers that were really forty
Statpro's player table had a quiet duplication problem. Some NFL players carried two, three, even four rows: records created years apart by different import runs, each holding its own slice of the same career. James Williams existed three times. A couple of obscure players differed by a single letter (Greg vs Gregg Briggs, Twyner vs Twynner). Sum a duplicated career and you get two partial lifetimes added together and labeled as one.
Preview, fingerprint, then write
Merging rows in a production sports database is the hand-tightening kind of operation: one shot, no undo. So the new players-merge command works like a careful release. It builds a plan in memory: for each reviewed pair, which rows move, which careers concatenate, which rows delete as empty. Preview mode prints that plan with a fingerprint and touches nothing. Write mode refuses to run unless the database still matches the fingerprint the plan was built from.
Every pair also carries refusal reasons as plain strings: leagues differ, names differ, the fold row changed since review. A merge that isn't clearly safe refuses to exist. The machine proposes, a human reviews, and the command re-checks both at run time.
function nameRefusals(pair, keep, fold) {
if (pair.foldName !== undefined) {
return when(pair.foldName !== fold.fullName,
`fold name is "${fold.fullName}", reviewed "${pair.foldName}"`);
}
return when(keep.fullName.toLowerCase() !== fold.fullName.toLowerCase(),
"names differ");
}
The map of who lives where
One subtlety shaped the whole run: a player's rows are scattered across dozens of tables, and the merge only needs the tables that actually hold him. The pipeline figures that out in two phases.
flowchart TD SCAN["scan player columns"] --> MAP["column hit map"] PAIRS["reviewed pairs"] --> PLAN["per-id merge plan"] MAP --> PLAN PLAN --> FOLD["fold into keep rows"]
One scan per column beats one scan per pair
The first full run crawled. The reason: several player columns (play-by-play actors, odds history) have no index, so counting one player's rows there is a full table scan, and the naive version did that per pair. A few dozen pairs meant a few dozen full scans of tables with tens of millions of rows.
The fix borrows a page from query planners. Scan each column once for every id in the entire plan, build a map of which columns hold which ids, and let each merge unit read only what holds its player. Same answer, and the scan count stops growing with the plan.
-- once per column, for every id in the whole plan
select distinct col::bigint as id
from play_rows
where col in (:plan_ids);
-- then each merge unit reads only columns that hold its id
Reviewed spellings become data
The merge used to require both rows to agree on the player's name, case-insensitively. But the duplicates exist precisely because old records disagree: Gregg vs Greg Briggs. Loosening the check would have made every pair easier to fold. Instead the pair file records foldName: the exact spelling a human reviewed, and the check passes only against that value. The guardrail didn't get weaker. It got more precise.
What changed for readers
- Prop context under the news: the prop module now shows under NFL, NBA and MLB stories, and clicking through is measurable, so we can see what readers want next.
- Better prop discovery: search snippets improved and research guides went public.
- One truth for totals: we settled a real design fight with a written decision. Box scores are the source of truth for career totals, and partial games stay counted in play-based stats. No more guessing which total is right.
What we'd do differently
Refusal reasons as free strings seemed fine until we wanted to sort the review queue by reason; structured codes would have made that trivial. And the two-green-runs merge bar, borrowed from release discipline, should have existed from pair one. The alternative is discovering your merge bar during the scary merge. Thirty careers folded to one row each so far, fourteen empty rows deleted, and the queue keeps shrinking.