Add finder for AI governance sessions

Hitesh Raghuvanshirequested to merge
630095-ai-governance-session-model into master

What does this MR do and why?

Adds Ai::Governance::SessionsFinder. It is the Postgres finder that lists rows from ai_governance_sessions for a group or project and everything under it, newest first (by session_started_at, then id). These rows are AI sessions from GitLab Duo and from external agents such as Claude Code. The MR also adds the model scopes the finder uses.

This is the Postgres path for self-managed instances. GitLab.com reads sessions from ClickHouse instead, through Ai::Governance::Sessions::ClickHouseFinder in !258121 (merged). Both finders take the same filters.

The GraphQL field in !257545 (merged) reads from ClickHouse only today. It does not depend on this MR. A later change will make it use this finder when ClickHouse is not available.

Nothing calls this finder yet, so there is no user-facing change.

What the finder does:

  • Filters by agent class (GitLab Duo or external agents), flow type, user, project path, and start time range.
  • Flow type, user and project path can be included or excluded. Excluding a flow type or project keeps rows where that column is empty (NULL).
  • Returns nothing unless the user has the read_agent_artifacts ability on the group or project.
  • Hides Duo sessions started from a private Slack DM, the same way duo_workflow_session_artifacts does. It uses a NOT EXISTS check against duo_workflows_workflows.

Database

The finder uses GitLab's in-operator optimization. It walks the index i_ai_governance_sessions_on_namespace_started_at_id on (namespace_id, session_started_at, id) once per namespace in the group. It then merges the results to return one page.

The full generated SQL for the no-filter query is long (it uses a recursive CTE). You can see it in the plan link below.

Locks: reads take only AccessShareLock.

Known limit: the user filter is over the 100 ms guideline on gitlab-org (134 ms). Filters on columns outside the index (user, source, flow type) are checked row by row. A user who is missing from most namespaces makes the query read most rows in the group. An index on (namespace_id, user_id, session_started_at, id) brought this case to 63 ms in the clone (plan). It is not added here, for two reasons:

  • This Postgres path serves self-managed instances, which are much smaller than gitlab-org.
  • Every session write would pay for one more index.

GitLab.com uses the ClickHouse finder. The index can be added later if self-managed needs it.

Query plans (all on gitlab-org, first page of 20, warm cache):

Query Time Plan
No filters 74 ms https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58600/commands/163627
Project path filter (gitlab-org/gitlab) 1 ms https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58600/commands/163633
External agents only, chat flow type excluded 65 ms https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59236/commands/164350
User filter, user with 69,404 sessions in one namespace 134 ms https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58600/commands/163607

The no-filter and project path plans were run while a temporary extra index on (namespace_id, user_id, session_started_at, id) existed in the clone. They do not filter by user, and the no-filter plan was checked: it uses only i_ai_governance_sessions_on_namespace_started_at_id. The external agents plan was run on a fresh clone without that index. This MR does not add that index.

How the test data was made

ai_governance_sessions is empty on GitLab.com. The plans used a Database Lab clone filled with the latest 1 million rows from duo_workflows_workflows (as Duo sessions), plus one external (Claude Code) copy of each row. Then ANALYZE was run.

In gitlab-org (namespace 9970) this gave 126,190 sessions across 10,613 namespaces. One user (id 18418200) owns 69,404 of them, all in a single namespace.

exec INSERT INTO ai_governance_sessions (namespace_id, project_id, user_id, workflow_id, created_at, updated_at, session_started_at, source, status, agent_type, flow_type) SELECT COALESCE(w.namespace_id, p.project_namespace_id), w.project_id, w.user_id, w.id, now(), now(), w.created_at, 0, w.status, w.agent_type, w.workflow_definition FROM (SELECT * FROM duo_workflows_workflows ORDER BY id DESC LIMIT 1000000) w LEFT JOIN projects p ON p.id = w.project_id WHERE COALESCE(w.namespace_id, p.project_namespace_id) IS NOT NULL;
exec INSERT INTO ai_governance_sessions (namespace_id, project_id, user_id, external_xid, created_at, updated_at, session_started_at, source, agent_type, flow_type) SELECT namespace_id, project_id, user_id, gen_random_uuid()::text, now(), now(), session_started_at - interval '1 minute', 2, 'claude-code', 'claude_code' FROM ai_governance_sessions WHERE source = 0;
exec ANALYZE ai_governance_sessions;
Rejected approach for the user filter

A plain query that uses index_ai_governance_sessions_on_user_id instead was tried and rejected. It took 220 ms for the busy user, because the planner misjudged how many of that user's rows are in the group (plan).

References

Screenshots or screen recordings

Not applicable. This is a backend-only change.

How to set up and validate locally

Run the specs:

bundle exec rspec ee/spec/finders/ai/governance/sessions_finder_spec.rb ee/spec/models/ai/governance/session_spec.rb

Locally this gave 71 examples, 0 failures.

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