MicroSQL Authoring Guide

MicroSQL is a restricted SQL dialect for querying print domain reporting tables. Tenant isolation is enforced automatically — you never write WHERE tenant_id = '...'.

Quick Start

1. Discover the schema — call describe_sql_schema to see all tables, columns, and descriptions
2. Write a query — use standard SELECT syntax (see below)
3. Validate — call validate_sql to check syntax and see inferred column types
4. Execute — call execute_sql to run the query and get tabular results
5. Save as Data Clip — use the upsertDataClip GraphQL mutation to persist the query

MicroSQL Syntax

SELECT [DISTINCT] [columns | aggregates | CASE ... END]
FROM table
[[LEFT] JOIN table ON condition]
[WHERE conditions]
[GROUP BY columns]
[ORDER BY columns ASC|DESC]
[LIMIT n [OFFSET m]]

Supported Features

• SELECT: Column references, which MUST be qualified as table.column — a bare column is rejected with "Invalid SELECT column: <name>", even in a single-table query. Also aggregate functions and aliases (AS name). Qualification applies in WHERE, GROUP BY and ORDER BY too.
• FROM: Single table or multiple tables via JOIN
• JOIN: JOIN table ON left.col = right.col (INNER JOIN); LEFT JOIN is accepted too
• DISTINCT: SELECT DISTINCT table.column
• CASE: CASE WHEN … THEN … ELSE … END AS name in the SELECT list
• WHERE: Comparisons (=, !=, <, >, <=, >=), AND, OR, IS NULL, IS NOT NULL, IN (...), BETWEEN … AND …, LIKE / NOT LIKE. An OR must sit inside parentheses (WHERE (a = 1 OR a = 2)); outside them the query is rejected with "OR outside parentheses". There is no prefix NOT — use !=, NOT LIKE or IS NOT NULL. In LIKE, % and _ are matched literally, not as wildcards, so LIKE 'COMP%' only matches the exact text COMP%.
• GROUP BY: Group results by one or more columns
• ORDER BY: Sort by columns; the direction ASC or DESC is required
• LIMIT: Cap the number of returned rows (server enforces max 1000); LIMIT n OFFSET m pages through larger results

Aggregate Functions

• COUNT(column) or COUNT(*) — count rows
• SUM(column) — sum numeric values
• AVG(column) — average numeric values
• MIN(column) — minimum value
• MAX(column) — maximum value

Scalar Functions

• Date and time: NOW(), DATE_TRUNC('day', t.col), DATE_PART('year', t.col)
• Text: UPPER, LOWER, LENGTH, TRIM, CONCAT(a, b)
• Numbers: ROUND, ABS, CEIL, FLOOR, GREATEST(a, b), LEAST(a, b)
• Nulls: COALESCE(a, b), NULLIF(a, b)

Not Supported

• Subqueries
• UNION / INTERSECT / EXCEPT
• Window functions (OVER, PARTITION BY)
• INSERT / UPDATE / DELETE (read-only)
• HAVING
• CAST
• CTEs (WITH ... AS)

MCP Tools

execute_sql

Run a MicroSQL query and get tabular results (headers + rows).

execute_sql(query: "SELECT jobs.name, jobs.quantity FROM jobs WHERE jobs.status = 'COMPLETED' LIMIT 10")

validate_sql

Parse and validate a query without executing. Returns inferred result columns with data types.

validate_sql(query: "SELECT jobs.name, COUNT(*) FROM jobs GROUP BY jobs.name")
→ Valid query.
  Result columns:
    jobs.name  —
    COUNT(*)         integer (aggregate)

describe_sql_schema

List all available tables, columns, types, and descriptions. No arguments needed.

describe_sql_schema()
→ Full DDL-style schema reference with column descriptions

search_sql_tables

Semantic search over SQL tables. Use to orient — find which table has the data you need.

search_sql_tables(query: "production orders")
→ 1. jobs (score: 0.91)
     jobs: Production jobs with scheduling, status tracking, and customer references

search_sql_columns

Semantic search over SQL columns. Use to find specific fields by meaning.

search_sql_columns(query: "delivery deadline")
→ 1. jobs.due_at (score: 0.87)
     jobs.due_at: Delivery due date (timestamp)

Both accept optional top_k parameter (default 5, max 20).

Data Clips

Data clips are saved MicroSQL queries with a persistent share token for public CSV/JSON export.

List Data Clips

query {
  listDataClips(first: 20) {
    edges {
      node {
        id
        name
        description
        queryString
        shareToken
        createdAt
        updatedAt
      }
    }
    totalCount
  }
}

Get a Data Clip

query GetClip($id: DataClipID!) {
  getDataClip(id: $id) {
    id name description queryString shareToken
    creatorId createdAt updatedAt
  }
}

Create or Update a Data Clip

mutation UpsertClip($input: UpsertDataClipInput!) {
  upsertDataClip(input: $input) {
    dataClip {
      id name description queryString shareToken
    }
    errors { path message }
  }
}

Variables (create):

{
  "input": {
    "name": "Completed Jobs This Month",
    "description": "All jobs with status COMPLETED, ordered by completion date",
    "queryString": "SELECT jobs.name, jobs.quantity, jobs.actual_end_at FROM jobs WHERE jobs.status = 'COMPLETED' ORDER BY jobs.actual_end_at DESC LIMIT 500"
  }
}

Variables (update — include id):

{
  "input": {
    "id": "dcl_...",
    "name": "Updated Name",
    "queryString": "SELECT jobs.name, jobs.quantity FROM jobs LIMIT 100"
  }
}

Preview a Query (Without Saving)

query Preview($input: PreviewDataClipInput!) {
  previewDataClip(input: $input) {
    headers
    rows
    rowCount
    error
  }
}

Variables:

{ "input": { "query": "SELECT jobs.name, jobs.status FROM jobs LIMIT 5" } }

Delete a Data Clip

mutation DeleteClip($id: DataClipID!) {
  deleteDataClip(id: $id) {
    success
    errors { path message }
  }
}

Roll (Regenerate) Share Token

Invalidates old share links and generates a new token:

mutation RollToken($input: RollShareTokenInput!) {
  rollDataClipShareToken(input: $input) {
    dataClip { id shareToken }
    errors { path message }
  }
}

Variables:

{ "input": { "id": "dcl_..." } }

Public Data Export (Share Token)

Data clips can be exported publicly via share token — no authentication required:

GET /api/v1/clips/{shareToken}.csv
GET /api/v1/clips/{shareToken}.json
GET /api/v1/clips/{shareToken}.ndjson

The share token is generated when a data clip is created. Use rollDataClipShareToken to regenerate it and invalidate old links.

Lua Integration

In custom app server scripts (Lua), use ctx.sql.query():

local ok, result = ctx.sql.query([[
  SELECT jobs.name, jobs.quantity, jobs.status
  FROM jobs
  WHERE jobs.status = 'PRESS'
  ORDER BY jobs.due_at ASC
  LIMIT 50
]])
if not ok then error(result) end

-- result.headers = {"jobs.name", "jobs.quantity", "jobs.status"}  (headers keep the table prefix)
-- result.rows = {{...}, {...}, ...}
-- result.rowCount = 50

ML Integration

ML model configs accept MicroSQL via the dataQuerySQL field as an alternative to Lua data scripts. The SQL query provides the training data directly.

Worked Examples

Example 1: Simple Status Report

SELECT jobs.status, COUNT(*) AS job_count
FROM jobs
GROUP BY jobs.status
ORDER BY job_count DESC

Example 2: Jobs with Component Details

SELECT jobs.name, jobs.status,
       components.name AS component_name,
       components.kind, components.quantity
FROM jobs
JOIN components ON components.job_id = jobs.id
WHERE jobs.status = 'PRESS'
ORDER BY jobs.due_at ASC
LIMIT 100

Example 3: Overdue Jobs

SELECT jobs.name, jobs.job_number, jobs.due_at, jobs.status
FROM jobs
WHERE jobs.status != 'COMPLETED'
  AND jobs.status != 'CANCELLED'
  AND jobs.due_at < '2026-02-22'
ORDER BY jobs.due_at ASC

Example 4: Component Mix by Kind

SELECT components.kind,
       COUNT(*) AS count,
       AVG(components.quantity) AS avg_quantity
FROM components
GROUP BY components.kind
ORDER BY count DESC

Example 5: Full Data Clip Workflow

1. Discover the schema — what tables and columns exist
2. Draft the query and check it parses
3. Preview the results before saving anything (describe_sql_schema(), validate_sql(query:) and execute_sql(query:) do these three.)
4. Save as clip with the upsertDataClip mutation

   execute_graphql_mutation(query: "mutation { upsertDataClip(input: { name: \"Monthly Report\", queryString: \"SELECT ...\" }) { dataClip { id shareToken } errors { path message } } }")

5. Share the public URL: /api/v1/clips/{shareToken}.csv