Add group dimension to AiUsageEvents engine

Brandon Labuschagnerequested to merge
605518-ai-usage-events-adopt-group-dimension into master

What does this MR do and why?

This MR adopts the traversal_path dimension (added to the framework in !254110 (merged)) in the Analytics::AggregationEngines::AiUsageEvents engine.

  • Adds a group_id dimension to the engine, keyed by depth, reading the traversal_path column: 
    traversal_path :group_id, :integer, -> { sql('traversal_path') },
  association: {
    finder: ->(ids) { Group.id_in(ids).with_route.index_by(&:id) },
    preloader: Preloaders::GroupPolicyPreloader
  },
  description: 'Group at the requested depth of the hierarchy. ' \
    'NULL for events tracked above that depth. Events tracked in a project at that depth ' \
    'bucket by project namespace ID, which also resolves to NULL.'
  • The duoUsageEvents field on Group, Project, and Organization analytics gains dimensions { group(depth: Int) { id fullPath ... } }.
  • depth defaults to 1. The dimension resolves to Group records through the association mechanism, so GraphQL returns a Group object.
  • The finder loads groups with their routes, and the preloader runs Preloaders::GroupPolicyPreloader, so resolving fullPath and the read_group authorization check does not issue one query per group. The request spec guards this with ActiveRecord::QueryRecorder.

Why

The "Duo adoption by group" dashboard chart needs distinct AI users per group (usersCount grouped by group), and per-group trends when combined with the existing timestamp date bucket. A first attempt, !250645 (closed), grouped by the last segment of the path and was closed after review, since that segment is the project namespace id for events tracked in a project, not a group id. This MR wires the depth-based traversal_path dimension, added to the framework in MR 254110, into the AiUsageEvents engine instead.

Notes for reviewers

  • NULL semantics: ai_usage_events.traversal_path identifies namespaces only, with no marker for project rows. When the requested depth equals the path length of an event tracked in a project, the extracted id is the project namespace id; the Group finder does not find it, and GraphQL returns group: null for that row. Events tracked in a group shallower than the requested depth, and rows with the 0/ placeholder path, also return group: null. Several group: null nodes can appear in one response, since each unresolved id is its own ClickHouse bucket. Consumers charting adoption by group should drop null-group rows. This is covered by specs.
  • depth is absolute: it is counted from the top-level group, not from the group the query is scoped to.
  • prepare_base_aggregation_scope filters on traversal_path since !253365 (merged) merged on 2026-09-10, so the base scope and this dimension read the same column, and the scope condition is a primary key range on the rebuilt sort key.
  • The framework MR !254110 (merged) merged on 2026-09-15, so this MR now targets master and carries only the engine change, rebased onto current master as a single commit.
  • The sibling MR !254120 (merged) adds a groupId filter to the same engine and touches the same generated GraphQL files, so whichever of the two merges second needs a rebase.

Dependencies and merge order

  • The traversal_path column on ai_usage_events was added by !253282 (merged) (merged 2026-09-08), with a '0/' default and dual writes from Ruby.
  • !253365 (merged) ("Rebuild AI Usage Events", part of #608216) merged on 2026-09-10 and rebuilt the table with traversal_path as the leading sort key, resolving historic rows through namespace_traversal_paths_dict; rows it could not resolve keep '0/' and return group: null.
  • !254110 (merged) (the framework traversal_path dimension) merged on 2026-09-15.
  • Nothing else blocks this MR.

Query plan

Since !253365 (merged), ai_usage_events is ordered by (traversal_path, event, timestamp, user_id) and the base scope is startsWith(traversal_path, ...), a primary key range that prunes parts and granules. The dimension expression (splitByChar plus arrayElement over the same column) is evaluated only on rows that survive pruning, in the inner query, like the existing computed feature dimension.

Representative query generated by the engine for the dimension group(depth: 2) plus monthly timestamp, metric usersCount, scoped to the group gitlab-org (traversal path 1/24/, where 1 is the organization id). Depth 2 reads the third path segment because the first one is the organization id.

SELECT `ch_aggregation_inner_query`.`aeq_group_id_2` AS aeq_group_id_2,
  toStartOfInterval(`ch_aggregation_inner_query`.`aeq_timestamp_granularity_monthly`, INTERVAL 1 month) AS aeq_timestamp_granularity_monthly,
  COUNT(DISTINCT `ch_aggregation_inner_query`.`aeq_users_count`) AS aeq_users_count
FROM (
  SELECT nullIf(toUInt64OrNull(arrayElement(splitByChar('/', traversal_path), 3)), 0) AS aeq_group_id_2,
    timestamp AS aeq_timestamp_granularity_monthly,
    user_id AS aeq_users_count,
    `ai_usage_events`.`traversal_path`, `ai_usage_events`.`event`, `ai_usage_events`.`timestamp`, `ai_usage_events`.`user_id`
  FROM `ai_usage_events`
  WHERE startsWith(`ai_usage_events`.`traversal_path`, '1/24/')
  GROUP BY ALL
) `ch_aggregation_inner_query`
GROUP BY ALL

EXPLAIN indexes = 1 on a local development ClickHouse with 175,953 rows in ai_usage_events, on the rebuilt schema:

Expression ((Project names + Projection))
  Aggregating
    Expression ((Before GROUP BY + (Change column names to column identifiers + (Project names + Projection))))
      Aggregating
        Expression (Before GROUP BY)
          Expression ((WHERE + Change column names to column identifiers))
            ReadFromMergeTree (gitlab_clickhouse_development.ai_usage_events)
            Indexes:
              MinMax
                Condition: true
                Parts: 14/14
                Granules: 31/31
              Partition
                Condition: true
                Parts: 14/14
                Granules: 31/31
              PrimaryKey
                Keys:
                  traversal_path
                Condition: (traversal_path in ['1/24/', '1/240'))
                Parts: 5/14
                Granules: 5/31
                Search Algorithm: binary search
                Ranges: 5

The PrimaryKey step shows the scope condition used as a key range on traversal_path (5 of 14 parts and 5 of 31 granules read) before the dimension expression runs on the surviving rows.

References

Screenshots or screen recordings

Backend only, no screenshots.

How to set up and validate locally

Enable ClickHouse for analytics in GDK, with AI usage events flowing into ai_usage_events. Run the ClickHouse migrations so the table has the traversal_path column and the rebuilt sort key from !253365 (merged). Rows whose namespace the traversal path dictionary could not resolve show group: null.

Open http://gdk.test:3000/-/graphql-explorer and run this query, adjusting fullPath and depth:

query {
  group(fullPath: "gitlab-org") {
    analytics {
      duoUsageEvents {
        aggregated {
          nodes {
            dimensions {
              group(depth: 2) { id fullPath }
              timestampMonthly: timestamp(granularity: "monthly")
            }
            usersCount
            totalCount
          }
        }
      }
    }
  }
}

Expect one node per subgroup and month. Events tracked directly in the top-level group, or in its direct projects, appear with group: null.

42 engine spec examples and 31 request spec examples pass locally.

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.

🤖 Generated with Claude Code

Edited by Brandon Labuschagne

Merge request reports