Refactor and improve performance of /stats

[dvassallo]

The /stats page is currently so slow that it often times out. Also, there is a lot of code duplication that should be refactored. The stats logic is used in the /stats page and all its subpages, in the users page (https://smallbets.com/users/1), and in the rooms stats page (https://smallbets.com/rooms/1/stats). All these pages should be using the exact same logic.

[AdityaTeltia]

Hi @dvassallo @raulpopadineti @renatonitta, I’ve already opened a PR (#122) as a starting point for this issue to help ground the discussion in code. Would appreciate any feedback on direction and scope.

[renatonitta]

Hi @AdityaTeltia, thanks for the contribution!

I was asked by @dvassallo to refactor the entire Stats area, and we'll be rebuilding it from scratch. The current issue description doesn't clearly define the full scope, so I'll improve it and break the work down into smaller, well-defined subtasks.

Once those subtasks are in place, you're very welcome to contribute. For now, I left a comment on your PR letting you know we may need to close it.

This is my first contribution to the repo, and I'm looking forward to collaborating and continuing to improve it together.

[renatonitta]

Hey @dvassallo I did my first investigation, here is a summary:

================================================================================
📊 STATS PAGE PERFORMANCE REPORT
================================================================================
📈 DATA COLLECTION
       • total_users: 2.66ms
       • total_messages: 40.13ms
       • total_threads: 32.76ms
       • total_boosts: 34.63ms
       • total_posters: 63.72ms
  ├─ Total counts:             176.10ms
  ├─ Online users count:     16517.14ms ⚠️  SLOW - needs index
  ├─ Database size:              1.53ms
  ├─ Top rooms (10):             3.93ms
  ├─ Daily stats (7 days):       0.31ms
  ├─ All-time stats (722 days):  0.04ms
  ├─ System metrics (CPU):     659.80ms ⚠️  SLOW - shell commands
  ├─ System metrics (Memory):   14.58ms
  ├─ System metrics (Disk):     33.33ms
  ├─ System metrics (TOTAL):   707.71ms
  ├─ Top posters (today):        0.65ms
  ├─ Top posters (month):        0.32ms
  ├─ Top posters (year):         0.13ms
  ├─ Top posters (all time):     0.05ms
  ├─ User rank (today):        832.36ms
  ├─ User rank (month):         26.21ms
  ├─ User rank (year):          19.72ms
  ├─ User rank (all time):     375.18ms
  ├─ Newest members:             0.23ms
  └─ Blocked members:            0.10ms
--------------------------------------------------------------------------------
⏱️  PERFORMANCE SUMMARY
--------------------------------------------------------------------------------
Controller execution:  18942.74ms (18.94s)
View rendering:        33432.52ms (33.43s) ⚠️  SLOW
--------------------------------------------------------------------------------
TOTAL TIME:            52375.26ms (52.38s)
================================================================================

Not all of these numbers will reflect production reality, since the benchmark was run on my local machine, but they still give us a useful direction and will be helpful when refactoring each stat.

I will try to keep everything, but in case some stats are still slow, I have some questions for you below.

1. Is the online users count mandatory?
2. The CPU stat is quite expensive to fetch. Can we consider removing it?
3. Do we need this data to be fully live, or could we adopt an approach where it updates once a day? The exception would be the “Today” card, which would likely still require near-real-time data.

[renatonitta]

I finished the analysis and put together a plan with my buddy Claude.

It may look extensive at first, but it will be much easier to follow once I break it down into sub-issues, which I’ll do next.

Conclusion

• The task is too large to deliver in a single release, so we’ll ship it in phases.
• To avoid breaking the existing feature, we’ll introduce a new structure under the V2 namespace.
• Once all phases are released, we can clean up the legacy implementation.

Stats V2 - Vertical Slice Implementation Plan

Overview

Rebuild the /stats page from scratch to solve performance issues (52s+ load time with timeouts) and improve code quality. This plan delivers functionality in vertical slices that can each be tested and deployed independently.

Current Issues:

• 52+ second page load times with frequent timeouts
• 18.9s controller execution
• 33.4s view rendering
• Missing database indexes
• God object controller (314 lines)
• Mixed concerns (business logic, shell commands, performance logging)
• No caching strategy

Goals:

• Sub-500ms page load times
• Clean architecture (Query Objects, Service Layer, Caching)
• Background job processing
• Progressive loading with Turbo Frames
• Zero downtime migration

---

Slice 1: Foundation + Minimal Dashboard

Deliverable: A working Stats V2 page accessible at /stats/v2 with all-time top 10 leaderboard

---

Slice 2: Complete Leaderboards

Deliverable: Dashboard shows all four time periods (today, month, year, all-time)

---

Slice 3: Caching Infrastructure

Deliverable: All leaderboard queries are cached, background jobs keep cache warm

---

Slice 4: Current User Rankings

Deliverable: Logged-in users see their rank if not in top 10

---

Slice 5: System Metrics

Deliverable: Info card with member counts, online users, database size

---

Slice 6: Room Statistics

Deliverable: Top Rooms card showing most active rooms

---

Slice 7: Message History Chart

Deliverable: Chart showing last 90 days of message activity

---

Slice 8: Advanced Rankings Cache

Deliverable: Redis ZSET-based ranking system for O(log N) rank lookups

---

Slice 9: Full Leaderboard Pages

Deliverable: Dedicated pages showing top 100+ users per period

---

Slice 10: Cross-Version Navigation

Deliverable: Easy switching between V1 and V2, monitoring both versions

---

Slice 11: Migration & Cleanup

Deliverable: V2 becomes the default, V1 deprecated then removed

---

Deployment Strategy

Slice Dependencies

Sequential:
Slice 1 → Slice 2 → Slice 3

Parallel (after Slice 3):
Slice 4, 5, 6, 7

Sequential continuation:
Slice 4 → Slice 8 → Slice 9

Final stages:
Slice 10 → Slice 11

Deployment Pattern (per slice)

1. Develop in feature branch
2. Deploy to production
3. Test /stats/v2 route
4. Verify functionality
5. Move to next slice

Rollback

Every slice can be rolled back by:

• Reverting deployment
• V1 remains untouched at /stats
• Zero downtime

Milestones

• MVP: Slices 1-3 (working, fast stats page)
• Feature Parity: Slices 1-7 (matches V1 functionality)
• Production Ready: All 11 slices (battle-tested, monitored, migrated)

---

Expected Performance

Before (V1)

• Total time: 52+ seconds
• Controller: 18.9s
• View: 33.4s
• Frequent timeouts

After (V2)

• Total time: 200-500ms
• Controller: 50-100ms (cache hits)
• View: 150-400ms
• Charts load separately via Turbo: ~100ms
• Zero timeouts

Improvements

• 100x faster overall
• 200x faster controller execution
• 100x faster view rendering
• Progressive loading feels instant