Exclude archived projects from scan profile project count

rossfuhrmanrequested to merge
629984-exclude-archived-projects-from-scan-profile-count into master

What does this MR do and why?

This MR excludes archived projects from ScanProfileType#projectCount by reading a denormalized archived flag on security_scan_profiles_projects, and adds Security::ScanProfiles::ProcessArchivedEventsWorker to keep that flag current.

ScanProfileType#projectCount (batch-loaded via Security::ScanProfile.project_counts_by_id) was counting every row in security_scan_profiles_projects regardless of whether the attached project is archived, so the number shown on the list view was inflated. The obvious fix — join to projects and filter on archived — isn't available: security_scan_profiles_projects lives in the gitlab_sec database and projects lives in gitlab_main_org, and cross-database joins between main/ci/sec/geo are prohibited. Instead this denormalizes an archived boolean onto security_scan_profiles_projects, the same pattern already used by vulnerability_reads, sbom_occurrences and analyzer_project_statuses. "Archived" here means the effective state (project.self_or_ancestors_archived?), so a project under an archived ancestor group is also excluded.

Specifically:

  • Security::ScanProfileProject.unarchived (where(archived: false)), chained into project_counts_by_id so the count stays a single, indexed, cheap query even when batch-loaded across many profiles.
  • Security::ScanProfiles::ProjectAttachService now computes archived per project at insert time (preloading :project_namespace alongside namespace: :namespace_settings_with_ancestors_inherited_settings to avoid an N+1 over the batch of up to 500 projects) and writes it via a parallel-array raw INSERT (UNNEST(ARRAY[:project_ids], ARRAY[:archived_flags])), so newly attached rows are correct immediately.
  • Security::ScanProfiles::ProcessArchivedEventsWorker subscribes to both Projects::ProjectArchivedEvent and Namespaces::Groups::GroupArchivedEvent and dispatches on the event class, the same shape as the existing Vulnerabilities::ProcessTransferEventsWorker. It's the only new worker — an earlier revision of this MR had four classes (a service plus three workers, one of them a per-project fan-out worker for group archival); this collapses to one queue instead of three. The group branch narrows with Gitlab::Database::NamespaceProjectIdsEachBatch, whose resolver: reduces each 100-project batch to only projects that actually have security_scan_profiles_projects rows — scan profiles are opt-in, so that's a small fraction of any group. That narrowing is what removes the need for the fan-out worker: archiving a group no longer enqueues one Sidekiq job per project, it runs a couple of bounded update_alls per batch instead.
  • The writes moved onto Security::ScanProfileProject as class methods — attached_project_ids, sync_archived_for, update_archived_for — per the reusing-abstractions guidance that update_all belongs in model class methods, not in a service.
    • sync_archived_for computes each project's own self_or_ancestors_archived? rather than applying one value to the whole subtree, because GroupArchivedEvent fires on archive and unarchive and carries no state flag beyond group_id. The group archive services flatten descendant state today (Group#unarchive_descendants!), but Namespaces::Stateful is moving toward preserving it, at which point a single shared value would be silently wrong.
    • update_archived_for carries a where.not(archived: archived) predicate, so retries and no-op events write zero rows.

Also in this MR, a drive-by N+1 fix: ProjectAttachService#archived_flags now preloads :project_namespace alongside the namespace-settings chain. Project#self_or_ancestors_archived? checks self_archived? first (archived → super && !self_deletion_scheduled?), and Ruby short-circuits on &&, so this only bites for a project whose own archived column is already true — but such a project then reaches project_namespace via deletion_scheduled? and costs one extra query each, across a batch of up to MAX_PROJECTS (500). There's a regression spec that fails without the preload; the same preload (and spec) is in sync_archived_for.

ProcessArchivedEventsWorker is introduced here but is not subscribed to either event yet — that's deliberate. Registering the store.subscribe calls in the same release would let the event store enqueue jobs for a class that Sidekiq nodes still running the previous revision can't load, which violates GitLab's multi-version compatibility guidance. The subscriptions land in the follow-up MR once this one is fully deployed.

This MR is the first in a series of two MRs. See the second one.

Changelog: fixed
EE: true

Related issue

Exclude archived projects from security scan pr... (#629984) • rossfuhrman • 19.6

Database changes

No migration in this MR. The archived column and the composite index idx_scan_profiles_projects_on_profile_id_and_archived on (security_scan_profile_id, archived) already merged in Add archived column to security_scan_profiles_p... (!256529 - merged) • rossfuhrman • 19.5. A post-deploy backfill for existing rows is in flight separately in Backfill security_scan_profiles_projects archiv... (!256629 - merged) • Gal Katz • 19.5. Until that backfill runs, pre-existing rows keep the column default of false, so their count is unchanged rather than wrong in a new way.

Query plans

Note: The idx_scan_profiles_projects_on_profile_id_and_archived which was added with the previous MR is not present on postgres.ai, so I manually ran the adding of the index in the postgres.ai session (re-adding it after resetting the database before running the queries below).

Security::ScanProfile.project_counts_by_id

Raw SQL 
explain SELECT COUNT(*) AS count_all, security_scan_profile_id 
  FROM "security_scan_profiles_projects" 
  WHERE "security_scan_profiles_projects"."security_scan_profile_id" IN (70472537, 65339032, 80141160) 
  AND "security_scan_profiles_projects"."archived" = FALSE 
  GROUP BY "security_scan_profiles_projects"."security_scan_profile_id"
Query plan

Full details here

GroupAggregate  (cost=0.29..6.98 rows=3 width=16) (actual time=0.028..0.029 rows=0 loops=1)
   Group Key: security_scan_profiles_projects.security_scan_profile_id
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using idx_scan_profiles_projects_on_profile_id_archived on public.security_scan_profiles_projects  (cost=0.29..6.93 rows=3 width=8) (actual time=0.027..0.028 rows=0 loops=1)
         Index Cond: (security_scan_profiles_projects.security_scan_profile_id = ANY ('{70472537,65339032,80141160}'::bigint[]))
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off'
Query ID: 3946352289648378466

Expect this to be served by the composite index idx_scan_profiles_projects_on_profile_id_and_archived; security_scan_profiles_projects is a small table, and the previous version of this query was identical minus the archived = FALSE predicate.

Security::ScanProfileProject.update_archived_for

Raw SQL 
UPDATE security_scan_profiles_projects
SET archived = TRUE, updated_at = '2026-09-29 12:00:00'
WHERE security_scan_profiles_projects.project_id IN (70472537, 65339032, 80141160)
  AND security_scan_profiles_projects.archived <> TRUE;
Query plan

Full details here

At most two of these UPDATEs run per batch of 100 projects (one for the archived partition, one for unarchived), each bounded to 100 × MAX_PROFILES_PER_PROJECT (10) = 1000 rows and served by the existing unique index index_security_scan_profiles_projects_on_unique_project_profile (project_id, security_scan_profile_id).

 Update on public.security_scan_profiles_projects  (cost=0.42..13.23 rows=0 width=0) (actual time=6.737..6.738 rows=0 loops=1)
   Buffers: shared hit=135 read=19 dirtied=18
   WAL: records=46 fpi=17 bytes=119950
   I/O Timings: read=6.169 write=0.000
   ->  Index Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects  (cost=0.42..13.23 rows=5 width=15) (actual time=1.569..1.800 rows=9 loops=1)
         Index Cond: (security_scan_profiles_projects.project_id = ANY ('{70472537,65339032,80141160}'::bigint[]))
         Index Searches: 3
         Filter: (NOT security_scan_profiles_projects.archived)
         Buffers: shared hit=12 read=6
         I/O Timings: read=1.696 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 2223146672803217753

Security::ScanProfileProject.attached_project_ids

Raw SQL 
SELECT DISTINCT security_scan_profiles_projects.project_id
FROM security_scan_profiles_projects
WHERE security_scan_profiles_projects.project_id IN (84957626, 84957627, 84957628, 84957629, 84957630, 84957631, 84957632, 84957633, 84957634, 84957635, 84957636, 84957637, 84957638, 84957639, 84957640, 84957641, 84957642, 84957643, 84957644, 84957645, 84957646, 84957647, 84957648, 84957649, 84957650, 84957651, 84957652, 84957653, 84957654, 84957655, 84957656, 84957657, 84957658, 84957659, 84957660, 84957661, 84957662, 84957663, 84957664, 84957665, 84957666, 84957667, 84957668, 84957669, 84957670, 84957671, 84957672, 84957673, 84957674, 84957675, 84957676, 84957677, 84957678, 84957679, 84957680, 84957681, 84957682, 84957683, 84957684, 84957685, 84957686, 84957687, 84957688, 84957689, 84957690, 84957691, 84957692, 84957693, 84957694, 84957695, 84957696, 84957697, 84957698, 84957699, 84957700, 84957701, 84957702, 84957703, 84957704, 84957705, 84957706, 84957707, 84957708, 84957709, 84957710, 84957711, 84957712, 84957713, 84957714, 84957715, 84957716, 84957717, 84957718, 84957719, 84957720, 84957721, 84957722, 84957723, 84957724, 84957725)
LIMIT 100;
Query plan

Full details here

Also bounded to a 100-project batch and served by the same unique index.

 Limit  (cost=0.42..125.13 rows=100 width=8) (actual time=1.160..1.171 rows=17 loops=1)
   Buffers: shared hit=1 read=3
   I/O Timings: read=1.053 write=0.000
   ->  Unique  (cost=0.42..202.45 rows=162 width=8) (actual time=1.158..1.167 rows=17 loops=1)
         Buffers: shared hit=1 read=3
         I/O Timings: read=1.053 write=0.000
         ->  Index Only Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects  (cost=0.42..202.05 rows=162 width=8) (actual time=1.155..1.158 rows=17 loops=1)
               Index Cond: (security_scan_profiles_projects.project_id = ANY ('{84957626,84957627,84957628,84957629,84957630,84957631,84957632,84957633,84957634,84957635,84957636,84957637,84957638,84957639,84957640,84957641,84957642,84957643,84957644,84957645,84957646,84957647,84957648,84957649,84957650,84957651,84957652,84957653,84957654,84957655,84957656,84957657,84957658,84957659,84957660,84957661,84957662,84957663,84957664,84957665,84957666,84957667,84957668,84957669,84957670,84957671,84957672,84957673,84957674,84957675,84957676,84957677,84957678,84957679,84957680,84957681,84957682,84957683,84957684,84957685,84957686,84957687,84957688,84957689,84957690,84957691,84957692,84957693,84957694,84957695,84957696,84957697,84957698,84957699,84957700,84957701,84957702,84957703,84957704,84957705,84957706,84957707,84957708,84957709,84957710,84957711,84957712,84957713,84957714,84957715,84957716,84957717,84957718,84957719,84957720,84957721,84957722,84957723,84957724,84957725}'::bigint[]))
               Heap Fetches: 0
               Index Searches: 1
               Buffers: shared hit=1 read=3
               I/O Timings: read=1.053 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -7449373488854007331

Both statements above are single-schema (gitlab_sec); the projects read that feeds them (in sync_archived_for and in NamespaceProjectIdsEachBatch) is a separate statement on gitlab_main_org, outside any transaction, so there's no cross-database modification.

How to set up and validate locally

All steps run in rails console against a group you already have locally, with a subgroup.

  1. Attach a scan profile to two active projects and check the count:

    user    = User.find_by_username('root')
group   = Group.find_by_full_path('<your-group>')
subgroup = group.children.first
profile = group.security_scan_profiles.not_deleted.first
project_a, project_b = group.all_projects.first(2)

Security::ScanProfiles::ProjectAttachService.execute(
  profile: profile, current_user: user, projects: [project_a, project_b]
)

Security::ScanProfile.project_counts_by_id([profile.id])
# => { profile.id => 2 }

  2. Archive one of them, drive the worker with the real event, and check the count again:

    Projects::ArchiveService.new(project: project_a, current_user: user).execute

event = Projects::ProjectArchivedEvent.new(data: {
  project_id: project_a.id, namespace_id: group.id, root_namespace_id: group.root_ancestor.id
})
Security::ScanProfiles::ProcessArchivedEventsWorker.new.handle_event(event)

Security::ScanProfile.project_counts_by_id([profile.id])
# => { profile.id => 1 }

  3. Attach the profile to an already-archived project and confirm the new row is written as archived straight away, with no worker run:

    archived_project = group.all_projects.third
Projects::ArchiveService.new(project: archived_project, current_user: user).execute

Security::ScanProfiles::ProjectAttachService.execute(
  profile: profile, current_user: user, projects: [archived_project.reset]
)

Security::ScanProfileProject
  .find_by(security_scan_profile_id: profile.id, project_id: archived_project.id)
  .archived
# => true

  4. Archive the group and confirm only the projects with a profile attached are touched:

    subgroup_project = subgroup.all_projects.first # no profile attached

Namespaces::Groups::ArchiveService.new(group, user).execute

group_event = Namespaces::Groups::GroupArchivedEvent.new(data: {
  group_id: group.id, root_namespace_id: group.root_ancestor.id
})
Security::ScanProfiles::ProcessArchivedEventsWorker.new.handle_event(group_event)

Security::ScanProfileProject.by_project_id(project_b.id).pluck(:archived)
# => [true]
Security::ScanProfileProject.by_project_id(subgroup_project.id).exists?
# => false - no row, so the resolver never loaded that project

  5. Confirm the GraphQL field agrees:

    {
  group(fullPath: "<your-group>") {
    availableSecurityScanProfiles {
      name
      projectCount
    }
  }
}

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.

🤖 Generated with Claude Code

Edited by rossfuhrman

Merge request reports