Schemas
Dependency Management Data produces various types of data into the underlying SQLite database.
Below you can find the schemas in the database.
internal/advisory/db/schema.sql
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
);
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
-- 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
);
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/datasources/sbom/db/schema.sql
-- 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
);
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` 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
);
internal/metadata/db/schema.sql
CREATE TABLE IF NOT EXISTS metadata (
name TEXT NOT NULL,
value TEXT NOT NULL,
UNIQUE (name) 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
);