rekor

Query

Rekor provides two ways to query data: SQL queries for agents and advanced use cases, and a Filter DSL for the frontend list endpoint and trigger conditions.

SQL Query

Execute read-only SQL queries against workspace data. Supports filtering, aggregation, JOINs across collections, CTEs, and array operations.

Endpoint

POST /v1/{workspace_id}/sql

Request

{
  "query": "SELECT ... FROM records FINAL WHERE workspace_id = {workspace_id:String} AND ...",
  "params": { "custom_param": "value" }
}

Response

{
  "data": [ { "column": "value", ... }, ... ],
  "meta": { "rows": 100, "has_more": false }
}

Rules

  • Always include workspace_id = {workspace_id:String} — the placeholder is auto-filled with the workspace from the URL
  • Always include deleted = false to exclude soft-deleted records
  • Always add FINAL after table names (e.g., FROM records FINAL)
  • Read-only — only SELECT statements are allowed
  • Max 1000 rows per query (automatically enforced)
  • 10 second timeout, 512MB memory limit

Available tables

TableDescription
recordsAll records across collections. Filter by collection = 'name'.
relationshipsAll relationships. Filter by rel_type = 'name'.
collectionsCollection metadata and schemas.
workspacesWorkspace metadata.
operations_logAudit log of all write operations.

Accessing JSON fields

Record data is stored in a data column (JSON type). Access fields using subcolumn syntax:

-- String field
data.status.:String

-- Numeric field (use CAST for type-safe conversion)
CAST(data.amount, 'Float64')

-- Date filtering
CAST(data.due_at.:String, 'Date') < today()

Working with arrays

Arrays embedded in JSON can be queried using subcolumn syntax and array functions:

-- Sum values from an array of objects
arraySum(CAST(data.line_items[].amount, 'Array(Float64)'))

-- Explode array elements into rows
SELECT item.description.:String, CAST(item.amount, 'Float64')
FROM records FINAL
ARRAY JOIN data.line_items[] as item
WHERE workspace_id = {workspace_id:String}
  AND collection = 'invoices'
  AND deleted = false

CTEs and JOINs

Use Common Table Expressions to join data across tables. This is useful for reconciliation queries that combine records with relationship metadata:

WITH
  inv AS (
    SELECT id, data.invoice_number.:String as invoice_number,
      arraySum(CAST(data.line_items[].amount, 'Array(Float64)')) as total
    FROM records FINAL
    WHERE workspace_id = {workspace_id:String}
      AND collection = 'invoices' AND deleted = false
  ),
  pay AS (
    SELECT target_id,
      sum(CAST(data.allocated, 'Float64')) as total_paid
    FROM relationships FINAL
    WHERE workspace_id = {workspace_id:String}
      AND rel_type = 'payment_for' AND deleted = false
    GROUP BY target_id
  )
SELECT inv.invoice_number, inv.total,
  coalesce(pay.total_paid, 0) as paid,
  inv.total - coalesce(pay.total_paid, 0) as balance
FROM inv
LEFT JOIN pay ON pay.target_id = inv.id
ORDER BY balance DESC

Cross-Workspace Query

Query data across all workspaces in an account. Useful for dashboards, reporting, and analytics that span multiple workspaces.

Endpoint

POST /v1/accounts/{account_id}/sql

Request

{
  "query": "SELECT workspace_id, collection, count() as record_count FROM records FINAL WHERE account_id = {account_id:String} AND deleted = false GROUP BY workspace_id, collection ORDER BY record_count DESC"
}

Uses {account_id:String} instead of {workspace_id:String}. The token must have read:records permission. Results are scoped to the token's account.

Examples

-- Record counts across all workspaces
SELECT workspace_id, count() as total
FROM records FINAL
WHERE account_id = {account_id:String} AND deleted = false
GROUP BY workspace_id

-- Search for a record across all workspaces
SELECT workspace_id, collection, id, data.name.:String as name
FROM records FINAL
WHERE account_id = {account_id:String}
  AND deleted = false
  AND data.name.:String ILIKE '%acme%'

Filter DSL

JSON filter expressions used by the frontend list endpoint (GET /v1/{workspace}/records/{collection}?filter=...) and trigger conditions.

Simple filter

{
  "field": "data.status",
  "op": "eq",
  "value": "active"
}

Compound filter

{
  "and": [
    { "field": "data.status", "op": "eq", "value": "issued" },
    { "field": "data.amount", "op": "gt", "value": 5000 }
  ]
}

Operators

OperatorDescriptionExample
eqEqual{"field":"data.status","op":"eq","value":"active"}
neqNot equal{"field":"data.status","op":"neq","value":"deleted"}
gtGreater than{"field":"data.amount","op":"gt","value":100}
gteGreater than or equal{"field":"data.amount","op":"gte","value":100}
ltLess than{"field":"data.amount","op":"lt","value":100}
lteLess than or equal{"field":"data.amount","op":"lte","value":100}
inIn array{"field":"data.status","op":"in","value":["active","pending"]}
not_inNot in array{"field":"data.status","op":"not_in","value":["deleted"]}
likePattern match (case-sensitive){"field":"data.name","op":"like","value":"%Jane%"}
ilikePattern match (case-insensitive){"field":"data.name","op":"ilike","value":"%jane%"}
is_nullIs null{"field":"data.email","op":"is_null"}
is_not_nullIs not null{"field":"data.email","op":"is_not_null"}
hasArray contains{"field":"data.tags","op":"has","value":"urgent"}

Sorting

{
  "sort": [
    { "field": "data.created_at", "direction": "desc" },
    { "field": "data.name", "direction": "asc" }
  ]
}

Pagination

Use limit and offset query parameters:

GET /v1/{workspace}/records/{collection}?limit=20&offset=40

Field selection

Return a subset of fields with the fields parameter:

GET /v1/{workspace}/records/{collection}?fields=id,data.name,data.status
Query — Rekor