Move counter arithmetic into SQL for ScanProfileStatus::UpdateWorker

Problem

The Security::ScanProfileStatus::UpdateWorker currently uses a read-then-write counter pattern: it reads the current consecutive_failure_count / consecutive_success_count from the database in Ruby, computes prev + 1, and writes the new value back via upsert_all.

Because the new value depends on the previous run's commit being visible, we had to set data_consistency :always (see !229868 (merged)) to ensure reads always hit the primary. While the primary load impact is negligible (the worker only fires on default branch pipeline completions), this is not ideal long-term.

Proposal

Move the counter increment logic into SQL using INSERT ... ON CONFLICT DO UPDATE with expressions like:

ON CONFLICT (project_id, security_scan_profile_id) DO UPDATE SET
  consecutive_failure_count = CASE
    WHEN EXCLUDED.status IN (2, 3) -- warning, failed
      THEN security_scan_profile_project_statuses.consecutive_failure_count + 1
    ELSE 0
  END,
  consecutive_success_count = CASE
    WHEN EXCLUDED.status = 1 -- success
      THEN security_scan_profile_project_statuses.consecutive_success_count + 1
    ELSE 0
  END,
  status = CASE
    WHEN EXCLUDED.status = 1 THEN 1
    WHEN security_scan_profile_project_statuses.consecutive_failure_count + 1 >= 3 THEN 3
    ELSE 2
  END,
  ...

This way Postgres computes the increment atomically and no read is needed at all, which means:

  • The existing_statuses query can be removed entirely
  • data_consistency can be dropped back to :sticky
  • No stale-read risk regardless of dedup/lease scope mismatch

Implementation notes

  • Rails' upsert_all doesn't natively support SQL expressions in values, so we'll likely need raw SQL or Arel.sql(...) wrappers
  • The status transition logic (success resets failures, 1st-2nd failure = warning, 3rd+ = failed) needs to be expressed in SQL CASE expressions
  • Tests should verify the same state transitions as the current Ruby implementation
  • Consider using RETURNING to get the updated rows if we need post-upsert logic

Acceptance criteria

  • Counter arithmetic is computed atomically in SQL
  • data_consistency is changed back to :sticky
  • The existing_statuses read query is removed
  • All existing state transition tests continue to pass
  • RuboCop disable comment for SidekiqLoadBalancing/WorkerDataConsistency is removed

Related

Edited by 🤖 GitLab Bot 🤖