# LakehouseBox — full reference for agents and people Generated by platform/scripts/docs_build.py from the CLI and the control plane source on the day of the last site release. The HTML version is at https://lakehousebox.com/docs/. Support: hello@lakehousebox.com. ## What LakehouseBox is A managed Apache Iceberg warehouse: an Iceberg REST catalog and S3-compatible object storage run as one system in Nuremberg, Germany, by NewCo SL, a company in formation in Spain. Bring your own engine (DuckDB 1.5.5 or newer, PyIceberg 0.12, Spark 3.5 + Iceberg 1.11); LakehouseBox is never in the query path. Every warehouse is a separate catalog with its own storage identity, enforced by the catalog; storage credentials handed to engines are per table and short-lived. Open formats: Iceberg tables and Parquet files you can take elsewhere. Hostnames: - https://lakehousebox.com — website, signup, docs - https://api.lakehousebox.com — the control plane API (accounts, projects, warehouses, tables, recipes, organisations, usage) - https://catalog.lakehousebox.com — the Iceberg REST catalog (engines talk to it with the warehouse credential) - https://s3.lakehousebox.com — S3-compatible storage (path-style, region us-east-1) The free plan meters one thing: storage, 5 GB. No card. Paid plans to be announced. # Getting started From an email address to a queryable Apache Iceberg catalog in a few minutes, from the terminal or from an agent. Everything here works against https://api.lakehousebox.com today; nothing is described that the service does not do. ## What you get A catalog is one Iceberg REST catalog with its own object storage, run in Nuremberg, Germany, by NewCo SL: it organises your tables and connects them to your tools; LakehouseBox includes the storage and the table maintenance. You bring the engine: DuckDB, PyIceberg or Spark read and write it directly; LakehouseBox is never in the query path. Credentials are per catalog, storage sessions are per table and short-lived, and every tenant is separated at the catalog. (The API calls a catalog a warehouse: /v1/warehouses, warehouse_id; the CLI accepts both words.) The free plan meters one thing, storage: 5 GB. No card. ## 1. Install the command line One Python file, standard library only, verified against a published SHA-256. Python 3.9 or newer. No sudo. ``` curl -fsSL https://lakehousebox.com/install.sh | sh lhbox --version ``` Or with pip tooling: pipx install lakehousebox (the package on PyPI follows the same file; until the first release is published there, install from the URL above). The installer puts lhbox in ~/.local/bin and prints a PATH hint if that directory is not on your PATH. The CLI talks to https://api.lakehousebox.com by default; set LHBOX_URL only for a self-hosted or local stack. Details: CLI reference (https://lakehousebox.com/docs/cli/). ## 2. Connect this machine One command. The CLI prints a link and a code and waits; open the link, create your account there if you have none (name, email, organisation, a password from the mail), accept the Terms (https://lakehousebox.com/legal/terms/), and approve the connection: this machine's name, the catalog, read and write (you may lower it to read). The CLI receives its own key, saves it to ~/.config/lhbox/credentials.json (mode 0600) and never shows it. Every later command reads it from there; revoke it any time from your account page. The CLI talks to https://api.lakehousebox.com by default; set LHBOX_URL only for a self-hosted or local stack. ``` lhbox login # Open https://lakehousebox.com/app/device?user_code=XXXX-XXXX and approve the connection … # Connected to demo_data (read and write) as laptop. Saved to ~/.config/lhbox/credentials.json. ``` Without a browser on this machine, the email-code path still works: lhbox signup --email you@company.com --workspace acme, then lhbox signup --email … --code 123456 --accept-terms --save (six digits by mail from no-reply@lakehousebox.com, valid ten minutes). Your first catalog is named demo_data; another is lhbox catalog create (a minute or so; names match ^[a-z][a-z0-9_]{0,62}$). Every command that names a catalog takes the name (or the id) as its positional argument, --catalog as an alias; the destructive ones (catalog delete, catalog publish) also want --confirm (or --yes). Global flags (--json, --human, --url, --api-key) may go before or after the subcommand. ## 3. Connect your engine ``` lhbox connect --engine duckdb # or pyiceberg, spark; --catalog when you have several ``` Prints a paste-ready recipe with the catalog's credential inside. For DuckDB it is four statements; the one that matters is ATTACH 'acme--demo-data' AS demo_data (TYPE ICEBERG, …) with the bare bucket name, -- with underscores as hyphens: the s3:// form attaches read-only. The catalog is attached under its own name, so the recipes of two catalogs paste into one session without colliding, and the recipe's example query is a comment so the block pastes whole. Engines exchange the credential for catalog tokens (900 s) and per-table storage sessions themselves. All three recipes and their gotchas: Engines (https://lakehousebox.com/docs/engines/). ## 4. Load and query ``` CREATE TABLE demo_data.demo.cities AS SELECT * FROM read_parquet('cities.parquet'); SELECT country, count(*) FROM demo_data.demo.cities GROUP BY 1 ORDER BY 2 DESC; ``` That is an Iceberg table now: versioned, compacted by the catalog's maintenance, readable by any engine pointed at the catalog. Many files, or CSV files, in one commit: lhbox table import 'data/*.parquet' --namespace demo --name cities (Engines (https://lakehousebox.com/docs/engines/)). To choose identifier fields, required columns or partitioning, create the table first with lhbox table create --column id:long:required:identifier … and then INSERT. Check what landed with lhbox table get --catalog demo_data --namespace demo --name cities. ## 5. Give agents their own keys ``` lhbox agent create --name reporter --grant :write # Agent reporter created (member of the organisation; grants: demo_data:write). Its API key, shown once: al_live_… ``` An agent key holds exactly the grants you give it, read or write per catalog (none until granted), is revoked on its own (lhbox agent revoke --agent-id …) and never manages the organisation or its people. Invite colleagues with lhbox org invite --email …; claim your company's email domain with lhbox org domain --domain … so verified colleagues can join. ## For agents Output is JSON whenever stdout is not a terminal; every error is a typed object with a code, a message and usually a remedy; the exit code carries the class (1 fix the input, 2 a real limit or conflict, 3 auth, 4 not found, 5 retry, 6 malformed command). lhbox usage (GET /v1/usage) lists every limit with its headroom so a limit is never met by failing. Every mutation takes --idempotency-key. The whole documentation as one plain-text file: /llms-full.txt (https://lakehousebox.com/llms-full.txt); the index: /llms.txt (https://lakehousebox.com/llms.txt). ## Limits on the free plan | what | value | enforced | | storage, private · public | 5 GB · 50 GB | yes: the one metered thing; bucket quota, and 409 at create and vend time | | catalogs per project · projects per account | 3 · 5 | yes, at creation | | tables per catalog · namespaces per catalog | 50 · 10 | no: guideline (engines commit straight to the catalog; enforcement planned) | | objects | 50,000 | no: guideline, metered hourly | | catalog operations · commits per month · seconds between commits | 1,000,000 · 20,000 · 10 | no: guideline | | snapshot retention | 20 snapshots · 7 days | applied by maintenance, not disableable | Storage is measured hourly by maintenance and summed over the organisation; over the limit, creating catalogs or tables and vending write credentials answer 409 quota_exceeded (reading still works) until space is freed. The guidelines are abuse limits, not prices, and nothing refuses the 51st table today; GET /v1/usage returns every limit with enforced: true|false and how (limits_detail). Paid plans are to be announced. ## Not available today - A Snowflake data path (the catalog integration connects; reading data needs work on both sides). - Sign in with Google or any social login; accounts are created with your email address (a password set from the signup mail in the browser, a six-digit code in the CLI). A self-hosted deployment may trust its own OpenID Connect issuer instead. - A full web dashboard. There is /app/ (https://lakehousebox.com/app/), "Your account" (small, for people; agents use the API): log in with email and password (the session stays in the tab), see the organisation's catalogs with your level on each, create one, delete one by typing its name, create tokens for agents with read or write per catalog (the key is shown once), invite and manage members, read usage against the 5 GB, and run SQL with DuckDB compiled to WebAssembly in the browser (the site never sees the data; it talks to the catalog and the object store with your own scoped credentials). It refuses writes as a courtesy, not as a permission; the API and the CLI remain the primary interface. - An SLA; we publish what we measure instead. One site, daily off-host backups, rehearsed restore. - Paid plans (waiting for the company registration). ## Help hello@lakehousebox.com. Security reports: security@lakehousebox.com (security.txt (https://lakehousebox.com/.well-known/security.txt)). Legal: Terms (https://lakehousebox.com/legal/terms/), Privacy (https://lakehousebox.com/legal/privacy/), Acceptable use (https://lakehousebox.com/legal/aup/), Data processing (https://lakehousebox.com/legal/dpa/), Security overview (https://lakehousebox.com/legal/security/), Legal notice (https://lakehousebox.com/legal/legal-notice/). # CLI reference One Python file, standard library only, the same REST API a curl call would use. Output is JSON when stdout is not a terminal (or with --json), a table when it is. Anywhere a project_id or a catalog's warehouse_id is accepted, the name works too. The CLI says catalog where the API says warehouse: lhbox catalog … is the command group (lhbox warehouse … still works), and every --catalog flag takes --warehouse as well. ## Install ``` curl -fsSL https://lakehousebox.com/install.sh | sh # ~/.local/bin/lhbox, SHA-256 verified, no sudo pipx install lakehousebox # once published on PyPI; the same file export LHBOX_URL=https://api.lakehousebox.com ``` ## Configuration | what | how | | LHBOX_URL / --url | API base URL. The built-in default is https://api.lakehousebox.com (the service); set it to your own address for a self-hosted or local stack, e.g. http://localhost:8080. | | LHBOX_API_KEY / --api-key | The key. Otherwise read from the credentials file. | | LHBOX_CREDENTIALS | Path of the credentials file, default ~/.config/lhbox/credentials.json (0600, keyed by API URL; written by signup --save, recover --save, login --save). | | AGENTLAKE_* | The pre-rename names are still read as fallbacks, and ~/.config/agentlake/credentials.json is read (never written). | ## Exit codes | code | meaning | what to do | | 0 | ok | | | 1 | invalid input | the values you sent are wrong; fix them, never retry unchanged | | 2 | limit or conflict | a real quota or a real 409; stop and change the plan | | 3 | auth | key missing, wrong or revoked | | 4 | not found | no such project, catalog, namespace or table | | 5 | server or unreachable | ours or the network's; safe to retry with backoff | | 6 | usage error | the command line itself is malformed; fix the command, never retry | The server's typed error body goes to stderr verbatim: it names the limit, the current value and the remedy. ## Commands Rendered from the CLI's own --help at build time. ### lhbox ``` usage: lhbox [-h] [--version] [--url URL] [--api-key API_KEY] [--json] [--human] {signup,terms,account,login,recover,api-key,org,agent,whoami,doctor,usage,audit,project,catalog,namespace,table,credentials,connect,duckdb,sink,mcp} ... LakehouseBox: an open lakehouse you control. positional arguments: {signup,terms,account,login,recover,api-key,org,agent,whoami,doctor,usage,audit,project,catalog,namespace,table,credentials,connect,duckdb,sink,mcp} signup create your account and catalog: email -> code -> API key terms the Terms version in force and whether you accepted it account your account login connect this machine to your catalog: approve a code in the browser; an agent named after this machine is created with the access you approve, and its key is saved here, never printed recover lost API key: a code to your email gives you a new one api-key API keys of the caller org organisations: members, domains, invitations agent agents (tokens): machine identities of the organisation, each with its own key, its own grants and an organisation role whoami who am I: identity, organisation, my level on each catalog, limits with headroom and whether each is enforced, the next step -- brief lines on a terminal, a brief JSON object when piped doctor check the whole path to a DuckDB session: API, credential, catalog (FAIL while it is still provisioning), recipe and level, a read through the catalog, write-policy (HEAD and a one-key list: the bucket accepts the identity, no object written), the DuckDB here; PASS/FAIL/SKIP per line, exit 1 on any FAIL. Writes nothing unless --write-probe usage limits, current usage and headroom (which limits are enforced), one row per catalog -- a brief JSON object when piped, tables on a terminal; --catalog for one catalog's storage and traffic by day audit who did what: your events, or your organisation's (owner/admin) project projects (placement is immutable) catalog catalogs: one Iceberg catalog per lakehouse, with its own storage and identity (the API names one by warehouse_id, under /v1/warehouses) namespace namespaces (created on demand by table create) table tables credentials short-lived, table-scoped storage credentials connect a paste-ready recipe for your own engine, carrying the identity that matches your level (read holders get the read-only one; snowflake always does); with one catalog visible no --catalog is needed; for duckdb the DuckDB on this machine is checked against 1.5.5 duckdb open the `duckdb` shell already attached to the catalog (the recipe goes in through a 0600 init file, deleted at once; the credential is never on screen or in argv); --persist instead writes DuckDB persistent secrets so a bare ATTACH works in any session sink ingest sinks: one HTTP intake per table for a device, a webhook or an agent without an engine; JSON arrays in, Iceberg rows out on a rolling policy (https://lakehousebox.com/docs/ingest/) mcp the MCP server for Claude Desktop, Claude Code and other hosts: install its configuration with the saved key, or run it options: -h, --help show this help message and exit --version show program's version number and exit --url URL API base URL (env LHBOX_URL, default https://api.lakehousebox.com) --api-key API_KEY API key (env LHBOX_API_KEY) --json force JSON output --human force human-readable output Output is JSON when piped and a table on a terminal. Anywhere a project_id or a catalog's warehouse_id is accepted, the name works too. Exit codes, one meaning each: 0 ok 1 invalid input the values you sent are wrong; fix them, do not retry 2 limit/conflict a real quota or a real 409; change the plan 3 auth key missing, wrong or revoked 4 not found no such project, catalog, namespace or table 5 server or unreachable (safe to retry) 6 usage error the command line is malformed; fix it, do not retry ``` ### lhbox signup ``` usage: lhbox signup [-h] --email EMAIL [--workspace WORKSPACE] [--full-name FULL_NAME] [--organization ORGANIZATION] [--handle HANDLE] [--code CODE] [--engine {duckdb,pyiceberg,spark}] [--save] [--show-key] [--accept-terms] options: -h, --help show this help message and exit --email EMAIL --workspace WORKSPACE workspace name (default: the part before @) --full-name FULL_NAME your name, for the account --organization ORGANIZATION organisation name; with --handle the organisation is reserved now --handle HANDLE the organisation's handle (lakehousebox.com/app/; lowercase letters, digits, dashes; fixed after signup) --code CODE the code you received; prompted for if omitted --engine {duckdb,pyiceberg,spark} --save store the API key in ~/.config/lhbox/credentials.json; with it the key is NOT printed --show-key print the API key even when it was saved (default: a saved key stays out of the terminal) --accept-terms accept the Terms of Service and Privacy Policy ``` ### lhbox terms ``` usage: lhbox terms [-h] [--accept] options: -h, --help show this help message and exit --accept ``` ### lhbox account ``` usage: lhbox account [-h] {delete} ... positional arguments: {delete} delete delete your account: keys revoked now, data purged within 30 days options: -h, --help show this help message and exit ``` ### lhbox account delete ``` usage: lhbox account delete [-h] --confirm CONFIRM options: -h, --help show this help message and exit --confirm CONFIRM your email, typed out ``` ### lhbox login ``` usage: lhbox login [-h] [--issuer [ISSUER]] [--label LABEL] [--name NAME] [--level {read,write}] [--catalog CATALOG[:LEVEL]] [--new] [--no-wait] [--resume] [--wait] [--engine ENGINE] [--save] [--show-key] [--accept-terms] options: -h, --help show this help message and exit --issuer [ISSUER] log in through an external OpenID Connect issuer instead of the LakehouseBox login (the first configured one, or the one named); for self-hosted deployments with their own identity provider --label LABEL API key label on the --issuer path (the LakehouseBox login labels it cli ) --name NAME the agent's name shown on the consent screen and in Connections (default: this machine's hostname) --level {read,write} the access to ask for (default write); the person approving may lower it to read --catalog CATALOG[:LEVEL] connect to this catalog, by name or id, with the level to ask for on it (read or write; default: --level), e.g. --catalog sales:read (repeatable; default: the organisation's default catalog). The person approving sees them preselected and may change the set --new start a new authorisation even when the saved key still works --no-wait print the page and the code, keep the pending login in the credentials file (pending_device) and exit 0; finish it later with --resume --resume poll the pending login left by --no-wait once: connected (exit 0), or still pending (exit 3, the credential is still missing); --resume --wait polls until the person decides --wait with --resume: wait for the approval instead of polling once (the default `lhbox login` waits anyway, up to the code's 15 minutes) --engine ENGINE --save kept for compatibility: the key is always saved to ~/.config/lhbox/credentials.json --show-key print the API key as well (default: it is saved and never shown; an agent should not need it) --accept-terms accept the Terms of Service on first login (--issuer path) ``` ### lhbox recover ``` usage: lhbox recover [-h] --email EMAIL [--code CODE] [--save] [--show-key] options: -h, --help show this help message and exit --email EMAIL --code CODE --save store the key in ~/.config/lhbox/credentials.json; with it the key is NOT printed --show-key print the key even when it was saved ``` ### lhbox api-key ``` usage: lhbox api-key [-h] {create,list,revoke} ... positional arguments: {create,list,revoke} options: -h, --help show this help message and exit ``` ### lhbox api-key create ``` usage: lhbox api-key create [-h] [--label LABEL] options: -h, --help show this help message and exit --label LABEL ``` ### lhbox api-key list ``` usage: lhbox api-key list [-h] options: -h, --help show this help message and exit ``` ### lhbox api-key revoke ``` usage: lhbox api-key revoke [-h] --key-id KEY_ID options: -h, --help show this help message and exit --key-id KEY_ID ``` ### lhbox org ``` usage: lhbox org [-h] {list,members,invite,accept,domain,set,role} ... positional arguments: {list,members,invite,accept,domain,set,role} domain claim your organisation's email domain (yours must be on it), or --remove it role change a member's role: admin or member (an organisation keeps at least one admin) options: -h, --help show this help message and exit ``` ### lhbox org list ``` usage: lhbox org list [-h] options: -h, --help show this help message and exit ``` ### lhbox org members ``` usage: lhbox org members [-h] [--org ORG] options: -h, --help show this help message and exit --org ORG ``` ### lhbox org invite ``` usage: lhbox org invite [-h] [--org ORG] --email EMAIL [--role {member,admin}] options: -h, --help show this help message and exit --org ORG --email EMAIL --role {member,admin} ``` ### lhbox org accept ``` usage: lhbox org accept [-h] --token TOKEN options: -h, --help show this help message and exit --token TOKEN ``` ### lhbox org domain ``` usage: lhbox org domain [-h] [--org ORG] --domain DOMAIN [--remove] options: -h, --help show this help message and exit --org ORG --domain DOMAIN --remove release the domain instead of claiming it (admin); domain join switches off when no domain remains ``` ### lhbox org set ``` usage: lhbox org set [-h] [--org ORG] [--name NAME] [--domain-join {on,off}] options: -h, --help show this help message and exit --org ORG --name NAME --domain-join {on,off} let verified-domain users join ``` ### lhbox org role ``` usage: lhbox org role [-h] [--org ORG] --principal PRINCIPAL --role {admin,member} options: -h, --help show this help message and exit --org ORG org_id or the organisation's handle (lhbox org list shows both); default: your only organisation --principal PRINCIPAL --role {admin,member} ``` ### lhbox agent ``` usage: lhbox agent [-h] {create,update,list,revoke} ... positional arguments: {create,update,list,revoke} create a new token; it holds exactly the grants given (none until granted otherwise) and the role given (member unless --role admin) update rename a token, replace its grants and/or change its role (admin, or the member who created it, within their own role) list the organisation's tokens; grants as [{catalog, level, warehouse_id}] in JSON, catalog:level on a terminal revoke revoke a token's key at once (admin, or the member who created it) options: -h, --help show this help message and exit ``` ### lhbox agent create ``` usage: lhbox agent create [-h] [--org ORG] --name NAME [--role {admin,member}] [--grant CATALOG:LEVEL] options: -h, --help show this help message and exit --org ORG org_id or the organisation's handle (lhbox org list shows both); default: your only organisation --name NAME --role {admin,member} organisation role of the token (default member): what it may do to the organisation, its people and its tokens; capped by your own role. Data access is its grants --grant CATALOG:LEVEL :, repeatable (a name is resolved here against lhbox catalog list); capped by your own level on each catalog ``` ### lhbox agent update ``` usage: lhbox agent update [-h] [--org ORG] --agent AGENT [--name NAME] [--role {admin,member}] [--grant CATALOG:LEVEL] options: -h, --help show this help message and exit --org ORG org_id or the organisation's handle (lhbox org list shows both); default: your only organisation --agent, --agent-id AGENT the agent's id or its name (lhbox agent list shows both) --name NAME the new name --role {admin,member} new organisation role; capped by your own --grant CATALOG:LEVEL :, repeatable; the list REPLACES the current grants ``` ### lhbox agent list ``` usage: lhbox agent list [-h] [--org ORG] options: -h, --help show this help message and exit --org ORG org_id or the organisation's handle (lhbox org list shows both); default: your only organisation ``` ### lhbox agent revoke ``` usage: lhbox agent revoke [-h] [--org ORG] --agent AGENT options: -h, --help show this help message and exit --org ORG org_id or the organisation's handle (lhbox org list shows both); default: your only organisation --agent, --agent-id AGENT the agent's id or its name (lhbox agent list shows both) ``` ### lhbox whoami ``` usage: lhbox whoami [-h] [--full] options: -h, --help show this help message and exit --full the whole answer: every limit, project and organisation as tables on a terminal; piped or --json, GET /v1/usage as it comes plus the per-catalog levels (the default JSON is the brief object) ``` ### lhbox doctor ``` usage: lhbox doctor [-h] [--catalog CATALOG] [--write-probe] options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list; default: your only catalog (with several, name one) --write-probe also write and delete a 0-byte _lhbox/_probe/data/probe.parquet in the table bucket as the recipe's identity: the one check that sees a store refusing writes (the store's error is quoted verbatim on FAIL) ``` ### lhbox usage ``` usage: lhbox usage [-h] [--catalog CATALOG] [--days DAYS] [--full] options: -h, --help show this help message and exit --catalog CATALOG one catalog's view, day by day: stored bytes and objects, S3 requests and bytes in/out; the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list --days DAYS length of the daily history, 1..30 (default 7) --full piped or --json: GET /v1/usage as it comes (limits_detail, every project's warehouse_storage with history[]) instead of the brief object ``` ### lhbox audit ``` usage: lhbox audit [-h] [--org ORG] [--since SINCE] [--action ACTION] [--limit LIMIT] options: -h, --help show this help message and exit --org ORG org_id: the organisation's events (owners and admins) instead of only yours --since SINCE 24h, 7d, 30m, or an ISO-8601 timestamp --action ACTION one action, e.g. credentials.vend, or a prefix like org.* --limit LIMIT at most this many events (max 1000) ``` ### lhbox project ``` usage: lhbox project [-h] {create,list} ... positional arguments: {create,list} options: -h, --help show this help message and exit ``` ### lhbox project create ``` usage: lhbox project create [-h] --name NAME [--provider PROVIDER] [--region REGION] [--idempotency-key IDEMPOTENCY_KEY] options: -h, --help show this help message and exit --name NAME --provider PROVIDER default: the deployment's own --region REGION default: the deployment's own placement --idempotency-key IDEMPOTENCY_KEY any string; safe to reuse when retrying the same call ``` ### lhbox project list ``` usage: lhbox project list [-h] options: -h, --help show this help message and exit ``` ### lhbox catalog ``` usage: lhbox catalog [-h] {create,list,get,measure,update,rotate,grants,grant,revoke,delete,publish,unpublish,public-url,uploader,lifecycle} ... positional arguments: {create,list,get,measure,update,rotate,grants,grant,revoke,delete,publish,unpublish,public-url,uploader,lifecycle} create a new catalog: `lhbox catalog create ` puts it in the organisation's default project; slow (identities at the store, up to ~2 minutes), progress on stderr list every catalog you can see: name, id (warehouse_id in the API), your level, project, status and the format version its new tables get, side by side get one catalog by name or id: the row of `catalog list` plus its bucket, storage figure, default format version and status (ready, or provisioning while its storage has not accepted a write yet) measure measure a catalog's storage now (bytes and objects of its two buckets) instead of waiting for the hourly sweep; once a minute per catalog update change a catalog's settings: the format version LakehouseBox gives its new tables (admin, or a write holder) and its tier policy, hot or cold (admin) rotate replace the catalog's credentials (read/write and read-only): `lhbox catalog rotate `; the old keys and their tokens are refused at once, vended storage sessions run out within 900s; slow (identity writes at the store), progress on stderr grants who holds read or write on a catalog, and how (admin, membership, creator, explicit): `lhbox catalog grants ` grant grant read or write on a catalog to a member or an agent (admin, or a write holder up to write): `lhbox catalog grant --principal --level read|write` revoke revoke a grant (admin, or whoever made it): `lhbox catalog revoke --principal `; revoking a member's membership read records level none delete delete a catalog for good (admin): `lhbox catalog delete --confirm ` (or --yes); tables, buckets, identities, grants; slow, progress on stderr publish make a catalog PUBLIC (admin, the name typed out): every data file and every retained metadata.json becomes readable by anyone without credentials at its public URL; the REST catalog endpoint stays private; its bytes count against the 50 GB public allowance unpublish make a public catalog private again (admin): `lhbox catalog unpublish `; anonymous reads are refused at once public-url the public URL of a catalog and the no-credentials DuckDB recipe: `lhbox catalog public-url `; with --table, that table's current metadata URL (the thing to paste into iceberg_scan) uploader write-only credentials for the catalog's blob bucket: a relay, a camera, a field device or a partner dropping files under one prefix, never able to read, list (unless asked) or touch tables (https://lakehousebox.com/docs/uploaders/) lifecycle expiry rules on the catalog's blob bucket: delete camera/ after 90 days, abort stale multipart uploads; never on tables (https://lakehousebox.com/docs/lifecycle/) options: -h, --help show this help message and exit ``` ### lhbox catalog create ``` usage: lhbox catalog create [-h] [--project PROJECT] [--default-format-version {2,3}] [--no-wait] [--idempotency-key IDEMPOTENCY_KEY] [CATALOG] POST /v1/warehouses. `lhbox catalog create ` (lowercase letters, digits, _; a letter first). The call writes identities and policies at the store and can take up to two minutes on a busy host: a line every ~10 s says so; a timeout is exit 5 with the remedy to check `catalog list` first. Should the server answer 202 status: provisioning, the CLI polls until ready (--no-wait returns at once). positional arguments: CATALOG the catalog's name options: -h, --help show this help message and exit --project PROJECT project_id (UUID) or project name; list both with: lhbox project list (default: the organisation's default project) --default-format-version {2,3} the Iceberg format version (2 or 3) LakehouseBox gives tables IT creates in this catalog: table create without --format-version, the MCP create_table tool, table import; an engine's own CREATE TABLE is honoured as sent (default 2) --no-wait do not poll a 202 provisioning answer until ready --idempotency-key IDEMPOTENCY_KEY any string; safe to reuse when retrying the same call ``` ### lhbox catalog list ``` usage: lhbox catalog list [-h] [--project PROJECT] options: -h, --help show this help message and exit --project PROJECT project_id (UUID) or project name; list both with: lhbox project list (default: every project you can see) ``` ### lhbox catalog get ``` usage: lhbox catalog get [-h] [CATALOG] GET /v1/warehouses/{id}. `lhbox catalog get `; without a name, your only catalog. positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit ``` ### lhbox catalog measure ``` usage: lhbox catalog measure [-h] [CATALOG] POST /v1/warehouses/{id}/measure. `lhbox catalog measure `. Lists the catalog's table bucket and blob bucket as the tenant and stores the figure GET /v1/usage and `catalog list` show until the sweep's next sample (stale_after). Read level suffices. Quota enforcement still follows the sweep. positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit ``` ### lhbox catalog update ``` usage: lhbox catalog update [-h] [--default-format-version {2,3}] [--tier {hot,cold}] [CATALOG] PATCH /v1/warehouses/{id}. `lhbox catalog update --default-format- version 2|3` and/or `--tier hot|cold` (the catalog is always named). the Iceberg format version (2 or 3) LakehouseBox gives tables IT creates in this catalog: table create without --format-version, the MCP create_table tool, table import; an engine's own CREATE TABLE is honoured as sent. Tables an engine creates itself keep the version the engine chose; existing tables are unchanged. GET /v1/config/formats (no login) lists which engines write each version. Tier policy: cold (the default) lets volumes untouched for the quiet period move to cold storage, where a table's first read is slower; hot never moves them. The sample catalog and public catalogs stay hot. positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both) options: -h, --help show this help message and exit --default-format-version {2,3} 2 (written by every engine) or 3 (geometry/geography columns; not every engine writes it yet) --tier {hot,cold} hot: never moved to cold storage; cold: quiet volumes move to cold storage (admin only) ``` ### lhbox catalog rotate ``` usage: lhbox catalog rotate [-h] [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both) options: -h, --help show this help message and exit ``` ### lhbox catalog grants ``` usage: lhbox catalog grants [-h] [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit ``` ### lhbox catalog grant ``` usage: lhbox catalog grant [-h] --principal PRINCIPAL --level {read,write} [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both) options: -h, --help show this help message and exit --principal PRINCIPAL principal_id of the member or agent (lhbox org members / agent list) --level {read,write} ``` ### lhbox catalog revoke ``` usage: lhbox catalog revoke [-h] --principal PRINCIPAL [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both) options: -h, --help show this help message and exit --principal PRINCIPAL ``` ### lhbox catalog delete ``` usage: lhbox catalog delete [-h] [--confirm [NAME]] [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both) options: -h, --help show this help message and exit --confirm, --yes [NAME] the catalog's name, exactly (a bare --yes confirms the NAME given positionally); this cannot be undone ``` ### lhbox catalog publish ``` usage: lhbox catalog publish [-h] [--confirm [NAME]] [CATALOG] Puts one plain S3 bucket policy on the catalog's bucket (anyone may GET and LIST its objects, unsigned; writes and deletes stay refused). Public means the whole catalog: every table, every data file, every retained metadata.json including the table's history, and the list of object names. The Iceberg REST endpoint is NOT opened: readers use the metadata file's URL (lhbox catalog public-url --table .) with DuckDB's iceberg_scan and no credentials. Undo with: lhbox catalog unpublish . `lhbox catalog publish --confirm ` (or --yes). positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both) options: -h, --help show this help message and exit --confirm, --yes [NAME] the catalog's name, exactly (a bare --yes confirms the NAME given positionally); this cannot be undone ``` ### lhbox catalog unpublish ``` usage: lhbox catalog unpublish [-h] [--confirm [NAME]] [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both) options: -h, --help show this help message and exit --confirm, --yes [NAME] accepted for symmetry with publish; not required ``` ### lhbox catalog public-url ``` usage: lhbox catalog public-url [-h] [--table TABLE] [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit --table TABLE .: also print its current metadata_url and version_hint_url ``` ### lhbox catalog uploader ``` usage: lhbox catalog uploader [-h] {create,list,revoke} ... positional arguments: {create,list,revoke} create a new uploader: one IAM identity confined to s3:PutObject on s3://b-/*; the secret is shown once (--show-key) or written to a 0600 file (--save) list the catalog's uploaders without their secrets (never stored); --all includes revoked ones revoke revoke an uploader by id or name: its key is deleted at the store first and refused at once (the store's IAM writes: key and identity; the prefix grant is a bucket policy) options: -h, --help show this help message and exit ``` ### lhbox catalog uploader create ``` usage: lhbox catalog uploader create [-h] --name NAME [--prefix PREFIX] [--list] [--expires-at ISO8601] [--save FILE] [--show-key] [CATALOG] POST /v1/warehouses/{id}/uploaders. Write level. Three IAM writes at the store (each up to 10 s at the host's current identity count); the policy is written once, never re-put. Without --save or --show-key the command stops before calling the API: no identity is created whose secret nobody received. The uploader cannot read what it uploads; consume the files from the catalog side with the catalog's own credential (lhbox connect). `lhbox catalog uploader create --name ...`. positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit --name NAME who uploads: relay, camera-north, partner-acme (a lowercase letter, then lowercase, digits, _ -) --prefix PREFIX key prefix the credential is confined to, e.g. photos/2026/ (default: the whole blob bucket) --list also allow ListBucket under the prefix (an s3:prefix condition: the device must list with ?prefix=); without it the uploader cannot list at all --expires-at ISO8601 recorded and shown as the status 'expired' after that time; the key itself stays valid until revoked --save FILE write the pair, endpoint, bucket and prefix to FILE (mode 0600) and do not print the secret --show-key print the secret once (as well as saving it, with --save) ``` ### lhbox catalog uploader list ``` usage: lhbox catalog uploader list [-h] [--all] [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit --all include revoked uploaders ``` ### lhbox catalog uploader revoke ``` usage: lhbox catalog uploader revoke [-h] --uploader UPLOADER [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit --uploader UPLOADER the uploader_id or the name (lhbox catalog uploader list) ``` ### lhbox catalog lifecycle ``` usage: lhbox catalog lifecycle [-h] {set,get,clear} ... positional arguments: {set,get,clear} set add or replace ONE rule (by id; the default id is the prefix), keeping the others get the rules configured on the blob bucket, including any set with boto3 clear remove every lifecycle rule from the blob bucket options: -h, --help show this help message and exit ``` ### lhbox catalog lifecycle set ``` usage: lhbox catalog lifecycle set [-h] [--prefix PREFIX] [--expire-days N] [--expire-date YYYY-MM-DD] [--abort-multipart-days N] [--id ID] [--disabled] [--replace-all] [CATALOG] PUT /v1/warehouses/{id}/lifecycle. Write level. Reads the current rules first and re-puts them with this one (the S3 API replaces the whole document); --replace-all keeps none. Objects are deleted at the store's next daily pass after they are due (up to 24 h late), permanently. Transitions do not exist here (no storage classes); --expire-days 0 is refused (the store would accept it and never fire it). positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit --prefix PREFIX key prefix the rule applies to, e.g. camera/ (default: the whole blob bucket) --expire-days N delete objects N days (24 h each) after their last write; at least 1 --expire-date YYYY-MM-DD delete every matching object at the first pass on or after this date (a past date: the next pass) --abort-multipart-days N abort multipart uploads not completed N days after they began --id ID the rule's id (default: the prefix without slashes, or 'bucket'); a rule with the same id is replaced --disabled store the rule disabled (kept, not evaluated) --replace-all drop every other rule, this one alone remains ``` ### lhbox catalog lifecycle get ``` usage: lhbox catalog lifecycle get [-h] [--xml] [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit --xml print the stored S3 LifecycleConfiguration document instead of the table ``` ### lhbox catalog lifecycle clear ``` usage: lhbox catalog lifecycle clear [-h] [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit ``` ### lhbox namespace ``` usage: lhbox namespace [-h] {list} ... positional arguments: {list} options: -h, --help show this help message and exit ``` ### lhbox namespace list ``` usage: lhbox namespace list [-h] [--catalog CATALOG] options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list; default: your only catalog (with several, name one) ``` ### lhbox table ``` usage: lhbox table [-h] {list,get,create,import,set-properties} ... positional arguments: {list,get,create,import,set-properties} list every table in a catalog, or in one namespace get schema, real format-version, snapshot and row count import import Parquet or CSV files into a table as ONE commit: DuckDB on this machine runs one CREATE TABLE … AS SELECT (or INSERT INTO … SELECT) over read_parquet([all the files]) or read_csv([…]); GeoParquet metadata is kept as geo.* table properties set-properties set or remove table properties (one commit through the catalog): --property key=value …, --remove key … options: -h, --help show this help message and exit ``` ### lhbox table list ``` usage: lhbox table list [-h] [--catalog CATALOG] [--namespace NAMESPACE] options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list; default: your only catalog (with several, name one) --namespace NAMESPACE restrict to one namespace ``` ### lhbox table get ``` usage: lhbox table get [-h] --catalog CATALOG --namespace NAMESPACE --name NAME options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list --namespace NAMESPACE --name NAME ``` ### lhbox table create ``` usage: lhbox table create [-h] --catalog CATALOG --namespace NAMESPACE --name NAME [--column COLUMN] [--schema-file SCHEMA_FILE] [--format-version {2,3}] [--idempotency-key IDEMPOTENCY_KEY] options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list --namespace NAMESPACE --name NAME --column COLUMN name:type[:required][:identifier], repeatable --schema-file SCHEMA_FILE a full Iceberg schema as JSON, e.g. {"type":"struct","schema-id":0,"identifier-field-ids": [1],"fields":[{"id":1,"name":"id","required":true,"typ e":"long"}]} --format-version {2,3} 2 or 3; default: the catalog's default_format_version (2 unless changed with lhbox catalog update). 3 holds geometry/geography columns; GET /v1/config/formats says which engines write each. The version actually used is printed on stderr. --idempotency-key IDEMPOTENCY_KEY any string; safe to reuse when retrying the same call ``` ### lhbox table import ``` usage: lhbox table import [-h] [--catalog CATALOG] --namespace NAMESPACE --name NAME [--format {auto,csv,parquet}] [--python PATH] [--full] [--mode {create,append}] [--format-version {2,3}] [--no-version-check] [--idempotency-key IDEMPOTENCY_KEY] FILE_OR_GLOB [FILE_OR_GLOB ...] Imports many files in one statement, so one Iceberg commit however many files (44 files one by one were 44 commits and 44 waits). Parquet (.parquet) or CSV (.csv, .csv.gz, .tsv; header and types auto-detected by DuckDB's read_csv) by extension, or --format; one format per run. Globs are expanded here; s3:// and https:// sources are read by DuckDB's httpfs. DuckDB is the python module of the interpreter running lhbox when importable (--python PATH or LHBOX_PYTHON names another interpreter's), else the `duckdb` binary on PATH (1.5.5 or newer). The catalog credential goes recipe -> DuckDB and is never printed. A format-version 3 table (--format-version 3, or the catalog's default) is created through the API with the schema DuckDB inferred (geometry columns typed), then filled with one INSERT; a format-version 2 table is DuckDB's own CREATE TABLE AS SELECT, geometry written as WKB. When a file carries GeoParquet metadata, the table gets geo.encoding, geo.crs, geo.columns, geo.primary_column (docs/engines). positional arguments: FILE_OR_GLOB Parquet or CSV files, globs (quote them: 'data/**/*.parquet') or URLs (s3://…, https://…) options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list; default: your only catalog (with several, name one) --namespace NAMESPACE created on demand --name NAME the table --format {auto,csv,parquet} what the files are (default auto: by extension; an extension nobody recognises is a usage error) --python PATH the interpreter whose duckdb module runs the import (env LHBOX_PYTHON; default: the python running lhbox, else the duckdb binary on PATH) --full include the generated SQL statement (every column and file) in the JSON --mode {create,append} create (default; the table must not exist: 409 -> exit 2 otherwise) or append into an existing table (columns matched by name; missing ones become NULL) --format-version {2,3} for --mode create: 2 or 3; default the catalog's default_format_version. 3 goes through the API (POST /v1/tables with the inferred schema, then INSERT); 2 is DuckDB's own CREATE TABLE AS SELECT --no-version-check import with a DuckDB older than 1.5.5 anyway (its manifests are not maintained by the catalog) --idempotency-key IDEMPOTENCY_KEY any string; safe to reuse when retrying the same call ``` ### lhbox table set-properties ``` usage: lhbox table set-properties [-h] --catalog CATALOG --namespace NAMESPACE --name NAME [--property KEY=VALUE] [--remove KEY] options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list --namespace NAMESPACE --name NAME --property KEY=VALUE repeatable; values are strings --remove KEY repeatable ``` ### lhbox credentials ``` usage: lhbox credentials [-h] --catalog CATALOG --namespace NAMESPACE --table TABLE options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list --namespace NAMESPACE --table TABLE ``` ### lhbox connect ``` usage: lhbox connect [-h] [--catalog CATALOG] [--engine {duckdb,pyiceberg,spark,snowflake}] [--no-version-check] options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list; default: your only catalog (with several, name one) --engine {duckdb,pyiceberg,spark,snowflake} --no-version-check do not run `duckdb --version` / `python3 -c 'import duckdb'` (writes need DuckDB 1.5.5 or newer: older versions write manifests the catalog's maintenance cannot read) ``` ### lhbox duckdb ``` usage: lhbox duckdb [-h] [--catalog CATALOG] [--persist] [--python PATH] [--no-version-check] ... Without --persist: runs the duckdb binary with -init pointing at a private 0600 file holding the connect recipe (secrets and ATTACH), removes the file a second later, and hands you the shell with the catalog attached under its alias. Arguments after -- go to duckdb. With --persist: runs CREATE OR REPLACE PERSISTENT SECRET lhbox_ (TYPE ICEBERG) and lhbox__s3 (TYPE S3, scoped to the catalog's bucket) in DuckDB, so any later session -- the shell or python -- needs only the printed ATTACH line. Trade-off: the persistent secrets keep the catalog credential on disk in DuckDB's secret directory (~/.duckdb/stored_secrets, 0600), readable by any DuckDB this user runs; revoke with lhbox catalog rotate or DROP PERSISTENT SECRET. positional arguments: -- DUCKDB_ARGS passed to the duckdb binary, e.g. -- -readonly or -- mydb.duckdb options: -h, --help show this help message and exit --catalog CATALOG the catalog's warehouse_id (UUID) or its name; list both with: lhbox catalog list; default: your only catalog (with several, name one) --persist write persistent DuckDB secrets and print the ATTACH line instead of opening a shell --python PATH with --persist: the interpreter whose duckdb module writes the secrets (env LHBOX_PYTHON) --no-version-check do not warn about a duckdb older than 1.5.5 ``` ### lhbox sink ``` usage: lhbox sink [-h] {create,list,get,delete,send} ... positional arguments: {create,list,get,delete,send} create a new sink on an existing, unpartitioned table; the send key is shown once (--show-key) or written to a 0600 file (--save) list the sinks of every catalog you can see (or of one), with what is waiting and the last roll; never a key get one sink by id or name: state, roll policy, lag, last roll, rejects, totals delete delete a sink: its send key is refused from now on; batches still waiting are discarded (--discard-pending when there are any); the table is untouched send POST one batch (a JSON array of objects in a file, or - for stdin) to a sink with its send key options: -h, --help show this help message and exit ``` ### lhbox sink create ``` usage: lhbox sink create [-h] --table NS.TABLE --name NAME [--roll-seconds N] [--roll-bytes N] [--inactivity-seconds N] [--save FILE] [--show-key] [CATALOG] POST /v1/sinks. Write level on the catalog. No IAM write and no commit: the first roll adds the optional __ingest_ts column and the metadata-retention properties. One sink per table. Without --save or --show-key the command stops before calling the API: no sink is created whose key nobody received. `lhbox sink create --table .
--name --save sink.json`. positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both); default: your only catalog (with several, name one) options: -h, --help show this help message and exit --table NS.TABLE the table the sink feeds, .
; it must exist (lhbox table create) --name NAME the sink's name (the catalog rule: lowercase letter, then lowercase, digits, _) --roll-seconds N commit what is waiting every N seconds (default 300, floor 60); a roll is a commit, 300-900 suits a small producer --roll-bytes N also roll when N bytes wait (32-64 MiB; default 64 MiB) --inactivity-seconds N also roll when the sink has been quiet for N seconds --save FILE write the send key, the send URL and the sink id to FILE (mode 0600) and do not print the key --show-key print the send key once (as well as saving it, with --save) ``` ### lhbox sink list ``` usage: lhbox sink list [-h] [--all] [CATALOG] positional arguments: CATALOG the catalog's name (or its id; lhbox catalog list shows both) options: -h, --help show this help message and exit --all include deleted sinks ``` ### lhbox sink get ``` usage: lhbox sink get [-h] SINK positional arguments: SINK the sink_id or the name options: -h, --help show this help message and exit ``` ### lhbox sink delete ``` usage: lhbox sink delete [-h] [--discard-pending] SINK positional arguments: SINK the sink_id or the name options: -h, --help show this help message and exit --discard-pending delete even if batches are waiting for a roll ``` ### lhbox sink send ``` usage: lhbox sink send [-h] [--send-key-file FILE] [--send-key SEND_KEY] [--batch-id ID] [SINK] FILE The send route, POST /v1/sinks/{id}: the key from --send-key-file (what `sink create --save` wrote), --send-key or LHBOX_SEND_KEY; the batch id from --batch-id or a fresh UUID. The same batch id again answers duplicate without storing twice. 202 accepted; 400 not an array; 413 over 16 MiB. positional arguments: SINK the sink_id (omit it when --send-key-file names one) FILE a file holding a JSON array of objects, or - for stdin options: -h, --help show this help message and exit --send-key-file FILE the file `sink create --save` wrote --send-key SEND_KEY the send key itself (prefer --send-key-file or LHBOX_SEND_KEY) --batch-id ID the Idempotency-Key: 1-128 chars of A-Z a-z 0-9 . _ : - (default: a UUID) ``` ### lhbox mcp ``` usage: lhbox mcp [-h] {install,run} ... positional arguments: {install,run} install write the mcpServers entry into a host's configuration; the key comes from --api-key, LHBOX_API_KEY or the credentials file and is never printed run start the MCP server over stdio with the saved key in its environment (for a host configured by hand: command lhbox, args mcp run) options: -h, --help show this help message and exit ``` ### lhbox mcp install ``` usage: lhbox mcp install [-h] [--client {claude-code,claude-desktop}] [--url URL] [--from SOURCE] [--name NAME] [--scope {local,user,project}] [--print] Writes {"lhbox": {"command": "uvx", "args": ["--from", SOURCE, "lakehousebox- mcp"], "env": {LHBOX_URL, LHBOX_API_KEY}}} into the host's mcpServers, with the key read from the credentials file (or --api-key / LHBOX_API_KEY). Claude Code: `claude mcp add-json` when `claude` is on PATH, else the entry is printed with the key masked and the command to run. Claude Desktop: its claude_desktop_config.json is merged into (other servers kept), after a .bak- copy; the file is written 0600. The saved key is a person's (lhbox signup --save / login --save), which today is what the account tools need; pass --api-key with a token's key to confine the server to its grants instead (docs/mcp.md). options: -h, --help show this help message and exit --client {claude-code,claude-desktop} which host's configuration to write (default: claude- code) --url URL API base URL the server will talk to and whose saved key is used (default: the global --url / LHBOX_URL, i.e. https://api.lakehousebox.com) --from SOURCE what uvx installs the server from: the platform/mcp directory of a checkout, or the package name lakehousebox-mcp (once published); default: the checkout this CLI runs from, else lakehousebox-mcp --name NAME the server's name in the host (default: lhbox) --scope {local,user,project} Claude Code only: user (default; every project on this machine), local (this project, this machine), or project (.mcp.json, shared through version control: the key would be too) --print print the mcpServers snippet with the key MASKED instead of writing anything (for another host's configuration; put the real key in from the credentials file) ``` ### lhbox mcp run ``` usage: lhbox mcp run [-h] [--url URL] [--from SOURCE] ... positional arguments: -- SERVER_ARGS passed to the server, e.g. -- --list-tools options: -h, --help show this help message and exit --url URL API base URL (default: the global --url / LHBOX_URL, i.e. https://api.lakehousebox.com) --from SOURCE what uvx installs the server from: the platform/mcp directory of a checkout, or the package name lakehousebox- mcp (once published); default: the checkout this CLI runs from, else lakehousebox-mcp ``` # API reference Base URL https://api.lakehousebox.com. JSON in, JSON out. Every failure is {"error": {"code", "message", …}} with, where it helps, remedy, field, limit, current, available, retry_after_seconds or attempts_left, and always a request_id to quote when writing to us; unexpected failures are 500 with the same id. Request bodies are limited to 1 MB. ## Authentication Send Authorization: Bearer al_live_… with the API key itself, or a token from POST /v1/tokens (RS256, 900 s), or a token from an identity provider the deployment trusts. There are three kinds of principal: humans (created by signup), agents (created by an organisation admin, optionally confined to warehouses) and the operator. Keys look like al_live__; only the hash is stored; ten wrong secrets for one key id from one address within ten minutes answer 429 with Retry-After. Words: a catalog in the account page, the CLI and these docs is a warehouse in the API (/v1/warehouses, warehouse_id, my_level); the API keeps that name until a versioned rename adds /v1/catalogs aliases. The Iceberg catalog at https://catalog.lakehousebox.com and the object store at https://s3.lakehousebox.com are separate surfaces with their own credential, the catalog's catalog_credential from GET /v1/connection. They never accept LakehouseBox API keys. ## Idempotency POST /v1/projects, /v1/warehouses and /v1/tables take an Idempotency-Key header (any string). A replay returns the original response; the same key on a different endpoint is 409 idempotency_key_reused. ## Identifiers Wherever a project_id or warehouse_id is expected, the name you chose is accepted too; an unknown value is a 404 that names both accepted forms and the command that lists them. Two warehouses with one name in different projects make the name ambiguous (409 ambiguous_name with candidates). ## Health and identity ### GET /.well-known/openid-configuration → 200 OpenID metadata for the tokens this API issues (client_credentials, RS256). Auth: none Response: issuer, jwks_uri, token_endpoint, grant_types_supported ### GET /jwks.json → 200 The RS256 public key that verifies tokens from POST /v1/tokens. Auth: none Response: keys[{kty, use, alg, kid, n, e}] ### POST /v1/tokens → 200 Exchange an API key for a short-lived bearer token for THIS API. Optional: every authenticated route also accepts the API key itself as the bearer. The Iceberg catalog never accepts either; it takes the warehouse's catalog_credential. Auth: none Request: {"api_key": "al_live_…"} Response: access_token (JWT), token_type: Bearer, expires_in: 900, principal_id Errors: 401 invalid_api_key, 401 api_key_revoked, 401 account_deleted (the account was deleted; WWW-Authenticate: Bearer error="invalid_token"), 403 principal_suspended, 429 rate_limited after 10 wrong secrets for one key id within 600 s (Retry-After header) ### GET /healthz → 200 Liveness: the state database answers and the catalog answers /v1/config. Auth: none Response: ok, catalog_ok, issuer, kid ## Signup and recovery ### POST /v1/signup/start → 202 Step 1 of 2 of the email-code signup: a six-digit code is mailed to the address; nothing is created yet. The signup FORM sends full_name, organization_name and handle too: the organisation is created pending (its handle reserved) and verify activates it. An address on a claimed, open domain joins that organisation instead (will_join). Auth: none Request: {"email": "you@example.com", "workspace"?: "acme", "full_name"?: "Ana Lopez", "organization_name"?: "Acme Labs", "handle"?: "acme-labs"} Response: email, workspace, delivery (email|log), organization{org_id, name, handle, status: pending} (form), will_join{org_id, name, handle} (claimed domain), terms{version, url, privacy_url, note}, next, note, expires_in (600 s), code_length (6) Errors: 400 invalid_value (email; handle: reason invalid|reserved, suggestion), 409 handle_taken (suggestion), 409 already_exists (the address has an account; recover the key instead), 429 rate_limited (5 starts per hour per address; or a code less than 60 s old, with retry_after_seconds), 502 mail_error CLI: lhbox signup --email … ### POST /v1/signup/verify → 201 Step 2 of 2: the right code creates the account, its API key, an organisation (owned by you unless an invitation or an open verified domain places you elsewhere), a project named after the workspace, a warehouse named `lake`, and returns the connection recipe. Auth: none Request: {"email": …, "code": "123456", "accept_terms": true, "engine": "duckdb"} (engine: duckdb|pyiceberg|spark, default duckdb) Response: api_key (shown once), api_key_note, principal_id, project_id, warehouse_id, organization{org_id, role, joined_via}, connection{as GET /v1/connection}, next[], terms{version, accepted, url} Errors: 404 no_pending_signup, 410 code_expired, 401 invalid_code (attempts_left; five attempts, then a new code is needed), 400 terms_required (accept_terms must be exactly true), 409 already_exists CLI: lhbox signup --email … --code … --accept-terms --save ### POST /v1/signup → 201 The one-call, UNVERIFIED signup for local development stacks. The managed service answers 403 verification_required: use /v1/signup/start + /verify. Auth: none Request: {"email", "workspace", "engine", "accept_terms"} Response: as /v1/signup/verify Errors: 403 verification_required on the managed service ### GET /v1/handles/{handle} → 200 Is this organisation handle free? For the signup form as you type (per-address rate limit). Handles: one character, or 3-40 lowercase letters, digits and dashes, starting and ending with a letter or digit; never two dashes in a row (`--` separates the handle from the catalog name in the organisation's storage bucket names, --); app, api, docs, legal, admin, lhbox, www, s3, catalog and auth are reserved. Auth: none Response: handle, valid, available, reason (invalid|double_dash|reserved|taken|null), suggestion (a free handle near it), pattern, note (with reason double_dash: why) Errors: 429 rate_limited ### POST /v1/recover/start → 202 Lost API key: a code to the account's email. The answer is identical whether or not the address has an account, so accounts cannot be enumerated here. Auth: none Request: {"email": …} Response: email, delivery (email when a code was mailed; smtp when the address has no account and nothing was sent; log on a development stack), expires_in (600 s), next Errors: 429 rate_limited (same 5/hour/address budget as signup; retry_after_seconds when a code is under 60 s old) CLI: lhbox recover --email … ### POST /v1/recover/verify → 200 The right code mints a NEW API key. Earlier keys keep working until revoked. Auth: none Request: {"email": …, "code": "123456"} Response: api_key (shown once), key_id, principal_id, note Errors: 404 no_pending_recovery, 410 code_expired, 401 invalid_code (attempts_left) CLI: lhbox recover --email … --code … --save ## Login and sessions ### POST /v1/auth/signup → 202 The signup form: a pending account and a pending organisation (handle reserved), and a set-password mail with a link to /app/welcome?token=… valid 3 days. Nothing is usable until the link sets a password. An address with a pending invitation joins the inviting organisation instead (the organisation fields are ignored, nothing is reserved; will_join says so with via: invitation), an address on a claimed and open domain joins that organisation as a member (via: domain); the invitation wins when both apply. The code path for agents and the CLI stays at POST /v1/signup/start. Auth: none Request: {"email": …, "full_name": …, "organization_name": …, "handle": …, "return_to": "/app/…"} (return_to optional: a path under /app/ on this site the welcome page continues to after the password is set, e.g. the device consent screen) Response: 202: email, delivery (email | log), expires_in (259200), organization{org_id, name, handle, status: pending} or will_join{org_id, name, handle, role, via: invitation|domain} + note_join, terms, next, note (when the link went to the log: why), return_to Errors: 400 invalid_value (handle grammar or a reserved word; suggestion carries a free one), 409 handle_taken, 409 already_exists (the address has finished signing up: log in), 429 rate_limited (5 per hour per address; one re-send per minute), 502 mail_error ### POST /v1/auth/password/set → 200 The set-password link: verifies the address, stores the argon2id hash, activates the pending organisation with the person as admin (or joins the inviting organisation with the invited role, or the open-domain one as a member -- a joiner provisions nothing: the organisation's default catalog is theirs), records the Terms version the form showed, and returns a session with warehouse{warehouse_id, name, project_id, created} and next. Auth: none Request: {"token": …, "password": …} (12 to 256 characters, not containing the address or the organisation name) Response: access_token (RS256, 15 min, claims sub/email/kind/sid), token_type, expires_in, refresh_token (30 days sliding), refresh_expires_in, session_id, principal_id, principal, organization{…, role, joined_via}, terms, next Errors: 400 password_too_short | password_too_long | password_contains_identity (the link is not spent), 401 invalid_login_token (unknown or already used), 410 token_expired ### POST /v1/auth/login → 200 Email and password to a session. The same 401 for an unknown address, a wrong password and an account without a password (a constant-time compare against a dummy hash in every case). Auth: none Request: {"email": …, "password": …} Response: the session shape of POST /v1/auth/password/set Errors: 401 invalid_login, 403 principal_suspended, 429 rate_limited (10 failures per address per hour, retry_after_seconds; 120 attempts per caller address per hour) ### POST /v1/auth/refresh → 200 A new access token and a rotated refresh token; the session's expiry slides 30 days. Presenting the refresh token that was just rotated out revokes the whole session (theft signal). Auth: none Request: {"refresh_token": …} Response: the session shape, with the new refresh_token Errors: 401 invalid_refresh_token | refresh_reused | session_revoked | session_expired ### POST /v1/auth/logout → 200 Revokes the session, by its refresh token or by the access token in Authorization (sid claim). The access token itself expires within 15 minutes. Auth: none Request: {"refresh_token": …} or Authorization: Bearer Response: revoked, session_id ### GET /v1/auth/sessions → 200 The caller's live sessions: where and when they logged in; `current` marks the one making the call. Auth: Authorization: Bearer Response: sessions[{session_id, created_at, last_used_at, expires_at, user_agent, ip, current}], count ### DELETE /v1/auth/sessions/{session_id} → 200 Revokes one of the caller's sessions. Auth: Authorization: Bearer Response: session_id, revoked: true Errors: 404 session_not_found (also for another account's session) ### POST /v1/auth/password/reset/start → 202 A 30-minute reset link to /app/reset?token=… by mail. 202 with the same body whether or not the address has an account. An account created through an identity provider that has no password yet gets the same link, which sets its first password. Auth: none Request: {"email": …} Response: email, delivery, expires_in (1800), next Errors: 429 rate_limited (5 per hour per caller address, shared with signup) ### POST /v1/auth/password/reset → 200 The reset link: a new password, every session revoked, a notice mailed; returns a fresh session. Auth: none Request: {"token": …, "password": …} Response: the session shape plus sessions_revoked, first_password Errors: 400 password_* (the link is not spent), 401 invalid_login_token, 410 token_expired ### POST /v1/auth/password/change → 200 A logged-in human changes their password; every OTHER session is revoked; a notice is mailed. Auth: Authorization: Bearer Request: {"current_password": …, "new_password": …} Response: changed: true, other_sessions_revoked, password_changed_at Errors: 401 invalid_login (wrong current password), 400 password_*, 409 no_password_set (identity-provider account: use the reset link), 403 forbidden (a token has no password) ### POST /v1/auth/device → 200 RFC 8628 device authorisation for the CLI, asking for a CONNECTION (docs/agent-setup.md): a device code to poll with and an 8-character user code (no ambiguous glyphs) for the human to approve at /app/device. client_name is the agent the approval creates (default the hostname), requested_level what it asks for (default write), warehouses which catalogs at which level ([{name | warehouse_id, level}], from `login --catalog NAME[:LEVEL]`; resolved within the approver's organisation when the code is read; default the organisation's default catalog at requested_level). warehouse_ids (ids or names, all at requested_level) is the older spelling and still accepted. Auth: none Request: {"client": "cli", "hostname": …, "client_name": …, "requested_level": "read|write", "warehouses": [{"name": …, "level": …}]} (all optional) Response: device_code, user_code (XXXX-XXXX), verification_uri, verification_uri_complete, expires_in (900), interval (5), client_name, requested_level, warehouse_ids, warehouses Errors: 429 rate_limited (60 per hour per caller address), 400 invalid_value CLI: lhbox login [--no-wait] ### GET /v1/auth/device/pending → 200 The consent screen's content for a pending code (humans only; nothing is decided by reading it): the agent's name, EVERY catalog of the organisation the caller holds something on -- `requested` marks the ones the CLI named, `level` the level the agent would hold there (the requested one, capped by the caller's own; null when not requested) -- and `requested` as the list the page sends back. Auth: Authorization: Bearer Request: ?user_code=XXXX-XXXX Response: user_code, client, hostname, client_name, requested_level, level, organization, warehouses[{warehouse_id, name, level, my_level, my_via, capped, pending, requested}], requested[{warehouse_id, name, level}], expires_in Errors: 404 device_code_not_found (mistyped, expired or already decided), 403 forbidden (a token), 409 org_required, 404 warehouse_not_found (the CLI named a catalog that is not the caller's), 409 ambiguous_name ### POST /v1/auth/device/approve → 200 A logged-in human connects the CLI as an AGENT of the organisation -- a token named after the machine, member role, holding exactly the catalogs and levels chosen in `warehouses` (the consent page's picker; each level capped by the approver's own) -- or refuses it with deny: true. The older pair still works: level lowers the requested level (never raises it), warehouse_ids narrows the set. An organisation without a catalog gets "demo_data" first. The answer is approved, not connected: the agent exists once the machine polls (claimed: false; GET /v1/auth/device/status follows it). Only humans; a token approving would hand out a key of the token. Auth: Authorization: Bearer Request: {"user_code": …, "warehouses": [{"warehouse_id" | "name": …, "level": "read|write"}], "deny": false} Response: approved, claimed: false, user_code, client, hostname, client_name, label, requested_level, connection{name, level, org_id, warehouses[{warehouse_id, name, level}]}, expires_in, note Errors: 404 device_code_not_found (mistyped, expired or already decided), 403 forbidden (a token; or a warehouse the approver holds nothing on), 404 warehouse_not_found, 409 org_required ### GET /v1/auth/device/status → 200 Where a CLI login stands, for the page that approved it: pending | approved | denied | used | expired, and `claimed` (the machine collected its key). Approving is not connecting -- the agent is created when the machine polls -- so the page says "waiting for to pick up its key" until this says claimed. A pending code is visible to any signed-in human (as the consent read is); a decided one only to the human who decided it. `expired` is an approval nobody collected within the code's 15 minutes. Auth: Authorization: Bearer Request: ?user_code=XXXX-XXXX Response: user_code, status, claimed, client, hostname, client_name, expires_in, connection{name, org_id, agent_id (once claimed), warehouses[{warehouse_id, name, level}]} | null Errors: 404 device_code_not_found, 403 forbidden (a token), 400 invalid_value ### POST /v1/auth/device/token → 200 The CLI polls until the human decides. The RFC's error names travel as error.code in the usual envelope: authorization_pending (keep polling), slow_down (interval grows by 5 s), expired_token, access_denied. Once approved: the key of the NEW AGENT (never the person's), labelled "cli ", shown once, with the connection it holds. Revoking the agent revokes the connection. Auth: none Request: {"device_code": …} Response: api_key, key_id, label, principal_id (the agent's), kind: agent, token_type: api_key, connection{agent_id, name, org_id, role, warehouses[{warehouse_id, name, level}], created_by, grants}, note Errors: 400 authorization_pending | slow_down | expired_token | access_denied | invalid_grant CLI: lhbox login | lhbox login --resume [--wait] ## Remote MCP: OAuth 2.1 authorization server ### GET /.well-known/oauth-authorization-server → 200 RFC 8414 metadata an MCP host reads to authorise against the remote MCP server: issuer, authorization/token/revocation endpoints, jwks_uri, scopes_supported, code + PKCE S256 only, public clients (token_endpoint_auth_methods_supported: none), Client ID Metadata Documents supported, iss on authorization responses, and the deprecated registration_endpoint while dynamic registration is on. Auth: none Response: issuer, authorization_endpoint, token_endpoint, revocation_endpoint, jwks_uri, registration_endpoint, scopes_supported, response_types_supported [code], grant_types_supported [authorization_code, refresh_token], code_challenge_methods_supported [S256], token_endpoint_auth_methods_supported [none], client_id_metadata_document_supported, authorization_response_iss_parameter_supported ### GET /.well-known/jwks.json → 200 The RS256 public key(s) that verify every token this API signs (kid on every key and every token; two keys during a rotation). The same document as GET /jwks.json, at the path the MCP server's token verifier uses. Auth: none Response: keys[{kty, use, alg, kid, n, e}] ### GET /oauth/authorize → 200 The authorization endpoint. The MCP host opens the person's browser here; the client is identified by a Client ID Metadata Document (client_id = its https URL, fetched server-side behind an SSRF guard and cached) or a registered client_id. redirect_uri must be one the client lists (exact; loopback on any port), response_type=code, code_challenge + code_challenge_method=S256 and resource (the MCP server's URL) are required. A bad client or redirect_uri is a 400 (never a redirect); every other error is a 302 to the redirect_uri with error, error_description, state and iss. On success: 302 to the consent page (/app/authorize?request=), where the logged-in person decides. Auth: none Request: ?client_id=&redirect_uri=…&response_type=code&code_challenge=…&code_challenge_method=S256&resource=&scope=&state=… Response: 302 to the consent page, or 302 to redirect_uri with error (unsupported_response_type, invalid_request, invalid_target, invalid_scope), state, iss Errors: 400 invalid_client (unknown client, or a metadata document that is not https, names a private or local address, redirects to another host, exceeds 64 KiB, is not JSON or does not match), 400 invalid_request (redirect_uri not registered), 429 rate_limited (120 per hour per address). Flat RFC 6749 shape: {error, error_description} ### GET /v1/oauth/authorize/pending → 200 The consent page's content for a pending authorization (a logged-in human, via a session): who is asking (client_name, client_uri, logo_uri, the host of its metadata URL, how it registered), the scopes as sentences, the MCP server the token is for, the organisation and the catalogs the person may grant with the level proposed (capped by their own), and the token record already bound to this client if any (the picker's initial state). Nothing is decided by reading it. Auth: Authorization: Bearer Request: ?request_id= Response: request_id, expires_at, expires_in, client{client_id, client_name, client_uri, logo_uri, host, registration (cimd|dynamic)}, scopes[], scope, scope_descriptions{}, resource, redirect_host, person{}, organization{org_id, name, handle, role}, warehouses[{warehouse_id, name, my_level, my_via, level, granted, default}], existing{agent_id, name, role, grants} | null, organizations[], default_org_id Errors: 404 oauth_request_not_found, 410 oauth_request_expired (15 minutes), 410 oauth_request_decided, 403 session_required (an API key instead of a login session), 403 forbidden (a token), 409 org_required ### POST /v1/oauth/authorize/decision → 200 The logged-in human allows or denies. Allowing creates a TOKEN RECORD of kind mcp in the organisation -- or reuses the one bound to (you, client_id) and replaces its grants -- named after the client and you, holding exactly the grants chosen (each capped by your own level; a catalog you hold nothing on is refused), member role unless you are an admin and ask otherwise; then mints a single-use authorization code (10 minutes). The token appears in the organisation's tokens list and is revoked there, which ends its refresh and access tokens at once. Auth: Authorization: Bearer Request: {"request_id": …, "allow": true, "grants": [{"warehouse_id": …, "level": "read|write"}], "org_id"?: …, "role"?: "member|admin"} or {"request_id": …, "allow": false} Response: allowed, redirect_to (the client's redirect_uri with code, state and iss; on deny error=access_denied, state, iss), client{}, scope[], expires_in, token{agent_id, name, org_id, role, kind: mcp, grants[], capped[], reused, connection{}}, note Errors: 404 oauth_request_not_found, 410 oauth_request_expired | oauth_request_decided, 403 session_required, 403 forbidden (a token; or a catalog you hold nothing on), 403 insufficient_role (a member asking for an admin token), 404 warehouse_not_found | org_not_found, 409 org_required, 429 rate_limited ### POST /oauth/token → 200 The token endpoint (form-encoded or JSON; public clients send client_id, never a secret). grant_type=authorization_code with code, code_verifier (PKCE S256), client_id, redirect_uri and resource -> an RS256 access token for the MCP server (aud = resource, sub = the mcp token record, act.sub = the person, scope, client_id, kind mcp, 15 minutes) and a refresh token (30 days sliding). A code is single use; presenting it twice revokes the tokens it produced. grant_type=refresh_token with refresh_token and client_id -> a new pair, the refresh token rotated; presenting the rotated-out one again revokes the whole family. A narrower scope may be asked for on refresh. Auth: none Request: grant_type=authorization_code&code=…&code_verifier=…&client_id=…&redirect_uri=…&resource=… or grant_type=refresh_token&refresh_token=…&client_id=…[&scope=…] Response: access_token (JWT), token_type: Bearer, expires_in (900), refresh_token, refresh_expires_in, scope Errors: RFC 6749 §5.2 flat shape {error, error_description}: 400 invalid_request | invalid_grant (unknown, used, expired or foreign code; wrong verifier; unknown, rotated, revoked or expired refresh token; the token record revoked) | invalid_scope | invalid_target | unsupported_grant_type, 401 invalid_client, 429 rate_limited ### POST /oauth/revoke → 200 RFC 7009: a client that logs out revokes its refresh token (or an access token, whose session is revoked: every access token of that session is then refused by the API and its refresh token is dead). 200 with {} whatever the token, as the RFC says. The person's own revocation is the tokens list on the account page. Auth: none Request: token=…[&token_type_hint=refresh_token|access_token] Response: {} ### POST /oauth/register → 201 RFC 7591 dynamic registration, the DEPRECATED fallback for hosts that cannot publish a Client ID Metadata Document. Public clients only: https redirect URIs, loopback http on any port, or a native app's private-use scheme. Registrations unused for 90 days are pruned. Off when AL_OAUTH_ALLOW_DCR is false. Auth: none Request: {"client_name": …, "redirect_uris": […], "grant_types"?: [authorization_code, refresh_token], "response_types"?: [code], "token_endpoint_auth_method"?: "none", "client_uri"?, "logo_uri"?, "application_type"?: "web|native", "software_id"?} Response: 201: client_id (dcr_…; no secret), client_id_issued_at, client_name, redirect_uris, grant_types, response_types, token_endpoint_auth_method: none, application_type, client_uri, logo_uri, software_id, note Errors: 400 invalid_client_metadata | invalid_redirect_uri, 403 access_denied (registration disabled), 429 rate_limited (20 per hour per address) ## Projects and catalogs (warehouses in the API) ### POST /v1/projects → 201 A project is the placement boundary: provider and region are fixed at creation and never change. Today there is one placement: hetzner, the deployment's own region. Auth: Authorization: Bearer Request: {"name": …, "provider"?, "region"?, "org_id"?} Header Idempotency-Key: any string Response: project_id, name, provider, region, placement_immutable: true, org_id Errors: 409 already_exists (name in use by you; project_id in the body), 400 unsupported_placement (available[]), 400 org_required (you belong to several organisations), 404 org_not_found, 409 idempotency_key_reused CLI: lhbox project create --name … ### GET /v1/projects → 200 Projects you own or reach through an organisation you belong to. Auth: Authorization: Bearer Response: projects[{project_id, name, provider, region, org_id, created_at}] CLI: lhbox project list ### POST /v1/warehouses → 201 A warehouse is one Iceberg catalog: one table bucket named -- (overture--data; u-<8 hex of the principal id>-- outside an organisation) plus one storage identity confined to it by the catalog. Also a blob bucket (----blobs) for non-Iceberg files. Catalogs created before 2026-09-20 keep w- / b-; the response and every row name the buckets as they are (bucket, blob_bucket). Bucket names are DNS names of at most 63 characters, so the catalog name is bounded per handle (400 name_too_long_for_bucket says the maximum) and may not end in _; catalog names are unique per organisation (docs/permissions-and-access.md, "Names"). Auth: Authorization: Bearer Request: {"project_id": , "name": …, "default_format_version": 2} (2 default, or 3: the Iceberg format version LakehouseBox's own create paths -- POST /v1/tables without format_version, the MCP create_table tool, table import -- give new tables here; an engine's own CREATE TABLE is honoured as sent) Header Idempotency-Key Response: warehouse_id, name, project_id, project, provider, region, catalog_uri, warehouse (s3:///), warehouse_name (the table bucket), bucket, blob_bucket, default_format_version, maintenance{compaction: true, target_file_size_mb: 128, snapshot_retention_days: 7, max_snapshots: 20, disableable: false}, storage_measurement{status: scheduled, in_seconds: 120, then} (the control plane measures the new catalog itself ~2 minutes after creation; until then its storage rows say measuring: true), status: ready | provisioning, storage_probe{table, blob, seconds} (provision = allocate: creation ends with a tenant-signed PUT + DELETE of a 0-byte object in each bucket -- 'ok', or the store's status and words, e.g. '500 InternalError ...'; on any refusal the catalog IS created and answers status: provisioning with retry_after_seconds: 30 and a remedy; the control plane retries every 30 s for 10 min and flips it to ready; GET /v1/connection answers 503 provisioning until then) Errors: 400 invalid_name (not a SQL-friendly name, or a name ending in _ whose bucket would end in -; suggestion), 400 name_too_long_for_bucket (the mapped bucket would exceed 63 characters; max_name_length for this owner, bucket_would_be), 400 invalid_value (default_format_version not 2 or 3; versions{} says who writes each), 409 already_exists (the name in this project, or in another project of the organisation: bucket, warehouse_id), 409 name_reserved_until_teardown (a deleted catalog of that name is still tearing down; poll, Retry-After 5), 409 bucket_leftover_from_deletion (a finished teardown left the bucket at the store; an operator removes it), 409 creation_in_progress (the same bucket is being provisioned right now; Retry-After 5), 409 limit_exceeded (warehouses per project; limit_value, current), 409 quota_exceeded (the organisation is over its storage limit; current, limit_value, scope), 404 project_not_found, 502 storage_error (safe to retry with the same idempotency key) CLI: lhbox catalog create [--project …] [--default-format-version 2|3] ### POST /v1/warehouses/{warehouse_id}/rotate → 200 Replace both of the warehouse's catalog credentials (the read/write identity t- and the read-only t--ro). Each new key is issued before the old one is deleted, so there is never a moment without a working key. The old READ/WRITE key is deleted before the answer: it and every catalog token minted from it are refused at once. The old READ-ONLY key (it could only read) is deleted by the background worker right after the answer -- one IAM write, about 10 s at the host's current identity count, later only if a catalog deletion's teardown is queued ahead of it -- and audited as warehouse.rotate_complete. Storage sessions already vended run out on their own within 900 s. Why the split: every IAM write costs the store a 10 s propagation deadline. Auth: Authorization: Bearer Response: warehouse_id, warehouse_name, catalog_credential{client_id, client_secret}, catalog_credential_ro{client_id, client_secret}, previous_client_id, previous_revoked, previous_client_id_ro, previous_revoked_ro (false at the answer), previous_ro_revocation{status: queued, audit, expected}, rotated_at, note, warning (only if the old read/write key could not be deleted) Errors: 404 warehouse_not_found, 409 ambiguous_name (two warehouses share the name; candidates[]) CLI: lhbox catalog rotate ### DELETE /v1/warehouses/{warehouse_id} → 202 Delete a warehouse for good, with its name typed in the body. Irreversible, in two steps. Before the 202: the catalog is gone for every caller and out of every list, every grant on it is revoked, its usage samples go, a public warehouse's anonymous-read policy is removed, warehouse.delete is audited. In the background (usually within a minute; one worker, so a queue of deletions runs in turn): tables dropped as the tenant, both buckets and the identities (the read-only one where it was ever needed) with their policies removed, then warehouse.delete_complete with the table count and every storage step, and the tombstone GET /v1/warehouses/{id} reports (404, state deleting -> deleted, the counts). Until the teardown reaches the identities, a recipe already in an engine's hands can still reach the catalog and the bucket directly. Why: every IAM write costs the store a 10 s propagation deadline and this route made six of them while the person waited. The nightly backup copy ages out within its retention window (14 days). Auth: Authorization: Bearer — admin of the organisation Request: {"confirm": ""} Response: 202 status: deleting, warehouse_id, name, project_id, bytes_at_deletion, measured_at, grants_removed, usage_samples_removed, tables_dropped: null, storage: null, storage_complete: null, poll (GET /v1/warehouses/{id}), note Errors: 400 confirmation_required (confirm_with names the expected value), 403 forbidden (not an admin), 404 warehouse_not_found, 404 warehouse_deleted (state, counts: already gone), 409 delete_in_progress (Retry-After: 5; the teardown is running) CLI: lhbox catalog delete --confirm ### POST /v1/warehouses/{warehouse_id}/uploaders → 201 A write-only credential for the catalog's blob bucket (----blobs; b- on older catalogs; the answer's `bucket`), for a relay, a camera, a field device or a partner that only drops files: one IAM identity u-- whose policy allows exactly s3:PutObject on arn:aws:s3:::/* (single and multipart uploads) and, only with list: true, s3:ListBucket on the blob bucket conditioned on s3:prefix * (the device must list with ?prefix=). Nothing on the table bucket, no reads, no deletes, no catalog access (a catalog token minted from the pair is refused). The secret is in this answer once and is never stored. Cost: 2 IAM writes at the store (CreateUser, CreateAccessKey), each a propagation deadline of up to 10 s at the host's current identity count, plus the two statements merged into the blob bucket's policy (an S3 call); nothing is re-put later. Auth: Authorization: Bearer — write level on the catalog Request: {"name": …, "prefix"?: "photos/2026/", "list"?: false, "expires_at"?: null} Response: uploader_id, warehouse_id, warehouse_name, name, prefix, list, iam_user, access_key_id, secret_access_key (once), endpoint, bucket, region, scope, policy, iam_writes, expires_at, created_at, created_by, status, recipe{aws_cli, python_boto3, curl_presign_note}, note Errors: 400 bad_name / invalid_value (prefix grammar, list, expires_at), 403 forbidden (read level), 404 warehouse_not_found, 409 already_exists (an active uploader of that name), 502 storage_error CLI: lhbox catalog uploader create --name relay --prefix photos/ [--list] (--save FILE | --show-key) ### GET /v1/warehouses/{warehouse_id}/uploaders → 200 The catalog's uploaders without their secrets (never stored); revoked ones only with ?include_revoked=true. Auth: Authorization: Bearer — read level on the catalog Response: warehouse_id, warehouse_name, bucket, count, uploaders[{uploader_id, name, prefix, list, iam_user, access_key_id, created_by, created_at, expires_at, revoked_at, status: active|expired|revoked}], note Errors: 404 warehouse_not_found CLI: lhbox catalog uploader list [--all] ### DELETE /v1/warehouses/{warehouse_id}/uploaders/{uploader_id} → 200 Revoke an uploader (by id or name): its access key is deleted at the store FIRST, so every request signed with it is refused from then on (a multipart upload in flight cannot complete), then its statements come off the blob bucket's policy and its identity is deleted: 2 IAM writes before the answer (3 for an uploader made before 2026-09-21, whose inline policy is deleted too). Idempotent: a revoked uploader answers 200 with already_revoked and retries whatever step is still pending. A catalog's deletion removes its uploaders' identities too. Auth: Authorization: Bearer — write level on the catalog Response: the uploader's row (status: revoked, revoked_at), revoked, already_revoked, storage{access_key, bucket_policy | user_policy, user}, iam_writes, note, warning (only if the key could not be deleted) Errors: 403 forbidden (read level), 404 warehouse_not_found / uploader_not_found CLI: lhbox catalog uploader revoke --uploader ### POST /v1/sinks → 201 An ingest sink for ONE existing, unpartitioned table: producers without an engine POST JSON arrays to its send route with the send-only key this answer shows once; the roller appends what is waiting to the table every roll_seconds (default 300, floor 60), or sooner when roll_bytes (32-64 MiB) or inactivity_seconds say so, stamping __ingest_ts. One sink per table. No IAM write and no catalog commit at creation: the first roll adds the optional __ingest_ts column and the metadata-retention properties. The send key is a hash on the sink row, never a principal: it can neither become a token nor hold a grant. Auth: Authorization: Bearer — write level on the catalog Request: {"name": …, "table": ".
", "warehouse": "", "roll_seconds"?: 300, "roll_bytes"?: 67108864, "inactivity_seconds"?: null} Response: sink_id, name, table, warehouse_id, warehouse_name, bucket, state, roll{seconds, bytes, inactivity_seconds, count}, prefix, ingest_url, send_url, send_key (once), send_key_id, table_changes{add_column, partition: none, properties, applied}, lag{}, received{}, rejects{}, iam_writes: [], created_at, note Errors: 400 bad_name / invalid_value, 403 forbidden (read level), 404 warehouse_not_found / table_not_found (with the namespace's tables), 409 sink_exists / already_exists / partitioned_table_unsupported, 404 not_found while sinks are off CLI: lhbox sink create --table .
[--roll-seconds 300] [--roll-bytes N] [--inactivity-seconds N] (--save FILE | --show-key) ### GET /v1/sinks → 200 The sinks of every catalog the caller can see (or of one with ?warehouse=), with lag, last roll and rejects; never a key. ?include_deleted=true adds the deleted ones. Auth: Authorization: Bearer — read level Response: count, sinks[{sink_id, name, table, warehouse_id, state, disabled_reason, roll{}, prefix, lag{batches_waiting, bytes_waiting, oldest_waiting_at, seconds_behind}, received{}, last_roll{}, rejects{}, created_at}], note Errors: 404 warehouse_not_found CLI: lhbox sink list [--catalog ] ### GET /v1/sinks/{sink_id} → 200 One sink by id or name: state, roll policy, lag (bytes and batches waiting, seconds behind), last roll (at, batches, rows, rejected_rows, snapshot_id, seconds), rejects counters and where the rejects files are, totals received. Auth: Authorization: Bearer — read level on its catalog Response: the sink's row as in GET /v1/sinks plus warehouse_name, bucket Errors: 404 sink_not_found, 409 ambiguous_name CLI: lhbox sink get ### DELETE /v1/sinks/{sink_id} → 202 Delete a sink: its send key is refused from now on, batches still waiting are discarded (the roller purges the intake prefix on its next tick; rejects files age out after 7 days), the table is untouched. 202. Auth: Authorization: Bearer — write level on its catalog Response: the sink's row (state: deleted, deleted_at), pending_batches_discarded, note Errors: 403 forbidden (read level), 404 sink_not_found CLI: lhbox sink delete [--discard-pending] ### POST /v1/sinks/{sink_id} → 202 The send route. Body: a JSON array of objects (<= 16 MiB); header Idempotency-Key: the batch id (1-128 chars of A-Z a-z 0-9 . _ : -). Each element's bytes are stored unchanged, one per line, as ONE NDJSON object under _sink//.ndjson in the catalog's blob bucket, written as the catalog's own identity: no IAM write, no catalog call, one small S3 PUT. The same batch id again answers 200 duplicate without writing. Elements that are not objects are rejected here (reasons[]); whether a row fits the table's schema is decided at the roll and shows in GET /v1/sinks/{id} rejects. Intake bytes are metered as ingest_bytes (GET /v1/usage), not as storage. Auth: Authorization: Bearer — an API key or a token here is 403 send_only Request: [ {…}, {…}, … ] with headers Authorization: Bearer , Idempotency-Key: , Content-Type: application/json Response: 202 {accepted, rejected, reasons[{index, reason, got}], batch_id, sink_id, bytes, received_at, state: pending, note} | 200 {duplicate: true, batch_id, accepted, rejected, bytes, received_at, state: pending|committed} Errors: 400 not_an_array / empty_batch / missing_idempotency_key / invalid_batch_id, 401 invalid_key, 403 send_only, 404 sink_not_found (deleted), 409 sink_disabled / quota_exceeded, 413 batch_too_large, 502 storage_error (safe to retry with the same batch id) CLI: lhbox sink send batch.json [--batch-id ] ### POST /v1/warehouses/{warehouse_id}/public → 200 Make a warehouse PUBLIC at the storage level: admin only, the warehouse name typed in the body. One plain bucket policy lets anyone read every object (data files and all retained metadata) with no credentials; writes stay refused and the catalog stays private. Its bytes move to the 50 GB public tier; 409 quota_exceeded (tier public) when that would overflow. Auth: Authorization: Bearer Request: {"confirm": ""} Response: warehouse_id, name, project_id, public: true, public_since, public_url, public_note, public_by, policy, tier: public, public_storage_bytes{…}, public_read{…}, bucket_quotas{quotas, errors}, note Errors: 400 confirmation_required, 403 forbidden (not an admin), 404 warehouse_not_found, 409 quota_exceeded (tier public) CLI: lhbox catalog publish --confirm ### DELETE /v1/warehouses/{warehouse_id}/public → 200 Make a public warehouse private again: admin only. The bucket policy is removed and anonymous reads are refused at once; bytes return to the private tier. Auth: Authorization: Bearer Response: warehouse_id, name, project_id, public: false, public_since: null, public_url: null, was_public, policy_removed, tier: private, storage_bytes{…}, bucket_quotas{quotas, errors}, note Errors: 403 forbidden (not an admin), 404 warehouse_not_found CLI: lhbox catalog unpublish ### PUT /v1/warehouses/{warehouse_id}/lifecycle → 200 Set the S3 lifecycle rules of the catalog's BLOB bucket (never the table bucket), replacing what was configured: each rule expires the objects under a prefix after N days (24 h each from the object's last write) or on a date, and/or aborts multipart uploads not completed after N days. The control plane writes the S3 LifecycleConfiguration XML and PUTs it as the tenant (the blob-bucket policy written at creation grants exactly that), then reads it back. The store's lifecycle worker passes daily and after every restart: deletion up to 24 h after an object is due, permanent; the storage meter follows at the next hourly sample or POST …/measure. Refused before the store sees it: expire_days 0 (the store accepts it and never fires it), any transition (no storage classes: 501 at the store), unknown fields, more than 1000 rules or 1 MiB. Tag and size filters are boto3's with the catalog credential; GET parses them back. Auth: Authorization: Bearer — write level on the catalog Request: {"rules": [{"id"?: "camera-90d", "prefix"?: "camera/", "expire_days"?: 90, "expire_date"?: "2026-12-31", "abort_incomplete_multipart_days"?: 7, "enabled"?: true}, …]} Response: warehouse_id, name, bucket (the blob bucket), configured: true, rules[{id, prefix, enabled, expire_days, expire_date, abort_incomplete_multipart_days, expressible}], count, xml_bytes, note Errors: 400 invalid_value (expire_days 0 or not a whole number, both expiry forms, unknown field with `accepted`, duplicate ids, empty rules, the prefix grammar) / not_supported (a transition), 403 forbidden (read level), 404 warehouse_not_found, 413 too_large (over 1 MiB of XML), 502 storage_error CLI: lhbox catalog lifecycle set --prefix camera/ --expire-days 90 [--abort-multipart-days 7] ### GET /v1/warehouses/{warehouse_id}/lifecycle → 200 The lifecycle rules configured on the catalog's blob bucket, read from the store as the tenant and parsed back -- rules set with boto3 included: tags, size_greater_than / size_less_than and `other` (a Disabled Transition, NoncurrentVersionExpiration) appear on the rule and `expressible` says whether PUT here could re-create it. configured: false with an empty list when none is set. ?include_xml=true adds the stored document verbatim. Auth: Authorization: Bearer — read level on the catalog Response: warehouse_id, name, bucket, configured, rules[…], count, note, xml (only with ?include_xml=true) Errors: 404 warehouse_not_found, 502 storage_error CLI: lhbox catalog lifecycle get [--xml] ### DELETE /v1/warehouses/{warehouse_id}/lifecycle → 200 Remove every lifecycle rule from the catalog's blob bucket (DELETE ?lifecycle as the tenant). Idempotent: 200 with had_rules false when none was configured. Objects a rule already deleted are gone. Auth: Authorization: Bearer — write level on the catalog Response: warehouse_id, name, bucket, configured: false, had_rules, rules_removed, rules: [], count: 0, note Errors: 403 forbidden (read level), 404 warehouse_not_found, 502 storage_error CLI: lhbox catalog lifecycle clear ### GET /v1/warehouses/{warehouse_id}/grants → 200 Who holds what on the warehouse: explicit grants and the implicit ones (admins write via admin, members read via membership, the creator write via creator, a legacy unconfined agent write via agent). A level of none is a membership read an admin revoked for this warehouse. Auth: Authorization: Bearer — any member of the organisation Response: [{principal_id, kind, display, level (read|write|none), via, granted_by, granted_at}] Errors: 404 warehouse_not_found CLI: lhbox catalog grants ### PUT /v1/warehouses/{warehouse_id}/grants/{principal_id} → 200 Grant read or write on a warehouse to a member or an agent of the organisation. A read grant yields read-only catalog and storage credentials (t--ro); a write grant the read/write ones. Auth: Authorization: Bearer — admin, or a human holding write (granting at most their own level) Request: {"level": "read"|"write"} Response: warehouse_id, principal_id, kind, display, level, via, granted_by Errors: 400 invalid_value, 403 forbidden (level, can_grant[]), 404 principal_not_found (not in this organisation) CLI: lhbox catalog grant --principal … --level read|write ### DELETE /v1/warehouses/{warehouse_id}/grants/{principal_id} → 200 Revoke a grant. Revoking a member's implicit membership read records level none for this warehouse; an admin's implicit write cannot be revoked (change their role). Auth: Authorization: Bearer — admin, or the person who made the grant Response: warehouse_id, principal_id, kind, display, revoked{level, via}, level, via Errors: 403 forbidden, 404 grant_not_found, 409 implicit_grant CLI: lhbox catalog revoke --principal … ### GET /v1/warehouses → 200 The catalogs you can see, ids and names side by side: every catalog of your organisation(s) and of the projects you own (a member token: exactly the catalogs it was granted), or those of one project with ?project=. Each row carries its project_id. Auth: Authorization: Bearer Request: ?project= (optional) Response: project, project_id (null without ?project), catalog_uri, warehouses[{name, warehouse_id, warehouse (s3:///), bucket, blob_bucket (the two storage bucket names as they are: -- and …--blobs, or w-/b- on older catalogs), project_id, project, created_at, my_level (read|write|none), my_via, status (ready | provisioning: its storage has not accepted a write yet), default_format_version (2|3), stored{bytes, table_bytes, blob_bytes, objects, blob_objects, complete, measured_at, stale_after (when the hourly sweep will have replaced the figure), source: sweep|creation|on_demand, unmetered, measuring (no sample yet and the first measurement is on its way)}, public, public_since, public_url}], count, limit (per project), headroom (null without ?project) Errors: 404 project_not_found CLI: lhbox catalog list [--project …] ### GET /v1/warehouses/{warehouse_id} → 200 One catalog by id or by name: the same row as the list, plus the bucket name and placement. Auth: Authorization: Bearer Request: the path holds the warehouse_id or the catalog's name Response: name, warehouse_id, warehouse (s3:///), warehouse_name (the table bucket), bucket, blob_bucket, project_id, project, created_at, my_level, my_via, status (ready | provisioning), default_format_version (2|3), stored{bytes, table_bytes, blob_bytes, objects, blob_objects, complete, measured_at, stale_after, source, unmetered, measuring}, public, public_since, public_url (public_note while public), catalog_uri, provider, region Errors: 404 warehouse_not_found (not yours; a member token: no grant on it), 404 warehouse_deleting / warehouse_deleted (a catalog you could see, by id: state, requested_at, completed_at, tables_dropped, bytes_at_deletion, grants_removed, usage_samples_removed, storage{step: ok|HTTP n}, storage_complete, tables_refused -- the counts once the background teardown has run), 409 ambiguous_name (two catalogs of that name: candidates[] lists their ids) CLI: lhbox catalog get ### PATCH /v1/warehouses/{warehouse_id} → 200 Change a catalog's settings: today default_format_version (2|3), the Iceberg format version LakehouseBox's OWN create paths give a new table -- POST /v1/tables without format_version, the MCP create_table tool, table import. An engine's own CREATE TABLE is honoured as sent, never upgraded; existing tables are unchanged. Sending the value already set changes and audits nothing. Audited as warehouse.update (changes, previous). Auth: Authorization: Bearer — admin or a write holder on the catalog Request: {"default_format_version": 3} (the path holds the warehouse_id or the catalog's name) Response: the row of GET /v1/warehouses/{id} (name, warehouse_id, warehouse, warehouse_name, project_id, project, created_at, my_level, my_via, default_format_version, the public fields, catalog_uri) plus updated[] (the fields that changed), previous{default_format_version}, format{writers[], readers[], note} for the default now in force, note Errors: 400 missing_field (empty body), 400 invalid_field (a setting other than default_format_version; accepts[]), 400 invalid_value (not 2 or 3; versions{} says who writes each), 403 forbidden (read level; can_grant[] names who can), 404 warehouse_not_found, 409 ambiguous_name CLI: lhbox catalog update --default-format-version 2|3 ### POST /v1/warehouses/{warehouse_id}/measure → 200 Measure the catalog's storage NOW instead of waiting for the hourly sweep: bytes and objects of its table bucket and its blob bucket (both as the row names them), listed as the tenant, summed and stored as a usage sample (source: on_demand) that GET /v1/usage, GET /v1/warehouses and the account page show until the sweep's next sample. Why it exists: the sweep lists warehouses at its start and measures each once an hour, so a load that landed after a catalog's sample read the old figure for up to an hour (2026-09-20: 1,818 bytes for 63 minutes after a 2.9 GB load). Same objects as the sweep counts, two differences stated in `note`: taken before the sweep's snapshot expiry and compaction, and a dropped table's files count until they are purged. Quota enforcement (the bucket quota, the 409s) still follows the sweep's sample. At most 50 listing pages (50,000 objects) per bucket: past that partial: true and bytes is a lower bound. Once a minute per catalog. Audited as warehouse.measure. Auth: Authorization: Bearer — read level on the catalog Request: the path holds the warehouse_id or the catalog's name; no body Response: name, warehouse_id, measured_at, stale_after (measured_at + the sweep interval), source: on_demand, bytes, table_bytes, blob_bytes, objects, blob_objects, complete, partial, pages, page_cap, blob_bucket: listed|unprovisioned, seconds, tier, storage{current, limit, headroom, measured_at, stale_after, over_limit, …}, public_storage{…}, bucket_quotas{…}, note Errors: 404 warehouse_not_found (not yours; a member token: no grant on it), 409 ambiguous_name, 429 rate_limited (retry_after_seconds; Retry-After header: measured less than a minute ago -- read the figure from GET /v1/usage), 502 storage_error (the listing was refused; safe to retry at once, no rate-limit mark is set) CLI: lhbox catalog measure ## Tables, credentials and connection recipes ### POST /v1/tables → 201 Create an Iceberg table through the catalog, as the warehouse's own identity. The namespace is created on demand. Engines can also create tables directly (DuckDB CREATE TABLE, PyIceberg create_table); this route exists for a schema with field ids, identifier fields and a chosen format version. Auth: Authorization: Bearer Request: {"warehouse_id": , "namespace": …, "name": …, "schema": , "format_version": 3} (optional: 2 or 3; WITHOUT it the catalog's default_format_version applies, 2 unless changed with PATCH /v1/warehouses/{id}; geometry/geography columns need 3; GET /v1/config/formats says which engines write each) Header Idempotency-Key Response: table (ns.name), warehouse_id, warehouse, namespace, name, table_uuid, location, format_version (the real one), format_version_requested, format_version_source (request | catalog_default), format_version_honoured, writers[], readers[], note (who writes and who only reads that version, from GET /v1/config/formats); for v3 also warnings[] and v3_columns[{name, type}] when the schema has geometry/geography columns Errors: 400 missing_field, 400 invalid_value (format_version not 2 or 3: versions{} says who writes each; or a geometry/geography column with an effective format version of 2: remedy says --format-version 3, catalog update --default-format-version 3, or WKB in a binary column), 404 warehouse_not_found, 409 quota_exceeded (over the storage limit), 4xx/502 catalog_error (catalog_status, catalog_type, catalog_message) CLI: lhbox table create --catalog … --namespace … --name … --column name:type … ### GET /v1/namespaces → 200 Namespaces in a catalog, read from the catalog service. Auth: Authorization: Bearer Request: ?warehouse= (warehouse or warehouse_id: both spellings name the catalog, by id or by name, on every route below) Response: warehouse, warehouse_id, namespaces[{namespace}] CLI: lhbox namespace list --catalog … ### GET /v1/tables → 200 Every table in a catalog, or in one namespace. Auth: Authorization: Bearer Request: ?warehouse=&namespace= (or warehouse_id=) Response: warehouse, warehouse_id, namespaces_searched[], tables[{namespace, name, table}] Errors: 404 namespace_not_found (available[]) CLI: lhbox table list --catalog … [--namespace …] ### GET /v1/table → 200 Schema, the real format version and counts, read from the table's current metadata. rows is NET of delete files: the snapshot summary's total-records minus its position and equality delete counts when the writer left them, else counted from the manifest list and the delete manifests (rows_source says which path). Position deletes remove one row each, so the net is exact; with equality deletes (predicates) rows_is_exact is false and rows_note says why. identifier_field_ids are not a primary key: the catalog accepts duplicate rows. Auth: Authorization: Bearer Request: ?warehouse=&namespace=…&name=… (or warehouse_id=) Response: table, namespace, name, warehouse, warehouse_id, table_uuid, location, format_version, schema[{id, name, type, required}], identifier_field_ids[], partition_spec, snapshots, current_snapshot_id, rows, data_files, delete_files, rows_source (snapshot_summary | snapshot_summary_net_of_deletes | manifest_list | manifests | manifests_net_of_deletes | no_snapshot | no_manifest_list), rows_is_exact; when delete files exist also rows_gross, position_delete_rows, equality_delete_rows and, if inexact, rows_note; writers[], readers[], note (who writes and who only reads the version the table has, from GET /v1/config/formats); warnings[] for v3; properties{} (the table's Iceberg properties: format-version, geo.* for an imported GeoParquet, whatever PATCH /v1/table set) Errors: 404 namespace_not_found, 404 table_not_found (available[]) CLI: lhbox table get --catalog … --namespace … --name … ### PATCH /v1/table → 200 Set and/or remove table properties: ONE commit through the catalog (Iceberg REST commitTable with set-properties / remove-properties, as the catalog's own identity), visible to every engine on its next load. Write holders only; audited table.update with the previous values. `lhbox table import` uses it to keep a GeoParquet's `geo` metadata as geo.encoding, geo.crs, geo.columns, geo.primary_column (the convention in docs/engines). The format version is not a property this route changes. Auth: Authorization: Bearer Request: {"warehouse_id": , "namespace": …, "name": …, "properties": {"geo.crs": "EPSG:4326", …}, "remove": ["key", …]} (properties and/or remove; values are strings -- numbers and booleans are stringified, nested objects refused; keys 1-128 chars of letters, digits, ".", "_", "-") Response: table (ns.name), namespace, name, warehouse, warehouse_id, properties{} (after the commit), set[] (keys set), removed[] (keys asked to be removed), metadata_location Errors: 400 missing_field (neither properties nor remove, or both empty), 400 invalid_value (a bad key, a nested value, a value over 32768 characters, or format-version: reserved[] says which keys are), 403 forbidden (read level), 404 namespace_not_found, 404 table_not_found, 4xx/502 catalog_error (the catalog's own refusal, relayed: catalog_status, catalog_type, catalog_message) CLI: lhbox table set-properties --catalog … --namespace … --name … --property key=value … [--remove key …] ### POST /v1/credentials → 200 Short-lived storage credentials scoped to ONE table's object prefix, vended by the catalog to the warehouse's read/write identity. For direct S3 access from a script; engines that load tables through the catalog obtain these themselves. Write holders only: a vended session is read/write whoever asks for it (measured, docs/security.md), so a read holder is refused and pointed at the static read-only credential in GET /v1/connection. Auth: Authorization: Bearer Request: {"warehouse_id": , "namespace": …, "name": …} ("warehouse" is accepted for the same field) Response: table, location, scope, level (write), identity (t-), storage{s3.access-key-id, s3.secret-access-key, s3.session-token, s3.endpoint, s3.session-token-expires-at-ms and the other s3.* keys the catalog vends}, expires_at, notes[] (only when the endpoint had to be rewritten for your network) Errors: 403 forbidden (level read or none; remedy names the grant command or /v1/connection), 404 namespace_not_found, 404 table_not_found, 409 quota_exceeded (over the storage limit: no new write credential; reading through the catalog credential still works), 502 catalog_error CLI: lhbox credentials --catalog … --namespace … --table … ### GET /v1/connection → 200 A paste-ready recipe for your engine, with the catalog credential of the identity matching your level inside: write holders get t- (reads and writes), read holders get t--ro (the catalog refuses its commits, the store its PUT/DELETE). The snowflake recipe is always the read-only identity (static keys, ALLOW_WRITES = FALSE). LakehouseBox is never in the query path: the engine talks to the catalog and the object store directly. Auth: Authorization: Bearer Request: ?warehouse_id=&engine=duckdb (or warehouse=; duckdb default, pyiceberg, spark, snowflake) Response: engine, catalog_uri, warehouse (s3:///), warehouse_name (the table bucket: --, or w- on older catalogs), level (read|write), level_via, identity (t- | t--ro), catalog_credential{client_id, client_secret, oauth2_server_uri, grant: client_credentials, note}, storage_endpoint, blob_bucket (----blobs), blob_note, blob_example_python, token_endpoint, token_exchange{…}, warehouse_id, project_id, format_version_hint{default (the catalog's default_format_version), note, versions{"2", "3": {writers, readers, note}}} (always present; the note says the catalog prefers 3 when it does), notes[] (the first says the level), and per engine: sql (duckdb, snowflake) | python (pyiceberg) | conf{spark.sql.catalog..*} (spark); catalog_alias (duckdb, spark: the catalog's name, else lake) -- two catalogs' recipes paste into one session without colliding Errors: 400 unsupported_engine (available[]), 403 forbidden (no grant on the warehouse; can_grant[] names who can), 404 warehouse_not_found, 503 provisioning + Retry-After: 30 (the catalog's storage has not accepted a write yet: status, retry_after_seconds, remedy; retry after it -- the control plane probes again every 30 s for 10 min) CLI: lhbox connect --catalog … --engine duckdb|pyiceberg|spark|snowflake ### GET /v1/config/formats → 200 Which Iceberg format versions a table here can have, the default for LakehouseBox's own create paths, and for each version the engines that write it and those that only read it -- the ONE list (FORMAT_WRITERS in the control plane) every other sentence about format versions is rendered from. Public, like /v1/auth/config: a CLI, an agent or a page reads this instead of carrying its own list. Version strings are machine-parseable, `[>=[+-]]` (duckdb>=1.5.5, spark>=3.5+iceberg-1.11, pyiceberg>=0.12, spark); the prose is in note. A catalog's own default is default_format_version on GET /v1/warehouses/{id}. Auth: none Response: default (2), versions{"2": {writers[], readers[], note}, "3": {writers[], readers[], note}}, v3_only_types[] (geometry, geography), note ## Organisations ### GET /v1/orgs → 200 The organisations you belong to and your role in each; for a token, the organisation that owns it and the token's role. Auth: Authorization: Bearer Response: organizations[{org_id, name, handle, status, role (admin|member), domain_join, domains[{domain, verified_via}], members, created_at}] CLI: lhbox org list ### PATCH /v1/orgs/{org_id} → 200 Rename the organisation, or let people whose verified email is on a claimed domain join as members. Auth: Authorization: Bearer — admin of the organisation Request: {"name"?, "domain_join"?: true|false} Response: the organisation as in GET /v1/orgs Errors: 403 insufficient_role, 400 no_domain (claim a domain first) CLI: lhbox org set [--name …] [--domain-join on|off] ### POST /v1/orgs/{org_id}/domains → 201 Claim an email domain. The proof is your own verified address on that domain; public mail domains (gmail.com, outlook.com, …) are never claimable; a domain belongs to one organisation. Auth: Authorization: Bearer — admin Request: {"domain": "yourcompany.com"} Response: the organisation Errors: 400 public_mail_domain, 403 domain_not_yours, 409 domain_claimed CLI: lhbox org domain --domain … ### DELETE /v1/orgs/{org_id}/domains/{domain} → 200 Release a claimed domain. Members who joined through it stay; nobody new joins through it, and domain join switches itself off when no domain is left. Auth: Authorization: Bearer — admin Response: the organisation, plus removed: Errors: 404 domain_not_found ### GET /v1/orgs/{org_id}/members → 200 Who is in the organisation. Auth: Authorization: Bearer — any member Response: org_id, members[{principal_id, role, created_at, display_name, email, kind}] CLI: lhbox org members ### PATCH /v1/orgs/{org_id}/members/{member_id} → 200 Change a member's role. Two roles, admin and member ("owner" is accepted as an alias for admin for one version and never returned). An organisation always keeps at least one admin. Auth: Authorization: Bearer — admin Request: {"role": "admin"|"member"} Response: org_id, principal_id, role Errors: 404 member_not_found, 409 last_admin CLI: lhbox org role --principal … --role admin|member ### DELETE /v1/orgs/{org_id}/members/{member_id} → 200 Remove a member (admin), or leave the organisation (your own principal_id). The last admin can do neither. Auth: Authorization: Bearer — admin, or any member removing themselves Response: org_id, principal_id, removed: true, left Errors: 404 member_not_found, 403 insufficient_role, 409 last_admin ### POST /v1/orgs/{org_id}/invitations → 201 Invite an address. The token is returned once (and mailed when the deployment has a mail sender). If the address signs up later, the invitation is applied automatically. Auth: Authorization: Bearer — admin Request: {"email": …, "role": "member"|"admin"} Response: org_id, email, role, token (shown once), mailed, expires_in (7 days), accept_with, note CLI: lhbox org invite --email … --role member|admin ### POST /v1/orgs/invitations/accept → 200 Redeem an invitation. The caller's email must be the invited one. Auth: Authorization: Bearer Request: {"token": …} Response: the organisation Errors: 404 invitation_not_found, 410 invitation_expired, 403 invitation_for_someone_else CLI: lhbox org accept --token … ## Agents ### POST /v1/orgs/{org_id}/agents → 201 A token (shown as "agent"): a machine identity of the organisation with its own API key, revocable on its own, holding exactly the grants given here and an organisation role (admin or member, default member) the organisation routes evaluate exactly as a human member's -- so an agent can do everything a human can. An empty or omitted grants list means no warehouse until granted (a warehouse a member token was not granted is 404 for it; an admin token sees every warehouse with its level, and may grant itself). Admins give any grants and either role; members at most their own level on each warehouse and the member role only. Role governs the organisation, grants govern data. Tokens never count for last-admin protection. The pre-contract `warehouses`/`warehouse_ids` list is read as write grants for one more version and answered with a `deprecation` note. Auth: Authorization: Bearer — admin, or a member within their own grants and role (a token is the member or admin it was made) Request: {"name": …, "role"?: "admin"|"member", "grants"?: [{"warehouse_id": …, "level": "read"|"write"}, …]} Response: agent_id, name, org_id, role, grants[{warehouse_id, name, level}], created_by, created_by_display, created_at, last_used_at, suspended, warehouses (legacy), api_key (shown once), key_id, note Errors: 400 invalid_value (grants, role), 403 forbidden (a level above your own: your_level, requested), 403 insufficient_role (an admin token from a member), 404 warehouse_not_found (not in this organisation), 409 already_exists (name) CLI: lhbox agent create --name … [--role admin|member] [--grant :read|write …] ### GET /v1/orgs/{org_id}/agents → 200 The organisation's tokens with their role and grants, who created each and when its key was last used. Auth: Authorization: Bearer — any member Response: org_id, agents[{agent_id, name, role, grants[{warehouse_id, name, level}], created_by, created_by_display, created_at, last_used_at, suspended, warehouses (legacy), note (legacy unconfined agents)}] CLI: lhbox agent list ### GET /v1/orgs/{org_id}/agents/{agent_id} → 200 One token by id or by name: the same view as the list (grants as [{warehouse_id, name, level}] on every agent route since 2026-09-21), its connection block, creator and last use. Never the key. Auth: Authorization: Bearer — any member Response: the token as in GET /v1/orgs/{org_id}/agents Errors: 404 agent_not_found CLI: lhbox agent get ### PATCH /v1/orgs/{org_id}/agents/{agent_id} → 200 Edit a token's name, grants and/or role; the path names the agent by id or by name. The grants list replaces the current one and is capped by the editor's own grants, the role by the editor's own role (an admin is uncapped; a token never edits itself unless it is an admin). Editing a legacy unconfined agent confines it. Audited with the previous role. Auth: Authorization: Bearer — admin, or the member who created it Request: {"name"?: …, "role"?: "admin"|"member", "grants"?: [{"warehouse_id": …, "level": …}]} Response: the token as in GET Errors: 400 missing_field, 403 forbidden, 403 insufficient_role, 404 agent_not_found, 409 already_exists CLI: lhbox agent update --agent [--name …] [--role admin|member] [--grant :read|write …] ### DELETE /v1/orgs/{org_id}/agents/{agent_id} → 200 Every key of the agent is revoked and the agent suspended: it stops at once. The path names the agent by id or by name. Auth: Authorization: Bearer — admin, or the member who created it Response: agent_id, revoked: true Errors: 404 agent_not_found CLI: lhbox agent revoke --agent ## API keys, terms and account ### GET /v1/auth/config → 200 What a browser or a CLI needs to log a human in: whether the deployment serves the email + password login (password_login; the form at /app/, docs/login.md) and the external OpenID Connect issuers a self-hosted deployment trusts (AL_OIDC_ISSUERS; device-code endpoints). The account page shows the form, the "Log in with " button, or both, from this answer. Auth: none Response: issuer, password_login (true when the deployment serves POST /v1/auth/login), external_issuers[{issuer, client_id, authorization_endpoint, token_endpoint, device_authorization_endpoint, jwks_uri}], api_key_exchange ### POST /v1/api-keys → 201 Another API key for the caller (for a second machine, a CI job). Shown once. Auth: Authorization: Bearer Request: {"label": "ci"} Response: api_key, key_id, label, principal_id, note CLI: lhbox api-key create --label … ### GET /v1/api-keys → 200 The caller's keys, by id: never the secret. Auth: Authorization: Bearer Response: api_keys[{key_id, label, created_at, last_used_at, revoked_at}] CLI: lhbox api-key list ### DELETE /v1/api-keys/{key_id} → 200 Revoke one of your keys. The key id is the part between al_live_ and the second underscore. Auth: Authorization: Bearer Response: key_id, revoked: true Errors: 404 api_key_not_found CLI: lhbox api-key revoke --key-id … ### POST /v1/onboard → 200 For a caller who arrived with a key or an identity-provider token: make sure an organisation, a project and a warehouse named `lake` exist and hand back the recipe. First login without a signup: organization_name + handle name the organisation (a handle is derived from the name when absent); an address on a claimed, open domain joins that organisation instead and the response says so. Idempotent: an existing organisation and warehouse are returned, not duplicated. Auth: Authorization: Bearer Request: {"engine"?: "duckdb", "accept_terms"?: true, "organization_name"?, "handle"?, "org_id"?} Response: created (bool), warehouse_id, connection, project_id (when created), organization{org_id, name, handle, status, role, joined_via (created|domain|invitation|signup-form|member)}, terms{version, accepted, url}, note Errors: 400 terms_required (a human who has not accepted the current Terms), 400 invalid_value (handle), 409 handle_taken (suggestion) ### GET /v1/terms → 200 The Terms version in force and whether you accepted it. Auth: Authorization: Bearer Response: current_version, terms_url, privacy_url, accepted_current, history[{version, accepted_at, via}] CLI: lhbox terms ### POST /v1/terms/accept → 200 Record acceptance of the current Terms version. A person's act: a token (kind agent or mcp) is refused; Terms bind the account holder and a token needs no acceptance of its own. Auth: Authorization: Bearer Request: {"version"?: } Response: accepted: true, version Errors: 403 not_a_person (a token), 409 terms_version_mismatch CLI: lhbox terms --accept ### DELETE /v1/me → 200 Delete your account. Immediate: every API key revoked, every session revoked, identities unlinked, memberships removed, agents of organisations left empty stopped; a credential of the deleted account answers 401 account_deleted from then on. Data of organisations left without members is erased within 30 days and leaves backups within 14 more (Terms). Auth: Authorization: Bearer Request: {"confirm": ""} Response: deleted: true, principal_id, keys_revoked, organisations_left_empty[], warehouses_to_purge[], note Errors: 400 confirmation_required, 409 last_admin (make someone else an admin first) CLI: lhbox account delete --confirm ## Usage and limits ### GET /v1/usage → 200 Every limit, the current value and the headroom. Storage is metered hourly by maintenance (the latest sample of every warehouse in the scope, summed; 'unmetered' until the first sample, which the control plane takes itself ~2 minutes after a catalog is created); every storage figure carries measured_at and stale_after (when the sweep will have replaced it), and POST /v1/warehouses/{id}/measure refreshes one catalog now. The storage limit applies to the organisation, or to the project when it has none. Over the limit, warehouse and table creation and credential vending answer 409 quota_exceeded and the buckets go read-only at the next enforcement pass. Read this before provisioning; an agent should never meet a limit by failing. Per warehouse it also shows usage that is NOT billed today: the latest footprint split by bucket, one row per UTC day for the last `days` days (the day's last footprint, and the S3 requests and bytes in/out counted that day from SeaweedFS's per-bucket counters), the footprint trend over the window and the window's traffic totals. `traffic` is null with a `traffic_note` until two consecutive hourly samples carry counters; no object keys and no query contents are collected. Auth: Authorization: Bearer Request: ?project_id=&days=<1..30, default 7: the per-catalog history length> Response: principal_id, limits{storage_bytes, public_storage_bytes, objects, tables, namespaces, warehouses, projects, catalog_operations_per_month, commits_per_month, min_seconds_between_commits, snapshot_retention_days, max_snapshots_per_table}, limits_detail{: {value, enforced: true|false, enforcement}} (storage, catalogs per project, projects per account and snapshot retention are enforced; tables, namespaces, objects and commit spacing are guidelines: engines commit straight to the catalog), limits_note, history_days, projects[{project_id, name, org_id, warehouses{current, limit, headroom}, warehouse_ids[], storage_bytes{current, limit, headroom, unmetered, measured_at, stale_after, complete, scope, scope_id, warehouses_metered, warehouses, over_limit}, public_storage_bytes{…}, warehouse_storage[{warehouse_id, name, tier, public, bytes, objects, measured_at, unmetered, stale_after, source: sweep|creation|on_demand, measuring, buckets{table, blob}, table_bytes, blob_bytes, blob_objects, complete, history[{day, bytes, objects, blob_bytes, blob_objects, complete, measured_at, samples, requests, requests_failed, bytes_in, bytes_out, intervals}], trend{days, from, to, bytes_from, bytes_to, bytes_delta, objects_delta, days_measured} | null, traffic{days, requests, requests_failed, bytes_in, bytes_out, intervals, resets, since, to, source} | null, traffic_note}]}], organizations[{org_id, storage_bytes{…}, public_storage_bytes{…}, role (the caller's: a member's or a token's)}], note CLI: lhbox usage [--full] [--catalog ] [--days N] (also: lhbox whoami; both print a brief object unless --full) ### GET /v1/audit → 200 Who did what. Without org_id: your own events. With org_id: an admin sees the organisation's events plus their own; a member only their own within it. Actions include signup.start/verify/complete, recovery.start/verify, api_key.create/revoke, project.create, warehouse.create/rotate, table.create, credentials.vend, org.create/update/domain_claim/invite/invitation_accept/member_join/member_role/member_remove, org.reserve/activate, agent.create/update/revoke, warehouse.grant/grant_revoke/delete/delete_complete/rotate_complete/measure, terms.accept, account.delete, quota.reject. Auth: Authorization: Bearer Request: ?org_id=&since=<24h|7d|30m|ISO-8601>&action=&limit=<1..1000, default 100> Response: events[{id, ts, principal_id, org_id, action, target_type, target_id, ip, request_id, detail{}}], count, limit, scope (own|organization), org_id, since, action, retention_days (400) Errors: 404 org_not_found CLI: lhbox audit [--org …] [--since 24h|7d|30m|] [--action credentials.vend|org.*] [--limit N] ## Internal (not public) These exist in the control plane for the operator and for development stacks. They require the deployment's bootstrap secret and the public gateway answers 404 for every /internal/* path. Listed so the surface is complete. ### POST /internal/principals → 200 Operator: create a principal and its first key. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### POST /internal/principals/{principal_id}/suspend → 200 Operator: suspend or unsuspend a principal (the enforcement point). Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### POST /internal/api-keys/{key_id}/revoke → 200 Operator: revoke any key. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### POST /internal/selftest/error → 200 Operator tool: raise one unhandled error on purpose to verify the error path end to end. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### GET /internal/signup-codes/{email} → 200 Development stacks without a mail server: read a pending code. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### GET /internal/sinks → 200 For the roller (worker/roll.py): every active or disabled sink with its catalog's key pair, buckets, roll policy and pending ledger, plus the sinks deleted in the last 7 days whose intake prefix it must purge. Empty while AL_SINKS_ENABLED is off. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### POST /internal/sinks/{sink_id}/roll → 200 The roller reports one roll: the batch ids committed (marked committed with the snapshot id), rows, rejected rows, seconds, an optional disabled_reason (table_missing disables the sink); ledger rows committed more than 7 days ago are pruned. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### GET /internal/lifecycle-audit → 200 The table-bucket guard, observable: as the admin, GET ?lifecycle on every catalog's TABLE bucket (expected 404 -- a rule there would be executed by the store's worker against Iceberg data) and GET ?policy on it (no statement may grant a lifecycle action). Lists offenders and errors; ok when both are empty; ?include_blobs=true also counts the blob buckets carrying rules. For the nightly sweep. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### GET /internal/tls-ask → 200 Caddy's on-demand TLS gate for virtual-hosted S3 names (.s3., the shape Snowflake's S3-compatible volumes need): 200 only for a bucket of an existing warehouse -- its table or blob bucket, looked up by name in the row's bucket / blob_bucket columns, so --, …--blobs and the older w-/b- are all served -- under the configured S3 host, 404 otherwise (a deleting catalog included), so nobody can make the gateway mint certificates for arbitrary names. Answers domain, bucket, warehouse_id, kind (tables|blobs), issue. Called on the docker network; never served publicly. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### GET /internal/login-tokens/{email} → 200 Development stacks without a mail server: read a pending set-password or reset link token (purpose=set_password | reset_password). The pattern of /internal/signup-codes. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### POST /internal/usage → 200 The host's hourly maintenance run reports one warehouse's footprint (bytes, objects, blob bytes and objects); recording it recomputes the scope's storage quota. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### GET /internal/tier/plan → 200 The host's hourly tiering job (deploy/on_host_tier.sh) asks which buckets may move to the cold tier: one item per bucket of every ready tier_policy=cold catalog with the backend its tenant hashes to, plus the knobs (quiet_for, full_percent) it passes to volume.tier.upload (research/27). Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path ### POST /internal/tier/report → 200 The tiering job reports one run: per bucket the absolute cold footprint after it (cold_bytes, cold_volumes), what moved, seconds and the error text; recorded in tier_state and shown as stored.cold_bytes / cold. Auth: the deployment's bootstrap secret (X-Bootstrap-Secret). Not public: api.lakehousebox.com answers 404 for every /internal/* path # Engines LakehouseBox is an Apache Iceberg REST catalog plus S3-compatible storage. Your engine talks to both directly. The DuckDB, PyIceberg, Spark and Snowflake recipes below are what lhbox connect --engine … prints (--catalog with several catalogs), with the credential and the bucket filled in, and each carries the date it was verified against the service. The Trino and ClickHouse recipes are written from the catalog's REST contract and are marked not yet verified until a recorded run says otherwise. Catalog: https://catalog.lakehousebox.com. Storage: https://s3.lakehousebox.com. | Engine | Status | Evidence | | DuckDB 1.5.5+ | read and write, verified | every phase of the integration suite; the CLI's own table import | | PyIceberg 0.12 | read and write (format version 2), verified | integration suite phase 2; reads v3, cannot write it | | Spark 3.5 + Iceberg 1.11 | read and write, format version 3 included, verified | 2026-09-20, geometry(EPSG:28992) column by plain INSERT | | Trino | recipe from the REST contract, not yet verified | no recorded run against the service | | ClickHouse | read recipe from the REST contract, not yet verified | no recorded run against the service | | Snowflake | catalog integration connects; data path not yet | 2026-09-19 measurement; needs work on both sides | | Databricks | untested | no run, no recipe | ## DuckDB (1.5.5 or newer) — read and write ``` INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs; CREATE SECRET lhbox (TYPE ICEBERG, CLIENT_ID '', CLIENT_SECRET '', OAUTH2_SERVER_URI 'https://catalog.lakehousebox.com/v1/oauth/tokens'); CREATE SECRET lhbox_s3 (TYPE S3, KEY_ID '', SECRET '', ENDPOINT 's3.lakehousebox.com', URL_STYLE 'path', USE_SSL true, REGION 'us-east-1'); ATTACH '--' AS (TYPE ICEBERG, ENDPOINT 'https://catalog.lakehousebox.com', SECRET lhbox); -- '--' is the recipe's warehouse_name (older catalogs keep w-) -- e.g. SELECT * FROM ..
LIMIT 10; ``` The lhbox_s3 secret is not needed to read or write tables through the catalog (vended credentials cover that); it lets read_blob('s3://acme--demo-data/**') and friends inspect the bucket. The recipe attaches the catalog under its own name (catalog_alias in the API answer): demo_data for the default catalog, which the examples below assume. ### Importing files: one commit for many Parquet or CSV files ``` lhbox table import 'data/*.parquet' --namespace demo --name cities # a new table (--catalog with several) lhbox table import more/*.parquet --namespace demo --name cities --mode append # into it lhbox table import 'data/*.parquet' --namespace demo --name places --format-version 3 # geometry stays typed lhbox table import 'exports/*.csv' --namespace demo --name orders # CSV, detected by the extension (or --format csv) lhbox table import 'data/*.parquet' --python ~/venv/bin/python --namespace demo --name cities --full # that venv's DuckDB; print the SQL ``` table import runs DuckDB on your machine (the duckdb python module of --python PATH or LHBOX_PYTHON when given, else of the interpreter running lhbox, else the duckdb binary; 1.5.5 or newer) over read_parquet([every file], union_by_name=true), or read_csv when every file ends in .csv or --format csv says so, in one statement, so the whole set is one Iceberg commit; importing files one statement at a time is one commit per file (44 files were 44 commits and 44 waits). Globs expand on your side; s3:// and https:// sources are read by DuckDB's httpfs. The report says rows, files, bytes, seconds, commits (1) and the path taken (--full adds the generated SQL statement): format version 2 (the catalog's default unless changed) is DuckDB's own CREATE TABLE … AS SELECT; format version 3 (--format-version 3, or the catalog's default) is created through POST /v1/tables with the schema DuckDB inferred, geometry columns typed, then filled with one INSERT. Measured 2026-09-21: three Parquet files, 3000 rows, one commit, 3.96 s from a laptop; append 1.21 s. The same by hand, in any DuckDB session with the recipe pasted: ``` CREATE SCHEMA IF NOT EXISTS demo_data.demo; -- CTAS needs the schema; the API creates it on demand CREATE TABLE demo_data.demo.cities AS SELECT * FROM read_parquet(['a.parquet', 'b.parquet'], union_by_name=true); INSERT INTO demo_data.demo.cities SELECT * FROM read_parquet('more-cities.parquet'); -- into a table that exists COPY demo_data.demo.cities FROM 'more-cities.parquet' (FORMAT PARQUET); CREATE TABLE demo_data.demo.orders AS SELECT * FROM read_csv(['a.csv', 'b.csv'], union_by_name=true); -- CSV: the list in ONE statement -- CREATE OR REPLACE TABLE is not supported on an Iceberg catalog: DROP TABLE demo_data.demo.orders; then CREATE TABLE again ``` ### Geometry columns: create the table first, then INSERT ``` lhbox table create --catalog demo_data --namespace demo --name places --format-version 3 --column id:long:required:identifier --column name:string --column geom:geometry -- then, in DuckDB with the recipe pasted (INSTALL spatial; LOAD spatial;): INSERT INTO demo_data.demo.places SELECT id, name, ST_Point(lon, lat) FROM read_csv('places.csv'); SELECT id, ST_AsText(geom) FROM demo_data.demo.places LIMIT 3; -- POINT (…) ``` A geometry column needs an Iceberg format-version 3 table, and DuckDB's own CREATE TABLE … AS SELECT ST_Point(…) cannot make one: DuckDB creates a version-2 table whatever the catalog's default_format_version, and the catalog answers the CTAS with a 400 that names no cause. Known limitation; the two-step path is the way: lhbox table create --format-version 3 --column geom:geometry (geometry(EPSG:xxxx) for a CRS) and then a plain INSERT … SELECT from DuckDB, measured at 0.8 s for a small table and read back as POINT. lhbox table import … --format-version 3 does exactly this for a GeoParquet. Measured against the service: a million rows (18 MB of Parquet) in about 2.3 s from a laptop, three data files, one commit; format-version 2. ### GeoParquet: the geo metadata becomes table properties Until Iceberg v3 geometry is readable by every engine, the geo metadata of an imported GeoParquet lives in the table's properties, and DuckDB spatial can rebuild the GeoParquet from them on export. table import reads the file's geo key (parquet_kv_metadata) and sets, through PATCH /v1/table: geo.encoding (WKB), geo.primary_column, geo.crs (EPSG:xxxx when the PROJJSON carries an authority id, else the PROJJSON text; OGC:CRS84 when the file names none), geo.columns (the columns object as JSON: encoding, geometry_types, bbox -- merged across files), geo.version, and geo.crs.projjson when there was a PROJJSON. Into a format-version 2 table the geometry column is written as WKB binary (what geo.encoding says); into a format-version 3 table it stays a typed geometry() column and the properties are the same. lhbox table get shows them; lhbox table set-properties --property geo.crs=EPSG:28992 sets or corrects any of them. ## PyIceberg (0.12) — read and write ``` from pyiceberg.catalog.rest import RestCatalog catalog = RestCatalog(name='lhbox', uri='https://catalog.lakehousebox.com', warehouse='s3://--/', credential=':', **{'s3.endpoint': 'https://s3.lakehousebox.com', 's3.path-style-access': 'true'}) table = catalog.load_table(('', '
')) # writing: create from an Arrow schema, then append import pyarrow.parquet as pq arrow = pq.read_table('cities.parquet') tbl = catalog.create_table(('demo', 'cities'), schema=arrow.schema) tbl.append(arrow) ``` PyIceberg writes one data file per append and a complete snapshot summary; DuckDB splits large writes into several files. Both are read identically by either engine. ## Spark (3.5 + Iceberg 1.11) — read and write, including format-version 3 ``` spark.sql.catalog.=org.apache.iceberg.spark.SparkCatalog spark.sql.catalog..type=rest spark.sql.catalog..uri=https://catalog.lakehousebox.com spark.sql.catalog..warehouse=s3://--/ spark.sql.catalog..credential=: spark.sql.catalog..io-impl=org.apache.iceberg.aws.s3.S3FileIO spark.sql.catalog..s3.endpoint=https://s3.lakehousebox.com spark.sql.catalog..s3.path-style-access=true spark.sql.catalog..client.region=us-east-1 spark.sql.catalog..header.X-Iceberg-Access-Delegation=vended-credentials ``` Pass these as --conf flags or in spark-defaults.conf; the catalog is then addressed by its name in Spark SQL (SELECT * FROM demo_data.demo.cities for the catalog demo_data). Jars: iceberg-spark-runtime-3.5_2.12 and iceberg-aws-bundle, both 1.11. Java 17 or newer: the Iceberg 1.11 runtime is compiled for Java 17 (class file 61.0) and dies with UnsupportedClassVersionError on the Java 11 that the apache/spark:3.5 images ship; 1.9.2 was the last Java 11 line. client.region=us-east-1 is the SigV4 region the store accepts, not a placement (the data stays in Nuremberg); without it the AWS SDK looks for a region on the JVM and fails before the first request. ## Trino — recipe from the REST contract, not yet verified ``` # etc/catalog/.properties connector.name=iceberg iceberg.catalog.type=rest iceberg.rest-catalog.uri=https://catalog.lakehousebox.com iceberg.rest-catalog.warehouse=s3://--/ iceberg.rest-catalog.security=OAUTH2 iceberg.rest-catalog.oauth2.credential=: iceberg.rest-catalog.oauth2.server-uri=https://catalog.lakehousebox.com/v1/oauth/tokens iceberg.rest-catalog.vended-credentials-enabled=true fs.native-s3.enabled=true s3.endpoint=https://s3.lakehousebox.com s3.region=us-east-1 s3.path-style-access=true ``` Trino's Iceberg connector against a REST catalog with OAuth2 client credentials (iceberg.rest-catalog.oauth2.credential, …oauth2.server-uri) and vended credentials for the data path; s3.region=us-east-1 is the SigV4 region the store accepts. Not yet verified against the service: written from the catalog's contract (the token endpoint, the s3:/// warehouse, path-style S3) and Trino's documented properties, no recorded run. If your run works or fails, tell us and this line changes. ## ClickHouse — read recipe from the REST contract, not yet verified ``` SET allow_experimental_database_iceberg = 1; CREATE DATABASE ENGINE = DataLakeCatalog('https://catalog.lakehousebox.com', '', '') SETTINGS catalog_type = 'rest', warehouse = 's3://--/', catalog_credential = ':', oauth_server_uri = 'https://catalog.lakehousebox.com/v1/oauth/tokens', storage_endpoint = 'https://s3.lakehousebox.com/--'; SHOW TABLES FROM ; SELECT count(*) FROM .`.
`; -- one namespace level: backticks around namespace.table ``` ClickHouse's DataLakeCatalog database engine reads Iceberg tables through a REST catalog; it is experimental in ClickHouse (allow_experimental_database_iceberg) and reads only. ClickHouse has one namespace level, so a table is addressed as `.
` in backticks. Not yet verified against the service: written from the catalog's contract and ClickHouse's documented settings, no recorded run. ## Snowflake — data path not yet The catalog integration connects (lhbox connect --engine snowflake prints it with the read-only identity, ALLOW_WRITES = FALSE). The data path is not available yet: Snowflake's S3-compatible external volumes need the endpoint allowed by Snowflake Support per account, and reading data needs work on both sides. On the roadmap; measured 2026-09-19. ## Databricks — untested No run against the service and no recipe. A Databricks cluster with the Iceberg REST catalog libraries should be able to use the Spark settings above; nothing here says that it does until a recorded run exists. ## Gotchas, all of them measured - DuckDB: ATTACH the bare bucket name. ATTACH 'acme--demo-data' is read-write. ATTACH 's3://acme--demo-data/' attaches READ-ONLY and every INSERT fails with "attached in read-only mode". PyIceberg and Spark want the s3://acme--demo-data/ form for warehouse, so copying one recipe's identifier into the other engine produces a false "DuckDB cannot write Iceberg". - DuckDB 1.5.5 or newer. 1.5.0 writes manifest lists the catalog's maintenance cannot read, so tables it wrote were neither compacted nor expired by the scheduler. 1.5.5 writes the spec-compliant schema. - Format version 2 by default; DuckDB 1.5.5+ also writes version 3. Every table has its own format version; lhbox table create without --format-version takes the catalog's default_format_version (2 unless you set it: lhbox catalog create --default-format-version 3 or catalog update --default-format-version 3; shown in catalog list and in the recipe's format_version_hint). An engine that creates a table itself (DuckDB CREATE TABLE … AS) chooses its own version, v2 today, and is never upgraded silently. Version 3 is needed for geometry/geography; it is written by DuckDB 1.5.5 or newer and by Spark 3.5 + Iceberg 1.11 (verified 2026-09-20 with a geometry(EPSG:28992) column loaded by a plain INSERT, CRS kept in the Parquet logical type). PyIceberg 0.12 reads v3 but cannot write it, and cannot load a geometry column with a non-default CRS. Who writes and who only reads each version: GET /v1/config/formats (no auth), echoed as writers/readers by table create and table get. - The catalog only accepts its own tokens. Engines mint them from the catalog credential (catalog_credential in the recipe) at https://catalog.lakehousebox.com/v1/oauth/tokens (client_credentials; client_id = access key, client_secret = secret key) and refresh them themselves; they expire after 900 s. LakehouseBox API keys (al_live_…) and tokens from POST /v1/tokens are 401 at the catalog by design. - PyIceberg asks for vended credentials on every request. That is the intended path: the catalog returns per-table storage credentials which override any static s3.* key you pass. If a cross-bucket copy fails with ACCESS_DENIED, pass "header.X-Iceberg-Access-Delegation": "none" to use your own keys instead. - Table buckets accept only Iceberg files. Data files must be .parquet/.orc/.avro/.lance and metadata must look like Iceberg metadata, under /
/(data|metadata)/; anything else is refused on write. Put photos, documents and other blobs in the catalog's blob bucket ----blobs (same credential, S3 path-style, region us-east-1; lhbox connect prints a boto3 example) and index them in a table. - CTAS makes every column optional. CREATE TABLE … AS SELECT from DuckDB cannot express identifier fields, required columns or partitioning. When you need them, lhbox table create --column name:type[:required][:identifier] first, then INSERT. - Maintenance is on and not disableable. Compaction (128 MB target files), snapshot expiry (20 snapshots, 7 days) and orphan cleanup run on every catalog. A client that hard-codes vN.metadata.json file names will 404 after a maintenance commit; list the prefix or go through the catalog. - Commits are optimistic: retry on 409. Iceberg writers commit against the table's current snapshot; when another writer or the platform's maintenance committed in between, the catalog answers 409 and DuckDB raises CommitFailedException … branch "main" has changed. DuckDB does not retry. An unattended job should catch that error, wait a few seconds and re-run the statement (measured 2026-09-20: two concurrent appenders with a client-side retry landed every row, no duplicates). Maintenance can commit into a table within seconds of its creation; a short blackout of the catalog after such a commit was also observed and is being fixed. - CREATE OR REPLACE TABLE is not supported. DuckDB's iceberg extension refuses CREATE OR REPLACE TABLE on an attached Iceberg catalog. To redo a table, DROP TABLE demo_data.demo.cities; then CREATE TABLE … again (two commits, the second into a fresh table). - TIMESTAMP WITH TIME ZONE values need pytz in Python. now() and every TIMESTAMP WITH TIME ZONE column come back through the Python client (duckdb.sql(…).fetchall(), .df()) only when the pytz module is installed, which a bare venv lacks: pip install pytz. Or print inside DuckDB with .show(), cast (now()::VARCHAR), or leave the column out of the SELECT. The duckdb binary and lhbox duckdb are not affected. - Rotation. lhbox catalog rotate replaces both catalog credentials: the old read/write key and every catalog token from it are refused at once; the old read-only key is deleted in the background right after the answer (about 10 s; warehouse.rotate_complete in the audit log records it); storage sessions already vended run out within 900 s. Fetch a fresh recipe afterwards. ## Direct S3 access from a script lhbox credentials --catalog demo_data --namespace demo --table cities (POST /v1/credentials) returns storage credentials scoped to that table's object prefix, with their expiry, for a boto3 or aws-cli session that needs the files themselves rather than the table. ## MCP server # Agent setup Written for the agent, not the person: paste https://lakehousebox.com/docs/agent-setup/ into your agent, or use the prompt on the agents page (https://lakehousebox.com/agents). The person's part is to approve the connection once in the browser. Source: the repository note docs/agent-setup.md. ## 0. Quick path You are an agent setting up LakehouseBox for a person. Credentials never appear in the conversation: the CLI saves the key it receives to ~/.config/lhbox/credentials.json and every command reads it from there. LakehouseBox is a managed Apache Iceberg lakehouse operated in Europe: one catalog per organisation to start (an Iceberg REST catalog with its own storage; organisation -> catalog -> namespace -> table), DuckDB as the engine you run locally. The default catalog is named demo_data. ``` curl -fsSL https://lakehousebox.com/install.sh | sh # one Python 3 file, no dependencies lhbox login # prints a link and a code; the person approves once in the browser lhbox doctor # PASS/FAIL per check; exit 1 on FAIL pip install 'duckdb>=1.5.5' # writes need 1.5.5 or newer; doctor names which interpreter to upgrade lhbox duckdb # the DuckDB shell with the catalog attached (the recipe is never on screen) ``` In DuckDB (the catalog is attached under its own name): ``` SHOW ALL TABLES; CREATE SCHEMA IF NOT EXISTS demo_data.raw; CREATE TABLE demo_data.raw.trips AS SELECT * FROM read_csv('data/*.csv'); -- one statement for all the files = one commit SELECT count(*) FROM demo_data.raw.trips; DROP TABLE demo_data.analysis.daily; -- when redoing a table: CREATE OR REPLACE is not supported CREATE TABLE demo_data.analysis.daily AS SELECT ...; ``` From Python instead of the shell: lhbox --json connect --engine duckdb returns the recipe; its sql field is the ATTACH block. Files from the CLI in one commit: lhbox table import 'data/*.parquet' --namespace raw --name trips (CSV too, by extension or --format csv). If login cannot block: lhbox login --no-wait, send the link, later lhbox login --resume. Several catalogs: lhbox catalog list, then --catalog on connect, duckdb and table …. What to say to the person: "Open this link and approve the connection in your browser" (repeat the code). No account yet: the same page creates one. Slow operations are normal: catalog create and catalog rotate take about a minute (four identity writes of ~10 s each on the store today) and print progress while they run; a catalog may answer provisioning for a while: retry, do not conclude "cannot write" (section 8). ## 1. Install the CLI ``` curl -fsSL https://lakehousebox.com/install.sh | sh # a single Python 3 file, no dependencies lhbox --version ``` Or, if the person prefers a package manager: pipx install lakehousebox or pip install --user lhbox. The CLI needs Python 3.10 or newer, which macOS and every current Linux ship. API: https://api.lakehousebox.com. Site and docs: https://lakehousebox.com. The API calls a catalog a warehouse (/v1/warehouses, warehouse_id); the CLI says catalog and accepts warehouse too. ## 2. Connect this machine ``` lhbox login ``` What happens: - The CLI prints a link (https://lakehousebox.com/app/device?user_code=XXXX-XXXX) and the code, opens the browser when there is one, and waits (up to 15 minutes, the code's life; every 60 s it prints still waiting for approval at , min left on stderr). If you cannot block that long, lhbox login --no-wait prints the link and the code and exits 0 at once, keeping the pending login in the credentials file; later, lhbox login --resume polls it once (exit 0 connected, exit 3 still pending, with the minutes left) and lhbox login --resume --wait waits for the decision. The code lives 15 minutes either way. - Tell the person: "Open this link and approve the connection in your browser" (repeat the code in case the link does not open). If they have no account yet, the same page offers Create your account: they fill in their name, email and organisation, click the link in the mail, choose a password, and land back on the approval with the code intact. Nothing else is needed from them. - The person sees a consent screen: this machine's name, the catalog, and the access asked for (read and write by default; they may lower it to read only). They approve. - The CLI receives the key of a new agent of their organisation, named after this machine and holding exactly the approved access, saves it, and prints: Connected to demo_data (read and write) as . Saved to ~/.config/lhbox/credentials.json. The key is never shown. Do not ask for it, do not look for it, do not cat the credentials file. Every lhbox command and the connection recipe below read it themselves. If the person wants to see or remove the connection later: their account page, Connections & tokens; revoking it stops this machine at once. Running lhbox login again on a machine whose saved key still works answers Still connected … and starts nothing new. --level read asks for read only; --name names the agent; --new forces a fresh authorisation. If the code expired before the person approved (15 minutes), run lhbox login again (or --no-wait again) and send the new link. ## 3. Check the path, then get the DuckDB recipe ``` lhbox doctor # API, credential, catalog, recipe and level, a read through the catalog, # write-policy (the bucket accepts the identity; no object written), the DuckDB here: # PASS/FAIL/SKIP, exit 1 on FAIL. `--write-probe` adds the real thing: a 0-byte # .parquet written and deleted as the recipe's identity, the store's error quoted on FAIL lhbox whoami # who you are, the organisation, your level on each catalog, limits (with `enforced`), # the next step -- a short JSON object when piped; --full for the whole document lhbox connect --engine duckdb # a paste-ready DuckDB recipe; the only catalog is the default (--catalog with several) # `lhbox connect --no-version-check` skips the DuckDB version check (either position works) lhbox duckdb # or: the duckdb shell already attached (the recipe goes in through a private # init file, never on screen); `lhbox duckdb -- -c "SELECT ..."` runs one statement ``` doctor fails loudly on the one thing the rest of this page cannot fix (the credential, the grant, the DuckDB version) before you paste anything. Its write-policy line says the bucket accepts the identity and writes nothing; only lhbox doctor --write-probe proves the store takes a write (it PUTs and DELETEs a 0-byte _lhbox/_probe/data/probe.parquet as the recipe's identity and quotes the store's error verbatim when it refuses). The DuckDB line checks the interpreter running lhbox first, then python3 on PATH, then the duckdb binary, and names which one to upgrade and how. connect prints the SQL to attach the catalog: its REST endpoint, the storage endpoint, and a short-lived credential it fetched for you; it warns on stderr when the DuckDB on this machine is older than 1.5.5. Run that SQL in DuckDB (pip install 'duckdb>=1.5.5', or the duckdb binary). Do not copy the credential anywhere else; re-run lhbox connect when it expires. lhbox catalog list shows every catalog's name, id and status when you need to pick one, lhbox catalog get one catalog with its storage figure; if the person needs a new one, lhbox catalog create (about a minute; section 8). When stdout is not a terminal, connect prints a JSON object and the SQL is its sql field; lhbox connect --human prints the SQL alone. Two ways to keep the credential out of the conversation altogether: lhbox duckdb opens the duckdb binary with the catalog attached (the recipe travels through a 0600 init file that is removed the moment duckdb has read it), and lhbox duckdb --persist writes two persistent DuckDB secrets (lhbox_, lhbox__s3) so that any later session -- the shell, or import duckdb in python -- needs only the one ATTACH line it prints. The trade-off of --persist: the catalog credential then lives on disk in DuckDB's secret directory (~/.duckdb/stored_secrets, 0600), readable by any DuckDB this user runs; lhbox catalog rotate or DROP PERSISTENT SECRET lhbox_ revokes it. ## 4. Check that DuckDB can read and write Attach with the bare bucket name the recipe gives (ATTACH '--' AS demo_data (TYPE iceberg, …); always paste the recipe's warehouse_name), never s3://--/ -- the s3:// form attaches read-only in DuckDB and every INSERT then fails with "attached in read-only mode". The recipe attaches the catalog under its own name (demo_data for the default catalog; the examples below use it), so two catalogs paste into one session. Then: ``` SHOW ALL TABLES; -- one SELECT against the catalog CREATE SCHEMA IF NOT EXISTS demo_data.scratch; CREATE TABLE demo_data.scratch.setup_check AS SELECT 1 AS ok, now() AS at; SELECT * FROM demo_data.scratch.setup_check; -- one INSERT read back -- optional: DROP TABLE demo_data.scratch.setup_check; if your rules forbid deleting, leave it and tell the person ``` If the CREATE fails with a 403 or AccessDenied, the connection is read only (the person lowered it, or holds read themselves). Say so; a person with write can grant it from the account page. Two DuckDB gotchas on this check (the full list is section 7): CREATE OR REPLACE TABLE is not supported on an Iceberg catalog, so redo a table with DROP TABLE then CREATE TABLE; and the now() column is a TIMESTAMP WITH TIME ZONE, which fetching into Python needs pytz for (pip install pytz; or .show(), now()::VARCHAR, or leave now() out). Then the same check from the CLI side, creating a table from files in one commit: ``` lhbox table import 'data/*.parquet' --namespace scratch --name import_check # many files, ONE statement, ONE commit lhbox table get --catalog demo_data --namespace scratch --name import_check # snapshots: 1, rows: the total ``` table import runs DuckDB on this machine (the duckdb python module of the interpreter running lhbox if importable, else the duckdb binary; --python PATH or LHBOX_PYTHON names another interpreter, for instance the venv where you installed duckdb>=1.5.5) over read_parquet([every file], union_by_name=true) -- or read_csv for CSV files, detected by extension or forced with --format csv -- and reports rows, files, bytes, seconds, commits (1) and which path it took; --full adds the generated SQL statement. Use table import (or one statement over the whole list) for more than one file: one statement per file is one commit per file (44 files were 44 commits and 44 waits, 2026-09-20). ## 5. The first task Do what the person asked. The three common starts: - Use their files. CSV, by hand in DuckDB with the recipe pasted: one statement over the file list, never one per file. ``` CREATE SCHEMA IF NOT EXISTS demo_data.sales; CREATE TABLE demo_data.sales.orders AS SELECT * FROM read_csv(['orders-2025.csv', 'orders-2026.csv'], union_by_name=true); -- to redo it: DROP TABLE demo_data.sales.orders; then CREATE TABLE again (CREATE OR REPLACE is not supported) ``` The same from the CLI, CSV or Parquet, one commit for the whole set: lhbox table import 'path/*.csv' --namespace --name (CSV by extension, or --format csv), lhbox table import 'path/*.parquet' … for Parquet; --mode append adds to a table that exists; a GeoParquet keeps its geo metadata as geo.* table properties. For anything else DuckDB reads (read_json, a database attached next to the catalog), the same one-statement shape. Iceberg tables, partitioning optional. Then answer their question with SQL. - Try an example dataset: DuckDB reads public Parquet over HTTPS; lhbox table import https://…/file.parquet … loads one into a table; show a few queries. - Continue an existing project: SHOW ALL TABLES, describe what is there, and pick up from it. - A producer with no engine (a device, a webhook, a job posting events): lhbox sink create --table .
--name --save gives it one URL and a send-only key; it POSTs JSON arrays and the platform commits them on a rolling policy (https://lakehousebox.com/docs/ingest/). The table must exist first (lhbox table create), unpartitioned. lhbox table list and lhbox table get show tables, schemas, row counts and properties from the API side; lhbox usage shows the free tier (5 GB), the headroom and which limits are enforced -- like whoami, a short JSON object when piped (--full for the API's whole document, --human for tables). lhbox catalog measure measures a catalog's storage now instead of at the hourly sweep. A geometry column needs a format-version 3 table created first (section 7). ## 6. Reference: flags and environment - Global flags -- --json, --human, --url, --api-key -- work before or after the subcommand: lhbox --json whoami and lhbox whoami --json are the same command. Per-command flags go after their command. Output is JSON whenever stdout is not a terminal (--json forces it, --human forces tables). - Catalog names are positional: lhbox catalog create , lhbox catalog get , lhbox catalog rotate , lhbox catalog publish --confirm. --catalog (and the older --name) still work as aliases; a UUID works wherever a name does; the destructive commands (catalog delete, catalog publish) want --confirm (or --yes). Commands that act inside a catalog (connect, duckdb, table …) take --catalog and default to the only catalog. - Environment: | variable | meaning | default | | LHBOX_API_KEY | an API key; overrides the credentials file (--api-key overrides both) | none: the file is used | | LHBOX_CREDENTIALS | the path of the credentials file; a new path starts empty, and the legacy ~/.config/agentlake/credentials.json is read only when this is unset | ~/.config/lhbox/credentials.json (0600) | | LHBOX_URL | the API (a self-hosted or local stack) | https://api.lakehousebox.com | | LHBOX_PYTHON | the interpreter whose duckdb module table import uses (--python PATH per run) | the interpreter running lhbox, else the duckdb binary | You never need to read any of these; the CLI does. - Every command's own flags: lhbox --help, and the CLI page of the docs, https://lakehousebox.com/docs/cli/, rendered from the same --help. ## 7. Gotchas by engine DuckDB (1.5.5 or newer to write; reads work on any 1.5) - ATTACH the bare bucket name from the recipe ('--'; catalogs created before 2026-09-20 keep w-, so always paste the recipe's warehouse_name), never s3://…/: the s3:// form attaches read-only and every INSERT fails with "attached in read-only mode". - 1.5.5 or newer to write. 1.5.0–1.5.4 write manifest lists the catalog's maintenance cannot read, so tables they write are never compacted or expired. doctor, connect and table import check. - CREATE OR REPLACE TABLE is not supported on an Iceberg catalog: DROP TABLE then CREATE TABLE. - A CTAS with a geometry column is refused by the catalog today (CREATE TABLE … AS SELECT ST_Point(…): DuckDB creates a format-version 2 table whatever the catalog's default, and the catalog answers a 400 that names no cause). Create the table first, then insert: lhbox table create --catalog demo_data --namespace demo --name places --format-version 3 --column id:long:required:identifier --column geom:geometry, then in DuckDB INSERT INTO demo_data.demo.places SELECT id, ST_Point(lon, lat) FROM … (0.8 s for a small table; reads back as POINT). lhbox table import … --format-version 3 does this for a GeoParquet. The recipe: https://lakehousebox.com/docs/engines/. - now() and every TIMESTAMP WITH TIME ZONE column need pytz when fetched from Python (duckdb.sql(...).fetchall(), .df()), which a bare venv lacks: pip install pytz; or .show() inside DuckDB, cast (now()::VARCHAR), or leave the column out. The duckdb binary and lhbox duckdb are not affected. - The first query on a table loads its metadata (~0.5 s); later ones on the same table take ~0.05 s. - Commits are optimistic: when maintenance or another writer committed in between, the catalog answers 409 and DuckDB raises CommitFailedException … branch "main" has changed without retrying; wait a few seconds and re-run the statement. PyIceberg (0.12) - warehouse='s3://--/': the s3:// form is required here (the opposite of DuckDB); copying one engine's identifier into the other produces a false "DuckDB cannot write Iceberg". - Reads format-version 3, cannot write it, and cannot load a geometry column with a non-default CRS. - Asks for vended credentials on every request (X-Iceberg-Access-Delegation), which override any static s3.* key you pass; a cross-bucket copy that fails ACCESS_DENIED wants "header.X-Iceberg-Access-Delegation": "none". Spark (3.5 + Iceberg 1.11) - Reads and writes, format-version 3 included (verified 2026-09-20 with a geometry(EPSG:28992) column); the s3:// form for warehouse, header.X-Iceberg-Access-Delegation=vended-credentials, client.region=us-east-1 (the SigV4 region the store accepts, not a placement; without it the AWS SDK fails before the first request). - Java 17 or newer: the Iceberg 1.11 runtime jars are compiled for Java 17 and die with UnsupportedClassVersionError on Java 11 (what the apache/spark:3.5 images ship). - Trino and ClickHouse recipes exist on the engines page but are not yet verified against the service; Snowflake's data path is not available yet; Databricks is untested. Say so if the person asks. Formats. Every table has its own Iceberg format version: 2 by default (the catalog's default_format_version, changed with lhbox catalog create --default-format-version 3 or catalog update), 3 for geometry/geography. An engine that creates a table itself (DuckDB CTAS) chooses its own version and is never upgraded silently. Who writes and who only reads each version: GET /v1/config/formats. All of it with the recipes: https://lakehousebox.com/docs/engines/. ## 8. Slow paths and what they look like The store performs several identity writes per catalog operation, ~10 s each today, so some commands take a minute; the CLI prints progress while it waits. Measured 2026-09-21 against api.lakehousebox.com: | operation | measured | what the CLI shows | what to do | | catalog create | ~42 s on an idle store, 60–90 s under load | progress on stderr, then the catalog (status: ready, or provisioning with retry_after_seconds: 30) | wait; lhbox catalog list shows it as soon as it exists | | catalog rotate | ~41 s | progress, then the new credential state | fetch a fresh recipe afterwards (connect) | | signup (creates the first catalog) | ~55 s | the signup call returns once the catalog exists | the person waits once | | first query on a table after ATTACH | ~0.5 s, then ~0.05 s | nothing special | nothing | | CSV 14 MB → Iceberg table (read_csv CTAS) | ~2 s | one commit | nothing | A catalog may answer provisioning for a while; retry. Creation ends with a write into each of the catalog's buckets. If the store refuses that first write, the catalog is created but its status is provisioning: catalog create says so with retry_after_seconds: 30, GET /v1/connection (and so connect) answers 503 provisioning with Retry-After: 30, and doctor fails the catalog line with the reason. The control plane retries every 30 s for up to 10 minutes and flips the catalog to ready; wait 30 s and run lhbox doctor (or lhbox catalog get , which shows status) again. Do not conclude "cannot write". If the CLI ever reports a timeout, check lhbox catalog list before re-running: the operation usually completed. ## 9. Reading fast and bulk pulls One HTTPS stream from a laptop is bounded by the network path, not by the store (measured 2026-09-21 from Madrid: 13 MB/s to Hetzner's own speed test, 39 MB/s to s3.lakehousebox.com with BBR on the host, 468 MB/s from a machine inside Hetzner). Parallel range requests for one file did not add up on that path (12.9 vs 12.8 MB/s) and HTTP/1.1 beat HTTP/2 for one big file (12.8 vs 10.0 MB/s). DuckDB already reads Parquet row groups in parallel; do not add threads or range tricks of your own. If the person needs a bulk pull of a large table, say so and run it from a machine in Europe, best inside Hetzner. ## 10. Environments and rules | Your environment | Start with | | You can execute commands and run DuckDB (Claude Code, Codex, a shell tool) | The quick path above: the CLI connection, then local analysis with DuckDB. Everything the person needs to do is approve once in the browser. | | MCP only, no shell (Claude Desktop with the LakehouseBox MCP server, no code execution) | Ask the person to run lhbox login and lhbox mcp install --client claude-desktop once in a terminal; the server then exposes the account as tools: list catalogs, connection recipes and short-lived table credentials, grants, tokens, members, usage, audit. Missing for analysis: the MCP server runs no queries -- there is no query tool -- so loading files or answering questions over the data needs an engine you can run. Say so, and offer the CLI path. The tool list: https://lakehousebox.com/docs/mcp/. | Rules: - Credentials stay out of the conversation. The CLI saves them; --show-key exists for a person who asks, not for you. - One connection per machine; revoke from the account page, never by deleting the file (the agent would stay valid). - Everything is in the open formats it says: Iceberg tables in an S3 bucket the person can take elsewhere. ## Blob uploaders # Blob uploaders: a write-only credential for the blob bucket Published from the repository note platform/docs/blob-uploader.md, measured on 2026-09-21. The short version for agents is on the agents page (https://lakehousebox.com/agents#relay); the routes are in the API reference (https://lakehousebox.com/docs/api/), the commands in the CLI reference (https://lakehousebox.com/docs/cli/). The founder's item 1 of 2026-09-21 (platform/STATE.md, 03:40 UTC): a scoped, long-lived, revocable credential that can only drop files into a catalog's blob bucket ----blobs (b- on catalogs created before 2026-09-20; the API's bucket field and the recipe's blob_bucket spell it, this document writes for it). For a relay, a camera, a field device, a partner that sends you files, a CI job that publishes artefacts: anything that must write and must never read, list what others wrote, or come near the tables and the catalog. ``` lhbox catalog uploader create garden --name camera-north --prefix photos/north/ --save ~/camera-north.json lhbox catalog uploader list garden lhbox catalog uploader revoke garden --uploader camera-north ``` lhbox connect says so in its notes (uploaders_note in GET /v1/connection): the catalog's own credential is for engines and for you, never for a device. ## What the credential is, exactly One IAM identity in the store, u--<8 hex of the uploader id>, granted its rights by two statements in the blob bucket's plain bucket policy (an S3 write, PutBucketPolicy; since 2026-09-21, PLAN.md item 11 -- before that the same two statements were an inline user policy, PutUserPolicy, name uploader, an IAM write of 10 s at the host's identity count; uploaders from before keep theirs, uploader.grant_mode NULL). The control plane writes them once and never re-puts them: ``` {"Sid": "tm-up--put", "Effect": "Allow", "Principal": {"AWS": ["arn:aws:iam::*:user/u--"]}, "Action": ["s3:PutObject"], "Resource": ["arn:aws:s3:::/photos/north/*"]} {"Sid": "tm-up--list", "Effect": "Allow", "Principal": {"AWS": ["arn:aws:iam::*:user/u--"]}, <- only with list: true "Action": ["s3:ListBucket"], "Resource": ["arn:aws:s3:::"], "Condition": {"StringLike": {"s3:prefix": ["photos/north/*"]}}} ``` The statements sit next to the tenant's own data-path statement and the lifecycle grant in the same document (docs/permissions-and-access.md, "How identities and policies are laid out"); a revocation removes exactly the two Sids and nothing else. | the holder can | the holder cannot | | PutObject under the prefix, single or multipart (create, part, complete, abort) | read, HEAD, delete or overwrite-then-read anything, including its own uploads | | with list: true: ListObjectsV2 with ?prefix= | list without a prefix, or another prefix | | | anything on the table bucket -- (no statement names it) | | | use the catalog: POST /v1/oauth/tokens mints a token for any valid pair, and the table-bucket policy, which names only t- and t--ro, refuses it | No prefix (prefix: "") means the whole blob bucket; list: true without a prefix lists the whole blob bucket (no Condition), and the answer's scope says which. A prefix without a trailing slash confines to keys that start with it: photos covers photos.jpg and photos/a.jpg both. The prefix grammar is what S3 keys allow minus what the policy engine would read as a pattern: letters, digits, . _ - / = @ :; no leading slash, no empty or dot segments, no * ? $. expires_at is recorded and shown (status: expired after it); the key itself stays valid until revoked. Revocation is the only thing that stops a holder today. ## What was read in the store's source before this was built SeaweedFS 4.47, branch tablemere/4.47, weed/s3api (cited in app.py's uploader block; every line below is a claim the live matrix at the end checks): - PutUserPolicy stores the inline policy AND recomputes the identity's legacy Actions (a lossy projection: Write:b-/photos/north/*, List:b-, Conditions dropped). The S3 server, though, hydrates inline policies into identity.PolicyNames on every configuration reload (hydrateRuntimePolicies, called from the credential-manager load path that the propagation after each IAM write triggers), and VerifyActionPermission then routes to authorizeViaAttachedPolicies: the policy engine is authoritative and the legacy Actions are not consulted. That is what makes a fine-grained, prefix-conditioned policy hold. - The engine matches fine-grained actions: s3:PutObject does not imply s3:GetObject (write-only is real). - policy_engine/types.go multipartActionSet: s3:PutObject implicitly covers CreateMultipartUpload, UploadPart, UploadPartCopy, CompleteMultipartUpload, AbortMultipartUpload, ListParts and ListMultipartUploads. The routes for all of them carry ACTION_WRITE (s3api_server.go; the bucket-level ListMultipartUploads is ACTION_READ). s3:ListMultipartUploadParts is still "not a valid action" for GetActions, the PutUserPolicy validator (CLAUDE.md), so it must not appear in a policy; it is not needed. - s3:ListBucket is evaluated on the bucket ARN with s3:prefix taken from the request's ?prefix= (policy_engine.ExtractConditionValuesFromRequest); a listing without ?prefix= has no context value and StringLike returns false. The device must ask for its own prefix. ## The IAM cost, stated Every IAM write costs the store a propagation deadline (10 s at the host's identity count on 2026-09-20, about 0.1–0.3 s after the fixture purge / patch #4; STATE.md 21:10 UTC). Designed for the fewest writes: | operation | IAM writes | which | | create an uploader | 2 (was 3 until 2026-09-21) | CreateUser, CreateAccessKey, then the two statements merged into the blob bucket's policy -- an S3 call, no propagation (in that order: the key never exists with any grant but the final one; an identity with neither actions nor policies is denied everything) | | list | 0 | | | any later call, GET /v1/connection included | 0 | the statements are never re-put | | revoke | 2 (3 for an uploader from before) | DeleteAccessKey first (that is the revocation; the key is refused from then on), then the statements off the bucket policy (S3) and DeleteUser; an uploader with an inline policy (grant_mode NULL) gets DeleteUserPolicy in between; before the answer, idempotent | | delete the catalog | 2 per uploader (3 for one from before) | inside the background teardown that already carries the tenant's writes; the statements go with the blob bucket; each step lands in the tombstone's storage map | The unit tests count them through the fake's call log (control_plane/tests/test_uploaders.py). Each uploader is one more identity in the store, so make one per device or partner, not one per file. ## Consuming what was uploaded The uploader cannot read; you do, from the catalog side, with the catalog's own credential (lhbox connect, blob_bucket and blob_example_python in GET /v1/connection): ``` -- DuckDB, with the S3 secret of the connect recipe (lhbox_s3) SELECT filename, size, content FROM read_blob('s3:///photos/north/*.jpg'); -- Parquet dropped by a partner, straight into a table of the catalog INSERT INTO garden.sensors SELECT * FROM read_parquet('s3:///partner-acme/2026-09/*.parquet'); ``` Index files in a table (the pattern of our first tenant: photos in the bucket, one row per photo in a table) and hand download links out from your side with generate_presigned_url('get_object', …); the uploader has no GetObject to presign with. Deleting or moving what was uploaded is yours too. ## Prefix design - One prefix per device or partner (photos/north/, partner-acme/), so revoking one revokes one. - Put the date in the key, not in the prefix, unless you want the credential to expire with the month (--prefix drops/2026-09/ is a cheap time box). - A prefix that engines can glob: read_parquet('s3:///partner-acme/*/*.parquet'). - The blob bucket counts against the catalog's storage tier like the table bucket does (/v1/usage). ## Expiring what uploaders drop: lifecycle rules (added 2026-09-21) The catalog credential at write level can put S3 lifecycle rules on the blob bucket — expire camera/ after 90 days, abort multipart uploads not completed in 7 days — and they apply to everything under the prefix, what uploaders wrote included. The uploader itself cannot set or read them (measured 403 on ?lifecycle while its PutObject was 200), nor can the read-only identity. lhbox catalog lifecycle set --prefix photos/north/ --expire-days 90; the API, the boto3 form, when deletion happens (up to 24 h after an object is due) and why table buckets never carry rules: https://lakehousebox.com/docs/lifecycle/. ## Revocation and rotation DELETE /v1/warehouses/{id}/uploaders/{id|name}: the key is deleted at the store before anything else, so a request signed with it is refused at once and a multipart upload in flight cannot complete. There is no rotate: a lost or leaked secret is revoke + create (the name is free again; 4 IAM writes in all, 6 before 2026-09-21). Deleting the catalog removes every uploader identity with it. uploader.create and uploader.revoke are audited (name, prefix, list, identity, key id, the writes; never the secret). ## Routes ``` POST /v1/warehouses/{id}/uploaders {name, prefix?, list?, expires_at?} write level; 201; the secret once GET /v1/warehouses/{id}/uploaders?include_revoked= read level; never a secret DELETE /v1/warehouses/{id}/uploaders/{uploader_id|name} write level; the key first ``` CLI: lhbox catalog uploader create|list|revoke. create refuses to run without --save FILE (0600, nothing printed) or --show-key (printed once), so no identity is ever created whose secret nobody received; with --json, --save masks the secret everywhere in the answer, recipes included. ## Live matrix, measured 2026-09-21 (real output; both runs 30 probes) Two runs of the same matrix with a stdlib SigV4 signer (the CLI's doctor signer plus a payload hash): - The routes, end to end, against a real SeaweedFS 4.47 (this worktree's control plane on the laptop, talking to the local stack's al-weed through the gateway): POST /v1/warehouses 41.4 s (the 4 tenant IAM writes), POST …/uploaders 201 in 20.6 s twice (iam_writes: [CreateUser, CreateAccessKey, PutUserPolicy]), GET …/uploaders 200 with no secret in the body, DELETE …/uploaders/{id} 200 in 30.7 s (storage: {access_key: ok, user_policy: ok, user: ok}), DELETE /v1/warehouses/{id} 202, tombstone with uploader__key|policy|user: ok for both uploaders. - The store on al-lake1 (https://s3.lakehousebox.com, https://catalog.lakehousebox.com): the exact IAM / S3Tables calls the route makes, as the admin, from the host, with a fresh t-, two u- identities and a fresh table bucket; everything removed afterwards. The control plane build with the route was not deployed for this run (another session owns deploys; the branch is not pushed). IAM timings on the host: CreateUser 0.27 s, CreateAccessKey 10.3 s, PutUserPolicy 10.4 s, every Delete* 10.3 s (the propagation deadline of STATE 21:10 UTC), and 20–31 s per write in the second run while another writer held the IAM lock. Status codes as answered, identical in both runs (relay = prefix photos/, lister = the same with list: true, tenant = t-, the positive control with the same key shape): | request | got | | relay PUT b/photos/a.txt | 200 | | relay PUT b/other/a.txt (outside the prefix) / tenant, same key | 403 / 200 | | relay GET, HEAD, DELETE b/photos/a.txt (its own upload) / tenant GET | 403, 403, 403 / 200 | | relay ListObjectsV2 b and b?prefix=photos/ (list: false) / tenant | 403, 403 / 200 | | lister ListObjectsV2 b?prefix=photos/, ?prefix=photos/2026/ | 200, 200 (KeyCount 1) | | lister ListObjectsV2 b (no prefix), ?prefix=other/ | 403, 403 | | lister GET b/photos/a.txt (listing is not reading) | 403 | | relay PUT w/x/y/data/a.parquet (a real 277-byte parquet), ListObjectsV2 w | 403, 403 | | tenant PUT w/x/y/data/a.parquet (positive control, same key) | 200 | | tenant PUT w/x/y/data/a.txt (the probe shape CLAUDE.md warns about) | 403 AccessDenied for the tenant too: a .txt proves nothing | | relay multipart photos/big.bin: CreateMultipartUpload, UploadPart 1, CompleteMultipartUpload; Abort of a second | 200, 200, 200; 204 | | relay CreateMultipartUpload other/big.bin (outside the prefix) | 403 | | relay POST /v1/oauth/tokens with its pair | 200, a token is minted (the store checks the pair only) | | relay with that token: GET /v1/config?warehouse=s3://w/ | 200: discovery answers any valid identity; it grants nothing | | relay with that token: GET /v1/w/namespaces, POST /v1/w/namespaces / tenant | 404, 403 / 200, 200 (the table-bucket policy hides the warehouse and refuses the write) | | relay PUT b/photos/b.txt after revoke / lister, same key | 403 / 200 | Two observations from the runs that are not about the uploader: (a) a 4-byte PAR1 stand-in for a parquet file was answered 500 by the host's table bucket (200 by the local one), so the positive control uses a real parquet written by DuckDB; (b) DeleteTableBucket answered 409 BucketNotEmpty for 20–30 s after the last object under x/y/data/ was deleted (204), then 200: the emptiness check lags object deletion, which is also why a tombstone can record table_bucket: HTTP 409 until the startup sweep retries. ## Lifecycle rules # Lifecycle rules on the blob bucket: expire files by age, abort stale uploads Published from the repository note platform/docs/lifecycle.md, measured on 2026-09-21. Rules apply to a catalog's blob bucket only; table buckets are managed by snapshot expiry and orphan cleanup. Measured 2026-09-21, 05:50–06:10 UTC, against the live service (api.lakehousebox.com, s3.lakehousebox.com, al-lake1) with a fresh QA account and against the SeaweedFS 4.47 source (tag 4.47-tm.1); the brief was platform/STATE.md "Additions to the brief" item (A). Built the same morning: one bucket policy per blob bucket, three API routes, a CLI group, a guard. This document says what a tenant can do, how, when it takes effect, and the one rule that never bends: table buckets never carry lifecycle rules. ``` lhbox catalog lifecycle set garden --prefix camera/ --expire-days 90 lhbox catalog lifecycle set garden --abort-multipart-days 7 # a second rule; the first is kept lhbox catalog lifecycle get garden lhbox catalog lifecycle clear garden ``` ## What a tenant can do On the catalog's blob bucket (----blobs; b- on catalogs from before 2026-09-20; the API's bucket field names it), the holder of the catalog credential at write level can set S3 lifecycle rules. Every rule below was PUT and read back identically, and the ones marked honoured were traced to the code path that executes them: | rule | stored | honoured by the store's worker | | Expiration.Days = N with Filter.Prefix | yes | yes: due at the object's last write + N × 24 h, deleted at the first pass after that (up to 24 h late). No midnight rounding | | Expiration.Date = | yes | yes: every matching object at the first pass with now >= Date (a past date: the next pass) — measured: two objects under camera/ gone within 14 s of the pass, the control outside the prefix untouched | | AbortIncompleteMultipartUpload.DaysAfterInitiation = N | yes | yes: on the upload's initiation record, N × 24 h | | Filter.Prefix (empty = the whole bucket), Filter.Tag, Filter.And {Prefix, Tag…, ObjectSizeGreaterThan/LessThan} | yes | yes; tags are re-read at evaluation | | Status: Disabled | yes | inert, as it should be | | Expiration.Days = 0 | yes (AWS would refuse) | never fires: ExpirationDays <= 0 -> none. The API refuses it for you; use a past Date for "now" | | Transition / NoncurrentVersionTransition, enabled | no: 501 NotImplemented, nothing half-applies | no storage classes exist here | | NoncurrentVersionExpiration | yes | inert: buckets are never versioned | | 1,000 rules (~95 KiB) | yes | — | | ~1.3 MiB of rules | no: 400 EntityTooLarge | the only size limit is 1 MiB of XML; there is no rule-count limit | | DELETE ?lifecycle | 204; GET answers 404 after | the next pass finds no rule | Not implemented at the store and staying so: x-amz-expiration on HEAD/GET (you cannot ask an object when it will go), AWS's rejection of Days: 0, midnight rounding, transitions. Who: the catalog credential at write level sets, reads and clears rules (the API needs write to set and clear, read to read). Uploaders cannot (their policy is s3:PutObject under a prefix; measured 403 on GET, PUT and DELETE while their PutObject was 200) and the read-only identity cannot (same mechanism; a read holder must not delete by rule). Rules apply to every object under the prefix whoever wrote it: a 90-day rule on camera/ removes what the camera's uploader dropped there. ## How: the API, the CLI, or boto3 The API takes a small shape and writes the XML for you, as the tenant (the tenant's own key pair; the control plane's admin key is not used), then reads it back: ``` PUT /v1/warehouses/{id}/lifecycle {"rules": [{"id"?, "prefix"?, "expire_days" | "expire_date", "abort_incomplete_multipart_days"?, "enabled"?}, …]} write level GET /v1/warehouses/{id}/lifecycle [?include_xml=true] read level DELETE /v1/warehouses/{id}/lifecycle write level ``` PUT replaces the whole configuration (S3 semantics). It refuses, before the store sees anything: expire_days: 0 or negative (400 invalid_value, the remedy says why), anything named transition or storage_class (400 not_supported), unknown fields (400 with accepted), both expiry forms on one rule, duplicate ids, an empty list (that is DELETE), more than 1,000 rules, more than 1 MiB of XML (413 too_large; the store's own EntityTooLarge maps to the same). Ids default to -. The prefix grammar is the uploader's: letters, digits, . _ - / = @ :, no leading slash, no * ? $. GET parses the stored document back, boto3-set rules included: tags, size filters and other elements appear on the rule and expressible: false says PUT here could not re-create it. Audit events: lifecycle.set, lifecycle.clear. The CLI merges: set reads the current rules, replaces the one with the same id (default id: the prefix without slashes, camera; bucket for the whole bucket) and re-puts the rest, so two set calls for two prefixes leave two rules. It refuses to rewrite a configuration carrying rules it cannot express (tag or size filters set with boto3) unless --replace-all. get --xml prints the stored document; --json everywhere. With boto3 and the catalog credential (catalog_credential from lhbox connect), for tag and size filters or an existing S3 workflow — exactly what was measured: ``` import boto3 from botocore.config import Config s3 = boto3.client("s3", endpoint_url="https://s3.lakehousebox.com", region_name="us-east-1", aws_access_key_id=CLIENT_ID, aws_secret_access_key=CLIENT_SECRET, # catalog_credential from `lhbox connect` config=Config(s3={"addressing_style": "path"})) s3.put_bucket_lifecycle_configuration( Bucket="----blobs", LifecycleConfiguration={"Rules": [ {"ID": "camera-90d", "Status": "Enabled", "Filter": {"Prefix": "camera/"}, "Expiration": {"Days": 90}}, {"ID": "abort-stale-uploads", "Status": "Enabled", "Filter": {"Prefix": ""}, "AbortIncompleteMultipartUpload": {"DaysAfterInitiation": 7}}, ]}) print(s3.get_bucket_lifecycle_configuration(Bucket="----blobs")["Rules"]) # s3.delete_bucket_lifecycle(Bucket=...) removes every rule ``` ## When it takes effect The store runs the s3_lifecycle worker job once a day and about five minutes after every restart of the store (al-lake1: enabled: true, detection_interval_minutes: 1440; 11 successful runs of 8–32 ms since 2026-09-19, one within ~5 min of every al-weed start). One pass compiles every bucket's rules, replays the filer's write log for Days and multipart rules and walks the buckets with Date rules; an object is deleted at the first pass after it is due, so up to 24 h after Days elapses. A day is 24 h from the object's last write. Deletion is a plain delete on an unversioned bucket: permanent, no versions, no undelete; an object overwritten since the rule saw it is left alone. A failed delete stalls the pass and is retried at the next. The storage meter (GET /v1/usage, the catalog row's storage) shows the reduction at its next hourly sample, or immediately with POST /v1/warehouses/{id}/measure / lhbox catalog measure (measured: blob_objects 3 → 1 on the next measure after the pass). ## Never on the table bucket Iceberg data and metadata are retained by the catalog's own maintenance — snapshot expiry (168 h / 20 snapshots) and orphan cleanup (files no snapshot references for 24 h) — never by key age: a lifecycle rule on a table bucket would delete Parquet and metadata.json files the catalog still references and corrupt every table under it. The store's handler does not distinguish table buckets (the admin identity could set a rule there, and the live worker would execute it), so the guard is ours, in three layers: - No tenant path reaches it. The tenant's user policy carries the five data-path actions; PutUserPolicy refuses s3:PutLifecycleConfiguration / s3:GetLifecycleConfiguration as "not a valid action" (measured 400); the table-bucket policy names s3tables:* and the data path. ?lifecycle on the table bucket is 403 for the tenant (measured, GET and PUT). - The control plane refuses to grant it. s3_put_bucket_policy raises (lifecycle_guard) on any document naming a lifecycle action — s3:*Lifecycle*, s3:*, s3:Put* — for a bucket that is not a blob bucket by shape or that the database knows as a table bucket (a catalog named blobs has the table bucket --blobs). Unit-tested against every policy document the fake store receives. - The audit. GET /internal/lifecycle-audit (bootstrap secret) reads ?lifecycle and ?policy on every table bucket as the admin and lists anything but 404 / no lifecycle grant; the nightly sweep is to call it and alert on ok: false. ## The mechanism, for the record A tenant could not set a rule before this: ?lifecycle resolves to s3:GetLifecycleConfiguration / s3:PutLifecycleConfiguration (DELETE uses the Put action) and no user policy can carry them. What opens them is a plain bucket policy on the blob bucket naming the identity as an IAM-user ARN — plain-bucket principals are ARNs (arn:aws:iam:::user/), so arn:aws:iam::*:user/t- matches and a bare name (the S3 Tables normalisation CLAUDE.md records) stays 403; a bucket-policy Allow bypasses the IAM check. PutBucketPolicy is an S3 call: no IAM write, no propagation deadline. The control plane writes this one statement at catalog creation (create_tenant), when a legacy catalog gets its blob bucket (ensure_blob_bucket), before every lifecycle request (idempotent, one GET), and once for every existing blob bucket at the first startup after the deploy (the blob_lifecycle_grant_v1 migration in the background worker: 301 rows, milliseconds each, retried at the next start if anything failed): ``` {"Version": "2012-10-17", "Statement": [{ "Sid": "TenantLifecycle", "Effect": "Allow", "Principal": {"AWS": ["arn:aws:iam::*:user/t-"]}, "Action": ["s3:GetLifecycleConfiguration", "s3:PutLifecycleConfiguration"], "Resource": ["arn:aws:s3:::----blobs"]}]} ``` The public-read policy of a public catalog lives on the table bucket, so the two documents never share a bucket; the grant is merged by Sid all the same. Deleting the catalog deletes the blob bucket with its policy and its rules (measured: both QA buckets gone, no bucket on the host carried a rule afterwards). Sources read: weed/s3api/s3api_bucket_handlers.go (validation: 1 MiB, MalformedXML, 501 for an enabled Transition), s3api_bucket_lifecycle_config.go (storage on the bucket entry), weed/worker/tasks/s3_lifecycle/ and weed/s3api/s3lifecycle/ (the daily pass, evaluate.go ExpirationDays <= 0 -> none, due_at.go 24 h days), s3api_internal_lifecycle.go (the delete), weed/iam/helpers.go (baseS3ActionMap, no Lifecycle), auth_credentials.go (buildPrincipalARN, the bucket-policy bypass), s3_action_resolver.go. A per-write TTL fast path exists in the store (Seaweed-X-Amz-Lifecycle-Ttl-Fast-Path, weed shell only, irreversible once stamped); it is off on every LakehouseBox bucket and not for tenants. ## Live smoke, after the deploy ``` # a QA catalog and its write-level key (lhbox login / an org admin key in LHBOX_API_KEY) lhbox catalog create qa_lifecycle lhbox catalog lifecycle get qa_lifecycle # None configured: 0 rules lhbox catalog lifecycle set qa_lifecycle --prefix camera/ --expire-days 90 --abort-multipart-days 7 lhbox catalog lifecycle get qa_lifecycle --xml # the stored document # the same through boto3 with the catalog credential from `lhbox connect --json`: put/get as above; 200/200 # the table bucket stays closed: as the tenant, GET ?lifecycle on --qa-lifecycle must be 403 # the guard's audit: on the host, curl -sS -H "X-Bootstrap-Secret: $AL_BOOTSTRAP_SECRET" 127.0.0.1:8080/internal/lifecycle-audit -> ok: true lhbox catalog lifecycle clear qa_lifecycle # Cleared 1 lifecycle rule lhbox catalog delete qa_lifecycle --confirm qa_lifecycle ``` ## Ingest sinks # Ingest sinks: one HTTP intake per table Published from the repository note platform/docs/ingest-sink.md, measured on 2026-09-21. The routes are in the API reference (https://lakehousebox.com/docs/api/), the commands in the CLI reference (https://lakehousebox.com/docs/cli/); the short version for agents is on agent setup (https://lakehousebox.com/docs/agent-setup/). Status: enabled on the service since the fourth pull request of the series (research/24a §4); AL_SINKS_ENABLED=false turns it off (every /v1/sinks route answers 404 not_found, the roller idles). Designed in research/24-ingest-sink.md, sized in research/24-ingest-sink-estimate.md; the founder's defaults (2026-09-21): the ingest host is the API host, consumed batches are deleted at commit, the roller is its own service. The integration check is Phase 10 of platform/scripts/test.sh (CI's stack job and the deploy gate run it). A device, a webhook, a cron job or an agent with no query engine posts JSON arrays to one URL with a send-only key. The platform stores each batch unchanged, appends what is waiting to the Iceberg table on a rolling policy, stamps when each row arrived, and puts the rows that do not fit the table's schema where the producer can read them. No transforms, no schema evolution, no ordering across batches, no user code: the table's current schema is the contract. ``` lhbox table create garden --namespace garden --name events --column x:long:required --column who:string lhbox sink create garden --table garden.events --name events_in --save ~/events-sink.json lhbox sink send events_in batch.json --send-key-file ~/events-sink.json # or any HTTP client, below lhbox sink get events_in # lag, last roll, rejects ``` The same with curl, the way a device does it: ``` curl -X POST https://api.lakehousebox.com/v1/sinks/ \ -H "Authorization: Bearer al_send_…" -H "Idempotency-Key: 2026-09-21T12:00:00Z-0001" \ -H "Content-Type: application/json" \ -d '[{"x": 1, "who": "north"}, {"x": 2}]' -> 202 {"accepted": 2, "rejected": 0, "reasons": [], "batch_id": "…", "bytes": 33, "received_at": "…", "state": "pending"} ``` ## What a sink is One row per table (409 sink_exists for a second): the table's single committer. The table must exist and be unpartitioned (404 table_not_found with the namespace's tables, 409 partitioned_table_unsupported; our maintenance worker does not compact partitioned tables, so the sink does not create one). Creating a sink costs no IAM write and no commit: the answer says iam_writes: [], and the store's identity count does not move (every other create route on the platform costs identity writes; research/25). The roll policy, per sink: roll_seconds (default 300, floor 60), roll_bytes (32–64 MiB, default 64), inactivity_seconds (optional: a producer that posts a burst and goes quiet gets its rows committed early), and a fixed count of 1,000 pending batches. Any of them rolls. A roll is a commit, and a commit costs more the more snapshots the table holds (research/24 §4 "Interplay"): 300–900 s suits a producer under about a megabyte per five minutes; the hourly maintenance keeps the table at 20 snapshots and compacts the small files. The first roll adds an optional __ingest_ts (timestamptz) column and sets the metadata-retention properties (write.metadata.delete-after-commit.enabled=true, write.metadata.previous-versions-max=5), one commit; a table that has both is untouched. Nothing is partitioned in v1; the owner partitions with their engine if they want to. ## The send key POST /v1/sinks answers the key once (al_send_<12 hex>_<43 chars>); the row keeps its SHA-256. It is not a principal: it can never become a token, hold a grant, or reach /v1/connection. It is accepted on exactly one route, POST /v1/sinks/{sink_id}; anywhere else it is 403 send_only, and an API key or a token on the send route is 403 send_only too. Revocation is the sink's deletion (the row is the key). The CLI treats it like the uploader's secret: --save FILE (mode 0600, nothing printed) or --show-key (printed once); without either flag sink create stops before calling the API. ## The send route ``` POST /v1/sinks/{sink_id} Authorization: Bearer Idempotency-Key: required; 1–128 chars of A-Z a-z 0-9 . _ : - Content-Type: application/json [ {…}, {…}, … ] a JSON array of objects, <= 16 MiB ``` | answer | when | | 202 {accepted, rejected, reasons[{index, reason: not_an_object, got}], batch_id, bytes, received_at, state: pending} | stored; accepted/rejected are structural (elements that are not objects); the schema check happens at the roll | | `200 {duplicate: true, batch_id, accepted, rejected, bytes, received_at, state: pending | committed}` | | 400 not_an_array / empty_batch / missing_idempotency_key / invalid_batch_id | the body or the header | | 401 invalid_key | a key that is not this sink's (or a deleted sink's) | | 403 send_only | the wrong kind of credential, either way | | 409 sink_disabled (disabled_reason: table_missing) | the table went away; recreate it and make a new sink | | 409 quota_exceeded | the organisation is over its storage tier (the gate runs before any write) | | 413 batch_too_large (limit_bytes) | over 16 MiB; split the array | | 502 storage_error | the store refused the write; nothing recorded; safe to retry with the same batch id | Each element's bytes are stored unchanged, one per line, as one NDJSON object under _sink//.ndjson in the catalog's blob bucket, written as the catalog's own identity in one small PUT (no IAM write, no catalog call). The object carries Content-Type: application/x-ndjson, x-amz-meta-received (the intake's clock; what __ingest_ts is set from), x-amz-meta-rows, x-amz-meta-sink. ## The roll The roller (worker/roll.py, the sink-roller service) ticks every 10 s and, for every sink whose policy says so, in order: reloads the table as the tenant; re-establishes the invariant (any batch the current snapshot's summary already names is deleted from intake before anything new is committed; a delete that fails ends the roll, and lag grows rather than a row being appended twice); does the first-roll changes once; reads the pending batches (at most 1,000); validates row by row against the table's current schema; writes the rejects files; writes one Parquet file with the Iceberg field ids and commits one append whose snapshot summary carries tablemere.sink.id, tablemere.sink.batches (the consumed ids), tablemere.sink.rows, tablemere.sink.rejected_rows, tablemere.sink.rolled_at; deletes the consumed batches; reports; sweeps rejects older than 7 days. A lost race against the compactor or an engine re-commits the same file against the new base (up to 5 attempts, 0.5–8 s backoff); an empty roll (every row rejected) commits nothing. Exactly-once is recovery, not coordination: pending = intake minus the ids in the last summary, recomputed from the store on every roll. A roller that dies at any step restarts and finds its batches either in the summary (consumed; deleted) or in intake (pending; rolled). Duplicate detection at the intake is bounded: ledger rows are pruned 7 days after commit, so a batch id replayed later than that is accepted again. ## Rejects A row is rejected, never a batch, with a reason: missing_required (field), unknown_field (field: dropping a field silently is the one failure a producer cannot see), type_mismatch (field, expected, got), unsupported_type (a geometry or geography column: v1 does not write them), not_an_object. A batch the roller cannot parse at all goes to rejects whole as batch_unreadable and the sink goes on. The files are _sink//rejects/.ndjson, one JSON object per rejected row with the original row and the batch id; the catalog's read recipe reads them (read_json in DuckDB, s3:///_sink//rejects/*.ndjson). GET /v1/sinks/{id} shows rejects {rows_total, batches_with_rejects, last_at, prefix}. Values: timestamps as RFC 3339 strings, dates as YYYY-MM-DD, decimals as strings or numbers, binary as base64, nested structs, lists and maps as JSON. ## What it costs and where it is counted Pending intake is transit, not storage: both storage meters (the hourly sweep and POST /v1/warehouses/{id}/measure) skip _sink/, and the bytes accepted at the intake are counted as ingest_bytes per project in GET /v1/usage (month_to_date, batches, rows_accepted, rows_rejected, by_sink; enforced: false). The same rows are metered once more as Parquet once committed, where they are stored. The store's hard bucket quota counts every byte in the blob bucket, intake included: an organisation at the edge of its allowance is stopped by the bucket going read-only before the meter would say so (409 quota_exceeded). ## Routes and commands ``` POST /v1/sinks {name, table: ".
", warehouse, roll_seconds?, roll_bytes?, inactivity_seconds?} write level GET /v1/sinks[?warehouse=] read level; ?include_deleted=true GET /v1/sinks/{id|name} read level: state, roll, lag{batches_waiting, bytes_waiting, oldest_waiting_at, seconds_behind}, received{…}, last_roll{at, batches, rows, rejected_rows, snapshot_id, seconds}, rejects{…} DELETE /v1/sinks/{id|name} write level; 202 {pending_batches_discarded}; the table is untouched POST /v1/sinks/{id} the send key only (above) GET /internal/sinks the roller's view (bootstrap secret; never served publicly) POST /internal/sinks/{id}/roll the roller's report ``` CLI: lhbox sink create --table .
--name [--roll-seconds N] [--roll-bytes N] [--inactivity-seconds N] (--save FILE | --show-key), sink list [] [--all], sink get , sink delete [--discard-pending], sink send [] (--send-key-file FILE | --send-key K | LHBOX_SEND_KEY) [--batch-id ID]. lhbox connect mentions sinks in its notes (sinks_note, ingest_url in GET /v1/connection) once the flag is on. Audit events: sink.create, sink.delete, sink.roll. ## Measured (2026-09-21, the local stack: weed mini 4.47-tm.2, the control plane of PR 1 + the roller of PR 2) Recorded in platform/STATE.md (the two "ingest sink" entries): | step | result | | POST /v1/sinks on garden.events (x long required, who string, v double) | 201, state active, iam_writes: [] | | three batches of four elements ({x}, {x, v}, {x: "bad"}, a bare string) | 202 accepted 3 rejected 1 each; lag 3 batches / 150 bytes | | the same batch id again | 200 duplicate | | the API key on the send route; the send key on /v1/warehouses | 403 send_only both | | the roll (trigger time, 60 s) | batches 3, rows 6, rejected_rows 3, seconds 0.062, one snapshot | | GET /v1/table afterwards | rows 6, snapshots 1, data_files 1, columns x, who, v, __ingest_ts, the two retention properties set | | the ledger | e2e-1/2/3 committed with the snapshot id | | ingest_bytes | 150 / 3 batches / 9 accepted / 3 rejected | Two facts the run taught, both in the code now: PyIceberg's S3 filesystem uploads every file in parts and the store refuses UploadPart to the tenant's static identity under the bucket-policy data grant, so the roll's data file goes through one signed PUT and the table's own metadata files go through vended credentials, exactly as every engine's do; and the intake's boto3 client sends a signed payload (request_checksum_calculation="when_required") because the store refuses the streaming-unsigned-trailer shape under that grant (STATE 2026-09-21 16:40Z). ## Not in v1 Creating the table from the sink request; a rotate-key route (delete and create rotates); NDJSON request bodies; MCP tools (after the REST and CLI have run for a week); a partition at the first roll; a per-table max_snapshots override for sinks at the 60 s floor; the sidecar id list for sinks needing more than 1,000 batches per roll. All named with their reasons in research/24 §6 and research/24a §2. # The LakehouseBox MCP server The MCP server is published from the repository note docs/mcp.md, as written on 2026-09-20. It is installable from a checkout (uvx --from /platform/mcp lakehousebox-mcp); the package name lakehousebox-mcp is reserved and not yet on PyPI. lhbox mcp install (CLI reference (https://lakehousebox.com/docs/cli/#lakehousebox-mcp-install)) writes the host configuration below for you, with the saved key and without showing it. lakehousebox-mcp gives an MCP host (Claude Desktop, Claude Code, Cursor, anything that speaks the protocol) the LakehouseBox account as tools: catalogs (list_catalogs, create_catalog, delete_catalog; a catalog is a warehouse in the API, and the former list_warehouses/create_warehouse/delete_warehouse tool names still work as aliases), connection recipes and short-lived table credentials, grants, tokens, members, the organisation, usage, the audit log, Terms. It is the third face of the same REST API as the CLI and the account page (https://lakehousebox.com/docs/api/), and it deliberately does not query data: the agent gets a connection recipe and runs DuckDB itself. Source: platform/mcp/lakehousebox_mcp.py (one file). Transports: stdio on your machine (this page, lhbox mcp install) and Streamable HTTP at https://mcp.lakehousebox.com/mcp (the "Remote" section at the end; the same module as an OAuth 2.1 resource server). SDK: the official mcp Python package, pinned. ## Install The shortest path is the CLI, which already holds your key (lhbox signup --save or lhbox login --save wrote it to ~/.config/lhbox/credentials.json): ``` lhbox mcp install # Claude Code: `claude mcp add-json`, user scope lhbox mcp install --client claude-desktop # Claude Desktop: merges into claude_desktop_config.json (backup first) lhbox mcp install --print # the snippet for any other host, key masked lhbox mcp run -- --list-tools # start the server yourself, key in its environment ``` The key travels from the credentials file into the host's configuration and is never printed; the output shows it masked (al_live__********). --api-key installs a different key, for instance a token's; --from says what uvx installs (a checkout's platform/mcp, the default when the CLI runs from one, or the package name once it is published); --url picks the API and the saved key for it. lhbox mcp install --help has the rest. The server itself is a Python package in the repo; there is no PyPI release yet. By hand, any of: ``` # run without installing (uv), from a checkout uvx --from /path/to/tablemere/platform/mcp lakehousebox-mcp --list-tools # install as a command (pipx) pipx install /path/to/tablemere/platform/mcp lakehousebox-mcp --version # or build a wheel once and install that anywhere cd platform/mcp && uv build && pipx install dist/lakehousebox_mcp-0.1.0-py3-none-any.whl ``` Python 3.10 or newer. --list-tools prints every tool with its JSON schema and exits; without it the server speaks MCP on stdin/stdout and is meant to be started by the host, not by hand. ## Configure the host Two environment variables, nothing else: | variable | meaning | default | | LHBOX_API_KEY | a token's API key (al_live_…). Create one in the account page (Tokens) or with lhbox agent create --name claude --grant :write; the key is shown once | none; every tool then answers missing_credential | | LHBOX_URL | the API | https://api.lakehousebox.com | Claude Desktop (claude_desktop_config.json) and Claude Code (.mcp.json in a project, or claude mcp add-json lhbox '') take the same mcpServers shape: ``` { "mcpServers": { "lhbox": { "command": "uvx", "args": ["--from", "/path/to/tablemere/platform/mcp", "lakehousebox-mcp"], "env": { "LHBOX_API_KEY": "al_live_...", "LHBOX_URL": "https://api.lakehousebox.com" } } } } ``` With a pipx install, "command": "lakehousebox-mcp" and no args. lhbox mcp install writes exactly this object (with uvx by absolute path, because hosts start servers with a short PATH). Give the server a token created for it, not a human's key, once tokens can hold organisation roles (below): a token can be confined to the catalogs it needs and revoked on its own, and its actions appear under its own name in the audit log. Today the account-management tools need a human member's key, which is what lhbox mcp install uses by default. ## Tools Every tool's arguments are typed and described in its schema (lakehousebox-mcp --list-tools). Where the API wants an id, the tools also accept the name (catalog, project, organisation) and resolve it; an ambiguous name is an error listing the candidates, never a guess. When the token belongs to one organisation or sees one project, org / project can be omitted. | tool | what it does | API | | whoami | principal id, organisations and role in each, projects, Terms status | GET /v1/usage, /v1/orgs, /v1/terms | | usage | every limit, current value and headroom; storage bytes per organisation and catalog | GET /v1/usage | | list_catalogs | the catalogs the token can see, grouped by project, with locations and catalog URI | GET /v1/projects + GET /v1/warehouses | | create_catalog | a new catalog, in the organisation's default project unless one is named; optional default_format_version (2 default, or 3: the Iceberg format version create_table gives new tables there when none is passed); takes an idempotency key (one is generated if omitted) | POST /v1/warehouses | | delete_catalog | irreversible: drops every table, both buckets, both identities, grants and usage; needs the catalog's exact name as confirm, checked before anything is sent; admins only | DELETE /v1/warehouses/{id} | | create_table | an Iceberg table through the catalog: columns in order (name, type, required, identifier; field ids follow), the namespace created on demand; format_version optional -- without it the table gets the catalog's default_format_version (2 unless changed with lhbox catalog update), 3 for geometry/geography columns; the answer says format_version_source (request or catalog_default) and which engines write that version (writers, readers, note, from GET /v1/config/formats) | POST /v1/tables | | connection | the paste-ready DuckDB / PyIceberg / Spark recipe for a catalog | GET /v1/connection | | table_credentials | short-lived S3 credentials scoped to one table's prefix (about an hour) | POST /v1/credentials | | grants_list | who holds read or write on a catalog and via what (membership, creator, admin, grant) | GET /v1/warehouses/{id}/grants | | grants_set | give a member or token read or write on a catalog | PUT /v1/warehouses/{id}/grants/{principal} | | grants_revoke | remove a grant | DELETE /v1/warehouses/{id}/grants/{principal} | | tokens_list | the organisation's tokens (agents): id, name, grants, creator, last used | GET /v1/orgs/{org}/agents | | tokens_create | a new token with grants [{warehouse, level}]; its key is returned once; at least one grant | POST /v1/orgs/{org}/agents | | tokens_update | rename a token and/or replace its grants | PATCH /v1/orgs/{org}/agents/{id} | | tokens_revoke | revoke a token: every key stops at once | DELETE /v1/orgs/{org}/agents/{id} | | members_list | members with role and join date | GET /v1/orgs/{org}/members | | members_invite | invite an address as admin or member; the invitation token is returned once | POST /v1/orgs/{org}/invitations | | members_set_role | admin or member; the last admin cannot be demoted | PATCH /v1/orgs/{org}/members/{id} | | members_remove | remove a member | DELETE /v1/orgs/{org}/members/{id} | | org_get | the organisation(s): name, your role, domains, member count | GET /v1/orgs | | org_rename | rename the organisation (admins) | PATCH /v1/orgs/{org} | | audit | who did what: own events, or the organisation's for admins; since, action, limit | GET /v1/audit | | terms_status | the Terms version in force and whether the account holder accepted it (there is no accept tool: Terms are a person's act, the API answers 403 not_a_person to a token, and this server always acts as a token; the person accepts at signup or with lhbox terms --accept under their own login) | GET /v1/terms | The three catalog tools were named list_warehouses, create_warehouse and delete_warehouse until 2026-09-21 (the API's word); those names still work as aliases of the same tools, so a host configured against them keeps working. The measurements below were taken under the old names. Tools carry the MCP annotations hosts use to decide when to ask the user: readOnlyHint on every read, destructiveHint on delete_catalog, grants_revoke, tokens_revoke, members_remove. ### Errors A failed call is an isError result whose content is the API's error envelope, unchanged, plus the HTTP status: ``` {"error": {"code": "limit_exceeded", "message": "This project already has its maximum warehouses.", "limit": "warehouses", "limit_value": 3, "current": 3, "remedy": "Delete a catalog, or ask for the limit to be raised.", "request_id": "…"}, "http_status": 409} ``` The code says whether to change the request or the plan; the remedy says how. Three envelopes are the server's own: missing_credential (no key configured), unreachable (no answer from LHBOX_URL; safe to retry), and route_missing — the deployed control plane predates a route of the 2026-09-20 contract (the grants routes, tokens_update, delete_catalog). Nothing was changed in that case; the envelope names the method and path. ### Today's control plane (measured 2026-09-20 against the local stack) The server is written against the 2026-09-20 contract; the control plane is catching up. Driven with a human member's key (the signup principal), every tool answered, and grants_list, tokens_update and delete_catalog came back route_missing (their routes do not exist yet; nothing was changed). Driven with an agent token's key created by that human: | tool | as an agent token, today | | whoami, usage, list_catalogs, connection, table_credentials, audit (own), terms_status | work | | org_get | answers, with an empty list: the token is not a member of the organisation that created it | | tokens_*, members_*, org_rename, audit with org | no_organization (this server's envelope), for the same reason | | create_catalog | 403 forbidden from the API: only an owner or admin may create a catalog | | list_catalogs | lists every catalog of the project, including those the token is confined out of; the API answers 404 on use, as it does for a stranger | The measured limitation, in one sentence: on today's control plane a token (principal kind agent) cannot call the organisation routes (/v1/orgs/…: tokens, members, the organisation itself, the organisation's audit log), because a token is not a member of the organisation that created it and the routes check membership. Nothing in this server can change that; it is a contract follow-up: docs/permissions-and-access.md already writes the MCP server as "authenticated with a token's API key", so either tokens gain organisation roles or the organisation routes accept a token as acting for its organisation within its grants, and the control plane has to implement it before that sentence is true for the account tools. So until that lands, the account-management tools want a human member's API key: lhbox login --save (or lhbox signup --save) writes one to the credentials file, and lhbox mcp install uses it by default; lhbox api-key create --label mcp mints a separate key for the server, shown once, revocable on its own (lhbox api-key revoke). A confined agent token serves the data-side tools (connection, table_credentials, usage, own audit). The no_organization remedy says this to the agent. ## Security model - The token's grants bound every tool. The server adds no permission of its own and holds no second credential; a read-only token gets read-only recipes and credentials, and a 403 from the API is returned as such. What an admin can do through the tools is exactly what that admin's token can do with curl. - The key is configuration, not conversation. It reaches the process through the environment and leaves it only as the Authorization header to LHBOX_URL. It is never logged, never part of a result, never in a traceback (Config.__repr__ prints set/unset). Keys the API creates on request (tokens_create, members_invite's invitation token) are returned once because that is what was asked for; store them where they belong, not in the chat. - Nothing is cached. Every tool call is one or a few HTTP requests made now; there is no local state, no credentials file, no memory between calls. Revoking the token ends the server's access at the next call. - No telemetry. The server talks to LHBOX_URL and to nothing else; the only thing it sends beyond the request is a User-Agent: lakehousebox-mcp/. - Destructive actions are refused client-side when the confirmation is wrong (delete_catalog compares confirm with the resolved name before sending) and the API refuses them again server-side. - Use HTTPS. LHBOX_URL defaults to https://api.lakehousebox.com; a plain http:// URL is for a local stack only. ## What is not there, on purpose - No query tool, no peek, no natural-language-to-SQL. Rows in a tool response are tokens in a context window and compute on our side; both are the wrong place for them (research/13 §1). The agent calls connection and runs the recipe in its own DuckDB: ``` INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs; -- then the CREATE SECRET / ATTACH statements exactly as `connection` returned them SELECT count(*) FROM demo_data..
; -- the catalog is attached under its own name ``` - No schema-evolution tools, and create_table is the one create tool. DuckDB does CREATE TABLE … AS SELECT * FROM read_parquet() against the attached catalog (https://lakehousebox.com/docs/engines/) and keeps the format version it chose; create_table exists for what CTAS cannot say (required columns, identifier fields, a chosen format version) and is one of LakehouseBox's own create paths, so it follows the catalog's default_format_version (the founder's decision, 2026-09-20). Altering a table is the engine's job. - No signup, login, key recovery or account deletion. Those are a human's actions in the account page or the CLI (lhbox signup|login|recover|account), not something an agent should do with someone else's key. - No metadata plane yet (row counts, partitions, freshness, plan_query from research/13 §2): it needs API routes first; the MCP server only ever projects what the API offers. ## Tests ``` /tmp/mcp-venv/bin/python -m pytest platform/mcp/tests -q # 54 tests, no network LHBOX_URL=http://localhost:8080 LHBOX_API_KEY=al_live_... \ /tmp/mcp-venv/bin/python platform/mcp/tests/live_smoke.py # skipped when the key is unset python3 -m pytest platform/cli/tests -q # lhbox mcp install|run: 17 tests, temp HOME ``` The unit tests drive every tool through the MCP layer against an httpx.MockTransport that plays the API, and assert the request shape (method, path, query, JSON body, Authorization, Idempotency-Key) and the error paths. The live smoke creates one catalog, one token and one invitation under the configured principal, then revokes and deletes what the deployed API lets it; run it under a throwaway principal. ## Remote (Streamable HTTP): https://mcp.lakehousebox.com/mcp The same module as a resource server (design and contract: docs/mcp-remote.md). It holds no credential: a host authorises once through the control plane's OAuth 2.1 flow (Client ID Metadata Documents; Dynamic Client Registration as a fallback), a consent creates a token in your Tokens tab, and every request carries that token, which the server verifies against the control plane's JWKS and forwards to the API unchanged. Add it in Claude.ai as a custom connector with the URL above, or claude mcp add --transport http lhbox https://mcp.lakehousebox.com/mcp. A request without a token answers 401 with the protected-resource metadata, which is the spec's own discovery step; the authorization server is the control plane's (https://api.lakehousebox.com/.well-known/oauth-authorization-server, live since 2026-09-20). Container: platform/mcp/Dockerfile, service mcp in the compose files, site https://{$AL_MCP_HOST} in the gateway. ## Architecture # How LakehouseBox stores your data Published from the repository note platform/docs/architecture.md; the numbers come from research/27-storage-tiering-experiment.md (2026-09-21) and are cited by section. Operator switches (the tiering timer, the mirror) are named as such: what they do is in the code, whether they are on is the operator's call. Written 2026-09-22 from the code as deployed and from the measurements in research/27-storage-tiering-experiment.md (cited by section). One host in Germany today; the pieces below are what an engineer evaluating the service needs to know about where bytes live, how they are protected and how they come back. ## One process, two buckets per catalog The store is SeaweedFS (weed mini, our own build): the Iceberg REST catalog, the S3 gateway, the filer (the metadata index) and the volume store run as one process on one Hetzner server. Every catalog you create is two S3 buckets — a table bucket that enforces the Iceberg layout and a blob bucket for files — and each bucket is its own collection of 64 MB volumes, the append-only files SeaweedFS packs objects into. A catalog's isolation is enforced at the catalog (a bucket policy naming its identity) and every engine gets per-table storage credentials that expire; that model is described in the repository's security note (platform/docs/security.md) and the API reference (https://lakehousebox.com/docs/api/), and is not repeated here. ``` engines (DuckDB, PyIceberg, Spark, ...) --S3 + Iceberg REST--> weed mini on al-lake1 | catalog · S3 · filer · volumes | hot set: Hetzner Cloud Volume <--------+--------> cold set: Hetzner Object Storage (replicated by Hetzner on 3 servers) (sealed 64 MB volume files, Falkenstein) ``` ## Hot and cold: where the bytes are New writes always land on the host's Hetzner Cloud Volume, a network block device Hetzner replicates across three physical servers. That is the hot set. A catalog whose volumes have had no write for the quiet period (AL_TIER_QUIET_FOR, default 7 days) has its sealed volumes moved, one file each, to Hetzner Object Storage in Falkenstein; the host keeps only the small index of each moved volume. This is the cold set, and it is why storage stays cheap: Object Storage costs about a ninth of a Volume per gigabyte (§2.11). Three rules follow from how the store works: - Cold reads are slower on first access. Nothing is cached: every read of a cold object is a range request to Object Storage, about 0.2 s to the first byte per file; the first scan of a cold table measured 7–8× slower than a local one, later scans in the same session are as fast as local because the engines cache (§2.4, §6.5). - New writes never touch a cold volume. A moved volume is read-only; the catalog's next write opens a fresh local volume. Active catalogs therefore stay hot on their own. - tier_policy is per catalog. cold (the default) allows tiering; hot keeps a catalog on the Volume for good. The demo catalog and public catalogs are always hot. Set it with lhbox catalog update --tier hot|cold. The tiering job is a timer on the host that runs after maintenance (snapshot expiry and compaction), because bytes deleted from a volume after it is tiered are never reclaimed (§2.7). It is installed with the platform and enabled by the operator; until it is enabled every byte is hot. cold_bytes per catalog appears in lhbox usage when the tier has moved something. ## Encryption at rest Every chunk is encrypted before it reaches a volume (AL_ENCRYPT_VOLUME_DATA, on by default). The per-chunk keys live in the catalog's metadata, not next to the data, so both the Volume and the cold copies in Object Storage hold ciphertext: a downloaded volume file contained no Parquet at all (§6.2). Measured cost: none on writes, about 3 ms per local read. Chunks written before the flag was on stay plaintext until compaction rewrites them. The metadata copy is therefore the keyring, and it is encrypted before it leaves the host (below). ## Recovery - Every hour the metadata — the filer index, every volume's index, the store's configuration and its keys — is archived, encrypted to a key the host does not hold, and copied off-host. From that archive alone a fresh host brings back every cold catalog (the bytes are in Object Storage; the drill took 1.5 s to serve, §2.8). Hot catalogs need the next item. - Every night the hot set is archived off-host with the store stopped for a few seconds. - If the host dies and its Volume survives, the Volume is attached to a new server and the store starts there: measured 25 s from stop to serving, plus provisioning and DNS (§2.9). This is the first move, before any restore. - Control-plane state (accounts, keys, grants) is copied off-host every 5 minutes. Not there yet, labelled roadmap: automatic failover (a host failure is minutes of outage handled by a person or the operations agent), a second location (all of this runs in one Hetzner datacentre; the cold set can be mirrored nightly to another location when that timer is enabled), and a hot standby. ## The limits that shaped this Hetzner Object Storage allows 100 buckets per account, 750 requests per second and 100 TB per bucket, 256 parallel connections per source address, and has no cross-location replication or published durability figure (§6.1). So the cold tier is one bucket per group of roughly 500 catalogs rather than one per catalog, the store speaks HTTP/1.1 to it with a capped connection pool (HTTP/2 there was measured at a tenth of the throughput, §6.5), one server serves about 150 cold requests per second before CPU binds (§6.6), and a nightly mirror of each cold bucket to another Hetzner location is the answer to single-datacentre durability. Growing the hot set is a Volume resize, online (§2.6). ## Self-hosting # Self-hosting LakehouseBox This is the engineering note from the repository, published as written on 2026-09-19. Paths (stack/, deploy/, scripts/) are relative to the platform directory of the source tree, which is not yet published: the licence for the control plane and the CLI is an open decision, as the note says. The managed service at lakehousebox.com is this shape, run by us. Ask hello@lakehousebox.com if you want to run it. Written 2026-09-19 to answer "are we blocking ourselves for a self-hosted version?" The answer today: no, because the managed service is the self-hosted shape run by us. Nothing in the stack knows it is ours. Revised 2026-09-20: the identity provider left the stack; login is the control plane's own, and an external OpenID Connect issuer is the self-hosting option (below). ## What a self-hoster runs The same four containers the laptop and al-lake1 run, from stack/docker-compose.yaml plus the production override: | container | image | role | | weed-init → weed | our private mirror's build of SeaweedFS 4.47-tm.4 (Apache-2.0 upstream 4.47 plus seven patches, listed in platform/upstream/seaweedfs/PATCHES.md; pinned by digest in the compose file) | object store, Iceberg REST catalog, IAM/STS, vending, maintenance | | control-plane | built from control_plane/ (FastAPI, SQLite) | tenants, recipes, quotas, organisations, signup, login (email + password, sessions, the CLI's device flow, our mails), the OAuth 2.1 authorization server for the MCP server | | mcp | built from mcp/ (the official mcp Python SDK) | the remote MCP server (Streamable HTTP at https://mcp./mcp), a resource server that forwards the caller's token to the control plane | | gateway | caddy:2.11-alpine | TLS on api., catalog., s3. + *.s3. (virtual-hosted buckets), mcp. and the website | State is one directory (/data), which is also the backup set (deploy/README.md). Identity is pluggable by configuration (AL_OIDC_ISSUERS, research/18): a company with Okta, Entra, Keycloak or Authentik names its issuer and gets SSO with it, beside or instead of the form login (docs/login.md). Mail for the signup, set-password and reset links is any SMTP sender (AL_SMTP_*). Placement is whatever they set (AL_PROVIDER, AL_REGION). ## The path, as it exists - A host with Docker and a data disk mounted at /data (deploy/host_setup.sh does it for a Hetzner Volume; any disk works). - deploy/render_env.sh writes the server .env; AL_DOMAIN=lake.example.com gives real hostnames: A records for api, catalog, s3, *.s3 and mcp at their DNS (plus the website's, if the gateway serves it; the wildcard wants a DNS-01 certificate, path-style s3. works without it). Size the store by bytes, not slots: weed mini's default caps the volume COUNT at data disk / AL_VOLUME_SIZE_LIMIT_MB (every bucket that has ever been written holds one volume, two per catalog — the default 256 MB gave 76 slots on a 20 GB disk and ran out on us), but the stack sets -volume.max (AL_VOLUME_MAX, default 2000), which lifts that cap; what binds is disk-free, so alert on it (the deploy's alerts warn at 80 % used and page at 90 %; deploy/README.md, "The volume-slot cap"). A Hetzner Volume grows online. Measurements: research/27 §2.1–2.2. - deploy/push.sh (rsync, ./al up, ./al bootstrap, TLS health) — or, without our laptop scripts, cp .env.example .env, edit, ./al up && ./al bootstrap on the host itself. - scripts/test.sh phase1 against their hostnames is the acceptance test they can run. ### Storage settings (optional) | key | what | | AL_VOLUME_MAX | volume-count cap of the store (default 2000; the disk binds instead) | | AL_VOLUME_SIZE_LIMIT_MB | volume size (default 64 MB; seals early so volumes can move to the cold tier) | | AL_ENCRYPT_VOLUME_DATA | chunk encryption at rest, default 1; the per-chunk keys live in the filer metadata, so the metadata backup is the keyring | | HETZNER_S3_* | the Object Storage bucket used as the backup tier and the default cold-tier backend s3.hetzner | | AL_TIER_BACKENDS_SPEC | extra cold-tier buckets rendered into master.toml as s3. (name|bucket|endpoint|key|secret;…); one per ~500 catalogs | | AL_TIER_BACKENDS | which backends the tiering job may use (comma list of s3.; a catalog hashes to one) | | AL_TIER_QUIET_FOR, AL_TIER_FULL_PERCENT | the tiering job's quiet period (default 168h) and fill threshold (default 50) | | AL_TIER_MIRRORS | src@endpoint>dst@endpoint;… pairs the nightly mirror syncs to another location | Every other variable the stack reads, with its default and its reader, is listed in the repository note platform/docs/configuration.md (2026-09-22; grep the code when in doubt). Two timers are optional and installed disabled: the tiering job (deploy/schedule_tier.sh , then systemctl enable --now agentlake-tier.timer once encryption at rest is on) and the mirror of the cold buckets (deploy/schedule_tier_sync.sh ). Without them every byte stays on the data disk. What the tier does and costs: /docs/architecture/ and research/27. ## Bringing your own identity provider (AL_OIDC_ISSUERS) The control plane trusts the issuers named in AL_OIDC_ISSUERS, a JSON list (single-quoted in .env, or compose strips the quotes) of ``` {"issuer": "", "client_id": "", "audience": "", "discovery_url": "", "trust_email": } ``` A verified token from a listed issuer is accepted wherever ours is: the first appearance of an address creates the principal (or activates the pending organisation a signup form reserved for it), /v1/onboard names the organisation, and POST /v1/api-keys mints keys for the person's agents. GET /v1/auth/config lists the issuers, so the account page shows a "Log in with" button for each beside the form (or instead of it, with AL_PASSWORD_LOGIN=false), and lhbox login --issuer runs the OpenID Connect device flow against it instead of ours. What the IdP side needs, whatever the product: a public client (no secret; PKCE S256), the grants authorization_code (the account page), urn:ietf:params:oauth:grant-type:device_code (the CLI) and refresh_token; redirect_uris with /app/ and the loopback prefix http://127.0.0.1:* for the CLI; as an allowed origin (the page calls the token endpoint with fetch); RS256 tokens that carry email and email_verified (or trust_email: true here). Worked example, Rauthy 0.36 (what the managed service ran until 2026-09-20; the scripts that provisioned it, scripts/rauthy_setup.sh and deploy/render_rauthy_env.sh, are in git history before that date). Rauthy behind the gateway on https://auth., PROXY_MODE=true, PUB_URL the public hostname; a client lhbox created over its admin API with the flows above and access_token_alg: RS256. The control plane's line, discovery fetched in-cluster because the public name does not resolve inside the compose network: ``` AL_OIDC_ISSUERS='[{"issuer":"https://auth.example.com/auth/v1","discovery_url":"http://rauthy:8080/auth/v1","client_id":"lhbox","audience":"lhbox"}]' ``` Two things learnt running it: Rauthy emits its issuer with a trailing slash in some places (the control plane matches modulo that slash), and probing paths under /docs/ on it blacklists the source IP for 24 hours. Its registration page and mails cannot be made yours (colours yes, structure and hostname no), which is why the managed service moved the login into the control plane. ## What would block a self-hoster today, honestly - Our deploy scripts assume Hetzner for provisioning (provision.sh) and for the backup tier (HETZNER_S3_*, master.toml). The compose stack does not; the backup upload does (boto3 to any S3-compatible endpoint, so a one-line change). - The Hetzner Volume assumption in docker-compose.prod.yaml is just bind mounts under /data. - weed mini is single-node. HA (PLAN §8) is the multi-process SeaweedFS topology; when we do it for ourselves the self-hosted shape follows. - No packaging: no Helm chart, no single installer. Not needed until someone asks. - License: the control plane and CLI have no licence file yet; that is a decision for the human (the platform's value is the operated service, so a permissive licence on the code costs little). ## Operating a single host: what the deploy scripts now guarantee (2026-09-20) - Every long-running container restarts after a reboot or an OOM kill (restart: unless-stopped); the control plane and the gateway run capability-less on a read-only rootfs (docker-compose.yaml). - The daily backup (deploy/on_host_backup.sh) checks free space before it stops anything, restarts weed from an EXIT trap whatever fails in between, prunes its own archives by count and size, and drops old fs.meta dumps. A restore (deploy/restore.sh) hardens the host, installs the backup, maintenance and self-update timers, and prints a checklist of what it did not do. An archive from before 2026-09-20 carries the retired identity provider's /data/rauthy; it is restored as inert data and nothing is started for it. - Logs our timers write under /data rotate weekly (deploy/on_host_logrotate.sh). - Destroying cloud resources keeps the data Volume unless AL_DELETE_VOLUMES=yes (scripts/cloud_nuke.sh); ./al nuke asks first. ## Keep it that way Rules that keep self-hosting cheap: no hard-coded hostnames (everything through .env), no cloud API in the data path, no dependency on a Hetzner-only service inside the containers, identity through standard OIDC only, and the acceptance suite runnable against any hostname.