gitfor/platform

-- Published security advisories by affected package, so dependency graphs can match any repository's
-- dependencies without asking every RepoState. Written by src/server/security/advisory-packages.ts.
CREATE TABLE published_advisory_packages (
  advisory_id TEXT NOT NULL,
  repo_id TEXT NOT NULL,
  ghsa_id TEXT NOT NULL,
  cve_id TEXT,
  title TEXT NOT NULL,
  severity TEXT NOT NULL,
  ecosystem TEXT NOT NULL,
  package_name TEXT NOT NULL,
  affected_versions TEXT,
  patched_versions TEXT,
  published_at INTEGER,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (advisory_id, ecosystem, package_name)
);

CREATE INDEX published_advisory_packages_package ON published_advisory_packages(ecosystem, package_name);

CREATE INDEX published_advisory_packages_repo ON published_advisory_packages(repo_id);