Skip to main content
← Back to list
01Issue
FeatureShippedSwamp Club
Assigneeskeeb

Relationships

#1658 One standing read: every scalar score consumer reads board_totals, not the ledger

Opened by keeb · 8/14/2026· Shipped 8/15/2026

An operative's canonical score has one owner-grain source — swamp.board_totals, rewritten in full by the score-rollup projector each tick — and exactly four surfaces read it: the three score boards and the paged leaderboard. Roughly thirty other consumers re-derive the same scalar from the ledger. #1654 moves the profile family (dossier, /u/{name}/activity headline, ghost dossier) onto the snapshot; this is the rest.

The rule that survives the sweep is not page-by-page, it is scalar vs series: a standing is one row and comes from the snapshot; a per-day series — trajectory, heatmap, combat log, score-history, the users API standings — is a different value the snapshot physically cannot hold, and keeps the walk. Stated that way there is no bifurcation left to explain.

Three splits that are already live

Same request, two substrates. routes/api/v1/leaderboard/page.ts ranks the board from board_totals, then at :155 and :194 calls resolveTiersByUsernames -> totalsByUsernames -> the ledger to colour those same names. One request, one set of operatives, two reads of the same number, free to disagree by a projector tick — a name painted a tier above where its own row's score puts it.

Same boards, two substrates. routes/api/v1/leaderboard/locate.ts reads the live ledger (findScoreTarget, scoreRankOf, sliceByScoreRank, windowScoreOf, windowRankOf) for the very boards it locates into. Documented as deliberate, but it means 'where am I' and the board it points at can disagree.

The watcher re-derives the projection. lib/app/tier-crossing-watch.ts:222 calls reads.resolvedTotals() — sum(amount) GROUP BY owner over the whole ledger — every 60s. That is precisely what board_totals holds. It is the only unkeyed whole-ledger scan left in the hot path, and it is the sole input to every badge mint, invite payout, Discord role sync and tier_up announcement in the system.

Why the watcher is the interesting one

Pointing it at the snapshot deletes a whole-population ledger aggregate per tick per pod, and it is safe from double-firing: crossings are claimed against a persisted high-water mark (claimCrossing(username, toOrdinal), :275), so a transient dip — the tombstone arm zeroing an owner mid-identity-move — cannot un-announce anything, and a re-rise to an already-claimed ordinal is idempotent. Only a genuine rise above the mark fires.

It also collapses a documented trap rather than adding one. #1574 has the watcher write a score_rollup_signal run request so the projector runs BEFORE the freshness marker advances — that entire dance exists because the thing that detects a crossing and the things that display it read different substrates. If the detector reads the projector's output, the ordering is inherent: projector writes, watcher sees it, watcher announces — and an announcement can no longer precede what the board and the dossier show.

Two mechanical adjustments: resolvedTotals filters ghost = 0 AND total > 0 in SQL, which becomes is_ghost = 0 AND t_all > 0 against the snapshot's own columns; and the snapshot is keyed on resolved owner, so a rename or identity move blanks a row for a cadence — the watcher already has a deferred path for operatives it cannot resolve, so it degrades correctly.

This is the part that needs its own review: moving it changes WHEN real side effects fire, from 60s ledger freshness to projector cadence. Badges, payouts and Discord roles are not a progress bar.

The remainder, by kind

  • Write triggers — the tier watcher and everything hanging off it; the Discord role sync (services/discord-bot/lib/reconcile.ts:108,146 -> POST /api/v1/tiers -> totalsByUsernames); the merge-winner pick (lib/app/admin/merge-operatives.ts:122); the name-style entitlement (lib/app/update-name-style.ts:58).
  • Rank / locate — the whole locate.ts family.
  • Display-only tier colouring — sixteen surfaces via resolveTiersByUsernames (landing, feed, lab, extensions, inbox, collective settings, admin user search, genesis leaderboard, ...), plus the collective roster which sorts by score (routes/o/[slug]/members.tsx:97) and the metrics active-users detail.
  • Cached projections — /og/u/, /og/p/, /api/v1/u//card, all via loadHeadlineTotal; their cache policy already turns on reason === 'failed', so the availability union has to survive the substrate swap.
  • /api/v1/users/ — mixed: its canonicalScore is scalar, its standings/scoreHistory are series.

Two things worth fixing while in here

lib/app/update-name-style.ts:58 calls deps.scoreReads.profileScore(...) raw — the one scalar-standing consumer outside the degradation seam. A ClickHouse failure throws out of a WRITE path (a 500 on saving a name style), and being outside the breaker it re-pays the full read ceiling on every attempt instead of fast-failing. Unifying the read behind one seam fixes it by construction.

routes/api/v1/users/[username]/score-card.ts:69 uses loadProfileScore — the full cumulative walk — to serve a scalar, and its comment at :66 argues nothing cheaper exists. loadHeadlineTotal (a keyed scalar aggregate) already is cheaper, and the snapshot is cheaper still.

Measurements (local stack, 10,722 owners)

A keyed board_totals FINAL WHERE owner = {username} lookup: 1.6 ms, 18,914 rows read — 2-3 parts x one 8192-row granule, so constant in both owner population and history depth. The projector's own recompute of that one owner's aggregate: 379,923 rows / 17 MiB / 11 ms. t_all and active_days matched the stored row exactly.

Follow-on to #1654.

02Bog Flow
✓OPEN✓TRIAGED✓IN PROGRESS✓SHIPPED+ 1 MOREASSIGNED+ 8 MOREREVIEW+ 2 MOREPR_LINKED

Shipped

8/15/2026, 1:02:17 AM

No activity in this phase yet.

03Sludge Pulse
keeb assigned keeb8/14/2026, 10:32:37 PM

Sign in to post a ripple.