erika/docs/adr/0002-realtime-leaderboards.md

ADR 0002: Real-time leaderboard read model

• Status: Accepted
• Date: 2026-07-24

Context

The Discord bot is the authoritative writer for member XP and cumulative voice time. The website needs public, paginated leaderboards that update while open without coupling the form database, migrations, or website write paths to the bot database. Voice history also contains channels that may no longer exist in the Discord guild.

Decision

1. The website uses a separate Drizzle/Postgres.js module configured only through LEADERBOARD_DATABASE_URL. Its client starts read-only sessions, uses a small connection pool and short timeouts, and is not exported. The normal DATABASE_URL, schema barrel, Drizzle configuration, and migrations remain unchanged.
2. Leaderboard database reads are uncached. A Server Component reads the current values on navigation and after router.refresh(). Discord voice and Stage channel metadata may remain in memory for five minutes.
3. The XP board includes stored level 1+ users and ranks cumulative XP descending. The voice board sums time across current Discord voice and Stage channels, or one selected current channel. Historical/deleted channels are excluded.
4. Both boards use shared competition ranks: equal values share a rank and the next rank skips the tied positions. Equal-value rows use name and Discord ID for stable display ordering.
5. The existing Redis-backed SSE route gains one public leaderboards topic. The bot publishes minimal XP or channel-scoped VC events only after its database transaction commits. The browser validates events, filters them to the visible board, debounces refreshes, and refreshes again after reconnecting.
6. Redis and SSE are notification paths, not the source of truth. A transport failure does not make the page unusable; navigation still performs a fresh database read.

Consequences

• The bot remains the only process allowed to mutate leaderboard tables.
• A website database credential should also be restricted to read-only access at the PostgreSQL role level; the client session setting adds defense in depth.
• Voice totals intentionally change when channels are created or removed because the default scope is the guild’s current voice surface.
• Open pages update quickly without polling, while reconnection and normal navigation recover missed notifications.
• Discord channel lookup and leaderboard database failures can be reported separately.