Add cumulative trend to AI governance KPIs

Andrew Jungrequested to merge
616814-governance-cumulative-trend into master

What does this MR do and why?

Adds a cumulativeTrend field to both KPIs on aiGovernanceMetrics (Option A from https://gitlab.com/gitlab-org/gitlab/-/work_items/616814): each point is the running total as of the end of that bucket, seeded with the 30 days before the selected timeframe. This is what the dashboard sparklines need to show a climbing line (the level and its growth) instead of per-day new counts; the frontend cannot compute it alone because the API does not return the pre-window baseline and daily distinct agent counts cannot be prefix-summed (!250277 (merged) shipped the sparkline axis work and deferred exactly this).

sessions { trend { bucketStart count } cumulativeTrend { bucketStart count } }
agents   { cumulativeTrend { bucketStart count } }

How it works

  • Sessions: one baseline COUNT(*) of rows in [lookback_from, from), then a Ruby prefix sum over the in-window trend buckets the service already computes.
  • Agents: no approximation needed. Each agent instance is bucketed by its first session in [lookback_from, to) (MIN(created_at) grouped by the existing instance key, pre-window instances under a NULL bucket), and a running sum over those buckets is the exact cumulative distinct count: distinct-as-of-X equals instances whose first session is <= X. One aggregation per request on both backends.
  • Lookahead-gated like the rankings: the two history queries only run when cumulativeTrend is selected. Nothing selects it yet; existing dashboard loads are unchanged until the frontend switches to it.
  • Bounded to 30 days: both seeds read from lookback_from (window start minus MetricsTimeframe::CUMULATIVE_LOOKBACK), so the widest scan is 60 days. Sessions or agents first seen earlier than that aren't counted in cumulativeTrend.
  • Buckets align 1:1 with the existing trend buckets (hourly for 24h, daily for 7d/30d).

Backend only. The dashboard tile switch from trend to cumulativeTrend is a one-line frontend change that ships separately; trend stays in the schema.

Screenshots or screen recordings

Before After
cumulative-trend-flightjs-BEFORE cumulative-trend-flightjs

Both from the same demo group with 10 pre-week agents and sessions. Before is master, per-day net-new activity. After is the running total from this MR, charted through a local frontend patch that is not part of this MR.

Database

No migrations. Two new query shapes on both backends, run only when cumulativeTrend is selected: a sessions baseline COUNT(*) and an agent first-seen aggregate that computes MIN(created_at) grouped by the instance key, bucketed in an outer aggregate. Both are bounded to lookback_from, 30 days before the window start, so the widest scan is 60 days. The first, unbounded pass measured 95 to 155 seconds on postgres.ai, which is why the bound exists.

Plans, on postgres.ai (gitlab-org 9970, gitlab-org/gitlab 278964), cold clone cache:

Q1 sessions baseline | container: group (gitlab-org 9970) | agent_class: all

SELECT COUNT(*) 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 (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))))
UNION ALL
(SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))))) duo_workflows_workflows WHERE "duo_workflows_workflows"."created_at" >= '2026-08-02 00:00:00' AND "duo_workflows_workflows"."created_at" < '2026-09-01 00:00:00';

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56698/commands/161051 (11.6 s, cold clone cache)

Q2 agent first-seen | container: group (gitlab-org 9970) | agent_class: all

SELECT CASE WHEN first_seen >= '2026-09-01 00:00:00' THEN DATE_TRUNC('day', first_seen) END, COUNT(*) FROM (SELECT MIN(created_at) AS first_seen 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 (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))))
UNION ALL
(SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))))) duo_workflows_workflows WHERE "duo_workflows_workflows"."created_at" >= '2026-08-02 00:00:00' AND "duo_workflows_workflows"."created_at" < '2026-09-08 15:41:03.589439' AND (split_part(workflow_definition, '/', 1) NOT IN ('chat', 'agentic_chat')) GROUP BY (user_id, project_id, namespace_id, agent_type, agent_identity_id, CASE WHEN agent_type IS NULL THEN CASE environment WHEN 1 THEN 4 WHEN 2 THEN 5 ELSE environment END END)) agent_first_seen GROUP BY CASE WHEN first_seen >= '2026-09-01 00:00:00' THEN DATE_TRUNC('day', first_seen) END;

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56698/commands/161053 (265 ms)

Q1 sessions baseline | container: group (gitlab-org 9970) | agent_class: external

SELECT COUNT(*) 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 (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))))
UNION ALL
(SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))))) duo_workflows_workflows WHERE "duo_workflows_workflows"."agent_type" IS NOT NULL AND "duo_workflows_workflows"."created_at" >= '2026-08-02 00:00:00' AND "duo_workflows_workflows"."created_at" < '2026-09-01 00:00:00';

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56698/commands/161054 (151 ms)

Q2 agent first-seen | container: group (gitlab-org 9970) | agent_class: external

SELECT CASE WHEN first_seen >= '2026-09-01 00:00:00' THEN DATE_TRUNC('day', first_seen) END, COUNT(*) FROM (SELECT MIN(created_at) AS first_seen 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 (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))))
UNION ALL
(SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))))) duo_workflows_workflows WHERE "duo_workflows_workflows"."agent_type" IS NOT NULL AND "duo_workflows_workflows"."created_at" >= '2026-08-02 00:00:00' AND "duo_workflows_workflows"."created_at" < '2026-09-08 15:41:03.589439' AND (split_part(workflow_definition, '/', 1) NOT IN ('chat', 'agentic_chat')) GROUP BY (user_id, project_id, namespace_id, agent_type, agent_identity_id, CASE WHEN agent_type IS NULL THEN CASE environment WHEN 1 THEN 4 WHEN 2 THEN 5 ELSE environment END END)) agent_first_seen GROUP BY CASE WHEN first_seen >= '2026-09-01 00:00:00' THEN DATE_TRUNC('day', first_seen) END;

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56698/commands/161056 (180 ms)

Q1 sessions baseline | container: project (gitlab-org/gitlab 278964) | agent_class: all

SELECT COUNT(*) FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."project_id" = 278964 AND "duo_workflows_workflows"."created_at" >= '2026-08-02 00:00:00' AND "duo_workflows_workflows"."created_at" < '2026-09-01 00:00:00';

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56698/commands/161057 (3.5 ms)

Q2 agent first-seen | container: project (gitlab-org/gitlab 278964) | agent_class: all

SELECT CASE WHEN first_seen >= '2026-09-01 00:00:00' THEN DATE_TRUNC('day', first_seen) END, COUNT(*) FROM (SELECT MIN(created_at) AS first_seen FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."project_id" = 278964 AND "duo_workflows_workflows"."created_at" >= '2026-08-02 00:00:00' AND "duo_workflows_workflows"."created_at" < '2026-09-08 15:41:03.589439' AND (split_part(workflow_definition, '/', 1) NOT IN ('chat', 'agentic_chat')) GROUP BY (user_id, project_id, namespace_id, agent_type, agent_identity_id, CASE WHEN agent_type IS NULL THEN CASE environment WHEN 1 THEN 4 WHEN 2 THEN 5 ELSE environment END END)) agent_first_seen GROUP BY CASE WHEN first_seen >= '2026-09-01 00:00:00' THEN DATE_TRUNC('day', first_seen) END;

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56698/commands/161058 (40 ms)

Q1 sessions baseline | container: group (gitlab-org 9970) | agent_class: all | LAST_24_HOURS (hourly)

SELECT COUNT(*) 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 (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))))
UNION ALL
(SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))))) duo_workflows_workflows WHERE "duo_workflows_workflows"."created_at" >= '2026-08-08 15:00:00' AND "duo_workflows_workflows"."created_at" < '2026-09-07 15:00:00';

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56698/commands/161059 (90 ms)

Q2 agent first-seen | container: group (gitlab-org 9970) | agent_class: all | LAST_24_HOURS (hourly)

SELECT CASE WHEN first_seen >= '2026-09-07 15:00:00' THEN DATE_TRUNC('hour', first_seen) END, COUNT(*) FROM (SELECT MIN(created_at) AS first_seen 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 (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))))
UNION ALL
(SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))))) duo_workflows_workflows WHERE "duo_workflows_workflows"."created_at" >= '2026-08-08 15:00:00' AND "duo_workflows_workflows"."created_at" < '2026-09-08 15:41:03.589508' AND (split_part(workflow_definition, '/', 1) NOT IN ('chat', 'agentic_chat')) GROUP BY (user_id, project_id, namespace_id, agent_type, agent_identity_id, CASE WHEN agent_type IS NULL THEN CASE environment WHEN 1 THEN 4 WHEN 2 THEN 5 ELSE environment END END)) agent_first_seen GROUP BY CASE WHEN first_seen >= '2026-09-07 15:00:00' THEN DATE_TRUNC('hour', first_seen) END;

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56698/commands/161060 (216 ms)

The INTERNAL_DAP variants only flip the agent_type IS NULL test, so those are listed without separate plans.

Unbounded first pass, for reference: agent first-seen 154.7 s on the group (https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56219/commands/160352) and 95.8 s on the project (https://console.postgres.ai/gitlab/projects/gitlab-production-main-v2/sessions/56219/commands/160383).

How to set up and validate locally

# bin/rails console, with ai_governance_dashboard enabled for the group
query = <<~GQL
  query {
    group(fullPath: "<group-path>") {
      aiGovernanceMetrics(timeframe: LAST_7_DAYS) {
        sessions { count trend { bucketStart count } cumulativeTrend { bucketStart count } }
        agents { cumulativeTrend { bucketStart count } }
      }
    }
  }
GQL
puts GitlabSchema.execute(query, context: { current_user: User.find_by_username('<you>') }).to_json

Expected: cumulativeTrend is monotonically non-decreasing, its first point >= the sessions created in the 30 days before the window, sessions' last point = that seed + count. Omitting the field from the selection skips both new queries.

Notes

  • Was stacked on !250274 (merged), which merged on 2026-09-03. Rebased onto master; the feature is one commit and the 30-day lookback a second.
  • Field is experiment (19.4 milestone marker), matching the rest of the dashboard API surface.

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

Edited by Andrew Jung

Merge request reports