CREATE TABLE package_vulnerability_rules (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source TEXT NOT NULL,
    os_family TEXT,
    os_name TEXT,
    os_version TEXT,
    package_type TEXT NOT NULL,
    package_name TEXT NOT NULL,
    architecture TEXT,
    version_constraint TEXT NOT NULL,
    fixed_version TEXT,
    vulnerability_identifier TEXT NOT NULL,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX package_vulnerability_rules_lookup_idx
    ON package_vulnerability_rules (package_type, package_name, os_family);
