Skip to content

Exclude add-on purchases from active that are expiring today

Corinna Gogolok requested to merge 465546_exclude_today_from_expires_on_for_active_add_on_purchases into master

What does this MR do and why?

Part of https://gitlab.com/gitlab-org/gitlab/-/issues/465546+

Prior to this change, an add-on purchase was considered still active if it was expiring today. This doesn't align with how subscriptions are handled on the CustomersDot side where the end date is excluded from the current term.

This change aligns the end date check for active add-on purchases to exclude the end date. Additionally, it now checks the start date as well. Any add-on purchases without a start date will be considered active as long as the expires on date is less than today.

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.

Database queries

Raw SQL for GitlabSubscriptions::AddOnPurchase.active scope

before:

SELECT
    subscription_add_on_purchases.*
FROM
    subscription_add_on_purchases
WHERE
    expires_on >= '2024-09-02';

after:

SELECT
    subscription_add_on_purchases.*
FROM
    subscription_add_on_purchases
WHERE
    (
        started_at IS NULL OR
        started_at <= '2024-09-02'
    ) AND
    '2024-09-02' < expires_on;

Query plans

Database migrations

Up migrations 
main: == [advisory_lock_connection] object_id: 128260, pg_backend_pid: 49935
main: == 20240830105808 AddIndexToSubscriptionAddOnPurchasesStartedAtAndExpiresOn: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0141s
main: -- index_exists?(:subscription_add_on_purchases, [:started_at, :expires_on], {:name=>"idx_subscription_add_on_purchases_on_started_on_and_expires_on", :algorithm=>:concurrently})
main:    -> 0.0033s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:subscription_add_on_purchases, [:started_at, :expires_on], {:name=>"idx_subscription_add_on_purchases_on_started_on_and_expires_on", :algorithm=>:concurrently})
main:    -> 0.0025s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20240830105808 AddIndexToSubscriptionAddOnPurchasesStartedAtAndExpiresOn: migrated (0.0362s) 

main: == [advisory_lock_connection] object_id: 128260, pg_backend_pid: 49935
ci: == [advisory_lock_connection] object_id: 128520, pg_backend_pid: 49937
ci: == 20240830105808 AddIndexToSubscriptionAddOnPurchasesStartedAtAndExpiresOn: migrating 
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- index_exists?(:subscription_add_on_purchases, [:started_at, :expires_on], {:name=>"idx_subscription_add_on_purchases_on_started_on_and_expires_on", :algorithm=>:concurrently})
ci:    -> 0.0030s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0036s
ci: -- add_index(:subscription_add_on_purchases, [:started_at, :expires_on], {:name=>"idx_subscription_add_on_purchases_on_started_on_and_expires_on", :algorithm=>:concurrently})
ci:    -> 0.0017s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20240830105808 AddIndexToSubscriptionAddOnPurchasesStartedAtAndExpiresOn: migrated (0.0224s) 

ci: == [advisory_lock_connection] object_id: 128520, pg_backend_pid: 49937
Down migrations 
main: == [advisory_lock_connection] object_id: 127800, pg_backend_pid: 47621
main: == 20240830105808 AddIndexToSubscriptionAddOnPurchasesStartedAtAndExpiresOn: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0123s
main: -- index_exists?(:subscription_add_on_purchases, [:started_at, :expires_on], {:name=>"idx_subscription_add_on_purchases_on_started_on_and_expires_on", :algorithm=>:concurrently})
main:    -> 0.0028s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:subscription_add_on_purchases, {:name=>"idx_subscription_add_on_purchases_on_started_on_and_expires_on", :algorithm=>:concurrently, :column=>[:started_at, :expires_on]})
main:    -> 0.0033s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20240830105808 AddIndexToSubscriptionAddOnPurchasesStartedAtAndExpiresOn: reverted (0.0333s) 

main: == [advisory_lock_connection] object_id: 127800, pg_backend_pid: 47621


ci: == [advisory_lock_connection] object_id: 127800, pg_backend_pid: 48210
ci: == 20240830105808 AddIndexToSubscriptionAddOnPurchasesStartedAtAndExpiresOn: reverting 
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0132s
ci: -- index_exists?(:subscription_add_on_purchases, [:started_at, :expires_on], {:name=>"idx_subscription_add_on_purchases_on_started_on_and_expires_on", :algorithm=>:concurrently})
ci:    -> 0.0035s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:subscription_add_on_purchases, {:name=>"idx_subscription_add_on_purchases_on_started_on_and_expires_on", :algorithm=>:concurrently, :column=>[:started_at, :expires_on]})
ci:    -> 0.0034s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20240830105808 AddIndexToSubscriptionAddOnPurchasesStartedAtAndExpiresOn: reverted (0.0408s) 

ci: == [advisory_lock_connection] object_id: 127800, pg_backend_pid: 48210

How to set up and validate locally

  1. Start a new rails console in sandbox mode: bin/rails c --sandbox
  2. Create a couple of GitlabSubscriptions::AddOnPurchase with different dates for started_at and expires_on to verify the GitlabSubscriptions::AddOnPurchase.active is working as expected.
Edited by Bishwa Hang Rai

Merge request reports