Bug: N+1 queries on new case contacts datatable endpoint

[cliftonmcintosh]

Impacted User Types

All users who visit /case_contacts/new_design — volunteers, supervisors, and admins.

Environment

Any environment where the new_case_contact_table Flipper flag is enabled.

Current Behavior

When the new case contacts table loads at /case_contacts/new_design, the datatable endpoint (POST /case_contacts/new_design/datatable) fires two N+1 query sets — one per row for casa_cases and one per row for followups. The queries scale linearly with the number of rows returned per page (default 10, configurable up to 100).

Rails log showing the N+1 warnings

18:09:23 web.1  | N+1 queries detected:
18:09:23 web.1  |   SELECT "casa_cases"."id", "casa_cases"."case_number", ... FROM "casa_cases" WHERE "casa_cases"."id" = $1 LIMIT $2
18:09:23 web.1  |   SELECT "casa_cases"."id", "casa_cases"."case_number", ... FROM "casa_cases" WHERE "casa_cases"."id" = $1 LIMIT $2
18:09:23 web.1  |   SELECT "casa_cases"."id", "casa_cases"."case_number", ... FROM "casa_cases" WHERE "casa_cases"."id" = $1 LIMIT $2
18:09:23 web.1  |   SELECT "casa_cases"."id", "casa_cases"."case_number", ... FROM "casa_cases" WHERE "casa_cases"."id" = $1 LIMIT $2
18:09:23 web.1  |   SELECT "casa_cases"."id", "casa_cases"."case_number", ... FROM "casa_cases" WHERE "casa_cases"."id" = $1 LIMIT $2
18:09:23 web.1  |   SELECT "casa_cases"."id", "casa_cases"."case_number", ... FROM "casa_cases" WHERE "casa_cases"."id" = $1 LIMIT $2
18:09:23 web.1  |   SELECT "casa_cases"."id", "casa_cases"."case_number", ... FROM "casa_cases" WHERE "casa_cases"."id" = $1 LIMIT $2
18:09:23 web.1  | Call stack:
18:09:23 web.1  |   app/datatables/case_contact_datatable.rb:20:in 'block in CaseContactDatatable#data'
18:09:23 web.1  |   app/datatables/case_contact_datatable.rb:14:in 'Enumerable#map'
18:09:23 web.1  |   app/datatables/case_contact_datatable.rb:14:in 'CaseContactDatatable#data'
18:09:23 web.1  |   app/datatables/application_datatable.rb:15:in 'ApplicationDatatable#as_json'
18:09:23 web.1  |   app/controllers/case_contacts/case_contacts_new_design_controller.rb:15:in 'CaseContacts::CaseContactsNewDesignController#datatable'

18:09:23 web.1  | N+1 queries detected:
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  |   SELECT 1 AS one FROM "followups" WHERE "followups"."case_contact_id" = $1 AND "followups"."status" = $2 LIMIT $3
18:09:23 web.1  | Call stack:
18:09:23 web.1  |   app/datatables/case_contact_datatable.rb:38:in 'block in CaseContactDatatable#data'
18:09:23 web.1  |   app/datatables/case_contact_datatable.rb:14:in 'Enumerable#map'
18:09:23 web.1  |   app/datatables/case_contact_datatable.rb:14:in 'CaseContactDatatable#data'
18:09:23 web.1  |   app/datatables/application_datatable.rb:15:in 'ApplicationDatatable#as_json'
18:09:23 web.1  |   app/controllers/case_contacts/case_contacts_new_design_controller.rb:15:in 'CaseContacts::CaseContactsNewDesignController#datatable'

Expected Behavior

The datatable endpoint should load all needed associations in a fixed number of queries regardless of page size.

Root Causes

N+1 #1 — casa_cases (datatable line 20)

case_contact.casa_case&.case_number triggers a query per row. The existing .left_joins(:casa_case) in raw_records joins for SQL filtering but does not eager load the association into memory.

Fix: add :casa_case to the includes chain in raw_records.

N+1 #2 — followups.requested.exists? (datatable line 38)

:followups is already in the includes, so the association records are loaded in memory. However, .requested.exists? fires a new SQL query regardless because exists? always hits the database.

Fix: replace with case_contact.followups.any?(&:requested?) — this operates on the already-loaded association without an additional query.

How to Replicate

1. Enable the new_case_contact_table Flipper flag
2. Log in as any user at the QA site
3. Visit /case_contacts/new_design
4. Observe the Rails log for N+1 queries detected warnings

How to access the QA site

Login Details:
Link to QA site

Login Emails:

• volunteer1@example.com — view site as a volunteer
• supervisor1@example.com — view site as a supervisor
• casa_admin1@example.com — view site as an admin
• all_casa_admin1@example.com — view site as an all casa admin
  • go to /all_casa_admins/sign_in

Password for all users: 12345678

Questions? Join Slack!

We highly recommend that you join us in slack #casa channel to ask questions quickly. And discord for office hours (currently Tuesday 5-7pm Pacific), stakeholder news, and upcoming new issues.