Sweep approval policy violations that stay running

What does this MR do and why?

Adds a cron sweep for merge request approval policy violations that stay running after the head pipeline has finished. A running row keeps the security_policy_violations merge check in checking and suppresses the policy bot comment. Every path that re-evaluates a merge request starts from a pipeline event, so nothing picks these rows up again.

Details

GitLab.com has about 18,000 running rows not updated in over 7 days, about 4,600 of them on open merge requests (#617804). Known causes:

  • Merge requests with an empty latest diff (#638061, fixed separately).
  • Rows written after the merge, after CleanupMergeRequestViolationsWorker has already run.

What this MR adds:

  • Index: a partial index idx_scan_result_policy_violations_running_updated_at_id on (updated_at, id) WHERE status = 0 (running), added in a post-deploy migration.
  • Worker: SweepStaleRunningViolationsWorker. It runs every 30 minutes and walks rows not updated for over 1 hour, in batches of 100, up to 1,000 rows per run. It handles each merge request once per run:
    • Merged or closed merge request: deletes its violations, like CleanupMergeRequestViolationsWorker does.
    • Open merge request with a complete head pipeline: enqueues UnblockPendingMergeRequestViolationsWorker for that merge request. That marks running rows skipped, which fails closed, then re-evaluates.
    • Anything else is left alone.
  • Resume cursor: when a run hits the cap, it saves (updated_at, id) in Redis and the next run resumes from there. Without it, rows the sweep has to leave alone (flag off, pipeline still running) would fill every run's budget.
  • Timeout worker argument: UnblockPendingMergeRequestViolationsWorker#perform takes an optional params hash with 'merge_request_id'. When it is set, the worker loads that open merge request instead of looking up merge requests by head SHA. Its self-reschedule only passes params when there are any. The argument is optional and only the flagged sweep passes it, so old and new Sidekiq nodes stay compatible during deploy.
    • Head pipeline guard: when 'merge_request_id' is set, the worker only acts if the merge request's current head pipeline is the queued one, so a newer pipeline is never timed out on the older one's behalf. The check runs at perform time, so it also covers reschedules.

Feature flag and logging:

  • Behind the gitlab_com_derisk flag sweep_stale_running_policy_violations (default off), rollout issue: #638431. The actor is the merge request's target project, checked per row.
  • No changelog: the flag is off by default.
  • One approval_policy_evaluation log line per merge request acted on. The per-run counts are in the job's done log (extra.result).

Reviewable LOC: 165 (plus 212 in tests)

References

Screenshots or screen recordings

Not applicable, backend only.

How to set up and validate locally

  1. Create an MR with a scan_finding policy and let its pipeline finish. In a Rails console, force a stale row:
    v = mr.scan_result_policy_violations.first
    v.update_columns(status: :running, updated_at: 2.hours.ago)
  2. Run Security::ScanResultPolicies::SweepStaleRunningViolationsWorker.new.perform.
  3. With the flag on, the timeout runs for the MR and the row gets a verdict. With Feature.disable(:sweep_stale_running_policy_violations), the row stays running.
  4. For a merged MR with a stale row, the sweep deletes the row.

Specs:

$ bin/rspec ee/spec/workers/security/scan_result_policies/sweep_stale_running_violations_worker_spec.rb ee/spec/workers/security/scan_result_policies/unblock_pending_merge_request_violations_worker_spec.rb
52 examples, 0 failures
$ bin/rspec ee/spec/models/security/scan_result_policy_violation_spec.rb  # with the above
174 examples, 0 failures

Database review

  • Table size: scan_result_policy_violations is table_size: small in db/docs, so the index uses add_concurrent_index in a post-deploy migration. Migrated up and down locally (0.16 s up, 0.07 s down).
  • Query shape:
    • The keyset iterator scans WHERE status = 0 AND updated_at < ? on the partial index.
    • Per batch: one MergeRequest.id_in(...) lookup.
    • Per merge request: target project, latest diff and head pipeline (CI database), about 3–4 queries, at most 1,000 merge requests per run.
    • Deletes for merged or closed merge requests use the existing merge_request_id index.
  • Data deletion: delete_all removes all violations of a merged or closed merge request, the same as CleanupMergeRequestViolationsWorker on merge. Those rows don't affect anything after the merge, so they don't need to be recovered.
  • Rows the sweep can't resolve: an open merge request with no head pipeline, or whose running rows have no matching approval rule, stays running. The sweep revisits it every pass, but the cursor keeps it from blocking other rows.

Raw SQL (as Rails runs it; the sweep walks running_updated_before(1.hour.ago) with Gitlab::Pagination::Keyset::Iterator, of: 100, at most 1,000 rows per run):

-- Scope: running_updated_before
SELECT "scan_result_policy_violations".* FROM "scan_result_policy_violations" WHERE "scan_result_policy_violations"."status" = 0 AND "scan_result_policy_violations"."updated_at" < '<now - 1 hour>' ORDER BY "scan_result_policy_violations"."updated_at" ASC, "scan_result_policy_violations"."id" ASC;

-- Keyset batch 1 (no cursor)
SELECT "scan_result_policy_violations".* FROM "scan_result_policy_violations" WHERE "scan_result_policy_violations"."status" = 0 AND "scan_result_policy_violations"."updated_at" < '<now - 1 hour>' ORDER BY "scan_result_policy_violations"."updated_at" ASC, "scan_result_policy_violations"."id" ASC LIMIT 100;

-- Keyset batch N, and the first batch of a run that resumes from the Redis cursor
SELECT "scan_result_policy_violations".* FROM "scan_result_policy_violations" WHERE "scan_result_policy_violations"."status" = 0 AND "scan_result_policy_violations"."updated_at" < '<now - 1 hour>' AND (("scan_result_policy_violations"."updated_at", "scan_result_policy_violations"."id") > ('<cursor_updated_at>', <cursor_id>)) ORDER BY "scan_result_policy_violations"."updated_at" ASC, "scan_result_policy_violations"."id" ASC LIMIT 100;

Query plans (production-size clone of the main database, 100-row batches, updated_at bound = 1 hour ago; the cursor values in the resumed batch are arbitrary):

Query Plan Execution Buffers Console
Batch 1, without the new index Parallel Seq Scan, then top-N sort, 2 workers, about 1M rows scanned 3.47 s (cold) 19,598 hit, 98,042 read plan
Batch 1, with idx_scan_result_policy_violations_running_updated_at_id Index Scan on the partial index, no sort, 100 rows 1.0 ms (cold) 70 hit, 31 read plan
Resumed batch (row comparison on the cursor), with the index Index Scan on the partial index, no sort, 100 rows 2.2 ms (cold) 61 hit, 40 read plan

The index was created on the clone with CREATE INDEX (0.7 s) followed by ANALYZE.

  • Without the index, the scan is a sequential scan of the whole table (about 118,000 buffers) on every batch. A 30-minute cron would run up to 10 of these per run, so the index is required, not optional.
  • With the index, both the first batch and the resumed batch are a single Index Scan on the partial index. It satisfies the status = 0 filter, the updated_at bound and the ORDER BY updated_at, id, with no sort node and about 100 buffers per batch.
  • Per-batch MergeRequest.id_in(...) lookup: primary-key lookup on merge_requests, so no separate plan.

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.

Context for LLM agents
  • Rejected: enqueuing the repair from the security_policy_violations merge check. It would add Sidekiq work to a read path that runs on every merge request view.
  • Rejected: a post-deploy migration that resyncs existing rows once. It doesn't cover rows created later by unknown causes.
  • Rejected: a background operation (batched background migration). Each row needs merge request state and the head pipeline from the CI database, so this isn't a pure SQL purge.
  • The timeout path fails closed (requires approval) before re-evaluating, the same as the existing 150 s timeout, so the sweep never unblocks a merge request without a verdict.
  • The cron entry is in ee/config/schedule.yml, where EE cron defaults now live.
Edited by Alan (Maciej) Paruszewski

Merge request reports

Loading
Loading