Skip to content

Add addOnAssignments to UserType in GraphQL API

Josianne Hyson requested to merge jh/add_on_assignments_to_users into master

What does this MR do and why?

Addresses part of issue: https://gitlab.com/gitlab-org/gitlab/-/issues/421974+

Add the add_on_assignments field to the GraphQL API's UserType for EE. We need to be able to query this value alongside the user record to render the add-on assignment UI for the group owner. It's intended that this API will be used to list many users, so ensure that batch loading works correctly for this field.

This MR will be followed up by some of the work in Create add on eligible users GraphQL API (!129203 - merged) to list the eligible add on users.

GraphQL Queries

Navigate to http://localhost:3000/-/graphql-explorer

Create Assignment

Click to expand 
mutation userAddOnAssignmentCreate($input: UserAddOnAssignmentCreateInput!, $addOnPurchaseIds: [GitlabSubscriptionsAddOnPurchaseID!]!) {
  userAddOnAssignmentCreate(input: $input) {
    errors
    clientMutationId
    addOnPurchase {
      id
      name
      purchasedQuantity
      assignedQuantity
    }
    user {
      name
      
       addOnAssignments(addOnPurchaseIds: $addOnPurchaseIds) {
        nodes {
          addOnPurchase {
            id
            name
          }
        }
      }
    }
  }
}
{
  "input": {
    "userId": "gid://gitlab/User/1",
    "addOnPurchaseId": "gid://gitlab/GitlabSubscriptions::AddOnPurchase/1"
  },
  "addOnPurchaseIds": ["gid://gitlab/GitlabSubscriptions::AddOnPurchase/1"]
}

Remove Assignment

Click to expand 
mutation userAddOnAssignmentRemove($input: UserAddOnAssignmentRemoveInput!, $addOnPurchaseIds: [GitlabSubscriptionsAddOnPurchaseID!]!) {
  userAddOnAssignmentRemove(input: $input) {
    errors
    clientMutationId
    addOnPurchase {
      id
      name
      purchasedQuantity
      assignedQuantity
    }
    user {
      name
      
       addOnAssignments(addOnPurchaseIds: $addOnPurchaseIds) {
        nodes {
          addOnPurchase {
            id
            name
          }
        }
      }
    }
  }
}
{
  "input": {
    "userId": "gid://gitlab/User/1",
    "addOnPurchaseId": "gid://gitlab/GitlabSubscriptions::AddOnPurchase/1"
  },
  "addOnPurchaseIds": ["gid://gitlab/GitlabSubscriptions::AddOnPurchase/1"]
}

How to set up and validate locally

  1. Enable the hamilton_seat_management FF
    • Feature.enable(:hamilton_seat_management)
  2. Create an add on purchase
    • GitlabSubscriptions::AddOnPurchase.create!(add_on: GitlabSubscriptions::AddOn.first, expires_on: 1.year.from_now, namespace_id: <NAMESPACE-ID>, quantity: 10, purchase_xid: 'A-12345')
  3. Assign the add on using the create assignment mutation
  4. View the add-on assignments in the response
  5. Remove the assignment using the remove assignment mutation
  6. View the add-on assignments in the response

SQL Queries

The following queries are executed when loading the users list via the GraphQL API and requesting this new attribute:

SELECT "users".* FROM "users" ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 101
  
SELECT "subscription_user_add_on_assignments".* 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_user_add_on_assignments"."user_id" IN (109, 108, 107, 106, 105, 104, 97, 96, 95, 94, 93, 92, 91, 90, 89, 88, 87, 86, 85, 84, 83, 82, 81, 80, 79, 78, 77, 76, 75, 74, 73, 72, 71, 70, 69, 68, 67, 66, 65, 64, 63, 62, 61, 60, 59, 58, 57, 56, 55, 54, 53, 52, 51, 50, 49, 48, 47, 46, 45, 44, 43, 42, 41, 40, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4) AND "subscription_add_on_purchases"."id" IN (1, 2) AND (expires_on >= '2023-08-28')
  
SELECT "subscription_add_on_purchases".* FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."id" IN (1, 2)
  
SELECT "subscription_add_ons".* FROM "subscription_add_ons" WHERE "subscription_add_ons"."id" = 1
  
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (73, 510)

# --------- the following are loaded as a result of the `Preloaders::GroupPolicyPreloader` --------

SELECT MAX("members"."access_level") AS "maximum_access_level", "hierarchy"."id" AS "hierarchy_id" FROM ((SELECT "members"."id", "members"."access_level", "members"."source_id", "members"."source_type", "members"."user_id", "members"."notification_level", "members"."type", "members"."created_at", "members"."updated_at", "members"."created_by_id", "members"."invite_email", "members"."invite_token", "members"."invite_accepted_at", "members"."requested_at", "members"."expires_at", "members"."ldap", "members"."override", "members"."state", "members"."invite_email_success", "members"."member_namespace_id", "members"."member_role_id", "members"."expiry_notified_at" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "users"."state" = 'active' 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" = 89)
UNION
(SELECT "members"."id", LEAST("group_group_links"."group_access", "members"."access_level") AS access_level, "group_group_links"."shared_group_id" AS source_id, "members"."source_type", "members"."user_id", "members"."notification_level", "members"."type", "members"."created_at", "members"."updated_at", "members"."created_by_id", "members"."invite_email", "members"."invite_token", "members"."invite_accepted_at", "members"."requested_at", "members"."expires_at", "members"."ldap", "members"."override", "members"."state", "members"."invite_email_success", "members"."member_namespace_id", "members"."member_role_id", "members"."expiry_notified_at" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" INNER JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "users"."state" = 'active' 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" = 89)) members INNER JOIN (SELECT id, unnest(traversal_ids) as traversal_id FROM "namespaces" WHERE "namespaces"."id" IN (73, 73, 510, 510, 510, 22)) as hierarchy ON members.source_id = hierarchy.traversal_id WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' GROUP BY "hierarchy"."id"

SELECT namespaces.*, root_query.id as source_id FROM "namespaces" INNER JOIN (SELECT id, traversal_ids[1] as root_id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (73, 73, 510, 510, 510, 22)) as root_query ON root_query.root_id = namespaces.id

SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" IN (22, 73, 510)

SELECT "saml_providers"."id", "saml_providers"."group_id", "saml_providers"."enabled", "saml_providers"."certificate_fingerprint", "saml_providers"."sso_url", "saml_providers"."enforced_sso", "saml_providers"."enforced_group_managed_accounts", "saml_providers"."prohibited_outer_forks", "saml_providers"."default_membership_role", "saml_providers"."git_check_enforced" FROM "saml_providers" WHERE "saml_providers"."group_id" IN (22, 73, 510) 

SELECT VERSION()

WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (73, 73, 510, 510, 510, 22)) SELECT "namespaces"."id", "namespaces"."parent_id", "gitlab_subscriptions"."hosted_plan_id" FROM "namespaces" INNER JOIN (SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids") FROM base_ancestors_cte) AS ancestors(ancestor_id) ON namespaces.id = ancestors.ancestor_id LEFT OUTER JOIN gitlab_subscriptions ON gitlab_subscriptions.namespace_id=namespaces.id WHERE "namespaces"."type" = 'Group'

SELECT "plans".* FROM "plans" WHERE "plans"."id" IN (1, 6)

The main new addition here is the one below (link to query plan). This feature is not used in production yet, so this query plan was generated locally.

SELECT
    "subscription_user_add_on_assignments".*
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_user_add_on_assignments"."user_id" IN (109, 108, 107, 106, 105, 104, 97, 96, 95, 94, 93, 92, 91, 90, 89, 88, 87, 86, 85, 84, 83, 82, 81, 80, 79, 78, 77, 76, 75, 74, 73, 72, 71, 70, 69, 68, 67, 66, 65, 64, 63, 62, 61, 60, 59, 58, 57, 56, 55, 54, 53, 52, 51, 50, 49, 48, 47, 46, 45, 44, 43, 42, 41, 40, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4)
    AND "subscription_add_on_purchases"."id" IN (1, 2)
    AND (expires_on >= '2023-08-28')

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Josianne Hyson

Merge request reports