Skip to content

Adjustments to add on eligible users API to return filtered results

Divya Mahadevan requested to merge 488507-filter-assigned-seats-be into master

What does this MR do and why?

Resolves Adjust eligible users list API to return filter... (#488507 - closed)

This MR implements ability to filter add on eligible users list based on the assigned seats for Gitlab Duo/Enterprise. The frontend functionality for this feature has been shipped through !166317 (merged) and this MR ties the whole functionality together.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Scenario Screenshot Result
Assigned seat: Yes Screenshot 2024-09-25 at 15.11.43.png Eligible users list is sorted based on assigned users
Assigned seat: No Screenshot 2024-09-25 at 15.12.33.png Eligible users list is sorted based on users not assigned
No filters Screenshot 2024-09-25 at 15.12.01.png All eligible users are returned

Database

Self-managed:

User selection query (filter_by_assigned_seat: true)

Explanation: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32126/commands/99214

SELECT
  "users".*
FROM
  "users"
WHERE ("users"."state" IN ('active'))
  AND "users"."user_type" IN (0, 6, 4, 13)
  AND "users"."user_type" IN (0, 4, 5, 15, 17)
  AND "users"."user_type" IN (0, 1, 2, 3, 4, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
  AND "users"."id" IN (
    SELECT
      "subscription_user_add_on_assignments"."user_id"
    FROM
      "subscription_user_add_on_assignments"
      INNER JOIN "subscription_add_on_purchases" ON "subscription_add_on_purchases"."id" = "subscription_user_add_on_assignments"."add_on_purchase_id"
    WHERE
      "subscription_add_on_purchases"."id" = 15
      AND (started_at IS NULL
        OR started_at <= '2024-10-01')
      AND ('2024-10-01' < expires_on))

User selection query(filter_by_assigned_seat: false)

Explanation: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32126/commands/99217

SELECT
  "users".*
FROM
  "users"
WHERE ("users"."state" IN ('active'))
  AND "users"."user_type" IN (0, 6, 4, 13)
  AND "users"."user_type" IN (0, 4, 5, 15, 17)
  AND "users"."user_type" IN (0, 1, 2, 3, 4, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
  AND "users"."id" NOT IN (
    SELECT
      "subscription_user_add_on_assignments"."user_id"
    FROM
      "subscription_user_add_on_assignments"
      INNER JOIN "subscription_add_on_purchases" ON "subscription_add_on_purchases"."id" = "subscription_user_add_on_assignments"."add_on_purchase_id"
    WHERE
      "subscription_add_on_purchases"."id" = 16
      AND (started_at IS NULL
        OR started_at <= '2024-10-01')
      AND ('2024-10-01' < expires_on))
SaaS:

User selection query (filter_by_assigned_seat: true)

Explanation: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32126/commands/99208

SELECT
  "users".*
FROM
  "users"
WHERE
  "users"."id" IN ( WITH "our_group_namespaces" AS (
      SELECT
        "namespaces"."id"
      FROM
        UNNEST(COALESCE((
            SELECT
              ids
            FROM (
              SELECT
                "namespace_descendants"."self_and_descendant_group_ids" AS ids
              FROM "namespace_descendants"
            WHERE
              "namespace_descendants"."outdated_at" IS NULL
              AND "namespace_descendants"."namespace_id" = 9970) cached_query), (
            SELECT
              ids
            FROM (
              SELECT
                ARRAY_AGG("namespaces"."id") AS ids
              FROM (
                SELECT
                  namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
                FROM "namespaces"
              WHERE
                "namespaces"."type" = 'Group'
                AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query))) AS namespaces (id)),
    "base_ancestors_cte" AS (
      SELECT
        "namespaces"."traversal_ids"
      FROM
        "namespaces"
      WHERE
        "namespaces"."type" = 'Group'
        AND ("namespaces"."id" IN (
            SELECT
              "group_group_links"."shared_with_group_id"
            FROM
              group_group_links
            WHERE
              "group_group_links"."shared_group_id" IN (
                SELECT
                  id
                FROM
                  our_group_namespaces)
              UNION
              SELECT
                "project_group_links"."group_id"
              FROM
                project_group_links
              WHERE
                "project_group_links"."project_id" IN (
                  SELECT
                    id
                  FROM
                    projects
                  WHERE
                    "projects"."namespace_id" IN (
                      SELECT
                        id
                      FROM
                        "our_group_namespaces"))))),
            "our_namespace_bans" AS (
              SELECT
                "namespace_bans"."user_id"
              FROM
                "namespace_bans"
              WHERE
                "namespace_bans"."namespace_id" = 9970
)
              SELECT
                "members"."user_id"
              FROM ((
                  SELECT
                    "members"."user_id"
                  FROM
                    "members"
                  LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
              WHERE
                "members"."type" = 'ProjectMember'
                AND "members"."source_type" = 'Project'
                AND ("users"."state" IN ('active'))
                AND "users"."user_type" IN (0, 4, 5, 15, 17)
                AND "members"."state" = 0
                AND "members"."requested_at" IS NULL
                AND "members"."invite_token" IS NULL
                AND (members.access_level > 5)
                AND "members"."user_id" NOT IN (
                  SELECT
                    "user_id"
                  FROM
                    "our_namespace_bans")
                  AND "members"."source_id" IN (
                    SELECT
                      "projects"."id"
                    FROM
                      "projects"
                    WHERE
                      "projects"."namespace_id" IN (
                        SELECT
                          "id"
                        FROM
                          "our_group_namespaces"))
                      /* allow_cross_joins_across_databases */)
                  UNION (
                    SELECT
                      "members"."user_id"
                    FROM
                      "members"
                    LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
                WHERE
                  "members"."type" = 'GroupMember'
                  AND "members"."source_type" = 'Namespace'
                  AND ("users"."state" IN ('active'))
                  AND "users"."user_type" IN (0, 4, 5, 15, 17)
                  AND "members"."state" = 0
                  AND "members"."requested_at" IS NULL
                  AND "members"."invite_token" IS NULL
                  AND (members.access_level > 5)
                  AND "members"."user_id" NOT IN (
                    SELECT
                      "user_id"
                    FROM
                      "our_namespace_bans")
                    AND ("members"."source_id" IN (
                        SELECT
                          unnest("base_ancestors_cte"."traversal_ids")
                        FROM
                          base_ancestors_cte
                        UNION
                        SELECT
                          id
                        FROM
                          our_group_namespaces))
                      /* allow_cross_joins_across_databases */)) members)
              AND "users"."id" IN (
                SELECT
                  "subscription_user_add_on_assignments"."user_id"
                FROM
                  "subscription_user_add_on_assignments"
                  INNER JOIN "subscription_add_on_purchases" ON "subscription_add_on_purchases"."id" = "subscription_user_add_on_assignments"."add_on_purchase_id"
                WHERE
                  "subscription_add_on_purchases"."id" = 13
                  AND (started_at IS NULL
                    OR started_at <= '2024-10-01')
                  AND ('2024-10-01' < expires_on))
              /* allow_cross_joins_across_databases */

User selection query (filter_by_assigned_seat: false)

Explanation: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32126/commands/99222

SELECT
  "users".*
FROM
  "users"
WHERE
  "users"."id" IN ( WITH "our_group_namespaces" AS (
      SELECT
        "namespaces"."id"
      FROM
        UNNEST(COALESCE((
            SELECT
              ids
            FROM (
              SELECT
                "namespace_descendants"."self_and_descendant_group_ids" AS ids
              FROM "namespace_descendants"
            WHERE
              "namespace_descendants"."outdated_at" IS NULL
              AND "namespace_descendants"."namespace_id" = 9970) cached_query), (
            SELECT
              ids
            FROM (
              SELECT
                ARRAY_AGG("namespaces"."id") AS ids
              FROM (
                SELECT
                  namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
                FROM "namespaces"
              WHERE
                "namespaces"."type" = 'Group'
                AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query))) AS namespaces (id)),
    "base_ancestors_cte" AS (
      SELECT
        "namespaces"."traversal_ids"
      FROM
        "namespaces"
      WHERE
        "namespaces"."type" = 'Group'
        AND ("namespaces"."id" IN (
            SELECT
              "group_group_links"."shared_with_group_id"
            FROM
              group_group_links
            WHERE
              "group_group_links"."shared_group_id" IN (
                SELECT
                  id
                FROM
                  our_group_namespaces)
              UNION
              SELECT
                "project_group_links"."group_id"
              FROM
                project_group_links
              WHERE
                "project_group_links"."project_id" IN (
                  SELECT
                    id
                  FROM
                    projects
                  WHERE
                    "projects"."namespace_id" IN (
                      SELECT
                        id
                      FROM
                        "our_group_namespaces"))))),
            "our_namespace_bans" AS (
              SELECT
                "namespace_bans"."user_id"
              FROM
                "namespace_bans"
              WHERE
                "namespace_bans"."namespace_id" = 9970
)
              SELECT
                "members"."user_id"
              FROM ((
                  SELECT
                    "members"."user_id"
                  FROM
                    "members"
                  LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
              WHERE
                "members"."type" = 'ProjectMember'
                AND "members"."source_type" = 'Project'
                AND ("users"."state" IN ('active'))
                AND "users"."user_type" IN (0, 4, 5, 15, 17)
                AND "members"."state" = 0
                AND "members"."requested_at" IS NULL
                AND "members"."invite_token" IS NULL
                AND (members.access_level > 5)
                AND "members"."user_id" NOT IN (
                  SELECT
                    "user_id"
                  FROM
                    "our_namespace_bans")
                  AND "members"."source_id" IN (
                    SELECT
                      "projects"."id"
                    FROM
                      "projects"
                    WHERE
                      "projects"."namespace_id" IN (
                        SELECT
                          "id"
                        FROM
                          "our_group_namespaces"))
                      /* allow_cross_joins_across_databases */)
                  UNION (
                    SELECT
                      "members"."user_id"
                    FROM
                      "members"
                    LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
                WHERE
                  "members"."type" = 'GroupMember'
                  AND "members"."source_type" = 'Namespace'
                  AND ("users"."state" IN ('active'))
                  AND "users"."user_type" IN (0, 4, 5, 15, 17)
                  AND "members"."state" = 0
                  AND "members"."requested_at" IS NULL
                  AND "members"."invite_token" IS NULL
                  AND (members.access_level > 5)
                  AND "members"."user_id" NOT IN (
                    SELECT
                      "user_id"
                    FROM
                      "our_namespace_bans")
                    AND ("members"."source_id" IN (
                        SELECT
                          unnest("base_ancestors_cte"."traversal_ids")
                        FROM
                          base_ancestors_cte
                        UNION
                        SELECT
                          id
                        FROM
                          our_group_namespaces))
                      /* allow_cross_joins_across_databases */)) members)
              AND "users"."id" NOT IN (
                SELECT
                  "subscription_user_add_on_assignments"."user_id"
                FROM
                  "subscription_user_add_on_assignments"
                  INNER JOIN "subscription_add_on_purchases" ON "subscription_add_on_purchases"."id" = "subscription_user_add_on_assignments"."add_on_purchase_id"
                WHERE
                  "subscription_add_on_purchases"."id" = 14
                  AND (started_at IS NULL
                    OR started_at <= '2024-10-01')
                  AND ('2024-10-01' < expires_on))
              /* allow_cross_joins_across_databases */

GraphQL query

query getAddOnEligibleUsers($fullPath: ID!, $addOnType: GitlabSubscriptionsAddOnType!, $addOnPurchaseIds: [GitlabSubscriptionsAddOnPurchaseID!]!, $sort: GitlabSubscriptionsUserSort!, $filterByAssignedSeat: String, $first: Int, $last: Int, $after: String, $before: String) {
  namespace(fullPath: $fullPath) {
    id
    addOnEligibleUsers(
      addOnType: $addOnType
      addOnPurchaseIds: $addOnPurchaseIds
      sort: $sort
      first: $first
      last: $last
      after: $after
      before: $before
      filterByAssignedSeat: $filterByAssignedSeat
    ) {
      nodes {
        id
        username
        name
        publicEmail
        avatarUrl
        webUrl
        lastActivityOn
        addOnAssignments(addOnPurchaseIds: $addOnPurchaseIds) {
          nodes {
            addOnPurchase {
              name
            }
          }
        }
      }
    }
  }
}

# Variables

{
  "fullPath": "twitter",
  "addOnType": "CODE_SUGGESTIONS",
  "addOnPurchaseIds": [
    "gid://gitlab/GitlabSubscriptions::AddOnPurchase/11"
  ],
  "sort": "ID_ASC",
  "first": 20,
  "last": null,
  "after": null,
  "before": null,
  "filterByAssignedSeat": "true"
}

How to set up and validate locally

  1. Needs both gdk and CustomersDot running for SaaS/SM (setup guide)
  2. In Rails console, enable Code Suggestions Feature.enable(:hamilton_seat_management)
  3. In Rails console, enable the filtering FF Feature.enable(:enable_add_on_users_filtering)

To assign Code Suggestions seats to your group:

SaaS:

  1. In Rails console, add_on = GitlabSubscriptions::AddOn.find_or_create_by!(name: "code_suggestions") {|e| e.description = "Test"}
  2. In Rails console, GitlabSubscriptions::AddOnPurchase.create!(add_on: add_on, namespace: Namespace.find(<you-name-space-id>), expires_on: 21.month.from_now, quantity: 5, purchase_xid: 'A-S0002')
  3. Visit any group or project member pages such as http://localhost:3000/groups/flightjs/-/usage_quotas#code-suggestions-usage-tab
  4. Click the search bar to select the Assinged seat filter, and running the search button should return expected results.

Self-managed:

  1. In Rails console, add_on = GitlabSubscriptions::AddOn.find_or_create_by!(name: "code_suggestions") {|e| e.description = "Test"}
  2. In Rails console, GitlabSubscriptions::AddOnPurchase.create!(add_on: add_on, expires_on: 21.month.from_now, quantity: 5, purchase_xid: 'A-S0002')
  3. Visit admin section's Duo area http://localhost:3000/admin/code_suggestions
  4. Click the search bar to select the Assinged seat filter, and running the search button should return expected results.

Related to #488507 (closed)

Edited by Divya Mahadevan

Merge request reports