checks

Schema reference for the checks and checks_latest SQL views — every policy check evaluation with status, enforcement, component, and PR context.

checks
checks_latest

The checks view is a time series representation of the collection of checks that Lunar ran on components.

A subset of this data is available in the checks_latest view, which contains only the checks for the latest git_sha in each pr, in each component. To get the latest row for the default branch, you can filter this view by pr IS NULL.

Schema

Column	Type	Description
component_id	TEXT	The identifier for the component - e.g. github.com/foo/bar/buz
committed_at	TIMESTAMP	The "committed at" UTC timestamp of the git_sha
git_sha	TEXT	The Git commit SHA of the component JSON
pr	BIGINT	The pull request number if the commit is part of a pull request. Set to NULL for the default branch
name	TEXT	The name of the check that was run
description	TEXT	The description of the check that was run
initiative_id	TEXT	The identifier of the initiative this check's policy belongs to
policy_id	TEXT	The identifier of the policy this check is part of
enforcement	TEXT	The enforcement level of the check. Can be one of draft, score, block-pr, block-release, block-pr-and-release
status	TEXT	The status of the check. Can be one of pass, fail, pending, error, skipped
failure_reasons	TEXT[]	Array of human-readable reasons the check failed. Set to NULL if the check passed.
staleness	INTERVAL	The time since the check was last evaluated. Set to NULL if the check is not stale.
manifest_version	TEXT	The version of the manifest used for this check

Notes

• To get the set of checks for a given component version, you need to filter by component_id, and git_sha.

• The committed_at is guaranteed to be the same for a given git_sha of a component_id. This timestamp will also match entries in other views such as components and component_deltas.

• While a certain git_sha for a component is being evaluated, the row may use a stale result from a previous version of the code. In such cases, the staleness column will be set to the time since the check was last evaluated.

Usage examples

Retrieve the checks associated with the latest git_sha for a given component, ordered by status.

SELECT *
FROM checks_latest
WHERE component_id = 'github.com/foo/bar/buz'
  AND pr IS NULL
ORDER BY  CASE status
            WHEN 'fail' THEN 1
            WHEN 'error' THEN 2
            WHEN 'pending' THEN 3
            WHEN 'pass' THEN 4
            WHEN 'skipped' THEN 5
          END ASC;

Retrieve time series data of the number of checks that passed, failed, had no data, or errored out over time.

SELECT
  committed_at,
  SUM(CASE WHEN status = 'pass' THEN 1 ELSE 0 END) AS passed,
  SUM(CASE WHEN status = 'fail' THEN 1 ELSE 0 END) AS failed,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) AS errored,
  SUM(CASE WHEN status = 'skipped' THEN 1 ELSE 0 END) AS skipped
FROM checks
WHERE component_id = 'github.com/foo/bar/buz'
  AND pr IS NULL
GROUP BY committed_at
ORDER BY committed_at ASC;

Retrieve the checks that are failing for all components with the tag soc2.

SELECT *
FROM checks_latest
WHERE component_id IN (
    SELECT component_id
    FROM components_latest
    WHERE 'soc2' = ANY(tags)
      AND pr IS NULL
  )
  AND pr IS NULL
  AND status = 'fail';

Retrieve the checks that are blocking PRs right now.

SELECT *
FROM checks_latest
WHERE pr IS NOT NULL
  AND status = 'fail'
  AND enforcement = 'block-pr';

Count the number of PRs that are blocked by checks for each domain.

WITH component_domains AS (
  SELECT
    component_id,
    domain
  FROM components_latest
  WHERE pr IS NULL
)
SELECT 
  domain,
  COUNT(DISTINCT pr) AS blocked_prs
FROM checks_latest
JOIN component_domains USING (component_id)
WHERE status = 'fail'
  AND enforcement = 'block-pr'
  AND pr IS NOT NULL
GROUP BY domain;

Total blocking checks for the domain payments over time.

WITH component_domains AS (
  SELECT
    component_id,
    domain
  FROM components_latest
  WHERE (domain = 'payments' OR domain LIKE 'payments.%')
    AND pr IS NULL
)
SELECT
  committed_at,
  COUNT(*) AS blocking_checks
FROM checks
JOIN component_domains USING (component_id)
WHERE status = 'fail'
  AND enforcement = 'block-pr'
  AND pr IS NOT NULL
GROUP BY committed_at
ORDER BY committed_at ASC;

All blocking failing checks of all PRs authored by Jane.

WITH janes_prs AS (
  SELECT component_id, pr
  FROM prs
  WHERE author_email = '[e-mail]'
)
SELECT *
FROM checks_latest
JOIN janes_prs USING (component_id, pr)
WHERE status = 'fail'
  AND enforcement = 'block-pr';