Add group filter to AiUsageEvents engine

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

What does this MR do and why?

This MR adds a groupId filter to the Analytics::AggregationEngines::AiUsageEvents ClickHouse aggregation engine, in ee/app/models/analytics/aggregation_engines/ai_usage_events.rb:

descendants :group_id, :string, -> { sql('traversal_path') }, max_size: 100,
  description: 'Filter by one or many group Global IDs, including events from their descendants'

On the GraphQL side, the duoUsageEvents field on Group, Project, and Organization analytics gets a new groupId: [String!] argument. doc/api/graphql/reference/_index.md and public/-/graphql/introspection_result.json are regenerated. There are no new translatable strings.

The filter accepts one or more Group Global IDs and matches events tracked in the group or any of its descendants (subgroups and projects). Global IDs of other models, for example a Project, are dropped, so a colliding numeric id cannot select an unrelated group. If none of the given values resolve to a group, the filter matches nothing and the query returns zero counts.

The engine always applies its authorized base scope, startsWith(traversal_path, '<scope path>'), on top of this filter. A group outside the queried scope, and not one of its ancestors, returns zero counts. An ancestor of the queried scope returns the unfiltered counts, because the ancestor's traversal path is a prefix of the scope path, so every in-scope row matches it too.

max_size: 100 limits how many Global IDs one request can resolve. The framework checks max_size before the formatter runs, so at most 100 Global IDs reach the PostgreSQL lookup (Group.id_in) and at most 100 startsWith conditions reach ClickHouse. A request with more values fails validation with the message maximum size of 100 exceeded for filter group_id``, surfaced as a GraphQL error.

The framework pieces used here, the descendants filter class Gitlab::Database::Aggregation::ClickHouse::DescendantsFilter, its filters_mapping registration, the default group_traversal_path_formatter(with_organization: true) that resolves Group Global IDs to organization-prefixed traversal paths, and the developer docs section, were split out of this MR at a reviewer's request into !255052 (merged). This MR is stacked on that one: it targets the branch 605518-traversal-path-filter until that MR merges, then it will be retargeted to master. Merge order: !255052 (merged) first, then this MR.

The companion traversal_path dimension merged in !254110 (merged). Its engine adoption, a group dimension with a depth argument on the same engine, is the sibling MR !254731 (merged).

Why

The "Duo adoption by group" dashboard needs to drill into one group. The original issue asked for an exact_match filter on a namespace id. During review of the closed first attempt, !250645 (closed), the reviewer asked for subtree semantics, so a group's numbers include its subgroups and projects. That design is written up in https://gitlab.com/gitlab-org/gitlab/-/work_items/617805.

Notes for reviewers

The formatter runs one PostgreSQL primary key lookup (Group.id_in) per request before ClickHouse is queried. It does not check the current user's access to the given groups, and does not need to, because the base scope condition is always applied as well. Traversal paths end in /, so a group with id 1 cannot match 12/.

ee/spec/models/analytics/aggregation_engines/ai_usage_events_spec.rb covers the group plus all descendants, multiple Global IDs, Global IDs of other models matching nothing, and more than 100 Global IDs being rejected. ee/spec/requests/api/graphql/analytics/ai_analytics/duo_usage_events_spec.rb covers an organization scope over two groups filtered down to one group's events, and a group scope filtered by an ancestor group returning the unfiltered counts. The group fixture is now a subgroup of a new parent_group fixture, to make that possible.

References

Screenshots or screen recordings

This MR is backend and GraphQL only, so there are no screenshots.

How to set up and validate locally

  1. Enable ClickHouse for analytics in GDK, with AI usage events in the ai_usage_events table, ideally in a project inside a subgroup. Run the ClickHouse migrations so the table has the traversal_path column.
  2. Open the GraphQL explorer at http://gdk.test:3000/-/graphql-explorer.
  3. Run this query, replacing <subgroup-id> with the id of a subgroup of gitlab-org:
query {
  group(fullPath: "gitlab-org") {
    analytics {
      duoUsageEvents(groupId: ["gid://gitlab/Group/<subgroup-id>"]) {
        aggregated {
          nodes {
            dimensions {
              timestampMonthly: timestamp(granularity: "monthly")
            }
            usersCount
            totalCount
          }
        }
      }
    }
  }
}
  1. Expect the totals to cover only events tracked in that subgroup and its projects. Passing the Global ID of a group outside gitlab-org returns zero counts. Passing the Global ID of an ancestor of the queried group returns the same numbers as the unfiltered query.

Query plan

Since !253365 (merged) merged, ai_usage_events is ordered by (traversal_path, event, timestamp, user_id), so traversal_path is the primary key prefix. Both the base scope and the new groupId filter are startsWith(traversal_path, ...) conditions, which ClickHouse turns into primary key ranges. The filter prunes parts and granules instead of adding a per-row computation.

Representative query generated by the engine for a request scoped to the group gitlab-org (traversal path 1/24/, where 1 is the organization id), a groupId filter resolving to a subgroup with traversal path 1/24/470/, the monthly timestamp dimension, and the usersCount metric:

SELECT toStartOfInterval(`ch_aggregation_inner_query`.`aeq_timestamp_monthly`, INTERVAL 1 month) AS aeq_timestamp_monthly,
  COUNT(DISTINCT `ch_aggregation_inner_query`.`aeq_users_count`) AS aeq_users_count
FROM (
  SELECT timestamp AS aeq_timestamp_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/') AND startsWith(traversal_path, '1/24/470/')
  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:

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: and((traversal_path in ['1/24/470/', '1/24/4700')), (traversal_path in ['1/24/', '1/240')))
                Parts: 1/14
                Granules: 1/31
                Search Algorithm: binary search
                Ranges: 1

The PrimaryKey step shows both startsWith conditions used as key ranges. 1 of 14 parts and 1 of 31 granules are read. Without the groupId filter, the scope condition alone reads 5 of 14 parts and 5 of 31 granules.

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