--- name: foxdb description: > Query, load and manage data in FoxDB (db.getdot.ai), a managed PostgreSQL-compatible analytical database with DuckDB extensions. Use when the user mentions FoxDB, db.getdot.ai or DOT_DB_TOKEN, or wants to run SQL, import files or URLs, create tables, manage users and grants, or connect a PostgreSQL client or BI tool (psql, psycopg2, dbt, Tableau, Metabase, Grafana, DBeaver, JDBC) to FoxDB through its HTTP API, MCP server or PostgreSQL wire protocol. metadata: author: foxdb version: "1.0" --- # FoxDB FoxDB (https://db.getdot.ai) is a managed analytical database: PostgreSQL SQL with DuckDB extensions, over an HTTPS API (also served as an MCP server) and the PostgreSQL wire protocol. Standard PostgreSQL SQL works; the exceptions are under PostgreSQL Compatibility below. Every query needs a token, `dot__`. Read it from the `DOT_DB_TOKEN` environment variable or a private file outside the repository; never write it into a file the repository tracks. The user gets one by signing in at https://db.getdot.ai (the Connection Hub shows it), or with the GitHub CLI as in the Quick Start of https://db.getdot.ai/llms.txt. ## Quick Start ```bash curl -X POST https://db.getdot.ai/ \ -H "X-GetDot-Key: $DOT_DB_TOKEN" \ -d "SELECT 'hello from FoxDB'" ``` No SDK, no driver, no connection pooling needed. ## Authentication Set the token in the `DOT_DB_TOKEN` environment variable. Four auth methods: 1. **Header** (recommended for agents): ``` X-GetDot-Key: dot__ ``` 2. **Bearer token** (MCP clients, HTTP libraries): ``` Authorization: Bearer dot__ ``` 3. **Basic auth** (curl, HTTP clients; the username is ignored): ``` curl --user 'default:' https://db.getdot.ai/ -d "SELECT 1" ``` 4. **PostgreSQL password** (Tableau, psql, dbt): ``` Host: db.getdot.ai Port: 5432 User: default Password: SSL: required ``` ### Token rotation ```bash # Returns a new token for the calling user; the previous one is invalidated. curl -X POST https://db.getdot.ai/api/tokens/rotate \ -H "X-GetDot-Key: $DOT_DB_TOKEN" ``` Signing up with `POST /api/projects` and a GitHub token returns a token only when it creates the tenant; for an existing tenant the token field is empty. Copy the token from the Connection Hub at https://db.getdot.ai instead. ## MCP for AI agents FoxDB ships a Streamable-HTTP MCP endpoint at `https://db.getdot.ai/mcp`. No npm install, no local process: point your MCP client at the URL with your token in `X-GetDot-Key` or `Authorization: Bearer`. Claude Code: ```bash claude mcp add --transport http --scope user foxdb https://db.getdot.ai/mcp \ --header "X-GetDot-Key: $DOT_DB_TOKEN" ``` Or a project `.mcp.json` (Claude Code reads `${DOT_DB_TOKEN}` from the environment): ```json { "mcpServers": { "foxdb": { "type": "http", "url": "https://db.getdot.ai/mcp", "headers": { "X-GetDot-Key": "${DOT_DB_TOKEN}" } }}} ``` Cursor uses the same shape in `~/.cursor/mcp.json` without `"type"` and with `"${env:DOT_DB_TOKEN}"`. Codex: `[mcp_servers.foxdb]` in `~/.codex/config.toml` with `url = "https://db.getdot.ai/mcp"` and `bearer_token_env_var = "DOT_DB_TOKEN"`. One tool is exposed: `query(sql)`. Responses are returned as `FORMAT JSON` (meta + data + rows) unless the SQL names another format, capped at 32 MB. Credits, budgets, RBAC and the IP allowlist apply as over HTTP. User, role, grant and policy statements run over MCP as over HTTP and PostgreSQL, each as a call of its own; the budget and priority statements run over HTTP only. A user's economy priority applies to MCP calls too. ## HTTP API Send SQL in a POST body to `https://db.getdot.ai/`. The response format depends on the `FORMAT` clause: ```bash # Default: TabSeparated (one row per line, tabs between columns) curl -X POST https://db.getdot.ai/ \ -H "X-GetDot-Key: $DOT_DB_TOKEN" \ -d "SELECT count(*) FROM my_table" # JSON: structured response with metadata curl -X POST https://db.getdot.ai/ \ -H "X-GetDot-Key: $DOT_DB_TOKEN" \ -d "SELECT count(*) FROM my_table FORMAT JSON" ``` ### Response Formats | FORMAT clause | Content-Type | Structure | |---------------|-------------|-----------| | *(default)* | `text/tab-separated-values` | Rows separated by `\n`, columns by `\t` | | `FORMAT JSON` | `application/json` | `{"meta": [{name, type}...], "data": [{row}...], "rows": N}` | | `FORMAT JSONEachRow` | `application/x-ndjson` | One JSON object per line | | `FORMAT CSV` | `text/csv` | CSV with header row | Always use `FORMAT JSON` when you need structured data. The `meta` array gives column names and types. ### Response headers - `X-Queue-Ms`: time the query waited before its response started: for admission, in the economy queue, for a query capsule, for the tenant's writer (ms). Waiting is never billed (see Cost). Useful for SLA monitoring. - `X-Priority: economy`: set when the request ran at economy priority (billed at half price). - `X-Rows-Affected`: rows changed by an INSERT, UPDATE, DELETE or COPY. - `X-FoxDB-SQLSTATE`: the PostgreSQL error code of a failed query, when one applies. - `Retry-After`: seconds to wait before retrying, sent with a rate-limit 429 and with memory backoff. These are HTTP response headers; PostgreSQL connections report errors as SQLSTATEs instead. Default TSV output writes NULL as `\N`. ## Data Loading Four ways to get data in. Pick by shape and throughput: | Path | When | Throughput | |------|------|------------| | HTTP `INSERT ... VALUES` | app inserts, webhooks | 500–1500 rows/s | | pgwire parameterized `INSERT` | JSON/TEXT with awkward content | 200–800 rows/s | | pgwire `COPY FROM STDIN` | bulk load, ETL seed | **100 K–1 M rows/s** | | URL reads / lakehouse scans | `read_csv` / `read_parquet` / `read_json` / `iceberg_scan` / `delta_scan` | network-bound | Read directly from a URL (http, https, s3, gs, az, r2; globs work in paths): ```sql CREATE TABLE events AS SELECT * FROM read_csv_auto('https://example.com/events.csv'); CREATE TABLE logs AS SELECT * FROM read_parquet('s3://bucket/logs/*.parquet'); CREATE TABLE api AS SELECT * FROM read_json_auto('https://api.example.com/data.json'); INSERT INTO events SELECT * FROM read_csv_auto('https://example.com/more.csv'); -- Iceberg / Delta Lake CREATE TABLE orders AS SELECT * FROM iceberg_scan('s3://bucket/warehouse/orders'); CREATE TABLE payments AS SELECT * FROM delta_scan('s3://bucket/payments'); ``` Your own S3 bucket: create a secret, then read. Over HTTP, send both statements in one request body; a later request can run in a different engine process without the secret: ```sql CREATE SECRET my_s3 ( TYPE S3, KEY_ID 'AKIA…', SECRET '…', REGION 'us-east-1', SCOPE 's3://my-bucket/' ); SELECT * FROM read_parquet('s3://my-bucket/logs/*.parquet'); ``` Query a remote Postgres live with the scan functions: ```sql SELECT count(*) FROM postgres_scan( 'host=mypg.example.com port=5432 user=reader password=… dbname=events', 'public', 'orders'); ``` **`ATTACH` is limited to `':memory:'`.** Attaching a database *file* (a DuckDB file, a SQLite file, a second DuckLake) and `ATTACH … (TYPE postgres)` are refused: the operand of a file `ATTACH` is not reliably a path, and a transaction spanning two databases is one DuckDB refuses to commit. Use `read_parquet` / `read_csv` for files and `postgres_scan` for PostgreSQL (`postgres_query` needs an attached database, so it is refused too). `ATTACH ':memory:' AS ns` still works for a scratch catalog namespace. Files your SQL writes (`COPY … TO`, `EXPORT DATABASE`, `write_blob`) go in your workspace, `/data/tenants//files/`, which is also the working directory, so `COPY t TO 'out.parquet'` lands there. Remote destinations (`s3://…`) are unaffected. `INSTALL`, `LOAD`, and `enable_logging` are not available: FoxDB owns the extension inventory and engine logging. For Snowflake / BigQuery: export to Parquet on cloud storage, then `read_parquet('s3://…')` or `read_parquet('gs://…')`. See [references/data-loading.md](references/data-loading.md) for every path with throughput notes, language examples, idempotency patterns, and limits. ## Query Management Cancel a runaway or inspect what's currently running per tenant. These endpoints need an admin token. The active list and cancel cover HTTP and MCP queries only; the recent history covers every interface, PostgreSQL included. ```bash # List active queries (id, user_name, sql, started_at, elapsed_ms) curl -H "X-GetDot-Key: $DOT_DB_TOKEN" https://db.getdot.ai/api/queries # Cancel a query by id curl -X POST -H "X-GetDot-Key: $DOT_DB_TOKEN" \ https://db.getdot.ai/api/queries//cancel # Recent history (last 20: user_name, sql, started_at, duration_ms, status, row_count, cpu_ms; # ?limit= up to 200; a read-only token sees its own statements) curl -H "X-GetDot-Key: $DOT_DB_TOKEN" "https://db.getdot.ai/api/queries/recent?limit=50" ``` `EXPLAIN` works over SQL (`EXPLAIN SELECT ...`, `EXPLAIN ANALYZE SELECT ...`). The `/api/explain` endpoint returns structured output for UI consumers. ## Schema Discovery All schema operations are standard SQL: ```sql SHOW TABLES; -- list all tables SHOW TABLES FROM schema_name; -- list tables in a schema DESCRIBE table_name; -- show columns and types SHOW SCHEMAS; -- list schemas SHOW SNAPSHOTS; -- list data versions SHOW HISTORY FOR table_name; -- changes within the retained snapshots ``` ## SQL Dialect Standard PostgreSQL SQL, plus DuckDB quality-of-life extensions: - `GROUP BY ALL` -- automatically group by all non-aggregate columns - `SELECT * EXCLUDE (col1, col2)` -- select all columns except specified - `QUALIFY` -- filter window function results (like HAVING for aggregates) - `PIVOT` / `UNPIVOT` -- reshape data - `unnest()`, `list_value()`, `array_agg()` -- array operations - `struct_pack()`, `{'key': 'val'}` (struct), `MAP {'key': 'val'}` -- structured types - `read_csv_auto()`, `read_parquet()`, `read_json_auto()` -- import from URLs - `approx_count_distinct()`, `quantile_cont(col, 0.95)` -- approximate aggregations See [references/sql-dialect.md](references/sql-dialect.md) for the full reference and Snowflake migration mappings. ## Time Travel Writes commit as immutable snapshots (writes that arrive together can share one), kept for 24 hours, or one hour on a very busy catalog: ```sql SELECT * FROM my_table AT (VERSION => 5); -- version from SHOW SNAPSHOTS SELECT * FROM my_table AT (TIMESTAMP => now() - INTERVAL '1 hour'); SHOW SNAPSHOTS; SHOW HISTORY FOR my_table; ``` See [references/time-travel.md](references/time-travel.md) for details. ## PostgreSQL Wire Protocol Same token, same SQL, same data -- on port 5432 with SSL: ``` psql "postgresql://default:@db.getdot.ai:5432/db?sslmode=require" ``` Works with Tableau, Metabase, Grafana, dbt, DBeaver, and any PostgreSQL client. Each tenant has one writer. A writable PostgreSQL session runs every statement on it, reads included, one at a time. Every statement is billed CPU time, and the time it waits for the writer is never billed (see Cost). Read-only sessions run their reads in parallel in separate processes. For analytical or BI connections, use the token of a user with a role (`IN ROLE`): its PostgreSQL sessions are read-only whatever the role grants, so a role's write privileges apply over HTTP and MCP only. If an existing admin credential is intentionally query-only for the whole connection, opt it into the isolated read-only scheduler at startup: ```bash PGOPTIONS='-c default_transaction_read_only=on' \ psql "postgresql://default:@db.getdot.ai:5432/db?sslmode=require&target_session_attrs=read-only" ``` That mode is immutable for the connection. Writes and attempts to switch back to read-write fail with SQLSTATE `25006`; the gateway also verifies the selected worker's storage-read-only status before completing the connection. For dbt, use `dbt-postgres` adapter. All materializations (view, table, incremental, ephemeral, snapshot) work. ### Connection settings for sustained workloads Migrations and bulk loads can sit idle between statements. Enable TCP keepalive on the client so dead peers are detected promptly without punishing legitimate idle transactions: - **psycopg2 / psycopg3**: `keepalives=1 keepalives_idle=60 keepalives_interval=15 keepalives_count=4` - **pgx**: append `tcp_user_timeout=30000` to the connection string - **JDBC**: `tcpKeepAlive=true` Use PostgreSQL's standard `statement_timeout` to bound server work. The startup form protects the first bootstrap/catalog command as well as later queries; `SET`, `SHOW`, and `RESET` also work per session. The default is 1 hour, `0` removes the limit, and expiration returns SQLSTATE `57014`: ```bash PGOPTIONS='-c statement_timeout=5s' \ psql "postgresql://default:@db.getdot.ai:5432/db?sslmode=require" ``` `statement_timeout` begins with a SQL command, matching PostgreSQL. TLS, authentication, and worker acquisition remain covered by connection-level timeouts instead. For compatibility with older FoxDB clients, `pg_statement_timeout_millis` is accepted over pgwire as a session-local alias for `statement_timeout`; it never mutates DuckDB's setting of the same name. DuckDB's other outbound `postgres_scanner` controls (protocol selection, scan tuning, and future `pg_*` settings) remain process-global and FoxDB-owned. See [references/data-loading.md](references/data-loading.md) for bulk-load paths (`COPY FROM STDIN` is the fastest, 100× individual INSERTs), escaping rules for JSON/TEXT columns, and S3 credential handling. ## PostgreSQL Compatibility FoxDB is **PostgreSQL-compatible** on the wire and for standard SQL. Any PostgreSQL client can connect; most SQL runs unmodified, with the limitations below. **Verified:** psycopg2, psql, dbt-postgres, SQLAlchemy, JDBC, Metabase, Tableau, Grafana, DBeaver, Airbyte. ### What you can expect - Standard DDL, DML, upserts (INSERT ON CONFLICT), parameterized queries ($1, $2) - All common types: JSONB, UUID, TIMESTAMP, arrays, TEXT, VARCHAR, BOOLEAN, etc. - Schema introspection: pg_catalog, information_schema; tools discover your schema automatically - COPY FROM STDIN for bulk loading (100-500x faster than individual INSERTs) - SET/SHOW, BEGIN/COMMIT: accepted. GRANT, REVOKE, CREATE ROLE/USER/POLICY change permissions over PostgreSQL as over HTTP (see references/permissions.md) - CREATE INDEX, VACUUM and COMMENT ON: accepted and ignored - Integer division truncates like PostgreSQL: `5/2 = 2`, in statements written in PostgreSQL SQL. A statement that uses DuckDB-only syntax (`GROUP BY ALL`, `EXCLUDE`, `QUALIFY`, `PIVOT`) runs as DuckDB SQL, where `5/2 = 2.5`; write `5 // 2` for integer division in any statement. ### Limitations to know - PRIMARY KEY is accepted but not enforced on regular tables. UNIQUE, FOREIGN KEY and CHECK constraints are refused. - No `SERIAL`, `IDENTITY` or sequences (SQLSTATE `0A000`): generate IDs client-side. Column defaults must be literals, `now()`, or one of `CURRENT_TIMESTAMP`, `CURRENT_DATE`, `CURRENT_TIME`, `LOCALTIMESTAMP`, `LOCALTIME`; `DEFAULT gen_random_uuid()` is refused. - Each statement autocommits. Explicit `BEGIN`/`COMMIT`/`ROLLBACK` are acknowledged but don't group statements: a failed query inside a "transaction" doesn't roll back prior ones. `SAVEPOINT` fails with SQLSTATE `0A000`. - Over PostgreSQL, `SET timezone` is honored for the session (default UTC), and `SET search_path` for admin sessions. Other PG-only `SET` commands (`datestyle`, `client_encoding`, etc.) are accepted as no-ops. - All dbt materializations supported (view, table, incremental, ephemeral, snapshot). ### Cold start First query to an idle tenant spawns a worker process and initializes the DuckDB pool: typically **~200 ms** on warm catalogs, up to ~2 s on a brand-new tenant. Subsequent queries reuse the same worker (hot path). Idle tenants stay warm for hours; no manual keep-alive needed. ## Access Control Two reference docs cover the full spec: - [references/permissions.md](references/permissions.md) covers the grant/policy model: table- and column-level GRANTs, row-level policies, schema grants, composition rules, and worked recipes. - [references/rbac.md](references/rbac.md) covers the operational side: users, roles, budgets, priority, and query history via `meta.query_history`. To set up teams and AI agents (a role per team, a user and token per person, Dot group or agent, agents that read no personal data or secrets), follow the Teams and AI agents recipe in [references/permissions.md](references/permissions.md), also at https://db.getdot.ai/docs#teams-and-agents. When you act as such an agent, write a column- or row-restricted table by its bare name (`customers`, not `main.customers`), and find tables with `SHOW TABLES` and `DESCRIBE t`: without `SELECT ON *`, `pg_catalog` and `information_schema` are refused. ## Clustering and partitioning `ALTER TABLE t CLUSTER BY (a, b)` (Snowflake form) and `ALTER TABLE t SET (cluster_by = 'a, b')` (PostgreSQL storage-param form) both physically sort rows on write so range predicates can prune entire Parquet rowgroups. Same goes for `… PARTITION BY (year(ts))` / `SET (partition_by = …)`. See [references/clustering.md](references/clustering.md) for the supported shapes, transforms, inspection via `foxdb_clustering_info`, the rename idiom for re-clustering existing data, and why FoxDB doesn't accept PG declarative partitioning (`PARTITION BY RANGE/LIST/HASH`). ## DuckLake quirks and migration patterns See [references/LESSONS.md](references/LESSONS.md) for quirks encountered during real migrations (no sequences, DEFAULT now() caveats, ALTER TABLE ADD COLUMN history, FORMAT JSON null, integer division) and the idioms that work cleanly: DELETE-before-INSERT for composite-key upserts, NULL-gated UPDATE for resumable jobs, and drop+recreate for schema evolution. ## Common Patterns ```sql -- Create table from query CREATE TABLE summary AS SELECT date_trunc('month', ts) AS month, count(*) AS events FROM raw_events GROUP BY ALL; -- Insert data INSERT INTO events (ts, event, user_id) VALUES ('2026-04-01', 'signup', 42); -- Parameterized queries (PG wire only) SELECT * FROM events WHERE user_id = $1; ``` ## Cost - Compute: $0.12 per core-hour of CPU time, with a 500 core-ms minimum per executed statement. Prefer fewer, larger statements. - Waiting is never billed. A statement that waited at least 1 second pays half price for as much of its run as it waited: a 100 ms statement that waited 1 s is half price; a one-minute statement that waited 1 s has one second of its minute at half price. `queue_ms` in `meta.query_history` shows the wait. - Memory: $0.02 per GiB-hour of the memory a statement needs above what its process already holds, while it executes (2 GiB for one second is $0.000011). No minimum; caches kept between statements are never billed; writes are not billed for memory yet. `meta.query_history` shows `peak_memory_bytes`, `memory_gib_ms` (GiB times its run in ms) and `memory_ms`, the charge in the unit of `cpu_ms`: a statement costs `cpu_ms` plus `memory_ms`. - A statement cancelled before it starts costs nothing; one cancelled while it runs is billed for its run up to the cancel. The time an answer takes to reach the client is not billed, even when it stops reading. A write the writer had already taken when it was cancelled still applies, and is billed then like any write that waited. - Economy is always half price, runs when the host is idle, and has no maximum wait. Use it for HTTP requests whose body starts with `SET PRIORITY = 'economy';` followed by the statement, or for every statement (HTTP, PostgreSQL, MCP) of a user an admin set with `ALTER USER etl SET PRIORITY = economy`. It ends early only if the client disconnects or cancels, or with HTTP 503 and `Retry-After: 1` when the gateway restarts, as it does during a deploy; up to 256 economy statements per tenant wait or run at a time (then HTTP 429). Through MCP a `SET PRIORITY` prefix fails. - Storage: $40 per TiB-month, charged daily, including Parquet files retained for time travel. - New tenants start with a one-time $10 credit. `SET BUDGET = 100` caps the tenant's compute spend (CPU and memory) per calendar month (UTC) and `ALTER USER analyst SET BUDGET = 25` one user's, on top of the tenant's. Both hold on every protocol: HTTP 429, SQLSTATE 53400 over PostgreSQL, an MCP tool error. Changing one needs an admin token; `SHOW BUDGET` works for every user. See [references/rbac.md](references/rbac.md). - Buying credits, auto-recharge and the billing portal (`POST /api/billing/checkout`, `POST /api/billing/auto-recharge`, `GET /api/billing/portal`) need an admin token; `GET /api/billing` shows the balance to every user. ## Error Codes | HTTP Status | Meaning | |-------------|---------| | 200 | Success | | 400 | SQL syntax, binder or catalog error | | 401 | Missing or invalid token | | 402 | No credits remaining; add credits in the Connection Hub (over PostgreSQL: SQLSTATE 53400) | | 403 | Forbidden: RBAC grant missing, readonly token, or tenant IP allowlist | | 409 | Write conflict (SQLSTATE 40001). FoxDB never replays a statement; retry only if it is safe to repeat | | 413 | Request body larger than 100 MB | | 429 | Rate limit (500 requests/s per user with a burst of 1,000; 2,000/s per tenant; honor `Retry-After`), a monthly budget spent (over PostgreSQL: SQLSTATE 53400), more than 500 active or queued queries for the tenant, or 256 of its economy requests already waiting or running | | 500 | Any other error (`Code: 999.`), including statements FoxDB refuses (file `ATTACH`, `INSTALL`/`LOAD`, a file path outside your workspace) and type conversion errors | | 503 | Out of memory, server busy, heavy-query queue full, or a gateway restart ended an economy request's wait (with `Retry-After: 1`) | | 504 | Query cancelled: the client disconnected | | 507 | Tenant storage quota exceeded (SQLSTATE 53100) | Error bodies are plain text, most of them `Code: NNN. `, where NNN is usually but not always the HTTP status: branch on the status and `X-FoxDB-SQLSTATE`, not on the body. SQL errors over pgwire are mapped to standard PostgreSQL SQLSTATEs (42601 syntax, 42P01 undefined table, 22P02 bad literal, 53200 out of memory, 53300 capacity, 53400 no credits or a monthly budget spent, 40001 write conflict, 57014 cancelled, 0A000 unsupported feature, etc.). ## Links - Endpoint: https://db.getdot.ai - Full docs: https://db.getdot.ai/llms.txt - Health: https://db.getdot.ai/health - This skill, to install or update it for Claude Code: `mkdir -p ~/.claude/skills && curl -fsSL https://db.getdot.ai/skills/foxdb.tar.gz | tar -xz -C ~/.claude/skills`