Create add on eligible users GraphQL API
What does this MR do and why?
Implements the last part of the API for: https://gitlab.com/gitlab-org/gitlab/-/issues/421974+, which will be used to create the UI in: https://gitlab.com/gitlab-org/gitlab/-/issues/421967+
Create a GraphQL API that can fetch the users in a namespace that are eligible for the :code_suggestions add-on.
Query to fetch users
{
namespace(fullPath: "<GROUP-FULL-PATH>") {
addOnEligibleUsers(addOnType: CODE_SUGGESTIONS, search: "Test") {
nodes {
id
username
name
publicEmail
avatarUrl
webUrl
lastActivityOn
lastLoginAt
addOnAssignments(
addOnPurchaseIds: ["gid://gitlab/GitlabSubscriptions::AddOnPurchase/<ADD-ON-PURCHASE-ID>"]
) {
nodes {
addOnPurchase {
name
}
}
}
}
}
}
}How to set up and validate locally
- Create a group and add a guest, developer, owner
- Enable the
hamilton_seat_managementFFFeature.enable(:hamilton_seat_management)
- Create an add on purchase for that group
GitlabSubscriptions::AddOnPurchase.create!(add_on: GitlabSubscriptions::AddOn.first, expires_on: 1.year.from_now, namespace_id: <GROUP-ID>, quantity: 10, purchase_xid: 'A-12345')
- Visit
localhost:3000/graphql-explorer, and add the query specified above
SQL Queries
Resolving the AddOnAssignments
This are generated by the batch loading in the UserAddOnAssignmentsResolver.
Query plans generated locally, where I have assignment data:
Load assignments:
Locally generated query plan: https://explain.depesz.com/s/ybts
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 (9459, 9455, 9451, 9446, 9445, 9444, 9443, 9442, 9441, 9440, 9439, 9438, 9437, 9436, 9435, 9434, 9433, 9432, 9431, 9430, 9429, 9428, 9427, 9426, 9425, 9424, 9423, 9422, 9421, 9420, 9419, 9418, 9415, 9414, 9413, 9412, 9411, 9410, 9409, 9408, 9407, 9406, 9405, 9404, 9403, 9402, 9401, 9400, 9399, 9398, 9397, 9396, 9395, 9394, 9393, 9392, 9391, 9390, 9389, 9388, 9387, 9357, 9354, 9353, 9352, 9351, 9350, 9349, 9348, 9347, 9346, 9345, 9344, 9343, 9342, 9341, 9340, 9339, 9338, 9337, 9336, 9335, 9334, 9333, 9332, 9331, 9330, 9329, 9328, 9327, 9326, 9320, 9319, 9318, 9317, 9316, 9315, 9314, 9313, 9312)
AND "subscription_add_on_purchases"."id" IN (1, 2, 3, 4, 5)
AND (expires_on >= '2023-10-02')Load purchases:
Locally generated query plan: https://explain.depesz.com/s/GQQZ
SELECT
"subscription_add_on_purchases".*
FROM
"subscription_add_on_purchases"
WHERE
"subscription_add_on_purchases"."id" IN (3, 2)Load add on:
Locally generated query plan: https://explain.depesz.com/s/SqjW
SELECT
"subscription_add_ons".*
FROM
"subscription_add_ons"
WHERE
"subscription_add_ons"."id" = 1 Load assigned_user count:
Locally generated query plan: https://explain.depesz.com/s/13KW
SELECT
"subscription_user_add_on_assignments".*
FROM
"subscription_user_add_on_assignments"
WHERE
"subscription_user_add_on_assignments"."add_on_purchase_id" IN (3, 2)Load namespaces for authorisation:
Locally generated query plan: https://explain.depesz.com/s/Mb2d
SELECT
"namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM
"namespaces"
WHERE
"namespaces"."id" IN (22, 510)Rewriting the BilledUsersFinder query
Context for this query here: !129203 (comment 1559493910)
Query plan on gitlab-org:
- First run: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22786/commands/73487
- Second run: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22786/commands/73488
Find all eligible users
SELECT
"users"."id",
"users"."email",
"users"."encrypted_password",
"users"."reset_password_token",
"users"."reset_password_sent_at",
"users"."remember_created_at",
"users"."sign_in_count",
"users"."current_sign_in_at",
"users"."last_sign_in_at",
"users"."current_sign_in_ip",
"users"."last_sign_in_ip",
"users"."created_at",
"users"."updated_at",
"users"."name",
"users"."admin",
"users"."projects_limit",
"users"."failed_attempts",
"users"."locked_at",
"users"."username",
"users"."can_create_group",
"users"."can_create_team",
"users"."state",
"users"."color_scheme_id",
"users"."password_expires_at",
"users"."created_by_id",
"users"."last_credential_check_at",
"users"."avatar",
"users"."confirmation_token",
"users"."confirmed_at",
"users"."confirmation_sent_at",
"users"."unconfirmed_email",
"users"."hide_no_ssh_key",
"users"."admin_email_unsubscribed_at",
"users"."notification_email",
"users"."hide_no_password",
"users"."password_automatically_set",
"users"."encrypted_otp_secret",
"users"."encrypted_otp_secret_iv",
"users"."encrypted_otp_secret_salt",
"users"."otp_required_for_login",
"users"."otp_backup_codes",
"users"."public_email",
"users"."dashboard",
"users"."project_view",
"users"."consumed_timestep",
"users"."layout",
"users"."hide_project_limit",
"users"."note",
"users"."unlock_token",
"users"."otp_grace_period_started_at",
"users"."external",
"users"."incoming_email_token",
"users"."auditor",
"users"."require_two_factor_authentication_from_group",
"users"."two_factor_grace_period",
"users"."last_activity_on",
"users"."notified_of_own_activity",
"users"."preferred_language",
"users"."theme_id",
"users"."accepted_term_id",
"users"."feed_token",
"users"."private_profile",
"users"."roadmap_layout",
"users"."include_private_contributions",
"users"."commit_email",
"users"."group_view",
"users"."managing_group_id",
"users"."first_name",
"users"."last_name",
"users"."static_object_token",
"users"."role",
"users"."user_type",
"users"."static_object_token_encrypted",
"users"."otp_secret_expires_at",
"users"."onboarding_in_progress"
FROM
"users"
WHERE
"users"."id" IN ( WITH "our_group_namespaces" AS (
SELECT
"namespaces"."id",
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS last_traversal_id
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))),
"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
last_traversal_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)
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")))
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)
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)))) members)
ORDER BY
"users"."id" DESC
LIMIT 101MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Josianne Hyson