Lunar SQL API Skill

Query Lunar's data using SQL. The SQL API provides read-only PostgreSQL access to components, checks, policies, domains, and PRs.

Quick Start

# Get connection string
lunar sql connection-string

# Connect interactively
psql $(lunar sql connection-string)

# Execute a query
psql $(lunar sql connection-string) -c "SELECT * FROM components_latest LIMIT 5"

Reference Documentation

SQL API Views

Use the local references/ files and embedded core view schemas below first. For full per-view SQL API details not covered there, use the hosted documentation backup below.

Component JSON Schema

The component_json column contains the merged Component JSON from all collectors. For schema conventions and structure:

Topic	Documentation
Schema conventions, presence detection, boolean patterns	references/component-json/conventions.md
Category reference (.repo, .sca, .k8s, etc.)	references/component-json/structure.md

Hosted Documentation Backup

The references/ files and embedded schemas below are the primary source. Only if they do not answer the question, fetch https://docs-lunar.earthly.dev/llms.txt to find the relevant hosted markdown page.

Relevant hosted pages include:

View	Documentation
Overview	https://docs-lunar.earthly.dev/sql-api/sql-api.md
components / components_latest	https://docs-lunar.earthly.dev/sql-api/views/components.md
component_deltas / component_deltas_latest	https://docs-lunar.earthly.dev/sql-api/views/component-deltas.md
checks / checks_latest	https://docs-lunar.earthly.dev/sql-api/views/checks.md
domains	https://docs-lunar.earthly.dev/sql-api/views/domains.md
initiatives	https://docs-lunar.earthly.dev/sql-api/views/initiatives.md
policies	https://docs-lunar.earthly.dev/sql-api/views/policies.md
prs	https://docs-lunar.earthly.dev/sql-api/views/prs.md

If a page still lacks enough context, ask the docs a specific, self-contained question with ?ask=<question> on that page URL, for example:

GET https://docs-lunar.earthly.dev/sql-api/sql-api.md?ask=How%20do%20I%20query%20latest%20checks%20for%20a%20component%3F

Core View Schemas

components / components_latest

Column	Type	Description
component_id	TEXT	Component identifier (e.g., github.com/foo/bar)
timestamp	TIMESTAMP	"Committed at" UTC timestamp of the git_sha
git_sha	TEXT	Git commit SHA
pr	BIGINT	PR number (NULL = default branch)
domain	TEXT	Domain in dotted format (e.g., payments.analytics)
owner	TEXT	Component owner
tags	TEXT[]	Array of tags
component_json	JSONB	Merged Component JSON from all collectors

checks / checks_latest

Column	Type	Description
component_id	TEXT	Component identifier
committed_at	TIMESTAMP	Commit timestamp
git_sha	TEXT	Git commit SHA
pr	BIGINT	PR number (NULL = default branch)
name	TEXT	Check name
description	TEXT	Check description
manifest_version	TEXT	Manifest version
initiative_id	TEXT	Parent initiative
policy_id	TEXT	Parent policy
enforcement	TEXT	draft, score, block-pr, block-release, block-pr-and-release
status	TEXT	pass, fail, pending, error, skipped
failure_reasons	TEXT[]	Failure reasons (NULL if passed)
staleness	INTERVAL	Time since last evaluation (NULL if current)

Key Concepts

Component Identification

A component version is uniquely identified by:

• component_id: Full path like github.com/org/repo/path
• git_sha: Git commit SHA
• pr: PR number (NULL for default branch)

-- Latest data for a component on default branch
SELECT * FROM components_latest
WHERE component_id = 'github.com/foo/bar'
  AND pr IS NULL;

-- Data for a specific PR
SELECT * FROM components_latest
WHERE component_id = 'github.com/foo/bar'
  AND pr = 123;

The _latest Views

Views with _latest suffix contain only the most recent git_sha for each pr in each component:

• Use _latest views for current state queries
• Use base views for historical/time-series analysis
• Filter by pr IS NULL for default branch data

Timestamp Consistency

The timestamp column represents the Git "committed at" time and is consistent across views for the same component_id + git_sha (named timestamp on components, committed_at on checks). Use this for joining time-series data.

Domain Hierarchy

Domains use dotted notation (e.g., payments.checkout.api). Query hierarchies with LIKE:

-- All components in payments domain (including subdomains)
WHERE domain = 'payments' OR domain LIKE 'payments.%'

-- Direct children only
WHERE domain LIKE 'payments.%' AND domain NOT LIKE 'payments.%.%'

JSONB Query Patterns

Operators

Operator	Description	Example
->	Get field as JSONB	component_json->'go'
->>	Get field as TEXT	component_json->'go'->>'version'
jsonb_path_exists()	Check path exists	jsonb_path_exists(component_json, '$.go.version')
@>	Contains	component_json @> '{"go": {}}'

Safe Value Extraction

Always check path existence before extraction:

SELECT component_id,
       component_json->'codecov'->'report'->'result'->'coverage'->>'total' AS coverage
FROM components_latest
WHERE jsonb_path_exists(component_json, '$.codecov.report.result.coverage.total')
  AND pr IS NULL;

Type Casting

The ->> operator returns TEXT. Cast explicitly:

-- Numeric comparison
WHERE (component_json->'coverage'->>'percentage')::NUMERIC >= 80

-- Boolean comparison
WHERE (component_json->'repo'->>'has_readme')::BOOLEAN = true

Common Query Patterns

Failing Checks by Domain

WITH component_domains AS (
  SELECT component_id, domain
  FROM components_latest
  WHERE pr IS NULL
)
SELECT domain, COUNT(*) AS failing_checks
FROM checks_latest
JOIN component_domains USING (component_id)
WHERE status = 'fail' AND pr IS NULL
GROUP BY domain
ORDER BY failing_checks DESC;

Blocked PRs

-- PRs blocked by checks
SELECT DISTINCT component_id, pr
FROM checks_latest
WHERE pr IS NOT NULL
  AND status = 'fail'
  AND enforcement = 'block-pr';

Check Status Time Series

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
FROM checks
WHERE component_id = 'github.com/foo/bar' AND pr IS NULL
GROUP BY committed_at
ORDER BY committed_at;

Components by Tag

SELECT component_id, domain, tags
FROM components_latest
WHERE 'soc2' = ANY(tags) AND pr IS NULL;

Cross-View Joins

Views share component_id, git_sha, and pr columns:

-- Components with their checks
SELECT c.component_id, c.domain, ch.name AS check_name, ch.status
FROM components_latest c
LEFT JOIN checks_latest ch USING (component_id, git_sha, pr)
WHERE c.pr IS NULL;

-- PRs by author with failing check count
SELECT p.component_id, p.pr, p.title, p.author_name,
       COUNT(*) FILTER (WHERE ch.status = 'fail') AS failing_checks
FROM prs p
LEFT JOIN checks_latest ch ON p.component_id = ch.component_id AND p.pr = ch.pr
GROUP BY p.component_id, p.pr, p.title, p.author_name;

Best Practices

1. Use _latest views for current state; base views for history
2. Always filter pr IS NULL when querying default branch
3. Check path existence with jsonb_path_exists() before extracting JSONB values
4. Cast JSONB values explicitly (::NUMERIC, ::BOOLEAN) after ->> extraction
5. Use CTEs for domain lookups to avoid repeated subqueries
6. Join on (component_id, git_sha, pr) for precise version matching