Add top user and project session rankings to AI governance metrics

Andrew Jungrequested to merge
611952-governance-sessions-by-user-project into master

What does this MR do and why?

Adds topUsers and topProjects to the aiGovernanceMetrics GraphQL field: AI sessions ranked by user and by project, for the Developer activity and Project exposure dashboard cards. The frontend builds those cards on mock data today (!250049 (merged)) shaped to this exact contract.

  • Both fields take a validated limit argument (default 5, max 20) and reuse the existing timeframe and agentClass arguments.
  • Both backends implement the ranking: PostgreSQL groups the same windowed scope the KPIs read, ClickHouse reuses the existing dedup subquery. Ties break on the id so the order is deterministic.
  • The rankings use the same population as the sessions KPI: every workflow definition, Duo Chat included. Namespace-attached sessions count for users but not for projects (no project to attribute to). Both facts are stated in the field descriptions.
  • The ranking queries only run when the fields are selected (GraphQL lookahead), so existing tile queries are unchanged.
  • No migrations. The queries read through the indexes added in !248831 (merged).

Targeted 607565-include-chat-in-sessions (the chat-inclusive session scope) until !248831 (merged) merged; now targets master.

On performance: the ClickHouse path serves GitLab.com. The PostgreSQL path is the self-managed fallback and reads the current window's rows through the (namespace_id, created_at) / (project_id, created_at) composites; happy to capture postgres.ai plans if wanted.

References

Screenshots or screen recordings

API-only change, no UI.

How to set up and validate locally

On a GDK with some Duo Workflow sessions, in a Rails console:

Feature.enable(:ai_governance_dashboard)
user = User.find_by(username: 'root')
query = <<~GQL
  query {
    group(fullPath: "<your-group-path>") {
      aiGovernanceMetrics(timeframe: LAST_30_DAYS) {
        sessions { count }
        topUsers { user { username } sessionCount }
        topProjects(limit: 3) { project { fullPath } sessionCount }
      }
    }
  }
GQL
puts GitlabSchema.execute(query, context: { current_user: user }).to_json

On master the query fails (unknown fields). On this branch it returns ranked lists ordered by sessionCount descending, and the two lists agree with sessions.count seeded data. Toggling ApplicationSetting.current.update(use_clickhouse_for_analytics: true/false) exercises the ClickHouse and PostgreSQL paths; both return the same payload (verified on seeded GDK data, byte-identical).

Database

No migrations or schema changes in this MR. The index migrations that appeared in earlier diff versions belong to the parent MR !248831 (merged), which carries their db-testing results and postgres.ai plans.

One new query: Ai::DuoWorkflows::Workflow.top_session_counts_by(column, limit:). It runs once per requested ranking (topUsers groups by user_id, topProjects by project_id) and only when the GraphQL query selects that field (lookahead gated). limit defaults to 5 and is validated to be at most 20. Project rankings skip namespace-attached rows (project_id IS NULL).

On GitLab.com the dashboard reads ClickHouse. The PostgreSQL path serves self-managed instances.

PostgreSQL shape (group container, topUsers, 30-day window, limit 5), assembled from the scope chain:

SELECT COUNT(*) AS count_all, "duo_workflows_workflows"."user_id"
FROM (
  (SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows"
   WHERE "duo_workflows_workflows"."project_id" IN (
     SELECT "projects"."id" FROM "projects"
     WHERE "projects"."namespace_id" IN (<self_and_descendants namespace ids>)))
  UNION ALL
  (SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows"
   WHERE "duo_workflows_workflows"."namespace_id" IN (<self_and_descendants namespace ids>))
) duo_workflows_workflows
WHERE "duo_workflows_workflows"."created_at" >= '<window start>'
  AND "duo_workflows_workflows"."created_at" < '<window end>'
  AND "duo_workflows_workflows"."user_id" IS NOT NULL
GROUP BY "duo_workflows_workflows"."user_id"
ORDER BY COUNT(*) DESC, "duo_workflows_workflows"."user_id" ASC
LIMIT 5

The UNION ALL arms are the same base scope !248831 (merged) measured on postgres.ai against a busy root. Each arm drives from its own composite index from that MR ((namespace_id, created_at), (project_id, created_at)) with the created_at range pushed into the arm. This query differs from the measured totals query only by the GROUP BY, the not-null filter, and the LIMIT. Plans for this exact shape are below.

ClickHouse query (client placeholders):

SELECT %{dimension}, count() AS session_count
FROM (%{dedup_subquery})
WHERE deleted = false
  AND created_at >= {from:DateTime64(6, 'UTC')}
  AND %{dimension} IS NOT NULL
  %{agent_class_filter}
GROUP BY %{dimension}
ORDER BY session_count DESC, %{dimension} ASC
LIMIT {top_activity_limit:UInt8}

postgres.ai plans on the busy root (gitlab-org 9970, 30-day window, partial temp indexes matching the parent MR's migrations, warm second runs, session 55075):

Query Warm time Buffers Rows into the aggregate Plan
topUsers (30d, limit 5) 272 ms 193,085 122,103 https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/55075/commands/158494
topProjects (30d, limit 5) 203 ms 93,586 119,993 https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/55075/commands/158496

Both ride the composite indexes; topProjects' project arm runs as an index-only scan. The clone still carries the single-column namespace_id index that the parent MR drops, and the planner chose it for the namespace arm with created_at as a filter, so these numbers are a slight upper bound on the post-migration shape. The namespace arm returns zero rows for topProjects (namespace-attached sessions have no project). Both rankings cost less than the 30-day totals KPI query on the same root (613 ms / 300k buffers).

Edited by Andrew Jung

Merge request reports