Kickoff is the worst possible moment for a data model to get honest. The game page should show what's happening right now, but the official box score lands hours later, and season totals, game logs and grading all read the same tables. Write live rows into those tables and every number on the site wobbles for three hours. Keep them out and the game page lies by omission.
This week Statpro shipped its way out of that trap for NFL games: live rows enter the canonical tables from the first snap, and almost nothing is allowed to see them.
Same tables, one fence
The design is a quarantine rather than a side room. When a game goes live, a status worker writes the situation (down and distance, spot of ball, clock, timeouts) and the in-progress player stats into the same tables every other feature already reads. Each live row carries a marker: a live source tag plus a live-box flag on the extras field. One shared predicate in the shared database package knows how to exclude those rows, and every reader outside the game page applies it.
-- the shape of the fence, names renamed
select *
from player_game_stats
where not (source = 'live' and extras ? 'liveBox');I like this better than a parallel "live" schema. A separate store means every future feature has to remember to merge two sources, and the one place that forgets ships wrong season totals. Here the default is the stable world; you opt into the live one explicitly, in exactly one place.
The swap is one transaction
When the official box score publication lands, it deletes the live rows and clears the live markers in a single transaction. There's no window where a reader sees a mix of live and official. Season totals never saw the live rows at all, so nothing jumps when the swap happens.
And because two views of the same plays will eventually disagree (they always do), a handover check compares the live plays against the official load before the swap replaces them. Divergence gets caught at the handover, where someone is watching, instead of in somebody's season log three weeks later.
On the game page side
The page polls every 15 seconds while a game is live, but it only re-renders when a version key changes: score, last play, live box hash. The situation bar has a fixed height so a down-and-distance update doesn't shove the whole page around. And the Drives card and Plays tab stay hidden until a game actually has plays, because a noon kickoff with no snap yet shouldn't show empty tabs.
// poll every 15s while live; rerender only on change
const version = [score, lastPlay, boxHash].join(":");
if (version !== lastVersion) applySituation(payload);Two small bugs worth stealing
Two fixes from the same push are reusable outside sports. First: a job set its database connection to read-only with a session-level flag, on a pooled connection. The next write on that same pooled connection, hours later, failed with a read-only error. The fix is scoped transactions (begin read only, then rollback) that leave no session state behind. If you pool connections, don't mutate session state; scope it per transaction.
Second: a writer stamped a source name that wasn't in the source registry, so every insert failed a foreign key on the spot. That looked annoying for the ten minutes it took to fix, and correct for every minute since. A registry with a foreign key on source names looks like bureaucracy until the day it's the only thing standing between you and silently mislabeled data.
The takeaway from the whole week: the fence is a predicate, and the table is just furniture. If the stable world is the default and the live world is opt-in, you can be fast and correct at the same time. Earlier this month I wrote about what a live score promises about time; this is the plumbing that finally keeps that promise without touching anyone's season stats.