Fall back to PostgreSQL for AI governance sessions without ClickHouse

What does this MR do and why?

The aiGovernanceSessions GraphQL field (on Group and Project) only read from ClickHouse. Instances without ClickHouse, which includes many self-managed ones, got an empty list.

A PostgreSQL finder, Ai::Governance::SessionsFinder, was merged in !257556 (merged), but nothing called it. This MR uses it when ClickHouse is not enabled for analytics, along with session auditEvents and auditEventsCount, and fixes the gaps that showed up. The ClickHouse path and the ai_governance_sessions_api flag check are unchanged.

References

Closes https://gitlab.com/gitlab-org/gitlab/-/work_items/632264

Screenshots or screen recordings

Not applicable, backend only.

How to set up and validate locally

  1. Disable ClickHouse for analytics on your GDK (or use an instance without it). In a Rails console, enable the flag with Feature.enable(:ai_governance_sessions_api).

  2. Create a session with Ai::Governance::Session.sync_from_workflow!(Ai::DuoWorkflows::Workflow.last), or use an existing one.

  3. Run this query in /-/graphql-explorer as a user with read_agent_artifacts on the group:

    query {
      group(fullPath: "gitlab-org") {
        aiGovernanceSessions(first: 10) {
          count
          nodes { id source sessionStartedAt auditEvents { nodes { id eventName } } }
        }
      }
    }
  4. Confirm sessions are returned from PostgreSQL with the right source.

Database review

Migration: a post-deploy migration adds the partial partitioned index idx_ai_audit_events_on_ai_governance_session_id_created_at_id on ai_audit_events (ai_governance_session_id, created_at DESC, id DESC) WHERE ai_governance_session_id IS NOT NULL. db:gitlabcom-database-testing passes in 23 s (+112 KiB): !259922 (comment 3999446659)

Local migration up and down

Migration up:

main: == 20261006072427 AddAiGovernanceSessionIndexToAiAuditEvents: migrating =======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- index_name_exists?(:ai_audit_events, "idx_ai_audit_events_on_ai_governance_session_id_created_at_id")
main:    -> 0.0434s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- index_exists?("gitlab_partitions_dynamic.ai_audit_events_000000", [:ai_governance_session_id, :created_at, :id], {order: {created_at: :desc, id: :desc}, name: "index_fec68dd42d", where: "ai_governance_session_id IS NOT NULL", algorithm: :concurrently})
main:    -> 0.0027s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index("gitlab_partitions_dynamic.ai_audit_events_000000", [:ai_governance_session_id, :created_at, :id], {order: {created_at: :desc, id: :desc}, name: "index_fec68dd42d", where: "ai_governance_session_id IS NOT NULL", algorithm: :concurrently})
main:    -> 0.0094s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
   (same index_exists? and concurrent add_index for partitions 202605 to 202704)
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- add_index(:ai_audit_events, [:ai_governance_session_id, :created_at, :id], {order: {created_at: :desc, id: :desc}, name: "idx_ai_audit_events_on_ai_governance_session_id_created_at_id", where: "ai_governance_session_id IS NOT NULL"})
main:    -> 0.0056s
main: == 20261006072427 AddAiGovernanceSessionIndexToAiAuditEvents: migrated (0.1968s)

Migration down:

main: == 20261006072427 AddAiGovernanceSessionIndexToAiAuditEvents: reverting =======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- index_name_exists?(:ai_audit_events, "idx_ai_audit_events_on_ai_governance_session_id_created_at_id")
main:    -> 0.0385s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_index(:ai_audit_events, {name: "idx_ai_audit_events_on_ai_governance_session_id_created_at_id"})
main:    -> 0.0021s
main: == 20261006072427 AddAiGovernanceSessionIndexToAiAuditEvents: reverted (0.0761s)

ai_governance_sessions queries

Plans on gitlab-org (about 10,800 namespaces in the hierarchy), gitlab-production-main, with a warm cache.

Query Time Buffers Plan
Sessions list, page 1 101 ms 53,576 hits 172322
Lookup by id 17.9 ms 18 hits, 7 reads 172318
Total count 171 ms 75,826 hits 172324
count(limit: 1000) 14 ms 815 hits 172326
agentClass: EXTERNAL list, page 1 46 ms 20,666 hits 172328
  • Lookup by id no longer uses the in-operator query, which read about 42,000 buffers for one row (before).
  • Total count uses a plain namespace_id IN (<hierarchy>) count. The in-operator count did not finish in 29 minutes. The UI does not request count.
  • agentClass: EXTERNAL now also requires external_xid IS NOT NULL, so it uses the existing partial index on external_xid instead of reading every GitLab Duo session (727 ms warm before).
  • Cold cache: on a fresh clone the list took 10.4 s, the EXTERNAL list 10.8 s and the total count 7.3 s. Most of that is the namespace lookup, about one page read per namespace. Ai::DuoWorkflows::SessionArtifacts::PostgresqlFinder on master runs the same lookup. This path only runs without ClickHouse, which means self-managed instances, usually with much smaller hierarchies.

ai_audit_events queries

ai_audit_events is empty on GitLab.com, so these plans use 3 million synthetic events on a clone, skewed so the busiest session has about 49,000 events.

Query Time Buffers Plan
Audit events list, busiest session 0.6 ms 39 167728
auditEventsCount, page of the 20 busiest sessions 57 ms 40,082 167730

The list stops at LIMIT 21 on the new index. The count uses an Index Only Scan, and its cost grows with the number of events on the page.

SQL

Sessions list, page 1:

SELECT * FROM (WITH RECURSIVE "array_cte" AS MATERIALIZED (SELECT "namespaces"."id" FROM "namespaces" WHERE (traversal_ids @> ('{9970}'))), "recursive_keyset_cte" AS ((SELECT NULL::ai_governance_sessions AS records, array_cte_id_array, ai_governance_sessions_session_started_at_array, ai_governance_sessions_id_array, 0::bigint AS count FROM (SELECT ARRAY_AGG("array_cte"."id") AS array_cte_id_array, ARRAY_AGG("ai_governance_sessions"."session_started_at") AS ai_governance_sessions_session_started_at_array, ARRAY_AGG("ai_governance_sessions"."id") AS ai_governance_sessions_id_array FROM (SELECT "array_cte"."id" FROM array_cte) array_cte LEFT JOIN LATERAL (SELECT "ai_governance_sessions"."session_started_at" AS session_started_at, "ai_governance_sessions"."id" AS id FROM "ai_governance_sessions" WHERE NOT (EXISTS (SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."id" = "ai_governance_sessions"."workflow_id" AND ((duo_workflows_workflows.messaging_callback_context ->> 'adapter') IN ('slack')))) AND "ai_governance_sessions"."namespace_id" = "array_cte"."id" ORDER BY "ai_governance_sessions"."session_started_at" DESC, "ai_governance_sessions"."id" DESC LIMIT 1) ai_governance_sessions ON TRUE WHERE "ai_governance_sessions"."session_started_at" IS NOT NULL AND "ai_governance_sessions"."id" IS NOT NULL) array_scope_lateral_query LIMIT 1)
UNION ALL
(SELECT (SELECT ai_governance_sessions FROM "ai_governance_sessions" WHERE "ai_governance_sessions"."id" = recursive_keyset_cte.ai_governance_sessions_id_array[position] LIMIT 1), array_cte_id_array, recursive_keyset_cte.ai_governance_sessions_session_started_at_array[:position_query.position-1]||next_cursor_values.session_started_at||recursive_keyset_cte.ai_governance_sessions_session_started_at_array[position_query.position+1:], recursive_keyset_cte.ai_governance_sessions_id_array[:position_query.position-1]||next_cursor_values.id||recursive_keyset_cte.ai_governance_sessions_id_array[position_query.position+1:], recursive_keyset_cte.count + 1 FROM recursive_keyset_cte,
LATERAL (SELECT session_started_at, id, position FROM UNNEST(ai_governance_sessions_session_started_at_array, ai_governance_sessions_id_array) WITH ORDINALITY AS u(session_started_at, id, position) WHERE session_started_at IS NOT NULL AND id IS NOT NULL ORDER BY 1 DESC, 2 DESC LIMIT 1) AS position_query,
LATERAL (SELECT "record"."session_started_at", "record"."id" FROM (VALUES (NULL, NULL)) AS nulls LEFT JOIN (SELECT "ai_governance_sessions"."session_started_at" AS session_started_at, "ai_governance_sessions"."id" AS id FROM "ai_governance_sessions" WHERE NOT (EXISTS (SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."id" = "ai_governance_sessions"."workflow_id" AND ((duo_workflows_workflows.messaging_callback_context ->> 'adapter') IN ('slack')))) AND "ai_governance_sessions"."namespace_id" = recursive_keyset_cte.array_cte_id_array[position] AND (("ai_governance_sessions"."session_started_at", "ai_governance_sessions"."id") < (recursive_keyset_cte.ai_governance_sessions_session_started_at_array[position], recursive_keyset_cte.ai_governance_sessions_id_array[position])) ORDER BY "ai_governance_sessions"."session_started_at" DESC, "ai_governance_sessions"."id" DESC LIMIT 1) record ON TRUE LIMIT 1) AS next_cursor_values
)) SELECT (records).* FROM "recursive_keyset_cte" AS "ai_governance_sessions" WHERE (count <> 0)) ai_governance_sessions LIMIT 21 OFFSET 0

External session audit events query (session 123, a page of 20 plus one; the created_at bound is the session start minus one day):

SELECT "ai_audit_events".* FROM "ai_audit_events" WHERE "ai_audit_events"."ai_governance_session_id" = 123 AND "ai_audit_events"."created_at" >= '2026-10-05 10:00:00' ORDER BY "ai_audit_events"."created_at" DESC, "ai_audit_events"."id" DESC LIMIT 21

Count queries for auditEventsCount (one per page and kind; 20 sessions per page shown):

SELECT COUNT(*) AS count_all, "ai_audit_events"."ai_governance_session_id" AS ai_audit_events_ai_governance_session_id FROM "ai_audit_events" WHERE "ai_audit_events"."ai_governance_session_id" IN (1000, 1001, ..., 1019) AND "ai_audit_events"."created_at" >= '2026-10-01 00:00:00' GROUP BY "ai_audit_events"."ai_governance_session_id"

SELECT COUNT(*) AS count_all, "ai_audit_events"."workflow_id" AS ai_audit_events_workflow_id FROM "ai_audit_events" WHERE "ai_audit_events"."workflow_id" IN (1000, 1001, ..., 1019) AND "ai_audit_events"."created_at" >= '2026-10-01 00:00:00' GROUP BY "ai_audit_events"."workflow_id"

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

Loading
Loading