Query events with SQL

POST /v1/events.query runs a read-only ClickHouse SQL query over your usage events and rollups. It streams compressed newline-delimited JSON using the same AnalyticsQueryLine contract as analytics.query, 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.

This endpoint is disabled by default. Contact 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

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}`);
  }
}

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']}")

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.

{"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:

{"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.

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

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

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

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

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`.

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

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)

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)

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

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)

Getting Started

Configure Pricing

Billing & Subscriptions

Customers

Additional Resources

Query events with SQL

Available tables

Running a query

The response stream

End lines, error lines and dropped connections

Errors before streaming

Limits

Compression

Query examples

Exact usage by customer

Daily totals

Hourly totals across customers

Schema reference

​Available tables

​Running a query

​The response stream

​End lines, error lines and dropped connections

​Errors before streaming

​Limits

​Compression

​Query examples

​Exact usage by customer

​Daily totals

​Hourly totals across customers

​Schema reference

Available tables

Running a query

The response stream

End lines, error lines and dropped connections

Errors before streaming

Limits

Compression

Query examples

Exact usage by customer

Daily totals

Hourly totals across customers

Schema reference