# DuckDB

The `syvain_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` decide which API requests run,
DuckDB evaluates everything else, and an optional 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:

```bash
duckdb -unsigned
```

```sql
INSTALL syvain_metrics FROM 'https://metrics.041.io/public/artifacts/duckdb';
LOAD syvain_metrics;
```

`FORCE INSTALL` picks up a newer build; 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:

```bash
uv add syvain-metrics-duckdb
```

Builds exist for `linux_amd64`, `linux_arm64` and `osx_arm64`, each for one
DuckDB release (1.5.5 today); 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

```sql
CREATE SECRET syvain (TYPE syvain_metrics, API_KEY 'ak_...');
ATTACH 'metrics.041.io' AS m (TYPE syvain_metrics, CACHE_DIR '~/.syvain-metrics/duckdb-cache');
SELECT slug, status FROM m.experiments LIMIT 5;
```

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 syvain_metrics)`, to take the host from the secret or the
CLI login, falling back to `https://metrics.syvain.com`. Both hosts are the same
service; the cache is kept per host, so pick one and keep it.

| option        | default    | meaning                                                               |
| ------------- | ---------- | --------------------------------------------------------------------- |
| `API_KEY`     | none       | organization API key; wins over every other source                    |
| `SECRET`      | best match | name of the `syvain_metrics` secret to use                            |
| `CACHE_DIR`   | none       | directory of the persistent cache; without it nothing is kept on disk |
| `CONCURRENCY` | `16`       | parallel API requests, 1 to 256                                       |

The API key comes from, in order:

1. the `API_KEY` ATTACH option;
2. a `syvain_metrics` secret: the one `SECRET 'name'` names, or else the one
   whose scope best matches the host;
3. `SYVAIN_METRICS_API_KEY`;
4. the login saved by `syvain-metrics auth login`, in
   `$XDG_CONFIG_HOME/syvain-metrics/auth.json` or
   `~/.config/syvain-metrics/auth.json`. A browser sign-in saves a personal API
   key there for exactly this; see [command line](https://metrics.041.io/docs/cli.md#sign-in).

A secret takes `API_KEY` and `HOST`. A secret with a `HOST` applies to that host
only, so keys for two hosts can coexist; one without applies to any 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.

| column      | type    |                           |
| ----------- | ------- | ------------------------- |
| `folder_id` | VARCHAR |                           |
| `name`      | VARCHAR |                           |
| `path`      | VARCHAR | absolute, `/models/mamba` |
| `parent_id` | VARCHAR | NULL at the root          |

`experiments` lists every experiment, or only the selected ones.

| column                                                               | type      |                                         |
| -------------------------------------------------------------------- | --------- | --------------------------------------- |
| `id`                                                                 | VARCHAR   |                                         |
| `slug`                                                               | VARCHAR   |                                         |
| `folder_id`                                                          | VARCHAR   | NULL for experiments at the root        |
| `status`                                                             | VARCHAR   | `created`, `running`, `done` or `error` |
| `description`                                                        | VARCHAR   |                                         |
| `meta`                                                               | JSON      | the experiment's configuration          |
| `error_message`                                                      | VARCHAR   |                                         |
| `error_meta`                                                         | JSON      |                                         |
| `created_at`, `updated_at`, `started_at`, `done_at`, `last_event_at` | TIMESTAMP | UTC; NULL when unset                    |

`annotations` holds one row per annotation.

| column                                          | type      |                                                         |
| ----------------------------------------------- | --------- | ------------------------------------------------------- |
| `experiment_id`, `experiment_slug`, `folder_id` | VARCHAR   |                                                         |
| `id`                                            | VARCHAR   |                                                         |
| `annotation`                                    | VARCHAR   | the text                                                |
| `meta`                                          | JSON      | includes `step` when the annotation was logged with one |
| `created_at`                                    | TIMESTAMP |                                                         |

`catalog` holds one row per series name of an experiment.

| column                                          | type    |                                                                          |
| ----------------------------------------------- | ------- | ------------------------------------------------------------------------ |
| `experiment_id`, `experiment_slug`, `folder_id` | VARCHAR |                                                                          |
| `metric_name`                                   | VARCHAR |                                                                          |
| `metadata`                                      | JSON    | each metadata key with every value seen, `{"split": ["train", "valid"]}` |

`series` holds one row per point.

| column                                          | type      |                                             |
| ----------------------------------------------- | --------- | ------------------------------------------- |
| `experiment_id`, `experiment_slug`, `folder_id` | VARCHAR   |                                             |
| `metric_name`                                   | VARCHAR   |                                             |
| `metadata`                                      | JSON      | the point's partition, `{"split": "valid"}` |
| `step`                                          | BIGINT    | NULL for points stored without a step       |
| `timestamp`                                     | TIMESTAMP | UTC                                         |
| `value`                                         | DOUBLE    |                                             |

## 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` without a selector reads every experiment. 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:

```sql
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 unless it
is already higher. For a larger subquery, `SET dynamic_or_filter_threshold`
above its row count. `LIKE`, `list_contains(...)` and other expressions on the
selector columns are not selectors.

Parenthesize `->>` comparisons next to other conditions:
`(metadata->>'split') = 'valid'`. Without the parentheses DuckDB parses
`a AND metadata->>'split' = 'valid'` as
`((a AND metadata) ->> 'split') = 'valid'`.

## Example queries

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

```sql
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:

```sql
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`:

```sql
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:

```sql
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:

```sql
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:

```sql
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 failed, and why:

```sql
SELECT slug, error_message, done_at FROM m.experiments
WHERE status = 'error' ORDER BY done_at DESC;
```

Checkpoints recorded as annotations:

```sql
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

```python
import duckdb
import syvain_metrics_duckdb

con = duckdb.connect(config={"allow_unsigned_extensions": "true"})
syvain_metrics_duckdb.load(con)
con.execute(
    "ATTACH 'metrics.041.io' AS m (TYPE syvain_metrics, CACHE_DIR '~/.syvain-metrics/duckdb-cache')"
)
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 `SYVAIN_METRICS_API_KEY` or the
CLI login. Results convert with `.df()`, `.pl()` or `.arrow()` as in any DuckDB
query.

## Cache

With `CACHE_DIR`, experiment records, catalogs, annotations and series are kept
on disk as JSON and Parquet under `v2/<host>__<key fingerprint>/`. Without it,
the folder tree and experiment records are remembered for the attachment and
everything else comes from the API each query.

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 with one request per
500 experiments and 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.

```sql
SELECT * FROM syvain_metrics_revalidate('m');                         -- recheck every cached experiment
SELECT * FROM syvain_metrics_revalidate('m', experiment_id := '019e...');
SELECT * FROM syvain_refresh('m');                                    -- reread the tree, then revalidate
SELECT * FROM syvain_refresh('m', folder_id := '019e...');
SELECT * FROM syvain_cache_status('m');
SELECT * FROM syvain_purge('m', experiment_id := '019e...');
SELECT * FROM syvain_purge('m');
SELECT syvain_version();
```

| function                                                        | does                                                                       | returns                                                                                                         |
| --------------------------------------------------------------- | -------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------- |
| `syvain_metrics_revalidate(db, experiment_id :=, folder_id :=)` | rechecks the revisions of cached experiments                               | `experiments` checked                                                                                           |
| `syvain_refresh(db, experiment_id :=, folder_id :=)`            | rereads the folder tree to find new or moved experiments, then revalidates | `experiments` checked                                                                                           |
| `syvain_purge(db, experiment_id :=, folder_id :=)`              | deletes cached files, forcing a download on the next query                 | `experiments` purged                                                                                            |
| `syvain_cache_status(db)`                                       | lists cached experiments                                                   | `experiment_id`, `slug`, `folder_id`, `status`, `catalog`, `annotations`, `series_files`, `bytes`, `fetched_at` |

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; `syvain_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:

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

That glob includes older revisions kept for readers that still use them; the
current one is named in each experiment's `record.json`.

## 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. Transport errors, HTTP 429
and 5xx, and API error events marked retryable are retried up to four times with
backoff before the query fails. `SYVAIN_METRICS_DEBUG=1` logs every request with
its thread, status and latency to standard error.

Related: [Python API client](https://metrics.041.io/docs/python-client.md), [command line](https://metrics.041.io/docs/cli.md),
[what to log](https://metrics.041.io/docs/logging-guide.md), [series storage](https://metrics.041.io/docs/series-storage.md).

---

Metrics by 041 documentation. Every page: https://metrics.041.io/llms.txt