component_metadata

2 rows

✎ View and edit SQL

This data as json, CSV (advanced)

CREATE TABLE component_metadata (
  -- component_name is the name of the Component that this metadata describes.
  --
  -- Note that `component_name` is expected to be unique.
  --
  -- See also: https://dmd.tanna.dev/concept/component/
  --
  -- Foreign key: `sboms.component_name`
  component_name TEXT NOT NULL,

  -- parent_component_name indicates the `component_name` of the parent
  -- Component to this Component.
  --
  -- This is generally used for the case where there is a monorepo of Components.
  --
  -- For instance, if you had a repository called `customer-help-service` with
  -- a structure such as:
  --
  --   /
  --   /.git/
  --   /apps/
  --   /apps/kafka-consumer/
  --   /apps/web/
  --   /apps/internal-web/
  --
  -- Then this would produce the following Components:
  --
  -- `customer-help-service`                (with a `path` of `/`)
  -- `customer-help-service-kafka-consumer` (with a `path` of `/apps/kafka-consumer/`)
  -- `customer-help-service-web`            (with a `path` of `/apps/web/`)
  -- `customer-help-service-internal-web`   (with a `path` of `/apps/internal-web/`)
  --
  -- Note that this does not currently introduce this as a foreign key (https://gitlab.com/tanna.dev/dependency-management-data/-/issues/106) but is intended to be treated as such
  --
  -- Foreign key: `component_metadata.component_name`
  parent_component_name TEXT,

  -- what platform hosts the source code for this Component? i.e. `github`,
  -- `gitlab`, `gitea`, etc
  --
  -- See also: https://dmd.tanna.dev/concepts/repo-key/#platform
  platform TEXT,
  -- what organisation manages the source code for this Component? Can include
  -- `/` for nested organisations
  --
  -- See also: https://dmd.tanna.dev/concepts/repo-key/#organisation
  organisation TEXT,
  -- what is the repo name for this Component?
  --
  -- See also: https://dmd.tanna.dev/concepts/repo-key/#repo
  repo TEXT,

  -- Where is the Component located within the source repository?
  --
  -- Examples:
  --
  -- NULL / `/`             - the root of the repository is this component
  -- `apps/kafka-processor` - a sub-path within a repository
  path TEXT,

  -- vendor denotes the vendor (i.e. third party) that this Component was
  -- produced by.
  --
  -- This makes sense when the component is a piece of software that
  -- is not developed by your organisation, for instance a product that a
  -- vendor provides you as-is to self-host, or provided as a
  -- Software-As-A-Service.
  --
  -- Must be provided alongside `product`.
  vendor TEXT,

  -- product denotes the piece of software, built by `vendor`, that this
  -- Component corresponds to.
  --
  -- This makes sense when the component is a piece of software that
  -- is not developed by your organisation, for instance a product that a
  -- vendor provides you as-is to self-host, or provided as a
  -- Software-As-A-Service.
  --
  -- Must be provided alongside `vendor`.
  product TEXT,

  -- product_version denotes the version of the Component's `product` this
  -- metadata corresponds to.
  --
  -- This makes sense when the component is a piece of software that
  -- is not developed by your organisation, for instance a product that a
  -- vendor provides you as-is to self-host, or provided as a
  -- Software-As-A-Service.
  --
  -- Must be provided alongside `vendor` and `product`.
  product_version TEXT,

  -- owner is a free-form identifier for who owns the Component. This could be
  -- an email address, a team name, Slack channel name, etc, but should ideally
  -- be clear from this column who should be contacted about any queries about
  -- the Component
  owner TEXT,
  -- notes allows adding additional, optional, context around the ownership,
  -- for instance a link to a Slack channel, Confluence page, internal Service
  -- Catalog, etc. The contents will be shown verbatim to a user, and will not
  -- be interpreted as markup.
  owner_notes TEXT,

  -- component_type is a free-form field to create enum-style data, for
  -- instance `LIBRARY` or `SERVICE`, or `EXAMPLE_CODE`.
  --
  -- This may track with your Developer Portal's own definition of a
  -- Component's type.
  --
  -- For additional usage, you can use the `component_usage` field, or for
  -- further key-value data, use the`additional_metadata` field
  component_type text,

  -- component_usage is a free-form field to note additional information
  -- around the repository's usage, which is organisation-specific.
  --
  -- For instance, this may be enum-style data, a space-separated list of
  -- enum-style data, or a long human-readable description.
  --
  -- For additional usage, you can use the `additional_metadata` field
  component_usage text,

  -- description is a textual description of the repo for more context, which
  -- can include links out to other systems i.e. a Service Catalog. The
  -- contents will be shown verbatim to a user, and will not be interpreted as
  -- markup
  description TEXT,

  -- additional_metadata is a JSON object of additional key-value data that can
  -- be used to provide custom organisation-specific configuration, and augment
  -- any queries for data with information around this additional metadata for
  -- instance:
  --
  -- - `last_commit_date` - the last commit date to the project
  -- - `pci_environment` - the PCI environment the application is deployed to
  -- - `customer_type` - i.e. whether it's used for government, financial
  --   users, etc
  --
  -- NOTE this must be a JSON object of key-value strings
  additional_metadata TEXT,

  CHECK
  (
    (
      platform is null
      and
      organisation is null
      and
      repo is null
    )
    OR
    (
      platform <> ''
      and
      organisation <> ''
      and
      repo <> ''
    )
  ),

  CHECK
  (
    -- ensure that `vendor` and `product` are set
    (
      (
        vendor is null
        and
        product is null
      )
      AND
      (
        vendor <> ''
        and
        product <> ''
      )
    )
    OR
    -- ensure that the `product_version` is only set when `vendor` and `product` are
    (
      (
        vendor is not null
        and
        vendor <> ''
        and
        product is not null
        and
        product <> ''
        and
        product_version <> ''
      )
    )
  ),

  UNIQUE (component_name) ON CONFLICT REPLACE
);