Schemas

Dependency Management Data produces various types of data into the underlying SQLite database.

Below you can find the schemas in the database.

`internal/datasources/awselasticache/db/schema.sql`

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
);

`internal/datasources/awslambda/db/schema.sql`

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
);

`internal/datasources/awsrds/db/schema.sql`

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
);

`internal/datasources/dependabot/db/schema.sql`

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
);

`internal/datasources/renovate/db/schema.sql`

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
);

`internal/depsdev/db/schema.sql`

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
);

`internal/endoflifedate/db/schema.sql`

-- 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`
CREATE TABLE IF NOT EXISTS renovate_endoflife (
  -- from renovate
  organisation TEXT NOT NULL,
  repo TEXT NOT NULL,

  package_name TEXT NOT NULL,
  version TEXT NOT NULL,

  package_manager TEXT NOT NULL,
  package_file_path TEXT NOT NULL,

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

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

-- dependabot_endoflife contains mappings for the `dependabot` datasource that can be JOINed with `endoflifedate_products`
CREATE TABLE IF NOT EXISTS dependabot_endoflife (
  -- from dependabot
  organisation TEXT NOT NULL,
  repo TEXT NOT NULL,

  package_name TEXT NOT NULL,
  version TEXT NOT NULL,

  package_manager TEXT NOT NULL,
  package_file_path TEXT NOT NULL,

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

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

`internal/osvdev/db/schema.sql`

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
);

`internal/ownership/db/schema.sql`

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
);