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:

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:

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

checks / checks_latest

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 gitsha 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 componentid + gitsha (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

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 componentid, gitsha, 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 jsonbpathexists() 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 (componentid, gitsha, pr) for precise version matching