Skip to content

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: Screenshot_2023-03-16_at_20.49.42

After: image

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.

Related to #395794 (closed)

Edited by Peter Leitzen

Merge request reports

Loading