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

# Query events with SQL

> Run read-only ClickHouse SQL over your usage events and rollups, and stream the result.

`POST /v1/events.query` runs a read-only [ClickHouse](https://clickhouse.com/docs/sql-reference) SQL query over your usage events and rollups. It streams compressed newline-delimited JSON using the same `AnalyticsQueryLine` contract as [analytics.query](https://docs.useautumn.com/documentation/query-with-sql.md), but **the SQL dialect is ClickHouse, not DuckDB**.

Each query sees only your organization and the environment of the key you use: a sandbox key reads sandbox events, and a live key reads live events.

> [!NOTE]
>
> This endpoint is disabled by default. Contact [support@useautumn.com](mailto:support@useautumn.com) to enable it. Its limits and concurrency pool are separate from `analytics.query`.

## Available tables

| Table | Use | Aggregate |
| - | - | - |
| `events` | Raw events, properties, deductions, exact values | `count()`, `sum(value)` |
| `events_customer_hourly_mv` | Customer/entity/plan totals per UTC hour | `sum(event_count)`, `sum(total_value)` |
| `events_customer_daily_mv` | Customer/entity totals per UTC day | `sumMerge(event_count)`, `sumMerge(total_value)` |
| `events_customer_monthly_mv` | Customer/entity/plan totals per UTC month | `sumMerge(event_count)`, `sumMerge(total_value)` |
| `events_org_hourly_mv` | Organization-wide totals per UTC hour | `sum(event_count)`, `sum(total_value)` |
| `event_names_mv` | Event names and their counts | `sum(event_count)` |

Always aggregate rollup rows with `GROUP BY`; multiple rows can represent the same group before background merges. Daily and monthly columns are aggregate states: selecting them without `sumMerge` returns unreadable state bytes. Rollup `total_value` uses approximate `Float64` values. For exact decimal totals, query `sum(value)` on `events`.

Submit a single `SELECT` query, optionally starting with a CTE (`WITH`), without a terminating semicolon. Multiple statements, user `FORMAT` clauses and `%` templates are not supported; Autumn controls the output format.

`SETTINGS` clauses are rejected **anywhere** in the query, including subqueries, CTEs and UNION branches. You cannot override the server's limits. `FINAL` and `PREWHERE` are not supported. Use ordinary `WHERE` filters and aggregates instead.

## Running a query

```typescript TypeScript theme={null}
import { Autumn } from "autumn-js";

const autumn = new Autumn({ secretKey: "am_sk_test_1234" });

const stream = await autumn.events.query({
  sql: "SELECT event_name, count() AS event_count FROM events GROUP BY event_name",
});

for await (const line of stream) {
  switch (line.type) {
    case "columns":
      console.log(line.columns);
      break;
    case "row":
      console.log(line.values);
      break;
    case "end":
      console.log(line.rows, line.truncated);
      break;
    case "error":
      throw new Error(`${line.code}: ${line.message}`);
  }
}
```

```python Python theme={null}
import json
from contextlib import closing

from autumn_sdk import Autumn

with Autumn(secret_key="am_sk_test_1234") as autumn:
    res = autumn.events.query(
        sql="SELECT event_name, count() AS event_count FROM events GROUP BY event_name",
    )

    with closing(res.result) as stream:
        for raw in stream.iter_lines():
            if not raw:
                continue
            line = json.loads(raw)
            if line["type"] == "columns":
                print(line["columns"])
            elif line["type"] == "row":
                print(line["values"])
            elif line["type"] == "end":
                print(line["rows"], line["truncated"])
            elif line["type"] == "error":
                raise RuntimeError(f"{line['code']}: {line['message']}")
```

```bash cURL theme={null}
curl --compressed -X POST "https://api.useautumn.com/v1/events.query" \
  -H "Authorization: Bearer $AUTUMN_SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT event_name, count() AS event_count FROM events GROUP BY event_name"}'
```

The TypeScript SDK parses the stream for you and yields typed `AnalyticsQueryLine` objects. If you already hold a raw response body, for example from `fetch`, `readAnalyticsQueryLines(body)` from the same package does the same parsing. Breaking out of the loop early cancels the request.

The Python SDK returns the raw streaming `httpx` response in `res.result`. Iterate it with `iter_lines()` and close it when you're done, which the `with` block does for you.

## The response stream

The response has `Content-Type: application/x-ndjson`: one JSON object per line, each with a `type` field.

```json theme={null}
{"type":"columns","columns":[{"name":"event_name","type":"String"},{"name":"event_count","type":"UInt64"}]}
{"type":"row","values":["api_call","42"]}
{"type":"row","values":["message_sent","17"]}
{"type":"end","rows":2,"truncated":false,"stopped_by":null}
```

| Line | When | Fields |
| - | - | - |
| `columns` | Always first | `columns`: each column's `name` and ClickHouse `type` |
| `row` | Once per result row | `values`: the row's values in column order |
| `end` | Last line on success | `rows`, `truncated`, `stopped_by` |
| `error` | Last line if the query failed after streaming began | `code`, `message`, optional `details` |

How values are encoded in `row` lines:

* `UInt64`, `Int64`, wider integers and `Decimal` values are strings, so counts and amounts keep their precision. Parse them with `BigInt` or a decimal library rather than `Number` when precision matters.
* Arrays and unnamed tuples are JSON arrays. Named tuples may be JSON objects. Maps and JSON columns are JSON objects.
* Date and timestamp values are strings. Event timestamps are UTC.
* Nullable values are JSON `null`. Non-finite floats are also `null`.

### End lines, error lines and dropped connections

A successful stream always ends with an `end` line. If the result goes over the 1 GiB output cap, the stream stops early and the `end` line has `truncated: true` and `stopped_by: "byte_cap"`.

If the query fails after rows have started streaming, the last line is an `error` line instead, for example:

```json theme={null}
{"type":"error","code":"statement_timeout","message":"Query exceeded the 30s compute limit for your plan and was cancelled. ...","details":{"limit_ms":30000}}
```

If the stream ends with neither an `end` nor an `error` line, the connection dropped and the result is incomplete. Treat it as a failure and retry rather than using the partial rows.

## Errors before streaming

Errors that happen before the first line are regular JSON error responses with `message`, `code`, `env` and sometimes `details`. The SDKs raise them as API errors.

| Status | Code | Meaning |
| - | - | - |
| 400 | `sql_error` | The SQL is invalid or not allowed |
| 408 | `statement_timeout` | The query hit the compute limit. `details.limit_ms` holds the limit |
| 429 | `rate_limited` | You already have the maximum number of queries running |
| 503 | `endpoint_disabled` / `tb_unavailable` | Querying is temporarily unavailable. Retry later |

## Limits

| Limit | Value |
| - | - |
| Compute time per query | 30 seconds |
| Uncompressed output per query | 1 GiB (1,073,741,824 bytes) |
| Individual encoded row or header | 8 MiB (8,388,608 bytes) |
| Concurrent queries | 2 per organization and environment, separate from analytics.query |

These are the default limits. There is no row-count cap beyond the byte cap.

A single row or header larger than 8 MiB cannot be decoded and fails with `tb_unavailable`; it is not a successful truncated result. Select fewer columns or aggregate large JSON values in SQL to stay under this bound.

A query that runs past 30 seconds fails with `statement_timeout`: a 408 response if no rows were sent yet, or a final `error` line if they were. Narrow the query with `WHERE` and `LIMIT`, or aggregate in SQL instead of fetching every row. If you need a higher limit, contact [support@useautumn.com](mailto:support@useautumn.com).

## Compression

Responses are always compressed: with zstd when your client's `Accept-Encoding` includes it, and with gzip otherwise. Both SDKs handle this for you. The TypeScript SDK asks for zstd on every server runtime (Node 20 and later, Bun and Deno) and decodes it itself where `fetch` doesn't. In browsers it sends no `Accept-Encoding` of its own, so the browser picks the encoding and decodes it. The Python SDK installs `zstandard`, so `httpx` asks for and decodes zstd. With curl, pass `--compressed` and curl decodes whichever encoding it gets.

## Query examples

### Exact usage by customer

```sql theme={null}
SELECT customer_id, sum(value) AS usage
FROM events
WHERE event_name = 'api_call'
  AND timestamp >= toDateTime('2026-11-01 00:00:00', 'UTC')
  AND timestamp < toDateTime('2026-11-02 00:00:00', 'UTC')
GROUP BY customer_id
ORDER BY usage DESC
LIMIT 100
```

### Daily totals

```sql theme={null}
SELECT day, event_name,
       sumMerge(event_count) AS event_count,
       sumMerge(total_value) AS total_value
FROM events_customer_daily_mv
WHERE customer_id = 'customer_123'
  AND day >= toDateTime('2026-11-01 00:00:00', 'UTC')
  AND day < toDateTime('2026-11-08 00:00:00', 'UTC')
GROUP BY day, event_name
ORDER BY day
```

Daily and monthly rollups represent whole UTC periods. Use raw events for partial periods or property filters.

### Hourly totals across customers

```sql theme={null}
SELECT hour, event_name, sum(event_count) AS event_count,
       sum(total_value) AS total_value
FROM events_org_hourly_mv
WHERE hour >= toDateTime('2026-11-01 00:00:00', 'UTC')
  AND hour < toDateTime('2026-11-02 00:00:00', 'UTC')
GROUP BY hour, event_name
ORDER BY hour
```

## Schema reference

> [accordion] events — usage event log
>
> **One row per:** event. **Key:** `org_id`, `env`, `id`.
>
> | Column | Type | Description |
> | - | - | - |
> | `id` | String | The event ID. |
> | `org_id` | String | Your organization ID. |
> | `org_slug` | Nullable(String) | Your organization's slug. |
> | `internal_customer_id` | Nullable(String) | Autumn's internal customer ID. |
> | `env` | String | `live` or `sandbox`. |
> | `created_at` | Nullable(Int64) | When Autumn recorded the event, in epoch milliseconds. Returned as a string. |
> | `timestamp` | DateTime64(6) | When the event happened, in UTC. |
> | `event_name` | String | The name you tracked, such as `api_call`. |
> | `idempotency_key` | Nullable(String) | The idempotency key you sent, if any. |
> | `value` | Nullable(Decimal(38, 19)) | The value you tracked. Returned as a string. |
> | `set_usage` | Nullable(UInt8) | Internal; not meaningful for queries. |
> | `entity_id` | Nullable(String) | Your entity ID, if the event was tracked for one. |
> | `internal_entity_id` | Nullable(String) | Autumn's internal entity ID. |
> | `internal_product_id` | Nullable(String) | The internal plan version ID attributed to the event. |
> | `customer_id` | String | Your customer ID. |
> | `properties` | JSON | Properties you sent. Read a key with `properties.region`. |
> | `deductions` | JSON | An object with a `list` array of JSON strings, each holding `balance_id`, `feature_id`, `plan_id` and `value`. |

> [accordion] events\_customer\_hourly\_mv — hourly totals per customer
>
> **One group per:** organization, environment, customer, entity, event name, UTC hour and plan version. Use `sum` and `GROUP BY`.
>
> | Column | Type |
> | - | - |
> | `org_id`, `env`, `customer_id`, `event_name` | String |
> | `entity_id`, `internal_product_id` | String (empty when absent) |
> | `hour` | DateTime |
> | `total_value` | Float64 |
> | `event_count` | UInt64 |

> [accordion] events\_customer\_daily\_mv — daily totals per customer
>
> **One group per:** organization, environment, customer, entity, event name and UTC day. Use `sumMerge` and `GROUP BY`. This table has no plan version column.
>
> | Column | Type |
> | - | - |
> | `org_id`, `env`, `customer_id`, `event_name` | String |
> | `entity_id` | String (empty when absent) |
> | `day` | DateTime |
> | `total_value` | AggregateFunction(sum, Float64) |
> | `event_count` | AggregateFunction(sum, UInt64) |

> [accordion] events\_customer\_monthly\_mv — monthly totals per customer
>
> **One group per:** organization, environment, customer, entity, event name, UTC month and plan version. Use `sumMerge` and `GROUP BY`.
>
> | Column | Type |
> | - | - |
> | `org_id`, `env`, `customer_id`, `event_name` | String |
> | `entity_id`, `internal_product_id` | String (empty when absent) |
> | `month` | DateTime |
> | `total_value` | AggregateFunction(sum, Float64) |
> | `event_count` | AggregateFunction(sum, UInt64) |

> [accordion] events\_org\_hourly\_mv — hourly totals across customers
>
> **One group per:** organization, environment, event name and UTC hour. Use `sum` and `GROUP BY`. Customer, entity, plan and property dimensions are not available.
>
> | Column | Type |
> | - | - |
> | `org_id`, `env`, `event_name` | String |
> | `hour` | DateTime |
> | `total_value` | Float64 |
> | `event_count` | UInt64 |

> [accordion] event\_names\_mv — event-name counts
>
> **One group per:** organization, environment and event name. Use `sum(event_count)` and `GROUP BY event_name`.
>
> | Column | Type |
> | - | - |
> | `org_id`, `env`, `event_name` | String |
> | `event_count` | SimpleAggregateFunction(sum, UInt64) |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.