Add bitmask columns to security_inventory_filters table

What does this MR do and why?

Adds triage_capabilities_on and triage_capabilities_auto bitmask columns to security_inventory_filters, one bit per triage-and-remediation trigger type, so per-project and per-namespace coverage can be read index-only instead of joining across the profile and trigger tables.

In addition to the new columns, there's a swap of the existing covering index idx_sec_inv_filters_traversal_proj_covering, adding the two new columns to its INCLUDE list.

This MR is the first in a series. The next MRs should add the model scopes and the recompute service that write these columns, the GQL surface, and a backfill.

Changelog: changed

[Backend] Capability coverage for Triage and Re... (#629986) • Gal Katz • 19.5

Query plans

Today, the replaced index supports filtering and counting projects and coverage statistics via the ::Security::InventoryFilter.aggregate_posture_counters_for(object)

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

Query plan - old index

See this

 Limit  (cost=3094.60..3094.61 rows=1 width=24) (actual time=1839.766..1839.768 rows=1 loops=1)
   Buffers: shared hit=4762 read=4840 dirtied=4103
   WAL: records=6479 fpi=4103 bytes=32724615
   I/O Timings: read=1785.960 write=0.000
   ->  Aggregate  (cost=3094.60..3094.61 rows=1 width=24) (actual time=1839.765..1839.765 rows=1 loops=1)
         Buffers: shared hit=4762 read=4840 dirtied=4103
         WAL: records=6479 fpi=4103 bytes=32724615
         I/O Timings: read=1785.960 write=0.000
         ->  Index Only Scan using idx_sec_inv_filters_traversal_proj_covering on public.security_inventory_filters  (cost=0.56..2987.11 rows=14331 width=3) (actual time=2.933..1838.098 rows=8360 loops=1)
               Index Cond: ((security_inventory_filters.traversal_ids >= '{9970}'::bigint[]) AND (security_inventory_filters.traversal_ids < '{9971}'::bigint[]))
               Heap Fetches: 5343
               Index Searches: 1
               Buffers: shared hit=4762 read=4840 dirtied=4103
               WAL: records=6479 fpi=4103 bytes=32724615
               I/O Timings: read=1785.960 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -1582329315239883889

Query plan - new index

See this

 Limit  (cost=3176.99..3177.00 rows=1 width=24) (actual time=9.810..9.811 rows=1 loops=1)
   Buffers: shared hit=8634 read=120
   I/O Timings: read=2.025 write=0.000
   ->  Aggregate  (cost=3176.99..3177.00 rows=1 width=24) (actual time=9.808..9.808 rows=1 loops=1)
         Buffers: shared hit=8634 read=120
         I/O Timings: read=2.025 write=0.000
         ->  Index Only Scan using idx_sec_inv_filters_traversal_proj_covering_triage on public.security_inventory_filters  (cost=0.56..3069.51 rows=14331 width=3) (actual time=0.493..9.308 rows=8360 loops=1)
               Index Cond: ((security_inventory_filters.traversal_ids >= '{9970}'::bigint[]) AND (security_inventory_filters.traversal_ids < '{9971}'::bigint[]))
               Heap Fetches: 4483
               Index Searches: 1
               Buffers: shared hit=8634 read=120
               I/O Timings: read=2.025 write=0.000
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off'
Query ID: -1582329315239883889

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.