Move ScanProfileStatus counter arithmetics to SQL

What does this MR do and why?

Moves the counter arithmetic in Security::ScanProfileStatus::UpdateService from app-level into the SQL upsert itself.

Today, the service read the existing Security::ScanProfileProjectStatus row, computed consecutive_failure_count + 1 / consecutive_success_count + 1, then wrote the result back with upsert_all.

This ia a TOCTOU, so at the time, it was forcing the read onto the primary with data_consistency :always. This MR removes the read entirely. build_upsert_attributes now only builds each row's insert values, and the increment logic moves into a custom on_duplicate clause passed to upsert_all SQL query. Postgres row-locks the conflicting tuple before evaluating that expression, so the increment is atomic inside the single write statement with no separate read and no race window. data_consistency goes back to :sticky.

Changelog: changed EE: true

Related issue

Move counter arithmetic into SQL for ScanProfil... (#598530 - closed) • Gal Katz • 19.6

Query plans

Security::ScanProfileStatus::UpdateService#execute

Raw SQL

INSERT INTO "security_scan_profile_project_statuses"
  ("project_id","security_scan_profile_id","status","consecutive_failure_count",
   "consecutive_success_count","last_scan_at","build_id","created_at","updated_at")
VALUES
  (86624825, 1004782, 1, 0, 1, '2026-09-27 08:51:29', 100, '2026-09-27 07:38:52.392210', '2026-09-27 07:38:52.392212'),
  (86624808, 974, 3, 1, 0, '2026-09-27 08:51:29', 101, '2026-09-27 07:38:52.392218', '2026-09-27 07:38:52.392218')
ON CONFLICT ("project_id","security_scan_profile_id") DO UPDATE SET
  build_id = EXCLUDED.build_id,
  last_scan_at = EXCLUDED.last_scan_at,
  consecutive_failure_count = CASE WHEN EXCLUDED.status = 1 THEN 0
    ELSE LEAST(security_scan_profile_project_statuses.consecutive_failure_count + 1, 32767) END,
  consecutive_success_count = CASE WHEN EXCLUDED.status = 1
    THEN LEAST(security_scan_profile_project_statuses.consecutive_success_count + 1, 32767) ELSE 0 END,
  status = CASE
    WHEN EXCLUDED.status = 1 THEN 1
    WHEN security_scan_profile_project_statuses.consecutive_failure_count + 1 >= 3
      THEN 2
    ELSE 3
  END,
  updated_at = NOW()
  WHERE security_scan_profile_project_statuses.build_id IS NULL
    OR security_scan_profile_project_statuses.build_id < EXCLUDED.build_id
RETURNING "id"

Query plan

See this - https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/60059/commands/165202

 Insert on public.security_scan_profile_project_statuses  (cost=0.00..0.03 rows=2 width=62) (actual time=11.863..12.841 rows=2 loops=1)
   Buffers: shared hit=46 read=25 dirtied=10
   WAL: records=14 fpi=8 bytes=45997
   I/O Timings: read=11.923 write=0.000
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=62) (actual time=0.288..0.315 rows=2 loops=1)
         Buffers: shared hit=18 read=1 dirtied=1
         WAL: records=1 fpi=0 bytes=99
         I/O Timings: read=0.066 write=0.000
Trigger RI_ConstraintTrigger_c_64917 for constraint fk_rails_840795ce98: time=11.482 calls=2
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off'
Query ID: 439079019594952494

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.