Skip to content

Add organization_users table

Abdul Wadood requested to merge 415418-user-access-org into master

What does this MR do and why?

This table represents users with access to an organization.

The user_id foreign key is added in a separate migration as per our database guideline https://docs.gitlab.com/ee/development/migration_style_guide.html#creating-a-new-table-when-we-have-two-foreign-keys.

Migration output

UP 
bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 228780, pg_backend_pid: 22629
main: == 20230608113106 CreateOrganizationUsers: migrating ==========================
main: -- create_table(:organization_users)
main:    -> 0.0142s
main: == 20230608113106 CreateOrganizationUsers: migrated (0.1686s) =================

main: == 20230615104902 AddUserIdForeignKeyToOrganizationUsers: migrating ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE organization_users ADD CONSTRAINT fk_8d9b20725d FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0026s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- execute("ALTER TABLE organization_users VALIDATE CONSTRAINT fk_8d9b20725d;")
main:    -> 0.0024s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20230615104902 AddUserIdForeignKeyToOrganizationUsers: migrated (0.0378s) ==

main: == 20230620104217 AddOrganizationIdForeignKeyToOrganizationUsers: migrating ===
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE organization_users ADD CONSTRAINT fk_8471abad75 FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0013s
main: -- execute("ALTER TABLE organization_users VALIDATE CONSTRAINT fk_8471abad75;")
main:    -> 0.0008s
main: == 20230620104217 AddOrganizationIdForeignKeyToOrganizationUsers: migrated (0.0220s)

main: == [advisory_lock_connection] object_id: 228780, pg_backend_pid: 22629
ci: == [advisory_lock_connection] object_id: 229260, pg_backend_pid: 22631
ci: == 20230608113106 CreateOrganizationUsers: migrating ==========================
ci: -- create_table(:organization_users)
ci:    -> 0.0109s
ci: == 20230608113106 CreateOrganizationUsers: migrated (0.0224s) =================

ci: == 20230615104902 AddUserIdForeignKeyToOrganizationUsers: migrating ===========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE organization_users ADD CONSTRAINT fk_8d9b20725d FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0022s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE organization_users VALIDATE CONSTRAINT fk_8d9b20725d;")
ci:    -> 0.0106s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: == 20230615104902 AddUserIdForeignKeyToOrganizationUsers: migrated (0.0400s) ==

ci: == 20230620104217 AddOrganizationIdForeignKeyToOrganizationUsers: migrating ===
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE organization_users ADD CONSTRAINT fk_8471abad75 FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0008s
ci: -- execute("ALTER TABLE organization_users VALIDATE CONSTRAINT fk_8471abad75;")
ci:    -> 0.0007s
ci: == 20230620104217 AddOrganizationIdForeignKeyToOrganizationUsers: migrated (0.0390s)

ci: == [advisory_lock_connection] object_id: 229260, pg_backend_pid: 22631
DOWN 
(arm64) ➜  gitlab git:(415418-user-access-org) bin/rails db:migrate:down:main VERSION=20230620104217 && bin/rails db:migrate:down:ci VERSION=20230620104217
main: == [advisory_lock_connection] object_id: 228500, pg_backend_pid: 26865
main: == 20230620104217 AddOrganizationIdForeignKeyToOrganizationUsers: reverting ===
main: -- transaction_open?()
main:    -> 0.0001s
main: -- remove_foreign_key(:organization_users, {:column=>:organization_id})
main:    -> 0.0896s
main: == 20230620104217 AddOrganizationIdForeignKeyToOrganizationUsers: reverted (0.1370s)

main: == [advisory_lock_connection] object_id: 228500, pg_backend_pid: 26865
ci: == [advisory_lock_connection] object_id: 228440, pg_backend_pid: 27302
ci: == 20230620104217 AddOrganizationIdForeignKeyToOrganizationUsers: reverting ===
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_foreign_key(:organization_users, {:column=>:organization_id})
ci:    -> 0.1240s
ci: == 20230620104217 AddOrganizationIdForeignKeyToOrganizationUsers: reverted (0.1548s)

ci: == [advisory_lock_connection] object_id: 228440, pg_backend_pid: 27302
(arm64) ➜  gitlab git:(415418-user-access-org) ✗ bin/rails db:migrate:down:main VERSION=20230615104902 && bin/rails db:migrate:down:ci VERSION=20230615104902
main: == [advisory_lock_connection] object_id: 228460, pg_backend_pid: 27731
main: == 20230615104902 AddUserIdForeignKeyToOrganizationUsers: reverting ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:organization_users, {:column=>:user_id})
main:    -> 0.1187s
main: == 20230615104902 AddUserIdForeignKeyToOrganizationUsers: reverted (0.1480s) ==

main: == [advisory_lock_connection] object_id: 228460, pg_backend_pid: 27731
ci: == [advisory_lock_connection] object_id: 228460, pg_backend_pid: 28146
ci: == 20230615104902 AddUserIdForeignKeyToOrganizationUsers: reverting ===========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_foreign_key(:organization_users, {:column=>:user_id})
ci:    -> 0.1333s
ci: == 20230615104902 AddUserIdForeignKeyToOrganizationUsers: reverted (0.1630s) ==

ci: == [advisory_lock_connection] object_id: 228460, pg_backend_pid: 28146
(arm64) ➜  gitlab git:(415418-user-access-org) ✗ bin/rails db:migrate:down:main VERSION=20230608113106 && bin/rails db:migrate:down:ci VERSION=20230608113106
main: == [advisory_lock_connection] object_id: 228460, pg_backend_pid: 28585
main: == 20230608113106 CreateOrganizationUsers: reverting ==========================
main: -- drop_table(:organization_users)
main:    -> 0.0029s
main: == 20230608113106 CreateOrganizationUsers: reverted (0.0075s) =================

main: == [advisory_lock_connection] object_id: 228460, pg_backend_pid: 28585
ci: == [advisory_lock_connection] object_id: 228440, pg_backend_pid: 28998
ci: == 20230608113106 CreateOrganizationUsers: reverting ==========================
ci: -- drop_table(:organization_users)
ci:    -> 0.0030s
ci: == 20230608113106 CreateOrganizationUsers: reverted (0.0142s) =================

ci: == [advisory_lock_connection] object_id: 228440, pg_backend_pid: 28998

How to set up and validate locally

  1. Run the migrations
  2. organization_user = Organizations::OrganizationUser.new(organization_id: 1, user_id: <user-id>)
  3. organization_user.save!
  4. Organizations::Organization.find(1).users
  5. User.find(<user-id>).organizations

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

Edited by Abdul Wadood

Merge request reports