Queries & exports

The Query page is Tessule’s ad-hoc analysis surface: a SQL console over your workspace’s data. Every data table in the workspace is queryable as an ordinary SQL relation, so anything a SELECT can express — joins across tables, aggregation, grouping, subqueries — works directly. Results appear in a grid, can be exported to CSV, and a query worth keeping can be saved, parameterised with variables, and reused as the data source behind charts and dashboards.

If you only remember one thing: query access is all-or-nothing, never filtered. The SQL engine cannot apply per-row or per-field permission filters, so instead of silently narrowing your results it refuses to run at all for anyone whose record visibility is restricted anywhere in the workspace. If a query runs, you are seeing everything; if your visibility is restricted, you get a clear “unavailable” answer rather than a misleading partial result.

Who can do what

Run queries — requires the query:execute action, which every built-in role (Admin, Member, Reader) grants, plus unrestricted record visibility (see Permissions and query results below — this second condition is what actually decides who can run SQL).
See the Query page — the page lives in the builder area of the app (Admin → Query), which sits behind the builder:access action: admin-only by default, grantable to power users through a custom role.
View saved queries — savedQueries:read, granted to all built-in roles.
Create, edit, delete saved queries — savedQueries:manage, granted to Members and Admins.
Start and download CSV exports — same gate as running the query: query:execute plus unrestricted visibility.

There is no per-query ownership or sharing model: every saved query belongs to the workspace. Anyone with savedQueries:manage can edit or delete any saved query, whoever created it (the creator is recorded on the query, but it grants no special rights).

What a query is

A query is a single SQL SELECT statement, executed read-only against your workspace’s own database schema. Each data table is exposed under its API name (the lowercase name shown in the schema editor), with one column per field plus the built-in id, createdAt, and updatedAt columns:

SELECT region, COUNT(*) AS deals, SUM(amount) AS pipeline
FROM deals
WHERE stage != 'closed_lost'
GROUP BY region
ORDER BY pipeline DESC

Because this is real SQL, joins, subqueries, HAVING, window functions, and date arithmetic all behave as they do in PostgreSQL. Two system views are also available to join against:

View	Columns	Contents
`sys_users`	`id`, `name`, `email`	Active members of this workspace
`sys_usergroups`	`id`, `name`, `description`	Active groups in this workspace

Useful for turning an owner id into a person’s name in a report.

Computed values

Not every field is a column. A formula field is stored (the database recomputes it on every write), so SQL reads it like any other column. A lookup, a rollup, or a computed column is evaluated when records are read through the app and the record API — nothing is stored — so the SQL table has no such column, and SELECT avg_rating FROM artworks is refused with COLUMN_NOT_FOUND.

That is a decision, not a gap: giving SQL its own copy of the number would mean two ways of computing one value that could drift on soft-deleted rows or rounding. Instead, the refusal tells you what the column is and hands you the expression the app evaluates, written against the table aliased as t:

SELECT title,
       (SELECT AVG(c."score") FROM "artwork_ratings" c
         WHERE c."artwork" = "t".id AND c.deleted_at IS NULL) AS avg_rating
FROM artworks t

The AI query assistant receives the same hint when its draft selects one, and the MCP get_table_schema tool lists each such column with queryable: false and its sqlEquivalent. Dashboards and saved queries therefore restate aggregates rather than selecting rollup columns — see Derived data for what each kind is.

What is not allowed

The engine validates before executing, and fails closed:

Exactly one SELECT statement. Writes (INSERT/UPDATE/DELETE), DDL, and multi-statement input are rejected — and the connection itself is read-only at the database level, so nothing can slip through.
No schema-qualified names. References like public.users are rejected; the connection is confined to your workspace’s own schema by the database, so other tenants’ data and platform tables are unreachable by construction.
Time budget. An interactive query is aborted after ~15 seconds. Narrow the query, or use a background export — exports get a far larger budget because they run off the request path.

Result limits

With no LIMIT, a default LIMIT 100 is applied.
The hard ceiling is 10,000 rows per interactive query, enforced outside your SQL — a larger LIMIT is capped, and the response’s hasMore flag tells you the result was cut short. To get more rows, use a background export (up to 100,000 rows by default).

Every query — interactive or export — passes the same gate and the same validation before touching data.

Permissions and query results

This is the section to read before granting query access widely.

Raw SQL cannot be row-filtered: once a statement is running, the engine has no way to hide “rows owned by someone else” or “the salary column” from it. So Tessule enforces the rule before execution, via POST /query’s permission gate:

The caller must hold query:execute (all built-in roles do), and
the caller’s record visibility must be unrestricted: a workspace-wide read scope on every private table, and no private field they cannot read.

In practice:

Admins can always run queries — they bypass row and field filters everywhere, so they are unrestricted by definition.
Members and Readers can run queries only while nothing in the workspace is restricted for them. The moment any table is switched to Private visibility (and their scope on it is narrower than workspace), or any field is made private without granting them read access, raw SQL is refused for them with a 403 explaining that their record visibility is restricted.

Restricted users are not locked out of data — they still read records through the normal record pages, lists, and views, where row and field filters apply properly. They only lose the raw-SQL shortcut.

Template variables

A query can be a template: {{name}} placeholders in the SQL are filled in at run time from value controls shown above the results. Values are substituted as escaped literals server-side before validation, so a variable can never smuggle in extra SQL.

SELECT * FROM deals
WHERE amount >= {{min_amount}}
[[AND region = {{region}}]]

[[ ... ]] marks an optional block: when every variable inside it is unset (a select left on “All”), the whole block is dropped from the SQL — the idiomatic way to build removable filters.
Declare each variable’s behaviour under Variables → Configure on the Query page. Types:

Type	Behaviour
`text` / `number`	Free-form scalar, substituted as an escaped literal
`select`	Single choice from a dropdown
`multiselect`	Multiple choices, substituted as a parenthesised list for `IN {{name}}`
`daterange`	A from/to pair referenced as `{{name.from}}` / `{{name.to}}` (from inclusive, to exclusive); relative presets resolve at run time

Select options can be a static list or an options query — SQL whose first column supplies the values (second column, if present, the labels).
Include "All" on a select adds an All choice that leaves the variable unset, activating the [[ ... ]] drop behaviour.
Each variable can carry a default value; charts and dashboards built on the query inherit its variable definitions.

Saved queries

Save stores the SQL and its variable definitions under a name; Load brings any saved query back into the editor. Saved queries are workspace-shared — everyone with savedQueries:read sees the same list — and they are the building block the rest of the analytics surface stands on: a chart can point at a saved query, and a dashboard tile can render one directly as a table.

Two behaviours worth knowing:

Editing a saved query changes every chart and dashboard built on it, immediately. The query list shows usage chips (which charts and dashboards reference each query) so you can see the blast radius; when in doubt, use Duplicate and edit the copy.
References are soft. Deleting a saved query does not break or delete the charts and dashboards that used it — their tiles show “This saved query no longer exists” until re-pointed.

The REST shape mirrors the UI: GET /queries, POST /queries, PUT /queries/{queryId}, DELETE /queries/{queryId} — see API access.

Save as view

Save as view promotes the SELECT in the editor into a SQL view — a read-only, table-like object that appears in the tables list and can be read like any table (including over the API). Creating one requires tables:manage; reading one back requires the same unrestricted visibility as running SQL, because a view can select from any workspace table. See Views for the full story.

The AI query assistant

The Query page (and the chart editor) includes an AI query assistant: describe what you want to know in plain language, and it drafts the SQL — validated against your workspace’s real tables — for you to review, run, and save. The assistant only drafts; nothing runs until you run it. It appears when AI features are enabled for your organization. See AI assistants.

Exporting results

There are two export buttons over the results grid, and they do different things:

Export shown — downloads the rows currently loaded in the grid as a CSV, built in your browser. Instant, and exactly what you can see — capped at the interactive 10,000-row limit.
Export full result — starts a background export job via POST /query/exports. The server re-runs the query on a worker with a much larger row ceiling and time budget, writes a complete CSV artifact, and the page polls the job until the download starts automatically.

How background exports behave

Same rules as running the query. The export goes through the identical validation and the identical permission gate — exporting cannot be used to see anything a live query couldn’t, and it draws on the same daily query quota.
Row ceiling: 100,000 rows by default (an entitlement your plan may raise). When the ceiling cuts a result short the job reports truncated: true and the UI says so — truncation is visible, never silent.
Format: one CSV file, UTF-8 with a BOM (so spreadsheet apps detect the encoding), a header row of column names, standard RFC 4180 quoting, and JSON-serialised object/array cells. Cells that could be interpreted as spreadsheet formulas (starting =, +, -, @, or a tab) are prefixed with a quote to neutralise formula injection.
Time budget: ~10 minutes of execution. A query that exceeds it fails with advice to narrow the query.
Concurrency: at most 2 exports per workspace can be queued or running at once; a third attempt returns 429 — retry when one finishes.
Where the file goes: the job (GET /query/exports/{jobId}) returns a short-lived download link — valid for about 5 minutes; re-fetch the job for a fresh one. The artifact itself is downloadable for 24 hours after the job succeeds, then it is deleted.
Requester-only: an export was built under the submitting user’s access, so only that user can see the job or obtain its download link. Anyone else gets a 404, as if the job didn’t exist. To share the data, share the file — or better, share the saved query.

Quotas

Ad-hoc SQL is the most expensive read a workspace can issue, so executions are metered:

Limit	Default	Notes
Query executions per day	20,000 per workspace	Interactive runs and exports share this quota; an entitlement your plan may raise
Interactive rows per query	10,000	Hard cap; `hasMore` flags truncation
Interactive time budget	~15 seconds	Statement aborted beyond it
Export rows per job	100,000	Entitlement; `truncated` flags the cut
Concurrent exports	2 per workspace	`429` beyond it
Export artifact lifetime	24 hours	Download link itself refreshes every ~5 minutes

FAQ

Q: I get “Your record visibility is restricted on one or more tables; raw SQL queries are unavailable.”

Some table in the workspace is set to Private visibility and your role’s read scope on it is narrower than workspace, or a private field exists that your roles can’t read. Raw SQL is all-or-nothing (see Permissions and query results), so it’s refused. Either an admin widens your visibility, or you use the record pages and views, where filters apply per row.

Q: My query says it ran but I only got 100 rows.

With no LIMIT clause, the default page size of 100 is applied. Add your own LIMIT (up to 10,000), or use Export full result for more.

Q: COLUMN_NOT_FOUND for a field I can see on the record page.

If the message says the field is a lookup, rollup or computed column, it is computed when records are read and is not a column of the SQL table. The refusal includes the equivalent expression; paste it into your SELECT with the table aliased as t. See Computed values.

Q: “Table ‘X’ not found” — but I can see the table in the app.

Query by the table’s API name (lowercase, as shown in the schema editor), not its display name. The editor’s autocomplete suggests valid table and column names as you type.

Q: I edited a saved query and a dashboard changed for everyone. Can I roll back?

There is no version history on saved queries. The safe workflow is Duplicate, edit the copy, and re-point tiles when you’re happy. The usage chips on the saved-query list show which charts and dashboards a query feeds before you edit it.

Q: Can Readers run queries?

Yes — query:execute is in the Reader baseline — but only while their visibility is unrestricted, and they can only reach the Query page itself if granted builder:access (dashboards still work for them regardless, since tiles run queries without the page).

Open the app