Dual write scan result policy violation details

What does this MR do and why?

Dual-writes scan result policy violation details into the new scan_result_policy_violation_details table alongside the existing violation_data JSONB blob on scan_result_policy_violations.

This MR adds a new CreateViolationDetailsService that is called after each violation upsert (gated by the dual_write_scan_result_policy_violation_details feature flag) and writes one normalized row per violation per rule type into scan_result_policy_violation_details.

Three rule types are handled:

• scan_finding — stores finding UUIDs split by newly_detected / previously_existing state
• license_scanning — stores denied license names with their dependency arrays (truncated to MAX_ARRAY_LIMIT = 10) and total dependency count in metadata
• any_merge_request — stores unsigned commit SHAs (truncated) and total commit count in metadata

Metadata counts (total dependencies per license, total unsigned commits) are propagated from the evaluating services through UpdateViolationsService#add_violation_detail_metadata → PolicyRuleEvaluationService#add_detail_metadata! and written atomically alongside the violation detail rows.

Background

Previously, structured violation details were stored only as a JSONB blob in scan_result_policy_violations.violation_data where the violation data is capped by MAX_VIOLATIONS limit. This is limiting as we want to render all the findings in reports tab.

scan_result_policy_violation_details is a new normalized table that stores one row per violation per policy rule type. It is the storage layer that will back the upcoming GraphQL violationDetails field. This MR is the runtime write path; the backfill of historical data is handled separately in #597917.

References

Issue: #597916

Rollout issue: #603756

POC MR: !232424

Database Queries

for_merge_request_and_approval_policy_rules scope

SELECT id, project_id, approval_policy_rule_id
FROM scan_result_policy_violations
WHERE merge_request_id = 298
  AND approval_policy_rule_id IN (219, 221, 217, 218, 220);

explain: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/53472/commands/155470

for_merge_request_and_scan_result_policies scope

Uses the new for_merge_request_and_scan_result_policies scope on ScanResultPolicyViolation.

SELECT id, project_id, scan_result_policy_id
FROM scan_result_policy_violations
WHERE merge_request_id = 298
  AND scan_result_policy_id IN (1380, 1383, 1382, 1381, 1384);

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/53472/commands/155471

Delete — atomic replace of existing detail rows

Uses the new for_violations scope on ScanResultPolicyViolationDetail.

DELETE FROM scan_result_policy_violation_details
WHERE scan_result_policy_violation_id IN (4696, 4697, 4698, 4699, 4700);

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/53472/commands/155472

Insert — bulk write of new detail rows:

INSERT INTO scan_result_policy_violation_details
  (scan_result_policy_violation_id, project_id, created_at, updated_at,
   policy_rule_type, finding_state, finding_uuid, license_name,
   dependencies, commit_shas, metadata)
VALUES
  (4696, 58, NOW(), NOW(), 0, 0, 'fe247729-a99a-55d5-ae65-8155ecba9e39', NULL, '{}', '{}', '{}'),
  (4696, 58, NOW(), NOW(), 0, 1, 'd42fd6ca-aea2-5f48-81ff-751570dc01e2', NULL, '{}', '{}', '{}'),
...

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/53472/commands/155473

How to set up and validate locally

1. Enable the feature flag:

   bin/rails runner "Feature.enable(:dual_write_scan_result_policy_violation_details)"

2. Create a new project and navigate to Secure > Policies. Create the following Merge request approval policy:

Test Setup

.gitlab/security-policies/policy.yml

approval_policy:
- name: Dual write test
  description: ''
  enabled: true
  enforcement_type: enforce
  rules:
  - type: scan_finding
    scanners:
    - secret_detection
    vulnerabilities_allowed: 0
    severity_levels: []
    vulnerability_states:
    - newly_detected
    branch_type: protected
  actions:
  - type: require_approval
    approvals_required: 1
    role_approvers:
    - owner
  fallback_behavior:
    fail: open

.gitlab-ci.yml

image: busybox:latest
include:
  - template: 'Jobs/Secret-Detection.gitlab-ci.yml'

3. Create an MR targeting a protected branch with the following file to trigger secret detection:

   env.sample

   AWS_TOKEN=AKIAZYONPI3G4JNCCWGQ

   Observation: The pipeline runs and the policy bot comment appears.

4. After the pipeline completes, verify rows were written to scan_result_policy_violation_details:

   # In rails console
   Security::ScanResultPolicyViolationDetail.last(5).map { |d| [d.policy_rule_type, d.finding_uuid, d.finding_state] }

   Observation: Rows exist with policy_rule_type: "scan_finding" and the finding UUID from the secret detection result.

For testing other policy types/violations - check the policies and setup in this example MR.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.