Security Inventory Filters do not search projects with no scanners enabled
The Security Inventory filters will never return results for projects that have no scanners enabled because the supporting table only holds records for projects that use some of our security products. This breaks essential security workflows where users need to filter to identify gaps in their security coverage.
The root cause is that the security_inventory_filters table only contains ~450K records for projects that have run security scans at some point, leaving out most of the ~45M total projects. Projects get added to this table only after their first security pipeline runs or first security-setting-based feature is toggled on.
IMO, we might meet a similar challenge in the near future with filtering based on security categories and attributes as well. I'd like to make sure we are taking the best approach with this fix.
Solutions
Option 1: Backfill all projects - Add records for all 45M projects to the existing security_inventory_filters table. This creates a 100x table growth where most records contain only default/null values, with project name and archive status. The sparse nature means storage overhead, slower query performance, and ongoing maintenance complexity.
Option 2: Cross-database sync - Create a projects table replica in the sec_db containing essential project metadata (name, namespace_id, archived status, etc.). This approach eliminates sparse data issues while enabling efficient joins between project attributes and security data. It would also support future security features that need project context without reconstructing information across multiple tables like we might do today.
Questions
- Should we optimize only for EE projects? Licenses change over time, so this might not be the best approach.
- Given trade-offs between filtering capability and storage/performance costs, which approach works best long-term?
- How much time will it take to implement Option 2 compared to the relatively quick fix offered by Option 1?
- Any other approaches that you can think of?