feat: Update default sort order for AI Catalog

What does this MR do and why?

feat: Update default sort order for AI Catalog

Implement default sort order for AI Catalog to improve discoverability of trusted, production-ready content.

Changes:

• GitLab foundational agents/flows appear first
• All other public agents/flows appear last

This sort order applies automatically on both agents and flows catalog pages (Explore global pages and Automate group/project pages) with no user action required.

This MR modifies the ordering logic for two GraphQL endpoints: aiCatalogItems and aiCatalogConfiguredItems. As part of the verification process, please also verify that pagination is working as expected on both the end-points.

References

#587379

How to set up and validate locally

Global Catalog Verification

Impersonate any user (or login as admin) and visit Explore > AI Catalog (Agents & Flows tabs).

Verify the following display priority:

1. Foundational Agents/Flows.
2. Other Agents/Flows
3. Verify pagination is working fine.
4. Verify searching is working fine.

---

Project-Level Verification

Navigate to the automation page within the specific project/group to verify local inheritance.

A. Flows Verification

• Path: Any Group > Project > Automate > Flows
• Expected Order:
  1. Foundational Flows (Gitlab maintained flows)
  2. Other Flows

B. Agents Verification

• Path: Any Group > Project > Automate > Agents
• Expected Order:
  1. Any agent

NOTE: Foundational Agents are not appear at the Project level. Refer to this issue for more details.

Please also verify that pagination is working as expected on both the GraphQL end-points (aiCatalogItems and aiCatalogConfiguredItems). Use below GraphQL endpoint for a reference

Queries

1) aiCatalogItems

query all_items {
  aiCatalogItems(first: 3) {
    pageInfo {
      endCursor
      startCursor
    },
    nodes {
      id
      name
      description
      itemType
      project {
        id
      }
      latestVersion {
        id
        releasedAt
        humanVersionName
        ... on AiCatalogAgentVersion {
          systemPrompt
          userPrompt
          tools {
            nodes {
              id
              name
            }
          }
        }
        ... on AiCatalogFlowVersion {
          definition
        }
      }
      versions {
        edges {
          node {
            id
            releasedAt
             ... on AiCatalogAgentVersion {
              systemPrompt
              userPrompt
                tools {
                  nodes {
                    id
                    name
                    }
                  }
                }
            ... on AiCatalogFlowVersion {
              definition
            }
          }
        }
      }
    }
    
  }
}

2) aiCatalogConfiguredItems

query configured_items{  
  aiCatalogConfiguredItems(groupId: "gid://gitlab/Group/35", first: 2, after: ""){
    pageInfo {
      endCursor
      startCursor
    },
    nodes {
      id
      item {
        id
        name
        description
      	itemType
        project {
          id
        }
        latestVersion {
        id
        releasedAt
        humanVersionName
        ... on AiCatalogAgentVersion {
          systemPrompt
          userPrompt
          tools {
            nodes {
              id
              name
            }
          }
        }
        ... on AiCatalogFlowVersion {
              definition
            }
      }
        versions {
        edges {
          node {
            id
            releasedAt
             ... on AiCatalogAgentVersion {
              systemPrompt
              userPrompt
                tools {
                  nodes {
                    id
                    name
                    }
                  }
                }
            ... on AiCatalogFlowVersion {
              definition
            }
          }
        }
      }
      }
    }
  }
}

Screenshots or screen recordings

Explore level catalog page

Project level pages

Database review

1) Ai::Catalog::Item.order_by_catalog_priority

Query

Plan - https://console.postgres.ai/gitlab/gitlab-production-main/sessions/47983/commands/144816

SELECT
  "ai_catalog_items".*
FROM
  "ai_catalog_items"
/* loading for pp */
ORDER BY
  array_position(ARRAY[348, 356]::bigint[], id) ASC,
  foundational_flow_reference ASC,
  "ai_catalog_items"."id" DESC
LIMIT 11

2) Ai::Catalog::ItemConsumer.order_by_catalog_priority

Query

Plan - https://console.postgres.ai/gitlab/gitlab-production-main/sessions/47983/commands/144815

EXPLAIN SELECT
  "ai_catalog_item_consumers".*
FROM
  "ai_catalog_item_consumers"
INNER JOIN
  "ai_catalog_items" "item" ON "item"."id" = "ai_catalog_item_consumers"."ai_catalog_item_id"
/* loading for pp */
ORDER BY
  item.foundational_flow_reference ASC,
  "ai_catalog_item_consumers"."id" DESC
LIMIT 11

3) ItemConsumer.order_by_user_affinity is being used in below way

container.configured_ai_catalog_items.order_by_user_affinity

SQL Query

Plan : https://console.postgres.ai/gitlab/gitlab-production-main/sessions/47983/commands/144821

SELECT
  "ai_catalog_item_consumers".*
FROM
  "ai_catalog_item_consumers"
INNER JOIN
  "ai_catalog_items" "item" ON "item"."id" = "ai_catalog_item_consumers"."ai_catalog_item_id"
WHERE
  "ai_catalog_item_consumers"."group_id" = 9970 /* gitlab-org */
/* loading for pp */
ORDER BY
  item.foundational_flow_reference ASC,
  "ai_catalog_item_consumers"."id" DESC
LIMIT 21

4) Item.order_by_user_affinity is being used in below way

Item.not_deleted.public_or_visible_to_user(current_user).order_by_user_affinity

SQL Query

Query

Plan : https://console.postgres.ai/gitlab/gitlab-production-main/sessions/47983/commands/144820

SELECT
  "ai_catalog_items".*
FROM
  "ai_catalog_items"
LEFT JOIN
  project_authorizations pa
    ON ai_catalog_items.project_id = pa.project_id
    AND pa.user_id = 487608
    AND pa.access_level >= 30
WHERE
  "ai_catalog_items"."deleted_at" IS NULL
  AND (ai_catalog_items.public = TRUE OR pa.project_id IS NOT NULL)
  AND "ai_catalog_items"."organization_id" = 1
  AND "ai_catalog_items"."item_type" IN (1, 3)
ORDER BY
  array_position(ARRAY[348, 356]::bigint[], id) ASC,
  foundational_flow_reference ASC,
  "ai_catalog_items"."id" DESC
LIMIT 21

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 #587379