Exclude archived projects from security scan profile project count

Problem

The projectCount field on ScanProfileType (GraphQL) reports how many projects a security scan profile is attached to. The underlying query, Security::ScanProfile.project_counts_by_id, counts every row in security_scan_profiles_projects regardless of whether the attached project is archived. This inflates the count shown to users — archived projects should not count toward a profile's active project total.

The count is batch-loaded across many profiles on a list view, so any fix must stay a single, indexed, cheap query.

This is a backend follow-up to [Frontend] Scan profile drawer (milestone 19.5, group::security controls). The feature is not yet on master; it lives on branch ccharnolevsky/624191-Add-Project-Count-To-Scan-Profile, and all work here layers on top of that branch. projectCount is still experiment: { milestone: '19.5' }, so its underlying query can change without the GraphQL deprecation process.

Why the obvious fix does not work

The obvious fix is to join to projects and filter on archival, e.g.:

Security::ScanProfileProject.joins(:project).merge(Project.non_archived)

This is not possible. GitLab's database is decomposed by domain: security_scan_profiles_projects lives in the gitlab_sec database, while projects lives in gitlab_main_org. Cross-database JOINs between main, ci, sec, and geo are prohibited, so any query that needs both tables cannot be expressed as a single JOIN.

Proposed solution

Denormalize an archived boolean column onto security_scan_profiles_projects itself, kept in sync by event-driven workers reacting to project/group archival events. This is the same pattern already used for other gitlab_sec tables that need to filter on archival state: vulnerability_reads, sbom_occurrences, and analyzer_project_statuses all carry a denormalized archived boolean DEFAULT false NOT NULL column. With the column in place, the count becomes a single indexed query scoped to gitlab_sec — no cross-database access needed.

"Archived" is the effective state, project.self_or_ancestors_archived?, so a project whose ancestor group is archived also counts as archived — consistent with Sbom::Occurrence and Security::AnalyzersStatus::UpdateArchivedService.

Implementation steps

  • Add a regular schema migration on security_scan_profiles_projects (schema gitlab_sec): archived boolean DEFAULT false NOT NULL, plus a partial index (WHERE archived = false) to serve the count query; verify the table stays under the index limit and commit the regenerated db/structure.sql.
  • Add a post-deploy backfill that enqueues the per-project sync worker for existing rows' distinct project_ids, batched via bulk_perform_async_with_contexts; each worker recomputes self_or_ancestors_archived? idempotently.
  • Add an unarchived scope to Security::ScanProfileProject and use it in Security::ScanProfile.project_counts_by_id to fix the count query.
  • Update Security::ScanProfiles::ProjectAttachService to compute and write archived per project at insert time, so newly attached rows are correct immediately.
  • Add Security::ScanProfiles::UpdateArchivedService, mirroring Security::AnalyzersStatus::UpdateArchivedService, to update a project's security_scan_profiles_projects rows to its current effective archived state.
  • Add Security::ScanProfiles::ProcessArchivedEventsWorker subscribed to ::Projects::ProjectArchivedEvent.
  • Add Security::ScanProfiles::ProcessGroupArchivedEventsWorker (with a per-project fan-out worker) subscribed to ::Namespaces::Groups::GroupArchivedEvent, to cascade archival to descendant projects.
  • Register both subscriptions in ee/lib/gitlab/event_store/subscriptions/security_subscriptions.rb.
  • Regenerate the Sidekiq queue YAML (bin/rake gitlab:sidekiq:all_queues_yml:generate and sidekiq_queues_yml:generate) after adding the new workers.

Testing

  • Migration spec: column present, defaults to false, index exists.
  • Model specs: ScanProfileProject.unarchived scope; ScanProfile.project_counts_by_id excludes both self-archived and ancestor-group-archived projects.
  • GraphQL request spec: a profile attached to one active and one archived project returns projectCount: 1.
  • Attach service spec: attaching an already-archived project sets archived: true on the new row.
  • Worker specs: idempotent-worker shared examples; project-archived and group-archived events correctly flip archived on the affected join rows.
  • Event store spec: new subscriptions registered.

Related

  • Feature issue: [Frontend] Scan profile drawer — #624191 (closed)
  • Feature branch: ccharnolevsky/624191-Add-Project-Count-To-Scan-Profile
Edited by Gal Katz