Delete orphaned running scan result policy violations

What does this MR do and why?

A scan_result_policy_violations row for an approval rule linked only to a scan_result_policy_read gets a NULL approval_policy_rule_id. The delete path in Security::SecurityOrchestrationPolicies::UpdateViolationsService filters on that column, so these rows are never reached. They stay running forever and block their merge request on "All policy rules must be satisfied" with a spinner that never resolves.

!250868 (merged) fixes the delete path so new rows stop getting stuck. It cannot clear existing ones: nothing re-evaluates a merge request that gets no new pipeline.

This MR adds a post-deployment migration that deletes rows with no verdict: status running, approval_policy_rule_id NULL, violation_data NULL, and updated_at older than a day. The guard is on updated_at because every new pipeline re-upserts the row back to running without touching created_at. Batched with each_batch(of: 500) and delete_all. down is a no-op.

The approval rule is untouched, so the policy's required approvals still apply and the next evaluation writes a real verdict.

A batched background migration isn't warranted: db/docs/scan_result_policy_violations.yml marks the table table_size: small, and the affected set is a few hundred rows on GitLab.com.

Database: batched DELETEs on scan_result_policy_violations, restrict_gitlab_migration gitlab_schema: :gitlab_main_org and disable_ddl_transaction!. No schema change, no new index. The WHERE clause is served by the existing btree index index_scan_result_policy_violations_on_approval_policy_rule_id (approval_policy_rule_id IS NULL).

Queries and production plans (postgresai, gitlab-production-main)

each_batch(of: 500) issues, per batch, a boundary query and a DELETE (timestamps and ids are placeholders):

-- first id
SELECT id FROM scan_result_policy_violations
WHERE status = 0 AND approval_policy_rule_id IS NULL AND violation_data IS NULL
  AND updated_at < <now - 1 day>
ORDER BY id ASC LIMIT 1;

-- batch boundary
SELECT id FROM scan_result_policy_violations
WHERE status = 0 AND approval_policy_rule_id IS NULL AND violation_data IS NULL
  AND updated_at < <now - 1 day> AND id >= <min_id>
ORDER BY id ASC LIMIT 1 OFFSET 500;

-- delete
DELETE FROM scan_result_policy_violations
WHERE status = 0 AND approval_policy_rule_id IS NULL AND violation_data IS NULL
  AND updated_at < <now - 1 day> AND id >= <min_id> AND id < <next_id>;

Plans from EXPLAIN (ANALYZE, BUFFERS) on a production-size thin clone. 182 rows match on the clone; about 9.7k rows have a NULL approval_policy_rule_id, and the other ~9.5k are removed by the filter.

Query	Plan	Execution	Buffers
first id	Index Scan on index_scan_result_policy_violations_on_approval_policy_rule_id (approval_policy_rule_id IS NULL), top-N sort, filter on the rest	2.3 s (cold clone: 6262 pages read from OS cache, 2.2 s of it I/O)	read=6262
batch boundary (OFFSET 500)	same index scan, quicksort of 182 rows, 0 rows returned	15 ms	hit=6266 read=4
DELETE (the single batch, 182 rows)	same index scan, then delete	15 ms; FK fk_ebf95a08ea trigger 1.7 ms for 182 calls	hit=6788 read=4 dirtied=88; WAL 182 records

The 2.3 s is the first cold read of the index range; the repeat runs hit the cache and take about 15 ms. With a few hundred matching rows this is one boundary query plus one DELETE. The DELETE plan used id >= (SELECT min(id) ...) in place of the literal, since the clone returns no row values.

Reviewable LOC: 25 (plus 101 in tests)

Context for LLM agents

• Why updated_at: a new pipeline re-upserts an existing row to running without changing created_at, so only updated_at shows an evaluation may be in flight.
• Why down is a no-op: the deleted rows carry no verdict, so there is nothing to reconstruct.
• Invariant for any future cleanup of this table: only delete verdict-less rows. Deleting a row with a real verdict unblocks a merge request that has a genuine violation.

Data deletion

Recovery

Nothing to recover. The rows are "evaluation in progress" placeholders with no verdict. The next trigger (new pipeline or policy resync) writes a fresh row if the merge request still needs evaluation.

Records affected

A few hundred. On GitLab.com, 191 rows are running with a NULL approval_policy_rule_id and older than 3 days. The migration uses a 1-day cutoff, so the real count is somewhat higher. 12 of those rows sit on 10 open merge requests across 8 projects; the rest are on merged or closed ones.

User experience

On open merge requests, the permanently spinning merge check clears. On merged or closed ones, nothing visible changes. Rows with a verdict, violation_data or an approval_policy_rule_id are never touched.

References

• Related issue: #617804
• The prevention fix !250868 (merged) is merged and its flag was removed in !258696 (merged), so new orphans no longer accumulate.

Screenshots or screen recordings

Backend-only, no UI change.

How to set up and validate locally

1. Run bundle exec rspec spec/migrations/delete_orphaned_running_scan_result_policy_violations_spec.rb.
2. Or seed a row with status running, NULL approval_policy_rule_id, NULL violation_data and updated_at older than a day, run bundle exec rails db:migrate, and confirm it is removed while non-matching rows stay.

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.

• Migration spec: 1 example, 0 failures. Seeds five rows and asserts only the orphaned one is deleted. The other four survive: updated 2 hours ago (created 3 days ago, so the spec fails if the guard uses created_at), has an approval_policy_rule_id, has violation_data, already failed.
• RuboCop clean on both changed files.