Schemas

CREATE TABLE IF NOT EXISTS advisories (
  package_pattern TEXT NOT NULL,
  package_manager TEXT NOT NULL,
  version TEXT,
  -- lexicographically match
  version_match_strategy TEXT
    CHECK (
      version_match_strategy IN (
        "ANY",
        "EQUALS",
        "LESS_THAN",
        "LESS_EQUAL",
        "GREATER_THAN",
        "GREATER_EQUAL"
      )
    ),
  advisory_type TEXT NOT NULL
    CHECK (
      advisory_type IN (
        "DEPRECATED",
        "UNMAINTAINED",
        "SECURITY",
        "OTHER"
      )
    ),
  description TEXT NOT NULL,

  UNIQUE (package_pattern, package_manager, version, version_match_strategy, advisory_type) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS aws_elasticache_datastores (
  account_id TEXT NOT NULL,
  region TEXT NOT NULL,
  arn TEXT NOT NULL,
  name TEXT NOT NULL,
  engine TEXT NOT NULL,
  engine_version TEXT NOT NULL,

  -- tags is a JSON object
  tags TEXT NOT NULL,

  UNIQUE (account_id, region, arn) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS aws_elasticache_datastore_engines (
  engine TEXT NOT NULL,
  engine_version TEXT NOT NULL,
  deprecation TEXT NOT NULL,

  UNIQUE (engine, engine_version) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS aws_lambda_functions (
  account_id TEXT NOT NULL,
  region TEXT NOT NULL,
  arn TEXT NOT NULL,
  name TEXT NOT NULL,
  runtime TEXT NOT NULL,
  last_modified TEXT,

  -- tags is a JSON object
  tags TEXT NOT NULL,

  UNIQUE (account_id, region, arn) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS aws_lambda_function_runtimes (
  runtime TEXT NOT NULL,
	-- deprecation marks the "Phase 1" deprecations as noted in https://docs.aws.amazon.com/lambda/latest/dg/lambda-runtimes.html:
	-- > Phase 1 - Lambda no longer applies security patches or other updates to
	-- > the runtime. You can no longer **create** functions that use the runtime, but
	-- > you can continue to update existing functions. This includes updating the
	-- > runtime version, and rolling back to the previous runtime version. Note
	-- > that functions that use a deprecated runtime are no longer eligible for
	-- > technical support
	deprecation TEXT,
  -- end_of_life marks the "Phase 2" deprecations as noted in https://docs.aws.amazon.com/lambda/latest/dg/lambda-runtimes.html
	-- > Phase 2 - you can no longer **create or update** functions that use the
	-- > runtime. To update a function, you need to migrate it to a supported
	-- > runtime version. After you migrate the function to a supported runtime
	-- > version, you cannot rollback the function to the previous runtime. Phase 2
	-- > starts at least 30 days after the start of Phase 1
  end_of_life TEXT,

  UNIQUE (runtime) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS aws_rds_databases (
  account_id TEXT NOT NULL,
  region TEXT NOT NULL,
  arn TEXT NOT NULL,
  name TEXT NOT NULL,
  engine TEXT NOT NULL,
  engine_version TEXT NOT NULL,

  -- tags is a JSON object
  tags TEXT NOT NULL,

  UNIQUE (account_id, region, arn) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS aws_rds_databases_engines (
  engine TEXT NOT NULL,
  engine_version TEXT NOT NULL,
  deprecation TEXT NOT NULL,

  UNIQUE (engine, engine_version) ON CONFLICT REPLACE
);

-- Deprecated: The `dependabot` table is no longer in use as of DMD v0.38.0, as
-- Dependabot data is now consumed via a Software Bill of Materials (SBOM), which uses the `sbom` table
CREATE TABLE IF NOT EXISTS dependabot (
  platform TEXT NOT NULL,
  organisation TEXT NOT NULL,
  repo TEXT NOT NULL,

  package_name TEXT NOT NULL,
  version TEXT NOT NULL,
  current_version TEXT,

  package_manager TEXT NOT NULL,
  package_file_path TEXT NOT NULL,

  UNIQUE (platform, organisation, repo, package_file_path, package_name, package_manager) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS renovate (
  platform TEXT NOT NULL,
  organisation TEXT NOT NULL,
  repo TEXT NOT NULL,

  package_name TEXT NOT NULL,
  -- this could be a version constraint, such as `<=1.3.4,>=1.3.0`
  version TEXT NOT NULL,
  -- current_version is derived from:
  -- - the `locked_version` or `fixed_version`, if there is a lockfile or some pinning set
  -- - the `current_version` if present, and can be recalculated each time the data is generated
  -- this will give you an exact version number, such as `1.3.4`
  current_version TEXT,

  package_manager TEXT NOT NULL,
  package_file_path TEXT NOT NULL,

  datasource TEXT NOT NULL,
  -- dep_types is a JSON array
  dep_types TEXT NOT NULL,

  UNIQUE (platform, organisation, repo, package_file_path, package_name, package_manager, dep_types) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS renovate_updates (
  platform TEXT NOT NULL,
  organisation TEXT NOT NULL,
  repo TEXT NOT NULL,

  package_name TEXT NOT NULL,
  -- this could be a version constraint, such as `<=1.3.4,>=1.3.0`
  version TEXT NOT NULL,
  -- current_version is derived from:
  -- - the `locked_version` or `fixed_version`, if there is a lockfile or some pinning set
  -- - the `current_version` if present, and can be recalculated each time the data is generated
  -- this will give you an exact version number, such as `1.3.4`
  current_version TEXT,

  package_manager TEXT NOT NULL,
  package_file_path TEXT NOT NULL,

  datasource TEXT NOT NULL,

  -- custom for package updates
  new_version TEXT NOT NULL,
  update_type TEXT NOT NULL,

  UNIQUE (platform, organisation, repo, package_file_path, package_name, package_manager, update_type) ON CONFLICT REPLACE
);

-- sboms contains packages defined by Software Bill of Materials (SBOMs)
CREATE TABLE IF NOT EXISTS sboms (
  -- what platform hosts the source code that this SBOM was produced for? i.e. `github`, `gitlab`, `gitea`, etc
  platform TEXT NOT NULL,
  -- what organisation manages the source code that this SBOM was produced for? Can include `/` for nested organisations
  organisation TEXT NOT NULL,
  -- what repo manages the source code that this SBOM was produced for?
  repo TEXT NOT NULL,

  package_name TEXT NOT NULL,
  version TEXT,
  current_version TEXT,

  -- package_type most commonly relates to the "Type" field of a Package URL, which may be a package ecosystem or package manager type
  package_type TEXT NOT NULL,

  UNIQUE (platform, organisation, repo, package_name, package_type) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS depsdev_cves (
  package_name TEXT NOT NULL,
  version TEXT NOT NULL,

  cve_id TEXT NOT NULL,

  updated_at TEXT NOT NULL,

  UNIQUE (package_name, version, cve_id) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS depsdev_licenses (
  package_name TEXT NOT NULL,
  version TEXT NOT NULL,

  license TEXT NOT NULL,

  updated_at TEXT NOT NULL,

  UNIQUE (package_name, version, license) ON CONFLICT REPLACE
);

-- endoflifedate_products contains the underlying data model for EndOfLife.date products + cycles that can be JOINed with the `*_endoflife` tables
CREATE TABLE IF NOT EXISTS endoflifedate_products (
  -- from Product

  product_name TEXT NOT NULL,

  -- from Cycle

  cycle TEXT NOT NULL,

  supported_until TEXT,
  eol_from TEXT,

  inserted_at TEXT NOT NULL,

  UNIQUE (product_name, cycle) ON CONFLICT REPLACE,
  CHECK(product_name <> ''),
  CHECK(cycle <> '')
);

-- renovate_endoflife contains mappings for the `renovate` datasource that can be JOINed with `endoflifedate_products` and `renovate` to provide the resulting set of renovate dependencies that are End Of Life - See the `RetrievePackageAdvisories` query in `advisory/db/queries.sql`
CREATE TABLE IF NOT EXISTS renovate_endoflife (
  -- from renovate
  package_name TEXT NOT NULL,
  version TEXT NOT NULL,
  current_version TEXT,

  package_manager TEXT NOT NULL,

  datasource TEXT NOT NULL,

  -- from endoflifedate_products
  product_name TEXT NOT NULL,
  cycle TEXT NOT NULL,

  UNIQUE (package_name, version, current_version, package_manager, datasource) ON CONFLICT REPLACE
);

-- dependabot_endoflife contains mappings for the `dependabot` datasource that can be JOINed with `endoflifedate_products` and `dependabot` to provide the resulting set of dependabot dependencies that are End Of Life
CREATE TABLE IF NOT EXISTS dependabot_endoflife (
  -- from dependabot
  package_name TEXT NOT NULL,
  version TEXT NOT NULL,

  package_manager TEXT NOT NULL,

  -- from endoflifedate_products
  product_name TEXT NOT NULL,
  cycle TEXT NOT NULL,

  UNIQUE (package_name, version, package_manager) ON CONFLICT REPLACE
);

-- sbom_endoflife contains mappings for the `sboms` datasource that can be JOINed with `endoflifedate_products` and `sboms` to provide the resulting set of SBOM dependencies that are End Of Life
CREATE TABLE IF NOT EXISTS sboms_endoflife (
  -- from sboms
  package_name TEXT NOT NULL,
  version TEXT,
  current_version TEXT,
  package_type TEXT NOT NULL,

  -- from endoflifedate_products
  product_name TEXT NOT NULL,
  cycle TEXT NOT NULL,

  UNIQUE (package_name, version, package_type) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS metadata (
  name TEXT NOT NULL,
  value TEXT NOT NULL,

  UNIQUE (name) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS osvdev_cves (
  package_name TEXT NOT NULL,
  version TEXT NOT NULL,

  cve_id TEXT NOT NULL,

  updated_at TEXT NOT NULL,

  UNIQUE (package_name, version, cve_id) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS owners (
  platform TEXT NOT NULL,
  organisation TEXT NOT NULL,
  repo TEXT NOT NULL,

  -- free-form information about who the owner is - could be an email address, a team name, Slack channel name, etc
  owner TEXT NOT NULL,
  -- free-form information for additional context, such as a link to a Slack channel, Confluence page, etc
  notes TEXT,

  updated_at TEXT NOT NULL,

  UNIQUE (platform, organisation, repo) ON CONFLICT REPLACE
);