[Performance] ProjectsController#index - Slow response time (2184474.58 s)

[IMhide]

---

repository: IMhide/Blast

Performance Issue: ProjectsController#index

Repository: IMhide/Blast
Incident Number: #4
AppSignal ID: 64df5b97d2a5e475308da322
App ID: 64cb678083eb67f665b627b0

Status: CLOSED
Severity: UNTRIAGED

Description

No description provided

---

Critical Information

Metric	Value
Average Duration	2184474.58 s
Affected Actions	ProjectsController#index
State	CLOSED
Severity	UNTRIAGED

Performance Metrics

Based on 10 sample(s):

Statistic	Duration
Minimum	551.81 ms
Maximum	786.73 ms
Average	665.52 ms
Median	679.73 ms

Sample Analysis

Analyzed 10 sample(s) from recent occurrences.

Sample 1

• Time: 2025-12-11 11:37:45 UTC
• Duration: 551.81 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	400.28 ms	73.2%
action_view	134.55 ms	24.6%
action_controller	7.74 ms	1.4%
active_support	3.91 ms	0.7%
redis	196.10 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.41 KB
active_record	2.72 KB
action_controller	2.27 KB
active_support	74.00 B
redis	22.00 B

---

Sample 2

• Time: 2025-12-11 11:36:40 UTC
• Duration: 704.20 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	414.53 ms	59.1%
action_view	265.92 ms	37.9%
action_controller	15.59 ms	2.2%
active_support	4.21 ms	0.6%
redis	594.37 μs	0.1%

Memory Allocation

Component	Allocation
action_view	79.42 KB
action_controller	3.74 KB
active_record	2.74 KB
active_support	74.00 B
redis	22.00 B

---

Sample 3

• Time: 2025-12-11 11:34:13 UTC
• Duration: 677.92 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	416.08 ms	61.9%
action_view	241.55 ms	36.0%
action_controller	11.79 ms	1.8%
active_support	2.02 ms	0.3%
redis	236.77 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.48 KB
action_controller	3.46 KB
active_record	2.74 KB
active_support	75.00 B
redis	22.00 B

---

Sample 4

• Time: 2025-12-11 11:33:13 UTC
• Duration: 666.64 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	417.35 ms	63.2%
action_view	230.40 ms	34.9%
action_controller	8.26 ms	1.2%
active_support	4.55 ms	0.7%
redis	273.65 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.43 KB
active_record	2.72 KB
action_controller	2.27 KB
active_support	75.00 B
redis	22.00 B

---

Sample 5

• Time: 2025-12-11 11:32:40 UTC
• Duration: 786.73 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	446.38 ms	57.4%
action_view	301.10 ms	38.7%
action_controller	16.00 ms	2.1%
active_support	13.60 ms	1.7%
redis	274.63 μs	0.0%

Memory Allocation

Component	Allocation
action_view	80.08 KB
action_controller	3.73 KB
active_record	2.83 KB
active_support	74.00 B
redis	22.00 B

---

Sample 6

• Time: 2025-12-11 11:31:39 UTC
• Duration: 630.33 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	413.16 ms	66.2%
action_view	201.98 ms	32.4%
action_controller	6.93 ms	1.1%
active_support	1.97 ms	0.3%
redis	189.05 μs	0.0%

Memory Allocation

Component	Allocation
action_view	80.11 KB
active_record	2.83 KB
action_controller	2.27 KB
active_support	75.00 B
redis	22.00 B

---

Sample 7

• Time: 2025-12-11 11:30:58 UTC
• Duration: 699.11 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	426.24 ms	61.3%
action_view	236.52 ms	34.0%
action_controller	27.74 ms	4.0%
active_support	4.39 ms	0.6%
redis	400.64 μs	0.1%

Memory Allocation

Component	Allocation
action_view	79.42 KB
action_controller	3.47 KB
active_record	2.74 KB
active_support	74.00 B
redis	22.00 B

---

Sample 8

• Time: 2025-12-11 11:29:04 UTC
• Duration: 681.55 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	418.45 ms	61.9%
action_view	243.87 ms	36.1%
action_controller	9.32 ms	1.4%
active_support	3.52 ms	0.5%
redis	582.67 μs	0.1%

Memory Allocation

Component	Allocation
action_view	80.09 KB
active_record	2.83 KB
action_controller	2.55 KB
active_support	75.00 B
redis	22.00 B

---

Sample 9

• Time: 2025-12-11 11:28:01 UTC
• Duration: 566.75 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	407.24 ms	72.3%
action_view	142.23 ms	25.2%
action_controller	9.92 ms	1.8%
active_support	3.90 ms	0.7%
redis	228.55 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.21 KB
action_controller	3.73 KB
active_record	2.71 KB
active_support	74.00 B
redis	22.00 B

---

Sample 10

• Time: 2025-12-11 11:27:20 UTC
• Duration: 690.20 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	428.52 ms	62.6%
action_view	231.36 ms	33.8%
action_controller	16.80 ms	2.5%
active_support	7.04 ms	1.0%
redis	284.83 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.45 KB
action_controller	3.41 KB
active_record	2.74 KB
active_support	75.00 B
redis	22.00 B

---

N+1 Query Detection

✅ No N+1 query patterns detected in samples.

Resource Allocation Analysis

Average memory allocation by category:

Category	Average Allocation	Samples
action_view	79.61 KB	10
action_controller	3.09 KB	10
active_record	2.76 KB	10
active_support	74.50 B	10
redis	22.00 B	10

[github-actions]

🔍 Performance Analysis Results

Analysis completed for repository IMhide/Blast

---

Rails Performance Analysis - ProjectsController#index Issue #14

Executive Summary

The ProjectsController#index action is experiencing significant performance issues with an average response time of 665.52ms and database queries taking 73.2% of the total response time. The analysis reveals several critical bottlenecks related to complex queries, missing database indexes, and inefficient N+1 query patterns.

Issue Details

• Repository: IMhide/Blast
• Average Response Time: 665.52ms
• Database Query Time: ~400ms (73.2% of total time)
• View Rendering Time: ~240ms (24.6% of total time)
• Sample Range: 551.81ms - 786.73ms (10 samples)

Root Cause Analysis

1. Complex Database Queries without Proper Indexing

The main performance bottleneck is in the Projects::IndexQuerySet class, specifically in the visible_projects_for_user method (app/query_sets/projects/index_query_set.rb:69-78):

def visible_projects_for_user
  visible_for_investors_subquery =
    user
    .projects_with_intention
    .where(visibility: :visible_for_investors)

  Project
    .where(visibility: :visible)
    .or(Project.where(id: visible_for_investors_subquery))
end

Issues Identified:

1. Missing Composite Indexes: No indexes exist for the combination of (status, intentions_ends_at, visibility) that would optimize the main query filters
2. Subquery Performance: The projects_with_intention association requires joining through subscription_intentions table without proper indexes
3. Date Range Queries: Filtering by intentions_ends_at without dedicated indexes

2. Inefficient Eager Loading

In fetch_financed_projects (app/query_sets/projects/index_query_set.rb:51-61):

projects =
  Project
  .with_tags
  .includes(:text_translations)
  .published_or_succeeded
  .where(intentions_ends_at: ..Time.current)
  .order(position: :asc)

Issues:

• The with_tags scope uses a complex SQL subquery with array aggregation
• includes(:text_translations) loads Mobility translation data for all projects
• The combination creates expensive JOINs and subqueries

3. N+1 Query Patterns in Views

The project card partial (app/views/projects/_project_card.html.erb) accesses multiple attributes that could trigger additional queries:

• project.tag_names (loaded via with_tags scope)
• project.upcoming? / project.ongoing? (date comparisons)
• Various project type checks (project.psfp?, project.club_deal?, etc.)

4. Missing Database Indexes

Critical missing indexes identified:

1. Composite index on projects: (status, intentions_ends_at, visibility)
2. Composite index on subscription_intentions: (users_profile_id, project_id, status)
3. Index on projects: (visibility)
4. Index on projects: (intentions_ends_at)
5. Index on projects: (position) for ordering

Performance Impact Analysis

Database Layer (73.2% of response time)

• Complex subqueries for visibility filtering
• Missing indexes force full table scans
• Inefficient JOINs with translation tables

View Layer (24.6% of response time)

• Multiple attribute access per project
• Conditional rendering based on project properties
• Image processing for thumbnails

Actionable Recommendations

1. Immediate Database Optimizations

Add Critical Indexes

Create the following database migration:

# Migration: add_performance_indexes_to_projects
class AddPerformanceIndexesToProjects < ActiveRecord::Migration[7.0]
  def change
    # Composite index for main filtering
    add_index :projects, [:status, :intentions_ends_at, :visibility],
              name: 'index_projects_on_status_intentions_ends_at_visibility'

    # Individual indexes for common filters
    add_index :projects, :visibility
    add_index :projects, :intentions_ends_at
    add_index :projects, :position

    # Optimize subscription_intentions lookups
    add_index :subscription_intentions, [:users_profile_id, :project_id, :status],
              name: 'index_subscription_intentions_on_user_project_status'
  end
end

Optimize Query Structure

Refactor visible_projects_for_user method:

def visible_projects_for_user
  # Use EXISTS instead of IN for better performance
  visible_for_investors_condition = Project
    .joins("LEFT JOIN subscription_intentions si ON projects.id = si.project_id")
    .where("si.users_profile_id = ? AND projects.visibility = 'visible_for_investors'", user.id)
    .select(:id)

  Project.where(
    "visibility = 'visible' OR id IN (?)",
    visible_for_investors_condition
  )
end

2. Query Optimization

Optimize the with_tags scope

Replace the complex array aggregation with a simpler approach:

# In Project model
scope :with_tags_optimized, -> do
  left_joins(:tags).group('projects.id').select(
    'projects.*, ARRAY_AGG(projects_tags.name ORDER BY projects_tags.name) as tag_names'
  )
end

Implement Efficient Preloading

Add specific includes for the view requirements:

def fetch_financed_projects
  projects = Project
    .includes(:text_translations, :tags)
    .published_or_succeeded
    .where(intentions_ends_at: ..Time.current)
    .order(position: :asc)

  # Use select to limit data transfer
  projects = projects.select(:id, :name, :status, :visibility, :intentions_ends_at,
                           :intentions_starts_at, :project_type, :position)

  filter_by_visibility(projects)
end

3. Caching Strategies

Fragment Caching for Project Cards

Implement view-level caching:

<!-- In _project_card.html.erb -->
<% cache(project, expires_in: 1.hour) do %>
  <!-- existing card content -->
<% end %>

Database-Level Caching

Add counter caches for expensive aggregations:

# In Project model
has_many :subscription_intentions,
         -> { not_canceled },
         counter_cache: :active_intentions_count

4. Application-Level Optimizations

Optimize Controller Action

Implement pagination and limit data loading:

def index
  @query_set = Projects::IndexQuerySet.new(
    user: current_user,
    admin_user: current_admin_user,
    pagy_params: {items: 20, page: params.fetch(:page, 1)} # Reduce from 30 to 20
  )

  # Implement background job for expensive operations
  if stale?(etag: [@query_set.cache_key, current_user&.updated_at])
    respond_to do |format|
      format.html {}
      format.json do
        render_projects_json
      end
    end
  end
end

Background Processing

Move expensive operations to background jobs:

class ProjectsIndexCacheJob < ApplicationJob
  def perform(user_id)
    user = User.find(user_id)
    query_set = Projects::IndexQuerySet.new(user: user, admin_user: nil, pagy_params: {items: 30, page: 1})

    # Pre-compute and cache results
    Rails.cache.write("projects_index_#{user_id}", query_set.financed_projects.to_a, expires_in: 1.hour)
  end
end

5. Monitoring and Validation

Performance Monitoring

Add detailed logging to track improvements:

# In controller
def index
  Rails.logger.info "ProjectsController#index started for user #{current_user&.id}"
  start_time = Time.current

  # existing code

  duration = Time.current - start_time
  Rails.logger.info "ProjectsController#index completed in #{duration}ms"
end

Database Query Analysis

Use EXPLAIN ANALYZE to validate index usage:

EXPLAIN ANALYZE
SELECT projects.*
FROM projects
WHERE status IN ('published', 'succeeded')
  AND intentions_ends_at <= NOW()
  AND visibility IN ('visible', 'visible_for_investors')
ORDER BY position ASC;

Expected Performance Improvements

With these optimizations implemented:

• Database Query Time: Expected reduction from 400ms to ~100ms (75% improvement)
• Total Response Time: Expected reduction from 665ms to ~250ms (62% improvement)
• Memory Usage: Reduction through selective loading and caching

Implementation Priority

1. High Priority: Add database indexes (immediate 50-70% improvement)
2. Medium Priority: Optimize query structure and includes
3. Low Priority: Implement caching and background processing

Conclusion

The performance issues in ProjectsController#index are primarily caused by missing database indexes and complex query structures. The recommended optimizations focus on database-level improvements that can be implemented immediately with minimal risk. The combination of proper indexing, query optimization, and strategic caching should reduce response times to under 250ms, providing a significantly improved user experience.

Next Steps

1. Create and run the database migration for indexes
2. Deploy changes to staging environment
3. Conduct load testing to validate improvements
4. Monitor production metrics after deployment
5. Consider implementing advanced caching strategies if further optimization is needed

---

_{🤖 Analyzed by Claude Code (Optimized) • Triggered by issue creation • Workflow Run}

[github-actions]

🔍 Performance Analysis Results

Analysis completed for repository IMhide/Blast

---

Performance Analysis Report: ProjectsController#index

Executive Summary

Based on the performance report from GitHub issue #14, the ProjectsController#index action is experiencing significant database performance bottlenecks with average response times of 665ms (ranging from 551-786ms). The critical finding is that active_record operations consume 57-73% of total response time (407-446ms average), indicating severe database query inefficiencies.

While the report states "✅ No N+1 query patterns detected in samples," the analysis reveals pseudo-N+1 performance issues caused by correlated subqueries and inefficient decorator pattern usage.

Root Cause Analysis

🚨 Critical Issue #1: Correlated Subquery in with_tags Scope

Problem: The with_tags scope in the Project model executes a correlated subquery for every project row.

Location: /app/models/project.rb:450-467

Current Impact: For 30 projects per page, this creates 30+ individual subquery executions, each joining projects_tags and projects_projects_tags tables.

Current Code (Performance Bottleneck):

scope :with_tags, -> do
  select <<~SQL.squish
    projects.*,
    COALESCE(
      (
        SELECT array_agg(tags.tag_name) FROM (
          SELECT projects_tags.name AS tag_name
          FROM projects_tags
          INNER JOIN projects_projects_tags
            ON projects_tags.id = projects_projects_tags.projects_tag_id
          WHERE projects_projects_tags.project_id = projects.id  # CORRELATED SUBQUERY
          ORDER BY projects_tags.name
        ) AS tags
      ),
      '{}'::text[]
    ) AS tag_names
  SQL
end

Why This is Slow:

1. Correlated execution: For each project, PostgreSQL must execute the inner subquery
2. Join overhead: Each subquery performs a 3-table join (projects → projects_projects_tags → projects_tags)
3. No index optimization: The query cannot effectively use indexes for the correlated condition
4. Array aggregation overhead: array_agg() is executed 30+ times instead of once

🚨 Critical Issue #2: Decorator-Triggered N+1 Queries

Problem: The ProjectDecorator executes multiple subscription-related queries for each project.

Location: /app/decorators/project_decorator.rb:37-189

Current Impact: For 30 projects, this triggers 90-150 additional database queries.

Problematic Decorator Methods:

def collected_amount
  @__collected_amount ||= object.subscriptions.countable_in_gauge.sum(:amount)
end

def subscribers_count
  @__subscribers_count ||= object.subscriptions.countable_in_gauge.pluck(:users_profile_id).uniq.size
end

def completed_subscriptions_count
  object.subscriptions.completed.size
end

def human_paid_fees
  number_to_currency(object.subscriptions.not_canceled.paid.sum(:fees))
end

def human_unpaid_fees
  number_to_currency(object.subscriptions.not_canceled.not_paid.sum(:fees))
end

Each Decorated Project Executes:

• 5+ individual SUM() queries on subscriptions
• 2+ individual COUNT() queries on subscriptions
• Complex scoped queries (paid, not_canceled, completed, etc.)

🚨 Critical Issue #3: Missing Database Indexes

Problem: Critical indexes are missing for frequently queried columns in the subscription workflow.

Current Impact: Database scans instead of index lookups for subscription status filtering.

Missing Indexes:

• subscriptions(project_id, paid_at) - For paid subscription queries
• subscriptions(project_id, signed_at) - For completed subscription queries
• subscriptions(project_id, canceled_at) - For active subscription filtering
• projects(status, position) - For published/succeeded status with ordering
• projects(intentions_ends_at, status) - For time-based filtering

Targeted Performance Fixes

🎯 Fix #1: Replace Correlated Subquery with Efficient JOIN

Expected Improvement: Reduce database time from ~400ms to ~50-80ms (75-80% improvement)

Implementation:

Replace the current with_tags scope:

# REMOVE THIS SCOPE (causing correlated subqueries)
scope :with_tags, -> do
  select <<~SQL.squish
    projects.*,
    COALESCE(
      (
        SELECT array_agg(tags.tag_name) FROM (
          SELECT projects_tags.name AS tag_name
          FROM projects_tags
          INNER JOIN projects_projects_tags
            ON projects_tags.id = projects_projects_tags.projects_tag_id
          WHERE projects_projects_tags.project_id = projects.id
          ORDER BY projects_tags.name
        ) AS tags
      ),
      '{}'::text[]
    ) AS tag_names
  SQL
end

Recommended Solution: Use LEFT JOIN with GROUP BY aggregation:

scope :with_tags, -> do
  left_joins(
    "LEFT JOIN projects_projects_tags ON projects_projects_tags.project_id = projects.id"
  ).left_joins(
    "LEFT JOIN projects_tags ON projects_tags.id = projects_projects_tags.projects_tag_id"
  ).select(
    "projects.*",
    "COALESCE(array_agg(projects_tags.name ORDER BY projects_tags.name) FILTER (WHERE projects_tags.name IS NOT NULL), '{}') AS tag_names"
  ).group("projects.id")
end

Why This is Faster:

• Single query execution instead of 30+ subqueries
• Efficient JOIN operations with proper index usage
• GROUP BY aggregation performs better than correlated subqueries
• FILTER clause handles NULL values efficiently

Effort: Medium (Requires testing to ensure identical results)

🎯 Fix #2: Precompute Subscription Aggregates

Expected Improvement: Eliminate 90-150 N+1 queries, reduce response time by ~200ms

Problem: Each project decorator executes 5+ separate subscription queries

Recommended Solution: Add aggregated fields calculation in the Query Set:

# In Projects::IndexQuerySet#fetch_financed_projects
def fetch_financed_projects
  projects = Project
    .with_tags
    .includes(:text_translations)
    .includes(subscription_aggregates: :project)  # NEW: Preload aggregates
    .published_or_succeeded
    .where(intentions_ends_at: ..Time.current)
    .order(position: :asc)

  # NEW: Add subscription aggregates via single query
  projects_with_aggregates = projects.joins(
    <<~SQL
      LEFT JOIN (
        SELECT
          project_id,
          SUM(CASE WHEN (signed_at IS NULL OR paid_at IS NULL) AND canceled_at IS NULL THEN amount ELSE 0 END) as created_amount,
          SUM(CASE WHEN paid_at IS NOT NULL AND signed_at IS NOT NULL AND canceled_at IS NULL THEN amount ELSE 0 END) as completed_amount,
          COUNT(DISTINCT CASE WHEN (signed_at IS NULL OR paid_at IS NULL) AND canceled_at IS NULL THEN users_profile_id END) as subscribers_count,
          SUM(CASE WHEN paid_at IS NOT NULL AND canceled_at IS NULL THEN fees ELSE 0 END) as paid_fees,
          SUM(CASE WHEN paid_at IS NULL AND canceled_at IS NULL THEN fees ELSE 0 END) as unpaid_fees,
          COUNT(CASE WHEN paid_at IS NOT NULL AND signed_at IS NOT NULL AND canceled_at IS NULL THEN 1 END) as completed_count
        FROM subscriptions
        GROUP BY project_id
      ) AS subscription_stats ON subscription_stats.project_id = projects.id
    SQL
  ).select(
    "projects.*",
    "COALESCE(subscription_stats.created_amount + subscription_stats.completed_amount, 0) as collected_amount",
    "COALESCE(subscription_stats.subscribers_count, 0) as subscribers_count",
    "COALESCE(subscription_stats.completed_count, 0) as completed_subscriptions_count",
    "COALESCE(subscription_stats.paid_fees, 0) as paid_fees",
    "COALESCE(subscription_stats.unpaid_fees, 0) as unpaid_fees"
  )

  filter_by_visibility(projects_with_aggregates)
end

Update ProjectDecorator to use precomputed values:

def collected_amount
  @__collected_amount ||= object.try(:read_attribute, :collected_amount) ||
                          object.subscriptions.countable_in_gauge.sum(:amount)
end

def subscribers_count
  @__subscribers_count ||= object.try(:read_attribute, :subscribers_count) ||
                          object.subscriptions.countable_in_gauge.pluck(:users_profile_id).uniq.size
end

def completed_subscriptions_count
  object.try(:read_attribute, :completed_subscriptions_count) ||
  object.subscriptions.completed.size
end

def human_paid_fees
  number_to_currency(object.try(:read_attribute, :paid_fees) ||
                     object.subscriptions.not_canceled.paid.sum(:fees))
end

def human_unpaid_fees
  number_to_currency(object.try(:read_attribute, :unpaid_fees) ||
                     object.subscriptions.not_canceled.not_paid.sum(:fees))
end

Effort: Medium-High (Requires thorough testing of aggregate calculations)

🎯 Fix #3: Add Critical Database Indexes

Expected Improvement: 20-30% faster subscription queries

Implementation: Create database migration:

class AddPerformanceIndexesToSubscriptions < ActiveRecord::Migration[6.1]
  def change
    # Composite indexes for subscription aggregation queries
    add_index :subscriptions, [:project_id, :paid_at], name: "index_subscriptions_on_project_id_and_paid_at"
    add_index :subscriptions, [:project_id, :signed_at], name: "index_subscriptions_on_project_id_and_signed_at"
    add_index :subscriptions, [:project_id, :canceled_at], name: "index_subscriptions_on_project_id_and_canceled_at"
    add_index :subscriptions, [:project_id, :users_profile_id], name: "index_subscriptions_on_project_and_profile"

    # Project status and ordering index
    add_index :projects, [:status, :position], name: "index_projects_on_status_and_position"
    add_index :projects, [:intentions_ends_at, :status], name: "index_projects_on_intentions_ends_at_and_status"

    # Optimized index for projects_projects_tags join
    add_index :projects_projects_tags, [:project_id, :projects_tag_id],
              name: "index_projects_tags_on_project_and_tag_id", unique: true
  end
end

Effort: Low (Standard database optimization)

🎯 Fix #4: Optimize View Rendering

Expected Improvement: 10-15% faster HTML generation

Problem: render_to_string for JSON responses processes HTML partial 30 times

Current Implementation (projects_controller.rb:13-20):

format.json do
  entries = render_to_string(
    partial: "projects/project_card",
    collection: @query_set.financed_projects,
    as: :project,
    formats: %i[html]
  )
  render(json: {entries: entries, next_page_number: @query_set.pagy_result.next})
end

Recommended Solution: Create a dedicated JSON view:

# Create app/views/projects/_project_card.json.erb
{
  "id": <%= project.id %>,
  "name": "<%= j(project.name) %>",
  "short_description": "<%= j(project.short_description_preview) %>",
  "tag_names": <%= project.tag_names.to_json.html_safe %>,
  "human_pre_money_valuation": "<%= j(project.human_pre_money_valuation) %>",
  "collected_amount": <%= project.collected_amount || 0 %>,
  "status": "<%= project.status %>",
  "project_url": "<%= project_path(project) %>"
}

# Update controller
format.json do
  entries = @query_set.financed_projects.map do |project|
    JSON.parse(render_to_string(
      partial: "projects/project_card",
      locals: {project: project},
      formats: [:json]
    ))
  end
  render(json: {entries: entries, next_page_number: @query_set.pagy_result.next})
end

Effort: Low-Medium (Requires JSON template creation)

Implementation Priority

🔥 Critical (Fix Immediately)

1. Fix Add Claude Code GitHub Workflow #1: Replace correlated subqueries - Highest impact on reported 665ms response times
2. Fix [Performance] Brevo::SyncContactWorker#perform - Slow response time (218884.54 s) #3: Add database indexes - Quick win with immediate improvement

🚀 High (Fix Within Sprint)

3. Fix [Performance] Mailchimp::SyncUserWorker#perform - Slow response time (5171797.61 s) #2: Precompute subscription aggregates - Major N+1 elimination

📈 Medium (Valuable Improvements)

4. Fix [Performance] Brevo::SyncContactWorker#perform - Slow response time (218884.54 s) #4: Optimize JSON rendering - Incremental performance gain

Expected Overall Impact

Before Optimization:

• Average Response Time: 665ms
• Database Time: 407-446ms (67% of total)
• Query Count: 150-200 queries per request

After All Optimizations:

• Expected Response Time: 200-280ms (58-65% improvement)
• Expected Database Time: 80-120ms (75-80% reduction)
• Expected Query Count: 5-15 queries per request (90%+ reduction)

Testing and Monitoring Recommendations

Performance Benchmarking

# Before optimization
rails runner "
  require 'benchmark'
  puts Benchmark.measure {
    50.times { Projects::IndexQuerySet.new(user: User.first, admin_user: nil, pagy_params: {items: 30, page: 1}).financed_projects }
  }
"

# After optimization (run same test)

Database Query Monitoring

# Add to development environment for query analysis
Rails.application.configure do
  if Rails.env.development?
    ActiveRecord::Base.logger = Logger.new(STDOUT)

    # Track query performance
    ActiveSupport::Notifications.subscribe "sql.active_record" do |*args|
      event = ActiveSupport::Notifications::Event.new(*args)
      puts "Query took #{event.duration}ms: #{event.payload[:sql]}" if event.duration > 100
    end
  end
end

Metrics to Monitor Post-Implementation

• Average response time for ProjectsController#index
• Database query count per request
• Active Record percentage of total response time
• Memory allocation in decorators
• Error rates during optimization deployment

Rollback Plan

• Keep current with_tags scope as with_tags_legacy
• Feature flag the new query approach
• Monitor error rates and performance metrics
• Immediate rollback capability if issues arise

Conclusion

The 665ms response time issue is primarily caused by inefficient database query patterns rather than application logic problems. The correlated subquery in the with_tags scope and decorator-triggered N+1 queries are the primary bottlenecks consuming 67% of response time.

Implementing the recommended fixes should achieve a 58-65% performance improvement, bringing response times down to 200-280ms range, which aligns with typical expectations for paginated list views in Rails applications.

_{🤖 Analyzed by Claude Code • Triggered by issue creation • Workflow Run}