SQL sessions

A SQL session is a persistent database connection that DuckHaven holds open on one agent on your behalf, so a client can run many statements with connection-scoped state — temporary relations, USE catalog.schema, SET, and multi-statement transactions — instead of the one-shot query execution path where every request is independent.

Sessions exist for external tools that expect a warehouse-style connection — a client that opens a connection, runs a sequence of statements against it, and closes it. A client never talks to a compute node directly: it opens a session through the DuckHaven API, and the API brokers each statement to the agent over the agent's own outbound WebSocket. No inbound agent port is ever opened.

Off by default

The session surface is disabled unless an operator sets SQL_SESSIONS_ENABLED=true. Enable it only after deploying the hardened agent (see Sandboxing below): turning sessions on also enables the broader statement policy, so the container hardening must be in place first.

This page names the settings that shape a session's behaviour but not their values; Configuration carries the current default for each one.

Lifecycle

1. Open — POST /api/workspaces/{workspace}/sql/sessions. The API picks the agent (an explicit agent_id, or an auto-picked compatible one), tells it to open and attach a DuckDB connection to the workspace's catalogs, and returns a session_id once the agent acknowledges. The session pins that agent: every later statement routes to it. When no agent is up and elastic compute is enabled, the open starts one first — see Cold start.
2. Run statements — POST /api/sql/sessions/{session_id}/statements. Each statement is checked against the statement policy and the caller's permissions, then dispatched to the held connection. A statement is recorded as an ordinary query row — queued, then running once the agent acknowledges receipt, then a terminal done/failed — so you poll and fetch it through the same GET /api/queries/{query_id} and /rows endpoints as any query, and it appears in the audit history tagged to its session. Most statements never need that poll, because the call waits for them to finish first.
3. Close — DELETE /api/sql/sessions/{session_id}. The agent drops the connection and frees the compute slot it held.

Cold start

With elastic compute enabled, the pool can legitimately be scaled to zero when a client connects — that is the point of it. Opening a session then has to start an agent and wait, which sits awkwardly with a synchronous open, so the open call lets the caller choose how that wait ends.

The session is written pending — no agent holds it yet — compute is started, and the call blocks. SQL_SESSION_WAIT_TIMEOUT_S is the budget, and it is deliberately one number for every backend: nothing a client sees depends on whether the deployment provisions Docker containers or Azure container groups.

Two request fields shape it:

Field	Meaning
wait_timeout_s	How long to block. Omit for the server default; 0 never blocks. Capped by SQL_SESSION_MAX_WAIT_TIMEOUT_S.
on_wait_timeout	cancel (default) or continue — what happens when the budget runs out.

And the answers:

Outcome	Status	Body
Opened within the budget	201	The open session, exactly as before
Budget expired, on_wait_timeout=cancel	503 + Retry-After	{"error": "compute_starting"}
Budget expired, on_wait_timeout=continue	202	The session, still pending or opening
Compute could not be started at all	503 + Retry-After	{"error": "compute_unavailable"}

cancel is the default because it is safe for a client that only knows how to open a session: it gets a plain "not yet, try again" it can retry, and never a session id it would immediately fail to run statements on. It abandons the session row, not the compute — the agent keeps starting, so a retry a few seconds later lands on warm compute rather than triggering a second cold start.

continue is for a client that can poll: it gets the pending session back with 202 and follows GET /api/sql/sessions/{session_id} until the status reads open. A 202 on this endpoint is itself the signal that the server supports the contract; nothing needs to negotiate a version.

Client support

The DuckHaven clients (duckhaven-sql-connector, dbt-duckhaven, dlt-duckhaven) do not yet retry the 503 or poll the 202 themselves. Until they do, a cold start slower than the wait budget surfaces as a connection error the caller has to retry. Deployments where compute starts in seconds (the Docker backend) are already covered by the default budget.

A session that stays pending because compute never arrives at all is failed by the elastic reaper at ELASTIC_PROVISIONING_DEADLINE_S, with close reason provisioning_timeout.

Naming an idle-terminated elastic agent explicitly starts that agent and parks the session for it, rather than failing — see Starting a terminated agent by naming it.

Waiting for a statement

Running a statement is asynchronous underneath: the API hands it to the agent over a WebSocket and the agent reports back when it is done. A client could discover that by polling GET /api/queries/{query_id}, but polling means sleeping, and sleeping means learning late — a client backing off between polls routinely spent longer waiting to notice a statement had finished than the statement took to run.

So the submit call waits for it. POST /api/sql/sessions/{session_id}/statements holds the response for up to SQL_STATEMENT_WAIT_TIMEOUT_S (10 seconds by default), and answers as soon as the statement reaches a terminal state:

Outcome	Status	Body
Finished within the budget	200	The finished statement — done or failed, with its timings
Still running when the budget ran out	202	The statement as it stands, to poll as before

A failed statement still answers 200: it completed, and its error is on the row, exactly where a polling client would have read it. 202 means only "not finished yet".

Per request, wait_timeout_s overrides the budget — 0 never blocks and reproduces the older behaviour of always answering 202. It is capped by SQL_STATEMENT_MAX_WAIT_TIMEOUT_S; asking for more is a 422.

Nothing about this is required of a client. One that ignores the field and polls anyway still works; it just finds the statement already finished on its first look. And a statement that outlives the budget is never cancelled for it — cancelling work the client can still collect would be the wrong trade.

Set the budget above your statements, or set it to zero

A budget most statements outlive is worse than no wait at all: the call is held for the whole budget, the statement is handed back still running, and the client polls for it anyway — so you pay the hold and keep the polling. Measured at 48-way concurrency, a 1-second budget ran 27% slower than 0. The 10-second default clears ordinary interactive statements comfortably; if yours routinely run longer, raise it rather than leaving it just under them.

For that longer tail, GET /api/queries/{query_id} takes the same wait_timeout_s as a query parameter, so a client following a slow statement can keep waiting rather than fall back to sleeping. It is opt-in and defaults to 0 there, because that route is also what the worksheet refreshes several times a second and what the audit history reads; neither wants a held request.

Getting the rows with the answer

A statement's rows normally come from a second call, GET /api/queries/{query_id}/rows. For a client that is going to read them immediately that is a wasted round trip — and it is not optional, because the column names arrive with the rows, so even a client that only wants to know the result's shape has to make it.

So the response carries the first page itself, as first_page, in the same shape the rows endpoint returns. A statement whose result fits in that page costs one HTTP call instead of two.

This is on by default (SQL_STATEMENT_FIRST_PAGE_LIMIT, 200 rows) because the round trip it removes is paid by every client, including ones that will never know the field exists. A request can ask for a different number, or 0 to opt out — worth doing for a caller that will not read the rows at all, since the page is serialized onto the same response the completion wait exists to keep short.

It is capped for the same reason: this saves a round trip on rows you are about to read, it is not a bulk transport. A larger result carries a cursor in its first page and pages from there as normal.

first_page is absent for DDL and DML, which finish without a result file, and absent if the rows could not be fetched — the statement still succeeded, and asking the rows endpoint directly reports why.

Statement delivery and deadlines

A statement's own execution timeout (timeout_s on the request) is enforced by the agent around execution, but that alone can't bound a statement whose dispatch frame never arrives — nothing would ever revisit a row that stays queued. Two server-side deadlines close that gap:

• Ack deadline (SQL_STATEMENT_ACK_DEADLINE_S) — the agent acknowledges receipt of a statement before doing anything else, flipping the row queued → running. A row still queued past this deadline never reached the agent and is failed with agent did not ack statement.
• Timeout + grace (SQL_STATEMENT_TIMEOUT_GRACE_S, on top of the statement's own timeout_s) — a running statement past this bound should have already been resolved by the agent's own timeout; reaching here means its reply is gone too, and it is failed with statement exceeded timeout.

A statement that fails this way is never automatically retried: if the original frame actually did reach the agent and only the acknowledgement was lost, retrying would re-run the statement a second time, which can duplicate or corrupt output for non-idempotent DDL/DML (CREATE TABLE, INSERT, MERGE). The client is expected to resubmit if it still wants the statement run.

The ack deadline only applies to agents that advertise support for it in their capabilities; an older agent's statements fall back to the timeout-based bound instead, so a rolling upgrade never fails every in-flight statement the moment the API is upgraded ahead of its agents.

Terminating a session for any reason — an explicit close, the idle/lifetime reaper, or an agent disconnect — also resolves any statement still queued or running on it, rather than leaving the row to be discovered only by a client's own poll deadline.

Pinning, lifetime, and failure

A session holds a real connection and a memory reservation on its agent for its whole life, so it counts against that agent's admission budget just like a running query — long-lived sessions can't oversubscribe memory or starve interactive queries.

Under the default auto profile that reservation is not one fixed size. A session holds a small idle baseline (SESSION_BASELINE_BYTES) between statements, and each statement it runs is sized to its own workload: the agent estimates the statement from its EXPLAIN plan, grows the session's reservation to fit, runs it, and shrinks straight back to the baseline. A heavy query gets the memory it needs without a one-off heavy query pinning that memory for the rest of the session's life.

Growth is bounded by what the agent can actually spare at that moment. If the budget is tight the statement gets whatever is free and runs at that size — slower, possibly spilling to disk, but never blocked and never at the expense of another session's memory. SESSION_MAX_BUCKET_FRACTION caps how much of the agent one statement may take.

On top of that required size, a statement is lent whatever memory the agent has idle, which DuckDB spends on caching the Parquet files it reads — the elastic memory described under what a query is actually given, bounded here by ELASTIC_CEILING_FRACTION and by an even share between the open sessions.

One thing differs for a session: the lent memory is kept across statements rather than handed back, because dropping it would throw the cache away between every statement. So a session querying the same tables repeatedly — a dbt run, an analyst iterating on a query — does not re-fetch and re-decompress the same data. How much it keeps does not depend on what it last ran; an idle session settles at the same share either way. It stays revocable throughout, so a session sitting idle with a warm cache never makes anyone wait.

Because the idle baseline is small, sessions-per-agent is bounded by the baseline rather than by peak query size: roughly the memory budget divided by SESSION_BASELINE_BYTES (about 56 on a 4 GB agent at the default). Opens beyond that queue for capacity rather than failing outright, and an open that has waited SESSION_QUEUED_TIMEOUT_S gives up with a clear error instead of hanging until the control plane's own deadline. If your clients routinely open more sessions at once than an agent can hold, that is a sizing question — a larger agent or more agents in the pool — not something a longer timeout fixes.

Static profiles size sessions differently

Under a static ladder (single, equal_2, …) a session takes a whole ladder slot for its lifetime and does not grow or shrink, because slots don't subdivide. single in particular gives one session the entire agent, which also means no other query can run until it closes.

To keep a crashed client from pinning an agent forever, a background reaper closes sessions that have been idle past SQL_SESSION_IDLE_TIMEOUT_S or have run longer than SQL_SESSION_MAX_LIFETIME_S. A session that never finishes opening — the agent's acknowledgement is lost, so its row is stuck opening — is reaped once it is older than SQL_SESSION_OPENING_DEADLINE_S (which must exceed the open timeout), so a slot the agent did manage to reserve is never stranded. That deadline runs from when an agent was actually told to open the session, not from when the client asked, so a session that first waited out a cold start still gets its full budget. Reaping a session this way also reaches an open the agent had started but not finished — one still queued for capacity, or still building its connection — so the reservation it was holding comes back rather than being lost until the agent restarts. If the agent's connection drops, DuckHaven fails that agent's sessions immediately — the held connection is gone and Postgres is the source of truth — and the next statement on the session returns 409; the client simply opens a new one. Sessions survive an API restart or failover as long as their pinned agent stays connected, because every statement is routed by the agent's recorded owner, not by in-memory state.

As a backstop beneath the control-plane reaper, each agent also holds its own lease on every session it is running and self-expires one that goes idle past SESSION_IDLE_TIMEOUT_S or outlives SESSION_MAX_LIFETIME_S (both agent settings, deliberately larger than the API's so the reaper stays primary). This is what guarantees a slot is reclaimed even if a close instruction from the API is lost in flight — the agent that owns the slot frees it on its own clock rather than holding it until its next reconnect. This is the churn safeguard: a client that opens many sessions and exits without closing them (repeated dbt runs, for instance) can never slowly exhaust an agent's admission budget.

Statement policy

The one-shot query path enforces a fixed allowlist (data + catalog DDL only). Sessions need more — connection-scoped SETs and COPY from staged files — so the session path replaces the allowlist with a capability-scoped policy, still enforced entirely at the API, per statement:

• Allowed: SELECT/INSERT/UPDATE/DELETE/TRUNCATE/MERGE, CREATE/ALTER/DROP, DESCRIBE (read-only relation introspection that dbt relies on), USE, transaction control, a small safe subset of SET (e.g. timezone), COPY to or from the session's staging prefix only, and ATTACH of the workspace's own managed catalog. TRUNCATE is DuckDB's alias for DELETE FROM without a WHERE (dbt's seed reset emits it), and is authorized as a write against its target — see SQL support.
• Rejected: COPY or read_parquet/read_csv to a local file, an arbitrary URL, or any object-store path outside the staging prefix; arbitrary INSTALL/LOAD; ATTACH of anything else; and any SET that could widen the sandbox (memory, external access, filesystem allowlists). Anything the parser can't classify is rejected.

This is what keeps a broadened SQL surface from becoming an open one: a session gets a bigger box, not an unbounded box, and every rejection is counted for monitoring.

Staging and credentials

Bulk data does not flow through the API. A bulk load stages Parquet to the workspace's object storage and then issues a COPY command through the session; only the command crosses the API. Each session is given a scoped staging_uri (a unique prefix under its catalog's storage) that the statement policy is pinned to — a COPY may only read or write there.

The API is the credential vendor for a session: it supplies the Polaris connection the agent's session uses, rather than the agent reading a static secret from its own config. Today that identity is still DuckHaven's shared Polaris service principal — governance rests on the API's per-statement authorization and the catalog grants, not on the token's identity. Per-principal Polaris identities are still planned; the staging-write leg, however, no longer needs raw credentials at all — see below.

Staging files (presigned URLs)

To get bulk bytes into the stage, a client asks the API to presign them rather than handing out storage credentials. POST /api/sql/sessions/{session_id}/staging-files takes a list of file names and returns, per file, a short-lived presigned put_url (upload) and get_url (read) scoped to a key under that session's staging prefix, plus an expires_at. This models a Snowflake internal stage: the broker vends time-boxed, single-key access and bulk bytes flow directly between the client, the object store, and the agent — never through the control plane.

A load then looks like:

1. POST …/staging-files with {"files": ["orders.parquet"]} → put_url / get_url.
2. The client uploads the Parquet with a plain HTTP PUT to put_url — no storage SDK, no secret.
3. The client runs INSERT INTO … SELECT * FROM read_parquet('<get_url>') through the session; the agent reads the get_url over its httpfs extension with no staging credential of its own — all authorization is in the URL signature.

Because backend-specific signing lives only in the API, the client and agent treat every backend uniformly as opaque HTTPS: S3 and the bundled store use SigV4 presigned URLs, Azure ADLS/Blob uses the equivalent SAS URLs. This is why it works on the bundled backend, which has no STS — a presigned URL is a signature, not a vended session token, so it grants genuinely narrow, time-boxed access there (narrower than the static credentials Polaris would otherwise vend). The statement policy admits read_parquet('https://…') only when the URL points at the session's own staging prefix; arbitrary local-FS or external reads are still rejected. Presigned URLs expire, and a request against a reaped/closed session returns 409 (the client reconnects, exactly as for statement execution).

Sandboxing

With the allowlist relaxed, the statement policy is no longer the only thing standing between a statement and the outside world. The agent is contained at three further layers, all on by default.

The container

The agent container runs with a read-only root filesystem (only its results volume and a /tmp tmpfs are writable), dropped Linux capabilities, no-new-privileges, a process cap, and a non-root user — so a stray local write outside the results volume fails at the kernel rather than at a parser.

Network egress

The agent is attached to an isolated Docker network with no route off the host. It can reach the API, Polaris, the object store, and the trace collector; it cannot reach anything else. This is what contains a statement that tries to read from or write to an arbitrary address — the second layer the design called for, enforced by the kernel rather than by SQL parsing.

You can verify it on a running stack:

docker compose -f deploy/docker-compose.yml exec -T agent python -c "
import duckdb; c = duckdb.connect(); c.execute('LOAD httpfs')
c.execute(\"SELECT content FROM read_text('https://example.com/')\").fetchone()"
# expected: a network/IO error, not a result

Some deployments must opt out — and then this layer is gone

An agent whose catalogs use external storage (s3, adls_gen2), or that must reach an off-host Polaris or collector, needs real outbound access. Those deployments apply deploy/docker-compose.egress-opt-out.yml, which puts the agent back on the default network. When you do that, the API statement policy becomes the only remaining layer between a session statement and arbitrary egress. Prefer restricting egress to the specific hosts you need (a host firewall, or a Kubernetes NetworkPolicy — see Install) over removing the restriction outright.

DuckDB configuration

After the agent has set a connection up — extensions loaded, catalogs attached, credentials vended — it locks DuckDB's configuration (SANDBOX_LOCK_CONFIGURATION, on by default). A session statement can no longer widen its own sandbox with SET: disabled_filesystems, enable_external_access, secret_directory, extension_directory, home_directory, custom_extension_repository, and allow_unsigned_extensions all become read-only for the life of the connection, as does the lock itself. A small exception list keeps writable only what the agent needs afterwards — the per-statement memory/thread slice, the profiler, and the SET timezone the statement policy admits.

SANDBOX_DISABLED_FILESYSTEMS can additionally disable a whole DuckDB filesystem. It is off by default because the agent reads staged files over presigned HTTP(S) URLs, and disabling HTTPFileSystem would break that. Set it to HTTPFileSystem on a deployment that does not use staging. (Contrary to earlier guidance, it does not break the bundled Polaris or object store: the Iceberg REST client and the S3 filesystem are independent of the generic HTTP one.)

Observability

Sessions emit their own metrics — sessions opened, sessions closed by reason (client, idle, max-lifetime, open-timeout, agent-disconnect, agent-self-reap, failed), statements by outcome, statement-policy rejections by rule, an active-sessions gauge, and a per-agent held-session count — alongside OpenTelemetry spans for open/exec/close that continue the same trace across the API→agent hop. To watch for a leak, alert when a per-agent held-session count stays above that agent's share of the active-sessions gauge, or when the agent-self-reap close reason is firing at all: the backstop only fires when a normal close was dropped. See Monitoring.

The audit trail

Metrics tell you how many sessions ended badly; the audit trail tells you which one. Every session is a row in Postgres and every statement it runs is an ordinary query row tagged origin="session" with its session_id, so a whole dbt run is one workload you can read top to bottom instead of a few hundred unattributed history entries. The Connections screen in the UI renders both — see Read the session audit trail for the walkthrough.

Two things the row records that are worth knowing about:

Why a session ended. A session's final status (closed, expired, failed) does not say why, and "expired" covers two quite different situations. The row therefore also carries a typed close reason:

Reason	What happened
client	The client called DELETE /sql/sessions/{session_id} — a clean shutdown
idle	Reaped: no statement for SQL_SESSION_IDLE_TIMEOUT_S. Usually a client that crashed or forgot to close
max_lifetime	Reaped: alive longer than SQL_SESSION_MAX_LIFETIME_S, however busy it was
open_timeout	The agent never confirmed the open, so the session never became usable
compute_timeout	The open gave up while compute was still starting — see Cold start
provisioning_timeout	The session waited for compute that never arrived at all
agent_disconnect	The agent holding the connection dropped; everything in flight on it died with it
agent_lease	The agent self-reaped an orphan it was still holding — the backstop for a lost close
failed	The agent reported it could not open the connection at all

An idle reap and an explicit close look identical in an aggregate; here they do not. Sessions that ended before this field existed have no reason recorded, and the UI reports them as unknown rather than guessing.

Which tool opened it. The API reads the request's User-Agent when the session opens and stores the product name and version on the row — dbt-duckhaven 0.1.0, dlt-duckhaven 0.2.0. It takes the first product/version token, so clients are expected to lead their User-Agent with the calling application; the connector does so from duckhaven-sql-connector 0.3.0. A session opened by an older connector (or one with no application set) is recorded as duckhaven-sql-connector rather than the workload, so audit rows written before that client fix are attributed to the connector, not the tool. This is deliberately server-captured rather than client-declared: the client cannot forge it, cannot forget to set it, and cannot leave a stale value behind after a failure. It is the same idea as PostgreSQL's application_name or Databricks' client_application column.

Richer, client-supplied context — a dbt model name, a dlt load id — is not yet part of the contract. When it lands it will be an optional set of labels supplied once at session open, and it will live on the session row rather than being smuggled through the SQL text.

Who can see what

Any member of a workspace can see every session in that workspace, including the SQL each statement ran. This is the same visibility the query history has always had, and it is deliberate: sessions run against shared catalogs and consume shared agent capacity, so "who is running what right now" is workspace-level information. If you need SQL that one member cannot read, it belongs in a different workspace.

Two capabilities are narrower. Filtering sessions by principal, agent, or time requires the queries:admin permission, matching the query history's audit filters. Force-closing someone else's session — which drops its connection and fails whatever it had in flight — is likewise admin-only.

Sessions and their statements are kept for as long as the rows are: there is no retention sweep for them today, so plan for the table to grow with your session volume.

Not this

Sessions are for tool connections, not a second interactive UI: the DuckHaven worksheet still uses the one-shot query path. Sessions also do not add cross-agent transactions — each session is one connection on one agent.

Related

• Query execution — the one-shot path sessions sit alongside.
• Elastic compute — starting compute for a session that finds none.
• Read the session audit trail — the Connections screen.
• Configuration — every setting named on this page.