schema.sql

-- SBOMix Central Repository Schema
-- Postgres 14+  (uses gen_random_uuid(), jsonb, timestamptz)

-- ── Organizations ─────────────────────────────────────────────────────────────
CREATE TABLE organizations (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    name       TEXT        NOT NULL,
    email      TEXT        UNIQUE,
    -- Stores HMAC-SHA256(plaintext_key, HMAC_SECRET) — plaintext never persisted.
    -- Legacy org:admin key; prefer api_keys table for new issuance.
    api_key     TEXT        UNIQUE NOT NULL,
    vuln_alerts            BOOLEAN NOT NULL DEFAULT TRUE,
    plan                   TEXT    NOT NULL DEFAULT 'trial'
                                   CHECK (plan IN ('free','trial','starter','team','business','enterprise')),
    stripe_customer_id     TEXT    UNIQUE,
    stripe_subscription_id TEXT    UNIQUE,
    subscription_status    TEXT,   -- active | trialing | past_due | canceled | unpaid
    current_period_end     TIMESTAMPTZ,
    trial_ends_at          TIMESTAMPTZ,
    created_at             TIMESTAMPTZ DEFAULT NOW()
);

-- ── API Keys (scoped, rotatable) ──────────────────────────────────────────────
-- Multiple keys per org with independent scopes and revocation.
-- Scopes: sbom:ingest  — POST /api/v1/ingest
--         sbom:read    — GET  /api/v1/apps, /search, /report
--         org:admin    — all of the above + key management
CREATE TABLE api_keys (
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id       UUID        NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name         TEXT        NOT NULL DEFAULT 'default',
    -- HMAC-SHA256(plaintext_key, HMAC_SECRET) — plaintext shown once on creation only
    key_hash     TEXT        NOT NULL UNIQUE,
    scopes       TEXT[]      NOT NULL DEFAULT '{sbom:ingest,sbom:read}',
    created_at   TIMESTAMPTZ DEFAULT NOW(),
    last_used_at TIMESTAMPTZ,
    revoked_at   TIMESTAMPTZ
);

-- ── Applications ──────────────────────────────────────────────────────────────
CREATE TABLE applications (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id     UUID        NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name       TEXT        NOT NULL,
    repo_url   TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (org_id, name)
);

-- ── SBOMs (one row per build) ─────────────────────────────────────────────────
CREATE TABLE sboms (
    id                  UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id              UUID        NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
    org_id              UUID        NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    version             TEXT,
    commit_sha          TEXT,
    branch              TEXT,
    cyclonedx           JSONB,
    spdx                JSONB,
    component_count     INT         DEFAULT 0,
    vulnerability_count INT         DEFAULT 0,
    critical_count      INT         DEFAULT 0,
    quality_score       NUMERIC(5,2),
    ecosystems          TEXT[],
    elapsed_ms          INT,
    -- AI-BOM fields (populated when AI artifacts are detected during scan)
    aibom               JSONB,
    ai_models           INT         NOT NULL DEFAULT 0,
    ai_threats          INT         NOT NULL DEFAULT 0,
    ai_critical         INT         NOT NULL DEFAULT 0,
    least_agency_score  INT,
    generated_at        TIMESTAMPTZ,
    created_at          TIMESTAMPTZ DEFAULT NOW()
);

-- ── Components (normalised per org, deduplicated by purl) ─────────────────────
CREATE TABLE components (
    id         UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id     UUID    NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    purl       TEXT    NOT NULL,
    name       TEXT    NOT NULL,
    version    TEXT    NOT NULL,
    ecosystem  TEXT    NOT NULL,
    license    TEXT,
    UNIQUE (org_id, purl)
);

-- ── SBOM ↔ Component (many-to-many) ──────────────────────────────────────────
CREATE TABLE sbom_components (
    sbom_id      UUID    NOT NULL REFERENCES sboms(id) ON DELETE CASCADE,
    component_id UUID    NOT NULL REFERENCES components(id),
    scope        TEXT,       -- required | dev | optional
    is_direct    BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (sbom_id, component_id)
);

-- ── Vulnerabilities ───────────────────────────────────────────────────────────
-- One row per (component, osv_id). Refreshed on each ingest where vulns differ.
CREATE TABLE vulnerabilities (
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    component_id    UUID        NOT NULL REFERENCES components(id) ON DELETE CASCADE,
    org_id          UUID        NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    osv_id          TEXT        NOT NULL,   -- OSV canonical ID (may be CVE-xxx or GHSA-xxx)
    cve_id          TEXT,                   -- CVE alias if available
    severity        TEXT,                   -- CRITICAL | HIGH | MEDIUM | LOW
    cvss_score      NUMERIC(4,2),
    fixed_version   TEXT,
    title           TEXT,
    kev             BOOLEAN     NOT NULL DEFAULT FALSE,
    last_checked    TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (component_id, osv_id)
);

-- ── Latest SBOM per app (materialised at ingest time) ────────────────────────
-- Avoids DISTINCT ON / LATERAL in every read endpoint.
-- Upserted inside the ingest transaction; guarded so out-of-order ingests
-- cannot overwrite a newer row.
CREATE TABLE app_latest_sboms (
    app_id              UUID        PRIMARY KEY
                                    REFERENCES applications(id) ON DELETE CASCADE,
    org_id              UUID        NOT NULL
                                    REFERENCES organizations(id) ON DELETE CASCADE,
    sbom_id             UUID        NOT NULL
                                    REFERENCES sboms(id) ON DELETE CASCADE,
    created_at          TIMESTAMPTZ NOT NULL,
    component_count     INT         NOT NULL DEFAULT 0,
    vulnerability_count INT         NOT NULL DEFAULT 0,
    critical_count      INT         NOT NULL DEFAULT 0,
    quality_score       NUMERIC(5,2),
    ecosystems          TEXT[]
);

-- ── Indexes ───────────────────────────────────────────────────────────────────
CREATE INDEX idx_api_keys_org         ON api_keys(org_id);
CREATE INDEX idx_api_keys_active      ON api_keys(key_hash) WHERE revoked_at IS NULL;
CREATE INDEX idx_applications_org     ON applications(org_id);
CREATE INDEX idx_sboms_app_time       ON sboms(app_id, created_at DESC);
CREATE INDEX idx_sboms_org_time       ON sboms(org_id, created_at DESC);
CREATE INDEX idx_components_purl      ON components(purl);
CREATE INDEX idx_components_org_eco   ON components(org_id, ecosystem);
CREATE INDEX idx_sc_component         ON sbom_components(component_id);
CREATE INDEX idx_vulns_component      ON vulnerabilities(component_id);
CREATE INDEX idx_vulns_cve_org        ON vulnerabilities(org_id, cve_id) WHERE cve_id IS NOT NULL;
CREATE INDEX idx_vulns_osv_org        ON vulnerabilities(org_id, osv_id);
CREATE INDEX idx_vulns_severity_org   ON vulnerabilities(org_id, severity);
CREATE INDEX idx_vulns_kev            ON vulnerabilities(org_id) WHERE kev = TRUE;
CREATE INDEX idx_app_latest_sboms_org ON app_latest_sboms(org_id);

-- ── VEX statements ────────────────────────────────────────────────────────────
-- One row per (org, component, vulnerability). Status aligns with CycloneDX VEX
-- and OpenVEX specs. 'not_affected' rows suppress vulns from risk reports.
CREATE TABLE vex_statements (
    id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id           UUID        NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    component_id     UUID        NOT NULL REFERENCES components(id) ON DELETE CASCADE,
    osv_id           TEXT        NOT NULL,
    status           TEXT        NOT NULL CHECK (status IN (
                                     'not_affected', 'affected',
                                     'fixed', 'under_investigation'
                                 )),
    justification    TEXT        CHECK (justification IN (
                                     'component_not_present',
                                     'vulnerable_code_not_present',
                                     'vulnerable_code_not_in_execute_path',
                                     'vulnerable_code_cannot_be_controlled_by_adversary',
                                     'inline_mitigations_already_exist'
                                 )),
    impact_statement TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (org_id, component_id, osv_id)
);

CREATE INDEX idx_vex_org_osv   ON vex_statements(org_id, osv_id);
CREATE INDEX idx_vex_component ON vex_statements(component_id);

-- ── CISA KEV catalog (global, refreshed daily from katzilla.dev) ──────────────
-- Not org-scoped — the KEV catalog is public data shared across all orgs.
CREATE TABLE kev_catalog (
    cve_id         TEXT        PRIMARY KEY,
    date_added     DATE,
    due_date       DATE,
    ransomware_use TEXT,
    refreshed_at   TIMESTAMPTZ DEFAULT NOW()
);

-- ── Scan jobs (server-side async repo scans) ──────────────────────────────────
-- Jobs are claimed by workers using SELECT ... FOR UPDATE SKIP LOCKED.
-- Worker columns are populated at claim time; finished_at on completion.
CREATE TABLE scan_jobs (
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id          UUID        NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    repo            TEXT        NOT NULL,
    ref             TEXT,
    status          TEXT        NOT NULL DEFAULT 'pending'
                                CHECK (status IN ('pending','running','done','failed','canceled','timed_out')),
    error           TEXT,
    sbom_id         UUID        REFERENCES sboms(id) ON DELETE SET NULL,
    app_name        TEXT,
    -- Queue control
    priority        INT         NOT NULL DEFAULT 100,
    attempts        INT         NOT NULL DEFAULT 0,
    max_attempts    INT         NOT NULL DEFAULT 2,
    -- Worker lease
    locked_by       TEXT,
    locked_at       TIMESTAMPTZ,
    started_at      TIMESTAMPTZ,
    finished_at     TIMESTAMPTZ,
    timeout_at      TIMESTAMPTZ,
    -- Scan metadata
    scan_mode       TEXT        NOT NULL DEFAULT 'hosted_fast'
                                CHECK (scan_mode IN ('hosted_fast','hosted_deep','cli')),
    token_ref       TEXT,
    repo_size_bytes BIGINT,
    component_count INT,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_scan_jobs_org         ON scan_jobs(org_id, created_at DESC);
CREATE INDEX idx_scan_jobs_queue       ON scan_jobs(status, priority, created_at) WHERE status = 'pending';
CREATE INDEX idx_scan_jobs_running_org ON scan_jobs(org_id, status) WHERE status = 'running';
CREATE INDEX idx_scan_jobs_stale_locks ON scan_jobs(status, locked_at) WHERE status = 'running';

-- ── Email verifications ───────────────────────────────────────────────────────
-- Holds pending registrations until the user clicks the link.
-- Unique on email so re-registrations overwrite the stale token.
CREATE TABLE email_verifications (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    email      TEXT        NOT NULL UNIQUE,
    org_name   TEXT        NOT NULL,
    token      TEXT        NOT NULL UNIQUE,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);