state.sql

On this page

State: where operational runtime lives

Phase: State | Order: 5 | Required: Yes

One ATTACH statement tells OndatraSQL where to put the push queue, fetch staging buffer, and OAuth refresh tokens. Separate from catalog.sql (which defines the DuckLake catalog) because state is hot operational data, not analytical data.

Default (created by `ondatrasql init`)

ATTACH 'state.duckdb' AS state
    (ENCRYPTION_KEY '${ONDATRA_STATE_KEY}');

A local DuckDB file in the project root, encrypted at rest with AES-GCM-256. ondatrasql init generates ONDATRA_STATE_KEY and writes it to .env.

Why the `state` alias is reserved

After executing state.sql, OndatraSQL runs USE state so unqualified table references (sync_evt, sync_inflight, sync_claim, sync_jobref, sync_apply_log, tokens) resolve into the attached catalog. Renaming the alias breaks every state operation.

Schema

OndatraSQL creates the tables idempotently on first run. You do not need to define them.

Table	Purpose
`sync_evt`	Pending outbound events (push queue)
`sync_inflight`	Events currently being pushed by a worker
`sync_claim`	Per-claim heartbeat, used for orphan recovery
`sync_jobref`	Async-push job references (sinks that return a job id)
`sync_apply_log`	Per-row push outcomes for crash-safe ack
`tokens`	OAuth refresh tokens, plaintext column, file-level encrypted

Encryption

DuckDB’s file-level encryption is enabled by passing ENCRYPTION_KEY to ATTACH. The key must be a 32-byte secret (the init template uses base64-encoded random bytes). The entire file — including WAL and any temporary spill — is encrypted.

The token column is plaintext in SQL; the file is encrypted on disk.
Lose the key and all state (push queue + tokens + jobref) becomes unreadable.
Back up the key separately from the state file (secret manager, password manager, etc.).
Rotate the key by exporting state with one key and re-encrypting on import — there is no in-place rotation.

To run without encryption (not recommended for tokens), omit the option:

ATTACH 'state.duckdb' AS state;

Required for these commands

state.sql is opened by every command that touches operational state:

ondatrasql run — push queue, fetch staging, OAuth refresh
ondatrasql sandbox — same (in sandbox mode the state catalog is still the real one; only the DuckLake catalog is forked)
ondatrasql auth — writes refresh tokens to state.tokens

Running these in a project without config/state.sql fails with open state: config/state.sql required.

Other backends

The Go runtime is backend-agnostic — it only depends on state being an attached catalog with the schema above. Switching backend is an edit to state.sql; no Go code changes.

Postgres (shared state, multi-process)

INSTALL postgres;
LOAD postgres;
ATTACH 'dbname=ondatra_state host=db.internal port=5432 user=ondatra password=${PG_STATE_PASSWORD}'
    AS state (TYPE postgres);

Removes the filesystem lock, so several ondatrasql processes can hold state open at once. Useful when running in ephemeral containers, where a local state.duckdb would be wiped between runs.

No DDL adapter is needed: DuckDB’s postgres extension maps BLOB and DEFAULT now() itself, and the upsert the token store relies on (INSERT OR REPLACE) works. The state file’s AES-GCM encryption does not apply here — secure the Postgres instance instead.

Removing the lock is not the same as supporting concurrent workers. Two processes running the same pipeline against one state database will interfere: the fetch-staging claim is UPDATE … SET claim_id = ? WHERE claim_id IS NULL, which is unscoped, so one worker claims another’s staged rows. Worse, startup recovery resets any claim not yet recorded in _ondatra_acks, so starting a second worker while the first is mid-run un-claims its live rows and both process them. Give each pipeline its own database or schema, and run one worker per pipeline at a time.

Quack (shared DuckDB server, multi-pod)

LOAD quack;
ATTACH 'quack://state.example.com:9494' AS state
    (TYPE quack, TOKEN '${ONDATRA_QUACK_TOKEN}');

Currently blocked. ATTACH, CREATE TABLE and INSERT work, but the write operations the state store depends on do not:

Operation	Error
`UPDATE`	`Binder Error: Can only update base table`
`DELETE`	`Binder Error: Can only delete from base table`
`INSERT OR REPLACE`	`Not implemented Error: GetStorageInfo not implemented yet`

Verified against DuckDB 1.5.4. Track the duckdb-quack issue tracker; a production-ready Quack is expected alongside DuckDB 2.0. Until then, use Postgres for shared state.