Writing queries

The query area posts three fields to /api/query:

• profile_name — required;
• sql — required;
• max_rows — defaults to 1000;
• chart_type — table, pie, bar, line, or scatter.

Both profile_name and sql must be non-empty; otherwise the endpoint answers with a one-line notice.

The catalog is authoritative

The catalog returned by /api/catalog is the authoritative description of what you can query. For each visible table it lists:

• the public table name;
• public columns with their field types and system-column flags;
• money currency and rounding metadata where applicable;
• link columns and the table each link targets.

Build queries from this catalog rather than assuming a fixed schema. Table definitions can differ between profiles and evolve over time.

The catalog exposes id, the table’s user columns under their public names, and created_at. The deleted and row_revision columns are not exposed, and rows whose deletion flag is set are filtered out before your SQL runs. Physical ordinal column names are hidden: a column publicly named invoice_total is used as invoice_total, not the database column that stores it.

Identifiers and quoting

Identifiers in the catalog are double-quoted (" escaped as ""). Clicking a column in the sidebar inserts its already-quoted name, which is the safest way to reference a name that is reserved, contains a space, or otherwise needs quoting.

Each table in the sidebar also offers a starter query:

SELECT *
FROM "table_name"
LIMIT 100;

Joins

A link entry says that a public source column holds the ID of a row in another table. Analytics does not join linked tables automatically — the query chooses the join and the columns. The analytical view is columnar, so a link remains an ID rather than an expanded record. Join the linked table when the report needs its fields, using only columns listed in the catalog.

The LLM schema context

The catalog response also contains a ready-made schema prompt, used to generate queries with an LLM. Its rules describe the contract the engine expects:

• return one read-only SELECT statement, without Markdown fences or explanation;
• use only the public table and column aliases listed;
• physical database column names are not visible through the API;
• add a reasonable LIMIT unless the query is an aggregate with a naturally small result;
• for a chart, put the category or x-axis column first and numeric value columns after it.

The same rules apply whether a query is written by hand or generated.