Skip to content

[Backend] Add project-level filtering capabilities

See [Backend] Spike: MVC: Investigate requirements ... (#524187 - closed) • Gal Katz • 18.5 for details and suggested approach.

High-Level Solution

  1. Create a new denormalized table in the sec-db based on the schema in this comment by @gkatz1
  2. Ensure all new data syncs to the denormalized table
  3. Backfill the denormalized table with existing data
  4. Implement a new GraphQL filter that queries the denormalized table for filtered project_ids, then uses those IDs to query the projects table

security_inventory_filters table schema:

CREATE TABLE security_inventory_filters (
  project_id bigint NOT NULL PRIMARY KEY,
  project_name varchar NOT NULL,
  archived boolean DEFAULT false NOT NULL,
  traversal_ids bigint[] DEFAULT ARRAY[]::bigint[] NOT NULL,
  
  -- Vulnerability counts (like in vulnerability_statistics)
  total integer DEFAULT 0 NOT NULL,
  critical integer DEFAULT 0 NOT NULL,
  high integer DEFAULT 0 NOT NULL,
  medium integer DEFAULT 0 NOT NULL,
  low integer DEFAULT 0 NOT NULL,
  unknown integer DEFAULT 0 NOT NULL,
  info integer DEFAULT 0 NOT NULL,
  
  -- Analyzer status columns (enum: not_configured=0, success=1, failed=2)
  sast integer DEFAULT 0 NOT NULL,
  sast_advanced integer DEFAULT 0 NOT NULL,
  sast_iac integer DEFAULT 0 NOT NULL,
  dast integer DEFAULT 0 NOT NULL,
  dependency_scanning integer DEFAULT 0 NOT NULL,
  coverage_fuzzing integer DEFAULT 0 NOT NULL,
  api_fuzzing integer DEFAULT 0 NOT NULL,
  cluster_image_scanning integer DEFAULT 0 NOT NULL,
  secret_detection_secret_push_protection integer DEFAULT 0 NOT NULL,
  container_scanning_for_registry integer DEFAULT 0 NOT NULL,
  secret_detection_pipeline_based integer DEFAULT 0 NOT NULL,
  container_scanning_pipeline_based integer DEFAULT 0 NOT NULL
);

CREATE INDEX idx_security_inventory_project_name_trigram ON security_inventory_filters USING gin(project_name gin_trgm_ops);
Edited by Gal Katz