This change is part of the following stack:

• ROX-33252: optimize ViolationsMultiplier to query what it needs #19115 ◀

_{Change managed by git-spice.}

Skipping CI for Draft Pull Request.
If you want CI signal for your change, please convert it to an actual PR.
You can still manually trigger a test run with /test all

Images are ready for the commit at 2994031.

To use with deploy scripts, first export MAIN_IMAGE_TAG=4.11.x-164-g2994031a4b.

Codecov Report

❌ Patch coverage is 81.25000% with 6 lines in your changes missing coverage. Please review.
✅ Project coverage is 49.52%. Comparing base (573923b) to head (2994031).
⚠️ Report is 10 commits behind head on master.

@@           Coverage Diff           @@
##           master   #19115   +/-   ##
=======================================
  Coverage   49.52%   49.52%           
=======================================
  Files        2672     2672           
  Lines      201665   201686   +21     
=======================================
+ Hits        99870    99895   +25     
+ Misses      94337    94334    -3     
+ Partials     7458     7457    -1     

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

I did ask Claude to evaluate the impact of this change with the assumption of 10K deployments that have on average 6 active violations. This is the quantification that Claude provided which is likely reasonable. Before this change this was a very expensive operation.

Summary

ViolationsMultiplier.Score() only needs policy_name and policy_severity from alerts but was deserializing full storage.Alert protobuf blobs via SearchListAlerts. The optimization replaces this with a column projection query using RunSelectRequestForSchema, fetching only the two needed columns directly from PostgreSQL.

Call Frequency

ViolationsMultiplier.Score() is the first multiplier in the deployment scoring pipeline and is called on:

• Every policy violation detection (build-time, deploy-time, runtime)
• Every deployment change (via sensor pipeline)
• Service account and process baseline changes
• Periodic batch reprocessing every 10 minutes (deduped)
• Full reprocessing of all deployments every 4 hours
• Up to 15 concurrent risk scoring operations per Central instance (semaphore-controlled)

Query Change

Old:

SELECT alerts.serialized FROM alerts WHERE deployment_id=$1 AND state=$2

• Transfers entire serialized protobuf blob per row
• Deserializes full storage.Alert proto (all nested messages)
• Converts to ListAlert (allocates another struct, copies ~11 fields)
• Caller reads 2 fields, discards the rest

New:

SELECT alerts.policy_name, alerts.policy_severity FROM alerts WHERE deployment_id=$1 AND state=$2

• Transfers only a varchar + integer per row
• No protobuf deserialization
• Scans directly into a 2-field struct

Assumptions

• Typical serialized storage.Alert protobuf: ~2-5KB (includes policy with all fields, violation messages, deployment metadata, timestamps, enforcement info, nested messages)
• The two projected columns: policy_name (varchar, ~30-50 bytes) + policy_severity (integer, 4 bytes) ~ 50 bytes per row
• Each UnmarshalVTUnsafe of a full Alert allocates the top-level object plus every nested message (Policy, Deployment, Violation list entries, ProcessIndicators, NetworkFlows, timestamps) — easily 20+ allocations per alert
• WHERE clause and index usage are identical (deployment_id and state are both btree indexed)

Scenario: 10,000 Deployments, 3 Violations Each (30,000 alerts)

Per Score() Call (1 deployment, 3 alerts)

Per Full Reprocessing Cycle (10,000 deployments)

Scenario: 10,000 Deployments, 6 Violations Each (60,000 alerts)

Per Score() Call (1 deployment, 6 alerts)

Per Full Reprocessing Cycle (10,000 deployments)

What This Doesn't Change

• WHERE clause and index usage are identical — query planning cost is the same
• SAC (Scoped Access Control) filtering still runs
• The scoring math (severityImpact, NormalizeScore, factor sorting) is unchanged and cheap
• Savings scale linearly with violation count per deployment