rekorpreview

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 base data. Supports filtering, aggregation, JOINs across record_types, CTEs, and array operations.

Endpoint

POST /v1/{base_id}/sql

Request

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

Response

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

Rules

  • Always include BOTH org_id = {org_id:String} AND base_id = {base_id:String} — a base id is unique per-org, not globally, so both are required to isolate your data. Both placeholders are auto-filled server-side from your authenticated org and the base in the URL
  • Always include deleted = false to exclude soft-deleted records
  • The records table also has archived and cancelled columns — add archived = false to query only active records
  • Read-only — only SELECT statements are allowed
  • Queries always see the latest version of each row — the server handles deduplication
  • Max 1000 rows per query (automatically enforced)
  • 10 second timeout, 512MB memory limit

Available tables

TableDescription
recordsAll records across record_types. Filter by record_type = 'name'.
relationshipsAll relationships. Filter by rel_type = 'name'.
record_typesRecordType metadata and schemas.
basesBase metadata.
operations_logAudit log of all write operations.
organizationOrg-level metadata (plan, status). Requires an {org_id:String} predicate.

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
ARRAY JOIN data.line_items[] as item
WHERE org_id = {org_id:String} AND base_id = {base_id:String}
  AND record_type = '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
    WHERE org_id = {org_id:String} AND base_id = {base_id:String}
      AND record_type = 'invoices' AND deleted = false
  ),
  pay AS (
    SELECT target_id,
      sum(CAST(data.allocated, 'Float64')) as total_paid
    FROM relationships
    WHERE org_id = {org_id:String} AND base_id = {base_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-Base Query

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

Endpoint

POST /v1/orgs/{org_id}/sql

Request

{
  "query": "SELECT base_id, record_type, count() as record_count FROM records WHERE org_id = {org_id:String} AND deleted = false GROUP BY base_id, record_type ORDER BY record_count DESC"
}

Uses {org_id:String} instead of {base_id:String}. The token must have read:records permission. Results are scoped to the token's org.

Examples

-- Record counts across all bases
SELECT base_id, count() as total
FROM records
WHERE org_id = {org_id:String} AND deleted = false
GROUP BY base_id

-- Search for a record across all bases
SELECT base_id, record_type, id, data.name.:String as name
FROM records
WHERE org_id = {org_id:String}
  AND deleted = false
  AND data.name.:String ILIKE '%acme%'

Filter DSL

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

List queries return active records by default — archived records are excluded. To query archived records, add a filter condition on archived (e.g. {"field":"archived","op":"eq","value":true}); your condition then decides which records you see. This applies to record and relationship lists (record traversal is active-only, with no override); raw SQL queries apply no default — add archived = false yourself for active-only results.

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%"}
searchFuzzy/approximate text match — results are ranked by closeness, each carrying a _search_score (0–1). Query-only (not valid in trigger conditions).{"field":"data.car_model","op":"search","value":"honda civic"}
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"}

The search operator matches a field by approximate value (when you have a near-correct string but not the exact stored one). Combine it with exact operators in the same filter to narrow first, then rank; results come back ordered by _search_score (highest first). Every field is searchable by default — set an optional x-search hint on the field in the record_type schema (mode: name | text | fuzzy, threshold, or searchable: false to forbid it) to tune matching.

Datetimes

Declare datetime fields in the record_type schema with "format": "date-time" (or "format": "date"). Set an optional record_type x-timezone (e.g. "America/Sao_Paulo") — or a base-wide settings.timezone — to interpret values written without an explicit offset; the default is UTC. eq, neq, in, not_in and the range operators (gt/gte/lt/lte) match datetimes by point in time, so the exact representation you pass (T vs space separator, with or without an offset) does not change the result; a value with no offset is read in the configured timezone. Use eq for an exact datetime — like/ilike are substring matching only. Raw SQL queries are not rewritten this way, so match the stored value as returned.

Sorting

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

Pagination

Use limit and offset query parameters:

GET /v1/{base_id}/records/{record_type}?limit=20&offset=40

Field selection

Return a subset of fields with the fields parameter:

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