Expose security inventory filters based counters

What does this MR do and why?

Adds a securityPostureCounters field to Group type that returns aggregate counts of unarchived projects. The field is behind inventory_filter_aggregate_booleans feature flag.

The counters are derived from three boolean columns (has_scanners, has_failed_or_warning, has_stale) that were added to security_inventory_filters in a prior MR.

To support this efficiently, this MR adds a new index:

(traversal_ids, project_id) INCLUDE (has_scanners, has_failed_or_warning, has_stale) WHERE NOT archived

project_id is included as a key column because the same index should support filtering projects by these boolean columns and and returning project_ids ordered by (traversal_ids, project_id) for keyset pagination under NamespaceSecurityProjectsResolver (next MR)

Changelog: added
EE: true

How to set up and validate locally

1. Create a root group root with a few projects under it (some with security scans, some without).

2. Enable the feature flag:

   Feature.enable(:inventory_filter_aggregate_booleans)

3. Force an update for the boolean columns for existing rows (backfill MR, logic MR for reference):

   Security::InventoryFilter.update_all(<<~SQL)
     has_scanners = GREATEST(
       sast, sast_advanced, sast_iac, dast,
       dependency_scanning, coverage_fuzzing, api_fuzzing,
       cluster_image_scanning, secret_detection,
       secret_detection_secret_push_protection,
       secret_detection_pipeline_based, container_scanning,
       container_scanning_for_registry,
       container_scanning_pipeline_based
     ) > 0,
     has_failed_or_warning = 2 IN (
       sast, sast_advanced, sast_iac, dast,
       dependency_scanning, coverage_fuzzing, api_fuzzing,
       cluster_image_scanning, secret_detection,
       secret_detection_secret_push_protection,
       secret_detection_pipeline_based, container_scanning,
       container_scanning_for_registry,
       container_scanning_pipeline_based
     )
   SQL

4. Verify the values look correct:

   Security::InventoryFilter.where(has_scanners: true).count
   Security::InventoryFilter.where(has_failed_or_warning: true).count

5. Query the GraphQL field:

   query {
     group(fullPath: "<root.full_part>") {
       securityPostureCounters {
         withScanners
         withFailures
         withStale
       }
     }
   }

6. Verify the returned counts match the values from step 4.

7. Disable the feature flag and confirm the field returns null:

   Feature.disable(:inventory_filter_aggregate_booleans)

Query plan

SQL

The resolver runs a single aggregate query:

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
FROM
    "security_inventory_filters"
WHERE (security_inventory_filters.traversal_ids >= '{62}'
    AND '{63}' > security_inventory_filters.traversal_ids)
AND "security_inventory_filters"."archived" = FALSE
LIMIT 1

Plan for namespace = 4249178

Warmer cache:

See this

 Limit  (cost=13042.09..13042.10 rows=1 width=24) (actual time=24.652..24.653 rows=1 loops=1)
   Buffers: shared hit=34024
   ->  Aggregate  (cost=13042.09..13042.10 rows=1 width=24) (actual time=24.650..24.651 rows=1 loops=1)
         Buffers: shared hit=34024
         ->  Index Only Scan using idx_sec_inv_filters_traversal_project_aggregate on public.security_inventory_filters  (cost=0.56..12428.41 rows=81824 width=3) (actual time=0.039..22.433 rows=41928 loops=1)
               Index Cond: ((security_inventory_filters.traversal_ids >= '{4249178}'::bigint[]) AND (security_inventory_filters.traversal_ids < '{4249179}'::bigint[]))
               Heap Fetches: 10436
               Buffers: shared hit=34024
Settings: effective_cache_size = '338688MB', jit = 'off', random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4'

Colder cache:

See this

 Limit  (cost=13042.09..13042.10 rows=1 width=24) (actual time=401.210..401.211 rows=1 loops=1)
   Buffers: shared hit=26335 read=7689
   ->  Aggregate  (cost=13042.09..13042.10 rows=1 width=24) (actual time=401.208..401.209 rows=1 loops=1)
         Buffers: shared hit=26335 read=7689
         ->  Index Only Scan using idx_sec_inv_filters_traversal_project_aggregate on public.security_inventory_filters  (cost=0.56..12428.41 rows=81824 width=3) (actual time=0.561..398.491 rows=41928 loops=1)
               Index Cond: ((security_inventory_filters.traversal_ids >= '{4249178}'::bigint[]) AND (security_inventory_filters.traversal_ids < '{4249179}'::bigint[]))
               Heap Fetches: 10436
               Buffers: shared hit=26335 read=7689
Settings: seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', random_page_cost = '1.5', work_mem = '100MB'

Plan for namespace = 9970

See this

 Limit  (cost=3.59..3.60 rows=1 width=24) (actual time=151.585..151.586 rows=1 loops=1)
   Buffers: shared hit=3305 read=606
   ->  Aggregate  (cost=3.59..3.60 rows=1 width=24) (actual time=151.583..151.584 rows=1 loops=1)
         Buffers: shared hit=3305 read=606
         ->  Index Only Scan using idx_sec_inv_filters_traversal_project_aggregate on public.security_inventory_filters  (cost=0.56..3.58 rows=1 width=3) (actual time=9.218..151.151 rows=6816 loops=1)
               Index Cond: ((security_inventory_filters.traversal_ids >= '{9970}'::bigint[]) AND (security_inventory_filters.traversal_ids < '{9971}'::bigint[]))
               Heap Fetches: 825
               Buffers: shared hit=3305 read=606
Settings: work_mem = '100MB', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', random_page_cost = '1.5'

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 #596691