> ## Documentation Index
> Fetch the complete documentation index at: https://docs.orquestr.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Saved SQL

> Org-scoped read-only saved queries exposed as saved.<uuid> functions.

# Saved SQL

Base: `/v1/saved-sql-queries`. Authenticate with an API key:

```http theme={"system"}
Authorization: Bearer sk_live_…
```

| Method   | Path   | Body / params                                                                      | Notes                                      |
| -------- | ------ | ---------------------------------------------------------------------------------- | ------------------------------------------ |
| `GET`    | `/`    | `?customer_id=` (required), `adapter?`, `device_id?`, optional `page` / `per_page` | `{ data, meta }`                           |
| `POST`   | `/`    | See below                                                                          | Store encrypted read-only SQL → `{ data }` |
| `GET`    | `/:id` | path                                                                               | Retrieve (SQL decrypted) → `{ data }`      |
| `PATCH`  | `/:id` | `{ name?, sql?, device_id? \| null }`                                              | Update → `{ data }`                        |
| `DELETE` | `/:id` | —                                                                                  | `200` — `{ data: { ok: true } }`           |

Saved queries appear on the device function list as `saved.<uuid>` and are always executed with **read-only** SQL policy. Invoke accepts **no** client `input` (SQL stays server-side).

## Validate before save

`POST /v1/devices/{device_id}/functions/sicarv4.custom.validate` checks the SQL on an enrolled device and **does not store it**. Use it before `POST /v1/saved-sql-queries`. Scope: `functions.invoke`.

```bash theme={"system"}
curl -s -X POST "$API/v1/devices/$DEVICE_ID/functions/sicarv4.custom.validate" \
  -H "Authorization: Bearer $ORQUESTR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"input":{"sql":"SELECT art_id, clave, descripcion FROM articulo WHERE status = 1 LIMIT 100"}}'
```

`200` with `data.result.ok: true` means the tables resolved and, for a `SELECT`, the zero-row probe succeeded. Mutating SQL is `400` and is never saved. `ok: false` means the device rejected the statement (`schema_error` / `explain_error`).

## Create saved query

```bash theme={"system"}
curl -s -X POST "$API/v1/saved-sql-queries" \
  -H "Authorization: Bearer $ORQUESTR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "customer_id": "ORG_UUID",
    "adapter": "sicarv4",
    "name": "Top sellers",
    "sql": "SELECT art_id, clave, descripcion FROM articulo WHERE status = 1 LIMIT 100",
    "device_id": null
  }'
```

| Field         | Type   | Required | Notes                                                 |
| ------------- | ------ | -------- | ----------------------------------------------------- |
| `customer_id` | UUID   | Yes      |                                                       |
| `adapter`     | enum   | Yes      | `sicarv4` · `eleventa` · `microsip` · `mybusinesspos` |
| `name`        | string | Yes      | 1–200 chars; unique per scope                         |
| `sql`         | string | Yes      | Read-only (SELECT / SHOW / DESCRIBE)                  |
| `device_id`   | UUID   | No       | `null` = all devices for adapter                      |

## Get saved query

```bash theme={"system"}
curl -s "$API/v1/saved-sql-queries/$SAVED_SQL_ID" \
  -H "Authorization: Bearer $ORQUESTR_API_KEY"
```

`200` response (SQL is decrypted server-side):

```json theme={"system"}
{
  "data": {
    "id": "e9999999-9999-4999-8999-999999999999",
    "customer_id": "o2222222-2222-2222-2222-222222222222",
    "adapter": "sicarv4",
    "name": "Top sellers last week",
    "sql": "SELECT clave, SUM(cantidad) AS qty FROM ventas GROUP BY clave ORDER BY qty DESC LIMIT 20",
    "device_id": null,
    "created_by": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "created_at": "2026-09-12T10:00:00.000Z",
    "updated_at": "2026-09-14T11:00:00.000Z"
  }
}
```

## List with filters

```bash theme={"system"}
curl -s "$API/v1/saved-sql-queries?customer_id=ORG_UUID&adapter=sicarv4&device_id=DEVICE_UUID" \
  -H "Authorization: Bearer $ORQUESTR_API_KEY"
```

When `device_id` is set, returns queries scoped to that device **plus** global queries (`device_id IS NULL`). Response: `{ data: Query[], meta }`. Optional `page` / `per_page` (max 100); omit both for the full set.

## Delete saved query

```bash theme={"system"}
curl -s -X DELETE "$API/v1/saved-sql-queries/$SAVED_SQL_ID" \
  -H "Authorization: Bearer $ORQUESTR_API_KEY"
```

`200` response:

```json theme={"system"}
{
  "data": {
    "ok": true
  }
}
```