Dashboard aggregation fetches all rows for the month in PHP instead of using SQL GROUP BY

Problem/Motivation

QuotaManager::getDashboardData() fetches every row for the month and aggregates per-user totals in PHP — response time grows linearly with row count. The loop also builds $by_user[$uid]['rows'] (all raw rows per user) which the dashboard template never reads, wasting memory proportional to table size.

Steps to reproduce

1. Seed 10,000+ rows for the current month.
2. Run drush cr, then load /admin/reports/ai-metering with query logging enabled.
3. Observe: one query fetches all columns for all rows in the month; PHP iterates every row.

Proposed resolution

Query 1 — numeric aggregates via GROUP BY:

SELECT
  uid,
  COUNT(*)                                                   AS calls,
  SUM(input_tokens)                                          AS total_input,
  SUM(output_tokens)                                         AS total_output,
  SUM(cached_tokens)                                         AS total_cached,
  SUM(estimated_cost_usd)                                    AS total_cost,
  SUM(CASE WHEN provider_type = 'local' THEN 1 ELSE 0 END)  AS local_calls,
  SUM(CASE WHEN provider_type != 'local' THEN 1 ELSE 0 END) AS cloud_calls
FROM {ai_metering_usage}
WHERE timestamp BETWEEN :start AND :end
GROUP BY uid

Query 2 — dominant provider per user (current logic: provider with the highest single-call cost; MIN() for deterministic tie-breaking):

SELECT u.uid, MIN(u.provider_id) AS provider_id
FROM {ai_metering_usage} u
INNER JOIN (
  SELECT uid, MAX(estimated_cost_usd) AS max_cost
  FROM {ai_metering_usage}
  WHERE timestamp BETWEEN :start AND :end
  GROUP BY uid
) m ON u.uid = m.uid AND u.estimated_cost_usd = m.max_cost
WHERE u.timestamp BETWEEN :start AND :end
GROUP BY u.uid

Scan efficiency requires the standalone (timestamp) index (see related issue). For higher-volume sites, a monthly rollup table — same pattern as ai_metering_quota — would eliminate aggregation at read time entirely.

Remaining tasks

• Rewrite QuotaManager::getDashboardData() with the two-query approach
• Remove the unused $by_user[$uid]['rows'] accumulation
• Revisit cache invalidation — a short TTL (5 min) may be preferable to tag-based invalidation on every write for busy sites

UI / API / Data model changes

No UI or schema changes for the short-term fix. A rollup table would require a schema addition and a backfill update hook.

AI assistance

Analysis and proposed resolution drafted with AI assistance and reviewed by the module maintainer.