Add caller to triage and remediation coverage updates

What does this MR do and why?

Wire the triage coverage recompute pipeline into the scan profile flows with SchedulesTriageCoverageUpdate, a concern that enqueues Security::TriageCoverage::ScheduleUpdateWorker whenever a remediation profile's project coverage changes.

The security inventory filters on triage coverage bitmasks, so they must be refreshed whenever the set of projects covered by a remediation profile (dependency_scanning_post_processing, triage_and_remediation) changes. This MR schedules that recompute from:

• ProjectAttachService and ProjectDetachService: projects attached to or detached from a remediation profile.
• DeleteScanProfileService: projects whose connection is deleted with the profile. Connections are deleted per batch with delete_all_returning(:project_id).
• CleanOldNamespaceConnectionsService and ProcessProjectTransferEventsWorker: connections to profiles outside the new root namespace are deleted with delete_all_returning(:project_id, :security_scan_profile_id). Only projects that lost a remediation profile are enqueued, using one primary-key lookup of the deleted profile ids (ScanProfile.remediation.id_in(...)).
• UpdateService: only when the profile's triggers actually changed.
• FindOrCreateService: only when a missing trigger was created for the default profile. The worker is called with the profile id (perform_async(nil, profile.id)) so it fans out to the attached projects itself.

Non-remediation profiles never enqueue the worker.

Changelog: added
EE: true

Related issue

[Backend] Capability coverage for Triage and Re... (#629986 - closed) • Gal Katz • 19.6

Query plans

scope.delete_all_returning(:project_id, :security_scan_profile_id)

Raw SQL

DELETE FROM security_scan_profiles_projects
WHERE id IN (
        SELECT
            "security_scan_profiles_projects"."id"
        FROM
            "security_scan_profiles_projects"
            INNER JOIN "security_scan_profiles" ON "security_scan_profiles"."id" = "security_scan_profiles_projects"."security_scan_profile_id"
        WHERE
            "security_scan_profiles_projects"."project_id" IN (70472537, 65339032, 80141160)
            AND "security_scan_profiles"."namespace_id" != 9971)
RETURNING
    "project_id",
    "security_scan_profile_id"

query plan

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

 Delete on public.security_scan_profiles_projects  (cost=30.10..46.41 rows=5 width=18) (actual time=7.509..7.603 rows=9 loops=1)
   Buffers: shared hit=78 read=20 dirtied=9
   WAL: records=9 fpi=8 bytes=65862
   I/O Timings: read=7.212 write=0.000
   ->  Nested Loop  (cost=30.10..46.41 rows=5 width=18) (actual time=7.459..7.513 rows=9 loops=1)
         Buffers: shared hit=52 read=20
         I/O Timings: read=7.212 write=0.000
         ->  HashAggregate  (cost=29.81..29.86 rows=5 width=20) (actual time=6.507..6.513 rows=9 loops=1)
               Group Key: security_scan_profiles_projects_1.id
               Batches: 1  Memory Usage: 32kB
               Buffers: shared hit=27 read=18
               I/O Timings: read=6.272 write=0.000
               ->  Nested Loop  (cost=0.70..29.80 rows=5 width=20) (actual time=3.207..6.484 rows=9 loops=1)
                     Buffers: shared hit=27 read=18
                     I/O Timings: read=6.272 write=0.000
                     ->  Index Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects security_scan_profiles_projects_1  (cost=0.42..13.28 rows=5 width=22) (actual time=2.392..4.301 rows=9 loops=1)
                           Index Cond: (security_scan_profiles_projects_1.project_id = ANY ('{70472537,65339032,80141160}'::bigint[]))
                           Index Searches: 3
                           Buffers: shared hit=4 read=14
                           I/O Timings: read=4.187 write=0.000
                     ->  Index Scan using security_scan_profiles_pkey on public.security_scan_profiles  (cost=0.28..3.30 rows=1 width=14) (actual time=0.240..0.240 rows=1 loops=9)
                           Index Cond: (security_scan_profiles.id = security_scan_profiles_projects_1.security_scan_profile_id)
                           Index Searches: 9
                           Filter: (security_scan_profiles.namespace_id <> 9971)
                           Buffers: shared hit=23 read=4
                           I/O Timings: read=2.085 write=0.000
         ->  Index Scan using security_scan_profiles_projects_pkey on public.security_scan_profiles_projects  (cost=0.29..3.31 rows=1 width=14) (actual time=0.110..0.110 rows=1 loops=9)
               Index Cond: (security_scan_profiles_projects.id = security_scan_profiles_projects_1.id)
               Index Searches: 9
               Buffers: shared hit=25 read=2
               I/O Timings: read=0.940 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'
Query ID: 7051622946323104096

batch.delete_all

Raw SQL

DELETE FROM security_scan_profiles_projects
WHERE id IN (
  SELECT "security_scan_profiles_projects"."id"
  FROM "security_scan_profiles_projects"
  WHERE "security_scan_profiles_projects"."security_scan_profile_id" = 1049
    AND "security_scan_profiles_projects"."id" >= 250000
    AND "security_scan_profiles_projects"."id" < 500000
)
RETURNING "project_id"

query plan

See this - https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/59532/commands/164695

 Delete on public.security_scan_profiles_projects  (cost=4.51..9.04 rows=1 width=12) (actual time=1.184..1.186 rows=0 loops=1)
   Buffers: shared hit=4 read=3
   I/O Timings: read=1.080 write=0.000
   ->  Nested Loop  (cost=4.51..9.04 rows=1 width=12) (actual time=1.182..1.184 rows=0 loops=1)
         Buffers: shared hit=4 read=3
         I/O Timings: read=1.080 write=0.000
         ->  Bitmap Heap Scan on public.security_scan_profiles_projects security_scan_profiles_projects_1  (cost=4.22..5.73 rows=1 width=14) (actual time=1.181..1.183 rows=0 loops=1)
               Recheck Cond: ((security_scan_profiles_projects_1.security_scan_profile_id = 1049) AND (security_scan_profiles_projects_1.id >= 250000) AND (security_scan_profiles_projects_1.id < 500000))
               Buffers: shared hit=4 read=3
               I/O Timings: read=1.080 write=0.000
               ->  BitmapAnd  (cost=4.22..4.22 rows=1 width=0) (actual time=1.168..1.169 rows=0 loops=1)
                     Buffers: shared hit=4 read=3
                     I/O Timings: read=1.080 write=0.000
                     ->  Bitmap Index Scan on idx_security_scan_profiles_projects_on_security_scan_profile_id  (cost=0.00..1.94 rows=20 width=0) (actual time=1.005..1.006 rows=20 loops=1)
                           Index Cond: (security_scan_profiles_projects_1.security_scan_profile_id = 1049)
                           Index Searches: 1
                           Buffers: shared hit=3 read=2
                           I/O Timings: read=0.933 write=0.000
                     ->  Bitmap Index Scan on security_scan_profiles_projects_pkey  (cost=0.00..2.02 rows=23 width=0) (actual time=0.160..0.160 rows=0 loops=1)
                           Index Cond: ((security_scan_profiles_projects_1.id >= 250000) AND (security_scan_profiles_projects_1.id < 500000))
                           Index Searches: 1
                           Buffers: shared hit=1 read=1
                           I/O Timings: read=0.147 write=0.000
         ->  Index Scan using security_scan_profiles_projects_pkey on public.security_scan_profiles_projects  (cost=0.29..3.31 rows=1 width=14) (never executed)
               Index Cond: (security_scan_profiles_projects.id = security_scan_profiles_projects_1.id)
               I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB'
Query ID: 8247481006882054557

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.