Docs

Get started

FoxDB is a PostgreSQL-compatible analytical database for you and your AI agents. One token works over HTTPS, PostgreSQL and MCP. New databases start with $10 of free credit, no card.

1. Get a token

   Sign in with GitHub. You get a database and an API token right away.

   Sign in with GitHub

   Or from a terminal, with the GitHub CLI:

   export DOT_DB_TOKEN=$(curl -s -X POST https://db.getdot.ai/api/projects \
     -H "Authorization: Bearer $(gh auth token)" | grep -o '"token":"[^"]*"' | cut -d'"' -f4)

   This returns a token only when it creates your database. If your GitHub account already has one, the token comes back empty: sign in and copy it from the Connection Hub.

2. Run a query

   Every database can read the shared datasets (billed as usual). Here, 31 million UK property sales:

   curl -X POST https://db.getdot.ai/ \
     -H "X-GetDot-Key: $DOT_DB_TOKEN" \
     -d "SELECT county, round(avg(price)) AS avg_price, count(*) AS sales
         FROM uk.uk_price_paid
         WHERE date >= '2024-01-01'
         GROUP BY county ORDER BY avg_price DESC LIMIT 5 FORMAT JSON"

3. Connect your agent or your tools

   Coding agentsClaude Code, Cursor, Codex Toolspsql, dbt, Metabase, Python

Coding agents

Give your agent two things: the skill, so it knows FoxDB's SQL and limits, and the MCP server, so it can run SQL.

The skill

An agentskills.io skill: SKILL.md plus references. Install it for Claude Code, and run the same line again to update it:

mkdir -p ~/.claude/skills && curl -fsSL https://db.getdot.ai/skills/foxdb.tar.gz | tar -xz -C ~/.claude/skills

Other agents that read agentskills.io skills take the same archive, extracted into their own skills directory. The files are also served one by one, starting at /skills/foxdb/SKILL.md.

The MCP server

Streamable HTTP at https://db.getdot.ai/mcp, with your token in the X-GetDot-Key header or as Authorization: Bearer <token>. Claude Code:

claude mcp add --transport http --scope user foxdb https://db.getdot.ai/mcp --header "X-GetDot-Key: $DOT_DB_TOKEN"

Or a project file, which you can commit: it names ${DOT_DB_TOKEN}, read from the environment, instead of the token.

{"mcpServers": {"foxdb": {"type": "http", "url": "https://db.getdot.ai/mcp",
  "headers": {"X-GetDot-Key": "${DOT_DB_TOKEN}"}}}}

Cursor and Codex:

{"mcpServers": {"foxdb": {"url": "https://db.getdot.ai/mcp",
  "headers": {"X-GetDot-Key": "${env:DOT_DB_TOKEN}"}}}}

[mcp_servers.foxdb]
url = "https://db.getdot.ai/mcp"
bearer_token_env_var = "DOT_DB_TOKEN"

Keep the token in a private file or an environment variable, never in the repository. --scope user stores it in your own ~/.claude.json.

The server has one tool, query(sql), which returns FORMAT JSON unless the SQL names another format. Results are 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.

Connect tools

One token, the same SQL and the same data on every interface.

psql

psql "postgresql://default:$DOT_DB_TOKEN@db.getdot.ai:5432/db?sslmode=require"

dbt

my_project:
  target: prod
  outputs:
    prod:
      type: postgres
      host: db.getdot.ai
      port: 5432
      user: default
      pass: "dot_YOUR_TOKEN"
      dbname: db
      schema: main
      threads: 4

All dbt materializations (view, table, incremental, ephemeral, snapshot) work.

Tableau, Metabase, Grafana

Connector:  PostgreSQL
Server:     db.getdot.ai
Port:       5432
Database:   db
Username:   default
Password:   <your token>
SSL:        Require

Connect dashboards with a read-only session so that their reads run in parallel.

Python

import os
import httpx

r = httpx.post(
    "https://db.getdot.ai/",
    headers={"X-GetDot-Key": os.environ["DOT_DB_TOKEN"]},
    content="SELECT count(*) AS sales FROM uk.uk_price_paid FORMAT JSON",
)
r.raise_for_status()
print(r.json()["data"])

No SDK or driver needed: any HTTP client works. Over HTTP, put the token in X-GetDot-Key or Authorization: Bearer (all methods under HTTP API).

Load data

One line of SQL loads a file from a URL or a bucket, or a table from PostgreSQL. No staging, no pipeline.

From a URL

-- CSV over HTTPS
CREATE TABLE events AS SELECT * FROM read_csv_auto('https://example.com/events.csv');

-- Parquet from S3
CREATE TABLE logs AS SELECT * FROM read_parquet('s3://bucket/logs/*.parquet');

-- JSON
CREATE TABLE api_data AS SELECT * FROM read_json_auto('https://api.example.com/data.json');

-- Append to an existing table
INSERT INTO events SELECT * FROM read_csv_auto('https://example.com/more.csv');

http://, https://, s3://, gs:// and r2:// work, and so do globs. Azure Blob Storage (az://) does not.

Private S3 buckets

Create a secret with your keys and read in the same request: each HTTP request can run in a different engine process, so a secret is not carried over to later requests.

CREATE SECRET my_s3 (
  TYPE S3, KEY_ID 'AKIA...', SECRET '...', REGION 'us-east-1',
  SCOPE 's3://my-bucket/'
);
CREATE TABLE data AS SELECT * FROM read_parquet('s3://my-bucket/data/*.parquet');

From PostgreSQL

Read a table from a publicly reachable PostgreSQL server live with postgres_scan, then filter and aggregate in FoxDB. ATTACH is limited to ':memory:', so ATTACH ... (TYPE postgres) is refused, and so is postgres_query, which needs an attached database.

CREATE TABLE orders AS SELECT * FROM postgres_scan(
  'host=mypg.example.com port=5432 user=reader password=... dbname=events connect_timeout=5',
  'public', 'orders');

Bulk loads with COPY

Over PostgreSQL, COPY FROM STDIN buffers the rows and loads them in a single transaction, 100-500x faster than single INSERTs. Use it for any bulk load, including from Airbyte, dlt and other ETL tools.

\copy my_table FROM 'data.csv' WITH (FORMAT CSV, HEADER)

INSERT

curl -X POST https://db.getdot.ai/ \
  -H "X-GetDot-Key: $DOT_DB_TOKEN" \
  -d "INSERT INTO events VALUES ('2026-04-01', 'signup', 42)"

Query

Output formats

Over HTTP, results come back tab-separated unless the SQL ends with a format:

Schema discovery

SHOW SCHEMAS;
SHOW TABLES;
SHOW TABLES FROM tpcds;
DESCRIBE my_table;
SELECT * FROM information_schema.columns WHERE table_name = 'my_table';

Reads, writes and read-only sessions

Each tenant has one writer: writes and DDL run one statement at a time. Reads run in parallel, each in its own read-only process. HTTP and MCP send reads there automatically.

Over PostgreSQL, a writable session runs every statement on the writer, reads included. Connect dashboards and BI tools with a read-only session so their reads run in parallel: use the token of a user with a role (its PostgreSQL sessions are read-only whatever the role grants), or open the session with default_transaction_read_only=on. A write on a read-only session fails with SQLSTATE 25006.

psql "postgresql://default:$DOT_DB_TOKEN@db.getdot.ai:5432/db?sslmode=require&options=-c%20default_transaction_read_only%3Don"

Manual compaction

Background maintenance merges small Parquet files on its own. For a table that has collected many small files, you can merge them yourself:

CALL ducklake_merge_adjacent_files('db', 'my_table');

For a table outside the main schema, add schema => 'my_schema'.

Stream tables

For tables that take upserts or many small writes at once. Declare a primary key, and reads see the latest row per key.

FoxDB stores tables as append-only Parquet under DuckLake. That suits analytics (immutable history, columnar compression, snapshot isolation), but on a growing regular table every INSERT ... ON CONFLICT DO UPDATE rewrites a Parquet file. A stream table durably group-commits concurrent small writes into an append-only raw shadow with a dedup view on top, and background compaction folds the shadow periodically.

When to use them

• Use them for tables that receive INSERT ... ON CONFLICT DO UPDATE traffic: tracking, event streams, session state, status updates.
• Use them when many writers hit the same table at once, such as worker pools or real-time ingestion.
• Skip them for INSERT-only logs (appending is already fast) and small reference tables.
• Skip them when you need every historical version of a row: a stream table exposes the latest value per key.

Create one

-- Single-column key
CREATE TABLE events (
    id          UUID PRIMARY KEY,
    occurred_at TIMESTAMP,
    user_id     INTEGER,
    status      VARCHAR,
    payload     JSON
) WITH (foxdb.engine='stream');

-- Composite key (any combination of columns)
CREATE TABLE tracking (
    message_id  VARCHAR,
    region      VARCHAR,
    start_time  TIMESTAMP,
    status      VARCHAR,
    PRIMARY KEY (message_id, region, start_time)
) WITH (foxdb.engine='stream');

The primary key is the idempotency key: distinct keys keep distinct events, and a repeated key exposes its latest value. replacing is still accepted as an alias, but new DDL should use stream.

Write to it like any PostgreSQL table; the dedup is transparent:

INSERT INTO events (id, occurred_at, user_id, status, payload)
VALUES ('0b6f3c2e-6d1a-4c8e-9f3b-2a7d5e1c9b40', NOW(), 42, 'started', '{"source":"web"}')
ON CONFLICT (id) DO UPDATE SET
  occurred_at = EXCLUDED.occurred_at,
  user_id = EXCLUDED.user_id,
  status = EXCLUDED.status,
  payload = EXCLUDED.payload;

SELECT count(*) FROM events;  -- reads through the dedup view

Convert an existing table

A phased migration converts a regular table without downtime: writes and reads keep flowing through the source while it runs.

OPTIMIZE TABLE events SET KEY = (id)
  WITH (migration='phased', cursor='occurred_at');

-- Watch progress:
SELECT table_name, state FROM _foxdb_keyed_meta;
-- migrating → migrating_dual_write → swapping → active

The cursor column must exist and be monotonically orderable (TIMESTAMP, INTEGER, BIGINT or ISO-8601 VARCHAR). Migration throughput on production hardware: about 7 million rows a minute. Abort a migration before its cutover with:

ROLLBACK MIGRATION events;

Behavior

• Reads always see the latest value for each key. The dedup view joins the raw rows against max(_seq) per key and filters tombstones; this happens automatically when you SELECT FROM events.
• Durable small writes: on PostgreSQL-catalog tenants, prepared single-row inserts like the one above take a durable group-commit path: FoxDB acknowledges only after PostgreSQL WAL has the row, then publishes many rows to DuckLake together. A later read sees every write acknowledged before it. This covers only active keyed tables and whole-row replacements FoxDB can prove from the statement; complex expressions, predicates, RETURNING, DO NOTHING, literal or multi-row SQL and partial updates take the regular path. Use COPY or an explicit multi-row INSERT for bulk loads.
• DELETE writes a tombstone, which the dedup view filters out. DELETE FROM events WHERE id = ... works as expected.
• UPDATE writes a new raw row with the updated values; the dedup view picks it as the latest.
• Backpressure: the durable small-write tail is bounded. If publication cannot keep up, new eligible writes return SQLSTATE 53300 so clients can retry, without risking unbounded RAM or disk growth.
• Cutover: during the brief swap step of a phased migration, about 1-2% of in-flight writes may see a transient binder error. Their write already reached the raw shadow, so the data is in the dedup view; clients that retry see no net errors.

Time travel

Writes commit as immutable snapshots; writes that arrive together can share one. Query any table as it was at any point within the retention window.

Query the past

-- By version number (from SHOW SNAPSHOTS)
SELECT * FROM my_table AT (VERSION => 5);

-- By timestamp (within the retention window)
SELECT * FROM my_table AT (TIMESTAMP => now() - INTERVAL '1 hour');

Snapshots

SHOW SNAPSHOTS;
SHOW SNAPSHOT;    -- current version

Change history

Every insert, update and delete over the retained snapshots, or between two snapshot versions. This works on regular tables; a stream table is a view and has no change feed.

SHOW HISTORY FOR my_table;
SELECT * FROM db.table_changes('my_table', 10, 20);
SELECT * FROM db.table_insertions('my_table', 10, 20);
SELECT * FROM db.table_deletions('my_table', 10, 20);

Schema evolution

Add, drop or rename columns without rewriting data files:

ALTER TABLE events ADD COLUMN region VARCHAR;
ALTER TABLE events DROP COLUMN old_field;
ALTER TABLE events RENAME old_name TO new_name;

Retention

Snapshots are kept for 24 hours. Maintenance expires older ones, and on a very busy catalog it can shorten the window to one hour. A version or timestamp outside the window fails with "No snapshot found". FoxDB sets retention and Parquet compression (zstd): changing them with db.set_option has no lasting effect. Retained snapshots keep their Parquet files, which count toward billed storage.

SHOW OPTIONS;

Pricing

You pay for the CPU time and memory your statements use, by the millisecond, and for the data you keep. An idle database costs only its storage.

New tenants start with a one-time $10 credit, about 83 core-hours. No card. Add credits in the Connection Hub, from $10. Buying credits, auto-recharge and the billing portal need an admin token; GET /api/billing shows the balance to every user.

How compute is metered

• CPU time: actual CPU core-time, with a 500 core-millisecond minimum per executed statement, on every protocol and session. A statement without a CPU measurement is billed its run time instead.
• Memory: the memory a statement needs above what its process already holds, for as long as it executes: 2 GiB for one second costs $0.000011. There is no minimum, and memory a process keeps between statements, such as its caches, is never billed. Reads are metered over HTTP, MCP and read-only PostgreSQL sessions; writes are not billed for memory yet. The wait discount and economy apply to it as to CPU time.
• Waiting is never billed. A statement that waited at least 1 second, for admission, the economy queue or the tenant's writer, pays half price for as much of its run as it waited: a 100 ms statement that waited 1 s is half price, and a one-minute statement that waited 1 s has one second of its minute at half price. Query history shows each statement's wait in queue_ms.
• Cancelling: a statement cancelled before it starts costs nothing, and one cancelled while it runs is billed for its run up to the cancel. A write the writer had already taken when you cancelled it still applies, and is billed then like any write that waited.
• Delivery: the time an answer takes to reach you is not billed, even when you stop reading it.
• Storage is all Parquet data your tenant keeps, including files retained for time travel and files awaiting cleanup.

Economy priority (half price)

Run nightly ETL, batch aggregations, exports and backfills at half price. Economy is chosen per request over HTTP, or per user on every protocol.

Per request: start the request body with SET PRIORITY = 'economy'; and put the statement after it, in the same request. The prefix is HTTP only; through MCP it fails.

curl -X POST https://db.getdot.ai/ \
  -H "X-GetDot-Key: $DOT_DB_TOKEN" \
  -d "SET PRIORITY = 'economy'; SELECT count(*) FROM big_table"

Per user: an admin sets a user's priority, and every statement from that user, over HTTP, PostgreSQL or MCP, runs at economy priority until it is set back. The setting stays with the user; a user dropped and created again starts at standard.

ALTER USER etl SET PRIORITY = economy;
ALTER USER etl SET PRIORITY = standard;

• Price: $0.06 per core-hour and $0.01 per GiB-hour of memory (half of standard, 250 core-ms minimum), whether or not it waits. Waiting earns it no further discount: half price is the lowest price.
• When it runs: when the host is idle: fewer than half of the execution slots busy, a 1-minute load average of at most half the CPU count, and at least half of the memory free. Economy statements are admitted one at a time, in arrival order across all tenants.
• No maximum wait: a statement waits as long as the host stays busy, and nothing is billed while it waits. It ends early only if you disconnect or cancel it (over PostgreSQL, a statement_timeout you set ends it too), or when the gateway restarts (for example during a deploy), which answers HTTP 503 with Retry-After: 1 (SQLSTATE 57P01): retry then. A tenant can have 256 economy statements waiting or running; more are refused with HTTP 429 (SQLSTATE 53300). Do not use it for work with a deadline.
• Over PostgreSQL a waiting statement looks like a slow one. It holds no server resources, and every two minutes it sends a NOTICE so that proxies keep the quiet connection open.
• Headers (HTTP): the response carries X-Priority: economy; X-Queue-Ms includes the idle wait.

Budgets

A budget caps compute spend, CPU and memory, in US dollars per calendar month (UTC). Set one for the whole tenant, and one for any user on top of it.

SET BUDGET = 10.00;                   -- the tenant, $10 a month
ALTER USER analyst SET BUDGET = 2.50; -- one user, $2.50 a month
SHOW BUDGET;                          -- budgets, spend, remaining, reset date
ALTER USER analyst SET BUDGET = 0;    -- 0 removes a budget
SET BUDGET = 0;

• Both apply: a user is refused once either the user's or the tenant's budget is spent, and what the user spends counts toward the tenant's budget too. Storage charges never count.
• Every protocol: HTTP, PostgreSQL and MCP check the budgets and the credit balance before each statement starts. A spent budget answers HTTP 429, SQLSTATE 53400 over PostgreSQL, or an MCP tool error; the message names the budget and the day it resets.
• Monthly: spend starts again at 00:00 UTC on the first day of each month. It is kept in FoxDB's database, so restarts and deploys do not reset it.
• Running statements finish: a statement already running when a budget is reached completes and is billed. Usage usually counts within a second, so a statement or two started in that time may also run; later ones are refused.
• Who can change them: changing a budget needs an admin token (a readonly token gets HTTP 403). SHOW BUDGET works for every user and shows the tenant's budget and, if you have one, your own. Budget statements still work when a budget is spent or the credits run out.
• Syntax: an amount in dollars from 0 to 1,000,000, after = or TO. Send each budget statement over HTTP or from the console, as a request of its own. Anything else, such as a quoted or negative amount, is refused with HTTP 400 and changes nothing.

Users and permissions

Each user has a token of their own; roles decide what it can read and write. Run user, role and grant statements as an admin, over HTTP, a PostgreSQL connection or MCP, each statement as a request of its own. Over HTTP and MCP, and in a PostgreSQL prepared statement, one sent with other statements is refused and nothing in the request runs; a PostgreSQL simple query of several statements (psql -c, a script) runs them one by one and stops at the first error, so the statements before it stay saved.

All change the same permissions, and a change applies to the next statement of every session, open ones included. A statement FoxDB cannot apply exactly fails and changes nothing: a role or user that does not exist, a role that already exists, a privilege FoxDB does not have, or a PostgreSQL form it does not implement, such as ON ALL TABLES IN SCHEMA or WITH GRANT OPTION. Over HTTP a refusal answers 400, or 403 for a user who is not an admin, with its SQLSTATE in X-FoxDB-SQLSTATE.

Users

Each user gets a unique API token, shown once, for HTTP and PostgreSQL alike.

CREATE USER analyst;
CREATE USER dashboard IN ROLE viewer;          -- the role must exist
CREATE USER reporter DEFAULT_ROLE = viewer;  -- Snowflake syntax

SHOW USERS;
ALTER USER analyst SET ROLE admin;
ALTER USER analyst RESET PASSWORD;  -- generates a new token
DROP USER dashboard;

Roles and grants

CREATE ROLE viewer;
CREATE ROLE editor;

GRANT SELECT ON * TO ROLE viewer;
GRANT SELECT, INSERT ON * TO ROLE editor;

GRANT ROLE viewer TO USER dashboard;
REVOKE ROLE editor FROM USER analyst;

SHOW ROLES;
SHOW GRANTS;

A user created with a role, or moved to one with ALTER USER ... SET ROLE, can do what its roles grant, but its PostgreSQL sessions are read-only: write privileges granted to a role apply over HTTP and MCP only. A user created without a role is an admin, whom no role limits: GRANT ROLE is refused for it, and ALTER USER ... SET ROLE makes it a user of the role.

Column grants and row policies

-- Analysts see four columns of employees
CREATE ROLE analyst;
GRANT SELECT (id, department, hire_date, salary_band) ON employees TO analyst;

-- EMEA managers see only EMEA orders
CREATE ROLE emea_manager;
GRANT SELECT ON * TO emea_manager;
CREATE POLICY emea_orders ON orders FOR SELECT TO emea_manager USING (region = 'EMEA');

• Through views: column grants and row policies work through views that an unqualified table name resolves to. A user whose roles have any such restriction reads a restricted table only by its unqualified name: main.orders, SHOW HISTORY FOR orders and SET search_path or SET schema are refused, even under GRANT SELECT ON *. Such a user names the schema of what it creates, drops or alters (CREATE TABLE main.notes), and cannot create a view over a table its roles restrict.
• Tables in main: column grants and policies name a table in main; orders, main.orders and public.orders are one table, and one in another schema is refused. A policy's condition is one SQL expression, without parameters.
• Table functions: apart from admins, only users whose roles have SELECT ON * and no restriction can call table functions such as read_parquet and the catalog functions duckdb_tables, duckdb_columns, duckdb_views and duckdb_functions. Everyone else gets only generate_series, range, unnest, repeat, repeat_row, duckdb_types and duckdb_settings.
• Names: DESCRIBE t and SHOW COLUMNS FROM t need SELECT on t; SHOW TABLES and SHOW SCHEMAS list names for everyone. SHOW GRANTS and SHOW POLICIES show a non-admin their own roles.
• Views: column grants and policies restrict the table they name, not the views over it. A role that may read a view reads everything the view shows, and SELECT ON * includes every view. To give a role part of a view, grant columns of the view or create a policy on it.
• PostgreSQL catalogs: pg_catalog and information_schema need SELECT ON * (column grants and policies may come with it). A user whose roles name their tables is refused them, so tools that list tables there (psql's \dt, Metabase, Dot) list none for that user. GET /api/catalog lists the tables and columns a token can read.
• Admins only, whatever the grants: reading meta.query_history or a local file (read_parquet('data.parquet'), sqlite_scan(...)), and calling DuckLake's catalog functions (ducklake_snapshots, db.snapshots()) or query().

Teams and AI agents

Give each team a role, each person, Dot group and agent a user of their own, and keep personal data and secrets out of every agent's reach.

Say the database holds campaigns, campaign_spend, customers (with name, email, phone and marketing_opt_in), orders (with amount and cost) and api_keys. Marketing reads the campaigns and the customers who opted in, finance reads orders and their costs, and AI agents (Dot, Claude) read everything they need but no personal data or secrets. As an admin, over HTTP, PostgreSQL or the console:

Roles and users

-- Marketing: the campaign tables, and only the customers who opted in
CREATE ROLE marketing;
GRANT SELECT ON campaigns, campaign_spend, customers TO marketing;
CREATE POLICY opted_in ON customers FOR SELECT TO marketing USING (marketing_opt_in);

-- Finance: orders with their costs, for margins, and campaign spend
CREATE ROLE finance;
GRANT SELECT ON orders, campaign_spend TO finance;

-- AI agents: what they need, without personal data or secrets
CREATE ROLE agents;
GRANT SELECT ON campaigns, campaign_spend, orders TO agents;
GRANT SELECT (id, country, plan, signed_up_at) ON customers TO agents;

-- One user, and so one token, per person, Dot group or agent
CREATE USER maria IN ROLE marketing;
CREATE USER frank IN ROLE finance;
CREATE USER dot_marketing IN ROLE agents;
CREATE USER claude_maria IN ROLE agents;

Each CREATE USER returns the user's token once (over PostgreSQL, as a one-row result).

• maria reads the campaign tables and every column of the customers whose marketing_opt_in is true. orders and api_keys are refused.
• frank reads orders and campaign_spend: SELECT sum(amount - cost) FROM orders is the margin. customers is refused.
• dot_marketing and claude_maria read customers as id, country, plan and signed_up_at: SELECT * returns those columns, in the table's order, and a query that names email fails as if the column did not exist. api_keys is refused, and so is meta.query_history, which holds every user's SQL.

To keep it that way:

• Name the tables. SELECT ON * covers every table, api_keys included, and overrides column grants.
• Agents read only. Give the agents role SELECT and nothing else, and keep an agent's user in that role alone: a user's roles add up.
• Secrets need no grant. A table no role is granted is read by admins only. Its name is not secret: SHOW TABLES lists every table for everyone.
• Bare names. Tell agents to write a restricted table by its bare name, customers: main.customers and public.customers are refused.

Connect

Each user connects with their own token, over any interface:

# PostgreSQL (psql, Dot, BI tools): the token is the password
psql "postgresql://maria:$MARIA_TOKEN@db.getdot.ai:5432/db?sslmode=require"

# HTTP: what may this token read?
curl -X POST https://db.getdot.ai/ -H "X-GetDot-Key: $AGENT_TOKEN" -d "SHOW GRANTS"

# MCP: Claude Code, as the agent's user
claude mcp add --transport http --scope user foxdb https://db.getdot.ai/mcp --header "X-GetDot-Key: $AGENT_TOKEN"

The token, not the user name in the URL, decides who connects. These users' PostgreSQL sessions are read-only, so their reads run in parallel. Because their roles name their tables, PostgreSQL tools cannot list tables for them from pg_catalog (see PostgreSQL catalogs): they find them with SHOW TABLES and DESCRIBE.

Who ran what

Every query is recorded under the user that sent it, on every interface. An admin reads them all in meta.query_history (see Monitoring); other users get their own from GET /api/queries/recent. The statements that manage users, roles, grants, policies and budgets are not recorded there: SHOW USERS, SHOW GRANTS and SHOW POLICIES show what is in effect.

-- What the marketing Dot group ran, newest first
SELECT started_at, status, sql FROM meta.query_history
WHERE user_name = 'dot_marketing' ORDER BY id DESC LIMIT 20;

To cap what an agent spends, give its user a budget: ALTER USER dot_marketing SET BUDGET = 20.

Take access away

REVOKE SELECT ON campaign_spend FROM finance;   -- a table, from a team
REVOKE marketing FROM maria;                    -- a person, from a team
ALTER USER dot_marketing RESET PASSWORD;        -- a new token; the old one stops working
DROP USER claude_maria;                         -- a user and its token

• Next statement: each change applies to the next statement of every session, PostgreSQL sessions already open included. A statement already running finishes.
• New token: RESET PASSWORD refuses the old token at once over HTTP and MCP and for new PostgreSQL connections. Like a password change in PostgreSQL, it leaves PostgreSQL sessions already open running as that user, though they can no longer run user, role, grant or policy statements: to cut them off, drop the user or revoke its roles.
• New names: give a new user a new name. One created under a dropped user's name while that user's sessions are still open acts in them.

IP allowlist

Tenant admins can restrict access to known networks in the Connection Hub under IP allowlist, or through the API.

The policy covers every user and token: HTTP queries, MCP, PostgreSQL connections and the authenticated console APIs. Tokens and permissions are still required. Allowlists are off by default and take up to 256 addresses or ranges, IPv6 included. Changes apply to later HTTP requests and new PostgreSQL connections; open sessions stay open. Requests from outside the list get HTTP 403 or SQLSTATE 28000.

# Read your policy and the client IP FoxDB sees
curl --fail-with-body https://db.getdot.ai/api/network-policy \
  -H "X-GetDot-Key: $DOT_DB_TOKEN"

# Replace these example addresses with your public IPs
curl --fail-with-body -X PUT https://db.getdot.ai/api/network-policy \
  -H "X-GetDot-Key: $DOT_DB_TOKEN" \
  -H 'Content-Type: application/json' \
  -d '{"enabled":true,"cidrs":["203.0.113.42","2001:db8::/48"]}'

Both enabled and cidrs are required, and a PUT replaces the whole policy. One invalid entry rejects the update. An update must keep your current IP allowed, and readonly users cannot manage policies. If your IP changes and you lose access, use an allowed network or ask your operator to restore access.

Turn it off and clear the list with {"enabled":false,"cidrs":[]}; to keep the saved entries while it is off, include them in cidrs. The allowlist covers incoming client access only, not the URLs your SQL reads from.

Monitoring and limits

Query history

Queries are logged with user, SQL, arrival time (UTC), elapsed time from arrival to completion (duration_ms), the part of it spent waiting (queue_ms, never billed), status, rows and billed CPU (cpu_ms, after the 500 core-ms minimum, the wait discount and any economy discount), the same on every protocol. Each statement also reports the memory it needed above what its process already held: peak_memory_bytes, and memory_gib_ms, that memory in GiB times its run in milliseconds. Reads report it over HTTP, MCP and read-only PostgreSQL sessions; writes, and statements on a PostgreSQL connection that can write, report 0 for now. memory_ms is its memory charge in the unit of cpu_ms, so a statement cost cpu_ms plus memory_ms. The most recent 250,000 statements are kept. Reading meta.query_history needs an admin token; other users get their own statements from GET /api/queries/recent.

SELECT * FROM meta.query_history ORDER BY id DESC LIMIT 20;

SELECT user_name, count(*) AS queries, sum(cpu_ms) / 1000.0 AS billed_core_seconds
FROM meta.query_history GROUP BY user_name;

SELECT sql, peak_memory_bytes, memory_gib_ms FROM meta.query_history
ORDER BY memory_gib_ms DESC LIMIT 10;

Running queries

Admins can list and cancel in-flight HTTP and MCP queries in the Connection Hub or through the API; statements sent over PostgreSQL are not listed there. The recent list covers every interface.

GET /api/queries              -- list running queries
POST /api/queries/{id}/cancel -- cancel a query
GET /api/queries/recent       -- last 20 completed, any interface (with cpu_ms); ?limit=N up to 200
                              -- read-only tokens see only their own statements

Limits

From Snowflake

Export to Parquet, then load it with one statement.

1. Export from Snowflake

COPY INTO @my_stage/export/ FROM my_table FILE_FORMAT = (TYPE = PARQUET);

2. Import into FoxDB

CREATE TABLE my_table AS
  SELECT * FROM read_parquet('s3://my-stage-bucket/export/*.parquet');

SQL differences

SQL

FoxDB speaks standard PostgreSQL SQL: most SQL you would write for PostgreSQL runs unmodified. On top of it come DuckDB's extensions such as GROUP BY ALL, EXCLUDE, QUALIFY and PIVOT.

Differences from PostgreSQL

• No multi-statement transactions. Each statement commits on its own; BEGIN, COMMIT and ROLLBACK are acknowledged but do not group statements. SAVEPOINT fails with SQLSTATE 0A000.
• No SERIAL, IDENTITY or sequences. Generate IDs in the client. Column defaults must be literals, now(), or one of CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, LOCALTIMESTAMP, LOCALTIME.
• Constraints: PRIMARY KEY is accepted but not enforced on regular tables; UNIQUE, FOREIGN KEY and CHECK are refused.
• 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.
• CREATE INDEX, VACUUM and COMMENT ON are accepted and ignored.

DuckDB extensions

-- GROUP BY ALL: groups by every non-aggregate column
SELECT county, avg(price) FROM uk.uk_price_paid GROUP BY ALL;
SELECT * EXCLUDE (postcode1, postcode2) FROM uk.uk_price_paid;
PIVOT uk.uk_price_paid ON type USING avg(price) GROUP BY county;
SELECT *, ROW_NUMBER() OVER (PARTITION BY county ORDER BY price DESC) AS rn
FROM uk.uk_price_paid QUALIFY rn <= 3;

Data types

HTTP API

Authentication

Endpoints

Response headers

Errors

Error bodies are plain text, most of them starting with Code: NNN., where NNN is usually but not always the HTTP status. Branch on the HTTP status and X-FoxDB-SQLSTATE, not on the body.