Add group transfer event handler to security scan profile

What does this MR do and why?

Adds a group transfer handler for security scan profiles. The new worker schedules a worker based on the previous state of the group based on the decision made here.

Changelog: added
EE: true

How to set up and validate locally

Actions:

Create a scan profile

profile = Security::ScanProfile.create!(
  namespace_id: <group_id>,
  scan_type: :secret_detection,
  name: 'Test profile',
  description: 'Test profile for secret detection',
  gitlab_recommended: false
)

Attach scan profile to a project

Security::ScanProfileProject.create!(
  security_scan_profile_id: profile.id,
  project_id: <project_id>
)

Verify project-profile associations

<project>.security_scan_profiles

Verify scan profile exists

Security::ScanProfile.find(<profile_id>)

Validation steps:

Setup

1. Select a root group rg.
2. Create a scan profile for rg.
3. Create (or select) a nested subgroup g under rg.
4. Create (or select) a project p1 under g.
5. Attach the scan profile to p1.

Test 1: Move subgroup within same root namespace

1. Move group g to a different group nested under rg (same root namespace).
2. Verify the connection between the scan profile and p1 remains.

Test 2: Move subgroup to different root namespace

1. Move group g to a different root namespace.
2. Verify the connection between the scan profile and p1 is deleted.

Test 3: Move root group to be nested group

1. Create (or select) a project p2 under rg.
2. Attach the scan profile to p2.
3. Move rg to be nested under a different root group.
4. Verify both the scan profile and all project connections are deleted.

Query plans

Note - These tables are new and empty. All queries will return 0 rows.

clean_old_namespace_connections_service - delete all

Raw sql

DELETE FROM "security_scan_profiles_projects"
WHERE ("security_scan_profiles_projects"."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 (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
            AND "security_scan_profiles"."namespace_id" != 9970)

Query plan

See full plan here

 ModifyTable on public.security_scan_profiles_projects  (cost=0.04..0.06 rows=0 width=0) (actual time=0.006..0.007 rows=0 loops=1)
   ->  Nested Loop Semi Join  (cost=0.04..0.06 rows=1 width=18) (actual time=0.005..0.006 rows=0 loops=1)
         ->  Seq Scan on public.security_scan_profiles_projects  (cost=0.00..0.00 rows=1 width=14) (actual time=0.005..0.006 rows=0 loops=1)
         ->  Nested Loop  (cost=0.04..0.05 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=0)
               ->  Seq Scan on public.security_scan_profiles_projects security_scan_profiles_projects_1  (cost=0.04..0.04 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=0)
                     Filter: (security_scan_profiles_projects_1.project_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}'::bigint[]))
                     Rows Removed by Filter: 0
               ->  Seq Scan on public.security_scan_profiles  (cost=0.00..0.00 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=0)
                     Filter: (security_scan_profiles.namespace_id <> 9970)
                     Rows Removed by Filter: 0
Settings: random_page_cost = '1.5', work_mem = '100MB', jit = 'off', seq_page_cost = '4', effective_cache_size = '338688MB'

delete_scan_profile_service - scan_profile.destroy

Raw sql

DELETE FROM "security_scan_profiles" WHERE "security_scan_profiles"."id" = 1

Query plan

See full plan here

 ModifyTable on public.security_scan_profiles  (cost=0.00..0.00 rows=0 width=0) (actual time=0.004..0.005 rows=0 loops=1)
   ->  Seq Scan on public.security_scan_profiles  (cost=0.00..0.00 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=1)
         Filter: (security_scan_profiles.id = 1)
         Rows Removed by Filter: 0
Settings: work_mem = '100MB', jit = 'off', seq_page_cost = '4', effective_cache_size = '338688MB', random_page_cost = '1.5'

delete_scan_profile_service - delete_all

Raw sql

 DELETE FROM "security_scan_profiles_projects" WHERE "security_scan_profiles_projects"."id" IN (100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110)

Query plan

See full plan here

 ModifyTable on public.security_scan_profiles_projects  (cost=0.03..0.03 rows=0 width=0) (actual time=0.002..0.003 rows=0 loops=1)
   ->  Seq Scan on public.security_scan_profiles_projects  (cost=0.03..0.03 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (security_scan_profiles_projects.id = ANY ('{100,101,102,103,104,105,106,107,108,109,110}'::bigint[]))
         Rows Removed by Filter: 0
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', work_mem = '100MB', jit = 'off', seq_page_cost = '4'

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.

Related to [Backend] Handle project and group transfer events (#581533) • Gal Katz • 18.8