Skip to main content
← Back to list
01Issue
BugOpenSwamp Club
AssigneesNone

Relationships

#1579 Leaderboard board windows re-anchor on wall-clock while the rest of the view is frozen at asOf

Opened by keeb · 8/10/2026

Split out of #1573 (phase 1 of epic #1572) so that the query-cache change there stays surgically revertible. Found while auditing what determines the board read's cache key.

The bug

buildBoardsViewInner (lib/app/leaderboard-view.ts:105) reads the three score boards with an asOf pin, but computes the two window anchors from wall-clock now:

query.findTopScoreBoards({
  limit,
  weekSince: utcDateDaysAgo(7),   // <- Date.now()
  todaySince: utcDateDaysAgo(1),  // <- Date.now()
  asOf,                           // <- the frozen instant
})

utcDateDaysAgo (lib/date.ts:6) derives from Date.now(), so two parameters of the same query move on two different clocks.

Why it matters

The asOf pin exists to freeze a paginating session so rows and the pagination that follows them come from one instant (#1055). That property holds for the alltime column and for the as-of bound inside the substrate, but not for the today/week window predicates: sumIf(amount, day > {todaySince:Date}).

A session that pins before UTC midnight and paginates after it gets an alltime column frozen at yesterday sitting beside a today column that silently re-anchored to the new day. The two columns of one response describe different instants. It is a narrow window (once a day, only for a session that spans it) and nobody has reported it, which is why this is filed rather than hotfixed.

Fix

Derive the anchors from asOf rather than from now — e.g. a utcDateDaysBefore(asOf, n) alongside the existing utcDateDaysAgo. Same shape the substrate already uses: resolvedRows, ownedDays and streaksSql all move their anchors with asOf when withAsOf is set (clickhouse-score-reads.ts:151-159, :258, :296), and the comments there spell out the reasoning — "a session that crosses local midnight can't drop a day that hasn't rolled into score_daily yet". The view layer is the one place that did not get the same treatment.

Bonus, and the reason it surfaced now

It also makes the board read's cache key a pure function of (asOf, limit). #1573 is opting this read into ClickHouse's server-side query cache; with the anchors on a separate clock the key carries a second, independently-moving dimension. Not a correctness problem for the cache — the anchors are day-grained so they are stable within a day — but it means the key is not derivable from the pin alone, which is worth fixing before phase 3 builds a snapshot table on top of it.

Verification

tests/app/leaderboard_view_test.ts — pin that the anchors derive from asOf, with a case straddling UTC midnight.

02Bog Flow
◉OPEN○TRIAGED○IN PROGRESS○SHIPPED

Open

8/10/2026, 7:55:04 PM

No activity in this phase yet.

03Sludge Pulse

Sign in to post a ripple.