Clean gitlab subscription trial date attributes

What does this MR do and why?

When we create a trial subscription, then we use the attributes trial_starts_on and trial_ends_on. The values for the attributes get sent to the API. Part of the endpoint is to set the trial start date to the other attribute start date if there is no trial start date. This can be seen here.

We have in our database some older subscriptions of the type trial that don't have trial_starts_on , and trial_ends_on set. We want to put that information to enable validations in a follow-up merge request.

The last trial subscription without one of the two attributes is from 2019 and ending in 2022, so we probably stopped creating trial subscriptions without those values. Also, we set trial_starts_on to start_date if no value is supplied. We could install the same mechanism for trial_ends_on, but as the last trial subscription with an empty trial_ends_on is from 2019, there is no need for that.

This merge request sets the trial subscription's empty trial_start_date attributes to start_date and the empty trial_ends_on attributes to end_date.

Every subscription without a trial_starts_on attribute has a start_date and every subscription without a trial_ends_on attribute has a end_date.

Overall, there are only 97 rows to update, so the migration is expected to run through quickly in less than 3 minutes.

Trial starts on

• Code
  • GitlabSubscription.where(trial: true, trial_starts_on: nil)
• SQL
  • SELECT "gitlab_subscriptions".* FROM "gitlab_subscriptions" WHERE "gitlab_subscriptions"."trial" = TRUE AND "gitlab_subscriptions"."trial_starts_on" IS NULL
• Query plan
  • https://console.postgres.ai/gitlab/gitlab-production-main/sessions/33396/commands/103006

Trial ends on

• Code
  • GitlabSubscription.where(trial: true, trial_ends_on: nil)
• SQL
  • SELECT "gitlab_subscriptions".* FROM "gitlab_subscriptions" WHERE "gitlab_subscriptions"."trial" = TRUE AND "gitlab_subscriptions"."trial_ends_on" IS NULL
• Query plan
  • https://console.postgres.ai/gitlab/gitlab-production-main/sessions/33396/commands/103007
• Amount: 1

Trial starts on OR trial ends on

• Code
  • GitlabSubscription.where(trial: true, trial_starts_on: nil).or(GitlabSubscription.where(trial: true, trial_ends_on: nil))
• SQL
  • SELECT "gitlab_subscriptions".* FROM "gitlab_subscriptions" WHERE "gitlab_subscriptions"."trial" = TRUE AND ("gitlab_subscriptions"."trial_starts_on" IS NULL OR "gitlab_subscriptions"."trial_ends_on" IS NULL)
• Query plan
  • https://console.postgres.ai/gitlab/gitlab-production-main/sessions/33396/commands/103008
• Amount: 96

Update subscription

• Code
  • subscription.update(trial_starts_on: subscription.start_date, trial_ends_on: subscription.end_date)
• SQL
  • UPDATE "gitlab_subscriptions" SET "updated_at" = '2024-11-10 22:59:23.853299', "trial_ends_on" = '2024-11-10', "trial_starts_on" = '2024-11-10' WHERE "gitlab_subscriptions"."id" = 1
• Query plan
  • https://console.postgres.ai/gitlab/gitlab-production-main/sessions/33396/commands/103009

Select trial subscription without uptime

• Code
  • GitlabSubscription.where(trial: true).where("trial_starts_on = trial_ends_on")
• SQL
  • SELECT "gitlab_subscriptions".* FROM "gitlab_subscriptions" WHERE "gitlab_subscriptions"."trial" = TRUE AND (trial_starts_on = trial_ends_on)
• Query plan
  • https://console.postgres.ai/gitlab/gitlab-production-main/sessions/33438/commands/103098

Migrate Up

➜  gitlab git:(lw/341085-gitlab-subscriptions-cleanup-trial-date-attributes) rails db:migrate:main STEP=3 
DEPRECATION WARNING: Support for Rails versions < 7.1 is deprecated and will be removed from ViewComponent 4.0.0 (ViewComponent v4 will remove support for Rails versions < 7.1 no earlier than April 1, 2025) (called from <main> at /home/lukas/projects/gitlab-development-kit/gitlab/config/environment.rb:7)
main: == [advisory_lock_connection] object_id: 129720, pg_backend_pid: 74888
main: == 20241111084209 AddTemporaryIndicesForGitlabSubscriptionsCleanup: migrating =
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0393s
main: -- index_exists?(:gitlab_subscriptions, :id, {:where=>"trial = TRUE and (trial_starts_on IS NULL or trial_ends_on IS NULL)", :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_dates_null", :algorithm=>:concurrently})
main:    -> 0.0120s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0015s
main: -- add_index(:gitlab_subscriptions, :id, {:where=>"trial = TRUE and (trial_starts_on IS NULL or trial_ends_on IS NULL)", :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_dates_null", :algorithm=>:concurrently})
main:    -> 0.0058s
main: -- execute("RESET statement_timeout")
main:    -> 0.0016s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0018s
main: -- index_exists?(:gitlab_subscriptions, :id, {:where=>"trial = TRUE AND trial_starts_on = trial_ends_on", :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_start_eq_end", :algorithm=>:concurrently})
main:    -> 0.0114s
main: -- add_index(:gitlab_subscriptions, :id, {:where=>"trial = TRUE AND trial_starts_on = trial_ends_on", :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_start_eq_end", :algorithm=>:concurrently})
main:    -> 0.0041s
main: == 20241111084209 AddTemporaryIndicesForGitlabSubscriptionsCleanup: migrated (0.1490s) 

main: == 20241111092153 CleanTrialGitlabSubscriptionsDateAttributes: migrating ======
main: -- execute("UPDATE gitlab_subscriptions\n  SET\n    trial_starts_on = COALESCE(trial_starts_on, start_date),\n    trial_ends_on = COALESCE(trial_ends_on, end_date)\n  WHERE trial = true\n    AND (trial_starts_on IS NULL OR trial_ends_on IS NULL)\n")
main:    -> 0.0025s
main: -- execute("UPDATE gitlab_subscriptions\n  SET\n    trial_ends_on = trial_ends_on + 1,\n    end_date = end_date + 1\n  WHERE trial = true\n    AND trial_starts_on = trial_ends_on\n")
main:    -> 0.0019s
main: == 20241111092153 CleanTrialGitlabSubscriptionsDateAttributes: migrated (0.0115s) 

main: == 20241111104411 RemoveTemporaryIndicesForGitlabSubscriptionsCleanup: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0017s
main: -- indexes(:gitlab_subscriptions)
main:    -> 0.0104s
main: -- remove_index(:gitlab_subscriptions, {:algorithm=>:concurrently, :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_dates_null"})
main:    -> 0.0028s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0016s
main: -- indexes(:gitlab_subscriptions)
main:    -> 0.0105s
main: -- remove_index(:gitlab_subscriptions, {:algorithm=>:concurrently, :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_start_eq_end"})
main:    -> 0.0024s
main: == 20241111104411 RemoveTemporaryIndicesForGitlabSubscriptionsCleanup: migrated (0.0688s) 

main: == [advisory_lock_connection] object_id: 129720, pg_backend_pid: 74888

Migration Down

➜  gitlab git:(lw/341085-gitlab-subscriptions-cleanup-trial-date-attributes) ✗ rails db:rollback:main STEP=3
DEPRECATION WARNING: Support for Rails versions < 7.1 is deprecated and will be removed from ViewComponent 4.0.0 (ViewComponent v4 will remove support for Rails versions < 7.1 no earlier than April 1, 2025) (called from <main> at /home/lukas/projects/gitlab-development-kit/gitlab/config/environment.rb:7)
main: == [advisory_lock_connection] object_id: 129460, pg_backend_pid: 76239
main: == 20241111104411 RemoveTemporaryIndicesForGitlabSubscriptionsCleanup: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0310s
main: -- index_exists?(:gitlab_subscriptions, :id, {:where=>"trial = TRUE and (trial_starts_on IS NULL or trial_ends_on IS NULL)", :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_dates_null", :algorithm=>:concurrently})
main:    -> 0.0128s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0014s
main: -- add_index(:gitlab_subscriptions, :id, {:where=>"trial = TRUE and (trial_starts_on IS NULL or trial_ends_on IS NULL)", :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_dates_null", :algorithm=>:concurrently})
main:    -> 0.0076s
main: -- execute("RESET statement_timeout")
main:    -> 0.0017s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0017s
main: -- index_exists?(:gitlab_subscriptions, :id, {:where=>"trial = TRUE AND trial_starts_on = trial_ends_on", :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_start_eq_end", :algorithm=>:concurrently})
main:    -> 0.0114s
main: -- add_index(:gitlab_subscriptions, :id, {:where=>"trial = TRUE AND trial_starts_on = trial_ends_on", :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_start_eq_end", :algorithm=>:concurrently})
main:    -> 0.0041s
main: == 20241111104411 RemoveTemporaryIndicesForGitlabSubscriptionsCleanup: reverted (0.1456s) 

main: == 20241111092153 CleanTrialGitlabSubscriptionsDateAttributes: reverting ======
main: == 20241111092153 CleanTrialGitlabSubscriptionsDateAttributes: reverted (0.0119s) 

main: == 20241111084209 AddTemporaryIndicesForGitlabSubscriptionsCleanup: reverting =
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0016s
main: -- indexes(:gitlab_subscriptions)
main:    -> 0.0168s
main: -- remove_index(:gitlab_subscriptions, {:algorithm=>:concurrently, :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_dates_null"})
main:    -> 0.0036s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0029s
main: -- indexes(:gitlab_subscriptions)
main:    -> 0.0118s
main: -- remove_index(:gitlab_subscriptions, {:algorithm=>:concurrently, :name=>"tmp_index_gitlab_subscriptions_on_id_where_trial_start_eq_end"})
main:    -> 0.0037s
main: == 20241111084209 AddTemporaryIndicesForGitlabSubscriptionsCleanup: reverted (0.0969s) 

main: == [advisory_lock_connection] object_id: 129460, pg_backend_pid: 76239

trial_starts_on equals trial_ends_on

There is one record in which trial_ends_on is equal to trial_starts_on from 2021. I suggest correcting it by setting end_date and trial_ends_on to 2021-09-04. Increasing the duration afterward should not result in any billing problems, as trials are for free.

[ gprd ] production> ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM gitlab_subscriptions WHERE trial=TRUE AND trial_starts_on >= trial_ends_on").first
=> {"count"=>1}
[ gprd ] production> ActiveRecord::Base.connection.execute("SELECT * FROM gitlab_subscriptions WHERE trial=TRUE AND trial_starts_on >= trial_ends_on").first
=> 
{"id"=>6019645,
 "created_at"=>2021-09-03 14:35:15.032508 +0000,
 "updated_at"=>2021-09-03 14:39:40.852891 +0000,
 "start_date"=>"2021-09-03",
 "end_date"=>"2021-09-03",
 "trial_ends_on"=>"2021-09-03",
 "namespace_id"=>13247415,
 "hosted_plan_id"=>101,
 "max_seats_used"=>0,
 "seats"=>0,
 "trial"=>true,
 "trial_starts_on"=>"2021-09-03",
 "auto_renew"=>nil,
 "seats_in_use"=>0,
 "seats_owed"=>0,
 "trial_extension_type"=>nil,
 "max_seats_used_changed_at"=>nil,
 "last_seat_refresh_at"=>nil}

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

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

Before	After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

changed milestone to %17.6

assigned to @lwanko

added pipelinetier-1 label

added database databasereview pending labels

	4 Warnings
	This MR has a Changelog commit for EE, but no code changes in ee/. Consider removing the EE: true trailer from your commits.
	05e188c1: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	New migrations added but db/structure.sql wasn't updated Usually, when adding new migrations, db/structure.sql should be updated too (unless the migration isn't changing the DB schema and isn't the most recent one).
	The master pipeline status page reported failures in rspec-ee integration pg14 single-db-ci-connection 8/9 rspec-ee integration pg14 single-db 8/9 If these jobs fail in your merge request with the same errors, then they are not caused by your changes. Please check for any on-going incidents in the incident issue tracker or in the #master-broken Slack channel.

Reviewer roulette

Category	Reviewer	Maintainer
database	@dskim_gitlab (UTC+11, 10 hours ahead of author)	@tigerwnz (UTC+13, 12 hours ahead of author)

Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

Proper labels assigned to this merge request. Please ignore me.

@lwanko - please see the following guidance and update this merge request.

	1 Error
	Please add typebug typefeature, or typemaintenance label to this merge request.

added groupprovision label

added devopsfulfillment sectionfulfillment labels

changed the description

added 1 commit

• 485d45b3 - Clean gitlab subscription trial date attributes

Compare with previous version

changed the description

changed the description

marked this merge request as ready

changed the description

added 5148 commits

• 485d45b3...4470379d - 5147 commits from branch master
• a51e58a5 - Cleanup trial gitlab subscription date attributes

Compare with previous version