Exclude soft-deleted profiles from project attachment count

What does this MR do and why?

Fixes the per-project attachment cap in Security::ScanProfiles::ProjectAttachService, which was counting soft-deleted scan profiles against the limit. insert_under_limit enforces Security::ScanProfileProject::MAX_PROFILES_PER_PROJECT via a raw SQL COUNT over security_scan_profiles_projects rows for the candidate project. Because a scan profile is now soft-deleted first (deleted_at set) and its join rows are only removed later by the asynchronous Security::ScanProfiles::DeleteScanProfileService hard-delete worker, there was a window where a project could be wrongly blocked from attaching a new profile — the count was inflated by attachments to profiles that are effectively gone. The fix adds an EXISTS subquery so only attachments whose profile has deleted_at IS NULL count toward the limit.

This MR is a follow-up stacked on Soft-delete Security::ScanProfile records (!247671 - merged) • rossfuhrman • 19.3, which introduced soft-delete for Security::ScanProfile, and targets that MR's branch — the diff here is limited to this fix.

Changelog: fixed
EE: true

Related issue

[Backend] Follow-up from "Soft-delete Security:... (#611580 - closed) • rossfuhrman • 19.3

Query plans

insert_under_limit

Raw SQL

Old count subquery (counted soft-deleted profiles):

SELECT COUNT(*) FROM security_scan_profiles_projects
WHERE project_id = candidate_project_id

New count subquery (excludes soft-deleted profiles):

SELECT COUNT(*) FROM security_scan_profiles_projects spp
WHERE spp.project_id = candidate_project_id
AND EXISTS (
  SELECT 1 FROM security_scan_profiles sp
  WHERE sp.id = spp.security_scan_profile_id
  AND sp.deleted_at IS NULL
)

Query plan

Full details here

 Aggregate  (cost=10.05..10.06 rows=1 width=8) (actual time=0.540..0.541 rows=1 loops=1)
   Buffers: shared hit=10 read=7 dirtied=1
   WAL: records=1 fpi=1 bytes=8169
   I/O Timings: read=0.460 write=0.000
   ->  Nested Loop  (cost=0.70..10.05 rows=2 width=0) (actual time=0.323..0.536 rows=3 loops=1)
         Buffers: shared hit=10 read=7 dirtied=1
         WAL: records=1 fpi=1 bytes=8169
         I/O Timings: read=0.460 write=0.000
         ->  Index Only Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects spp  (cost=0.41..3.45 rows=2 width=8) (actual time=0.235..0.386 rows=3 loops=1)
               Index Cond: (spp.project_id = 82644511)
               Heap Fetches: 1
               Index Searches: 1
               Buffers: shared hit=4 read=4 dirtied=1
               WAL: records=1 fpi=1 bytes=8169
               I/O Timings: read=0.332 write=0.000
         ->  Index Scan using security_scan_profiles_pkey on public.security_scan_profiles sp  (cost=0.28..3.30 rows=1 width=8) (actual time=0.048..0.048 rows=1 loops=3)
               Index Cond: (sp.id = spp.security_scan_profile_id)
               Index Searches: 3
               Filter: (sp.deleted_at IS NULL)
               Buffers: shared hit=6 read=3
               I/O Timings: read=0.128 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'
Query ID: 7432374521301366330

 Aggregate  (cost=10.05..10.06 rows=1 width=8) (actual time=0.540..0.541 rows=1 loops=1)
   Buffers: shared hit=10 read=7 dirtied=1
   WAL: records=1 fpi=1 bytes=8169
   I/O Timings: read=0.460 write=0.000
   ->  Nested Loop  (cost=0.70..10.05 rows=2 width=0) (actual time=0.323..0.536 rows=3 loops=1)
         Buffers: shared hit=10 read=7 dirtied=1
         WAL: records=1 fpi=1 bytes=8169
         I/O Timings: read=0.460 write=0.000
         ->  Index Only Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects spp  (cost=0.41..3.45 rows=2 width=8) (actual time=0.235..0.386 rows=3 loops=1)
               Index Cond: (spp.project_id = 82644511)
               Heap Fetches: 1
               Index Searches: 1
               Buffers: shared hit=4 read=4 dirtied=1
               WAL: records=1 fpi=1 bytes=8169
               I/O Timings: read=0.332 write=0.000
         ->  Index Scan using security_scan_profiles_pkey on public.security_scan_profiles sp  (cost=0.28..3.30 rows=1 width=8) (actual time=0.048..0.048 rows=1 loops=3)
               Index Cond: (sp.id = spp.security_scan_profile_id)
               Index Searches: 3
               Filter: (sp.deleted_at IS NULL)
               Buffers: shared hit=6 read=3
               I/O Timings: read=0.128 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'
Query ID: 7432374521301366330

Extra notes/output:

original query - fresh session https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/54561/commands/157353

original query altered to return rows (3 rows returned) https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/54561/commands/157354

new query altered to return rows (3 rows returned) https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/54561/commands/157366

How to set up and validate locally

1. In a Rails console, pick a root group, a project in it, and a user. Ensure the security_scan_profiles licensed feature is available in your environment (GDK EE license):

   group   = Group.find_by_full_path('your-group')
   project = group.projects.first
   user    = User.find_by_username('root')

2. Fill the project to the cap by creating MAX_PROFILES_PER_PROJECT profiles and attaching each:

   profiles = Array.new(Security::ScanProfileProject::MAX_PROFILES_PER_PROJECT) do |i|
     FactoryBot.create(:security_scan_profile, namespace: group, name: "profile-#{i}", scan_type: :sast)
   end
   
   profiles.each do |profile|
     Security::ScanProfiles::ProjectAttachService.execute(profile: profile, current_user: user, projects: [project])
   end

3. Soft-delete one of the attached profiles (calling destroy now soft-deletes it):

   profiles.first.destroy

4. Attach a new profile and confirm it now succeeds instead of being blocked by the stale count:

   new_profile = FactoryBot.create(:security_scan_profile, namespace: group, name: 'profile-new', scan_type: :sast)
   
   result = Security::ScanProfiles::ProjectAttachService.execute(profile: new_profile, current_user: user, projects: [project])
   result[:errors] # => [] (previously blocked with a "reached the maximum limit" error)

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.