[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
- Create a new denormalized table in the sec-db based on the schema in this comment by @gkatz1
- Ensure all new data syncs to the denormalized table
- Backfill the denormalized table with existing data
- Implement a new GraphQL filter that queries the denormalized table for filtered
project_ids, then uses those IDs to query theprojectstable
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