> For the complete documentation index, see [llms.txt](https://docs-lunar.earthly.dev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs-lunar.earthly.dev/sql-api/views/checks.md).

# 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`                                                        |
| `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`](/sql-api/views/components.md) and [`component_deltas`](/sql-api/views/component-deltas.md).
* 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.

```sql
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.

```sql
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`.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
WITH janes_prs AS (
  SELECT component_id, pr
  FROM prs
  WHERE author_email = 'jane@example.com'
)
SELECT *
FROM checks_latest
JOIN janes_prs USING (component_id, pr)
WHERE status = 'fail'
  AND enforcement = 'block-pr';
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs-lunar.earthly.dev/sql-api/views/checks.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.