Add field query.addOnPurchase

What does this MR do and why?

Add new query Query.addOnPurchase

This new query has accepts add_on_type and returns active add_on_purchase for it, with namespace_id: nil by default. This is used in the context of Self-Managed(SM) instance to fetch the AddOnPurchase information for a given add-on type. Namespace does not exists on AddOnPurchase record when in SM.

Optionally, you can also pass namespace_id. Then the query returns active add_on_purchase with the add_on_type, belonging to that namespace. Namespace is required for AddOnPurchase in the context of SaaS , and this query can also be used for that purpose. We will discuss with frontend to do the future migration from Query.namespace.addOnPurchase to this new query.

The query is agnostic of SaaS and SM, and returns correct add_on_purchase record based on the given arguments, when called with correct authorization.

Screenshots or screen recordings

query with add_on_type only on SM context

How to set up and validate locally

Setting up the seed records

1. Make sure that GDK is run as self-managed instance, i.e., GITLAB_SIMULATE_SAAS=0
2. Open up a rails console GITLAB_SIMULATE_SAAS=0 gdk rails c
3. Create a new AddOnPurchase record for testing:

# cleanup any existing old test records
::GitlabSubscriptions::AddOnPurchase.destroy_all
# create a add_on record if not already created
add_on = GitlabSubscriptions::AddOn.find_or_create_by!(name: "code_suggestions") {|e| e.description = "Test"}
# create add_on_purchase record without the namespace, mimicking SM instance
GitlabSubscriptions::AddOnPurchase.create!(add_on: add_on, expires_on: 1.year.from_now, quantity: 10, purchase_xid: 'A-12345')

Testing in GraphQL

{
  addOnPurchase(addOnType: CODE_SUGGESTIONS) {
    id
    assignedQuantity
    purchasedQuantity
    name
  }
}

1. Go to http://gdk.test:3000/-/graphql-explorer, logged in as admin
2. Enter the query above, it should return correct response
3. Log out as admin, and log in as owner of some group
4. Hard reset (CMD + SHIFT + R) the http://gdk.test:3000/-/graphql-explorer page to set correct cookies for newly logged user.
5. The query should return null as owner doesn't have access.

Testing with optional argument namespace_id

1. Make sure that GDK is run as SaaS instance, i.e., GITLAB_SIMULATE_SAAS=1 gdk restart
2. Create a new root group namespace
3. Open up a rails console GITLAB_SIMULATE_SAAS=1 gdk rails c
4. Create a new AddOnPurchase record for testing:

# cleanup any existing old test records
::GitlabSubscriptions::AddOnPurchase.destroy_all
# create add_on record if not already created
add_on = GitlabSubscriptions::AddOn.find_or_create_by!(name: "code_suggestions") {|e| e.description = "Test"}
namespace = Namespace.last # created in step 2
# create a add_on_purchase record with namespace, mimicking SaaS instance
add_on_purchase = GitlabSubscriptions::AddOnPurchase.create!(
  add_on: add_on, namespace: namespace, expires_on: 1.month.from_now, quantity: 20, purchase_xid: 'A-S0001'
)

5. Go to graphql explorer: http://gdk.test:3000/-/graphql-explorer, and use following query and variables:

{
  addOnPurchase(addOnType: CODE_SUGGESTIONS, namespaceId: "gid://gitlab/Group/<NAMESPACE_ID>") {
    id
    name
    assignedQuantity
    purchasedQuantity
  }
}

6. It should return correct response.

SQL

The SQL query:

SELECT
    "subscription_add_on_purchases".*
FROM
    "subscription_add_on_purchases"
    INNER JOIN "subscription_add_ons" "add_on" ON "add_on"."id" = "subscription_add_on_purchases"."subscription_add_on_id"
WHERE (expires_on >= '2023-11-23')
AND "add_on"."name" = 1
AND "subscription_add_on_purchases"."namespace_id" IS NULL
ORDER BY
    "subscription_add_on_purchases"."id" ASC
LIMIT 1

# with namespace_id not null
SELECT
    "subscription_add_on_purchases".*
FROM
    "subscription_add_on_purchases"
    INNER JOIN "subscription_add_ons" "add_on" ON "add_on"."id" = "subscription_add_on_purchases"."subscription_add_on_id"
WHERE (expires_on >= '2023-11-23')
AND "add_on"."name" = 1
AND "subscription_add_on_purchases"."namespace_id" = 243
ORDER BY
    "subscription_add_on_purchases"."id" ASC
LIMIT 1

Query plan on Joe instance: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24245/commands/77427

Query plan locally: https://explain.depesz.com/s/Wy5t

Note: There will only be one add_on_purchase record per add_on. And, there will be only one add_on called "Code Suggestions" for now.

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.

Related to #432209