Change OauthAccessTokenArchiveWorker deletion strategy from soft deletion to hard deletion

What does this MR do and why?

Change OauthAccessTokenArchiveWorker to hard delete tokens

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

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

Related: #562373

References

• Issue: #562373
• Worker: !202767 (merged)
• Related cleanup: !213902 (merged)

Additional notes

Query plan: !213853 (comment 2936116852)
Previous query plan: !202767 (comment 2774688387)

How to set up and validate locally

Database

Drop foreign keys

Migrate up

>>> Executing: bin/rails db:migrate:up:main VERSION=20251210151422
main: == [advisory_lock_connection] object_id: 130800, pg_backend_pid: 86655
main: == 20251210151422 RemovingForeignKeyOauthAccessTokenArchivedRecords: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: == 20251210151422 RemovingForeignKeyOauthAccessTokenArchivedRecords: migrated (0.0693s) 

main: == [advisory_lock_connection] object_id: 130800, pg_backend_pid: 86655
>>> Executing: bin/rails db:migrate:up:ci VERSION=20251210151422
ci: == [advisory_lock_connection] object_id: 130800, pg_backend_pid: 86891
ci: == 20251210151422 RemovingForeignKeyOauthAccessTokenArchivedRecords: migrating 
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: == 20251210151422 RemovingForeignKeyOauthAccessTokenArchivedRecords: migrated (0.1088s) 

ci: == [advisory_lock_connection] object_id: 130800, pg_backend_pid: 86891

Migrate down

No-op, as foreign key will be recreated by the table creation migration's rollback

Drop table

Migrate up

>>> Executing: bin/rails db:migrate:up:main VERSION=20251210151528
main: == [advisory_lock_connection] object_id: 130800, pg_backend_pid: 87363
main: == 20251210151528 DropOauthAccessTokenArchivedRecords: migrating ==============
main: -- drop_table(:oauth_access_token_archived_records, {:if_exists=>true})
main:    -> 0.0038s
main: == 20251210151528 DropOauthAccessTokenArchivedRecords: migrated (0.0648s) =====

main: == [advisory_lock_connection] object_id: 130800, pg_backend_pid: 87363
>>> Executing: bin/rails db:migrate:up:ci VERSION=20251210151528
ci: == [advisory_lock_connection] object_id: 130800, pg_backend_pid: 87581
ci: == 20251210151528 DropOauthAccessTokenArchivedRecords: migrating ==============
ci: -- drop_table(:oauth_access_token_archived_records, {:if_exists=>true})
ci:    -> 0.0056s
ci: == 20251210151528 DropOauthAccessTokenArchivedRecords: migrated (0.0551s) =====

ci: == [advisory_lock_connection] object_id: 130800, pg_backend_pid: 87581

Migrate down

>>> Executing: bin/rails db:migrate:down:main VERSION=20251210151528
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: 131080, pg_backend_pid: 98971
main: == 20251210151528 DropOauthAccessTokenArchivedRecords: reverting ==============
main: -- create_table(:oauth_access_token_archived_records, {:id=>false})
main:    -> 0.0066s
main: -- execute("ALTER TABLE oauth_access_token_archived_records ADD PRIMARY KEY (id)")
main:    -> 0.0010s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_access_token_archived_records\nADD CONSTRAINT check_79b7edb4ac\nCHECK ( char_length(code_challenge) <= 128 )\nNOT VALID;\n")
main:    -> 0.0005s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE oauth_access_token_archived_records VALIDATE CONSTRAINT check_79b7edb4ac;")
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_token_archived_records\nADD CONSTRAINT check_aa3cdd90f4\nCHECK ( char_length(code_challenge_method) <= 5 )\nNOT VALID;\n")
main:    -> 0.0004s
main: -- execute("ALTER TABLE oauth_access_token_archived_records VALIDATE CONSTRAINT check_aa3cdd90f4;")
main:    -> 0.0003s
main: == 20251210151528 DropOauthAccessTokenArchivedRecords: reverted (0.0711s) =====

main: == [advisory_lock_connection] object_id: 131080, pg_backend_pid: 98971
>>> Executing: bin/rails db:migrate:down:ci VERSION=20251210151528
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: 131080, pg_backend_pid: 99157
ci: == 20251210151528 DropOauthAccessTokenArchivedRecords: reverting ==============
ci: -- create_table(:oauth_access_token_archived_records, {:id=>false})
ci:    -> 0.0052s
ci: -- execute("ALTER TABLE oauth_access_token_archived_records ADD PRIMARY KEY (id)")
ci:    -> 0.0005s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_access_token_archived_records\nADD CONSTRAINT check_79b7edb4ac\nCHECK ( char_length(code_challenge) <= 128 )\nNOT VALID;\n")
ci:    -> 0.0005s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- execute("ALTER TABLE oauth_access_token_archived_records VALIDATE CONSTRAINT check_79b7edb4ac;")
ci:    -> 0.0003s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_access_token_archived_records\nADD CONSTRAINT check_aa3cdd90f4\nCHECK ( char_length(code_challenge_method) <= 5 )\nNOT VALID;\n")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE oauth_access_token_archived_records VALIDATE CONSTRAINT check_aa3cdd90f4;")
ci:    -> 0.0004s
ci: == 20251210151528 DropOauthAccessTokenArchivedRecords: reverted (0.0953s) =====

ci: == [advisory_lock_connection] object_id: 131080, pg_backend_pid: 99157

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.