Add cleanup worker (!130622) · Merge requests · GitLab.org / GitLab

Bishwa Hang Rai requested to merge bhr/415302-add-cleanup-worker into master Aug 31, 2023

What does this MR do and why?

Add cleanup worker

Adds a worker to cleanup code_suggestions seat assignment, when the user is no more eligible for seat.

This can happen if user is removed from the group membership, and then has no any existing membership to the root namespace.

This worker will be invoked in follow up MR, whenever the user membership is removed (🚧 Call cleanup worker (!130751 - merged)), or invited GroupLink is deleted.

This is done to clean up seat assignment of the user if they are not member of the root-namespace via any means.

If the user still has any kind of membership to root-namespace, that makes them eligible for code_suggestions, the seat assignment won't be removed.

Issue: https://gitlab.com/gitlab-org/gitlab/-/issues/415302

Screenshots or screen recordings

n/a

How to set up and validate locally

Check out this branch
Create a new root group namespace
Setup some seed records

namespace = Namespace.last
add_on = GitlabSubscriptions::AddOn.find_or_create_by!(name: "code_suggestions") {|e| e.description = "Test"}
add_on_purchase = GitlabSubscriptions::AddOnPurchase.create!(
  add_on: add_on, namespace: namespace, expires_on: 1.month.from_now, quantity: 5, purchase_xid: 'A-S0001'
)
user = User.find 70 # John Doe7

# add user as guest
namespace.add_guest(user)

# assign seat to the user
add_on_purchase.assigned_users.create(user: user)

# enable the feature flag
Feature.enable(:hamilton_seat_management)

# does not remove seat assignment as the user still has membership to namespace
GitlabSubscriptions::AddOnPurchases::CleanupUserAddOnAssignmentWorker.new.perform(namespace.id, user.id) # returns nil
add_on_purchase.assigned_users.count # 1

# remove the existing memberships
namespace.members.where(user: user).destroy_all

# removes the seat assignment as there aren't any memberships to namespace
GitlabSubscriptions::AddOnPurchases::CleanupUserAddOnAssignmentWorker.new.perform(namespace.id, user.id) # returns deleted assignment
add_on_purchase.assigned_users.count # 0

SQL

Existing scope/sql connection were used, with no new query addition, except for destroy!. Also, refactored query from the create service, to create the helper method eligible_for_code_suggestions_seat?(user) on the group itself.

Delete command explain: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21817/commands/70714

fetch and delete

# fetch root namespace
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1764 LIMIT 1
# fetch remove_user
SELECT "users".* FROM "users" WHERE "users"."id" = 774 LIMIT 1

# fetch add_on_purchase
SELECT "subscription_add_ons"."id" FROM "subscription_add_ons" WHERE "subscription_add_ons"."name" = 1 LIMIT 1
SELECT "subscription_add_on_purchases".* FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."namespace_id" = 1764 AND "subscription_add_on_purchases"."subscription_add_on_id" = 52 ORDER BY "subscription_add_on_purchases"."id" ASC LIMIT 1

# fetch user assignment
SELECT "subscription_user_add_on_assignments".* FROM "subscription_user_add_on_assignments" WHERE "subscription_user_add_on_assignments"."add_on_purchase_id" = 72 AND "subscription_user_add_on_assignments"."user_id" = 774 ORDER BY "subscription_user_add_on_assignments"."id" ASC LIMIT 1

# delete user assignment
DELETE FROM "subscription_user_add_on_assignments" WHERE "subscription_user_add_on_assignments"."id" = 46

eligible_for_code_suggestions_seat

# group user
User Exists? (0.9ms)  SELECT 1 AS one FROM "users" WHERE "users"."id" IN (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" = 'active' AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5) AND "members"."source_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{1766}'))) AND "members"."user_id" NOT IN (SELECT "namespace_bans"."user_id" FROM "namespace_bans" WHERE "namespace_bans"."namespace_id" = 1766)) AND "users"."user_type" IN (0, 4, 5) AND "users"."id" = 775 LIMIT 1

# project user
User Exists? (0.9ms)  SELECT 1 AS one FROM "users" WHERE "users"."id" IN (SELECT "members"."user_id" FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5) AND "members"."source_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{1766}')))) AND "members"."user_id" NOT IN (SELECT "namespace_bans"."user_id" FROM "namespace_bans" WHERE "namespace_bans"."namespace_id" = 1766)) AND "users"."user_type" IN (0, 4, 5) AND ("users"."state" IN ('active')) AND "users"."id" = 775 LIMIT 1

# invited to group
User Exists? (0.9ms)  SELECT 1 AS one FROM "users" WHERE "users"."id" IN (SELECT "members"."user_id" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "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"."source_id" IN (WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids" FROM "namespaces" INNER JOIN "group_group_links" ON "group_group_links"."shared_with_group_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "group_group_links"."shared_group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{1766}')))) SELECT "namespaces"."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 WHERE "namespaces"."type" = 'Group') AND "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."user_id" NOT IN (SELECT "namespace_bans"."user_id" FROM "namespace_bans" WHERE "namespace_bans"."namespace_id" = 1766)) AND "users"."user_type" IN (0, 4, 5) AND "users"."id" = 775 LIMIT 1

# invited to project
User Exists? (1.0ms)  SELECT 1 AS one FROM "users" WHERE "users"."id" IN (SELECT "members"."user_id" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "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"."source_id" IN (WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids" FROM "namespaces" INNER JOIN "project_group_links" ON "project_group_links"."group_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "project_group_links"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{1766}'))))) SELECT "namespaces"."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 WHERE "namespaces"."type" = 'Group') AND "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."user_id" NOT IN (SELECT "namespace_bans"."user_id" FROM "namespace_bans" WHERE "namespace_bans"."namespace_id" = 1766)) AND "users"."user_type" IN (0, 4, 5) AND "users"."id" = 775 LIMIT 1

MR 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 Sep 04, 2023 by Bishwa Hang Rai

Add cleanup worker

What does this MR do and why?

Screenshots or screen recordings

How to set up and validate locally

SQL

MR acceptance checklist

Merge request reports