DuckDB

The o41_metrics DuckDB extension attaches an organization's Metrics as a read-only database: folders, experiments, annotations, catalog and series are tables you query with SQL. Filters on experiment_id, experiment_slug, folder_id and metric_name (id and slug in experiments) decide which API requests run, DuckDB evaluates everything else, and a persistent on-disk cache makes repeat queries answer without the network. Use it for any analysis that spans more than one run.

Install

In the DuckDB CLI, start with -unsigned, install once and load in every session:

duckdb -unsigned

INSTALL o41_metrics FROM 'https://metrics.041.io/public/artifacts/duckdb';
LOAD o41_metrics;

INSTALL leaves an existing installation unchanged. To upgrade, run this before LOAD in a new session:

FORCE INSTALL o41_metrics FROM 'https://metrics.041.io/public/artifacts/duckdb';

A specific release is under https://metrics.041.io/public/artifacts/duckdb/releases/<version>.

In Python, install the wheel, which pins the matching duckdb release and bundles the extension binary:

uv add o41-metrics-duckdb

To upgrade the Python wheel, run uv add -U o41-metrics-duckdb or pip install -U o41-metrics-duckdb, then restart Python. The wheel bundles its own binary, so SQL FORCE INSTALL does not upgrade the binary it loads.

On the first ATTACH per process, release builds check the attached host's public duckdb/latest.json for a newer release. An outdated build prints a warning to stderr in both the CLI and Python, with the installed and latest versions and upgrade commands. The check has a roughly two-second timeout, no retries, and stays silent when offline or the manifest is unavailable. Set O41_METRICS_DISABLE_UPDATE_CHECK=1 before starting DuckDB or Python to disable the check and warning. Local and preview builds skip the check.

Builds exist for linux_amd64, linux_arm64 and osx_arm64, each for DuckDB 2.0.0; an extension loads only in the release it was built for. The extension is not signed by DuckDB, so DuckDB must allow unsigned extensions before it loads.

Attach

CREATE SECRET o41 (TYPE o41_metrics, API_KEY 'ak_...');
ATTACH 'metrics.041.io' AS m (TYPE o41_metrics);
SELECT folder_id, path FROM m.folders ORDER BY path;
SELECT slug, status FROM m.experiments WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318';

Give experiments a selector, as the second query does: a filter on status, a sort or slug LIKE '%...%' without one reads the record of every experiment in the organization. See what a query costs.

The ATTACH path is the API host without a scheme; DuckDB hands https:// paths to httpfs before the extension sees them. Leave it empty, ATTACH '' AS m (TYPE o41_metrics), to take the host from the secret, O41_METRICS_HOST or the CLI login, as below, falling back to https://metrics.041.io. The older metrics.syvain.com is the same service; the cache is kept per host, so pick one and keep it.

The API key comes from, in order:

1. the API_KEY ATTACH option;
2. a o41_metrics secret: the one SECRET 'name' names, or else one whose scope covers the destination;
3. O41_METRICS_API_KEY;
4. the login saved by o41-metrics auth login, in $XDG_CONFIG_HOME/o41-metrics/auth.json or ~/.config/o41-metrics/auth.json. A browser sign-in saves a personal API key there for exactly this; see command line.

A key goes only to the host it is paired with. An API_KEY, secret or environment key goes to the ATTACH path, then a named secret's HOST, then O41_METRICS_HOST, then production, never to the saved login's host. The saved login's key goes only to the host it was saved for: with no other host given the ATTACH uses that host, and if one of those names another host the ATTACH fails.

A secret takes API_KEY and HOST. Without an explicit SCOPE, a secret with a HOST applies to that origin only. A secret with neither applies only to https://metrics.041.io and https://metrics.syvain.com. Explicit scopes must be complete HTTP(S) origins. A secret covers a destination only when one of its scopes is exactly that origin; DuckDB's prefix match only nominates candidates. A secret that DuckDB picks automatically and that does not cover the destination is skipped, and the key comes from the next source. A secret named with SECRET that does not cover the destination fails the ATTACH.

Persistent secrets created without HOST by 0.2.x releases stored the scope https:// and http://, meaning any host. They now cover only the two production origins, like a new secret without HOST.

Series requests run on their own pool of CONCURRENCY fetchers, so a low DuckDB threads setting does not serialize them. Catalog and annotation scans are also capped by threads.

Tables

folders lists every folder.

experiments lists every experiment, or only the selected ones. description, meta, error_message and error_meta come from each experiment's record; the other columns come from the folder tree; see what a query costs.

annotations holds one row per annotation.

catalog holds one row per series name of an experiment.

series holds one row per point.

Selectors

Scans of annotations, catalog and series need an experiment selector in the WHERE clause: =, IN (...) or OR chains on experiment_id, experiment_slug or folder_id. Without one the query fails and says so. experiments takes the same selectors on id, slug and folder_id. Before it reads a record it also applies the other filters DuckDB hands the scan on those columns: comparisons, IS NULL and prefix matches such as slug LIKE 'lr-%'. Without either it reads the records of every experiment in the organization that the rest of the query needs. A metric_name selector limits which series are fetched; without it every series of the selected experiments is fetched and DuckDB filters.

folder_id selects experiments directly in that folder, not below it. An uncorrelated IN (SELECT ...) is a selector too, because DuckDB runs the subquery first and hands the scan its values, so a subtree is one query:

SELECT experiment_slug, count(*) AS points
FROM m.series
WHERE folder_id IN (SELECT folder_id FROM m.folders WHERE path = '/sweeps/lr' OR path LIKE '/sweeps/lr/%')
  AND metric_name = 'loss'
GROUP BY ALL;

DuckDB passes a subquery's values as a list only up to the dynamic_or_filter_threshold setting, which ATTACH raises to 10,000 on the connection that runs it unless it is already set or higher. For a larger subquery, SET dynamic_or_filter_threshold above its row count. On annotations, catalog and series, LIKE, list_contains(...) and other expressions on the selector columns are not selectors.

What a query costs

The API keeps each experiment in its own storage on the server, and reading an experiment's record wakes that storage. A query costs about one such read per experiment it touches, so the number of experiments decides the cost, not the number of rows returned. folders and the selector resolution read one folder tree for the whole organization.

The folder tree also carries an index: each experiment's status, its timestamps and the revisions the cache checks, as of the experiment's last read. An experiment that changed since then, usually one that is logging metrics, has no index entry; the scan asks for those in one request per 500, which reads only them. Everything else comes from the tree without waking any experiment.

A query that needs only tree and index columns reads no record, whatever it filters on. Exact id selectors are the exception: they read the selected records, which confirm the IDs exist.

DuckDB hands the scan comparisons, IN lists, IS NULL and prefix matches (LIKE 'lr-%', starts_with, a regex anchored with ^), and the scan applies them to the tree and the index before it reads a record. DuckDB applies other expressions, such as LIKE '%lr%', ends_with, an unanchored regex or lower(slug), only after the scan, so they read every record the query needs. To find runs by part of their name, match in a subquery that reads only the tree; its IDs then select the records:

SELECT slug, meta->>'lr' AS lr FROM m.experiments
WHERE id IN (SELECT id FROM m.experiments WHERE slug LIKE '%mamba%');

Records are read as the scan reaches them, so a LIMIT without ORDER BY stops after the first batch; ORDER BY ... LIMIT on a record column has to read every record it sorts. Sorting on status or a timestamp does not:

SELECT slug, status, last_event_at FROM m.experiments
WHERE status = 'running'
ORDER BY last_event_at DESC;

A sweep of the organization's records reads thousands of them. Put a selector or a status or timestamp filter on the experiments side of every query that reads meta or description, and pass folder_id := or experiment_id := to revalidate.

Releases up to 0.4.2 read every record for status and timestamp columns and their filters; releases up to 0.4.1 also for experiments under a LIMIT, for count(*) and for a prefix match on slug; releases up to 0.4.0 also for slug = '...'. Check yours with SELECT o41_version().

A new attachment, which is every new duckdb process, validates each experiment it touches again, even with CACHE_DIR. With the index that costs one tree read plus a request per 500 changed experiments, but keeping one connection open for a batch of queries still saves the tree read.

Example queries

Best validation loss of every run in a folder, and the step it was reached:

SELECT experiment_slug,
       min(value)            AS best_val_loss,
       arg_min(step, value)  AS best_step
FROM m.series
WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
  AND metric_name = 'loss' AND (metadata->>'split') = 'valid'
GROUP BY experiment_slug
ORDER BY best_val_loss;

The last logged value of several metrics per run:

SELECT experiment_slug, metric_name, metadata,
       max(step)              AS last_step,
       arg_max(value, step)   AS final_value
FROM m.series
WHERE experiment_slug IN ('mamba-run-001', 'mamba-run-002')
  AND metric_name IN ('loss', 'grad_norm')
GROUP BY ALL
ORDER BY experiment_slug, metric_name;

Final validation loss against hyperparameters from meta:

SELECT e.slug,
       (e.meta->>'$.config.lr')::DOUBLE          AS lr,
       (e.meta->>'$.config.batch_size')::INTEGER AS batch_size,
       arg_max(s.value, s.step)                  AS final_val_loss
FROM m.series s
JOIN m.experiments e ON e.id = s.experiment_id
WHERE s.folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
  AND e.folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
  AND s.metric_name = 'loss' AND (s.metadata->>'split') = 'valid'
  AND e.status = 'done'
GROUP BY ALL
ORDER BY lr, batch_size;

Two runs side by side, one row per step, NULL where a run has no point:

PIVOT (
  SELECT experiment_slug, step, value
  FROM m.series
  WHERE experiment_slug IN ('baseline', 'candidate')
    AND metric_name = 'loss' AND (metadata->>'split') = 'valid'
) ON experiment_slug USING any_value(value)
ORDER BY step;

Runs that evaluate at different steps, aligned to the latest candidate point at or before each baseline point:

WITH v AS (
  SELECT experiment_slug, step, value
  FROM m.series
  WHERE experiment_slug IN ('baseline', 'candidate')
    AND metric_name = 'loss' AND (metadata->>'split') = 'valid'
)
SELECT b.step, b.value AS baseline, c.value AS candidate, c.value - b.value AS delta
FROM (SELECT * FROM v WHERE experiment_slug = 'baseline') b
ASOF JOIN (SELECT * FROM v WHERE experiment_slug = 'candidate') c ON b.step >= c.step
ORDER BY b.step;

Training loss smoothed into 1,000-step buckets across a folder, per rank:

SELECT experiment_slug, metadata->>'rank' AS rank,
       step // 1000 * 1000 AS bucket, avg(value) AS loss
FROM m.series
WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
  AND metric_name = 'loss' AND (metadata->>'split') = 'train'
GROUP BY ALL
ORDER BY experiment_slug, rank, bucket;

Which runs in a folder failed, and why:

SELECT slug, error_message, done_at FROM m.experiments
WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318' AND status = 'error'
ORDER BY done_at DESC;

Checkpoints recorded as annotations:

SELECT experiment_slug, (meta->>'step')::BIGINT AS step, meta->>'path' AS path
FROM m.annotations
WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318' AND annotation = 'checkpoint saved'
ORDER BY experiment_slug, step;

Python

import duckdb
import o41_metrics_duckdb

con = duckdb.connect(config={"allow_unsigned_extensions": "true"})
o41_metrics_duckdb.load(con)
con.execute(
    "ATTACH 'metrics.041.io' AS m (TYPE o41_metrics)"
)
rows = con.sql(
    """
    SELECT experiment_slug, min(value) AS best
    FROM m.series
    WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
      AND metric_name = 'loss' AND (metadata->>'split') = 'valid'
    GROUP BY ALL ORDER BY best
    """
).fetchall()

load(con) loads the bundled binary; extension_path() returns its path. With no secret and no API_KEY the key comes from O41_METRICS_API_KEY or the CLI login. Results convert with .df(), .pl() or .arrow() as in any DuckDB query.

Cache

Experiment records, catalogs, annotations and series are cached by default in ~/.o41-metrics/duckdb-cache, shared across connections and processes. Files are JSON and Parquet under v2/<host>__<key fingerprint>/. Set CACHE_DIR 'path' on ATTACH to choose another directory.

To explicitly disable disk caching, use:

ATTACH 'metrics.041.io' AS m (TYPE o41_metrics, CACHE false);

CACHE false ignores CACHE_DIR and any existing disk cache. The folder tree and experiment records are still remembered for the attachment, and everything else comes from the API each query. Empty or null CACHE_DIR values are errors; use CACHE false to opt out. If the cache directory cannot be created, the attachment fails instead of silently disabling caching.

The API keeps two counters per experiment: an experiment revision, which changes with record updates and annotations, and a series revision, which changes with metric activity. A new attachment checks cached experiments against the folder tree's index once it has read the tree, and otherwise, or for experiments the index does not cover, with one request per 500 experiments. It downloads only what changed. Within an attachment, queries reuse the last validated revision until you revalidate or refresh, so a long-lived connection does not see new points on its own.

Each cached series keeps the resume cursor the server sent with it. When the series revision changes, the next query that needs a series sends that cursor and adds the points after it to the cached copy. When the server can no longer resume from the cursor, the series comes back whole. That happens about five minutes after the points the cursor covers were written, or after the experiment's storage was recreated, so poll more often than that to stay incremental. Finished experiments are read without cursors. Once a series is stored under a new revision, its copy under the older revision is deleted, so the cache keeps one copy of each series however often you poll.

All-series reads discover names from the live writer, including series whose first points are still staged. A cold read downloads them again by name to obtain their first cursors. Each request carries at most 1,000 cursors, so a scan with more series also reads the remaining series whole before obtaining their cursors by name. Active scans hold private links to their files, so newer revisions and cache purges leave those readers' data available.

A running experiment lists a new series in its catalog about a minute after its first points, so the cache keeps a catalog only once the experiment is finished and its catalog final; until then every catalog scan fetches it again. When the API can serve an experiment's series only without its newest points, the query returns what it has, prints a warning, and caches nothing for it, so a later query fetches the series again.

SELECT * FROM o41_metrics_revalidate('m');                         -- recheck every cached experiment
SELECT * FROM o41_metrics_revalidate('m', experiment_id := '019e...');
SELECT * FROM o41_refresh('m');                                    -- reread the tree, then revalidate
SELECT * FROM o41_refresh('m', folder_id := '019e...');
SELECT * FROM o41_cache_status('m');
SELECT * FROM o41_purge('m', experiment_id := '019e...');
SELECT * FROM o41_purge('m');
SELECT o41_version();

Pass at most one of experiment_id and folder_id; without either, the function covers the whole cache. Revalidate and refresh keep unchanged data and fetch changed series lazily, on the next query that needs them.

The series revision is updated on a best-effort basis after ingestion. If that update fails, revalidation cannot see the new points until a later update succeeds; o41_purge for the experiment forces a fresh download. Ordinary queries may answer from complete cached records while the API is unreachable; the maintenance functions report the outage instead.

The cached Parquet files are readable without the extension or a key:

SELECT * FROM read_parquet('~/.o41-metrics/duckdb-cache/v2/*/experiments/*/series/*/loss.parquet');

That glob can include an older revision of a series that no query has needed since the revision changed; the current one is named in each experiment's record.json.

When a read fails

One experiment can fail to read while the rest of the organization is fine: its series database can be stuck, or its record can fail to load. What a scan does then is set by the ON_ERROR ATTACH option, or, for attachments without it, by the o41_on_error setting (SET o41_on_error = 'warn'), default 'fail'.

• 'fail' fails the query, as before, so a result is never silently partial. The error names the experiment that failed and suggests 'warn'.
• 'warn' skips what failed after its retries and returns everything else: a whole experiment when its record, catalog, annotations or series request fails, or only the failed series of an experiment whose other series read fine. DuckDB logs a warning that names the skipped experiments, which the CLI prints. When every selected experiment fails, the query still fails.

ATTACH 'metrics.041.io' AS m (TYPE o41_metrics, ON_ERROR 'warn');
SELECT experiment_slug, min(value) FROM m.series
WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318' AND metric_name = 'eval/loss'
GROUP BY ALL;
SELECT * FROM o41_scan_errors('m');

o41_scan_errors(db) lists the reads that 'warn' skipped in the attachment, the latest 10,000: scan_id (one per scan that skipped something), table_name, experiment_id, experiment_slug, series (NULL when the whole experiment was skipped), code, message, retryable and failed_at. Python does not show DuckDB warnings, so check this table there. A retryable failure may read fine on the next query. Nothing that failed is cached: the next query asks for it again, and a skipped series keeps the experiment's list of series out of the cache. When a record fails to load, a record already in the cache is used instead, as during an outage.

With 'warn' the extension asks the API to report single failures (onError: "report", see API); an API deployment older than this extension release rejects such scans.

Requests and retries

A series scan reads the folder tree once to resolve selectors, then sends one streaming request per experiment for every series, or for the selected names not already cached, up to 1,000 names per request. Each series is checked against the stream's counts before anything enters the cache.

A catalog scan lists up to 100 experiments per request. An experiment the API could not list is asked for again on its own; if it still fails at the deadline, the query fails with that experiment's ID and error, or skips it under ON_ERROR 'warn'.

Requests retry transport failures, HTTP 408, 425, 429 and 5xx, Cloudflare edge errors even when returned as 4xx, malformed or uncorrelated 2xx replies, truncated streams, stream count mismatches, and API error events marked retryable. Pauses start at 0.5 seconds and double to 10 seconds. Integer Retry-After seconds and API retryAfterMs can lengthen a pause, up to 10 seconds; HTTP dates are ignored. A 300-second deadline covers each operation's requests and pauses. Retries keep verified series. Named retries request only unfinished names. An unnamed stream interrupted before enumeration finishes repeats live enumeration and ignores the series already verified. Under ON_ERROR 'warn', what still fails when its retries run out is skipped instead of failing the query. Records, revisions and catalogs retry only the experiments that failed; a completed series stream retries reported failed series by name.

Certificate and hostname verification failures remain fatal. On macOS, clients share one checked Keychain trust store, so concurrent reads do not each load it. Transient trust-store loading failures are retried. SSL_CERT_FILE or SSL_CERT_DIR selects an explicit trust store. No shorter idle timeout cuts off slow server streams before the operation's deadline. On macOS, closed TLS peers cannot terminate the query through SIGPIPE; the host's signal handler is preserved.

O41_METRICS_DEBUG=1 logs every request with its thread, status and latency to standard error.

Related: Python API client, command line, what to log, series storage.