Create new table to store vulnerability risk score

To calculate Project and Group risk scores, we need to store individual risk scores for each vulnerability finding. We are implementing a new vulnerability_finding_risk_scores database table that will store risk scores for all vulnerability findings. The use of vulnerability findings as our data model will support future tracking of vulnerabilities across multiple branches.

Requirements

The vulnerability_finding_risk_scores table should:

• Include a finding_id column to associate metrics with specific vulnerability findings
• finding_id as primary key
• Include a project_id column for sharding purposes

Table Schema

CREATE TABLE vulnerability_finding_risk_scores (
    finding_id bigint NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    project_id bigint NOT NULL,
    risk_score double precision DEFAULT 0.0 NOT NULL
);

Indexes:

• vulnerability_finding_id

Validations:

• Ensure risk_score is positive