CREATE TABLE vulnerability_identifiers (
    vulnerability_id BIGINT NOT NULL REFERENCES vulnerabilities (id) ON DELETE CASCADE,
    source TEXT NOT NULL,
    kind TEXT NOT NULL,
    identifier TEXT NOT NULL,
    is_primary BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (source, kind, identifier)
);

CREATE INDEX vulnerability_identifiers_vulnerability_id_idx
    ON vulnerability_identifiers (vulnerability_id);

CREATE INDEX vulnerability_identifiers_kind_identifier_idx
    ON vulnerability_identifiers (kind, identifier);
