45 lines
2.4 KiB
Markdown
45 lines
2.4 KiB
Markdown
# 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.
|