SQL export design#

Exports stream a validated query into private gzip files without staging the full result on the application host. See the API and configuration.

Usage#

POST /api/v1/projects/{id}/exports accepts the same authorized SELECT and typed parameters as interactive queries, plus format: jsonl|csv and an idempotency key. It returns a durable job. Clients poll its status and fetch paginated, temporary links to gzip files after success. Use the operation IDs from HTTP, the standalone CLI or MCP. The MCP client discovers the contract with porcupine_docs and calls it with porcupine_call.

Existing boundaries#

Huma operation metadata enforces events:read and project scope and also generates MCP discovery/dispatch. SQL validation returns one canonical SELECT. Project views and restricted ClickHouse readers enforce RLS, public columns and mandatory FINAL. Interactive HTTP buffers 2 MiB and its reader fixes 5 seconds/256 MiB/1,000 rows; exports must not weaken that profile. SQLite WAL/FULL uses one connection, so no rows or transaction may remain open across ClickHouse/S3 network work. Workers persist creator key IDs and reload active authorization, never persist tokens.

Shape#

exports_types.go defines public jobs/files, private stored jobs and deployment budgets. exports_jobs.go and exports_api.go own SQLite and API state transitions. exports_worker.go owns the single worker, admission, progress, cleanup and final publication. export_reader.go provisions a distinct project reader and streaming transport; export_stream.go validates the complete JSONCompact envelope. internal/exportstore owns official AWS SDK multipart IO and presigning.

One canonical SELECT executes once. Rows stream through bounded decoding, gzip and sequential 8-MiB S3 multipart buffers. File rotation divides output, never SQL. SQLite records attempt keys and multipart IDs. Earlier completed files stay private and unavailable through the API until the entire response closes successfully, its row footer matches, and publication commits against current creator grants, current attempt and cancellation state. Late HTTP-200 errors fail the whole job.

Jobs record a delivery watermark for accepted original and mutation events and wait for that prefix of the queue before execution. This is a delivery barrier, not an injected SQL filter or transactional snapshot. Corrected data is read using ClickHouse's execution-time semantics. Retries execute SQL afresh, never append rows from another attempt to an old result.

Streaming and recovery#

Direct multipart streaming avoids requiring enough application-host SSD space for the full export. Local staging would simplify retries of uploads, but would also impose a dataset-sized disk requirement. The worker instead validates the complete query response and fences publication against cancellation, attempt identity, and current permissions.

File rotation bounds multipart part counts and individual downloads. A small sequence-based delivery barrier includes already accepted queued events. The API supports submission, listing, status, cancellation, and file links. Budgets and S3 configuration remain deployment-only. The worker needs no separate service or staging filesystem.

Limits and tradeoffs#

One export runs at a time and waits when ingestion is behind or delivery unhealthy. Its separate ClickHouse reader uses one processing thread, 512 MiB memory, 5 MiB/s network/remote-read ceilings, bounded disk spill and a longer deadline. Thread limits are not strict CPU percentage isolation. Bounded Go/S3 buffers avoid holding the dataset on SSD or in RAM. Individual rows, total output, pending jobs, attempts and artifact lifetime remain bounded; crossing a limit fails explicitly.

A crash can require rerunning the whole SQL query. Unknown multipart uploads in the narrow create-before-checkpoint window require an S3 lifecycle abort rule. Completed orphan objects are removed using their persisted attempt keys. Cleanup is durable and retries. Already issued presigned URLs remain valid briefly after key revocation. No query result is silently truncated and reported successful.

Verification#

Unit/race tests cover SQLite transitions, permissions, idempotency, streaming completion, oversized values, late errors, abort/delete and publication races. Real ClickHouse and MinIO checks exercise large results, types, aggregates, multiple files, cancellation, restart, cold data and object integrity. Use the load-testing guide to measure ingestion and interactive latency while an export is active.

Provisioning locks are context-aware. Export account/schema setup has a separate five-second deadline so a slow administrative request cannot hold interactive callers beyond their own deadline.