Count Guests with custom roles as billable users

What does this MR do and why?

This MR changes how we count and show paid users in admin section for self-managed installation on Ultimate Plan.

Now we count Guest users with additional custom roles that elevate the guest into the billed seat.

Disclaimer: to make this change consistent in different places, I need to change how we count usage statistics. I want to do it in subsequent MR.

Screenshots or screen recordings

Before:

After:

Plans:

Before:

SQL:

SELECT
    "users".*
FROM
    "users"
WHERE ("users"."state" IN ('active'))
    AND ("users"."user_type" IS NULL
        OR "users"."user_type" IN (6, 4, 13))
    AND ("users"."user_type" IS NULL
        OR "users"."user_type" IN (4, 5))
    AND (EXISTS (
            SELECT
                1
            FROM
                "members"
            WHERE
                "members"."user_id" = "users"."id"
                AND (members.access_level > 10)))

plan

Nested Loop  (cost=1768637.65..3423761.63 rows=1193217 width=1453)
  ->  HashAggregate  (cost=1768637.21..1781719.13 rows=1308192 width=4)
        Group Key: members.user_id
        ->  Index Scan using index_members_on_access_level on members  (cost=0.56..1683124.12 rows=34205238 width=4)
              Index Cond: (access_level > 10)
  ->  Index Scan using index_users_for_active_billable_users on users  (cost=0.43..1.33 rows=1 width=1453)
        Index Cond: (id = members.user_id)

After

Without elevating guest roles in the system:

SQL:

SELECT
    "users".*
FROM
    "users"
WHERE ("users"."state" IN ('active'))
    AND ("users"."user_type" IS NULL
        OR "users"."user_type" IN (6, 4, 13))
    AND ("users"."user_type" IS NULL
        OR "users"."user_type" IN (4, 5))
    AND (EXISTS (
            SELECT
                1
            FROM ((
                    SELECT
                        "members".*
                    FROM
                        "members"
                    WHERE (members.access_level > 10))) members
        WHERE
            "members"."user_id" = "users"."id"))

plan

Nested Loop  (cost=1768637.65..3423761.63 rows=1193217 width=1453)
  ->  HashAggregate  (cost=1768637.21..1781719.13 rows=1308192 width=4)
        Group Key: members.user_id
        ->  Index Scan using index_members_on_access_level on members  (cost=0.56..1683124.12 rows=34205238 width=4)
              Index Cond: (access_level > 10)
  ->  Index Scan using index_users_for_active_billable_users on users  (cost=0.43..1.33 rows=1 width=1453)
        Index Cond: (id = members.user_id)

With elevating guest roles in the system:

SQL:

SELECT "users".* FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" IN (6, 4, 13)) AND ("users"."user_type" IS NULL OR "users"."user_type" IN (4, 5)) AND (EXISTS (SELECT 1 FROM ((SELECT "members".* FROM "members" WHERE (members.access_level > 10)) UNION (SELECT "members".* FROM "members" INNER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id" WHERE (members.access_level = 10) AND (download_code = true))) members WHERE "members"."user_id" = "users"."id"))

plan

Nested Loop  (cost=133820277.37..133820882.08 rows=6071588 width=1453)
  ->  HashAggregate  (cost=133820276.93..133820278.93 rows=200 width=4)
        Group Key: members.user_id
        ->  Unique  (cost=131469211.70..133384894.48 rows=34830596 width=2145)
              ->  Sort  (cost=131469211.70..131556288.19 rows=34830596 width=2145)
                    Sort Key: members.id, members.access_level, members.source_id, members.source_type, members.user_id, members.notification_level, members.type, members.created_at, members.updated_at, members.created_by_id, members.invite_email, members.invite_token, members.invite_accepted_at, members.requested_at, members.expires_at, members.ldap, members.override, members.invite_email_success, members.state, members.member_namespace_id, members.member_role_id
                    ->  Append  (cost=0.56..2205655.74 rows=34830596 width=2145)
                          ->  Index Scan using index_members_on_access_level on members  (cost=0.56..1683124.12 rows=34205238 width=188)
                                Index Cond: (access_level > 10)
                          ->  Nested Loop  (cost=0.56..72.68 rows=625358 width=188)
                                ->  Seq Scan on member_roles  (cost=0.00..4.38 rows=19 width=8)
                                      Filter: download_code
                                ->  Index Scan using index_members_on_member_role_id on members members_1  (cost=0.56..3.58 rows=1 width=188)
                                      Index Cond: (member_role_id = member_roles.id)
                                      Filter: (access_level = 10)
  ->  Index Scan using index_users_for_active_billable_users on users  (cost=0.43..3.45 rows=1 width=1453)
        Index Cond: (id = members.user_id)

How to set up and validate locally

1. The only permission we can use right now for testing is :download_code, so we need to comment the line 5 in the MemberRole class (the one that adds the the download_code to the ignored columns).
2. Add :download_code to the ALL_CUSTOMIZABLE_PERMISSIONS hash.
3. Add member role that has download_code attribute set to true.
4. Associate this member role with member of a group on a Guest level.
5. See the Admin > Users and verify if the guest user with elevating permission is having "Is using seat" badge.
6. To see new number in the "Admin > Dashboard" section, please run in the console Analytics::UsageTrends::CountJobTriggerWorker.new.perform - to make it even faster, it's good to change line 30 in the Analytics::UsageTrends::CountJobTriggerWorker class and put there CounterJobWorker.new.perform(*args) instead of CounterJobWorker.perform_in(perform_in, *args).

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 #395794 (closed)