ItemConsumersFinder & GraphQL support for group inheritance

Luke Duncalferequested to merge
627519-ItemConsumersFinder into master

What does this MR do and why?

ItemConsumersFinder & GraphQL support inheritance

This MR adds support for group inheritance records within ItemConsumersFinder, which are now exposed through the existing aiCatalogConfiguredItems GraphQL field.

By default, consumers enabled for group inheritance will be included in results. The caller can pass include_inherited: false to opt-out of receiving group inheritance records. The intention of this decision is to make group inheritance look ups to work by default.

References

Group inheritance (BE): Finders and GraphQL type (#627519)

How to set up and validate locally

In rails console enable the feature flag:

Feature.enable(:ai_catalog_group_inherited_consumers)

Create an agent in the project (Automate > Flows > New flow).

Ensure you set its visibility to public.

Now create a group inheritance consumer for a top-level group (replacing <item-id> with the new agent ID, and <top-level-group-id> with the group ID:

mutation {
  aiCatalogItemConsumerCreate(input: {
    itemId: "gid://gitlab/Ai::Catalog::Item/<item-id>",
    target: { groupId: "gid://gitlab/Group/<top-level-group-id>" },
    consumerKind: INHERITANCE
  }) {
    errors
    itemConsumer { 
      id
      consumerKind
    }
  }
}

Now, for any descendant project of the group, query for the configured items of the project (replacing <project-id> with the real project ID):

query {
  aiCatalogConfiguredItems(projectId: "gid://gitlab/Project/<project-id>"){
    nodes {
      id
      consumerKind
      item {
        name
      }
    }
  }
}

In amongst the results should be the group inheritance record, for example:

{
  "data": {
    "aiCatalogConfiguredItems": {
      "nodes": [
        {
          "id": "gid://gitlab/Ai::Catalog::ItemConsumer/450",
          "consumerKind": "INHERITANCE",
          "item": {
            "name": "Agent for group enablement"
          }
        }
      ]
    }
  },
  "correlationId": "01M49T9JTJWRGVJVP9BE3GQ5P9"
}

Now call the same query, this time with includeInherited: false:

query {
  aiCatalogConfiguredItems(projectId: "gid://gitlab/Project/<project-id>", includeInherited: false){
    nodes {
      id
      consumerKind
      item {
        name
      }
    }
  }
}

The inheritance record should not be included.

Also test without includeInherited, but the flag disabled, and inheritance records should not be included.

New finder SQL

We've added this line to ItemConsumersFinder.

First, adding 100 inheritance records into a Postgres.ai session, to be able to test the new OR:

exec INSERT INTO ai_catalog_item_consumers
  (ai_catalog_item_id, group_id, kind, enabled, locked, created_at, updated_at)
SELECT i.id, 9970, 1, true, true, now(), now()
FROM ai_catalog_items i
WHERE i.visibility = 2                          -- public
  AND i.deleted_at IS NULL
  AND i.foundational_flow_reference IS NULL     -- foundational flows can't be inherited
  AND i.organization_id = (SELECT organization_id FROM namespaces WHERE id = 9970)
  AND NOT EXISTS (
    SELECT 1 FROM ai_catalog_item_consumers c
    WHERE c.group_id = 9970 AND c.ai_catalog_item_id = i.id
  )
ORDER BY i.id
LIMIT 100;

SQL generated through GraphQL:

SELECT "ai_catalog_item_consumers".*,
       CASE
         WHEN "item"."id" IN ( 348, 356, 1003596, 1004583,
                               1005590, 1003979 ) THEN 1
         WHEN "item"."id" IN ( 2337, 2334, 2331, 2332 ) THEN 2
         WHEN "item"."verification_level" = 100 THEN 3
         WHEN "project_authorizations"."access_level" IS NOT NULL THEN 4
         ELSE 5
       end AS consumer_priority
FROM   "ai_catalog_item_consumers"
       INNER JOIN "ai_catalog_items" "item"
               ON "item"."id" = "ai_catalog_item_consumers"."ai_catalog_item_id"
       LEFT OUTER JOIN "project_authorizations"
                    ON "project_authorizations"."project_id" =
                       "item"."project_id"
                       AND "project_authorizations"."user_id" = 2702368
WHERE  ( "ai_catalog_item_consumers"."project_id" = 278964
          OR "ai_catalog_item_consumers"."group_id" = 9970
             AND "ai_catalog_item_consumers"."kind" = 1 )
ORDER  BY CASE
            WHEN "item"."id" IN ( 348, 356, 1003596, 1004583,
                                  1005590, 1003979 ) THEN 1
            WHEN "item"."id" IN ( 2337, 2334, 2331, 2332 ) THEN 2
            WHEN "item"."verification_level" = 100 THEN 3
            WHEN "project_authorizations"."access_level" IS NOT NULL THEN 4
            ELSE 5
          end ASC,
          "ai_catalog_item_consumers"."id" DESC
LIMIT  20

Explain in Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62263/commands/167392

Explain plan:

 Limit  (cost=45.81..45.83 rows=6 width=86) (actual time=49.570..49.576 rows=20 loops=1)
   Buffers: shared hit=1308 read=47
   I/O Timings: read=48.181 write=0.000
   ->  Sort  (cost=45.81..45.83 rows=6 width=86) (actual time=49.569..49.572 rows=20 loops=1)
         Sort Key: (CASE WHEN (item.id = ANY ('{348,356,1003596,1004583,1005590,1003979}'::bigint[])) THEN 1 WHEN (item.id = ANY ('{2337,2334,2331,2332}'::bigint[])) THEN 2 WHEN (item.verification_level = 100) THEN 3 WHEN (project_authorizations.access_level IS NOT NULL) THEN 4 ELSE 5 END), ai_catalog_item_consumers.id DESC
         Sort Method: top-N heapsort  Memory: 30kB
         Buffers: shared hit=1308 read=47
         I/O Timings: read=48.181 write=0.000
         ->  Nested Loop Left Join  (cost=4.79..45.74 rows=6 width=86) (actual time=4.431..49.477 rows=182 loops=1)
               Buffers: shared hit=1302 read=47
               I/O Timings: read=48.181 write=0.000
               ->  Nested Loop  (cost=4.21..32.97 rows=6 width=100) (actual time=0.067..0.775 rows=182 loops=1)
                     Buffers: shared hit=620
                     I/O Timings: read=0.000 write=0.000
                     ->  Bitmap Heap Scan on public.ai_catalog_item_consumers  (cost=3.92..13.15 rows=6 width=82) (actual time=0.055..0.240 rows=182 loops=1)
                           Recheck Cond: ((ai_catalog_item_consumers.project_id = 278964) OR ((ai_catalog_item_consumers.group_id = 9970) AND (ai_catalog_item_consumers.kind = 1)))
                           Heap Blocks: exact=62
                           Buffers: shared hit=74
                           I/O Timings: read=0.000 write=0.000
                           ->  BitmapOr  (cost=3.92..3.92 rows=6 width=0) (actual time=0.045..0.046 rows=0 loops=1)
                                 Buffers: shared hit=12
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Bitmap Index Scan on index_ai_catalog_item_consumers_on_project_id  (cost=0.00..1.98 rows=6 width=0) (actual time=0.024..0.024 rows=63 loops=1)
                                       Index Cond: (ai_catalog_item_consumers.project_id = 278964)
                                       Buffers: shared hit=6
                                       I/O Timings: read=0.000 write=0.000
                                 ->  Bitmap Index Scan on index_ai_catalog_item_consumers_on_group_id_kind_sa  (cost=0.00..1.94 rows=1 width=0) (actual time=0.020..0.020 rows=119 loops=1)
                                       Index Cond: ((ai_catalog_item_consumers.group_id = 9970) AND (ai_catalog_item_consumers.kind = 1))
                                       Buffers: shared hit=6
                                       I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using ai_catalog_items_pkey on public.ai_catalog_items item  (cost=0.29..3.30 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=182)
                           Index Cond: (item.id = ai_catalog_item_consumers.ai_catalog_item_id)
                           Buffers: shared hit=546
                           I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using project_authorizations_pkey on public.project_authorizations  (cost=0.58..2.10 rows=1 width=8) (actual time=0.267..0.267 rows=0 loops=182)
                     Index Cond: ((project_authorizations.user_id = 2702368) AND (project_authorizations.project_id = item.project_id))
                     Heap Fetches: 1
                     Buffers: shared hit=682 read=47
                     I/O Timings: read=48.181 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: -5221354515240028815

Explain link: https://explain.depesz.com/s/nLTd

SQL Generated within API endpoints

Generated here (as requested !259236 (comment 3967653840)).

SELECT "ai_catalog_item_consumers".*,
       CASE
         WHEN "item"."verification_level" = 100 THEN 3
         WHEN "project_authorizations"."access_level" IS NOT NULL THEN 4
         ELSE 5
       end AS consumer_priority
FROM   "ai_catalog_item_consumers"
       INNER JOIN "ai_catalog_items" "item"
               ON "item"."id" = "ai_catalog_item_consumers"."ai_catalog_item_id"
       LEFT OUTER JOIN "project_authorizations"
                    ON "project_authorizations"."project_id" =
                       "item"."project_id"
                       AND "project_authorizations"."user_id" = 2702368
WHERE  ( "ai_catalog_item_consumers"."project_id" = 278964
          OR "ai_catalog_item_consumers"."group_id" = 9970
             AND "ai_catalog_item_consumers"."kind" = 1 )
       AND "ai_catalog_item_consumers"."ai_catalog_item_id" = 1015514
ORDER  BY CASE
            WHEN "item"."verification_level" = 100 THEN 3
            WHEN "project_authorizations"."access_level" IS NOT NULL THEN 4
            ELSE 5
          end ASC,
          "ai_catalog_item_consumers"."id" DESC
LIMIT  1

Explain in Postgres.ai https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62263/commands/167393

Explain plan:

 Limit  (cost=14.78..14.78 rows=1 width=86) (actual time=2.846..2.848 rows=1 loops=1)
   Buffers: shared hit=30 read=3
   I/O Timings: read=2.657 write=0.000
   ->  Sort  (cost=14.78..14.78 rows=1 width=86) (actual time=2.844..2.847 rows=1 loops=1)
         Sort Key: (CASE WHEN (item.verification_level = 100) THEN 3 WHEN (project_authorizations.access_level IS NOT NULL) THEN 4 ELSE 5 END), ai_catalog_item_consumers.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=30 read=3
         I/O Timings: read=2.657 write=0.000
         ->  Nested Loop Left Join  (cost=7.19..14.77 rows=1 width=86) (actual time=2.812..2.818 rows=1 loops=1)
               Buffers: shared hit=24 read=3
               I/O Timings: read=2.657 write=0.000
               ->  Nested Loop  (cost=6.61..11.15 rows=1 width=92) (actual time=0.767..0.772 rows=1 loops=1)
                     Buffers: shared hit=18 read=1
                     I/O Timings: read=0.662 write=0.000
                     ->  Bitmap Heap Scan on public.ai_catalog_item_consumers  (cost=6.32..7.84 rows=1 width=82) (actual time=0.752..0.754 rows=1 loops=1)
                           Recheck Cond: ((ai_catalog_item_consumers.ai_catalog_item_id = 1015514) AND ((ai_catalog_item_consumers.project_id = 278964) OR ((ai_catalog_item_consumers.group_id = 9970) AND (ai_catalog_item_consumers.kind = 1))))
                           Heap Blocks: exact=1
                           Buffers: shared hit=15 read=1
                           I/O Timings: read=0.662 write=0.000
                           ->  BitmapAnd  (cost=6.32..6.32 rows=1 width=0) (actual time=0.739..0.740 rows=0 loops=1)
                                 Buffers: shared hit=14 read=1
                                 I/O Timings: read=0.662 write=0.000
                                 ->  Bitmap Index Scan on index_ai_catalog_item_consumers_on_ai_catalog_item_id  (cost=0.00..2.15 rows=29 width=0) (actual time=0.691..0.691 rows=2 loops=1)
                                       Index Cond: (ai_catalog_item_consumers.ai_catalog_item_id = 1015514)
                                       Buffers: shared hit=5 read=1
                                       I/O Timings: read=0.662 write=0.000
                                 ->  BitmapOr  (cost=3.92..3.92 rows=6 width=0) (actual time=0.046..0.047 rows=0 loops=1)
                                       Buffers: shared hit=9
                                       I/O Timings: read=0.000 write=0.000
                                       ->  Bitmap Index Scan on index_ai_catalog_item_consumers_on_project_id  (cost=0.00..1.98 rows=6 width=0) (actual time=0.022..0.022 rows=63 loops=1)
                                             Index Cond: (ai_catalog_item_consumers.project_id = 278964)
                                             Buffers: shared hit=3
                                             I/O Timings: read=0.000 write=0.000
                                       ->  Bitmap Index Scan on index_ai_catalog_item_consumers_on_group_id_kind_sa  (cost=0.00..1.94 rows=1 width=0) (actual time=0.024..0.024 rows=119 loops=1)
                                             Index Cond: ((ai_catalog_item_consumers.group_id = 9970) AND (ai_catalog_item_consumers.kind = 1))
                                             Buffers: shared hit=6
                                             I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using ai_catalog_items_pkey on public.ai_catalog_items item  (cost=0.29..3.30 rows=1 width=18) (actual time=0.014..0.015 rows=1 loops=1)
                           Index Cond: (item.id = 1015514)
                           Buffers: shared hit=3
                           I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_project_authorizations_on_project_user_access_level on public.project_authorizations  (cost=0.58..3.60 rows=1 width=8) (actual time=2.041..2.042 rows=1 loops=1)
                     Index Cond: ((project_authorizations.project_id = item.project_id) AND (project_authorizations.user_id = 2702368))
                     Heap Fetches: 0
                     Buffers: shared hit=6 read=2
                     I/O Timings: read=1.994 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: -4169221697175183799

Explain link: https://explain.depesz.com/s/gjRS

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.

Related to #627519

Edited by Luke Duncalfe

Merge request reports