migrations

Overview ¶

Package migrations holds PostgreSQL migrations for databases to back "datastore" implementations.

The SQL definition reference in the following sections does NOT mean database structure is considered public API. Users MUST NOT rely on a certain database structure.

Indexer Schema ¶

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;

COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';

CREATE TYPE public.versionrange AS RANGE (
    subtype = integer[],
    multirange_type_name = public.versionmultirange
);

CREATE TABLE public.dist (
    id bigint NOT NULL,
    name text DEFAULT ''::text NOT NULL,
    did text DEFAULT ''::text NOT NULL,
    version text DEFAULT ''::text NOT NULL,
    version_code_name text DEFAULT ''::text NOT NULL,
    version_id text DEFAULT ''::text NOT NULL,
    arch text DEFAULT ''::text NOT NULL,
    cpe text DEFAULT ''::text NOT NULL,
    pretty_name text DEFAULT ''::text NOT NULL
);

CREATE SEQUENCE public.dist_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.dist_id_seq OWNED BY public.dist.id;

CREATE TABLE public.dist_scanartifact (
    dist_id bigint NOT NULL,
    scanner_id bigint NOT NULL,
    layer_id bigint NOT NULL
);

CREATE TABLE public.file (
    id bigint NOT NULL,
    path text NOT NULL,
    kind text NOT NULL
);

CREATE SEQUENCE public.file_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.file_id_seq OWNED BY public.file.id;

CREATE TABLE public.file_scanartifact (
    file_id bigint NOT NULL,
    scanner_id bigint NOT NULL,
    layer_id bigint NOT NULL
);

CREATE TABLE public.indexreport (
    state text,
    scan_result jsonb,
    manifest_id bigint NOT NULL
);

CREATE TABLE public.layer (
    hash text NOT NULL,
    id bigint NOT NULL
);

CREATE SEQUENCE public.layer_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.layer_id_seq OWNED BY public.layer.id;

CREATE TABLE public.libindex_migrations (
    version integer NOT NULL
);

CREATE TABLE public.manifest (
    hash text NOT NULL,
    id bigint NOT NULL
);

CREATE SEQUENCE public.manifest_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.manifest_id_seq OWNED BY public.manifest.id;

CREATE TABLE public.manifest_index (
    id bigint NOT NULL,
    package_id bigint NOT NULL,
    dist_id bigint,
    repo_id bigint,
    manifest_id bigint
);

CREATE SEQUENCE public.manifest_index_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.manifest_index_id_seq OWNED BY public.manifest_index.id;

CREATE TABLE public.manifest_layer (
    i bigint NOT NULL,
    manifest_id bigint NOT NULL,
    layer_id bigint NOT NULL
);

CREATE TABLE public.package (
    id bigint NOT NULL,
    name text NOT NULL,
    kind text DEFAULT ''::text NOT NULL,
    version text DEFAULT ''::text NOT NULL,
    norm_kind text,
    norm_version integer[],
    module text DEFAULT ''::text NOT NULL,
    arch text DEFAULT ''::text NOT NULL
);

CREATE SEQUENCE public.package_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.package_id_seq OWNED BY public.package.id;

CREATE TABLE public.package_scanartifact (
    package_id bigint NOT NULL,
    source_id bigint NOT NULL,
    scanner_id bigint NOT NULL,
    package_db text NOT NULL,
    repository_hint text NOT NULL,
    layer_id bigint NOT NULL,
    filepath text
);

CREATE TABLE public.repo (
    id bigint NOT NULL,
    name text NOT NULL,
    key text DEFAULT ''::text,
    uri text DEFAULT ''::text,
    cpe text DEFAULT ''::text
);

CREATE SEQUENCE public.repo_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.repo_id_seq OWNED BY public.repo.id;

CREATE TABLE public.repo_scanartifact (
    repo_id bigint NOT NULL,
    scanner_id bigint NOT NULL,
    layer_id bigint NOT NULL
);

CREATE TABLE public.scanned_layer (
    scanner_id bigint NOT NULL,
    layer_id bigint NOT NULL
);

CREATE TABLE public.scanned_manifest (
    scanner_id bigint NOT NULL,
    manifest_id bigint NOT NULL
);

CREATE TABLE public.scanner (
    id bigint NOT NULL,
    name text NOT NULL,
    version text NOT NULL,
    kind text NOT NULL
);

CREATE SEQUENCE public.scanner_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.scanner_id_seq OWNED BY public.scanner.id;

CREATE TABLE public.scannerlist (
    id bigint NOT NULL,
    manifest_hash text,
    scanner_id bigint
);

CREATE SEQUENCE public.scannerlist_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.scannerlist_id_seq OWNED BY public.scannerlist.id;

ALTER TABLE ONLY public.dist ALTER COLUMN id SET DEFAULT nextval('public.dist_id_seq'::regclass);

ALTER TABLE ONLY public.file ALTER COLUMN id SET DEFAULT nextval('public.file_id_seq'::regclass);

ALTER TABLE ONLY public.layer ALTER COLUMN id SET DEFAULT nextval('public.layer_id_seq'::regclass);

ALTER TABLE ONLY public.manifest ALTER COLUMN id SET DEFAULT nextval('public.manifest_id_seq'::regclass);

ALTER TABLE ONLY public.manifest_index ALTER COLUMN id SET DEFAULT nextval('public.manifest_index_id_seq'::regclass);

ALTER TABLE ONLY public.package ALTER COLUMN id SET DEFAULT nextval('public.package_id_seq'::regclass);

ALTER TABLE ONLY public.repo ALTER COLUMN id SET DEFAULT nextval('public.repo_id_seq'::regclass);

ALTER TABLE ONLY public.scanner ALTER COLUMN id SET DEFAULT nextval('public.scanner_id_seq'::regclass);

ALTER TABLE ONLY public.scannerlist ALTER COLUMN id SET DEFAULT nextval('public.scannerlist_id_seq'::regclass);

ALTER TABLE ONLY public.dist
    ADD CONSTRAINT dist_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.dist_scanartifact
    ADD CONSTRAINT dist_scanartifact_pkey PRIMARY KEY (layer_id, scanner_id, dist_id);

ALTER TABLE ONLY public.file
    ADD CONSTRAINT file_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.file_scanartifact
    ADD CONSTRAINT file_scanartifact_pkey PRIMARY KEY (layer_id, scanner_id, file_id);

ALTER TABLE ONLY public.indexreport
    ADD CONSTRAINT indexreport_pkey PRIMARY KEY (manifest_id);

ALTER TABLE ONLY public.layer
    ADD CONSTRAINT layer_hash_unique UNIQUE (hash);

ALTER TABLE ONLY public.layer
    ADD CONSTRAINT layer_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.libindex_migrations
    ADD CONSTRAINT libindex_migrations_pkey PRIMARY KEY (version);

ALTER TABLE ONLY public.manifest
    ADD CONSTRAINT manifest_hash_unique UNIQUE (hash);

ALTER TABLE ONLY public.manifest_index
    ADD CONSTRAINT manifest_index_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.manifest_layer
    ADD CONSTRAINT manifest_layer_pkey PRIMARY KEY (manifest_id, layer_id, i);

ALTER TABLE ONLY public.manifest
    ADD CONSTRAINT manifest_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.package
    ADD CONSTRAINT package_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.package_scanartifact
    ADD CONSTRAINT package_scanartifact_pkey PRIMARY KEY (layer_id, package_id, source_id, scanner_id, package_db, repository_hint);

ALTER TABLE ONLY public.repo
    ADD CONSTRAINT repo_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.repo_scanartifact
    ADD CONSTRAINT repo_scanartifact_pkey PRIMARY KEY (layer_id, repo_id, scanner_id);

ALTER TABLE ONLY public.scanned_layer
    ADD CONSTRAINT scanned_layer_pkey PRIMARY KEY (layer_id, scanner_id);

ALTER TABLE ONLY public.scanned_manifest
    ADD CONSTRAINT scanned_manifest_pkey PRIMARY KEY (manifest_id, scanner_id);

ALTER TABLE ONLY public.scanner
    ADD CONSTRAINT scanner_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.scannerlist
    ADD CONSTRAINT scannerlist_pkey PRIMARY KEY (id);

CREATE UNIQUE INDEX dist_unique_idx ON public.dist USING btree (name, did, version, version_code_name, version_id, arch, cpe, pretty_name);

CREATE UNIQUE INDEX file_unique_idx ON public.file USING btree (path, kind);

CREATE INDEX idx_manifest_index_manifest_id ON public.manifest_index USING btree (manifest_id);

CREATE INDEX idx_manifest_layer_layer_id ON public.manifest_layer USING btree (layer_id);

CREATE INDEX layer_hash_idx ON public.layer USING btree (hash);

CREATE INDEX manifest_hash_idx ON public.manifest USING btree (hash);

CREATE UNIQUE INDEX manifest_index_unique ON public.manifest_index USING btree (package_id, COALESCE(dist_id, (0)::bigint), COALESCE(repo_id, (0)::bigint), manifest_id);

CREATE UNIQUE INDEX package_unique_idx ON public.package USING btree (name, version, kind, module, arch);

CREATE UNIQUE INDEX repo_unique_idx ON public.repo USING btree (name, key, uri);

CREATE UNIQUE INDEX scanner_unique_idx ON public.scanner USING btree (name, kind, version);

CREATE INDEX scannerlist_manifest_hash_idx ON public.scannerlist USING btree (manifest_hash);

ALTER TABLE ONLY public.dist_scanartifact
    ADD CONSTRAINT dist_scanartifact_dist_id_fkey FOREIGN KEY (dist_id) REFERENCES public.dist(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.dist_scanartifact
    ADD CONSTRAINT dist_scanartifact_layer_id_fkey FOREIGN KEY (layer_id) REFERENCES public.layer(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.dist_scanartifact
    ADD CONSTRAINT dist_scanartifact_scanner_id_fkey FOREIGN KEY (scanner_id) REFERENCES public.scanner(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.file_scanartifact
    ADD CONSTRAINT file_scanartifact_file_id_fkey FOREIGN KEY (file_id) REFERENCES public.file(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.file_scanartifact
    ADD CONSTRAINT file_scanartifact_layer_id_fkey FOREIGN KEY (layer_id) REFERENCES public.layer(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.file_scanartifact
    ADD CONSTRAINT file_scanartifact_scanner_id_fkey FOREIGN KEY (scanner_id) REFERENCES public.scanner(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.indexreport
    ADD CONSTRAINT indexreport_manifest_id_fkey FOREIGN KEY (manifest_id) REFERENCES public.manifest(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.manifest_index
    ADD CONSTRAINT manifest_index_dist_id_fkey FOREIGN KEY (dist_id) REFERENCES public.dist(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.manifest_index
    ADD CONSTRAINT manifest_index_manifest_id_fkey FOREIGN KEY (manifest_id) REFERENCES public.manifest(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.manifest_index
    ADD CONSTRAINT manifest_index_package_id_fkey FOREIGN KEY (package_id) REFERENCES public.package(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.manifest_index
    ADD CONSTRAINT manifest_index_repo_id_fkey FOREIGN KEY (repo_id) REFERENCES public.repo(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.manifest_layer
    ADD CONSTRAINT manifest_layer_layer_id_fkey FOREIGN KEY (layer_id) REFERENCES public.layer(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.manifest_layer
    ADD CONSTRAINT manifest_layer_manifest_id_fkey FOREIGN KEY (manifest_id) REFERENCES public.manifest(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.package_scanartifact
    ADD CONSTRAINT package_scanartifact_layer_id_fkey FOREIGN KEY (layer_id) REFERENCES public.layer(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.package_scanartifact
    ADD CONSTRAINT package_scanartifact_package_id_fkey FOREIGN KEY (package_id) REFERENCES public.package(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.package_scanartifact
    ADD CONSTRAINT package_scanartifact_scanner_id_fkey FOREIGN KEY (scanner_id) REFERENCES public.scanner(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.package_scanartifact
    ADD CONSTRAINT package_scanartifact_source_id_fkey FOREIGN KEY (package_id) REFERENCES public.package(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.repo_scanartifact
    ADD CONSTRAINT repo_scanartifact_layer_id_fkey FOREIGN KEY (layer_id) REFERENCES public.layer(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.repo_scanartifact
    ADD CONSTRAINT repo_scanartifact_repo_id_fkey FOREIGN KEY (repo_id) REFERENCES public.repo(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.repo_scanartifact
    ADD CONSTRAINT repo_scanartifact_scanner_id_fkey FOREIGN KEY (scanner_id) REFERENCES public.scanner(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.scanned_layer
    ADD CONSTRAINT scanned_layer_layer_id_fkey FOREIGN KEY (layer_id) REFERENCES public.layer(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.scanned_layer
    ADD CONSTRAINT scanned_layer_scanner_id_fkey FOREIGN KEY (scanner_id) REFERENCES public.scanner(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.scanned_manifest
    ADD CONSTRAINT scanned_manifest_manifest_id_fkey FOREIGN KEY (manifest_id) REFERENCES public.manifest(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.scanned_manifest
    ADD CONSTRAINT scanned_manifest_scanner_id_fkey FOREIGN KEY (scanner_id) REFERENCES public.scanner(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.scannerlist
    ADD CONSTRAINT scannerlist_scanner_id_fkey FOREIGN KEY (scanner_id) REFERENCES public.scanner(id) ON DELETE CASCADE;

Matcher Schema ¶

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;

COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';

CREATE TYPE public.versionrange AS RANGE (
    subtype = integer[],
    multirange_type_name = public.versionmultirange
);

CREATE TABLE public.alias (
    id integer NOT NULL,
    namespace integer NOT NULL,
    name text NOT NULL
);

COMMENT ON TABLE public.alias IS 'Table for all known aliases of vulnerabilities in the system. All vulnerabilities should have at least one alias.';

ALTER TABLE public.alias ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.alias_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.alias_namespace (
    id integer NOT NULL,
    namespace text NOT NULL
);

COMMENT ON TABLE public.alias_namespace IS 'Contains namespaces for aliases. Usually short IDs like "CVE" or "GHSA".';

ALTER TABLE public.alias_namespace ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.alias_namespace_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.enrichment (
    id bigint NOT NULL,
    hash_kind text,
    hash bytea,
    updater text,
    tags text[],
    data jsonb
);

CREATE SEQUENCE public.enrichment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.enrichment_id_seq OWNED BY public.enrichment.id;

CREATE TABLE public.update_operation (
    id bigint NOT NULL,
    ref uuid DEFAULT public.uuid_generate_v4(),
    updater text NOT NULL,
    fingerprint text,
    date timestamp with time zone DEFAULT now(),
    kind text
);

CREATE MATERIALIZED VIEW public.latest_update_operations AS
 SELECT DISTINCT ON (updater) id,
    kind,
    updater
   FROM public.update_operation
  ORDER BY updater, id DESC
  WITH NO DATA;

CREATE TABLE public.uo_vuln (
    uo bigint NOT NULL,
    vuln bigint NOT NULL
);

CREATE TABLE public.vuln (
    id bigint NOT NULL,
    hash_kind text NOT NULL,
    hash bytea NOT NULL,
    updater text,
    name text,
    description text,
    issued timestamp with time zone,
    links text,
    severity text,
    normalized_severity text,
    package_name text,
    package_version text,
    package_module text,
    package_arch text,
    package_kind text,
    dist_id text,
    dist_name text,
    dist_version text,
    dist_version_code_name text,
    dist_version_id text,
    dist_arch text,
    dist_cpe text,
    dist_pretty_name text,
    repo_name text,
    repo_key text,
    repo_uri text,
    fixed_in_version text,
    arch_operation text,
    vulnerable_range public.versionrange DEFAULT public.versionrange('{}'::integer[], '{}'::integer[], '()'::text) NOT NULL,
    version_kind text,
    not_vulnerable boolean DEFAULT false NOT NULL
);

COMMENT ON COLUMN public.vuln.not_vulnerable IS 'Invert the meaning of the record: assert that described versions are not vulnerable.';

CREATE VIEW public.latest_vuln AS
 SELECT v.id,
    v.hash_kind,
    v.hash,
    v.updater,
    v.name,
    v.description,
    v.issued,
    v.links,
    v.severity,
    v.normalized_severity,
    v.package_name,
    v.package_version,
    v.package_module,
    v.package_arch,
    v.package_kind,
    v.dist_id,
    v.dist_name,
    v.dist_version,
    v.dist_version_code_name,
    v.dist_version_id,
    v.dist_arch,
    v.dist_cpe,
    v.dist_pretty_name,
    v.repo_name,
    v.repo_key,
    v.repo_uri,
    v.fixed_in_version,
    v.arch_operation,
    v.vulnerable_range,
    v.version_kind
   FROM ((( SELECT DISTINCT ON (update_operation.updater) update_operation.id
           FROM public.update_operation
          ORDER BY update_operation.updater, update_operation.id DESC) uo
     JOIN public.uo_vuln ON ((uo_vuln.uo = uo.id)))
     JOIN public.vuln v ON ((uo_vuln.vuln = v.id)));

CREATE TABLE public.libvuln_migrations (
    version integer NOT NULL
);

CREATE TABLE public.uo_enrich (
    uo bigint NOT NULL,
    enrich bigint NOT NULL,
    updater text,
    fingerprint text,
    date timestamp with time zone
);

CREATE SEQUENCE public.update_operation_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.update_operation_id_seq OWNED BY public.update_operation.id;

CREATE TABLE public.updater_status (
    updater_name text NOT NULL,
    last_attempt timestamp with time zone DEFAULT now(),
    last_success timestamp with time zone,
    last_run_succeeded boolean,
    last_attempt_fingerprint text,
    last_error text
);

CREATE SEQUENCE public.vuln_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.vuln_id_seq OWNED BY public.vuln.id;

CREATE TABLE public.vulnerability_alias (
    vulnerability bigint NOT NULL,
    alias integer NOT NULL
);

COMMENT ON TABLE public.vulnerability_alias IS 'Pivot table linking vulnerabilities to aliases.';

CREATE TABLE public.vulnerability_self (
    vulnerability bigint NOT NULL,
    self integer NOT NULL
);

COMMENT ON TABLE public.vulnerability_self IS 'Indicates the "self" alias for a vulnerability.';

ALTER TABLE ONLY public.enrichment ALTER COLUMN id SET DEFAULT nextval('public.enrichment_id_seq'::regclass);

ALTER TABLE ONLY public.update_operation ALTER COLUMN id SET DEFAULT nextval('public.update_operation_id_seq'::regclass);

ALTER TABLE ONLY public.vuln ALTER COLUMN id SET DEFAULT nextval('public.vuln_id_seq'::regclass);

ALTER TABLE ONLY public.alias
    ADD CONSTRAINT alias_namespace_name_key UNIQUE (namespace, name);

ALTER TABLE ONLY public.alias_namespace
    ADD CONSTRAINT alias_namespace_namespace_key UNIQUE (namespace);

ALTER TABLE ONLY public.alias_namespace
    ADD CONSTRAINT alias_namespace_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.alias
    ADD CONSTRAINT alias_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.enrichment
    ADD CONSTRAINT enrichment_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.libvuln_migrations
    ADD CONSTRAINT libvuln_migrations_pkey PRIMARY KEY (version);

ALTER TABLE ONLY public.uo_enrich
    ADD CONSTRAINT uo_enrich_pkey PRIMARY KEY (uo, enrich);

ALTER TABLE ONLY public.uo_vuln
    ADD CONSTRAINT uo_vuln_pkey PRIMARY KEY (uo, vuln);

ALTER TABLE ONLY public.update_operation
    ADD CONSTRAINT update_operation_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.update_operation
    ADD CONSTRAINT update_operation_ref_key UNIQUE (ref);

ALTER TABLE ONLY public.updater_status
    ADD CONSTRAINT updater_status_pkey PRIMARY KEY (updater_name);

ALTER TABLE ONLY public.vuln
    ADD CONSTRAINT vuln_hash_kind_hash_key UNIQUE (hash_kind, hash);

ALTER TABLE ONLY public.vuln
    ADD CONSTRAINT vuln_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.vulnerability_alias
    ADD CONSTRAINT vulnerability_alias_pkey PRIMARY KEY (vulnerability, alias);

ALTER TABLE ONLY public.vulnerability_self
    ADD CONSTRAINT vulnerability_self_pkey PRIMARY KEY (vulnerability);

CREATE UNIQUE INDEX enrichment_hash_kind_hash_idx ON public.enrichment USING btree (hash_kind, hash);

CREATE INDEX enrichment_tags_idx ON public.enrichment USING gin (tags);

CREATE INDEX enrichment_updater_idx ON public.enrichment USING btree (updater);

CREATE UNIQUE INDEX idx_updater_uniq ON public.latest_update_operations USING btree (updater);

CREATE INDEX uo_enrich_enrich_idx ON public.uo_enrich USING btree (enrich);

CREATE INDEX uo_enrich_uo_idx ON public.uo_enrich USING btree (uo);

CREATE INDEX uo_updater_idx ON public.update_operation USING btree (updater);

CREATE INDEX uo_vuln_uo_idx ON public.uo_vuln USING btree (uo);

CREATE INDEX uo_vuln_vuln_idx ON public.uo_vuln USING btree (vuln);

CREATE INDEX update_operation_kind_idx ON public.update_operation USING btree (kind);

CREATE INDEX update_operation_updater_idx ON public.update_operation USING btree (updater);

CREATE INDEX vuln_lookup_idx ON public.vuln USING btree (package_name, dist_id, dist_name, dist_pretty_name, dist_version, dist_version_id, package_module, dist_version_code_name, repo_name, dist_arch, dist_cpe, repo_key, repo_uri);

CREATE INDEX vuln_updater_idx ON public.vuln USING btree (updater);

ALTER TABLE ONLY public.alias
    ADD CONSTRAINT alias_namespace_fkey FOREIGN KEY (namespace) REFERENCES public.alias_namespace(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.uo_enrich
    ADD CONSTRAINT uo_enrich_enrich_fkey FOREIGN KEY (enrich) REFERENCES public.enrichment(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.uo_enrich
    ADD CONSTRAINT uo_enrich_uo_fkey FOREIGN KEY (uo) REFERENCES public.update_operation(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.uo_vuln
    ADD CONSTRAINT uo_vuln_uo_fkey FOREIGN KEY (uo) REFERENCES public.update_operation(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.uo_vuln
    ADD CONSTRAINT uo_vuln_vuln_fkey FOREIGN KEY (vuln) REFERENCES public.vuln(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.vulnerability_alias
    ADD CONSTRAINT vulnerability_alias_alias_fkey FOREIGN KEY (alias) REFERENCES public.alias(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.vulnerability_alias
    ADD CONSTRAINT vulnerability_alias_vulnerability_fkey FOREIGN KEY (vulnerability) REFERENCES public.vuln(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.vulnerability_self
    ADD CONSTRAINT vulnerability_self_self_fkey FOREIGN KEY (self) REFERENCES public.alias(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.vulnerability_self
    ADD CONSTRAINT vulnerability_self_vulnerability_fkey FOREIGN KEY (vulnerability) REFERENCES public.vuln(id) ON DELETE CASCADE;