Query your data with SQL

POST /v1/analytics.query runs a read-only DuckDB SQL query over your Autumn data and streams the result back as newline-delimited JSON. Use it for reporting, reconciliation and one-off questions (“how many customers are on each plan?”) without exporting data or calling list endpoints page by page. Each query sees only your own organization and the environment of the key you use: a sandbox key reads sandbox data, a live key reads live data. Data is roughly 15 minutes behind the API, so a customer you created a moment ago may not show up yet.

Available tables


`balances`	`breakdowns`	`coupons`
`customer_plan_items`	`customers`	`entities`
`feature_grants`	`features`	`flags`
`invoice_line_items`	`invoices`	`licenses`
`plan_items`	`plan_licenses`	`plans`
`pooled_balance_contributions`	`purchases`	`referral_programs`
`referrals`	`rollovers`	`subscriptions`

Every table’s columns are listed in the schema reference.

Discovering the schema

List the tables and describe their columns with DuckDB’s own commands:

SELECT table_name FROM duckdb_tables();
DESCRIBE customers;

information_schema.columns is empty, so use DESCRIBE instead.

Running a query

import { Autumn } from "autumn-js";

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

const stream = await autumn.analytics.query({
  sql: "SELECT plan_id, count(*) AS customers FROM subscriptions WHERE status = 'active' GROUP BY 1",
});

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.analytics.query(
        sql="SELECT plan_id, count(*) AS customers FROM subscriptions WHERE status = 'active' GROUP BY 1",
    )

    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/analytics.query" \
  -H "Authorization: Bearer $AUTUMN_SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT plan_id, count(*) AS customers FROM subscriptions WHERE status = '"'"'active'"'"' GROUP BY 1"}'

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":"plan_id","type":"VARCHAR"},{"name":"customers","type":"BIGINT"}]}
{"type":"row","values":["pro","42"]}
{"type":"row","values":["starter","17"]}
{"type":"end","rows":2,"truncated":false,"stopped_by":null}

Line	When	Fields
`columns`	Always first	`columns`: each column’s `name` and DuckDB `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:

BIGINT and DECIMAL values are strings, so large numbers and amounts keep their precision. Parse them with BigInt or a decimal library rather than Number when precision matters.
Lists are JSON arrays.
STRUCT columns come back as DuckDB’s text form. Select to_json(col) to get JSON you can parse.

End lines, error lines and dropped connections

A successful stream always ends with an end line. If the result goes over the 1 GB 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` / `md_unavailable`	Querying is temporarily unavailable. Retry later

Limits

Limit	Value
Compute time per query	30 seconds
Uncompressed output per query	1 GB
Concurrent queries	2 per organization and environment

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.

Getting Started

Configure Pricing

Billing & Subscriptions

Customers

Additional Resources

Query your data with SQL

Available tables

Discovering the schema

Running a query

The response stream

End lines, error lines and dropped connections

Errors before streaming

Limits

Compression

​Available tables

​Discovering the schema

​Running a query

​The response stream

​End lines, error lines and dropped connections

​Errors before streaming

​Limits

​Compression

Available tables

Discovering the schema

Running a query

The response stream

End lines, error lines and dropped connections

Errors before streaming

Limits

Compression