## Why are we doing this work The `scan_result_policies` table, which backs the `Security::ScanResultPolicyRead` model, has been superseded by the `approval_policy_rules` and `security_policies` tables. Application code has already been migrated to read from the new tables, and this change is currently rolled out behind the `deprecate_scan_result_policies` feature flag (see the rollout issue at https://gitlab.com/gitlab-org/gitlab/-/work_items/510282). An initial round of old code removal was completed in https://gitlab.com/gitlab-org/gitlab/-/issues/504296. Once the feature flag is fully enabled in production and removed, nothing in the application needs the `scan_result_policies` table anymore. At that point we should clean it up: remove any remaining references to it, drop the foreign keys pointing to it, and finally drop the table itself. This follows the standard multi-release process for dropping tables safely, described at https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations/#dropping-tables. ## Relevant links - Parent deprecation issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/510281 - Feature flag rollout: https://gitlab.com/gitlab-org/gitlab/-/work_items/510282 - Old code removal: https://gitlab.com/gitlab-org/gitlab/-/issues/504296 - Epic: https://gitlab.com/groups/gitlab-org/-/epics/9971 - Process docs: https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations/#dropping-tables and https://docs.gitlab.com/ee/development/database/database_dictionary/#dropping-tables ## Non-functional requirements - [ ] Documentation: none needed, this is an internal table removal with no user-facing impact - [ ] Feature flag: `deprecate_scan_result_policies` must be default-enabled and removed before this work starts - [ ] Performance: foreign key removals on `approval_merge_request_rules` and other high-traffic tables must use `with_lock_retries` to avoid locking issues - [ ] Testing: existing specs must continue to pass without the `scan_result_policies` table ## Implementation plan **Prerequisite (this work is blocked until done):** - [ ] `deprecate_scan_result_policies` is fully enabled on production and the feature flag itself is removed - [ ] All remaining reads and writes of `Security::ScanResultPolicyRead` are removed from application code **MR 1 (Release M) — remove remaining application code** - [ ] ~backend Remove the `Security::ScanResultPolicyRead` model and all remaining references to it, including associations on `MergeRequest`, `Project`, `ApprovalRuleLike`, `ComplianceManagement::Framework`, `SoftwareLicensePolicy`, `Security::ScanResultPolicyViolation`, and `Security::ApprovalPolicySource`, plus any services or workers that still write to the table, such as the scan-result-policy sync services and `Security::ScanResultPolicies::DeleteScanResultPolicyReadsWorker` - [ ] ~backend Remove remaining usages of `scan_result_policy_id`, for example in approval rule attribute copying and webhook `hook_attrs` - [ ] ~database Add the `scan_result_policy_id` columns on the five referencing tables to their models' `ignore_columns` (the columns themselves will be dropped as follow-up work, but the foreign key constraints must be removed as part of this issue) **MR 2 (Release M+1) — database migrations** - [ ] ~database Post-deployment migrations, one per foreign key, removing the five inbound foreign keys referencing `scan_result_policies(id)`: - `fk_1485c451e3` on `approval_group_rules` - `fk_approval_merge_request_rules_on_scan_result_policy_id` on `approval_merge_request_rules` - `fk_e1372c912e` on `approval_project_rules` - `fk_f53706dbdd` on `scan_result_policy_violations` - `fk_rails_e5b77d620e` on `software_license_policies` - [ ] ~database Use `with_lock_retries` for each foreign key removal, since some of these tables see high traffic - [ ] ~database Add a final post-deployment migration that drops the `scan_result_policies` table itself (its own outbound foreign keys to `projects`, `namespaces`, `security_orchestration_policy_configurations`, and `approval_policy_rules` are dropped along with the table) - [ ] ~database Move `db/docs/scan_result_policies.yml` to `db/docs/deleted_tables/` and add `removed_by_url` and `removed_in_milestone` to it **Follow-up (can be tracked as a separate issue)** - [ ] ~database Remove the now-unused `scan_result_policy_id` columns from `approval_group_rules`, `approval_merge_request_rules`, `approval_project_rules`, `scan_result_policy_violations`, and `software_license_policies` (note that `software_license_policies` has a unique index that includes `scan_result_policy_id`, which will need to be handled as part of that removal) ## Verification steps 1. After the migrations run, confirm `scan_result_policies` no longer appears in `db/structure.sql`. 1. Confirm approval policy workflows still work as expected, including MR approval rules generated from policies, policy violations, and license approval policies. 1. Search the codebase to confirm no references to `Security::ScanResultPolicyRead` remain.