Performance Enhancement Results

Date: 2026-03-14 Branch: performance-enhancement Status: Complete - All 1008 tests passing

Executive Summary

Comprehensive performance optimization across the entire Lifehub application. The worst page (habit toggle) went from 10,273ms / 1,532 queries down to an estimated ~200ms / ~15 queries — a 98% reduction in database queries. Every major page in the app was optimized.

Changes Implemented

Phase 1: Critical N+1 Fixes

1. Achievement Checker Cascade (Biggest Single Impact)

• Problem: AchievementChecker.check_all ran inline on every XP award, loading all ~46 achievements and running individual queries per achievement. On habit toggle, this cascaded 3+ times = ~400+ queries.
• Solution:
  • Created Gamification::StatsCache — pre-fetches all stats in ~8 batch queries instead of 25+ individual ones
  • Pre-fetches unlocked achievement IDs in single query (eliminates ~46 EXISTS? queries)
  • Moved check_all to Gamification::AchievementCheckJob (SolidQueue background job)
• Files: app/models/gamification/stats_cache.rb (new), achievement_checker.rb, xp_awarder.rb, app/jobs/gamification/achievement_check_job.rb (new)
• Impact: Habit toggle response no longer blocked by achievement checking

2. HabitCompletion N+1 on completed_today?

• Problem: Each habit triggered a separate EXISTS? query. Called across dashboard, habits index, hub partial, gamification, and XpAwarder — 24+ queries per page.
• Solution: Added Habit.preload_completions_today(habits) class method that batch-loads all completions in 1 query. Applied in:
  • DashboardController#index
  • HabitsController#index
  • _hub_habits.html.erb partial
  • XpAwarder.check_all_daily_habits_bonus
• Files: app/models/habit.rb, dashboard_controller.rb, habits_controller.rb, _hub_habits.html.erb, xp_awarder.rb
• Impact: 8-24 queries → 1 query per page

3. Streak Recalculation Single Query

• Problem: CompletionTracker#recalculate_streak! iterated day-by-day with one EXISTS? query per day. A 30-day streak = 31 queries.
• Solution: Single query fetching all completion dates, streak calculated in Ruby.
• Files: app/models/habit/completion_tracker.rb
• Impact: O(streak_length) queries → 1 query

4. BalanceRegistry Memoization in DataAggregator

• Problem: DataAggregator queried balance_registries 4-5 times with different ORDER BY/LIMIT clauses across methods like monthly_growth, total_growth_pct, avg_monthly_growth, mom_normalized_series, patrimonial_history.
• Solution: Single memoized all_balance_registries method, all derived calculations use in-memory array.
• Files: app/models/dashboard/data_aggregator.rb
• Impact: 4-5 queries → 1 query per aggregator instance

5. DataAggregator#kpis Memoization

• Problem: FamilyAggregator accessed a.kpis[:key] repeatedly per field, recomputing the full hash each time.
• Solution: Added @kpis ||= memoization.
• Files: app/models/dashboard/data_aggregator.rb
• Impact: Eliminates N*M redundant query sets in family mode

Phase 2: Query Optimization

6. Accounts Index — Hash Lookup Instead of find_by Loop

• Problem: View called @organization.accounts.find_by(id: entry["account_id"]) inside a loop over every balance registry entry. With 7 accounts × 22 registries = ~154 queries.
• Solution: Pre-built @accounts_by_id = @accounts.index_by(&:id) in controller, O(1) hash lookup in view.
• Files: accounts_controller.rb, accounts/index.html.erb
• Impact: 154 queries → 0 extra queries

7. Gamification Weekly XP — Grouped Queries

• Problem: 8 individual SUM queries for weekly totals + 16 for category breakdowns = 24 queries.
• Solution: 2 grouped queries using strftime('%Y-%W', datetime(created_at)), results processed in Ruby.
• Files: app/controllers/organizations/gamification_controller.rb
• Impact: 24 queries → 2 queries

8. Expense Monthly Trend — Grouped Query

• Problem: 6 individual SUM(:amount) queries (one per month) in expenses index.
• Solution: Single grouped query using strftime('%Y-%m', date).
• Files: app/controllers/organizations/expenses_controller.rb
• Impact: 6 queries → 1 query

9. Analytics expense_monthly_totals — Grouped Query

• Problem: N per-month SUM queries in AnalyticsCalculator#expense_monthly_totals.
• Solution: Single grouped query, same pattern as expenses controller.
• Files: app/models/organization/analytics_calculator.rb
• Impact: 6-12 queries → 1 query

10. Analytics Balance Registries Memoization

• Problem: month_normalized_patrimony_series loaded all registries fresh each call (called 4+ times).
• Solution: Memoized via cached_balance_registries.
• Files: app/models/organization/analytics_calculator.rb
• Impact: 4+ queries → 1 query

11. Sidebar SQL SUM

• Problem: set_sidebar_context created BalanceCalculator on every authenticated request, loading ALL accounts into memory and summing in Ruby.
• Solution: Replaced with sidebar_org.accounts.sum(:balance) — single SQL SUM.
• Files: app/controllers/application_controller.rb
• Impact: Full table load eliminated on every request

12. BalanceCalculator SQL Aggregation

• Problem: BalanceCalculator#total used Ruby-level sum(&:balance) on loaded records.
• Solution: SQL-level sum(:balance) with proper scoping.
• Files: app/models/account/balance_calculator.rb
• Impact: Database-level aggregation instead of Ruby

13. set_locale Redundant Query

• Problem: set_locale loaded the organization from DB again even though already loaded by set_sidebar_context.
• Solution: Reuse @organization already set by before_actions.
• Files: app/controllers/application_controller.rb
• Impact: 1 fewer query per authenticated request

Phase 3: Infrastructure

14. Composite Database Index

• Added: (habit_id, completed, date) on habit_completions table
• Purpose: Optimizes the frequent EXISTS?(date: X, completed: true) query pattern
• Files: db/migrate/20260314151508_add_index_to_habit_completions_on_completed.rb

15. Bullet Gem Enabled

• Problem: Bullet was installed but not configured — bullet.log was empty.
• Solution: Enabled with bullet_logger: true and rails_logger: true in development.
• Files: config/environments/development.rb
• Purpose: Catches future N+1 regressions automatically

Results Summary

Before vs After

DRY Patterns Applied

• Gamification::StatsCache — single class for batch-loading all achievement stats
• Habit.preload_completions_today — reusable preloader across 4 call sites
• Memoized balance registries — shared by 8+ methods in DataAggregator and 4+ in AnalyticsCalculator
• Grouped query pattern — consistently applied across expenses, XP transactions, and analytics

New Files Created

| File | Purpose | |——|———| | app/models/gamification/stats_cache.rb | Batch stats loader for achievement checking | | app/jobs/gamification/achievement_check_job.rb | Background job for async achievement checking | | db/migrate/20260314151508_add_index_to_habit_completions_on_completed.rb | Composite index for habit completion queries |

Files Modified (29)

Controllers (10):

• app/controllers/application_controller.rb — sidebar SQL SUM + locale reuse
• app/controllers/organizations/accounts_controller.rb — hash lookup for accounts
• app/controllers/organizations/balance_registries_controller.rb — hash lookup for accounts
• app/controllers/organizations/construction_projects_controller.rb — eager-load construction_phase
• app/controllers/organizations/dashboard_controller.rb — habit preloading
• app/controllers/organizations/expenses_controller.rb — grouped monthly query
• app/controllers/organizations/finance_dashboard_controller.rb — SQL ordering for goals
• app/controllers/organizations/gamification_controller.rb — grouped XP queries
• app/controllers/organizations/habits_controller.rb — habit preloading
• app/controllers/organizations/sports_controller.rb — precomputed stats + per-sport counts
• app/controllers/users/notification_preferences_controller.rb — batch-load preferences

Models (13):

• app/models/account/balance_calculator.rb — SQL aggregation
• app/models/construction_phase.rb — memoize total_spent
• app/models/construction_project.rb — memoize total_spent + completed_phases_count
• app/models/dashboard/data_aggregator.rb — memoized registries + kpis
• app/models/gamification/achievement_checker.rb — StatsCache + batch unlocked check
• app/models/gamification/xp_awarder.rb — background job + preloading
• app/models/habit.rb — preload_completions_today class method
• app/models/habit/completion_tracker.rb — single-query streak recalculation
• app/models/habit/dashboard_aggregator.rb — preload completions
• app/models/habit/streak_calculator.rb — preload completions
• app/models/organization/analytics_calculator.rb — grouped expenses + memoized registries
• app/models/todo_board.rb — memoize JSON parsing (columns, labels)
• app/models/todo_card.rb — memoize JSON parsing (checklists, label_ids, assignee_ids)

Views (4):

• app/views/organizations/accounts/index.html.erb — hash lookup
• app/views/organizations/balance_registries/index.html.erb — hash lookup
• app/views/organizations/dashboard/_hub_habits.html.erb — preloading
• app/views/organizations/sports/index.html.erb — use precomputed stats
• app/views/users/notification_preferences/index.html.erb — batch-loaded preferences

Config (1):

• config/environments/development.rb — Bullet gem configuration

Test Results

• 1008 tests, 2212 assertions, 0 failures, 0 errors
• All existing functionality preserved
• No breaking changes