Implement profile status update (!229868) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Adds the profile status update service, worker, and lifecycle callbacks that track the health of scans triggered by security scan profiles.

When a pipeline completes on the default branch, the worker picks up profile-triggered builds (identified by source: security_scan_profiles from !227028 (merged)), resolves which scan profile triggered each job (via Redis mapping from !229429 (merged)), and upserts a ScanProfileProjectStatus record with the appropriate state.

Why: This is the core logic for #591555 (closed) (scanner status indicators for security profiles). Without this, the status table from !227103 (merged) would never be populated.

This is MR 3 of 4 in the implementation plan. Depends on MR 1 (source tagging), MR 1b (Redis persistence), and MR 2 (table/model).

Implementation details

UpdateService (ee/app/services/security/scan_profile_status/update_service.rb):
- Finds completed builds with source: security_scan_profiles via join on p_ci_build_sources
- Resolves profile ID: primary path reads from Redis (ScanProfileMappingStore.profile_id_for_build), fallback detects scan type from build artifacts and looks up the profile
- Upserts ScanProfileProjectStatus with state transition logic:
  - Success resets failure counter, increments success counter
  - 1st-2nd failure -> warning, 3rd+ failure -> failed
- Gated by licensed_feature_available?(:security_scan_profiles)
UpdateWorker (ee/app/workers/security/scan_profile_status/update_worker.rb):
- Triggered from pipeline after_transition on [:success, :failed, :canceled] for default branch only
- Exclusive lease scoped to project_id (prevents TOCTOU race in read-then-write)
- Re-enqueues on lock failure after 10 seconds
- data_consistency :sticky, urgency :low, idempotent!
- defer_on_database_health_signal for gitlab_sec
Lifecycle callbacks (ee/app/models/security/scan_profile_project.rb):
- after_create_commit :initialize_profile_status — creates a status record with success (Active) state
- after_destroy_commit :mark_profile_status_unconfigured — sets status to not_configured, resets counters, preserves last_scan_at
Pipeline trigger (ee/app/models/ee/ci/pipeline.rb):
- Added Security::ScanProfileStatus::UpdateWorker.perform_async(pipeline.id) alongside existing PipelineAnalyzersStatusUpdateWorker trigger

State transition logic

Current State	Scan Result	New State	Failure Count	Success Count
any	success	`success` (Active)	0	prev + 1
any	failed (1st)	`warning`	1	0
any	failed (2nd)	`warning`	2	0
any	failed (3rd+)	`failed`	3+	0

How to set up and validate locally

In GDK, create a group and a project with a repository
Attach a scan profile (e.g., Secret Detection) to the group and apply it to the project
Verify a status record was created (lifecycle callback):

project = Project.find_by_full_path('your-group/your-project')
Security::ScanProfileProjectStatus.where(project_id: project.id).each do |s|
  puts "profile_id=#{s.security_scan_profile_id} status=#{s.status} failures=#{s.consecutive_failure_count}"
end
# Should show status: "success" (initial Active state)

Push a commit to the default branch to trigger a pipeline
Wait for pipeline completion, then verify the status was updated:

project = Project.find_by_full_path('your-group/your-project')
Security::ScanProfileProjectStatus.where(project_id: project.id).each do |s|
  puts "profile_id=#{s.security_scan_profile_id} status=#{s.status} " \
       "successes=#{s.consecutive_success_count} failures=#{s.consecutive_failure_count} " \
       "last_scan=#{s.last_scan_at} build_id=#{s.build_id}"
end

Remove the profile from the project and verify status becomes unconfigured:

Security::ScanProfileProjectStatus.where(project_id: project.id).each do |s|
  puts "status=#{s.status} last_scan=#{s.last_scan_at}"
end
# Should show status: "not_configured", last_scan_at preserved

Query plans

Click to expand

Setup: seed data

Run these inserts first to ensure the queries below hit existing rows and exercise indexes.

INSERT INTO security_scan_profile_project_statuses
  (id, project_id, security_scan_profile_id, build_id, last_scan_at,
   created_at, updated_at, status, consecutive_failure_count, consecutive_success_count)
VALUES
  (nextval('security_scan_profile_project_statuses_id_seq'),
   74581969, 1, NULL, NOW() - INTERVAL '1 day',
   NOW() - INTERVAL '7 days', NOW() - INTERVAL '1 day',
   1, 0, 3),
  (nextval('security_scan_profile_project_statuses_id_seq'),
   74581969, 10, NULL, NOW() - INTERVAL '2 days',
   NOW() - INTERVAL '7 days', NOW() - INTERVAL '2 days',
   2, 2, 0)
ON CONFLICT (project_id, security_scan_profile_id) DO NOTHING;

1. ScanProfileProjectStatus.by_project

File: ee/app/services/security/scan_profile_status/update_service.rb (line 25)
Context: Loads all existing status records for a project to determine state transitions before upsert.

Query

SELECT *
FROM security_scan_profile_project_statuses
WHERE project_id = 74581969;

Plan

 Index Scan using idx_security_scan_profile_proj_statuses_on_project_and_profile on public.security_scan_profile_project_statuses  (cost=0.14..3.16 rows=1 width=62) (actual time=0.055..0.056 rows=2 loops=1)
   Index Cond: (security_scan_profile_project_statuses.project_id = 74581969)
   Buffers: shared hit=5
Settings: effective_cache_size = '338688MB', jit = 'off', random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4'

https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/51001/commands/151011

2. ScanProfileProjectStatus.for_projects_and_profile

File: ee/app/services/security/scan_profiles/project_detach_service.rb (line 57)
Context: Finds status records for detached projects to mark them as unconfigured.

Query

SELECT *
FROM security_scan_profile_project_statuses
WHERE project_id = 74581969
  AND security_scan_profile_id = 1;

Plan

 Index Scan using idx_security_scan_profile_proj_statuses_on_project_and_profile on public.security_scan_profile_project_statuses  (cost=0.14..3.16 rows=1 width=62) (actual time=0.020..0.020 rows=1 loops=1)
   Index Cond: ((security_scan_profile_project_statuses.project_id = 74581969) AND (security_scan_profile_project_statuses.security_scan_profile_id = 1))
   Buffers: shared hit=5
Settings: jit = 'off', random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4', effective_cache_size = '338688MB'

https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/51001/commands/151012

3. ScanProfileProjectStatus.for_projects_and_profile(...).update_all (UPDATE)

File: ee/app/services/security/scan_profiles/project_detach_service.rb (lines 58-63)
Context: Marks status records as not_configured when a profile is detached from projects.

Query

EXPLAIN
UPDATE security_scan_profile_project_statuses
SET status = 0,
    consecutive_failure_count = 0,
    consecutive_success_count = 0,
    updated_at = NOW()
WHERE project_id = 74581969
  AND security_scan_profile_id = 1;

Plan

 ModifyTable on public.security_scan_profile_project_statuses  (cost=0.14..3.16 rows=0 width=0) (actual time=0.201..0.202 rows=0 loops=1)
   Buffers: shared hit=15
   WAL: records=1 fpi=0 bytes=90
   ->  Index Scan using idx_security_scan_profile_proj_statuses_on_project_and_profile on public.security_scan_profile_project_statuses  (cost=0.14..3.16 rows=1 width=20) (actual time=0.034..0.035 rows=1 loops=1)
         Index Cond: ((security_scan_profile_project_statuses.project_id = 74581969) AND (security_scan_profile_project_statuses.security_scan_profile_id = 1))
         Buffers: shared hit=5
Settings: random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off'

https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/51001/commands/151015

4. ScanProfileProjectStatus.upsert_all (INSERT ... ON CONFLICT UPDATE)

File: ee/app/services/security/scan_profile_status/update_service.rb (lines 38-41)
Context: Upserts status records after resolving build results. Uses unique_by: [:project_id, :security_scan_profile_id].

Query

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
  (74581969, 1, 1, 0, 1, NOW(), NULL, NOW(), NOW())
ON CONFLICT (project_id, security_scan_profile_id)
DO UPDATE SET
  status = EXCLUDED.status,
  consecutive_failure_count = EXCLUDED.consecutive_failure_count,
  consecutive_success_count = EXCLUDED.consecutive_success_count,
  last_scan_at = EXCLUDED.last_scan_at,
  build_id = EXCLUDED.build_id,
  updated_at = EXCLUDED.updated_at;

Plan

 ModifyTable on public.security_scan_profile_project_statuses  (cost=0.00..0.02 rows=1 width=62) (actual time=0.282..0.283 rows=1 loops=1)
   Buffers: shared hit=35 dirtied=2
   WAL: records=4 fpi=1 bytes=1436
   ->  Result  (cost=0.00..0.02 rows=1 width=62) (actual time=0.101..0.102 rows=1 loops=1)
         Buffers: shared hit=17 dirtied=1
         WAL: records=1 fpi=0 bytes=99
Settings: jit = 'off', random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4', effective_cache_size = '338688MB'

https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/51001/commands/151059

5. ScanProfileProjectStatus.upsert_all (initialize_profile_statuses)

File: ee/app/services/security/scan_profiles/project_attach_service.rb (lines 108-113)
Context: Creates initial status records (success/Active) when a profile is attached to projects.

Query

INSERT INTO security_scan_profile_project_statuses
  (project_id, security_scan_profile_id, status,
   consecutive_failure_count, consecutive_success_count,
   created_at, updated_at)
VALUES
  (74581969, 10, 1, 0, 0, NOW(), NOW())
ON CONFLICT (project_id, security_scan_profile_id)
DO UPDATE SET
  status = EXCLUDED.status,
  consecutive_failure_count = EXCLUDED.consecutive_failure_count,
  consecutive_success_count = EXCLUDED.consecutive_success_count,
  updated_at = EXCLUDED.updated_at;

Plan

 ModifyTable on public.security_scan_profile_project_statuses  (cost=0.00..0.02 rows=1 width=62) (actual time=0.311..0.312 rows=1 loops=1)
   Buffers: shared hit=35
   WAL: records=2 fpi=0 bytes=143
   ->  Result  (cost=0.00..0.02 rows=1 width=62) (actual time=0.085..0.086 rows=1 loops=1)
         Buffers: shared hit=17
Settings: jit = 'off', random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4', effective_cache_size = '338688MB'

https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/51001/commands/151060

6. pipeline.builds.with_build_source(:security_scan_profiles).with_statuses(...).latest

File: ee/app/services/security/scan_profile_status/update_service.rb (lines 50-53)
File: ee/app/models/ee/ci/processable.rb (lines 10-12)
Context: Finds completed builds in a pipeline that were triggered by security scan profiles.
Joins p_ci_builds with p_ci_build_sources filtering by source = 'security_scan_profiles' (enum value 1003).

Query

SELECT p_ci_builds.*
FROM p_ci_builds
INNER JOIN p_ci_build_sources
  ON p_ci_build_sources.partition_id IS NOT NULL
  AND p_ci_build_sources.build_id = p_ci_builds.id
  AND p_ci_build_sources.partition_id = p_ci_builds.partition_id
WHERE p_ci_builds.type = 'Ci::Build'
  AND p_ci_builds.commit_id = 2471539536
  AND p_ci_builds.partition_id = 110
  AND p_ci_build_sources.source = 1003
  AND p_ci_builds.status IN ('success', 'failed', 'canceled', 'skipped')
  AND (p_ci_builds.retried = FALSE OR p_ci_builds.retried IS NULL);

Plan

 Nested Loop  (cost=1.15..342.10 rows=75 width=636) (actual time=0.053..0.112 rows=1 loops=1)
   Buffers: shared hit=88
   ->  Index Scan using ci_builds_110_commit_id_status_type_idx on gitlab_partitions_dynamic.ci_builds_110 p_ci_builds  (cost=0.57..71.72 rows=75 width=636) (actual time=0.024..0.055 rows=14 loops=1)
         Index Cond: ((p_ci_builds.commit_id = '2471539536'::bigint) AND ((p_ci_builds.status)::text = ANY ('{success,failed,canceled,skipped}'::text[])) AND ((p_ci_builds.type)::text = 'Ci::Build'::text))
         Filter: (((NOT p_ci_builds.retried) OR (p_ci_builds.retried IS NULL)) AND (p_ci_builds.partition_id = 110))
         Rows Removed by Filter: 0
         Buffers: shared hit=18
   ->  Index Scan using ci_build_sources_110_pkey on gitlab_partitions_dynamic.ci_build_sources_110 p_ci_build_sources  (cost=0.57..3.59 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=14)
         Index Cond: ((p_ci_build_sources.build_id = p_ci_builds.id) AND (p_ci_build_sources.partition_id = 110))
         Filter: (p_ci_build_sources.source = 1003)
         Rows Removed by Filter: 1
         Buffers: shared hit=70
Settings: jit = 'off', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '338688MB', random_page_cost = '1.5'

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/51284/commands/151694

MR acceptance checklist

Service tested: success, warning, failed transitions, recovery, fallback resolution, license gating, unresolvable profile (ee/spec/services/security/scan_profile_status/update_service_spec.rb)
Worker tested: happy path, missing pipeline, non-default branch, lock failure, idempotency (ee/spec/workers/security/scan_profile_status/update_worker_spec.rb)
Worker registered in all_queues.yml
No database migration needed (uses table from MR 2)
RuboCop clean
Feature flag: not needed (license check is sufficient)

Part of #591916 (closed) (implementation plan)
Related to #591555 (closed) (parent issue)
Closes #593132 (closed)
Blocks #593133 (closed) (GraphQL API, MR 4)

Changelog: added
EE: true

Edited Apr 29, 2026 by Nicolae Rotaru

Implement profile status update

What does this MR do and why?

Implementation details

State transition logic

How to set up and validate locally

Query plans

Setup: seed data

1. ScanProfileProjectStatus.by_project

Query

Plan

2. ScanProfileProjectStatus.for_projects_and_profile

Query

Plan

3. ScanProfileProjectStatus.for_projects_and_profile(...).update_all (UPDATE)

Query

Plan

4. ScanProfileProjectStatus.upsert_all (INSERT ... ON CONFLICT UPDATE)

Query

Plan

5. ScanProfileProjectStatus.upsert_all (initialize_profile_statuses)

Query

Plan

6. pipeline.builds.with_build_source(:security_scan_profiles).with_statuses(...).latest

Query

Plan

MR acceptance checklist

Related issues

Merge request reports