One /leaderboard render fires 6 ClickHouse queries, totalling ~2.36M rows read and ~660 MiB peak memory. Four of them run concurrently via Promise.all in lib/app/leaderboard-view.ts:100-105.
The streak board alone accounts for 940k rows read / 182 MiB peak, versus ~366k rows / ~150 MiB for a score board.
A 12-concurrent-viewer test produced 48 queries, 7.66 GiB summed memory, and page latency of 0.12s -> 0.99s.

All of this runs under a 5s max_execution_time (lib/infrastructure/clickhouse/clickhouse-score-reads.ts:462, SCORE_READ_CEILING_S) with a 7s client abort, behind a 3-strike / 30s-cooldown breaker — so the headroom between "slow" and "board goes unavailable" is thin.

Cause

ownedDays in lib/infrastructure/clickhouse/clickhouse-score-reads.ts:246-280 unions three day sources:

score_daily (via ownedGrants)
swamp.cli_daily
swamp.days_active

streaksSql (same file, :291-310) then does groupUniqArray(toInt32(day)) per owner for the gaps-and-islands streak computation. The triple union is what makes it the heaviest read on the page.

Measured overlap — the union is buying almost nothing

swamp.days_active is a 99.96% superset of the other two:

Of 30,757 distinct (distinct_id, day) pairs in cli_daily, only 8 are absent from days_active.
Of 197k pairs in score_daily, only 104 are absent.

So the three-way union triples the scan in order to recover 112 pairs.

Proposal

Add a compact table:

swamp.active_days_by_id (distinct_id String, day Date)
ENGINE = ReplacingMergeTree
ORDER BY (distinct_id, day)

Fed by materialized views:

from swamp.events — covers the days_active + cli_daily day sets
from swamp.score_grants — covers the grant-only days

ownedDays then becomes one ~257k-row keyed scan plus the existing identity_map join, instead of 940k rows across three sources.

Constraint that must hold

Identity stays late-bound. Key the table by distinct_id, never by username/owner. Resolution to owner happens at read time via identity_map, exactly as today — an operative is many machines, and freezing the owner into the rollup would re-break that.

An MV is not a cache: it fires on INSERT, so the streak board stays realtime. No freshness regression.

Notes

Prod ClickHouse is an external 3-node replicated cluster and the MVs are local, so the DDL has to reach all three nodes — same shape as prior schema changes, see infrastructure/clickhouse/CLAUDE.md.
This is one of three issues from the same 2026-08-01 /leaderboard investigation. The other two cover the shared circuit breaker and two cheaper scan pushdowns.

02Bog Flow

Open

8/2/2026, 2:12:05 AM

No activity in this phase yet.

03Sludge Pulse

keeb commented 8/2/2026, 2:12:29 AM

Siblings from the same 2026-08-01 /leaderboard investigation: #1496 (shared score-read circuit breaker) and #1497 (ghost-gate dictionary + eventCounts pushdown).

Document --restricted-commands flag for swamp serve

vault put: pass tags through to extension provider CreateSecret/setSecret calls

Docs: update manual for 23 new --server commands

Update VaultProvider interface in @systeminit/swamp-testing to support optional tags in put()

Docs: document --grants-file flag for swamp serve

Docs: document --server support for serve reload

Docs: add --tag flag to vault put reference

Docs: update manual for --detach-runs deprecation and HA auto-detection

Document the Codex plugin in swamp docs

Add --server support to CLI commands missing it

Document the swamp Claude Code plugin in the swamp documentation

Make --server a global CLI option

Add --server support to model cancel (server-side run cancellation)

Load grants from external source on swamp serve startup

swamp vault put should support tags for secret governance

Improve hot reload of the deamon, include extension source/bundle changes

Authenticated users must re-login after swamp serve restart

Add --server support to serve reload

Harden queue claim semantics: atomic claims + status-guarded markDelivered/markFailed across app queues

Auto-detect HA mode and deprecate --detach-runs flag

version-drift check (from #236) is a raw string-inequality, false-positives on multi-model manifests and metadata-only bumps

Resend newsletter audience sync: contacts, segments, backfill, unsubscribe sync-back

Move per-login token secrets from vault to encrypted control-plane store

Docs: document macOS TLS trust limitation and SSL_CERT_FILE workaround

Validate swamp serve deployment stack on startup

Docs: serve config file (.swamp/serve.yaml) and --config flag

Serve config file — read swamp serve configuration from YAML

Deno runtime does not trust ISRG Root YR — extensions fail with valid Let's Encrypt certs

Docs: update hot-reload reference to include trust list refresh

Cross-instance client re-attach for multi-instance swamp serve

vercel codegen: inline rawSyncResult in non-unwrap sync path

vercel codegen: unused listAll import blocks extension publish for 4 models

vercel codegen: add round-trip idempotency test for the pipeline

Operatives button on Collectives page does nothing

Track active runs in the control-plane store for cross-instance visibility

validateGlobalArgInputRefs checks step-level inputs but step schema rejects 'inputs' key

Docs: document continuous reconciliation and multi-instance serve flags

Docs: update workflow guard skip output to include guardExpression and guardResult

Make it aware hot reload on the deamon does not take into account trust list changes, or do take into account the trust list for the hot reload.

workflow validate: new 'GlobalArgument input references' check resolves model-definition ${{ inputs.* }} against workflow inputs, false-failing steps that supply the input

Continuous reconciliation for multi-instance swamp serve

Split the score-read circuit breaker so leaderboard pressure can't blank profile scores

oauth_sign_in telemetry records provider as the literal ":id" for every event

Ghost profile bypasses the score-read seam and 500s on a slow ledger

Profile score section blocks the render on a ClickHouse read

Council: set the scoring balance — command tiers, farm-decay parameters, cross-category ratio

Scoring coverage gaps: instrument drop-off, score ripples/mentions, reward invite-converts

Push down the two cheap leaderboard scans: ghost-gate dictionary and eventCounts owner filter

Materialize an active-days-by-id table for the streak board

score-rollup-projector: O(all-time ledger) closure + per-pod fan-out saturates prod ClickHouse CPU

Startup cache hydration for swamp serve instance replacement

Output a log line for steps skipped due to guards

extension quality scorer misreads a template-literal import specifier in generated code as an unresolvable bare import, hard-erroring the whole extension as UNSCORABLE

Add @swamp/kubernetes/statefulset model type

Docs: access token commands reference says plaintext is 'shown once'

Document --restricted-model-types flag for swamp serve

swamp auth server-login ignores SWAMP_SERVE_URL

s3-datastore extension: pullChanged writes namespace-stripped paths to local cache

serve oauth: an unrecognized device-token error kills the whole serve process instead of the bootstrap

s3-datastore: bidirectional sync pull writes namespace-stripped copies to cache root, wedging push with no supported recovery

CLI reports 'HTTP/2 not supported by this client' when the server actually returned 401 — masks auth failures as a transport error

access token mint: allow reusing a revoked token name

extension quality --json exits 1 with 'rubric factors missing' even when a full score was computed (unearned factor, not uncomputable)

serve oauth: startDeviceGrant discards the OAuth error field, making device-code failures undiagnosable

access token rotate: add --vault flag to override inherited vault

Extensions without a repository can't receive issues — including the issue asking them to add one

serve oauth bootstrap: device flow rate-limited to a 45-second window by validateClient counter, shared globally across tenants

workflow edit: after saving an invalid workflow, re-running edit reports "Workflow not found"

Authentication via Google Broken

Plus-addressed emails stripped on invite acceptance, breaking collective join and quest reward

Profile edit broken on Safari

Configurable write paths for object-creating commands (repo-relative swamp root)

Docs: document rollbackOnFailure with safety guidance for external-dependency models

Remote execution tutorial: sample runner output is missing the stdout line that #1462 restored

seadex-latent-bugs: latent product bugs pinned by the wave-2b test backfill

Remote step stdout is not streamed to the workflow runner (log artifact is transferred correctly)

Redactor false positives: HH:MM:SS timestamps masked as IPs, SHA-256 digests masked as secrets (both inside code fences)

Remote execution tutorial: worker token list output and inline workflow-summary block no longer match CLI output

Add rollbackOnFailure for method-level write atomicity

docs: working-with-data tutorial uses removed depends_on step key, blocking the tutorial