Added cleanup worker for expired GitlabSubscriptions::AddOnPurchases

What does this MR do and why?

The changes introduce a new cron job to clean up expired add-on purchases for GitLab subscriptions. This job runs every day at 1 AM and removes expired add-on purchases. Additionally, a new scope, .expired, is added to the AddOnPurchase model to retrieve all expired purchases easily. The cleanup worker logs information about each deleted purchase, including the add-on name, namespace, and a message indicating that the purchase was deleted via a scheduled cron job.

Queries

Ruby on Rails	Raw SQL	Query plan
GitlabSubscriptions::AddOnPurchase.expired	SELECT * FROM subscription_add_on_purchases WHERE expires_on < '2024-05-01';	Before adding index: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/28437/commands/88860 After adding index: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/28437/commands/88823
add_concurrent_index :subscription_add_on_purchases, :expires_on, name: INDEX_NAME	CREATE INDEX CONCURRENTLY "index_subscription_addon_purchases_on_expires_on" ON "subscription_add_on_purchases" ("expires_on")	-
remove_concurrent_index :subscription_add_on_purchases, :expires_on, name: INDEX_NAME	DROP INDEX CONCURRENTLY "index_subscription_addon_purchases_on_expires_on"	-
add_on_purchase.destroy!	DELETE FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."id" = 5;	https://console.postgres.ai/gitlab/gitlab-production-main/sessions/28437/commands/88865

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screencast

How to set up and validate locally

Create two namespaces

Create first group

1. Login into your local SaaS GitLab installation under http://localhost:3000 as non admin user
2. Click on Groups -> New group -> Create group
3. Enter name, for example test-mr-153713-1
4. Click Create group

Create second group

1. Go back to http://localhost:3000
2. Click on Groups -> New group -> Create group
3. Enter name, for example test-mr-153713-2
4. Click Create group

Create add-on purchases

1. As synchronizing add-on purchases between CustomersDot and GitLab seems to be complex, we create now manually two entries of Gitlab::Subscriptions::AddOnPurchases in the local GitLab installation
2. Paste the following two create statements into the console. One will create an expired purchase, and the other one will create an active purchase.

GitlabSubscriptions::AddOnPurchase.create!(
  expires_on: 15.days.ago,
  last_assigned_users_refreshed_at: Time.zone.now,
  namespace: Namespace.second_to_last,
  purchase_xid: "123456789",
  quantity: 1,
  subscription_add_on_id: GitlabSubscriptions::AddOn.find_by(name: "code_suggestions").id,
  trial: false
)

GitlabSubscriptions::AddOnPurchase.create!(
  expires_on: 1.day.from_now,
  last_assigned_users_refreshed_at: Time.zone.now,
  namespace: Namespace.last,
  purchase_xid: "123456789",
  quantity: 1,
  subscription_add_on_id: GitlabSubscriptions::AddOn.find_by(name: "code_suggestions").id,
  trial: false
)

Execute the worker

1. Login into your local GitLab installation as admin
2. Go to http://localhost:3000/admin/background_jobs -> Cron
3. Click on Enqueue now for gitlab_subscriptions_add_on_purchases_cleanup_worker

Validate functionality

1. Check via console that the expired GitlabSubscriptions::AddOnPurchase has been deleted

Clean up

1. Don't forget to clean up the second purchase with GitlabSubscriptions::AddOnPurchase.last.destroy!

Migration

Output

For rails db:migrate

➜  gitlab git:(461333-confidential-issue) ✗ bundle exec rails db:migrate      
main: == [advisory_lock_connection] object_id: 124980, pg_backend_pid: 1327817
main: == 20240521145938 AddIndexToSubscriptionAddOnPurchasesExpiresOn: migrating ====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0030s
main: -- index_exists?(:subscription_add_on_purchases, :expires_on, {:name=>"index_subscription_addon_purchases_on_expires_on", :algorithm=>:concurrently})
main:    -> 0.0028s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:subscription_add_on_purchases, :expires_on, {:name=>"index_subscription_addon_purchases_on_expires_on", :algorithm=>:concurrently})
main:    -> 0.0009s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240521145938 AddIndexToSubscriptionAddOnPurchasesExpiresOn: migrated (0.0181s) 

main: == [advisory_lock_connection] object_id: 124980, pg_backend_pid: 1327817

ci: == [advisory_lock_connection] object_id: 125220, pg_backend_pid: 1327819
ci: == 20240521145938 AddIndexToSubscriptionAddOnPurchasesExpiresOn: migrating ====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0008s
ci: -- index_exists?(:subscription_add_on_purchases, :expires_on, {:name=>"index_subscription_addon_purchases_on_expires_on", :algorithm=>:concurrently})
ci:    -> 0.0027s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:subscription_add_on_purchases, :expires_on, {:name=>"index_subscription_addon_purchases_on_expires_on", :algorithm=>:concurrently})
ci:    -> 0.0102s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20240521145938 AddIndexToSubscriptionAddOnPurchasesExpiresOn: migrated (0.0265s) 

ci: == [advisory_lock_connection] object_id: 125220, pg_backend_pid: 1327819

For bundle exec rails db:rollback:main db:rollback:ci

➜  gitlab git:(461333-confidential-issue) ✗ bundle exec rails db:rollback:main
main: == [advisory_lock_connection] object_id: 124640, pg_backend_pid: 1329633
main: == 20240521145938 AddIndexToSubscriptionAddOnPurchasesExpiresOn: reverting ====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0033s
main: -- index_exists?(:subscription_add_on_purchases, :expires_on, {:name=>"index_subscription_addon_purchases_on_expires_on", :algorithm=>:concurrently})
main:    -> 0.0038s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:subscription_add_on_purchases, {:name=>"index_subscription_addon_purchases_on_expires_on", :algorithm=>:concurrently, :column=>:expires_on})
main:    -> 0.0033s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20240521145938 AddIndexToSubscriptionAddOnPurchasesExpiresOn: reverted (0.0217s) 

main: == [advisory_lock_connection] object_id: 124640, pg_backend_pid: 1329633

ci: == [advisory_lock_connection] object_id: 141840, pg_backend_pid: 1329936
ci: == 20240521145938 AddIndexToSubscriptionAddOnPurchasesExpiresOn: reverting ====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0009s
ci: -- index_exists?(:subscription_add_on_purchases, :expires_on, {:name=>"index_subscription_addon_purchases_on_expires_on", :algorithm=>:concurrently})
ci:    -> 0.0057s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0006s
ci: -- remove_index(:subscription_add_on_purchases, {:name=>"index_subscription_addon_purchases_on_expires_on", :algorithm=>:concurrently, :column=>:expires_on})
ci:    -> 0.0061s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20240521145938 AddIndexToSubscriptionAddOnPurchasesExpiresOn: reverted (0.0317s) 

ci: == [advisory_lock_connection] object_id: 141840, pg_backend_pid: 1329936

Performance

Right now the amount of entities of GitlabSubscriptions::AddOnPurchase on production is 1010 and the amount of entities of GitlabSubscriptions::UserAddOnAssigments is 13623.

We are adding an index as part of this merge request to speed up the selection of expired GitlabSubscriptions::AddOnPurchases from the subscription_add_on_purchases table. When an expired GitlabSubscriptions::AddOnPurchases gets deleted, the related GitlabSubscriptions::UserAddOnAssignments gets deleted as well. The GitlabSubscriptions::UserAddOnAssignments in the table subscription_user_add_on_assignments already have an index on the foreign key add_on_purchase_id and the primary key id.

The migration for adding the index extends a table with 1010 entities, so its runtime is expected to be very short.

Note

Probably because of low cardinality I locally had to turn of seqscan for PostgreSQL to make sure index scan get used:

gitlabhq_development=# SET enable_seqscan = off;
EXPLAIN SELECT * FROM subscription_add_on_purchases WHERE expires_on > '2024-05-01';
SET
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_subscription_addon_purchases_on_expires_on on subscription_add_on_purchases  (cost=0.12..2.14 rows=1 width=69)
   Index Cond: (expires_on > '2024-05-01'::date)
(2 rows)

gitlabhq_development=# SET enable_seqscan = on;                                                        
EXPLAIN SELECT * FROM subscription_add_on_purchases WHERE expires_on > '2024-05-01';
SET
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Seq Scan on subscription_add_on_purchases  (cost=0.00..1.01 rows=1 width=69)
   Filter: (expires_on > '2024-05-01'::date)
(2 rows)

Related to #461333