Add per event name Service Ping metrics for AI audit events

What does this MR do and why?

Issue 607935 asks for Service Ping metrics covering:

• Number of AI audit events per event type (e.g., ai_agent_session_started, ai_llm_input_sent, ai_tool_invoked, etc.)

Event type breakdown metrics should use a generic counter pattern to avoid a metric per event name

This MR adds that breakdown:

Metric	Time frames	How
counts.count_ai_audit_events_by_event_name	28d, all	grouped count on ai_audit_events in PostgreSQL
counts.count_ai_audit_events_by_event_name_clickhouse	28d, all	count() GROUP BY event_name in ClickHouse

Both are object-valued metrics returning { event_name => count } with all allowed event names present (zero defaults) — the generic counter pattern the work item asks for, one metric per store instead of one per event name.

A failure in the batched grouped count (statement timeout after BatchCount's internal batch-size-halving retries, or any other database error) degrades the whole metric to the -1 fallback; Service Ping generation is not interrupted.

References

• https://gitlab.com/gitlab-org/gitlab/-/work_items/607935

Database review

The PostgreSQL metric runs one batched grouped count per time frame using Gitlab::Database::BatchCount over 100k-id windows. The scope's min and max id are computed once (a cheap pkey Merge Append, about 4 ms on the clone), then each window runs a single GROUP BY statement and BatchCount sums the per-window hashes. The result is merged onto the zero-filled allowed names, so stored names outside the allowlist surface too, matching the ClickHouse metric's behavior. Each statement stays bounded by the batch window no matter how large the table grows. One caveat: the all-frame statements carry no created_at predicate, so each one probes every monthly partition's pkey index; the benchmark clone had only a few populated partitions, while a years-old instance has dozens, adding a small per-partition constant per statement that grows with instance age. The 28d frame prunes to the two partitions covering the window and is unaffected.

-- all frame, one batch
SELECT COUNT("ai_audit_events"."id") AS "count_id",
       "ai_audit_events"."event_name" AS "ai_audit_events_event_name"
FROM "ai_audit_events"
WHERE "ai_audit_events"."id" >= 1 AND "ai_audit_events"."id" < 100001
GROUP BY "ai_audit_events"."event_name"

-- 28d frame, one batch
SELECT COUNT("ai_audit_events"."id") AS "count_id",
       "ai_audit_events"."event_name" AS "ai_audit_events_event_name"
FROM "ai_audit_events"
WHERE "ai_audit_events"."created_at" BETWEEN '2026-07-29 19:51:10.910147' AND '2026-08-26 19:51:10.910195'
  AND "ai_audit_events"."id" >= 1 AND "ai_audit_events"."id" < 100001
GROUP BY "ai_audit_events"."event_name"

Benchmarked on Database Lab with 1,000,000 synthetic rows spread over the last 90 days, ANALYZE run before measuring. About 10 windows per frame at this volume.

• Representative single window, all frame: ~49 ms, 2,362 buffer hits, 100k rows aggregated into 11 groups, no disk reads (plan)
• Representative single window, 28d frame: ~25 ms, 1,537 buffer hits, ~31k rows after the created_at filter (plan)

On GitLab.com these events are written to ClickHouse, so the PostgreSQL table is nearly empty there; the seeded volume stands in for a large self-managed instance using the PostgreSQL fallback store.

Approach A: per event name batched counts (superseded, kept for reference)

The PostgreSQL metric counts rows per event name using Gitlab::Database::BatchCount with the standard 100k id batch size. The time-constrained scope's min and max id are computed once and shared across all 11 counts, so we never pay for MIN/MAX per event name. Each underlying statement stays bounded by the batch window no matter how large the table grows:

-- all frame, one batch
SELECT COUNT(*) FROM "ai_audit_events"
WHERE "ai_audit_events"."event_name" = 'ai_llm_input_sent'
  AND "ai_audit_events"."id" >= 1 AND "ai_audit_events"."id" < 100001

-- 28d frame, one batch
SELECT COUNT(*) FROM "ai_audit_events"
WHERE "ai_audit_events"."created_at" BETWEEN '2026-07-27 11:23:34.747378' AND '2026-08-24 11:23:34.747436'
  AND "ai_audit_events"."event_name" = 'ai_llm_input_sent'
  AND "ai_audit_events"."id" >= 1 AND "ai_audit_events"."id" < 100001

Benchmarked on Database Lab with 1,000,000 synthetic rows spread over the last 90 days, seeded with deliberate id gaps so the id range spans roughly 1.45M (about 15 batches per event name, 165 statements per frame). ANALYZE was run before measuring.

• Representative single batch for all: ~35 ms (plan)
• Representative single batch for 28d: ~12ms (plan)
• Full run, all frame, all 11 names in one server side loop estimated 1.885 s total.
• Full run, 28d frame: estimated 805 ms.

On GitLab.com these events are written to ClickHouse, so the PostgreSQL table is nearly empty there; the seeded volume above stands in for a large self-managed instance using the PostgreSQL fallback store.Each per-event-name batched count degrades independently: a statement timeout on one name reports -1 for that key while the other names keep their real counts. Failures outside the per-name counts (scope building, the min/max id queries) fall back to -1 for the whole metric via alt_usage_data. Either way Service Ping generation is not interrupted.

How to set up and validate locally

require_relative 'spec/support/helpers/service_ping_helpers.rb'
ServicePingHelpers.get_current_usage_metric_value('counts.count_ai_audit_events_by_event_name')

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.