delete_approval_policy_rules_for_project isn't atomic, can still produce orphaned scan_result_policy_read rows

Summary

Security::Policy#delete_approval_policy_rules_for_project deletes across 5 separate, non-transactional each_batch passes, in this order: approval_project_rules → approval_merge_request_rules → scan_result_policy_violations → software_license_policies → scan_result_policy_reads.

If anything interrupts this sequence between the first and last step (job timeout, deploy-triggered termination, statement timeout), a scan_result_policy_read row survives with no corresponding approval_project_rule/approval_policy_rule. That's an orphan.

#625029 (closed) made resync self-heal from that state: Security::ScanResultPolicies::ApprovalRules::CreateService now reclaims an orphaned row on the next resync instead of raising ActiveRecord::RecordInvalid and getting silently discarded. It doesn't stop new orphans from forming.

Proposed fix

Reorder the deletes (drop scan_result_policy_reads first, or make its removal guaranteed relative to the others) or wrap the sequence so a partial failure can't leave a scan_result_policy_read behind without its owning rule.

Note

This becomes moot once #617802 finishes dropping the scan_result_policies table entirely. Only worth prioritizing if that timeline slips.

References