# 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

```sql
SELECT [columns | aggregates]
FROM table
[JOIN table ON condition]
[WHERE conditions]
[GROUP BY columns]
[ORDER BY column ASC|DESC, ...]
[LIMIT n]
```

### 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)
- **WHERE**: Comparisons (`=`, `!=`, `<`, `>`, `<=`, `>=`), `AND`, `OR`, `NOT`, `IS NULL`, `IS NOT NULL`, `LIKE`, `IN (...)`
- **GROUP BY**: Group results by one or more columns
- **ORDER BY**: Sort by columns; each target requires explicit `ASC` or `DESC`
- **LIMIT**: Cap the number of returned rows (server enforces max 1000; results report truncation and next OFFSET)

### 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

### Not Supported

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

## Data Clips

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

### List Data Clips
```graphql
query {
  listDataClips(first: 20) {
    edges {
      node {
        id
        name
        description
        queryString
        shareToken
        createdAt
        updatedAt
      }
    }
    totalCount
  }
}
```

### Get a Data Clip
```graphql
query GetClip($id: DataClipID!) {
  getDataClip(id: $id) {
    id name description queryString shareToken
    creatorId createdAt updatedAt
  }
}
```

### Create or Update a Data Clip
```graphql
mutation UpsertClip($input: UpsertDataClipInput!) {
  upsertDataClip(input: $input) {
    dataClip {
      id name description queryString shareToken
    }
    errors { path message }
  }
}
```
Variables (create):
```json
{
  "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`):
```json
{
  "input": {
    "id": "dcl_...",
    "name": "Updated Name",
    "queryString": "SELECT jobs.name, jobs.quantity FROM jobs LIMIT 100"
  }
}
```

### Preview a Query (Without Saving)
```graphql
query Preview($input: PreviewDataClipInput!) {
  previewDataClip(input: $input) {
    headers
    rows
    rowCount
    error
  }
}
```
Variables:
```json
{ "input": { "query": "SELECT jobs.name, jobs.status FROM jobs LIMIT 5" } }
```

### Delete a Data Clip
```graphql
mutation DeleteClip($id: DataClipID!) {
  deleteDataClip(id: $id) {
    success
    errors { path message }
  }
}
```

### Roll (Regenerate) Share Token
Invalidates old share links and generates a new token:
```graphql
mutation RollToken($input: RollShareTokenInput!) {
  rollDataClipShareToken(input: $input) {
    dataClip { id shareToken }
    errors { path message }
  }
}
```
Variables:
```json
{ "input": { "id": "dcl_..." } }
```

## Public Data Export (Share Token)

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

```text
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()`:
```lua
local ok, result = ctx.sql.query([[
  SELECT jobs.name, jobs.quantity, jobs.status
  FROM jobs
  WHERE jobs.status = 'READY'
  ORDER BY jobs.due_at ASC
  LIMIT 50
]])
if not ok then error(result) end

-- result.headers = {"name", "quantity", "status"}
-- 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
```sql
SELECT jobs.status, COUNT(*) AS job_count
FROM jobs
GROUP BY jobs.status
ORDER BY job_count DESC
```

### Example 2: Jobs with Component Details
```sql
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 = 'READY'
ORDER BY jobs.due_at ASC
LIMIT 100
```

### Example 3: Overdue Jobs
```sql
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
```sql
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
4. Save as clip with the `upsertDataClip` mutation
5. Share the public URL: `/api/v1/clips/{shareToken}.csv`