Add AI governance session and agent metrics services

Andrew Jungrequested to merge
603776-ai-governance-metrics-services into master

What does this MR do and why?

Adds the aggregation service layer for the AI Governance Dashboard's AI Sessions and AI Agents KPI tiles (#603776, beta aggregation items 1 and 2). Given a group or project and a timeframe (:last_24_hours / :last_7_days / :last_30_days), Ai::Governance::MetricsService returns session and distinct-agent counts for the current window, the preceding window (for the delta badge), and a zero-filled bucketed trend series (for the sparkline).

  • ClickHouse backend reads siphon_duo_workflows_workflows (ReplacingMergeTree argMax dedup, startsWith(traversal_path, …) hierarchy scoping) when the instance has ClickHouse analytics enabled.
  • Postgres backend produces a byte-identical payload via new Ai::DuoWorkflows::Workflow scopes (self_and_descendants hierarchy scoping) for instances without ClickHouse — the default for Self-Managed.
  • Owner-private chat sessions (workflow_definition = 'chat') are excluded from every aggregate, matching the visibility rules of the existing governance surfaces.
  • No caller yet — this MR is inert. The GraphQL exposure (aiGovernanceMetrics on Group and Project, behind the ai_governance_dashboard feature flag introduced with the dashboard tab in !243109 (merged)) follows in a stacked MR.

Database review

Two aggregate queries against duo_workflows_workflows (down from six): one single-pass totals query (COUNT(*) FILTER / COUNT(DISTINCT ...) FILTER for the current and previous windows) and one grouped trend query. Each resolves the namespace hierarchy once. The group-container form (UNION ALL of the project and namespace arms) is the heavier path; the project-container form replaces the union with a single project_id = ?.

Adds index_duo_workflows_workflows_on_namespace_id_created_at (namespace_id, created_at DESC) WHERE workflow_definition <> 'chat', the namespace-arm mirror of the existing project-arm index. With it the namespace arm's created_at becomes an index condition instead of a post-scan filter, and buffers touched by that arm drop from ~26.3k to ~4.6k (~82%) on group 9970.

Migration executed on GitLab.com data via db:gitlabcom-database-testing (results in the comment below).

Query plans (postgres.ai, with the index present)

base = Ai::DuoWorkflows::Workflow.in_namespace_hierarchy(group).without_workflow_definition(%w[chat])

base.created_between(previous_from, to).count_current_and_previous(from) (totals) https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/53867/commands/156194

base.created_between(from, to).counts_by_created_at_bucket (trend) https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/53867/commands/156195

ClickHouse queries (dedup subquery + totals + trend)

Source table: siphon_duo_workflows_workflows (ReplacingMergeTree, version col _siphon_replicated_at, ORDER BY (traversal_path, created_at, id)). All variable values are ClickHouse {name:Type} placeholders (server-side substitution); the %{dedup_subquery} / %{unit} tokens are Ruby format splices resolved before send.

Placeholder values (for last_7_days, frozen at 2026-07-03 15:30 UTC):

  • traversal_path = <org>/<group>/<subgroup>/<project_ns>/ via traversal_path(with_organization: true)
  • excluded_definitions = ['chat']
  • from = 2026-06-26 00:00:00.000000
  • previous_from = 2026-06-19 00:00:00.000000
  • to = 2026-07-03 15:30:00.000000

Deduplication subquery (shared, spliced into both queries below)

SELECT
  id,
  created_at,
  argMax(workflow_definition, _siphon_replicated_at) AS workflow_definition,
  argMax(_siphon_deleted, _siphon_replicated_at) AS deleted
FROM siphon_duo_workflows_workflows
WHERE startsWith(traversal_path, {traversal_path:String})
  AND created_at >= {previous_from:DateTime64(6, 'UTC')}
  AND created_at < {to:DateTime64(6, 'UTC')}
GROUP BY traversal_path, created_at, id

1. Totals (current + previous windows, sessions + agents, single scan)

SELECT
  countIf(created_at >= {from:DateTime64(6, 'UTC')}) AS sessions_count,
  countIf(created_at < {from:DateTime64(6, 'UTC')}) AS sessions_previous_count,
  uniqExactIf(workflow_definition, created_at >= {from:DateTime64(6, 'UTC')}) AS agents_count,
  uniqExactIf(workflow_definition, created_at < {from:DateTime64(6, 'UTC')}) AS agents_previous_count
FROM (<dedup subquery above>)
WHERE deleted = false
  AND workflow_definition NOT IN {excluded_definitions:Array(String)}

2. Trend buckets (current window; %{unit} = 'day' or 'hour')

SELECT
  toStartOfInterval(created_at, INTERVAL 1 day) AS bucket_start,
  count() AS sessions_count,
  uniqExact(workflow_definition) AS agents_count
FROM (<dedup subquery above>)
WHERE deleted = false
  AND created_at >= {from:DateTime64(6, 'UTC')}
  AND workflow_definition NOT IN {excluded_definitions:Array(String)}
GROUP BY bucket_start
ORDER BY bucket_start ASC

Notes for the plan review

  • Chat exclusion moved to the outer query (post-dedup) rather than inside the dedup subquery as originally sketched. Inside the subquery it collided with the argMax(...) AS workflow_definition alias (ClickHouse ILLEGAL_AGGREGATION: the raw workflow_definition in WHERE resolved to the aggregate). The excluded set is unchanged (['chat']); workflow_definition is immutable so pre/post-dedup filtering is equivalent. This also mirrors Ai::DuoWorkflows::SessionArtifacts::ClickHouseFinder, which filters workflow_definition in the outer query for the same reason.
  • Dedup GROUP BY uses the full primary key (traversal_path, created_at, id) rather than id alone, so created_at stays a plain grouping column. Grouping by id + min(created_at) also hit ILLEGAL_AGGREGATION under predicate pushdown of the created_at range. Immutable PK columns make this dedup identical to id-only.
  • with_organization: true on the traversal path matches the CH column format used by the sibling ClickHouseFinder; the plain traversal_path (no org) would not match siphon-populated rows in production.
  • Placeholder type DateTime64(6, 'UTC') matches the column type and forces UTC interpretation of the string values (mirrors ContributionAnalytics::ClickHouseDataCollector).

How to validate locally

Master:

Ai::Governance::MetricsService
NameError: uninitialized constant Ai::Governance

This branch:

Ai::Governance::MetricsService.new(group, current_user: user, timeframe: :last_7_days).execute.payload
{:sessions=>
  {:count=>1,
   :previous_count=>2,
   :trend=>
    [{:bucket_start=>2026-06-29 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-06-30 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-01 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-02 00:00:00 UTC, :count=>1},
     {:bucket_start=>2026-07-03 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-04 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-05 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-06 00:00:00 UTC, :count=>0}]},
 :agents=>
  {:count=>1,
   :previous_count=>1,
   :trend=>
    [{:bucket_start=>2026-06-29 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-06-30 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-01 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-02 00:00:00 UTC, :count=>1},
     {:bucket_start=>2026-07-03 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-04 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-05 00:00:00 UTC, :count=>0},
     {:bucket_start=>2026-07-06 00:00:00 UTC, :count=>0}]}}

Cross-check against raw rows in the same hierarchy:

scope = Ai::DuoWorkflows::Workflow.in_namespace_hierarchy(group)
scope.where(created_at: 14.days.ago..).pluck(:id, :workflow_definition, :environment, :created_at)
[[7654, "resolve_sast_vulnerability/v1", "web", 2026-06-25 16:49:46 UTC],
 [7656, "chat", "web", 2026-06-25 17:42:50 UTC],
 [7655, "chat", "web", 2026-06-25 16:49:53 UTC],
 [7644, "chat", "web", 2026-06-23 19:36:39 UTC],
 [7645, "resolve_sast_vulnerability/v1", "web", 2026-06-23 19:39:00 UTC],
 [7659, "resolve_sast_vulnerability/v1", "web", 2026-07-02 17:21:07 UTC]]
no_chat = scope.where.not(workflow_definition: 'chat')
no_chat.where(created_at: Time.utc(2026, 6, 29)...Time.current).count
no_chat.where(created_at: Time.utc(2026, 6, 22)...Time.utc(2026, 6, 29)).count
1
2

Matches the payload: 1 non-chat session in the current window (2026-07-02, lands in the 07-02 trend bucket), 2 in the previous window, the 3 chat sessions excluded, and both windows contain a single distinct definition so agents is 1/1.

Toggling ApplicationSetting.current.update!(use_clickhouse_for_analytics: true/false) switches the backend; the payload shape and semantics are identical (pinned by specs for both backends).

References

  • Planning issue: #603776 (AI Governance Dashboard aggregation layer)
  • FE consumers: !243109 (merged) (dashboard tab scaffold), !243779 (merged) (sparkline KPI tiles)
Edited by Andrew Jung

Merge request reports