Change OauthAccessGrantArchiveWorker deletion strategy from soft deletion to hard deletion

Daniele Braccianirequested to merge
db/562376/enforce-access-grants-retention-policy-and-cleanup-archival-system into master

What does this MR do and why?

Change OauthAccessGrantArchiveWorker to hard delete grants

Update worker from soft deletion to hard deletion, removing dependency on the temporary oauth_access_grant_archived_records table.

  • Simplify SQL from CTE with INSERT to direct DELETE
  • Rename archive terminology to delete throughout
  • Drop oauth_access_grant_archived_records table and related files
  • Update specs to test deletion instead of archiving

Related to: #562376

References

Screenshots or screen recordings

Before After

Database

Foreign key removal

Migrate up 

>>> Executing: bin/rails db:migrate:up:main VERSION=20251210143535
DEPRECATION WARNING: `config.active_record.warn_on_records_fetched_greater_than` is deprecated and will be removed in Rails 8.0. Please subscribe to `sql.active_record` notifications and access the row count field to detect large result set sizes. (called from <main> at /Users/daniele/projects/gdk/gitlab/config/environment.rb:7)
main: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 67865
main: == 20251210143535 DropOauthAccessGrantArchivedRecords: migrating ==============
main: -- drop_table(:oauth_access_grant_archived_records, {:if_exists=>true})
main:    -> 0.0110s
main: == 20251210143535 DropOauthAccessGrantArchivedRecords: migrated (0.0377s) =====

main: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 67865
>>> Executing: bin/rails db:migrate:up:ci VERSION=20251210143535
DEPRECATION WARNING: `config.active_record.warn_on_records_fetched_greater_than` is deprecated and will be removed in Rails 8.0. Please subscribe to `sql.active_record` notifications and access the row count field to detect large result set sizes. (called from <main> at /Users/daniele/projects/gdk/gitlab/config/environment.rb:7)
ci: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 68038
ci: == 20251210143535 DropOauthAccessGrantArchivedRecords: migrating ==============
ci: -- drop_table(:oauth_access_grant_archived_records, {:if_exists=>true})
ci:    -> 0.0056s
ci: == 20251210143535 DropOauthAccessGrantArchivedRecords: migrated (0.0336s) =====

ci: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 68038

Migrate down 

>>> Executing: bin/rails db:migrate:down:main VERSION=20251210143453
DEPRECATION WARNING: `config.active_record.warn_on_records_fetched_greater_than` is deprecated and will be removed in Rails 8.0. Please subscribe to `sql.active_record` notifications and access the row count field to detect large result set sizes. (called from <main> at /Users/daniele/projects/gdk/gitlab/config/environment.rb:7)
main: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 76200
main: == 20251210143453 RemovingForeignKeyOauthAccessGrantArchivedRecords: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_access_grant_archived_records ADD CONSTRAINT fk_1a50d006fe FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0018s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE oauth_access_grant_archived_records VALIDATE CONSTRAINT fk_1a50d006fe;")
main:    -> 0.0024s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20251210143453 RemovingForeignKeyOauthAccessGrantArchivedRecords: reverted (0.0841s) 

main: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 76200
>>> Executing: bin/rails db:migrate:down:ci VERSION=20251210143453
DEPRECATION WARNING: `config.active_record.warn_on_records_fetched_greater_than` is deprecated and will be removed in Rails 8.0. Please subscribe to `sql.active_record` notifications and access the row count field to detect large result set sizes. (called from <main> at /Users/daniele/projects/gdk/gitlab/config/environment.rb:7)
ci: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 76450
ci: == 20251210143453 RemovingForeignKeyOauthAccessGrantArchivedRecords: reverting 
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_access_grant_archived_records ADD CONSTRAINT fk_1a50d006fe FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0022s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE oauth_access_grant_archived_records VALIDATE CONSTRAINT fk_1a50d006fe;")
ci:    -> 0.0035s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20251210143453 RemovingForeignKeyOauthAccessGrantArchivedRecords: reverted (0.1072s) 

ci: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 76450

Table drop

Migrate up 

>>> Executing: bin/rails db:migrate:up:main VERSION=20251205145000
DEPRECATION WARNING: `config.active_record.warn_on_records_fetched_greater_than` is deprecated and will be removed in Rails 8.0. Please subscribe to `sql.active_record` notifications and access the row count field to detect large result set sizes. (called from <main> at /Users/daniele/projects/gdk/gitlab/config/environment.rb:7)
main: == [advisory_lock_connection] object_id: 130940, pg_backend_pid: 15625
main: == 20251205145000 DropOauthAccessGrantArchivedRecords: migrating ==============
main: -- drop_table(:oauth_access_grant_archived_records)
main:    -> 0.0248s
main: == 20251205145000 DropOauthAccessGrantArchivedRecords: migrated (0.0431s) =====

main: == [advisory_lock_connection] object_id: 130940, pg_backend_pid: 15625
>>> Executing: bin/rails db:migrate:up:ci VERSION=20251205145000
DEPRECATION WARNING: `config.active_record.warn_on_records_fetched_greater_than` is deprecated and will be removed in Rails 8.0. Please subscribe to `sql.active_record` notifications and access the row count field to detect large result set sizes. (called from <main> at /Users/daniele/projects/gdk/gitlab/config/environment.rb:7)
ci: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 15703
ci: == 20251205145000 DropOauthAccessGrantArchivedRecords: migrating ==============
ci: -- drop_table(:oauth_access_grant_archived_records)
ci:    -> 0.0202s
ci: == 20251205145000 DropOauthAccessGrantArchivedRecords: migrated (0.0779s) =====

Migrate down 

>>> Executing: bin/rails db:migrate:down:main VERSION=20251210143535
DEPRECATION WARNING: `config.active_record.warn_on_records_fetched_greater_than` is deprecated and will be removed in Rails 8.0. Please subscribe to `sql.active_record` notifications and access the row count field to detect large result set sizes. (called from <main> at /Users/daniele/projects/gdk/gitlab/config/environment.rb:7)
main: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 75496
main: == 20251210143535 DropOauthAccessGrantArchivedRecords: reverting ==============
main: -- create_table(:oauth_access_grant_archived_records, {:id=>false})
main:    -> 0.0080s
main: -- execute("ALTER TABLE oauth_access_grant_archived_records ADD PRIMARY KEY (id)")
main:    -> 0.0008s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_access_grant_archived_records\nADD CONSTRAINT check_bc69cc7ce0\nCHECK ( char_length(code_challenge) <= 128 )\nNOT VALID;\n")
main:    -> 0.0006s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE oauth_access_grant_archived_records VALIDATE CONSTRAINT check_bc69cc7ce0;")
main:    -> 0.0004s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_access_grant_archived_records\nADD CONSTRAINT check_ce125f5bae\nCHECK ( char_length(code_challenge_method) <= 5 )\nNOT VALID;\n")
main:    -> 0.0003s
main: -- execute("ALTER TABLE oauth_access_grant_archived_records VALIDATE CONSTRAINT check_ce125f5bae;")
main:    -> 0.0003s
main: == 20251210143535 DropOauthAccessGrantArchivedRecords: reverted (0.0908s) =====

main: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 75496
>>> Executing: bin/rails db:migrate:down:ci VERSION=20251210143535
DEPRECATION WARNING: `config.active_record.warn_on_records_fetched_greater_than` is deprecated and will be removed in Rails 8.0. Please subscribe to `sql.active_record` notifications and access the row count field to detect large result set sizes. (called from <main> at /Users/daniele/projects/gdk/gitlab/config/environment.rb:7)
ci: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 75658
ci: == 20251210143535 DropOauthAccessGrantArchivedRecords: reverting ==============
ci: -- create_table(:oauth_access_grant_archived_records, {:id=>false})
ci:    -> 0.0062s
ci: -- execute("ALTER TABLE oauth_access_grant_archived_records ADD PRIMARY KEY (id)")
ci:    -> 0.0007s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_access_grant_archived_records\nADD CONSTRAINT check_bc69cc7ce0\nCHECK ( char_length(code_challenge) <= 128 )\nNOT VALID;\n")
ci:    -> 0.0007s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- execute("ALTER TABLE oauth_access_grant_archived_records VALIDATE CONSTRAINT check_bc69cc7ce0;")
ci:    -> 0.0004s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_access_grant_archived_records\nADD CONSTRAINT check_ce125f5bae\nCHECK ( char_length(code_challenge_method) <= 5 )\nNOT VALID;\n")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE oauth_access_grant_archived_records VALIDATE CONSTRAINT check_ce125f5bae;")
ci:    -> 0.0004s
ci: == 20251210143535 DropOauthAccessGrantArchivedRecords: reverted (0.0609s) =====

ci: == [advisory_lock_connection] object_id: 130920, pg_backend_pid: 75658

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.

Edited by Daniele Bracciani

Merge request reports