DuckDB sources

A source has a kind (Source kinds). A DuckDB source reads files that live on the server: a directory of Parquet and CSV files, or a .duckdb database file, under a root the operator chooses. It is the second SQL kind. Its panels carry the same query.sql a TimescaleDB panel does, checked by the same guard and windowed by the same plan. What differs is where the rows come from: DuckDB runs inside the server process, so there is no connection, no credential and no network. Everything else the app guarantees still holds. The model writes a spec and never data, the query is untrusted, the server owns the window, and a spec names its source by an opaque id.

The source

A DuckDB source’s config is a path and the same table allowlist a database source has:

{
  "kind": "duckdb",
  "path": "lake/requests",
  "tables": [
    {
      "name": "http_requests",
      "timeField": "ts",
      "columns": [
        { "name": "ts", "type": "TIMESTAMP" },
        { "name": "route", "type": "VARCHAR" },
        { "name": "status", "type": "SMALLINT" },
        { "name": "duration_ms", "type": "DOUBLE" }
      ]
    }
  ],
  "rowFilter": { "column": "tenant", "claim": "tenant" }
}

• path is relative to DUCKDB_SOURCES_DIR, the root the operator sets (DuckDB files). It never starts with /, never contains .., an empty or . segment, or a backslash, and is at most 512 characters. Unset, DUCKDB_SOURCES_DIR refuses every DuckDB source, so the kind is closed until an operator decides what it may read.
• tables is the allowlist, exactly as for a TimescaleDB source: a table, its columns (with per-column exposure) and an optional timeField. A query may read only what it lists.
• rowFilter is optional and works as it does on a database source: see row-level filters.

A DuckDB source names no secret_ref, and saving one with a ref is refused. Its path is in the stored config, which only a source admin sees. A listing shows whether it is a directory or a database and how many tables it has, never the path. The path is kept out of results, Test reports, plan views, error messages and log lines too.

The Data sources page registers one through the DuckDB (files) card. Path offers the directories and .duckdb files directly under the root (GET /api/sources/duckdb/paths, names only), and Discover tables opens the path as a panel would and fills the allowlist. See your first dashboard.

The path and the root

A path is checked twice. When it is typed, lexically, by the rules above. Then on save, and again on every open, by the path guard (src/lib/duckdb/path.ts), which:

1. resolves DUCKDB_SOURCES_DIR and the joined path through realpath, so a symlink is judged by where it lands;
2. refuses anything that lands outside the root;
3. checks the target is a directory, or a regular file whose name ends .duckdb.

Its refusals name the variable, never a path. Because the guard runs on every open, a symlink changed after a source was saved cannot point an existing source somewhere new.

Two layouts

path names	What the tables are
A directory	One view per .parquet or .csv file directly under it, named by its stem: http_requests.parquet is the table http_requests. Not recursive. Other extensions, and anything a symlink takes outside the directory, are skipped. When a.parquet and a.csv both exist, the Parquet file wins. At most 200 files, in name order.
A .duckdb file	The database’s own tables in main. It is attached READ_ONLY.

A directory is the shape a data-lake drop, a vendor export or a batch job’s output usually has. A new file becomes a table on the next Refresh, never by itself: the catalog is what the last refresh found (out of scope: hot reload). Replacing a file’s contents under the same name needs no refresh. The next query reads the new file, which is how the files demo moves its live edge forward every minute.

The catalog

Discover tables and Refresh open a fresh, locked engine and read information_schema.columns in main. For a directory, that means listing the files again. Column types are DuckDB’s own names (TIMESTAMP, VARCHAR, DOUBLE, BIGINT). The time column picker offers TIMESTAMP, TIMESTAMP_NS/_MS/_S, TIMESTAMPTZ, DATE and TIME columns. As on every kind, a refresh never adds a table to the allowlist and never drops one: a file that has gone is reported as a missing table, with its last known columns kept. Test probes each allowlisted table with LIMIT 0 and reports the engine version (DuckDB 1.5.6), the layout and whether the lock holds.

The dialect

DuckDB’s parser descends from PostgreSQL’s, so the grammar the guard already parses is DuckDB’s grammar too, and a statement reads the same on both kinds. What the model is told for a DuckDB source (DUCKDB_PROMPT in src/lib/duckdb/catalog.ts):

• Bucketing time. time_bucket(INTERVAL '5 minutes', ts), date_trunc('minute', ts) and date_bin(INTERVAL '5 minutes', ts, TIMESTAMP '2000-01-01') all exist.
• Column names are case-sensitive as listed. A name that is not all lowercase is double-quoted ("Value").
• No ::timestamptz. The time column is used as it is.
• No table functions. Query the listed tables, not read_parquet(…).

SELECT time_bucket(INTERVAL '1 minute', ts) AS minute,
       count(*) FILTER (WHERE status >= 500) AS errors
  FROM http_requests
 GROUP BY minute
 ORDER BY minute

The window is bound the same way as on TimescaleDB, as parameters on timeField in a wrapper the server adds. The kind supplies the cast: TIMESTAMP rather than timestamptz. The engine’s TimeZone is fixed to UTC and locked, so a TIMESTAMP column is read as UTC, and a TIMESTAMPTZ column compares against the same instants.

What the guard adds

Every DuckDB query passes validateSql unchanged, the same SELECT-only, allowlisted, bounded walk a TimescaleDB query takes (The SQL guard). Two of its rules exist mostly for DuckDB:

• DuckDB’s file and engine functions are denied: the readers (read_csv, read_parquet, read_json, read_text, glob, …), the functions that execute a query string (query, query_table), the environment and settings (getenv, current_setting, duckdb_settings, duckdb_secrets, …), catalog introspection beyond the allowlist (duckdb_tables, duckdb_columns, …) and DuckDB’s clock and random readings. The fuzz suite holds them.
• No table function in FROM. A table function is how DuckDB reads a file, so FROM read_parquet('/etc/…'), and any function used as a table that a denylist might not name yet, is refused by its node type.

COPY, ATTACH, INSTALL, LOAD, SET, PRAGMA and CREATE are not a SELECT, so the guard refuses them as it refuses any other statement.

What the server owns

The guard is the first belt. The engine is the second. Each source gets one in-process engine, which is locked before any statement a panel wrote can run (DuckDB in the data model):

The engine is set to	So a statement the guard missed still cannot
allowed_paths = the source’s own files, then enable_external_access=false	read any other file, a sibling directory or /etc, create a file, or reach the network
autoinstall_known_extensions=false, autoload_known_extensions=false	install or load an extension, such as httpfs
a .duckdb file opened READ_ONLY	write to the database
memory_limit (DUCKDB_MEMORY_LIMIT), threads (DUCKDB_THREADS)	take more memory or cores than the operator allowed
TimeZone='UTC' (SET GLOBAL, before the lock)	read time in the server’s local zone
lock_configuration=true	change any of the above with SET

Each is tested against the real engine with the guard bypassed. On top of that, a query is interrupted at QUERY_TIMEOUT_SECONDS, rows stream through the same row and byte caps every SQL source has, and every cut travels as truncated to a badge on the panel. The plan dialog (Show query) shows the lock statements as they run, with the files named by their role.

One gap remains, by design of DuckDB 1.5: the engine may write wherever it may read, so a statement could overwrite one of the source’s own listed files. The guard, which allows exactly one SELECT, is what stops that, and mounting the root read-only is the belt behind it.

What is refused, and why

Refused	Because
A path that is absolute, has .., or resolves outside DUCKDB_SOURCES_DIR	A path is as untrusted as a URL. The root is the operator’s decision.
Any DuckDB source while DUCKDB_SOURCES_DIR is unset	Closed by default: nothing is readable until the operator names a root.
A table not on the allowlist	The allowlist is the catalog, exactly as for a database.
read_csv(…), FROM read_parquet(…), glob(…) and every other file function	A file function reads whatever path it is given, past the allowlist and the root.
ATTACH, INSTALL, LOAD, SET, COPY	Not a SELECT; and the locked engine would refuse each of them anyway.
s3://, https://, httpfs	A file is local to the server. Remote storage is an operator’s mount, not Holotable’s fetch.
Every run, with “DuckDB is not available on this server”	The platform has no DuckDB binding. Every other kind keeps working.