Expose triage and remediation capabilities coverage types

What does this MR do and why?

This MR adds a GraphQL surface for triage & remediation coverage. It exposes coverage data for a namespace's security projects, a group's coverage counters, and a project's full coverage matrix, all backed by the bitmask columns.

Changelog: added
EE: true

New types

• TriageAndRemediationCapabilityType - one trigger's coverage state: trigger type, enabled, run mode. Used as a list item inside TriageAndRemediationCoverageType.
• TriageAndRemediationCoverageType - wraps the full per-project coverage matrix. Used by Project.triageAndRemediationCoverage.
• TriageAndRemediationCoverageCountersType - enabled and automatic counts for one trigger type. Used by Group.triageAndRemediationCoverageCounters.
• TriageAndRemediationCoverageCountersResolver - resolver backing the counters field, takes a triggerType argument. Used by Group.triageAndRemediationCoverageCounters.

Query plans

wide scope :by_triage_trigger - Used with projects.

Raw SQL

SELECT
    "security_inventory_filters".*
FROM
    "security_inventory_filters"
WHERE (triage_capabilities_on & 2 > 0)
    AND archived = FALSE

Query plan

See - https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/58742/commands/163773

 Index Scan using idx_sec_inv_filters_traversal_proj_covering_triage on public.security_inventory_filters  (cost=0.56..1224818.45 rows=5299078 width=139) (actual time=73616.747..73616.779 rows=0 loops=1)
   Index Searches: 1
   Filter: (((security_inventory_filters.triage_capabilities_on)::integer & 2) > 0)
   Rows Removed by Filter: 15894640
   Buffers: shared hit=8068812 read=5355976 dirtied=109 written=6
   WAL: records=106 fpi=106 bytes=862634
   I/O Timings: read=48424.626 write=11.861
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 8523643767975420306

ProjectsFinderService with by_triage_trigger

Raw SQL

SELECT
    "security_inventory_filters"."traversal_ids",
    "security_inventory_filters"."project_id"
FROM
    "security_inventory_filters"
WHERE
    "security_inventory_filters"."archived" = FALSE
    AND (security_inventory_filters.traversal_ids >= '{9970}'
        AND '{9971}' > security_inventory_filters.traversal_ids)
    AND (triage_capabilities_on & 2 > 0)

Query plan

See - https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/58965/commands/164214

 Index Only Scan using idx_sec_inv_filters_traversal_proj_covering_triage on public.security_inventory_filters  (cost=0.56..2404.35 rows=4833 width=45) (actual time=1128.435..1128.437 rows=0 loops=1)
   Index Cond: ((security_inventory_filters.traversal_ids >= '{9970}'::bigint[]) AND (security_inventory_filters.traversal_ids < '{9971}'::bigint[]))
   Heap Fetches: 2491
   Index Searches: 1
   Filter: (((security_inventory_filters.triage_capabilities_on)::integer & 2) > 0)
   Rows Removed by Filter: 8392
   Buffers: shared hit=4892 read=2342 dirtied=5
   WAL: records=5 fpi=5 bytes=40253
   I/O Timings: read=1108.782 write=0.000
Settings: jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB'
Query ID: 700961816179273774

triage_coverage_counts_for

Raw SQL

SELECT
    COUNT(*) FILTER (WHERE has_scanners) AS with_scanners,
    COUNT(*) FILTER (WHERE has_failed_or_warning) AS with_failures,
    COUNT(*) FILTER (WHERE has_stale) AS with_stale,
    COUNT(*) FILTER (WHERE triage_capabilities_on <> 0) AS with_triage_coverage
FROM
    "security_inventory_filters"
WHERE (security_inventory_filters.traversal_ids >= '{9970}'
    AND '{9971}' > security_inventory_filters.traversal_ids)
AND "security_inventory_filters"."archived" = FALSE

Query plan

See - https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/58965/commands/164220

 Aggregate  (cost=2476.85..2476.86 rows=1 width=32) (actual time=6.025..6.027 rows=1 loops=1)
   Buffers: shared hit=7230
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using idx_sec_inv_filters_traversal_proj_covering_triage on public.security_inventory_filters  (cost=0.56..2295.60 rows=14500 width=5) (actual time=0.029..5.495 rows=8392 loops=1)
         Index Cond: ((security_inventory_filters.traversal_ids >= '{9970}'::bigint[]) AND (security_inventory_filters.traversal_ids < '{9971}'::bigint[]))
         Heap Fetches: 2489
         Index Searches: 1
         Buffers: shared hit=7230
         I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'
Query ID: 1278399473802197973

aggregate_posture_counters_for

Raw SQL

SELECT
    COUNT(*) FILTER (WHERE triage_capabilities_on & 2 > 0) AS enabled_count,
    COUNT(*) FILTER (WHERE triage_capabilities_auto & 2 > 0) AS auto_count
FROM
    "security_inventory_filters"
WHERE (security_inventory_filters.traversal_ids >= '{9970}'
    AND '{9971}' > security_inventory_filters.traversal_ids)
AND "security_inventory_filters"."archived" = FALSE

Query plan

See - https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/58965/commands/164223

 Aggregate  (cost=2585.60..2585.61 rows=1 width=16) (actual time=5.167..5.168 rows=1 loops=1)
   Buffers: shared hit=7230
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using idx_sec_inv_filters_traversal_proj_covering_triage on public.security_inventory_filters  (cost=0.56..2295.60 rows=14500 width=4) (actual time=0.031..4.639 rows=8392 loops=1)
         Index Cond: ((security_inventory_filters.traversal_ids >= '{9970}'::bigint[]) AND (security_inventory_filters.traversal_ids < '{9971}'::bigint[]))
         Heap Fetches: 2489
         Index Searches: 1
         Buffers: shared hit=7230
         I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 3764993690565933782

How to set up and validate locally

Attach a triage and remediation profile via GQL

mutation {
  securityScanProfileAttach(input: {
    securityScanProfileId: "gid://gitlab/Security::ScanProfile/triage_and_remediation_standard"
    projectIds: ["gid://gitlab/Project/<project_id>"]
  }) {
    errors
  }
}

triage_and_remediation_conservative also works as the profile ID,instead of triage_and_remediation_standard.

1: Filter namespace security projects by trigger type

Validates the new triggerType argument on NamespaceSecurityProjectsResolver:

query {
  namespaceSecurityProjects(namespaceId: "gid://gitlab/Group/<group_id>", triggerType: SAST_FALSE_POSITIVE) {
    nodes {
      fullPath
      triageAndRemediationCoverage {
        capabilities { triggerType enabled runMode }
      }
    }
  }
}

2: How many projects in a group have any triage and remediation trigger enabled

Validates the new withTriageCoverage field on SecurityPostureCounters.

query {
  group(fullPath: "<group_path>") {
    securityPostureCounters {
      total
      withScanners
      withFailures
      withStale
      withTriageCoverage
    }
  }
}

3: How many projects have trigger X enabled, and how many have it set to auto run mode

Validates Group.triageAndRemediationCoverageCounters(triggerType:).

query {
  group(fullPath: "<group_path>") {
    triageAndRemediationCoverageCounters(triggerType: SBOM_INGESTED) {
      enabledCount
      autoCount
    }
  }
}

4: Full coverage for a project

Validates Project.triageAndRemediationCoverage.

query {
  project(fullPath: "<project_path>") {
    triageAndRemediationCoverage {
      capabilities {
        triggerType
        enabled
        runMode
      }
    }
  }
}

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.