Read external agent metrics from ai_governance_sessions in ClickHouse

Hitesh Raghuvanshirequested to merge
630712-governance-metrics-from-sessions into master

What does this MR do and why?

The AI Governance dashboard reads aiGovernanceMetrics (sessions and agents KPIs with trends, top users, top projects, connected agents). Today the ClickHouse metrics only read Duo workflows (siphon_duo_workflows_workflows). External agents such as Claude Code are moving to ai_governance_sessions, so they would show zero.

Behind the ai_governance_sessions_api feature flag (off by default), and when ClickHouse is enabled for analytics:

  • agentClass: EXTERNAL reads external sessions from siphon_ai_governance_sessions (sources claude_code_compliance_api and claude_code_glab). Session start time is the time dimension. flow_type is used to exclude chat.
  • INTERNAL_DAP is unchanged (Duo workflows with no agent type).
  • ALL combines Duo workflows and external sessions in one SQL UNION ALL. Session rows with source gitlab_duo are always skipped. Every Duo workflow is also synced to the sessions table, so this avoids double counting.
  • Session rows are deduplicated like the existing session distribution service (latest version per row, deleted rows dropped). They are scoped to the group or project hierarchy.
  • Connected agents activity (session count and last session per agent type) also reads external sessions from ClickHouse, in a new Ai::Governance::ClickHouseConnectedAgentActivityService.

With the flag off, or ClickHouse disabled, behaviour is unchanged. The PostgreSQL path is a follow-up. It needs agent_identity_id on the Postgres table from !257557 (merged).

The flag YAML is also added in !257545 (merged). This MR has an identical copy so it can merge on its own.

Known limitation: with the flag on, external rows still in the old workflows table are not counted. There are none on GitLab.com today.

No GraphQL schema, frontend, or migration changes.

References

Screenshots or screen recordings

Not applicable. Backend only.

How to set up and validate locally

  1. Make sure ClickHouse is enabled for analytics in GDK. In a Rails console run Feature.enable(:ai_governance_sessions_api).

  2. Create an external session in the console. Wait a few seconds for Siphon (it copies PostgreSQL rows to ClickHouse).

    project = Project.first
Ai::Governance::Session.create!(namespace: project.project_namespace, project: project, user: User.first, source: :claude_code_glab, external_xid: SecureRandom.uuid, agent_type: 'claude-code', session_started_at: Time.current)

  3. In GraphiQL at /-/graphql-explorer run:

    query {
  group(fullPath: "gitlab-org") {
    aiGovernanceMetrics(agentClass: EXTERNAL, timeframe: LAST_30_DAYS) {
      sessions { count }
      agents { count }
    }
  }
}

    The external session is counted. Run it again with agentClass: ALL. Duo sessions are counted once.

  4. Disable the flag and run the query again. EXTERNAL falls back to the previous behaviour.

Query plan

This section shows the query plan for the new ClickHouse query in Ai::Governance::ClickHouseConnectedAgentActivityService, which reads siphon_ai_governance_sessions. I ran it on a local GDK ClickHouse (version 26.8) with EXPLAIN indexes = 1, for the group with the most sessions (traversal path 1/1000000/100/). The primary key (traversal_path, session_started_at, id) limits the read to that group's path range through binary search: 1 of 2 parts and 1 of 2 granules were read, taking 4.8 ms, reading 493 rows and about 29 KB. Local data is tiny (495 rows total), so this shows that the index is used, not what the query costs at GitLab.com scale. The inner query has no lower time bound on purpose; see the reasoning.

Raw SQL 
SELECT
  agent_type,
  countIf(session_started_at >= toDateTime64('2026-09-01 00:00:00.000000', 6, 'UTC')
    AND session_started_at < toDateTime64('2026-09-30 12:00:00.000000', 6, 'UTC')) AS session_count,
  max(session_started_at) AS last_session_at
FROM (
  SELECT
    id,
    session_started_at,
    argMax(source, _siphon_replicated_at) AS source,
    argMax(agent_type, _siphon_replicated_at) AS agent_type,
    argMax(agent_identity_id, _siphon_replicated_at) AS agent_identity_id,
    argMax(_siphon_deleted, _siphon_replicated_at) AS deleted
  FROM siphon_ai_governance_sessions
  WHERE startsWith(traversal_path, '1/1000000/100/')
  GROUP BY traversal_path, session_started_at, id
)
WHERE deleted = false
  AND source != 0
  AND agent_identity_id IS NOT NULL
  AND agent_type IN ['claude-code', 'opencode']
GROUP BY agent_type
EXPLAIN indexes = 1 
Output: argMax(agent_type, _siphon_replicated_at), countIf(session_started_at >= '2026-09-01 00:00:00.000000' AND session_started_at < '2026-09-30 12:00:00.000000'), max(session_started_at)

Aggregating
│  Keys: argMax(agent_type, _siphon_replicated_at)
│  Aggregates: countIf(session_started_at >= '2026-09-01 00:00:00.000000' AND session_started_at < '2026-09-30 12:00:00.000000'), max(session_started_at)
│  Skip merging: 0
└──Filter ((WHERE + (Change column names to column identifiers + (Project names + Projection))))
   │  Filter column: argMax(_siphon_deleted, _siphon_replicated_at) = 0 AND argMax(source, _siphon_replicated_at) != 0 AND argMax(agent_identity_id, _siphon_replicated_at) IS NOT NULL AND argMax(agent_type, _siphon_replicated_at) IN ('claude-code', 'opencode')
   └──Aggregating
      │  Keys: traversal_path, session_started_at, id
      │  Aggregates: argMax(source, _siphon_replicated_at), argMax(agent_type, _siphon_replicated_at), argMax(agent_identity_id, _siphon_replicated_at), argMax(_siphon_deleted, _siphon_replicated_at)
      │  Skip merging: 0
      └──ReadFromMergeTree (gitlab_clickhouse_development.siphon_ai_governance_sessions)
            Read type: Default
            Parts: 1 | Granules: 1
            Output: traversal_path, session_started_at, source, _siphon_replicated_at, argMax(agent_type, _siphon_replicated_at), agent_identity_id, _siphon_deleted, id
            Prewhere filter
            Prewhere filter column:  startsWith(traversal_path, '1/1000000/100/')
            Indexes:
              PrimaryKey
                Keys:
                  traversal_path
                Condition: (traversal_path in ['1/1000000/100/', '1/1000000/1000'))
                Parts: 1/2
                Granules: 1/2
                Search Algorithm: binary search
              Ranges: 1

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.

Edited by Hitesh Raghuvanshi

Merge request reports