Draft: Add highest access level to seat assignments table

What does this MR do and why?

• Adds a new column to seat assignments to store the highest access level of a user to safe traversal time
• Stores the highest access level when a user gets added to a group and a seat assignment gets created
• Ensures seat assignments get updated when the highest access level changes

The next task I will work on is a merge request that updates the seat type of the seat assignments based on the highest access level we have just added here.

References

Contributes to https://gitlab.com/gitlab-org/gitlab/-/issues/552136

Migration

Up

➜  gitlab git:(lw/552136-add-highest-access-level-on-members-added-event) ✗ rails db:migrate:up:main db:migrate:up:ci VERSION=20250721111034    
main: == [advisory_lock_connection] object_id: 139180, pg_backend_pid: 136555
main: == 20250721111034 AddHighestAccessLevelToSeatAssignments: migrating ===========
main: -- add_column(:subscription_seat_assignments, :highest_access_level, :integer, {:null=>true})
main:    -> 0.0137s
main: == 20250721111034 AddHighestAccessLevelToSeatAssignments: migrated (0.0224s) ==

main: == [advisory_lock_connection] object_id: 139180, pg_backend_pid: 136555
ci: == [advisory_lock_connection] object_id: 139180, pg_backend_pid: 136607
ci: == 20250721111034 AddHighestAccessLevelToSeatAssignments: migrating ===========
ci: -- add_column(:subscription_seat_assignments, :highest_access_level, :integer, {:null=>true})
ci:    -> 0.0043s
ci: == 20250721111034 AddHighestAccessLevelToSeatAssignments: migrated (0.0160s) ==

ci: == [advisory_lock_connection] object_id: 139180, pg_backend_pid: 136607

Down

➜  gitlab git:(lw/552136-add-highest-access-level-on-members-added-event) ✗ rails db:migrate:down:main db:migrate:down:ci VERSION=20250721111034
main: == [advisory_lock_connection] object_id: 139200, pg_backend_pid: 136014
main: == 20250721111034 AddHighestAccessLevelToSeatAssignments: reverting ===========
main: -- remove_column(:subscription_seat_assignments, :highest_access_level, :integer, {:null=>true})
main:    -> 0.0589s
main: == 20250721111034 AddHighestAccessLevelToSeatAssignments: reverted (0.1336s) ==

main: == [advisory_lock_connection] object_id: 139200, pg_backend_pid: 136014
ci: == [advisory_lock_connection] object_id: 139200, pg_backend_pid: 136122
ci: == 20250721111034 AddHighestAccessLevelToSeatAssignments: reverting ===========
ci: -- remove_column(:subscription_seat_assignments, :highest_access_level, :integer, {:null=>true})
ci:    -> 0.0285s
ci: == 20250721111034 AddHighestAccessLevelToSeatAssignments: reverted (0.1096s) ==

ci: == [advisory_lock_connection] object_id: 139200, pg_backend_pid: 136122

SQL

Query #1 (closed)

Instead of fetching only IDs, we are now batch processing users to receive their current highest access role and user ID

SELECT "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"."state", "members"."invite_email_success", "members"."member_namespace_id", "members"."member_role_id", "members"."expiry_notified_at", "members"."request_accepted_at" FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 24 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND (members.access_level > 5) AND "members"."user_id" IS NOT NULL AND "members"."user_id" IN (2147483647, 8)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41687/commands/127967

Query #2 (closed)

INSERT INTO "subscription_seat_assignments" ("highest_access_level","namespace_id","organization_id","user_id","created_at","updated_at") VALUES (40, 4, 1001, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT ("namespace_id","user_id") WHERE (namespace_id IS NOT NULL) DO UPDATE SET updated_at=(CASE WHEN ("subscription_seat_assignments"."highest_access_level" IS NOT DISTINCT FROM excluded."highest_access_level" AND "subscription_seat_assignments"."organization_id" IS NOT DISTINCT FROM excluded."organization_id") THEN "subscription_seat_assignments".updated_at ELSE CURRENT_TIMESTAMP END),"highest_access_level"=excluded."highest_access_level","organization_id"=excluded."organization_id" RETURNING "id"

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41687/commands/127974

I had to adapt the query for the above query plan to the following to be able to execute it in postgres.ai

• highest_access_level removed
• Sub-selects for group, organization and user ID

INSERT INTO "subscription_seat_assignments" ("namespace_id","organization_id","user_id","created_at","updated_at") VALUES ((SELECT id FROM namespaces WHERE type = 'Group' LIMIT 1), (SELECT id FROM organizations LIMIT 1), (SELECT id FROM users LIMIT 1), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT ("namespace_id","user_id") WHERE (namespace_id IS NOT NULL) DO UPDATE SET updated_at=(CASE WHEN ("subscription_seat_assignments"."organization_id" IS NOT DISTINCT FROM excluded."organization_id") THEN "subscription_seat_assignments".updated_at ELSE CURRENT_TIMESTAMP END),"organization_id"=excluded."organization_id" RETURNING "id"

Screenshots or screen recordings

Before	After

How to set up and validate locally

1. Create a parent group and two child groups
2. Add a user to one child group with Developer
3. Check the last GitlabSubscriptions::SeatAssignment.last for the highest access level developer
4. Add the user to the other child group with Maintainer
5. Ensure the same seat assignment got updated and the highest access level changed to maintainer

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.