MemberRoles::AssignService works with AdminRoles

What does this MR do and why?

MemberRoles::AssignService works with AdminRoles

Ensure MemberRoles::AssignService works with UserAdminRoles and other new models.

• remove read_only protection from member_role association
• share create_or_update method for both join models
• create shared_examples and test both classes in assign_service_spec

part of: &17563 (closed)

Database queries

When a join record doesn't exist

Details: https://console.postgres.ai/shared/6ce6b27c-ce9d-4cca-a841-2fb145e8140a

Plan w/ Execution:

 ModifyTable on public.user_admin_roles  (cost=0.00..0.01 rows=1 width=33) (actual time=5.546..5.548 rows=1 loops=1)
   Buffers: shared hit=48 read=2 dirtied=5 written=3
   WAL: records=5 fpi=0 bytes=396
   I/O Timings: read=0.086 write=1.756
   ->  Result  (cost=0.00..0.01 rows=1 width=33) (actual time=0.001..0.002 rows=1 loops=1)
         I/O Timings: read=0.000 write=0.000
Trigger RI_ConstraintTrigger_c_2807712261 for constraint fk_62ce6c86fd: time=3.020 calls=1
Trigger RI_ConstraintTrigger_c_2807714911 for constraint fk_d3e201cb93: time=0.615 calls=1
Settings: random_page_cost = '1.5', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '472585MB', jit = 'off'

Summary:

Time: 9.266 ms  
  - planning: 0.044 ms  
  - execution: 9.222 ms  
    - I/O read: 0.086 ms  
    - I/O write: 1.756 ms  
  
Shared buffers:  
  - hits: 48 (~384.00 KiB) from the buffer pool  
  - reads: 2 (~16.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 5 (~40.00 KiB)  
  - writes: 3 (~24.00 KiB)  
  

INSERT INTO "user_admin_roles" ("user_id", "admin_role_id", "created_at", "updated_at")
    VALUES (1614863, 1, '2025-04-21 18:34:24.455453', '2025-04-21 18:34:24.455453')
RETURNING
    "user_id";

When a join record exists

Details: https://console.postgres.ai/shared/ab2be876-acd2-42ee-8d94-645bc7d63c64

Plan w/ Execution:

 Limit  (cost=0.14..3.16 rows=1 width=33) (actual time=0.027..0.028 rows=1 loops=1)
   Buffers: shared hit=5
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using user_admin_roles_pkey on public.user_admin_roles  (cost=0.14..3.16 rows=1 width=33) (actual time=0.027..0.027 rows=1 loops=1)
         Index Cond: (user_admin_roles.user_id = 1614863)
         Buffers: shared hit=5
         I/O Timings: read=0.000 write=0.000
Settings: work_mem = '100MB', effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'

Summary:

Time: 0.402 ms  
  - planning: 0.345 ms  
  - execution: 0.057 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 5 (~40.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0  

Query:

SELECT
    "user_admin_roles".*
FROM
    "user_admin_roles"
WHERE
    "user_admin_roles"."user_id" = 1614863
LIMIT 1;

SELECT
    "admin_roles".*
FROM
    "admin_roles"
WHERE
    "admin_roles"."id" = 1
LIMIT 1;

SELECT
    "users"."id",
    "users"."email",
    "users"."encrypted_password",
    "users"."reset_password_token",
    "users"."reset_password_sent_at",
    "users"."remember_created_at",
    "users"."sign_in_count",
    "users"."current_sign_in_at",
    "users"."last_sign_in_at",
    "users"."current_sign_in_ip",
    "users"."last_sign_in_ip",
    "users"."created_at",
    "users"."updated_at",
    "users"."name",
    "users"."admin",
    "users"."projects_limit",
    "users"."failed_attempts",
    "users"."locked_at",
    "users"."username",
    "users"."can_create_group",
    "users"."can_create_team",
    "users"."state",
    "users"."color_scheme_id",
    "users"."password_expires_at",
    "users"."created_by_id",
    "users"."last_credential_check_at",
    "users"."avatar",
    "users"."confirmation_token",
    "users"."confirmed_at",
    "users"."confirmation_sent_at",
    "users"."unconfirmed_email",
    "users"."hide_no_ssh_key",
    "users"."admin_email_unsubscribed_at",
    "users"."notification_email",
    "users"."hide_no_password",
    "users"."password_automatically_set",
    "users"."encrypted_otp_secret",
    "users"."encrypted_otp_secret_iv",
    "users"."encrypted_otp_secret_salt",
    "users"."otp_required_for_login",
    "users"."otp_backup_codes",
    "users"."public_email",
    "users"."dashboard",
    "users"."project_view",
    "users"."consumed_timestep",
    "users"."layout",
    "users"."hide_project_limit",
    "users"."note",
    "users"."unlock_token",
    "users"."otp_grace_period_started_at",
    "users"."external",
    "users"."incoming_email_token",
    "users"."auditor",
    "users"."require_two_factor_authentication_from_group",
    "users"."two_factor_grace_period",
    "users"."last_activity_on",
    "users"."notified_of_own_activity",
    "users"."preferred_language",
    "users"."theme_id",
    "users"."accepted_term_id",
    "users"."feed_token",
    "users"."private_profile",
    "users"."roadmap_layout",
    "users"."include_private_contributions",
    "users"."commit_email",
    "users"."group_view",
    "users"."managing_group_id",
    "users"."first_name",
    "users"."last_name",
    "users"."static_object_token",
    "users"."role",
    "users"."user_type",
    "users"."static_object_token_encrypted",
    "users"."otp_secret_expires_at",
    "users"."onboarding_in_progress",
    "users"."color_mode_id",
    "users"."composite_identity_enforced"
FROM
    find_users_by_id (1614863) AS users
WHERE ("users"."id" IS NOT NULL)
LIMIT 1;

SELECT
    1 AS one
FROM
    "user_admin_roles"
WHERE
    "user_admin_roles"."user_id" = 1614863
    AND "user_admin_roles"."user_id" != 1614863
LIMIT 1;

UPDATE
    "user_admin_roles"
SET
    "admin_role_id" = 1,
    "updated_at" = '2025-04-21 18:36:45.510888'
WHERE
    "user_admin_roles"."user_id" = 1614863;

References

Screenshots or screen recordings

Before	After

How to set up and validate locally

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.