Freegle / Iznik / 23808

72%

push

circleci

perf(stats): hoist all remaining per-group queries in generate() into buildDailyContext

The nightly stats:generate-daily ran ~17 per-group aggregations × ~506 groups
(~6,600 queries per date), each degraded by the non-indexed
messages_groups.rippled_in=0 predicate, making a multi-date backfill infeasible.

Following the pattern already established in this file (avgWeight/searchCounts/
postMethod/messageTypes/activeUsers), hoist EVERY remaining per-group query into
buildDailyContext() as a single per-date query that aggregates GROUP BY groupid,
then read each value from $context with a safe default. generate() now issues NO
per-group DB queries — only context lookups + writeCount/writeBreakdown.

Hoisted: Outcomes, ApprovedMessageCount, ApprovedMemberCount, SpamMessageCount,
SpamMemberCount, SupportQueries, Happy/Fine/Unhappy feedback (one grouped query),
OurPostingBreakdown, Replies, Weight (reusing the regenerateWeightForRange grouped
subquery), and Activity (now ApprovedMessageCount + Replies from context). Each
grouped query preserves the original WHERE clauses / DISTINCT semantics exactly
(rippled_in=0, collection, outcome IN(...), happiness, type filters). Per-day
DATE(col)=$date predicates replaced with the sargable half-open range
col >= $date AND col < $next (equivalent for datetime columns). No new index.

generate() still computes its own context when called without one, so unit tests
calling generate($id,$date) directly keep working.

Validated read-only against prod (db2) for sample groups on 2026-06-26: all 11
hoisted queries return identical per-group results to the originals; a controlled
single-date run completed in 57s (vs infeasible before).

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_014aXkxBPEiPSJBaRMjz7opk

10743 of 12325 branches covered (87.16%)

Branch coverage included in aggregate %.

98623 of 138807 relevant lines covered (71.05%)

42.07 hits per line