Skip to content

Modern Docker Management, Designed for Everyone

1.9 KB
Raw Blame
-- +goose Up
ALTER TABLE vulnerability_scan_items ADD COLUMN cvss_score REAL;
UPDATE vulnerability_scan_items
SET cvss_score = COALESCE(
    NULLIF(CAST(json_extract(details, '$.cvss.v3Score') AS REAL), 0),
    NULLIF(CAST(json_extract(details, '$.cvss.v2Score') AS REAL), 0)
)
WHERE details IS NOT NULL AND json_valid(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 REAL,
    epss_percentile REAL,
    epss_checked_at DATETIME,
    updated_at DATETIME
);

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 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME
);

-- +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 cvss_score;