checks

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
timestamp	TIMESTAMPTZ	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_name	TEXT	The identifier of the initiative this check's policy belongs to
policy_name	TEXT	The name 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, no-data, error
failure_reason	TEXT[]	Array of human-readable reasons the check failed. Set to NULL if the check passed.
metadata	JSONB	The metadata of the check that was run

Notes

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

• The timestamp 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 set of rows in this view will dynamically change as collectors provide more data, and policies are iteratively evaluated against the updated information. (For example, new checks may appear as new policies are evaluated, or existing checks may switch status from no-data to pass or fail.)

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 'no-data' THEN 3
            WHEN 'pass' THEN 4
          END ASC;

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

SELECT
  timestamp,
  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 = 'no-data' THEN 1 ELSE 0 END) AS no_data,
  SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) AS errored
FROM checks
WHERE component_id = 'github.com/foo/bar/buz'
  AND pr IS NULL
GROUP BY timestamp
ORDER BY timestamp 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
  timestamp,
  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 timestamp
ORDER BY timestamp 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';