---
title: Keep the live scoreboard updating on the matchups page
type: task
status: completed
description: >-
  Owns the /matchups live scoring path end to end: the scoreboard socket subscription model, the
  per-tick query shape, the client play cache and watermark, and the handover from live derivation
  to the finalized gamelog.
base_uri: user:task/league/keep-the-live-scoreboard-updating.md
created_at: '2026-09-20T21:01:54.036Z'
entity_id: 6dd687c8-e469-49a0-a107-54ef262dc456
finished_at: '2026-09-22T02:23:25.987Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: High
public_read: true
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-09-22T02:23:25.987Z'
visibility_analyzed_at: '2026-11-06T00:17:56.829Z'
---

The `/matchups` page derives every live score and projection on the client from play-by-play pushed over the scoreboard socket. This task owns that path end to end — the socket subscription model, the per-tick query shape, the client's play cache, and the handover from live derivation to the finalized gamelog.

## Plan

### Shipped 2026-09-20, DEPLOYED 2026-09-21T01:01Z in `d9aeb3f18`

- `getGameStatusByPlayerId` no longer dereferences an undefined `lastPlay`. The two filters above it disagree — one requires a possession team, the other also a `play_description` — so a game whose plays arrive undescribed threw inside `mapStateToProps` and blanked the whole page.
- `Scoreboard.register()` binds its `close` listener once per socket instead of once per re-registration. The client re-registers on every broadcast, so one socket accumulated a handler per poll cycle for the length of a game day.
- `getScoreboardUpdated` filters before `maxBy`, and the orphan-playStat branch stamps an epoch `updated`. Immutable's `maxCompare` prefers a nullish value, so one play lacking `updated` dropped the client's watermark to the epoch and the server answered with the whole week (~3.9 MB on a full Sunday).
- A new saga refetches gamelogs when an `END_GAME` play arrives, after a finalization grace window. The `GET_GAMELOGS_*` request-history keys clear only on logout/sign-in/cancel/failure, so a tab opened before kickoff held zeroed placeholder rows for its whole life and never cleared the `is_live` styling.
- The subscription is keyed on the socket rather than the `user_id`, and the broadcast is split into its own method. Keying on the user meant closing one tab unregistered the other, the second registration overwrote the first watermark, and the `wss.clients` fan-out delivered one socket's diff to every socket that user had open.
- `build_play_stats_query` gains a `play_keys` scope (row-value `IN` over `(esbid, play_id)`), so the socket reads the stats it ships instead of every stat row in every game holding a changed play.

### Shipped 2026-09-21, DEPLOYED in `d9aeb3f18`

- **The watermark is scoped to the season and week the socket queries** (`4a9d5538f`). Two mismatches, both silent in the direction that stops the scoreboard. The store is keyed on week ALONE, so a past-season matchup lands prior-year plays in the live week's bucket, and a backfill re-enriching that season stamps today's instant — the watermark then claims progress the client never made and the socket has nothing to send. And the week was `fantasy_season_week`, a fantasy week indexing an NFL-week key, agreeing only because `register()` gates on `is_fantasy_regular_season` and closes one week before the two diverge. The rule moved into `@core/scoreboard/watermark.mjs` so a spec executes it rather than scanning source.
- **The charting view is registered in the season-column map, and its guard can see views** (`d9f19433b`). This cleared the orphaned red master, and all four failures were ONE live defect rather than two stale goldens plus a blind spec — see below.

### Open

Nothing. The deploy landed in `d9aeb3f18`, and the last live-slate check closed during the 2026-09-21 Monday night game — see the observations. The plays-store re-keying that sits underneath this work is its own lane, [[user:task/league/rekey-plays-store-on-season-and-week.md]].

### The orphaned red master, resolved

All four failures traced to one live defect. `view_nfl_player_play_charting_receiving` is targeted by an `apply_scope_to_query` call site in the route denominator and was absent from `PHYSICAL_YEAR_COLUMN`, so it resolved through the map's fallback to the vocabulary `year` — a column the view does not have. `player_routes` emitted `view_nfl_player_play_charting_receiving.year in (2024)`, a 42703 on every data view carrying the routes rate type.

The guard built to catch exactly that could not see it: the spec read `CREATE TABLE public.<name> (` out of `db/schema.postgres.sql`, so all 11 of the schema's VIEWs reported as "missing from the schema" rather than as unregistered — a diagnosis indistinguishable from a typo'd name. It now reads relation columns from the database `test/global.mjs` builds out of the same export, because 5 of the 11 views are CTE-based and one RECURSIVE, so their output columns need a real SQL parser.

The two goldens were stale because the denominator moved onto the charter in `fe77bffc4` — and the query they were stale against was WRONG, so regenerating first would have blessed the 42703. Code fixed, regenerated second, EXPLAINed third. The production plan now shows `Index Cond: (season_year = 2024)`, which is the partition pushdown that call site's comment exists for.

### The deploy gate, cleared

This gate is closed and is kept only for the lesson. The deploy shipped in `d9aeb3f18` at ack range 49, from a worktree pinned to `origin/master`.

The shape worth remembering: `origin/master` was 8 commits ahead of the deployed `d15938a34`, and 6 of those belonged to live sibling sessions. `docs/guides/ship.md` makes a deploy all-or-nothing over whatever `origin/master` holds, so a commit you cannot attribute means finding its owner rather than typing a larger number — and the count must be re-read immediately before the deploy, because it keeps moving. That is why this work, and four other continuations, sat parked for weeks on a range no single lane could clear.

### Left underneath, since CLOSED by its own lane

- **The plays store was keyed on week ALONE**, so the season collision reached every `get_plays` consumer — the scoreboard UI, gamelogs, the player Games tab — not just the watermark. That is fixed and deployed: [[user:task/league/rekey-plays-store-on-season-and-week.md]] re-keyed the store on `(season_year, week)` in `e09e3d07f`, live in production `a1dfd6139`. The watermark's own `season_year` filter is now redundant rather than wrong, and the note here that the orphan-playStat branch could not supply a year was WRONG — `/plays/stats` now projects `season_year`, which was a column the route had not asked for rather than a structural constraint.

Both prior open items are closed. All four commits are deployed in `d9aeb3f18` (ack range 49, shipped from a worktree pinned to `origin/master`), and the live-slate verification is done — see the observations. The re-keyed socket was confirmed RUNNING rather than merely on disk, by the changed log wording: `removing a socket for user_id(7)` where the old code wrote `removing user_id(1)`.

Two premises in the original brief were wrong and are corrected in the observations: `764b8f1ed` was already deployed before this session began, and the red master never gated the deploy — `fe77bffc4`, which causes all four of its failures, was already an ancestor of the deployed sha.

### Latent findings, triaged 2026-09-20

**Fixed and committed** (both with red controls run):

- `app/core/ws/service.js` — `discard_socket` now clears the keepalive interval. Detaching `onclose` is what stopped the timer ever being cleared, so every re-open over a live socket orphaned one for the life of the tab.
- `scripts/import-plays-nfl-v1.mjs` — a null `viewer`/`gameDetail` threw above the per-game try and aborted the remaining games in the week. Now guarded, counted as `games_malformed`, and failed at the end by its own oracle so the guard does not convert a loud abort into a silent hole.

**Closed, with the measurement:**

- **No index on `nfl_plays.updated`** — do not add one. Measured live mid-slate: the socket query runs in 2.07 ms on the existing partition index, with planning (3.67 ms) costing more than execution. An index here would tax the hottest write path in the system to save nothing.
- **`reconnect()` duplicate socket** — refuted. `openWS` discards the pending socket before opening the next, so no socket duplicates. The timer was the part that actually leaked, and it is fixed above.

**Held deliberately, not fixed:**

- **The scoreboard `initial_week` Tuesday rollback** — SUPERSEDED, see the watermark commit above. Two readings here were wrong and are corrected in the observations. The rollback moves `scoreboard.week`, which `getScoreboardUpdated` never read, so it was never the mechanism. And `getScoreboardUpdated` did not max over every play in the store: `get_plays` defaults its week and was already week-scoped. What was actually wrong was the week's DOMAIN and a missing `season_year` filter, both fixed in `4a9d5538f`.
- The plays-reducer dedupe key, the missing socket removal verb, the `is_valid` whole-game update deadlock, and the three scripts reading `nfl_play_stats` unfiltered — all reconfirmed as unreachable or benign, and left as the brief assessed them.

### Closed, do not re-open

**Play-stat revisions never reaching a connected client** was investigated at length and CLOSED. `nfl_plays.updated` only advances when a play column changes, and `nfl_play_stats` has no timestamp, so a stat-only correction is not rebroadcast. Measured on 2026-09-20: the genuinely invisible population is about **six stat rows per season**. The 2026 season carries 5,979 invalidated stat rows, but 2,775 are a scoring-inert `player_name` NULL-vs-empty-string churn carrying no `gsis_player_id`, and of the rest only 25 touch a stat id the fantasy calculator does not treat as a no-op. Enrichment already moves a play column for 19 of those 25, because `yards_gained` and the role pids are derived from the same stat rows.

Every proposed fix cost more than the defect. Adding a reset+re-insert cycle to every live poll would rewrite ~11,200 row-versions per minute against a table whose lifetime `n_tup_upd` is 1.67M, promote a once-per-game oracle into a slate-wide abort risk, and re-fire the `--final` re-finalization loop that a private module already documents. Detecting the change via `RETURNING` cannot see a retraction at all, since the retracted row is one the insert never touches.

**The `is_valid` consumer audit** was also run and closed on 2026-09-20. No live scoring path reads invalidated rows — the scoring path goes through `build_play_stats_query`, which filters `is_valid = true`. A review agent's list of five unfiltered consumers was four comment matches plus one referential check where including invalid rows is correct by design.

### Promoted out before close

The live-feed verification recipe — the authentication gate on the socket, the `is_fantasy_regular_season` gate, the cold-load requirement, the `WebSocket.prototype.send` frame-capture technique, and the ruling against an index on `nfl_plays.updated` — is promoted into [[user:text/league/matchup-page-architecture.md]] § Verifying the live feed against a real game. Those four preconditions each fail silently and cost two separate sessions a rediscovery, so they belong somewhere a future reader finds them without opening a closed task.