feat: Real-time bot statistics on homepage with Redis caching

[BillChirico]

Summary

Pull real bot statistics from Discord and display them on the public homepage. Stats should be cached in Redis to avoid hammering the Discord API on every page load.

Metrics to Display

• Server count — number of guilds the bot is in
• Total members — aggregate member count across all guilds
• Commands served — total slash command invocations (tracked in DB)
• Active conversations — current AI conversation count
• Uptime — bot process uptime
• Messages processed — total messages handled

Implementation

Backend (/api/public/stats)

• New public endpoint (no auth required, rate-limited)
• Collect stats from Discord client (client.guilds.cache.size, member counts, etc.)
• Query DB for command usage, message counts
• Cache results in Redis with configurable TTL (default: 5 minutes)
• Return JSON payload with all metrics + cachedAt timestamp

Frontend (Homepage)

• Animated counter components for each stat
• Auto-refresh on an interval (e.g., every 60s)
• Graceful fallback if API is unavailable (show last known or placeholder)
• Mobile-responsive grid layout

Redis Caching

• Key: bot:stats:public
• TTL: 300s (5 min) — configurable via config.stats.cacheTtlSeconds
• Invalidate on significant events (guild join/leave) or let TTL expire naturally

Acceptance Criteria

• /api/public/stats returns server count, member count, uptime, and at least 2 other metrics
• Response is served from Redis cache on subsequent requests within TTL
• Homepage renders stats with animated counters
• Stats degrade gracefully when Redis or Discord data is unavailable
• Endpoint is rate-limited (30 req/min, matching existing public endpoints)
• Tests cover the API endpoint and caching logic

Closes #(self)

[coderabbitai]

Coding Plan

Summary

• Extends the existing public API pattern (following community.js) with a new /public/stats route that requires no auth but applies rate limiting
• Leverages the established cacheGetOrSet pattern for Redis caching with a new TTL constant
• Reuses the existing AnimatedCounter component on the frontend, adding a custom hook for data fetching with auto-refresh
• Maintains graceful degradation at both layers: backend returns partial data if DB unavailable, frontend shows last known values on fetch failure

Design Choices

Design Choice 1: Interpretation of "Messages processed" metric

Options Considered:

1. SUM(messages_sent) FROM user_stats — represents all Discord messages the bot has tracked/processed
2. COUNT(*) FROM conversations — represents only messages processed by the AI module

Chosen Option: 1

Rationale: Using user_stats.messages_sent as it represents the broader "messages handled" metric and is more impressive for a landing page stat.

Design Choice 2: Interpretation of "Active conversations" metric

Options Considered:

1. Count distinct channels with any conversation history (all-time)
2. Count distinct channels with conversation activity in the last 24 hours
3. Count distinct channels with activity in the last 7 days

Chosen Option: 2

Rationale: 24-hour window provides a meaningful "active" metric that reflects current engagement without being overly narrow.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Backend Public Stats Endpoint

Create a new public API endpoint that collects bot statistics from the Discord client and database, caches results in Redis, and returns them with rate limiting matching existing public endpoints.

Task 1: Add TTL Constant for Public Stats Cache

Add a new TTL constant for the public stats cache duration, following the existing pattern in the codebase.

• Add TTL.PUBLIC_STATS to src/utils/cache.js with default 300 seconds (5 min), overridable via REDIS_TTL_PUBLIC_STATS env var
• Follow the exact pattern of existing TTL constants: PUBLIC_STATS: Number(process.env.REDIS_TTL_PUBLIC_STATS) || 300

Task 2: Create Public Stats Route Handler

Implement the stats collection and caching logic in a new route file.

• Create src/api/routes/public.js as a new route file for public endpoints
• Import redisRateLimit from middleware and create a limiter with windowMs: 60 * 1000, max: 30, keyPrefix: 'rl:public' (matching community.js pattern)
• Apply rate limiter to all routes via router.use()
• Implement GET /stats handler that:
  • Uses cacheGetOrSet('bot:stats:public', fetchStats, TTL.PUBLIC_STATS) pattern
  • The fetchStats factory function collects stats from req.app.locals.client (guilds.cache.size, aggregate memberCount, process.uptime())
  • Queries DB via getPool() for: COUNT(*) FROM command_usage, COUNT(DISTINCT channel_id) FROM conversations WHERE created_at > NOW() - INTERVAL '24 hours', SUM(messages_sent) FROM user_stats
  • Uses Promise.all() for parallel DB queries
  • Returns JSON with: serverCount, totalMembers, commandsServed, activeConversations, uptimeSeconds, messagesProcessed, cachedAt (ISO timestamp)
• Handle graceful degradation: if DB unavailable, return Discord-only stats with null for DB metrics
• Add OpenAPI JSDoc comment block for documentation

Task 3: Register Public Stats Route

Wire up the new route in the API router without authentication middleware.

• In src/api/index.js, import the public router from ./routes/public.js
• Register with router.use('/public', publicRouter) in the public routes section (alongside health, community, auth)
• No requireAuth() or auditLogMiddleware() — this is a public endpoint

🤖 Prompt for AI agents

Implement the backend public stats endpoint. The goal is to create a new
`/api/v1/public/stats` route that collects bot statistics, caches them in Redis,
and applies rate limiting — following the existing `community.js` public route
pattern.

**Step 1: Add TTL constant in `src/utils/cache.js`**
- Add `PUBLIC_STATS` to the existing `TTL` constants object using the pattern:
`PUBLIC_STATS: Number(process.env.REDIS_TTL_PUBLIC_STATS) || 300`

**Step 2: Create `src/api/routes/public.js`**
- Import `redisRateLimit` from the middleware module, `cacheGetOrSet` and `TTL`
from `src/utils/cache.js`, and `getPool` from the DB module
- Create a rate limiter with `windowMs: 60 * 1000`, `max: 30`, `keyPrefix:
'rl:public'` — matching the pattern in `community.js`
- Apply the rate limiter to all routes via `router.use()`
- Implement a `GET /stats` handler:
- Calls `cacheGetOrSet('bot:stats:public', fetchStats, TTL.PUBLIC_STATS)` where
`fetchStats` is a factory function
- `fetchStats` reads from `req.app.locals.client`: `guilds.cache.size`
(serverCount), aggregate `memberCount` across all guilds, and `process.uptime()`
(uptimeSeconds)
  - Runs three DB queries in parallel via `Promise.all()` using `getPool()`:
    - `COUNT(*) FROM command_usage`
- `COUNT(DISTINCT channel_id) FROM conversations WHERE created_at > NOW() -
INTERVAL '24 hours'`
    - `SUM(messages_sent) FROM user_stats`
- Returns JSON: `{ serverCount, totalMembers, commandsServed,
activeConversations, uptimeSeconds, messagesProcessed, cachedAt }` where
`cachedAt` is an ISO timestamp
- Graceful degradation: if the DB is unavailable, return Discord-only stats with
`null` for all DB-sourced metrics
- Add an OpenAPI JSDoc comment block documenting the endpoint

**Step 3: Register the route in `src/api/index.js`**
- Import the new public router from `./routes/public.js`
- Register it with `router.use('/public', publicRouter)` in the public routes
section, alongside health, community, and auth routes
- Do NOT apply `requireAuth()` or `auditLogMiddleware()` to this route

Phase 2: Frontend Stats Integration

Update the homepage to fetch real statistics from the new endpoint and display them with the existing animated counter component, including auto-refresh and fallback handling.

Task 1: Create Next.js API Proxy Route

Add a frontend API route that proxies requests to the bot's public stats endpoint.

• Create web/src/app/api/stats/route.ts
• Follow the existing proxy pattern from bot-api-proxy.ts but without auth (public endpoint)
• Use getBotApiConfig() and buildUpstreamUrl() helpers
• Call proxyToBotApi() with the upstream URL — no authorizeGuildAdmin needed
• Set export const dynamic = 'force-dynamic' for no caching at the Next.js layer

Task 2: Create usePublicStats Hook

Implement a custom React hook for fetching and auto-refreshing public statistics.

• Create web/src/hooks/use-public-stats.ts
• Define TypeScript interface for stats shape: { serverCount, totalMembers, commandsServed, activeConversations, uptimeSeconds, messagesProcessed, cachedAt }
• Implement fetch logic with AbortController for cleanup (matching existing hook patterns)
• Add state for stats, loading, error
• Fetch on mount and set up 60-second interval for auto-refresh
• Store last successful response in a ref for fallback on error
• On fetch error, return last known stats (if any) rather than clearing display
• Clean up interval and abort controller on unmount

Task 3: Update Stats Component with Real Data

Replace hardcoded values in the Stats component with data from the new hook.

• Modify web/src/components/landing/Stats.tsx
• Import and use usePublicStats hook
• Update the 3-column grid to display 6 stats (adjust to 2 rows of 3, or 3 rows of 2 on mobile)
• Map stats to display items: Servers (serverCount), Members (totalMembers), Commands (commandsServed), Conversations (activeConversations), Uptime (formatted from uptimeSeconds), Messages (messagesProcessed)
• Add loading state: show skeleton placeholders or keep showing previous values during refresh
• Add error fallback: display last known values or reasonable placeholders (0 or "—")
• Keep AnimatedCounter component; it already handles the counting animation
• Format uptime as human-readable (e.g., "5d 12h" or just days if > 1 day)

🤖 Prompt for AI agents

Implement the frontend stats integration. The goal is to wire up the new
`/api/v1/public/stats` backend endpoint to the homepage Stats component using a
proxy route and a custom hook.

**Step 1: Create `web/src/app/api/stats/route.ts`**
- Follow the existing proxy pattern from `bot-api-proxy.ts`, but without any
auth
- Use `getBotApiConfig()` and `buildUpstreamUrl()` helpers to construct the
upstream URL pointing to `/api/v1/public/stats`
- Call `proxyToBotApi()` with the upstream URL — no `authorizeGuildAdmin` step
- Export `const dynamic = 'force-dynamic'` to disable Next.js-layer caching

**Step 2: Create `web/src/hooks/use-public-stats.ts`**
- Define a TypeScript interface for the stats shape: `{ serverCount,
totalMembers, commandsServed, activeConversations, uptimeSeconds,
messagesProcessed, cachedAt }`
- Implement the hook with state for `stats`, `loading`, and `error`
- Use `AbortController` for fetch cleanup, matching existing hook patterns in
the codebase
- Fetch stats on mount; set up a 60-second interval for auto-refresh
- Store the last successful response in a `ref` to use as a fallback
- On fetch error, return the last known stats (from the ref) rather than
clearing the display
- Clean up both the interval and the abort controller on unmount

**Step 3: Modify `web/src/components/landing/Stats.tsx`**
- Import and call `usePublicStats` hook
- Expand the stats grid from 3 columns to 6 stats total (2 rows of 3, or 3 rows
of 2 on mobile)
- Map hook data to display items: Servers (`serverCount`), Members
(`totalMembers`), Commands (`commandsServed`), Conversations
(`activeConversations`), Uptime (formatted from `uptimeSeconds`), Messages
(`messagesProcessed`)
- Format `uptimeSeconds` into a human-readable string (e.g., "5d 12h", or just
days if > 1 day)
- During loading, show skeleton placeholders or retain previously loaded values
- On error, fall back to last known values or placeholder text (0 or "—")
- Retain the existing `AnimatedCounter` component for counting animation

Phase 3: Testing

Add test coverage for the new API endpoint, verifying stats collection, caching behavior, and error handling.

Add Public Stats Route Tests

Create comprehensive tests for the public stats endpoint.

• Create tests/api/routes/public.test.js
• Follow existing test patterns: mock logger, db, cache, config before importing app
• Use createApp() factory with mock Discord client that has stubbed guilds.cache
• Test cases to cover:
  • Returns all expected stat fields with correct types
  • Returns cached data on subsequent requests (verify cache hit by checking DB query count)
  • Rate limiting returns 429 after 30 requests in 1 minute
  • Graceful degradation: returns partial stats when DB is unavailable
  • Discord client unavailable: returns appropriate error or degraded response
• Mock cacheGetOrSet to verify caching behavior without actual Redis
• Mock getPool().query to return controlled test data for each query

🤖 Prompt for AI agents

Add test coverage for the new public stats API endpoint. The goal is to create a
comprehensive test suite in `tests/api/routes/public.test.js` following existing
test patterns.

- Mock logger, db, cache, and config modules before importing the app, following
the existing test setup pattern
- Use a `createApp()` factory that injects a mock Discord client with stubbed
`guilds.cache`
- Mock `cacheGetOrSet` to control and verify caching behavior without requiring
a real Redis connection
- Mock `getPool().query` to return controlled, predictable test data for each of
the three DB queries
- Implement the following test cases:
- **Happy path**: response includes all expected fields (`serverCount`,
`totalMembers`, `commandsServed`, `activeConversations`, `uptimeSeconds`,
`messagesProcessed`, `cachedAt`) with correct types
- **Cache hit**: subsequent requests return cached data; verify DB query count
does not increase on cache hit
- **Rate limiting**: after 30 requests within 1 minute, the endpoint returns
HTTP 429
- **DB unavailable (graceful degradation)**: when `getPool().query` rejects, the
response still returns Discord-sourced stats with `null` for DB metrics
- **Discord client unavailable**: when `req.app.locals.client` is absent or
throws, returns an appropriate error or degraded response

Research

The backend is an Express.js API with routes organized in src/api/routes/, registered centrally in src/api/index.js. Public routes (like community.js) apply custom rate limiting via redisRateLimit(). Redis caching uses cacheGetOrSet() from src/utils/cache.js with TTL constants. The Discord client is accessible via req.app.locals.client, and DB queries use the pg pool from getPool(). The Next.js frontend fetches data through proxy routes in web/src/app/api/, and the homepage already has an AnimatedCounter component in Stats.tsx with hardcoded values.

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Implement the backend public stats endpoint. The goal is to create a new
`/api/v1/public/stats` route that collects bot statistics, caches them in Redis,
and applies rate limiting — following the existing `community.js` public route
pattern.

**Step 1: Add TTL constant in `src/utils/cache.js`**
- Add `PUBLIC_STATS` to the existing `TTL` constants object using the pattern:
`PUBLIC_STATS: Number(process.env.REDIS_TTL_PUBLIC_STATS) || 300`

**Step 2: Create `src/api/routes/public.js`**
- Import `redisRateLimit` from the middleware module, `cacheGetOrSet` and `TTL`
from `src/utils/cache.js`, and `getPool` from the DB module
- Create a rate limiter with `windowMs: 60 * 1000`, `max: 30`, `keyPrefix:
'rl:public'` — matching the pattern in `community.js`
- Apply the rate limiter to all routes via `router.use()`
- Implement a `GET /stats` handler:
- Calls `cacheGetOrSet('bot:stats:public', fetchStats, TTL.PUBLIC_STATS)` where
`fetchStats` is a factory function
- `fetchStats` reads from `req.app.locals.client`: `guilds.cache.size`
(serverCount), aggregate `memberCount` across all guilds, and `process.uptime()`
(uptimeSeconds)
  - Runs three DB queries in parallel via `Promise.all()` using `getPool()`:
    - `COUNT(*) FROM command_usage`
- `COUNT(DISTINCT channel_id) FROM conversations WHERE created_at > NOW() -
INTERVAL '24 hours'`
    - `SUM(messages_sent) FROM user_stats`
- Returns JSON: `{ serverCount, totalMembers, commandsServed,
activeConversations, uptimeSeconds, messagesProcessed, cachedAt }` where
`cachedAt` is an ISO timestamp
- Graceful degradation: if the DB is unavailable, return Discord-only stats with
`null` for all DB-sourced metrics
- Add an OpenAPI JSDoc comment block documenting the endpoint

**Step 3: Register the route in `src/api/index.js`**
- Import the new public router from `./routes/public.js`
- Register it with `router.use('/public', publicRouter)` in the public routes
section, alongside health, community, and auth routes
- Do NOT apply `requireAuth()` or `auditLogMiddleware()` to this route
===============================================================================

Task: 2

Implement the frontend stats integration. The goal is to wire up the new
`/api/v1/public/stats` backend endpoint to the homepage Stats component using a
proxy route and a custom hook.

**Step 1: Create `web/src/app/api/stats/route.ts`**
- Follow the existing proxy pattern from `bot-api-proxy.ts`, but without any
auth
- Use `getBotApiConfig()` and `buildUpstreamUrl()` helpers to construct the
upstream URL pointing to `/api/v1/public/stats`
- Call `proxyToBotApi()` with the upstream URL — no `authorizeGuildAdmin` step
- Export `const dynamic = 'force-dynamic'` to disable Next.js-layer caching

**Step 2: Create `web/src/hooks/use-public-stats.ts`**
- Define a TypeScript interface for the stats shape: `{ serverCount,
totalMembers, commandsServed, activeConversations, uptimeSeconds,
messagesProcessed, cachedAt }`
- Implement the hook with state for `stats`, `loading`, and `error`
- Use `AbortController` for fetch cleanup, matching existing hook patterns in
the codebase
- Fetch stats on mount; set up a 60-second interval for auto-refresh
- Store the last successful response in a `ref` to use as a fallback
- On fetch error, return the last known stats (from the ref) rather than
clearing the display
- Clean up both the interval and the abort controller on unmount

**Step 3: Modify `web/src/components/landing/Stats.tsx`**
- Import and call `usePublicStats` hook
- Expand the stats grid from 3 columns to 6 stats total (2 rows of 3, or 3 rows
of 2 on mobile)
- Map hook data to display items: Servers (`serverCount`), Members
(`totalMembers`), Commands (`commandsServed`), Conversations
(`activeConversations`), Uptime (formatted from `uptimeSeconds`), Messages
(`messagesProcessed`)
- Format `uptimeSeconds` into a human-readable string (e.g., "5d 12h", or just
days if > 1 day)
- During loading, show skeleton placeholders or retain previously loaded values
- On error, fall back to last known values or placeholder text (0 or "—")
- Retain the existing `AnimatedCounter` component for counting animation
===============================================================================

Task: 3

Add test coverage for the new public stats API endpoint. The goal is to create a
comprehensive test suite in `tests/api/routes/public.test.js` following existing
test patterns.

- Mock logger, db, cache, and config modules before importing the app, following
the existing test setup pattern
- Use a `createApp()` factory that injects a mock Discord client with stubbed
`guilds.cache`
- Mock `cacheGetOrSet` to control and verify caching behavior without requiring
a real Redis connection
- Mock `getPool().query` to return controlled, predictable test data for each of
the three DB queries
- Implement the following test cases:
- **Happy path**: response includes all expected fields (`serverCount`,
`totalMembers`, `commandsServed`, `activeConversations`, `uptimeSeconds`,
`messagesProcessed`, `cachedAt`) with correct types
- **Cache hit**: subsequent requests return cached data; verify DB query count
does not increase on cache hit
- **Rate limiting**: after 30 requests within 1 minute, the endpoint returns
HTTP 429
- **DB unavailable (graceful degradation)**: when `getPool().query` rejects, the
response still returns Discord-sourced stats with `null` for DB metrics
- **Discord client unavailable**: when `req.app.locals.client` is absent or
throws, returns an appropriate error or degraded response

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For design choice 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!

[coderabbitai]

🔗 Related PRs

#1 - Health Status Command [merged]
#108 - feat: AFK system [merged]
#120 - feat: public community page — leaderboards, showcases, and stats [merged]
#210 - feat: bot status/custom activity [merged]
#220 - feat(redis): Comprehensive Redis integration (#177) [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!