Performance Enhancement Design Spec

Date: 2026-03-14 Branch: performance-enhancement Status: Approved

Problem Statement

The application has severe performance issues across multiple pages. The worst offender (POST /habits/toggle) takes 10,273ms and executes 1,532 queries. Several pages exceed 1-3 seconds with hundreds of redundant queries. Root causes: N+1 queries, achievement checker cascade, missing eager loading, redundant database hits, and no caching.

Current Performance Baseline

Architecture Overview

Phase 1: Critical N+1 Fixes (Highest Impact)

1.1 Achievement Checker Optimization

Problem: AchievementChecker.check_all is called on every XP award via XpAwarder.award (line 84). Each call loads ALL achievements (~46), runs UserAchievement Exists? per achievement (~46 queries), then runs calculate_progress with 25+ case branches, each executing its own query. On habit toggle, this cascades 3+ times (habit XP + achievement unlock XP + second achievement unlock XP) = ~400+ queries per toggle.

Fix:

• Pre-fetch all user achievements in a single query and check in-memory
• Pre-compute all stats needed by calculate_progress into a single stats hash
• Only check achievements relevant to the action context (category-scoped)
• Move check_all from inline to a background job (SolidQueue)
• Add a Gamification::StatsCache model class to batch-load all counts

1.2 HabitCompletion N+1 on completed_today?

Problem: Habit#completed_today? (line 133) runs habit_completions.exists?(date: Date.current, completed: true) per habit. With 8 habits across dashboard, gamification, and hub partials, this generates 24+ queries.

Fix:

• Add Habit::CompletionPreloader that batch-loads today's completions:

  def self.preload_today(habits)
    completed_ids = HabitCompletion.where(habit_id: habits.map(&:id), date: Date.current, completed: true).pluck(:habit_id).to_set
    habits.each { |h| h.instance_variable_set(:@completed_today, completed_ids.include?(h.id)) }
  end
  

• Modify completed_today? to check @completed_today if preloaded, else fallback to query

1.3 Streak Recalculation Optimization

Problem: CompletionTracker#recalculate_streak! (line 35) iterates day-by-day, running exists? per day. A 30-day streak = 31 queries.

Fix:

• Replace with single query fetching all recent completion dates, calculate streak in Ruby:

  dates = habit.habit_completions.where(completed: true).where("date <= ?", Date.current).order(date: :desc).pluck(:date)
  

1.4 BalanceRegistry Scattered Queries

Problem: DataAggregator queries balance_registries 4-5 times with different ORDER BY/LIMIT clauses. Methods: monthly_growth, total_growth_pct, avg_monthly_growth, mom_normalized_series, patrimonial_history.

Fix:

• Load all balance registries once, memoize, derive all calculations from the in-memory array:

  def balance_registries
    @balance_registries ||= organization.balance_registries.order(:date).to_a
  end
  

Phase 2: Query Optimization

2.1 Missing Database Indexes

Add composite index:

• habit_completions: (habit_id, completed, date) – optimizes the frequent exists?(date: X, completed: true) query

2.2 XpTransaction LIKE Queries -> action_type Column

Problem: XpAwarder and AchievementChecker use description LIKE '%Daily login%' and description LIKE '%Reviewed analytics%' for dedup checks. LIKE with leading wildcard cannot use indexes.

Fix:

• Add action_type string column to xp_transactions with values like daily_login, habit_completion, streak_milestone, analytics_review, etc.
• Add index on (membership_id, action_type, created_at)
• Replace LIKE queries with exact match on action_type

2.3 Gamification Weekly XP Breakdown

Problem: GamificationController#show runs 8 separate Sum queries for weekly totals + 16 for category breakdowns = 24 queries.

Fix:

• Single grouped query:

  XpTransaction.where(membership_id: m.id, created_at: 8.weeks.ago..)
    .group("strftime('%W', created_at)", :category)
    .sum(:amount)
  

• Process result hash in Ruby to build weekly/category breakdowns

2.4 Expense Monthly Totals N+1

Problem: AnalyticsCalculator#expense_monthly_totals and ExpensesController#index run separate sum(:amount) per month (6+ queries).

Fix:

• Single grouped query: Expense.where(...).group("strftime('%Y-%m', date)").sum(:amount)

2.5 Redundant Exists? + Load in Dashboard

Problem: Dashboard views check Exists? then immediately Load the same records (6+ extra queries).

Fix:

• Load records once, check .any? on loaded collection instead of separate existence query

2.6 set_sidebar_context Optimization

Problem: ApplicationController#set_sidebar_context creates BalanceCalculator on EVERY authenticated request, which loads ALL accounts into memory and sums in Ruby.

Fix:

• Replace BalanceCalculator.new(...).total with organization.accounts.sum(:balance) (single SQL SUM)
• Or cache the total balance, invalidated on account updates

2.7 FamilyAggregator kpis Memoization

Problem: FamilyAggregator#kpis calls a.kpis repeatedly per field, recomputing the full hash each time.

Fix:

• Memoize DataAggregator#kpis with @kpis ||= { ... }

2.8 Account::BalanceCalculator SQL Aggregation

Problem: BalanceCalculator#total uses scoped_accounts.sum(&:balance) (Ruby sum on loaded records) instead of SQL aggregation.

Fix:

• Use scoped_accounts_relation.sum(:balance) for SQL-level aggregation when only the total is needed

Phase 3: Caching & Infrastructure

3.1 Enable and Configure Bullet Gem

Problem: bullet.log is empty – Bullet is not detecting N+1s in development.

Fix:

• Verify Bullet is enabled in config/environments/development.rb
• Configure with alert: true, bullet_logger: true, rails_logger: true
• Add Bullet safelist for known acceptable patterns

3.2 Achievement Checking as Background Job

Problem: Achievement checking runs inline on every XP award, blocking the response.

Fix:

• Create Gamification::AchievementCheckJob that runs via SolidQueue
• XpAwarder.award enqueues the job instead of calling check_all inline
• The job batches all pending checks for the membership

3.3 Dashboard Fragment Caching

Fix:

• Cache dashboard partials (habits summary, goals summary, accounts summary)
• Invalidate on relevant model updates using touch: true

3.4 Counter Caches

Add counter caches where count queries are frequent:

• MarketList -> market_list_items_count
• Consider habits_count on Membership if displayed frequently

Expected Impact

DRY Patterns

• Gamification::StatsCache – single class for batch-loading all gamification stats, reused by AchievementChecker and GamificationController
• Habit::CompletionPreloader – reused across dashboard, habits index, gamification, hub partial
• Memoized balance_registries in DataAggregator – single load for all derived calculations
• Grouped query pattern for XP and expense monthly breakdowns

Testing Strategy

• Add performance assertions in integration tests (query count limits)
• Verify all existing tests still pass after each phase
• Benchmark before/after with development log comparison