Worksheets

A worksheet is a time-bounded, in-memory analytics workspace held by one data.svc process for one tenant, backed by a private in-process DuckDB instance. You create it, register CSV or XLSX files against it, define tables over those files, profile the columns, and run SQL. When the TTL elapses the worksheet and everything in it is gone.

Worksheets bypass the entity engine entirely. There are no entity definitions, no hooks, no migrations, no history, no access rules, no row-level security and no field protection on a worksheet. The only enforced boundary is the tenant key. Anything durable belongs in a datastore or in Data Pipes.

Caution

Worksheet state is process-local. The registry is an in-memory map inside a single data.svc process — there is no shared store, no database row, no cache backend and no replica affinity. A worksheet created on one replica is invisible to every other replica and returns 404; every worksheet is destroyed on restart, deploy or crash. Run the feature on a single replica, or route by worksheet_id with sticky sessions. Never treat a worksheet ID as durable, and never build a flow that depends on one surviving a deploy.

Lifecycle

create worksheet
  → register file(s)        (multipart upload, or a remote URL)
  → default tables appear   (one per CSV, one per XLSX sheet)
  → define tables           (optional: union several files into one)
  → schema / sample / analytics / distinct
  → query (SQL)
  → delete, or let the TTL expire

Registering a file creates its tables immediately: the file is read once, at registration, into an in-memory table. Nothing is re-read later.

Routes

All paths are relative to the service; the gateway serves them under /data/. Every route requires a JWT and the four tenant routing headers — there is no anonymous mirror.

Method	Path	Purpose
POST	/worksheets	Create a worksheet
GET	/worksheets/{worksheet_id}	Fetch worksheet state, files and tables
DELETE	/worksheets/{worksheet_id}	Destroy it and free its memory
POST	/worksheets/{worksheet_id}/files	Register a file (upload or URL)
GET	/worksheets/{worksheet_id}/files	List registered files
GET	/worksheets/{worksheet_id}/files/{file_id}	Fetch one file’s metadata
DELETE	/worksheets/{worksheet_id}/files/{file_id}	Unregister a file and drop its tables
POST	/worksheets/{worksheet_id}/tables	Define a table over one or more files
GET	/worksheets/{worksheet_id}/tables	List tables
DELETE	/worksheets/{worksheet_id}/tables/{table_name}	Drop a table
GET	/worksheets/{worksheet_id}/tables/{table_name}/schema	Column types plus per-column stats
GET	/worksheets/{worksheet_id}/tables/{table_name}/sample	Sample rows
GET	/worksheets/{worksheet_id}/tables/{table_name}/analytics/{column_name}	Profile one column
GET	/worksheets/{worksheet_id}/tables/{table_name}/columns/{column_name}/distinct	Distinct values with counts
POST	/worksheets/{worksheet_id}/query	Execute SQL

file_id is the file’s logical_name. There is no separate identifier.

Tenancy

The tenant key is taken from the request context, never from the body, and is stored on the worksheet at creation. Every subsequent operation compares it: a mismatch is 403 worksheet.cpet_violation, not 404. A worksheet belonging to another tenant can therefore be distinguished from one that does not exist, but not read.

Authentication is the IAM token; roles are not consulted. Any authenticated principal in the tenant can create a worksheet, upload to it, and run arbitrary SQL inside it.

Create a worksheet

POST /worksheets
Content-Type: application/json
Idempotency-Key: 3f1c-q3-sales

{
  "name": "Q3 sales analysis",
  "ttl_seconds": 3600
}

Field	Type	Required	Notes
name	string	no	Echoed back; not used for lookup
ttl_seconds	int	no	Default 1800. Outside 60–86400 is worksheet.invalid_ttl

A JSON body is required. An empty request body is 400 invalid_request.

{
  "worksheet_id": "01HQZ...",
  "name": "Q3 sales analysis",
  "created_at": "2026-04-13T14:22:00Z",
  "expires_at": "2026-04-13T15:22:00Z",
  "ttl_seconds": 3600,
  "status": "active",
  "files": [],
  "tables": []
}

status carries active or expired, but every response that reaches you comes from a live worksheet — an expired one answers 410 before a body is built — so in practice it always reads active. GET /worksheets/{worksheet_id} returns the same shape with files and tables populated.

The TTL is fixed at creation. No route extends it.

Idempotency-Key is honoured on this route only. A repeat with the same key from the same tenant inside 10 minutes replays the stored 201 body verbatim instead of creating a second worksheet. Keys are held per tenant and, like everything else here, in the process’s memory.

Registering files

One route serves both ingestion paths and dispatches on Content-Type: multipart/form-data is treated as an upload, anything else is parsed as a JSON URL registration.

Upload

POST /worksheets/{worksheet_id}/files
Content-Type: multipart/form-data

Form field	Required	Notes
file	yes	The bytes
logical_name	yes	Identifier inside the worksheet; also the file_id
format	yes	csv or xlsx
format_options	no	A JSON string holding the options object below

Remote URL

{
  "url": "https://example.com/data/sales_q3.csv",
  "logical_name": "sales_q3",
  "format": "csv",
  "format_options": { "delimiter": ",", "header": true }
}

Here format_options is a real JSON object, not a string.

Caution

Any url that is not an inline:// reference is handed to the engine’s file reader verbatim. The service applies no scheme allowlist, no host allowlist and no address-range check, so an authenticated tenant can make the process issue an arbitrary outbound fetch — including to internal addresses — and a bare filesystem path reads a local file off the pod. http, https and s3 all resolve. Restrict egress at the network layer if that matters to you. The 500 MB ceiling is enforced on uploaded bytes only; a URL-registered file is unbounded and reports size_bytes: -1. A URL that cannot be read fails as file.format_invalid carrying the reader’s message.

Formats

Two formats are accepted. Any other format value is file.format_invalid.

Format	Token	Produces
CSV	csv	One table
Excel	xlsx	One table per imported sheet

Format options

Key	Applies to	Type	Default	Meaning
delimiter	csv	string	auto-detected	Field separator
header	csv, xlsx	bool	xlsx true; csv auto-detected	First row holds column names
null_string	csv	string	none	Literal treated as NULL
encoding	—	string	—	Accepted by the request shape and never applied
sheets	xlsx	string[]	empty	Sheets to import; empty triggers discovery
header_row	xlsx	int	auto-detected	1-indexed header row; rows above are skipped

Everything else in a CSV — column names, types, quoting — is inferred by the engine’s CSV reader.

Caution

encoding is parsed into the options struct and then ignored: the CSV reader is never told about it, and the declared file.encoding_error code is emitted nowhere. Convert non-UTF-8 files before uploading.

XLSX sheet discovery

When sheets is omitted the service inspects the workbook and applies these rules in order:

1. Any sheet whose declared dimensions exceed 1,000,000 rows or 1,000 columns fails the whole file with file.size_exceeded.
2. Each sheet is scanned for a non-empty cell, bounded to the first 5,000 rows and 10 seconds. A sheet that exhausts either bound counts as having no data.
3. Exactly one sheet with data — it is imported.
4. More than one — file.sheet_selection_required, with available_sheets in the error details. Re-send with sheets naming the ones you want.
5. None — file.format_invalid.

Naming sheets explicitly skips discovery — including the dimension check — and imports exactly those sheets. Each becomes its own table. A sheet that loads but turns out to be entirely null or blank is dropped silently; if every sheet drops out, the file fails with file.format_invalid.

Note

Discovery and header-row detection open the workbook from local disk, which a URL-registered file is not. An XLSX registered by URL therefore fails with file.format_invalid unless you name sheets explicitly, and its header row is never auto-detected — row 1 is assumed. Upload XLSX bytes rather than referencing them.

When header_row is null the header is detected by taking the modal non-empty-cell count over rows 2 to 20 and picking the first row that reaches it — the common case of title and banner rows above the real header. Detection needs at least two rows agreeing on a width of at least two columns; otherwise row 1 is assumed. Load is capped at 1,000,000 rows per sheet.

Caution

XLSX columns are all read as text. The reader is invoked with type inference off, so every column of an imported sheet is VARCHAR regardless of cell content, and column analytics classify all of them as kind: text — no numeric statistics, no histogram, no temporal distribution. Cast in SQL (CAST(amount AS DECIMAL(18,2))) or ingest as CSV when you need typed columns.

Default table names

Registration creates tables without you asking. Names are lowercased, every run of characters outside a-z0-9 becomes _, and leading and trailing underscores are trimmed.

Source	Table name
CSV	logical_name
XLSX, one sheet imported	logical_name
XLSX, several sheets imported	logical_name_sheetname per sheet

These carry origin: default. A name collision with an existing table is table.name_conflict, and registering the same logical_name twice is rejected the same way.

{
  "file_id": "sales_q3",
  "logical_name": "sales_q3",
  "source": { "type": "upload" },
  "format": "csv",
  "size_bytes": 4823104,
  "row_count": 84302,
  "sheets": null,
  "tables_created": ["sales_q3"],
  "added_at": "2026-04-13T14:23:15Z",
  "status": "ready"
}

source.type is upload or url; a URL source also carries source.url. status is always ready. Removing a file drops every table created from it and every table that lists it as a source.

Defining tables

A defined table is a view that unions one or more registered sources. Use it to query several uploads as one.

POST /worksheets/{worksheet_id}/tables

{
  "name": "all_sales",
  "sources": [
    { "file": "sales_q1" },
    { "file": "sales_q2" },
    { "file": "sales_q3" }
  ],
  "union_strategy": "by_name",
  "add_filename_column": true
}

Field	Type	Required	Notes
name	string	yes	Used verbatim — not sanitized like a default table name
sources[].file	string	yes	A registered logical_name
sources[].sheet	string	no	For XLSX; selects the file_sheet table
union_strategy	string	no	Default by_name
add_filename_column	bool	no	Adds _filename carrying the source file’s logical name

Strategy	Alignment	Failure mode
by_name	By column name; columns missing from a source come back NULL	Rarely fails
by_position	By ordinal position	Differing column counts fail at view creation
strict	Names, order and types must match exactly	Checked before the view is built; reports the mismatching position

An unrecognised union_strategy string falls back to by_name without an error. A single-source definition is legal and just wraps the source. Union failures return table.union_incompatible; an unknown source is table.not_found; an empty sources array is table.union_incompatible.

{
  "name": "all_sales",
  "origin": "explicit",
  "source_files": ["sales_q1", "sales_q2", "sales_q3"],
  "row_count": 252906,
  "column_count": 14,
  "defined_at": "2026-04-13T14:30:00Z"
}

Dropping a defined table removes the view only; the underlying files and their default tables stay.

Reading a table without SQL

Four GET routes profile a table without a query: enriched schema, sample, per-column analytics, and distinct values with counts. Parameters, the four analytics result shapes, and the rule that decides when a distinct count is exact rather than estimated are in Worksheet profiling and analytics.

Query

POST /worksheets/{worksheet_id}/query

{
  "sql": "SELECT status, COUNT(*) AS cnt FROM sales_q3 WHERE amount > 1000 GROUP BY status ORDER BY cnt DESC",
  "max_rows": 1000,
  "timeout_ms": 30000
}

Field	Type	Default	Notes
sql	string	—	One statement, in DuckDB SQL
parameters	object	—	Accepted and never used
max_rows	int	100000	Clamped to 100,000; 0 means the default
timeout_ms	int	30000	Clamped to 300000

This is not the query DSL. Worksheet SQL goes straight to the worksheet’s own DuckDB instance and shares no syntax, operators or access checks with entity queries.

Caution

The sql string is passed to the engine unmodified. There is no statement-kind filter and no read-only mode, so CREATE, INSERT, UPDATE, DROP and ATTACH all execute — against the caller’s own private in-memory database, but they do execute. parameters is accepted by the request shape, carried through the service, and then dropped: nothing is ever bound. Parameterised queries are impossible today and fail silently rather than erroring, so build the statement safely in your own layer and assume no server-side escaping.

{
  "rows": [{ "status": "completed", "cnt": "8412" }],
  "schema": [
    { "name": "status", "type": "VARCHAR" },
    { "name": "cnt", "type": "BIGINT" }
  ],
  "row_count_returned": 1,
  "truncated": false,
  "execution_ms": 42,
  "query_id": "01HQZ..."
}

truncated is true when the result was cut at max_rows. query_id is generated per execution and is not addressable afterwards — there is no result cache and no fetch-by-id route.

Query failures are classified by matching the driver’s message, so the mapping is a heuristic: a message containing timeout becomes query.timeout, one containing memory becomes worksheet.memory_exceeded, and parser, syntax, catalog error or does not exist become query.syntax_error. Everything else is query.execution_error.

Value serialization

Result values are JSON-encoded by database type. Wide integers and decimals come back as strings to keep their precision — plan for that in the client.

Database type	JSON
BOOLEAN	boolean
TINYINT, SMALLINT, INTEGER	number
BIGINT, HUGEINT	string
REAL, DOUBLE	number
DECIMAL(p,s)	string
VARCHAR	string
DATE	"YYYY-MM-DD"
TIME	"HH:MM:SS"
TIMESTAMP	RFC 3339, UTC
BLOB	base64 string
NULL	null

Expiry

The TTL is absolute from creation. After it elapses:

Window	Behaviour
Before expires_at	Normal
expires_at to +5 minutes	Every operation returns 410 worksheet.expired. DELETE still succeeds
After +5 minutes	The worksheet is evicted and closed; requests return 404 worksheet.not_found

A background pass runs every 30 seconds and closes anything past the grace window; a request arriving first evicts it inline. DELETE frees the memory immediately, which is worth doing on large uploads rather than waiting out a 24-hour TTL.

Limits

Every worksheet bound — TTL range, 500 MB uploads, 50 files, 100 tables, 2 GB of memory, 100,000 query rows — is a compile-time constant with no configuration key and no per-tenant override, and four request fields are clamped to their maximum rather than rejected. The full table, with the error each breach returns, is in Worksheet limits and quotas.

Per-tenant quotas are counted in the process’s memory in fixed one-minute windows: 20 concurrent worksheets, 10 creations, 30 file registrations, and 60 queries per minute per worksheet. A breach returns 429 with Retry-After: 60 and code rate_limit_exceeded. Only creation, file registration and query are counted; every other route — introspection, table definition, listing, delete — is uncounted.

Errors

Every failure uses one envelope:

{
  "error": {
    "code": "worksheet.expired",
    "message": "worksheet expired at 2026-04-13T15:22:00Z",
    "details": { "worksheet_id": "01HQZ...", "expired_at": "2026-04-13T15:22:00Z" },
    "request_id": "01HQZ..."
  }
}

This is not the entity API’s {"error","code","details"} envelope — the fields are nested under error and carry a request_id.

Code	HTTP	Meaning
worksheet.not_found	404	No such worksheet on this replica, or evicted past the grace window
worksheet.expired	410	TTL elapsed, still inside the 5-minute grace window
worksheet.cpet_violation	403	The worksheet belongs to another tenant
worksheet.invalid_ttl	400	ttl_seconds outside 60–86400
worksheet.memory_exceeded	507	The 2 GB per-worksheet limit was hit during execution
file.not_found	404	Unknown file_id
file.uri_unreachable	502	Declared and mapped, never emitted — an unreadable URL fails as file.format_invalid
file.format_invalid	400	Unsupported format, unreadable bytes, or no usable sheet
file.size_exceeded	413	Upload over 500 MB, sheet over the row or column cap, or more than 50 files
file.sheet_selection_required	400	Several sheets hold data; name them in sheets
table.name_conflict	409	Table or file name already used, or more than 100 tables
table.not_found	404	Unknown table, or an unknown source in a definition
table.union_incompatible	400	Sources cannot be unioned under the chosen strategy
column.not_found	404	Unknown column in analytics or distinct
query.syntax_error	400	Parse or catalog error
query.timeout	504	Query exceeded its timeout
query.execution_error	400	Any other runtime failure
rate_limit_exceeded	429	A per-tenant quota was hit
invalid_request	400	Body is not valid JSON, or is empty

Two checks in the HTTP layer bypass the status map and always answer 400 regardless of the code they carry: a malformed multipart body reports file.size_exceeded, and GET on an unknown file_id reports file.not_found. The same codes are 413 and 404 everywhere else.

Operating worksheets

Worksheets need two DuckDB extensions — httpfs for URL registration and excel for XLSX — and both must be installed on the host beforehand. Automatic extension installation and autoloading are turned off on each worksheet’s database as it is created, so the process never reaches out to download anything at query time. At boot the service probes both extensions and logs the result; each worksheet then loads them at creation, and a load failure is a warning, not an error. The worksheet starts anyway, and the missing capability shows up later as an XLSX or URL registration failing. Check the startup log on an air-gapped build before concluding the API is broken.

Memory is the binding constraint: each worksheet holds up to 2 GB and 20 can be active per tenant, all inside the data.svc process. Size the pod for the concurrency you actually intend to allow, and keep the feature on a dedicated replica if worksheet load must not affect entity traffic.

Continue with

Concern	Page
Schema, sample, analytics, distinct	Worksheet profiling and analytics
Every bound, quota and clamp	Worksheet limits and quotas
Durable, modelled data instead of a scratch area	Datastores
Persistent file-to-table ingestion	Data Pipes
Querying entities	Query DSL
Block overview	Data API
Endpoint-level HTTP reference	Data API reference