Draft: POC for policy violations details storage

What does this MR do and why?

Introduces a normalized scan_result_policy_violation_details table and a new approvalPolicies GraphQL field on MergeRequest to expose structured, per-item violation data for security approval policies — replacing the opaque violation_data JSONB blob with queryable, typed rows.

Previously, all violation detail (which findings triggered a policy, which licenses, which unsigned commits) was buried inside a JSONB violation_data column on scan_result_policy_violations. Consumers had to parse the blob themselves and had no way to paginate, filter, or resolve individual findings through GraphQL.

This MR:

1. Adds scan_result_policy_violation_details — one row per violation item, typed by policy_rule_type (scan_finding, license_scanning, any_merge_request)
2. Populates those rows via CreateViolationDetailsService, called from UpdateViolationsService after each policy evaluation
3. Exposes them through a new approvalPolicies connection on MergeRequest that groups violations under their parent policy and provides a paginated violationDetails connection per violation
4. Resolves scanFinding items as a union of PipelineSecurityReportFinding (newly detected) or Vulnerability (previously existing) using batch loading

MR to create database table: !233059 (merged)

Background

Each scan_result_policy_violation record represents one policy rule that blocked a merge request. A single violation can have multiple detail items — for example, a scan_finding rule might be violated by 3 different critical findings.

The new scan_result_policy_violation_details table normalises these items:

Column	Purpose
policy_rule_type	scan_finding / license_scanning / any_merge_request
finding_uuid	UUID of the security finding (scan_finding only)
finding_state	newly_detected / previously_existing (scan_finding only)
license_name / dependencies	License name and affected packages (license_scanning only)
commit_shas	Unsigned commit SHAs (any_merge_request only)

GraphQL schema

mergeRequest {
  approvalPolicies {           # connection — null when FF disabled
    nodes {
      id name enforcementType
      violations {             # array, max 5 per policy
        id status dismissed
        errors { error reportType message }
        comparisonPipelines { reportType source target }
        violationDetails(policyRuleType: SCAN_FINDING) {  # paginated, filterable
          nodes {
            policyRuleType findingState
            scanFinding {
              __typename
              ... on PipelineSecurityReportFinding { uuid severity }
              ... on Vulnerability { id title }
            }
            licenseScanning { licenseName dependencies totalDependenciesCount }
            anyMergeRequest  { commitShas totalCommitShasCount }
          }
        }
      }
    }
  }
}

Gated behind the security_policy_violations_graphql_field feature flag.

References

Issue: #597625

How to set up and validate locally

1. Enable the feature flag:

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

2. Find a project with an open MR that has a failing scan_result policy. If none exists, seed one:

   # In rails console — find a violation with violation_data
   v = Security::ScanResultPolicyViolation.where.not(violation_data: nil).first
   puts v.merge_request.project.full_path
   puts "MR iid: #{v.merge_request.iid}"
   
   # Seed a detail row from the violation_data
   uuid = v.violation_data.dig('violations', 'scan_finding', 'uuids', 'newly_detected')&.first
   Security::ScanResultPolicyViolationDetail.create!(
     scan_result_policy_violation_id: v.id,
     project_id: v.project_id,
     policy_rule_type: 'scan_finding',
     finding_uuid: uuid,
     finding_state: 'newly_detected'
   )

3. Run the GraphQL query (replace fullPath and iid with values from step 2):

   {
     project(fullPath: "gitlab-org/verifications/599102-merge-request-widget") {
       mergeRequest(iid: "4") {
         approvalPolicies {
           nodes {
             id name enforcementType
             violations {
               id status dismissed
               comparisonPipelines { reportType source target }
               violationDetails {
                 nodes {
                   policyRuleType findingState
                   scanFinding {
                     __typename
                     ... on PipelineSecurityReportFinding { uuid severity }
                     ... on Vulnerability { id title }
                   }
                 }
               }
             }
           }
         }
       }
     }
   }

   Observation: Response includes the policy node with enforcementType: ENFORCE, a violation with status: FAILED, comparisonPipelines containing source and target pipeline GIDs, and violationDetails.nodes with policyRuleType: SCAN_FINDING, findingState: NEWLY_DETECTED, and scanFinding.__typename: PipelineSecurityReportFinding with the finding's uuid and severity.

4. Test the policyRuleType filter argument:

   violationDetails(policyRuleType: SCAN_FINDING) { nodes { policyRuleType } }

   Observation: Only SCAN_FINDING detail nodes are returned.

5. Test pagination on violationDetails:

   violationDetails(first: 1) {
     nodes { policyRuleType }
     pageInfo { hasNextPage endCursor }
   }

   Observation: hasNextPage: true when more than one detail exists; endCursor is present.

6. Disable the feature flag and re-run the query:

   bin/rails runner "Feature.disable(:security_policy_violations_graphql_field)"

   Observation: approvalPolicies returns null.

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.