> ## 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 your data with SQL

> Run read-only SQL over your Autumn customers, subscriptions, balances and invoices, and stream the result.

`POST /v1/analytics.query` runs a read-only [DuckDB](https://duckdb.org/docs/sql/introduction) 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](https://docs.useautumn.com/documentation/lakehouse/schema.md).

### Discovering the schema

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

```sql theme={null}
SELECT table_name FROM duckdb_tables();
DESCRIBE customers;
```

`information_schema.columns` is empty, so use `DESCRIBE` 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.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}`);
  }
}
```

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

```bash cURL theme={null}
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.

```json theme={null}
{"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:

```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` / `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](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.


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