# Capability coverage for Triage and Remediation profiles ## Requirements The current designs require: * R1. Capability status (`on`/`off`) per project. * R2. Capability coverage per group, on for `x` of `y` projects. * R3. Filtering abilities: show projects with or without capability `x` under group `g`. Archived projects stay out of the counts and the list (records can stay, with archived flag). See design: #### Inventory: {width=212 height=238} And: {width=387 height=326} #### Group level: {width=409 height=259} ## Background The `Triage and Remediation` profile, unlike the others, does not map to an actual scan type. It carries a set of capabilities, one per trigger: (`sbom_ingested`, `sast_false_positive`, `sast_vulnerability_resolution`, `secret_detection_false_positive`, `vulnerability_enrichment`, and possibly more soon). We want coverage per capability, not just per profile. Nothing tracks it today. Attaching the profile enqueues `ScheduleSettingChangedUpdateWorker(ids, 'triage_and_remediation')`, which reaches a dispatch fork with no branch for that type and returns at `return unless supported?`. The job runs and writes nothing. Capability state has `run_mode`: `auto` or `manual`, so a capability may never fire on its own. The exception is `sbom_ingested`, which has no `run_mode`. ## Option 1: new projection table **TL;DR:** A new table for capability data, one row per project. Clear separation, and the feature ships on its own schedule. The cost is rebuilding infra that already exists next door, plus an extra lookup per row when filtering the project list. A new `gitlab_sec` table with a row per project, with **one column per capability**, `traversal_ids`, `archived`, with a covering index on `(traversal_ids, project_id) INCLUDE (…) WHERE NOT archived`. R1 and R2 read it directly. R3 filters the inventory list with a correlated `EXISTS` against it, the pattern `by_security_attributes` already uses for `project_to_security_attributes`, so keyset pagination is unaffected. Covering index on `(traversal_ids, project_id) INCLUDE (<capability columns>) WHERE NOT archived`, so the group aggregate is an index-only scan. R1 and R2 read this table directly. R3 filters the inventory list with a correlated `EXISTS` against it, the pattern `by_security_attributes` already uses for `project_to_security_attributes`, so keyset pagination is unaffected. Steps: 1. Migration for the table and covering index. 2. Recalculate service and batched worker, wired to the write hooks. 3. Maintenance hooks: `traversal_ids` on project and group transfer, `archived` on project and group archive, loose FK on project delete. 4. Backfill for attached projects. 5. GraphQL: project field (R1), group aggregate (R2), inventory filter argument (R3). | Pros | Cons | |---|---| | `Capability` axis is isolated | R3 becomes a semi-join, costing an index probe per candidate row instead of a residual filter | | Rows exist only for attached projects, so nothing changes about which projects appear in the inventory | Rebuilds `traversal_ids`, `archived`, transfer and archive maintenance the inventory table already has | | Schema changes need no agreement with the inventory table's writers | New table, backfill, loose FK and reconciliation sweep, plus a second preload for R1 | Effort: 6/10 ## Option 2: one column per capability **TL;DR:** Add a **column per capability** to the table the inventory already reads. Every new capability costs another migration on a busy table. One `smallint` column per capability added to the covering index. R1, R2 and R3 all become existing query shapes. Steps: 1. Migration for the columns, plus a post-migration to swap the covering index. 1. Recompute service and batched worker. 1. Insert-shaped backfill for the projects that still have no row. 1. GraphQL: the same three surfaces. | Pros | Cons | |---|---| | Most readable and most idiomatic, since every sibling column is already an enum of this shape | Every capability added needs its own new column (with migration and a backfill) on a hot shared table each time | Couples the capability write cadence to the analyzer write path | `traversal_ids`, `archived`, transfer and archive maintenance come free | Covering index grows with the capability count: +10% at 5, +20% at 10, +50% at 20 (estimation) | | R3 is a residual filter on the index tuple, and combined filters stay one scan | | Effort: 5/10 ## Option 3: two bitmask columns **TL;DR:** Pack the capabilities into **two integer columns, one bit each**. Reads like option 2 and costs the same, but new capabilities are free: no migration, no backfill, no index change. The price is that the columns mean nothing without helper code. ```ruby add_column :security_inventory_filters, :triage_capabilities_on, :integer, default: 0, null: false add_column :security_inventory_filters, :triage_capabilities_auto, :integer, default: 0, null: false ``` Two parallel masks keep the three states without packing two bits per capability. Bit positions are an explicit frozen, append-only map, never derived from enum ordering, with a spec asserting existing values never change. <details> <summary> Example </summary> ```ruby scope :by_capability, ->(name, auto_only: false) do mask = 1 << TRIAGE_CAPABILITY_BITS.fetch(name.to_sym) where("#{auto_only ? :triage_capabilities_auto : :triage_capabilities_on} & ? > 0", mask) end ``` </details> Steps: 1. Migration for the two columns and the check constraint, then swap the index like option 2. 2. `TRIAGE_CAPABILITY_BITS`, named scopes and a decode helper on `Security::InventoryFilter`. 3. Steps 2 to 5 of option 2, unchanged. | Pros | Cons | |---|---| | A new capability costs one bit: no migration, no backfill, no index change | Opaque. `mask & 4 > 0` is unreadable next to `sast_false_positive: 2`, and non-idiomatic here, so harder to maintain and understand | | Index width stays flat at +10% where option 2 reaches +50% at 20 capabilities | Two columns have to stay consistent, and once a bit position ships it can never changed | | Read cost identical to option 2, and the free maintenance of option 2 still applies | R1 needs a decode helper, and per-capability values such as a severity threshold would force a redesign | Effort: 4/10 ## Open question 1. Does "covered" in the UI mean on, or `profile attached` and `auto mode`? The `conservative` preset attaches capabilities as `manual`. This blocks the GQL and backfill work, not the migration.