Add scan_result_policy_violation_details model and table

Imam Hossainrequested to merge
597912-create-scan_result_policy_violations-model-and-database-table into master

What does this MR do and why?

Introduces the scan_result_policy_violation_details table and its corresponding Security::ScanResultPolicyViolationDetail model to normalize per-item violation data for scan result policies.

Previously, individual violation items (finding UUIDs, license+component pairs, commit SHAs) were stored as nested arrays inside the violation_data JSONB column on scan_result_policy_violations. This made pagination impossible, dismissal checks expensive, and finding lookups required JSONB digging. The new table stores one row per violation item, enabling efficient querying, pagination, and future dismissal workflows.

References

Issue: #597912 (closed) POC MR: !232424

Database Schema

CREATE TABLE scan_result_policy_violation_details (
    id bigint NOT NULL,
    scan_result_policy_violation_id bigint NOT NULL,
    project_id bigint NOT NULL,
    policy_rule_type smallint NOT NULL,
    finding_uuid text,
    finding_state smallint,
    license_name text,
    dependencies text[] DEFAULT '{}'::text[] NOT NULL,
    commit_shas text[] DEFAULT '{}'::text[] NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    CONSTRAINT check_157ca48e3c CHECK ((char_length(license_name) <= 255)),
    CONSTRAINT check_d2dc12651e CHECK ((char_length(finding_uuid) <= 50))
);

How to set up and validate locally

  1. Run the migrations:

    bin/rails db:migrate

    Observation: All three migrations complete without error. db/structure.sql contains the scan_result_policy_violation_details table definition with both indexes and both foreign keys.

  2. Create a test record via the factory:

    # In rails console or a spec
violation = FactoryBot.create(:scan_result_policy_violation)
detail = FactoryBot.create(:scan_result_policy_violation_detail, violation: violation)
detail.persisted? # => true
detail.scan_finding? # => true

    Observation: Record persists and enum predicate returns true.

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.

Merge request reports