Skip to content

Modern Docker Management, Designed for Everyone

2.0 KB
Raw Blame
-- +goose Up
ALTER TABLE vulnerability_scan_items ADD COLUMN IF NOT EXISTS cvss_score DOUBLE PRECISION;
UPDATE vulnerability_scan_items
SET cvss_score = COALESCE(
    NULLIF((CAST(details AS jsonb) #>> '{cvss,v3Score}')::double precision, 0),
    NULLIF((CAST(details AS jsonb) #>> '{cvss,v2Score}')::double precision, 0)
)
WHERE details IS NOT NULL AND details <> '';

CREATE TABLE IF NOT EXISTS vulnerability_threat_intel (
    cve_id TEXT PRIMARY KEY,
    known_exploited BOOLEAN NOT NULL DEFAULT FALSE,
    kev_date_added TEXT NOT NULL DEFAULT '',
    kev_due_date TEXT NOT NULL DEFAULT '',
    kev_ransomware BOOLEAN NOT NULL DEFAULT FALSE,
    epss_score DOUBLE PRECISION,
    epss_percentile DOUBLE PRECISION,
    epss_checked_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_vulnerability_threat_intel_known_exploited ON vulnerability_threat_intel(known_exploited);

CREATE TABLE IF NOT EXISTS vulnerability_risk_snapshots (
    snapshot_date TEXT PRIMARY KEY,
    risk_score INTEGER NOT NULL DEFAULT 0,
    risk_band TEXT NOT NULL DEFAULT 'none',
    critical_count INTEGER NOT NULL DEFAULT 0,
    high_count INTEGER NOT NULL DEFAULT 0,
    medium_count INTEGER NOT NULL DEFAULT 0,
    low_count INTEGER NOT NULL DEFAULT 0,
    unknown_count INTEGER NOT NULL DEFAULT 0,
    findings_count INTEGER NOT NULL DEFAULT 0,
    known_exploited_count INTEGER NOT NULL DEFAULT 0,
    overdue_known_exploited_count INTEGER NOT NULL DEFAULT 0,
    high_epss_count INTEGER NOT NULL DEFAULT 0,
    exposed_critical_high_count INTEGER NOT NULL DEFAULT 0,
    fixable_count INTEGER NOT NULL DEFAULT 0,
    images_scanned INTEGER NOT NULL DEFAULT 0,
    images_total INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ
);

-- +goose Down
DROP TABLE IF EXISTS vulnerability_risk_snapshots;
DROP INDEX IF EXISTS idx_vulnerability_threat_intel_known_exploited;
DROP TABLE IF EXISTS vulnerability_threat_intel;
ALTER TABLE vulnerability_scan_items DROP COLUMN IF EXISTS cvss_score;