h5i-db
Koukyosyumei/h5i-db/docs/llms-full.txt
h5i-db is a high-performance, embedded, versioned analytical database for quantitative finance and time-series workloads. It runs full DataFusion SQL with native ASOF joins, OHLCV/VWAP rollups, time travel, and previewable mutations over immutable, time-sorted Parquet segments — driven from a CLI, Rust, or Python, and designed to be safe for AI agents. Written in Rust; Apache-2.0. Canonical site: https://db.h5i.dev/ This file concatenates the manual and Python API reference as plain markdown for LLM ingestion. Cookbook tutorials are at https://db.h5i.dev/cookbook/. h5i-db is…
llms.txt33 starsChanged 58 days ago
- Reads credentials
- Deletes or force-pushes
- Installs packages
# h5i-db — full documentation
> h5i-db is a high-performance, embedded, versioned analytical database for quantitative finance and time-series workloads. It runs full DataFusion SQL with native ASOF joins, OHLCV/VWAP rollups, time travel, and previewable mutations over immutable, time-sorted Parquet segments — driven from a CLI, Rust, or Python, and designed to be safe for AI agents. Written in Rust; Apache-2.0.
Canonical site: https://db.h5i.dev/
This file concatenates the manual and Python API reference as plain markdown for LLM ingestion. Cookbook tutorials are at https://db.h5i.dev/cookbook/.
---
# Overview (https://db.h5i.dev/manual/)
h5i-db is a fast analytical database for quantitative
finance and time-series workloads: an embedded, versioned store with DataFusion SQL,
native ASOF joins, and previewable mutations. It is written in Rust, driven from the
CLI or Python, and built to be safe in the hands of AI agents.
<div class="doc-divider"></div>
A database is a **single directory on disk**: like SQLite or DuckDB, there is no
server process. Data lives in immutable, time-sorted Parquet segments; every write is
an atomic commit that produces a new **version**, and any past version stays readable
forever:
```console
$ h5i-db init market.db
$ h5i-db ingest market.db trades ticks.parquet
$ h5i-db query market.db "SELECT symbol, vwap(price, size) FROM trades GROUP BY symbol"
```
```python
import h5i_db
db = h5i_db.Database("market.db")
df = db.sql("SELECT * FROM h5i('trades', 42)").to_pandas() # time travel
```
## What makes it different
- **Time-series SQL, natively:** full SQL through DataFusion, plus `asof_join`,
`time_bucket`, `vwap`, `ewma`, and `gapfill`. Storage is time-sorted and
declares it, so bucketed aggregations stream instead of sorting.
- **Every write is a version:** immutable segments and per-version manifests make
version reads O(1) and `as_of` lookups O(log V). Named snapshots pin exact
versions across tables, so a backtest stays reproducible.
- **Previewable mutations:** deletes and range replacements can be staged as
**plans**. You get exact affected-row counts and before/after samples first,
then a metadata-only `apply`. A mutation policy can *require* this flow.
- **Crash-safe by construction:** fsync-before-swap, checksums on every object,
and a manifest hash chain. The old head survives a crash at any step.
- **Agent-ready by contract:** machine-readable output formats, structured
errors with a `retryable` flag, stable exit codes, and resource limits as
flags. It is the same CLI and API humans use, safe to hand to automation.
## Finding your way around
## Where to go next
- Never used h5i-db? Start with [Installation](installation.html), then the
[Quickstart](quickstart.html).
- Coming from pandas/Polars research code? The
[Cookbook fundamentals](../cookbook/#00_fundamentals) teach the database
concepts through market-data examples.
- Running it in production? The [Operations guide](operations.html) covers
backup, vacuum, compaction, and the recovery runbook.
- Wiring it into an agent or pipeline? See
[Agents & automation](agents.html) for the machine contract, and
[Notebooks](notebooks.html) for a session whose state survives between
commands.
- Loading vendor data? The [Data on-ramp](data-onramp.html) turns archives,
bar files, trade dumps and live captures into the canonical tables a
[backtest](backtest.html) reads.
---
# Installation (https://db.h5i.dev/manual/installation/)
h5i-db ships as two installable artifacts backed by the same Rust core: the
`h5i-db` command-line tool and the `h5i_db` Python library. Install either or
both; they read and write the same database directories.
## CLI
```console
$ cargo install h5i-db-cli
$ h5i-db --version
```
The binary is self-contained; no runtime dependencies, no server to start.
Requires a Rust toolchain ([rustup.rs](https://rustup.rs)) to build during
install.
## Python
```console
$ pip install h5i-db
```
```python
import h5i_db
print(h5i_db.__version__)
```
The wheel bundles the native engine, so no separate CLI install is needed. The
only required dependency is `pyarrow >= 14`; `to_pandas()` and `to_polars()`
work when pandas/Polars are present.
## Build from source
```console
$ git clone https://github.com/h5i-dev/h5i-db
$ cd h5i-db
$ cargo build --release -p h5i-db-cli # CLI -> target/release/h5i-db
$ pip install maturin
$ maturin develop -m crates/h5i-db-python/Cargo.toml --release # Python module
```
!!! note "Filesystem requirements"
Crash-safety relies on POSIX `fsync` and atomic rename. Keep databases on a
local filesystem (ext4, xfs, apfs, NTFS). On WSL2 use the Linux side
(`~/data/…`), not `/mnt/c`. Network filesystems are not recommended; see
the [Operations guide](operations.html#filesystem-caveats).
## Verifying the install
```console
$ h5i-db init /tmp/smoke.db
$ h5i-db tables /tmp/smoke.db
$ python -c "import h5i_db; h5i_db.Database('/tmp/smoke.db')"
```
Next: the [Quickstart](quickstart.html).
---
# Quickstart (https://db.h5i.dev/manual/quickstart/)
Five minutes from an empty directory to time-series SQL with time travel. The
CLI and the Python library drive the same engine and the same on-disk format,
so pick either, or mix freely.
## CLI
```console
$ cargo install h5i-db-cli
$ h5i-db init market.db
$ h5i-db create-table market.db trades --like ticks.parquet --time-column ts
$ h5i-db ingest market.db trades ticks.parquet
$ h5i-db query market.db "SELECT symbol, vwap(price, size) AS vwap FROM trades GROUP BY symbol"
```
`init` creates the database: a plain directory, no server. `create-table`
infers the schema from an existing Parquet/CSV/Arrow file (or takes explicit
JSON via `--schema`); `--time-column` declares the time axis, which is what
makes pruning and the time-series operators work. `ingest` appends the file's
rows as one atomic, durable commit.
Query output goes to stdout in your chosen `--format`:
```console
$ h5i-db query market.db "SELECT time_bucket('1m', ts) AS bar, vwap(price, size) AS vwap
FROM trades GROUP BY bar ORDER BY bar" --format csv | head -3
bar,vwap
2026-07-01T09:30:00,101.24
2026-07-01T09:31:00,101.31
```
Every write created a version. Look at the history, read an old version, pin a
reproducible snapshot:
```console
$ h5i-db versions market.db trades
$ h5i-db query market.db "SELECT count(*) FROM h5i('trades', 1)" # time travel
$ h5i-db snapshot create market.db eod-2026-07-01
```
And when something needs fixing, preview before you commit:
```console
$ h5i-db delete-range market.db trades --start 2026-07-01T09:30:00Z \
--end 2026-07-01T09:31:00Z --plan
{"plan_id": "5c41…", "summary": {"rows_affected": 12481, "segments_reused": 127}}
$ h5i-db plan show market.db trades 5c41… # before/after samples
$ h5i-db plan apply market.db trades 5c41… # metadata-only swap
```
Finally, `h5i-db ui market.db` serves a loopback-only review surface: pending
plans with previews, a live fork monitor, the version timeline, and an SQL
scratchpad.
## Python
```console
$ pip install h5i-db
```
```python
import pyarrow as pa
import h5i_db
schema = pa.schema([
pa.field("ts", pa.timestamp("us", tz="UTC"), nullable=False),
pa.field("symbol", pa.string()),
pa.field("price", pa.float64()),
pa.field("size", pa.int64()),
])
db = h5i_db.Database("market.db", create=True)
db.create_table("trades", schema, time_column="ts")
db.append("trades", ticks) # pyarrow.Table / RecordBatch(es)
bars = db.sql("""
SELECT time_bucket('1m', ts) AS bar, symbol,
vwap(price, size) AS vwap, sum(size) AS volume
FROM trades GROUP BY bar, symbol ORDER BY bar
""").to_pandas()
```
Time travel and reproducibility:
```python
old = db.read("trades", version=3) # exact version
asof = db.sql("SELECT * FROM h5i('trades', '2026-07-01T00:00:00Z')")
db.snapshot("eod-2026-07-01") # pin all tables
```
Previewable mutation, the same plan/apply flow as the CLI:
```python
plan = db.plan_delete_range("trades", start_us, end_us) # raw µs bounds
plan.summary # {"rows_affected": …, "segments_reused": …}
plan.before_sample # pyarrow.Table of rows to be removed
plan.apply() # or plan.discard()
```
!!! note "Raw time units in Python"
SQL takes RFC3339 strings, but the Python range APIs
(`plan_delete_range`, `read(time_start=…)`) take **raw integers in the
time column's unit**, microseconds for `timestamp[us]`. See
[Core concepts](concepts.html#the-time-axis).
Errors are typed, with a code you can branch on:
```python
try:
db.append("trades", batch)
except h5i_db.ConflictError as e: # another writer won the race
print(e.code, e.retryable) # "version_conflict", True
```
## Where next
- [Core concepts](concepts.html): what a version, segment, snapshot, and plan
actually are.
- [SQL reference](sql.html): `h5i()`, `asof_join`, `time_bucket`, `gapfill`,
and the rest of the function library.
- The [Cookbook quickstart notebook](../cookbook/00_fundamentals/01_quickstart.html),
which is this page as an executed notebook on real data.
---
# Migrating from DuckDB (https://db.h5i.dev/manual/migrating-from-duckdb/)
h5i-db does **not** read `.duckdb` files directly, and that is deliberate. DuckDB's
on-disk format is an internal, engine-versioned block format; a native reader
would mean tracking DuckDB's internals release by release for a run-once
operation. Instead, migration goes through the interchange format both engines
already speak fluently: **Parquet**. DuckDB exports it in one statement, and
`h5i-db ingest` reads it directly.
This page is the recipe plus the "what changes" checklist. Budget a few minutes
per table.
!!! note "Is h5i-db the right destination?"
h5i-db is a **versioned, time-series** store, not a general-purpose OLAP
engine. It fits data that has a **time column** and is **append-mostly**
(market ticks, metrics, event logs) and that you want version history,
time travel, ASOF joins, and crash-safe commits underneath.
If you use DuckDB for ad-hoc general analytics, star-schema BI, or heavy
in-place `UPDATE`/`DELETE`, that is not what h5i-db is for. See
[Core concepts](concepts.html).
---
## The recipe
### 1. Export from DuckDB to Parquet
For a whole database, `EXPORT DATABASE` writes one Parquet file per table plus
the schema, into a directory:
```sql
-- in the duckdb shell, against your existing file
$ duckdb market.duckdb
D EXPORT DATABASE 'export_dir' (FORMAT PARQUET);
```
For a single table, `COPY` is enough:
```sql
D COPY trades TO 'trades.parquet' (FORMAT PARQUET);
```
Parquet preserves column names, types, and (unlike CSV) timestamp precision and
nullability, so it is always preferable to CSV for migration.
### 2. Create the table in h5i-db
Infer the schema straight from the exported Parquet, then **declare the time
column**. That step has no DuckDB equivalent, and it is what turns on manifest
pruning, streaming rollups, and ASOF joins; pick the column your queries filter
and order by.
```console
$ h5i-db init market.db
$ h5i-db create-table market.db trades --like export_dir/trades.parquet --time-column ts
```
If you want an explicit sort key beyond the time column (e.g. time then
symbol), pass `--sort-key ts,symbol`. See
[`create-table`](cli.html#h5i-db-create-table) for the full type list.
### 3. Ingest the data
```console
$ h5i-db ingest market.db trades export_dir/trades.parquet
```
Repeat steps 2–3 per table. For many tables, loop in your shell:
```console
$ for f in export_dir/*.parquet; do
t=$(basename "$f" .parquet)
h5i-db create-table market.db "$t" --like "$f" --time-column ts
h5i-db ingest market.db "$t" "$f"
done
```
### 4. Verify
```console
$ h5i-db tables market.db # row counts + time ranges per table
$ h5i-db query market.db "SELECT count(*) FROM trades"
```
Row counts should match DuckDB's `SELECT count(*)`. Every ingest is committed as
version 1 (and up); nothing is destructive, so a mistaken load is one
[`restore`](cli.html#h5i-db-restore) away from undone.
!!! tip "Python instead of the CLI"
The same flow works from Python: read the Parquet with `pyarrow` and
`db.append(table)`. DuckDB can hand you Arrow with zero copy via
`duckdb.sql("SELECT * FROM trades").arrow()`, which you pass straight to
`db.append("trades", ...)`. See the [Quickstart](quickstart.html).
---
## SQL dialect: what ports and what changes
The query engine is [DataFusion](sql.html), so standard analytical SQL (joins,
CTEs, window functions, `date_trunc`, `stddev`, `corr`,
`approx_percentile_cont`, `INTERVAL` arithmetic) moves over unchanged. String
literals are single-quoted; identifiers are case-insensitive. What differs:
### Ports cleanly
- **`time_bucket(...)`**: h5i-db adopts DuckDB/TimescaleDB semantics
deliberately, so bucketed rollups port as-is.
- **ASOF joins**: supported, and semantically differential-tested against
DuckDB as the oracle (ties, NULLs, strict/non-strict). The syntax differs
slightly, using `ASOF JOIN … MATCH_CONDITION` or the `asof_join(...)` table
function; see the [SQL reference](sql.html).
- Standard aggregates, window functions, and scalar date/time functions.
### Needs rewriting
DuckDB-specific extensions have no DataFusion equivalent, so rewrite these:
| DuckDB construct | In h5i-db |
|---|---|
| `read_parquet(...)`, `read_csv_auto(...)` in `FROM` | Data is **ingested first**; query plain table names |
| `PIVOT` / `UNPIVOT`, `SUMMARIZE` | Rewrite with explicit `CASE` / aggregation |
| `QUALIFY` | Wrap the window expression in a subquery + `WHERE` |
| `SELECT * EXCLUDE (...) / REPLACE (...)`, `COLUMNS(...)` | List columns explicitly |
| `USING SAMPLE` | `ORDER BY random() LIMIT n` or app-side sampling |
| List/struct/map sugar, list comprehensions | Restructure; nested types are not the target model |
| `HUGEINT`, `DECIMAL(p,s)`, nested `STRUCT`/`LIST` columns | Cast to a supported type at export (see below) |
### Time travel is different syntax
DuckDB's `AT (VERSION => …)` is rejected by its native storage. In h5i-db, time
travel is part of the query surface: read any past version with the `h5i()`
table function or the CLI's `--version` flag:
```sql
SELECT count(*) FROM h5i('trades', 1); -- the table as of version 1
```
See [Reading tables & time travel](sql.html) for `as_of` timestamp resolution.
---
## Workflow differences
- **Mutations are previewable, not free-form DML.** There is no ad-hoc
`UPDATE`/`DELETE` mid-query. Range deletes and updates go through a
**plan → apply** gate you can inspect (and policy can require approval for):
`plan_delete_range(...).apply()` in Python, the mutation verbs on the CLI. See
[Core concepts](concepts.html).
- **Writes are commits.** Each `ingest` is an atomic, immutable version. There
is no transaction you `COMMIT`/`ROLLBACK`; instead you `restore` to an earlier
version if a load was wrong.
- **Sessions see a consistent snapshot.** `FROM trades` is snapshot-bound when
the session opens, so every query in a session sees one consistent set of
versions; use `h5i('trades', N)` to reach across versions explicitly.
---
## Type mapping notes
h5i-db's type set is intentionally lean (see the
[`create-table` types](cli.html#h5i-db-create-table)): the integer/float family,
`utf8`, `bool`, `timestamp_s/ms/us/ns` (UTC), `date32/date64`. If a DuckDB table
uses `HUGEINT`, `DECIMAL`, `TIME`, `INTERVAL`, or nested `STRUCT`/`LIST`/`MAP`
columns, cast them to a supported representation **during export**, e.g.:
```sql
D COPY (
SELECT ts,
symbol,
CAST(notional AS DOUBLE) AS notional -- DECIMAL -> float64
FROM trades
) TO 'trades.parquet' (FORMAT PARQUET);
```
Timezone-aware DuckDB `TIMESTAMPTZ` values are stored UTC on the h5i-db side;
export as UTC and keep your time column in a single `timestamp_*` precision.
---
## What you gain
Once migrated, the same data carries capabilities DuckDB does not offer:
O(1) reads of any historical [version](concepts.html), previewable and
policy-gated mutations, crash-safe-by-construction commits, and time-series
operators (`vwap`, `ewma`, gapfill/resample, sort-free ASOF) that run on
declared-sorted, immutable segments. That structure is also why OHLCV+VWAP
rollups and ASOF joins are faster here than on DuckDB-over-Parquet; the numbers
are in the
[benchmark results](https://github.com/h5i-dev/h5i-db/blob/main/benchmarks/RESULTS.md).
---
# Core concepts (https://db.h5i.dev/manual/concepts/)
h5i-db rests on a handful of ideas. Once they click, every command and API
method is predictable.
## The database is a directory
A database is one directory on disk; there is no server. Inside it, each table
owns immutable Parquet **segments** (the data), immutable JSON **manifests**
(one per version), and a single small mutable file, `HEAD`, that names the
current version:
```text
market.db/
FORMAT
catalog/ snapshots/
tables/<table-uuid>/
HEAD # the only mutable file per table
manifests/<seq>.json # one immutable manifest per version
segments/<uuid>.parquet # immutable, time-sorted data
```
Everything except `HEAD` is write-once. That single fact is what makes
backups a file copy, crash recovery trivial, and old versions permanently
readable. See the [Operations guide](operations.html).
## Versions: every write is a commit
Every write (append, full write, range replace/delete, restore, compact)
produces a new **version**: a manifest listing exactly which segments make up
the table at that point, plus statistics, a commit timestamp, and your
optional `--note`. Publishing a version is an atomic compare-and-swap on
`HEAD`:
- **Readers never block** and never see partial writes; a reader holds a
manifest, and everything a manifest references is immutable.
- **Racing writers never interleave.** The loser of the swap gets an explicit
`version_conflict` error (retryable). Pass `--expected-version` /
`expected_version=` to demand the head hasn't moved (optimistic locking).
- **History is a hash chain.** Each manifest records its parent's blake3
checksum; `verify` walks the chain.
Reading old versions is O(1), because a version *is* a manifest read:
```sql
SELECT * FROM h5i('trades', 42); -- exact version
SELECT * FROM h5i('trades', '2026-07-01T00:00:00Z'); -- as of commit time
```
`restore` makes an old version current by committing a *new* version with the
old contents: history only moves forward, and nothing is erased.
## The time axis
Declaring `--time-column` at table creation is the schema decision with the
widest consequences:
- Storage is **sorted by the time column** (plus any additional `--sort-key`
columns). Appends must respect that order; out-of-order appends are
rejected, which is what keeps bucketed aggregations streaming instead of
sorting.
- Each segment's manifest entry records its **time range and column min/max**.
A query for a narrow time window prunes non-overlapping segments *before any
I/O*. Run `query --stats` to watch it happen.
- The time-series operators (`asof_join`, `gapfill`, `tail`, time-range plans)
are keyed off the declared time column.
!!! warning "Raw units"
APIs that take numeric time arguments (plan ranges, `gapfill` step, ASOF
tolerance, `read(time_start=…)`) use **raw integers in the time column's
unit**. For the common `timestamp[us]` column, that is microseconds:
`60_000_000` is one minute. The CLI's `--start`/`--end` accept RFC3339
strings and convert for you.
## Snapshots
A **snapshot** pins the current version of one or more tables under a name:
```console
$ h5i-db snapshot create market.db eod-2026-07-18
```
```sql
SELECT * FROM h5i('trades', 'eod-2026-07-18');
```
A snapshot is a tiny checksummed JSON map `{table → version}`: O(1) to
create, free to keep. Snapshots make backtests reproducible ("run against
`eod-2026-07-18`, forever") and answer audit questions ("what did we know at
close on date X?"). A table pinned by a snapshot cannot be dropped.
## Forks
A snapshot pins a version so you can *read* it later. A **fork** pins one and
lets you write on top:
```console
$ h5i-db fork create market.db agent-01
$ h5i-db ingest market.db features out.parquet --fork agent-01
```
Creating a fork writes one small JSON object and copies no data. The first
write to an existing table's name inside the fork copies that table's
*manifest* — a list of segment metadata, kilobytes — into a new table, so the
fork's rows are its own while the base's segments stay shared. Twenty agents
forking a 50 GB dataset store 50 GB once plus whatever each of them writes.
Because a fork's tables are ordinary tables, they contend with nothing: two
agents writing "the same" table in two forks are writing two different tables.
Reads resolve through the pin rather than through the head, so a fork neither
sees nor is disturbed by commits landing on the base meanwhile.
Work comes back with `promote`, which replaces one base table with the fork's
version if the base has not moved since the fork was made:
```console
$ h5i-db fork diff market.db agent-01
$ h5i-db fork promote market.db agent-01 --table features
$ h5i-db fork drop market.db agent-02
```
The first promote wins and later ones are rejected rather than merged: the
conflict unit is the whole table. There is no row-level merge, on purpose —
speculative work is mostly discarded, and `drop` is the common ending.
A fork pins the base versions it reads, so those versions cannot be expired
while it lives; `fork list` shows how much each is holding back. Dropping the
fork releases it.
`--as-of` forks the past instead of the present:
```console
$ h5i-db fork create market.db backtest --as-of 2026-03-01T00:00:00Z
```
That gives a workspace where the base is frozen at a past instant but you can
still materialise features, intermediate results, and scratch tables — the
writable counterpart of a read-only historical pin.
### Forks nest
A fork is itself something to fork. Add `--fork` to `fork create` and the new
fork is created *inside* that one, pinning its tables the way a top-level fork
pins the database's:
```console
$ h5i-db fork create market.db trunk
$ h5i-db fork create market.db branch-a --fork trunk
```
`branch-a` sees the database, plus whatever `trunk` had done when it was
taken, plus its own work, and stays frozen against anything `trunk` does
afterwards. `promote` moves work up exactly one level, so `branch-a` promotes
onto `trunk`, never onto the database. That is what makes a search tree
expressible: refine, evaluate, keep the good subtree, discard the rest.
Depth costs nothing to read. A fork's manifest names its segments by path, so
resolving a table twenty levels down takes the same number of reads as one
level down. There is no chain to replay. Nesting is capped at 32 levels as a
runaway guard.
Dropping is the one place the tree matters: a fork that others are nested
inside cannot be dropped out from under them, and the error names them.
### Querying across forks
`forks()` reads a table from every fork at once, labelling each row with the
fork it came from:
```console
$ h5i-db query market.db "SELECT __fork, avg(price) FROM forks('trades') GROUP BY __fork"
```
This is how you compare outcomes without exporting anything. It is cheap for
the same reason forking is: the forks share their segments, so a segment
several forks can see is read **once** however many reference it, and only
what each fork actually changed is read separately. Pass a comma-separated
list to narrow it (`forks('trades', 'branch-a,branch-b')`), and use
`db.fork_scan("trades")` for the same thing from Python.
Forks whose schema for the table has diverged are refused rather than blended;
`fork diff` is the tool for looking at that.
## Previewable mutations: plan / apply
Destructive operations (`delete-range`, `replace-range`) can run in two modes:
- **Direct**: commit immediately, like any write.
- **Planned** (`--plan` / `plan_delete_range()`): run the *full* write path
(affected rows computed, new segments staged on disk) but stop before
publishing. You get a plan id, a machine-readable summary (rows affected,
segments rewritten vs. reused), and before/after row samples.
```console
$ h5i-db delete-range market.db trades --start … --end … --plan
$ h5i-db plan show market.db trades <plan-id>
$ h5i-db plan apply market.db trades <plan-id> # metadata-only swap
```
`apply` is cheap and atomic; it fails with a conflict if the table head moved
after the plan was made (re-plan, don't retry). `discard` drops the staged
segments; abandoned plans expire after 7 days. Every manifest records whether
it was committed directly or via a reviewed plan (`execution_mode` and the
plan hash), so the history doubles as an audit trail.
## The mutation policy
The **policy** is a per-database set of boolean gates deciding which
operations may commit *without* a reviewed plan:
```console
$ h5i-db policy set market.db direct_delete=false direct_write=false
```
Flags: `direct_append`, `direct_write`, `direct_replace`, `direct_delete`,
`direct_restore`, `direct_compact`. With a flag off, the direct form of that
operation fails with a `policy_violation`; the plan/apply flow still works.
This is the recommended guardrail when [agents](agents.html) or shared
pipelines write to the database: the write path is identical, only the
review requirement changes.
## Queries and sessions
SQL runs on DataFusion with h5i-db's storage underneath:
- **Plain table names** (`FROM trades`) are snapshot-bound when the session
opens, so every query in a session sees a consistent set of versions.
- **`h5i('trades')`** re-resolves to the latest version at each query.
- Segment pruning, projection pushdown, and (for unchanged versions) cached
per-segment aggregate states make repeat analytics fast.
Resource guards are built in: row caps, timeouts, and memory budgets with
disk spilling are available as CLI flags and `sql()` keyword arguments. They
raise clean, typed errors instead of truncating silently.
## Maintenance in one paragraph
`verify` re-checks structural integrity (checksums, object existence;
`--deep` re-reads every segment). `compact` rewrites accumulations of small
segments into target-sized ones; it is a query-speed tool, not a space
reclaimer. `vacuum` deletes *unreachable* objects (crashed-writer debris,
discarded plans), dry run by default. Committed history is never deleted.
Cadence and disk-usage math: [Operations guide](operations.html).
## Read more
- [SQL reference](sql.html): the full function library with signatures.
- [CLI reference](cli.html): every command and flag.
- Cookbook deep dives:
[time travel](../cookbook/00_fundamentals/05_time_travel_and_versioning.html),
[previewable mutations](../cookbook/00_fundamentals/06_previewable_mutations.html),
[maintenance](../cookbook/00_fundamentals/08_maintenance.html).
---
# CLI reference (https://db.h5i.dev/manual/cli/)
```console
$ h5i-db <command> <db> [args…] [--format table|json|jsonl|csv|arrow]
```
The `h5i-db` binary is non-interactive by design: no prompts, no pager, SQL
from an argument or stdin, results on stdout, diagnostics on stderr. The
database path is always the first positional argument of a command (there is
no global `--db` flag).
## Global behavior
### Output formats
`--format` is global and defaults to `table`.
| Format | Behavior |
|---|---|
| `table` | Human-readable aligned columns (buffered; metadata commands render pretty JSON) |
| `json` | One JSON array of row objects; explicit nulls; empty result is `[]` |
| `jsonl` | One compact JSON object per row per line |
| `csv` | With header row; empty result still emits the header |
| `arrow` | Arrow IPC stream on stdout; lossless, pipes into other tools |
### Errors and exit codes
Errors are a single JSON envelope on **stderr**:
```json
{
"schema_version": 2,
"code": "table_not_found",
"message": "table \"trade\" not found",
"retryable": false,
"hint": "run `h5i-db tables <db>` to list tables",
"did_you_mean": "trades",
"next_actions": [
{"cmd": "h5i-db schema market.db trades", "why": "\"trade\" does not exist; \"trades\" is the closest name"},
{"cmd": "h5i-db tables market.db", "why": "list the tables that do exist"}
]
}
```
| Field | Use |
|---|---|
| `code` | Stable machine-readable identifier; branch on this, not on `message` |
| `retryable` | Whether re-running the same call can plausibly succeed |
| `hint` | One line of prose for a human |
| `did_you_mean` | Closest existing identifier when the failure looks like a typo; absent otherwise |
| `next_actions` | Commands you can run verbatim, best first. `<db>` is already substituted with the database you invoked |
| `schema_version` | Bumped on any breaking change to this shape |
Exit codes are stable and branchable:
| Code | Meaning |
|---|---|
| `0` | Success (including broken pipe from `… \| head`) |
| `2` | User error: bad arguments, bad SQL, missing table |
| `3` | Conflict: another writer moved the head; usually retryable |
| `4` | Limit exceeded: `--max-rows`, `--max-bytes`, memory budget, timeout |
| `5` | Internal error |
Diagnostics volume is controlled with `RUST_LOG` (default `warn`).
### Shared write flags
Commands that commit a version (`ingest`, `restore`, `replace-range`,
`delete-range`, `compact`) accept:
| Flag | Meaning |
|---|---|
| `--expected-version <N>` | Require the table head to be exactly version N (optimistic guard); mismatch exits 3 |
| `--note <text>` | Free-text note recorded in the version manifest |
| `--idempotency-key <token>` | Make the mutation replayable exactly once |
`--idempotency-key` is what makes an unattended ingest loop safe to retry. A
repeat carrying the same key finds the commit it already produced and returns
it with `"segments_added": 0` instead of writing the rows a second time, which
matters because a duplicated append does not error: it just leaves the data
wrong from then on. The key is recorded in the commit's own manifest, and
retries are deduplicated against the last 64 commits.
```bash
h5i-db ingest market.db trades day.parquet --idempotency-key load-2026-07-01
```
---
## Database & tables
### `h5i-db context`
Everything needed to orient, in one call: every table's columns, size, segment
count, time range and head version, which operations the policy gates, the
snapshots that exist, and any plan already staged and waiting for review. It
replaces a `tables` → `schema` → `sample` → `versions` walk repeated per table.
```console
$ h5i-db context market.db --format json
$ h5i-db context market.db --budget 2000 # cap the answer in tokens
$ h5i-db context market.db --stale-after 15m # flag tables whose head is old
```
| Flag | Meaning |
|---|---|
| `--budget <tokens>` | Approximate ceiling. Detail is shed in a fixed order (columns, snapshots, notes, then whole tables smallest-first) and whatever went is counted under `omitted`, with the command that recovers it |
| `--stale-after <dur>` | Report per-table commit age and flag anything older than this |
Output is deterministic for a given database state, so it can be cached as a
preamble; `--stale-after` is the only flag that makes it depend on the clock.
### `h5i-db init`
Create a new database directory.
```console
$ h5i-db init market.db
```
### `h5i-db create-table`
Create a table. The schema comes from `--schema` JSON **or** `--like` a data
file (exactly one required).
```console
$ h5i-db create-table market.db trades --like ticks.parquet --time-column ts
$ h5i-db create-table market.db bars \
--schema '[{"name":"ts","type":"timestamp_us","nullable":false},
{"name":"symbol","type":"utf8"},{"name":"close","type":"float64"}]' \
--time-column ts --sort-key ts,symbol
```
| Flag | Meaning |
|---|---|
| `--schema <json>` | Explicit schema. Types: `int8..int64`, `uint8..uint64`, `float32/float64`, `utf8`, `bool`, `timestamp_s/ms/us/ns` (UTC), `date32`, `date64`. Aliases accepted: `int`→int32, `long`/`bigint`→int64, `float`→float32, `double`→float64, `string`/`str`/`text`→utf8, `boolean`→bool, `date`→date32, `timestamp`→timestamp_ns. `nullable` defaults to `true`. |
| `--like <file>` | Infer the schema from a Parquet/CSV/Arrow file |
| `--time-column <col>` | Time index column; strongly recommended for time-series tables, and forced non-nullable |
| `--sort-key <cols>` | Comma-separated sort key (defaults to the time column) |
| `--target-segment-mb <N>` | Target segment size in MiB of in-memory data (default 128) |
### `h5i-db tables`
List tables with row counts and time ranges. Columns: `table`, `version`,
`rows`, `bytes`, `segments`, `time_range`, `time_column`.
### `h5i-db schema`
Show a table's schema and options (`schema_revision`, `time_column`,
`sort_key`, fields with types and nullability).
### `h5i-db sample`
Show the first rows of a table.
| Flag | Meaning |
|---|---|
| `-n, --rows <N>` | Row count (default 10) |
| `--version <N>` | Read at a specific version |
### `h5i-db rename`
Rename a table: a catalog edit, no data moves.
```console
$ h5i-db rename market.db trades trades_raw
```
### `h5i-db drop-table`
Drop a table and its data. Refuses if the table is pinned by a snapshot or a
fork, and requires `--yes`:
```console
$ h5i-db drop-table market.db scratch --yes
```
!!! danger "Irreversible"
`drop-table` permanently deletes data; it is the one command that
bypasses versioning. Snapshots protect tables from it, so use them.
---
## Reading & querying
### `h5i-db query`
Run SQL. The query comes from the argument, or stdin when `-`.
```console
$ h5i-db query market.db "SELECT symbol, vwap(price, size) FROM trades GROUP BY symbol" \
--format json --max-rows 1000 --timeout 30s
$ cat report.sql | h5i-db query market.db - --format csv > out.csv
```
| Flag | Meaning |
|---|---|
| `--max-rows <N>` | Abort after N produced rows |
| `--max-bytes <N>` | Stop after N output bytes (checked at batch boundaries); truncation exits 4 with a `limit_exceeded` envelope |
| `--timeout <dur>` | Query timeout, e.g. `30s`, `5m` |
| `--memory-limit-mb <N>` | Memory budget in MiB; enables disk spilling under pressure |
| `--spill-dir <path>` | Spill directory (with `--memory-limit-mb`) |
| `--threads <N>` | Number of threads / partitions |
| `--stats` | Print scan/pruning statistics to stderr after the query |
| `--predicate-cache` | Read and build immutable predicate-cache sidecars |
| `--as-of <at>` | Pin every table at a read point: a version, an RFC3339 availability timestamp, or a snapshot name |
| `--decision-time <ts>` | Hide rows stamped after this RFC3339 instant |
| `--embargo <dur>` | Extra gap subtracted from `--decision-time`, e.g. `1d` |
See the [SQL reference](sql.html) for `h5i()` time travel, `asof_join`, and
the time-series function library.
#### Point-in-time reads
`--as-of` and `--decision-time` bound what a query can read, on two different
clocks. `--as-of` is the *commit* clock: it pins every table at a version, so a
restatement that landed later is not visible. `--decision-time` is the *data*
clock: it hides rows whose time column is later than the instant, so a window
cannot reach forward and a join cannot pull in a later timestamp.
They are independent because under a bulk load the two clocks are far apart:
ten years of history committed this morning has a first commit of today, so an
arrival pin at a historical instant resolves to nothing while an event-time
cutoff at that same instant is exactly what you want. When the clocks do agree,
an RFC3339 `--as-of` also supplies the decision time, so the common case stays
one flag.
```bash
# One decision date, both axes, for a walk-forward step.
h5i-db query market.db "SELECT vwap(price, size) FROM trades" \
--as-of 2026-07-01T00:00:00Z --decision-time 2026-07-01T00:00:00Z --embargo 1d
```
`H5I_DB_AS_OF`, `H5I_DB_DECISION_TIME` and `H5I_DB_EMBARGO` set the same
things for a whole shell, which is how you hand a bounded view to a script or
an agent rather than relying on it to pass the flag every time. An explicit
flag still wins over the environment.
The bound is part of the table, not a filter you compose with, so a query that
explicitly asks for later rows still gets none. Three things refuse rather
than being quietly exempt: a table with no time column, a table whose time
column is a bare integer carrying no unit, and, under `--decision-time`, the
table functions (`h5i()`, `asof_join()`, `gapfill()`, `resample()`, `tail()`,
`latest_on()`), which read their tables directly and cannot have the cutoff
pushed into them. Under `--as-of` alone those functions work normally,
resolving at the pinned version; selecting a *different* read point from
inside a pinned session is refused.
### `h5i-db versions`
List a table's committed versions: `version`, `op`, `committed_at`, `rows`,
`bytes`, `segments`, `note`.
### `h5i-db arrival-delta`
How much a query's answer moved because data arrived late: run it once at the
current head and once at an earlier read point, and report the difference.
What moves is what depended on commits that had not landed at the earlier
point, which in a backtest is the part of a metric a live run starting that
day could not have earned.
```console
$ h5i-db arrival-delta market.db \
"SELECT symbol, vwap(price, size) AS vwap FROM trades GROUP BY symbol" \
--as-of 2026-07-01T16:00:00Z --format json
```
| Flag | Meaning |
|---|---|
| `--as-of <at>` | The decision point: an integer **version**, an RFC3339 **timestamp** (matched by commit *availability* time, like `h5i()`), or a **snapshot** name |
| `--tolerance <f>` | Absolute per-cell delta below which a difference is treated as noise (default `1e-9`) |
The report carries `changed`, per-column `head → asof (delta)` for the common
single-row-metric case, `max_abs_delta`, `row_count_differs`,
`withheld_versions` (per table, the head-vs-as-of version gap), `vacuous`, and
`notes`.
**Read it as a measurement, not a verdict.** Look-ahead comes in many shapes
and this sees one of them: rows that exist now but had not been published. A
signal that reads its own bar leaves the delta unmoved, so no value of it, high
or low, clears a query of look-ahead; bound the event-time axis with
[`query --decision-time`](#h5i-db-query) for that. And check `vacuous` before
reading the number: when both read points resolve to the same version, which is
the normal state of a bulk-loaded database, the zero is arithmetic rather than
evidence, and the report says so in `notes`.
---
## Writing data
### `h5i-db ingest`
Ingest Parquet/CSV/Arrow into a table, from a file or from stdin with `-`.
```console
$ h5i-db ingest market.db trades ticks.parquet
$ curl -s https://…/ticks.csv | h5i-db ingest market.db trades - --input-format csv
```
| Flag | Meaning |
|---|---|
| `--input-format <fmt>` | `auto` (default) \| `parquet` \| `csv` \| `arrow`. Auto uses the file extension, or sniffs leading bytes on stdin |
| `--mode <mode>` | `append` (default) for a strict ordered append; `write` to replace the table contents |
| `--retries <N>` | Retry appends on version conflicts (default 5; safe for pure appends) |
Plus the [shared write flags](#shared-write-flags). Input batches are
schema-aligned against the table (purely representational casts like
timezone-less CSV timestamps are applied automatically). CSV assumes a header
row.
!!! note "Arrow over stdin"
Pipe an Arrow IPC **stream**, not an IPC *file*: a file's random-access
footer can't be consumed from a pipe. h5i-db detects the difference and
tells you.
### `h5i-db restore`
Make a historical version current. History only moves forward, so restore
commits a *new* version holding the old contents.
```console
$ h5i-db restore market.db trades 42 --note "roll back bad load"
```
### `h5i-db replace-range`
Replace all rows in `[start, end)` of the time column with the given input.
```console
$ h5i-db replace-range market.db trades \
--start 2026-07-01T09:30:00Z --end 2026-07-01T16:00:00Z \
--input corrected.parquet --plan
```
| Flag | Meaning |
|---|---|
| `--start <t>` | Range start, **inclusive**; RFC3339, or a raw integer in the column's unit |
| `--end <t>` | Range end, **exclusive** |
| `--input <file>` | Replacement data (or `-` for stdin); **omit to delete the range** |
| `--input-format <fmt>` | As in `ingest` |
| `--plan` | Stage a previewable plan instead of committing immediately |
### `h5i-db delete-range`
Delete all rows in `[start, end)`, shorthand for `replace-range` with no
input. Same `--start`/`--end`/`--plan` flags.
```console
$ h5i-db delete-range market.db trades --start 09:30… --end 09:31… --plan
{"plan_id": "5c41…", "summary": {"rows_affected": 12481, "segments_reused": 127}}
```
---
## Plans, policy & snapshots
### `h5i-db plan`
Manage previewable-mutation plans (created by `--plan` above).
| Subcommand | Meaning |
|---|---|
| `plan list <db> <table>` | Pending plans: `plan_id`, `op`, `base_version`, `created_at`, `expired`, summary |
| `plan show <db> <table> <plan-id>` | Full plan JSON on stdout; before/after row samples on stderr |
| `plan apply <db> <table> <plan-id>` | Publish the plan; fails with a conflict if the head moved |
| `plan discard <db> <table> <plan-id>` | Drop the plan; staged segments become vacuumable |
Plans expire after 7 days; see
[plan hygiene](operations.html#mutation-plan-hygiene).
### `h5i-db policy`
The mutation policy decides which operations may commit **without** a
reviewed plan.
```console
$ h5i-db policy show market.db
$ h5i-db policy set market.db direct_delete=false direct_write=false
```
Keys: `direct_append`, `direct_write`, `direct_replace`, `direct_delete`,
`direct_restore`, `direct_compact`, each `true`/`false`. The update is an
atomic read-modify-write.
The mutation policy gates *who may write directly*; the **data policy** below
gates *what data may be written at all*.
### `h5i-db data-policy`
Opt-in, per-table data-safety constraints checked on every write (and at plan
time), so a violating mutation is refused before it can be applied. A table
with no policy is unconstrained and pays only one metadata lookup on the write
path; reads are never touched.
| Subcommand | Meaning |
|---|---|
| `data-policy get <db> <table>` | Show the table's policy (empty when unset) |
| `data-policy set <db> <table> <policy>` | Install a typed JSON policy (inline JSON, `@path` to read a file, or `-` for stdin) |
| `data-policy clear <db> <table>` | Remove the policy (writes become unconstrained) |
```console
$ h5i-db data-policy set market.db trades '{"constraints":[
{"name":"positive_price",
"predicate":{"compare":{"column":"price","op":"gt","value":{"float":0.0}}},
"on_fail":"reject"}]}'
```
Predicates compose `not_null`, `compare`, and `in_set` with `and`/`or`/`not`;
`on_fail` is `reject` (fail the write) or `warn`. Evaluation is fail-closed: a
`NULL` never satisfies a comparison. A violation raises `data_policy_violation`
(exit `2`, not retryable).
### `h5i-db snapshot`
Pin table versions under a name.
| Subcommand | Meaning |
|---|---|
| `snapshot create <db> <name> [tables…]` | Pin current versions (all tables when omitted); `--note` supported |
| `snapshot list <db>` | List snapshots |
| `snapshot delete <db> <name>` | Delete a snapshot (the versions it pinned remain readable by number) |
### `h5i-db fork`
Writable workspaces over a pinned base, for running several lines of work
against one dataset at once. See [Forks](concepts.html#forks) for the model.
| Subcommand | Meaning |
|---|---|
| `fork create <db> <name>` | Pin every table and open a workspace; `--note`, `--as-of`, `--meta`, `--count` supported |
| `fork list <db>` | Every fork with lineage and liveness: `parent`/`depth`, `commits_own`, `last_write_ns`, `stale_shadows` (promotes that would now conflict), what it owns (`bytes_own`) and what it holds back (`bytes_pinned`) |
| `fork show <db> <name>` | One fork's pins and metadata |
| `fork diff <db> <name>` | What the fork changed, from manifests alone; `--table` to narrow |
| `fork promote <db> <name> --table <t>` | Land one of its tables on the base |
| `fork drop <db> <name>…` | Delete the named forks and everything they own |
```console
$ h5i-db fork create market.db agent-01 --note "hypothesis 1"
$ h5i-db ingest market.db features out.parquet --fork agent-01
$ h5i-db query market.db "SELECT count(*) FROM features" --fork agent-01
$ h5i-db fork diff market.db agent-01
$ h5i-db fork promote market.db agent-01 --table features
$ h5i-db fork drop market.db agent-01
```
`fork create` accepts `--as-of <rfc3339>` to pin the past instead of the
present, and `--meta` (inline JSON, `@file`, or `-`) to record whatever ties
the fork back to the run that made it.
**Wide fanouts.** `fork create <db> <name> --count N` creates
`<name>-0000 … <name>-000(N-1)` over a *single* resolution of the base, and
`fork drop` takes several names at once. Every fork of one base at one instant
pins the same versions, so the batch does one pass over the catalog however
many branches it makes, which is what makes a few hundred short-lived
branches a reasonable thing to create and then throw away. With `--count` the
command returns a JSON list; without it, a single object as before.
**The `--fork` flag.** Every data command takes `--fork <name>` and then reads
and writes inside that workspace, so an existing script runs unchanged against
a fork by adding one flag. Database-wide commands (`snapshot`, `vacuum`, fork
management) refuse it and say so: they move state a fork's siblings depend
on. `fork create` is the exception: with `--fork` it creates the new
fork *inside* that one (see [Forks](concepts.html#forks-nest)).
**Promotion conflicts.** `fork promote` compare-and-swaps against the version
the fork started from. If the base moved, it exits 3 with
`code: "promote_conflict"` and `retryable: false` — retrying cannot help,
because the work was computed against a base that no longer exists. Re-fork
and re-run, or drop the fork.
Compaction is the one exception, and it is handled for you. If every
intervening base commit was a compaction then the base's *rows* did not
change, only where they live, so the promote is **rebased** onto the new
layout instead of rejected, and the result reports `rebased_from`. That works
while the fork still holds every row it inherited; a fork that deleted
inherited rows cannot be replayed from metadata (a compacted segment may merge
rows it dropped with rows it kept), so that case still conflicts and the
message says why.
---
## Notebooks
### `h5i-db nb`
In-terminal, Jupyter-compatible notebooks whose kernel persists between
invocations, with `%%sql` cells that run in-process against a database
instead of through a Python driver. The same commands are on a standalone
binary, `h5i-nb`; both drive the same sessions.
```console
$ h5i-db nb new research.ipynb --db market.db
$ h5i-db nb exec research.ipynb --code "df = load()" # the kernel outlives the command
$ h5i-db nb exec research.ipynb --code "df.shape"
$ h5i-db nb watch research.ipynb --split right # live read-only pane
```
| Subcommand | Meaning |
|---|---|
| `nb new <nb>` | Create a notebook; `--kernel <spec>` (default `python3`), `--db <path>` for its `%%sql` default |
| `nb exec <nb>` | Append a cell and run it; `--code <src>\|-`, `--timeout <secs>` (default 300), `--stream`, `--detach`, `--raw` |
| `nb run <nb>` | Re-run existing cells; `--cells 3-7\|1,4,9\|all`, `--from-clean`, `--keep-going` |
| `nb cells <nb>` | Index, id, type, execution count and output shape per cell |
| `nb output <nb> <cell>` | One cell's output; `--index <n>`, `--raw`, `--save <path>` |
| `nb edit <nb> …` | `set`, `insert`, `delete`, `move`, `clear-outputs` — no execution |
| `nb view <nb>` | Editable terminal UI; `--split right\|left\|down\|up` |
| `nb watch <nb>` | Live read-only view: no lock, no kernel, no writes |
| `nb ls` | Notebook sessions running on this machine |
| `nb export <nb>` | `--to md\|py\|html`, `-o <path>`, `--without-outputs` |
| `nb inspect\|complete <nb> <code>` | The kernel's `?` and completion, from a shell; `--cursor <n>` |
| `nb kernel …` | `list`, `start`, `status`, `interrupt`, `restart` (`--clear-outputs`), `stop` |
Output is token-budgeted by default — long text elided in the middle, frames
summarised by shape, images written to `<notebook>_files/` and reported as
paths — with the untruncated bytes still in the `.ipynb` behind `--raw`.
Notebook failures use their own codes (`cell_raised`, `execute_timeout`,
`session_busy`, `kernel_died`) in the same error envelope.
See [Notebooks](notebooks.html) for the full guide.
---
## Maintenance & tools
### `h5i-db compact`
Rewrite small segments into target-sized ones, as a new version. Row count is
verified preserved.
| Flag | Meaning |
|---|---|
| `--target-mb <N>` | Override the target segment size (MiB of in-memory data) |
### `h5i-db vacuum`
Remove unreachable objects, dry run unless `--apply`.
```console
$ h5i-db vacuum market.db # inspect candidates
$ h5i-db vacuum market.db --apply # actually delete
```
| Flag | Meaning |
|---|---|
| `[table]` | Restrict to one table (optional positional) |
| `--grace-seconds <N>` | Never touch objects newer than this (default 3600) |
| `--apply` | Actually delete |
Read [the cadence guidance](operations.html#vacuum) before scripting this.
### `h5i-db verify`
Check structural integrity: checksums, hash chain, object existence.
```console
$ h5i-db verify market.db trades --deep
```
`--deep` additionally re-reads every segment and verifies content checksums.
Problems are reported in the chosen format and the command exits non-zero.
### `h5i-db demo`
Build a small database and walk the whole arc on real data: ingest, the metric
a strategy would have traded on, a vendor restatement previewed through
plan/apply, the arrival delta that correction reveals, and a session that
cannot read past its decision instant. Takes about a second.
```console
$ h5i-db demo
$ h5i-db demo --dir ./scratch --keep # keep the database it builds
```
### `h5i-db ui`
Launch the local review UI, loopback only.
| Flag | Meaning |
|---|---|
| `--port <N>` | Port (default 7351) |
| `--allow-mutations` | Enable plan apply/discard from the UI (default: read-only) |
The UI shows pending plans with previews, agent backtest experiments, a live
fork monitor, the version timeline with audit badges, version diffs, and an
SQL scratchpad that reports pruning per query.
The **Experiments** tab routes attention using the state recorded on each
`bt-*` run fork: `needs-decision` > `failed/warned` > `finished-unseen` >
`running` > `seen`. Experiments inherit their most urgent trial. Opening a
trial detail—not scanning a list—marks it seen in the browser; the tab badge
counts unseen warnings. Attention, all-trials, and leaderboard views remain
separate so a best score cannot hide a blocked or suspicious run.
The **Forks** tab renders the fork lineage as a tree that updates itself over
a server-sent event stream: each fork carries one glanceable status —
`conflict` (the base moved under a shadowed table, so promote will refuse),
`working` (committed within the last 15 s), `ahead` (holds unpromoted
commits), or `idle` — plus what it wrote, what its pins hold back, and the
agent metadata attached at `fork create --meta`. Selecting a fork shows its
per-table divergence and copy-ready promote/drop commands; the UI itself
never mutates forks. `tools/fork_demo.sh <db>` drives a simulated agent
swarm against a database if you want to watch the tree move, and
`tools/demo_data.py` seeds one first — synthetic multi-symbol ticks
(`synth --symbols AAPL,MSFT,NVDA --db <db>`) or a day of real 1s bars from
Binance's public archive (`binance --symbols BTCUSDT,ETHUSDT --db <db>`,
no API key).
---
# Notebooks (https://db.h5i.dev/manual/notebooks/)
`h5i-db nb` is a notebook *client* for the terminal: it owns a real
`.ipynb` file, drives a real Jupyter kernel, and renders results for two
audiences at once — a human in a TUI, and a program reading a
token-budgeted digest on stdout. The same commands are available on a
standalone binary, `h5i-nb`, which drives the same sessions.
The property everything else follows from: **the kernel persists between
invocations.** `exec` twice and the second cell sees what the first defined,
so a forty-second load is paid once instead of once per idea. If you would
run a script once and never again, run the script; this is for the case
where state accumulates.
```console
$ h5i-db nb new research.ipynb --kernel python3 --db market.db
$ h5i-db nb exec research.ipynb --code "import pandas as pd; df = pd.read_parquet('big.parquet')"
$ h5i-db nb exec research.ipynb --code "df.shape" # df is still there
```
The file is nbformat v4.5 and stays readable in JupyterLab. Writes are
atomic (temp file, fsync, rename) and byte-compatible with `nbformat`'s own
writer, so opening a notebook in JupyterLab does not churn the diff.
## Running cells
`exec` appends a cell and runs it. `run` re-runs cells the notebook already
has.
```console
$ h5i-db nb exec research.ipynb --code "edge(book)"
$ h5i-db nb exec research.ipynb --code - <<'PY'
def edge(book):
return book.bid - book.ask
PY
$ h5i-db nb run research.ipynb --cells 3-7
$ h5i-db nb run research.ipynb --from-clean
```
`--code -` (the default) reads the cell from stdin, which is how code
containing quotes and newlines gets in without fighting the shell.
| `exec` flag | Meaning |
|---|---|
| `--code <src>` | Cell source, or `-` for stdin (default `-`) |
| `--timeout <secs>` | Interrupt the cell after this many seconds; `0` disables (default 300) |
| `--stream` | Print output as it arrives rather than only at the end |
| `--detach` | Return as soon as the cell is queued |
| `--raw` | Do not elide long output |
| `run` flag | Meaning |
|---|---|
| `--cells <sel>` | `3`, `3-7`, `3-`, `1,4,9`, or `all` (default `all`) |
| `--from-clean` | Restart the kernel and clear outputs first |
| `--keep-going` | Keep going after a cell raises |
| `--timeout`, `--raw` | As in `exec` |
`run --from-clean` is the reproducibility check: if it passes, the notebook
tells the truth about what produced its outputs.
## Output is budgeted, and nothing is lost
Cell output is summarised on stdout, not dumped:
- **Text** is elided in the middle: head lines, tail lines, and a
`… 4,912 lines elided …` marker between them.
- **Frames** are re-rendered as a compact aligned table — first and last
rows plus a `[10,000 rows x 12 columns]` shape line. For the next
decision, the shape is usually the whole information content.
- **Images** are never inlined as base64. They are written to
`<notebook>_files/cell-<id>-<n>.png` (nbconvert's convention, so
JupyterLab renders them too) and reported as a path.
- **Tracebacks** are printed in full, ANSI stripped. A tool that discards
the traceback on error forces a re-run to find out what broke.
The untruncated output stays in the `.ipynb`. `--raw` prints it, and
`nb output --save <path>` writes a binary output to a file.
```console
$ h5i-db nb cells research.ipynb # index, id, type, exec count, output shape
# id type exec outputs source
0 a7822631 code 1 result(application/vnd.h5i.table+text+text/html) %%sql
1 5afe1a3f code 2 - x = 41
2 e00300f7 code 3 result x + 1
$ h5i-db nb output research.ipynb 2 # index or cell id
$ h5i-db nb output research.ipynb 2 --index 1 # one output, when a cell made several
$ h5i-db nb output research.ipynb 2 --raw
```
Reach for `--raw` when you need the bytes. The digest is what makes a
notebook cost less context than a script, and `--raw` gives that back.
## `%%sql`: querying without an interpreter
A cell whose first line is `%%sql` runs against an h5i-db database
**in-process**. The magic is resolved by the client, not by the kernel, so
the statement never crosses into Python: no interpreter, no driver round
trip. Schema discovery and the first twenty exploratory queries need no
kernel at all.
```console
$ h5i-db nb exec research.ipynb --code - <<'SQL'
%%sql
SELECT symbol, count(*) AS n FROM trades GROUP BY symbol ORDER BY n DESC
SQL
[1] cell 0 · ok · 3ms
+--------+-----+
| symbol | n |
+--------+-----+
| AAPL | 120 |
| MSFT | 120 |
+--------+-----+
[2 rows x 2 columns]
```
| Magic option | Meaning |
|---|---|
| `--db <path>` | Database for this cell; `--database` is accepted too |
| `--fork <name>` | Query inside a [fork](concepts.html#forks) |
| `--into <name>` | Bind the result in the Python kernel under this name |
| `--max-rows <N>` | Cap the *rendered* table |
| `--write` | Open the database read-write for this cell |
`h5i-db nb new … --db market.db` records a notebook-wide default in the
file's own metadata, so it travels with the notebook and the `%%sql` cells
need no `--db`.
Two options are worth reading twice:
- **`--into` binds the full result, not the rendered one.** The handoff is
Arrow through a temp file, so `--max-rows 1 --into frame` prints one row
and still binds all of them.
```console
$ h5i-db nb exec research.ipynb --code - <<'SQL'
%%sql --into frame --max-rows 1
SELECT symbol, price FROM trades LIMIT 5
SQL
[5] cell 4 · ok · 1ms
stderr: note: kept the first 1 rows of a larger result (--max-rows)
…
frame: pandas.DataFrame 5 rows x 2 columns
```
- **`--write` is required before a cell can modify anything.** Without it
the database is opened read-only, which also means opening it never runs
transaction recovery against a database another process may be using. Add
it deliberately, not by habit.
The syntax deliberately matches IPython's `%%sql`, so the notebook still
reads correctly in JupyterLab even though JupyterLab would run the cell a
different way.
## Long-running cells
`--detach` returns as soon as the cell is queued. The session process owns
the notebook, so outputs are recorded whether or not anyone is still
attached — including the failure, if it fails.
```console
$ h5i-db nb exec research.ipynb --detach --code "backtest(years=10)"
$ h5i-db nb cells research.ipynb # poll, about once a second
$ h5i-db nb output research.ipynb 7
```
There is never a state where "still running" and "died" look the same.
```console
$ h5i-db nb kernel interrupt research.ipynb # stop the cell, keep the state
$ h5i-db nb kernel restart research.ipynb # throw the state away
$ h5i-db nb kernel status research.ipynb # answers even mid-cell
```
| `kernel` subcommand | Meaning |
|---|---|
| `kernel list` | Installed kernelspecs |
| `kernel start <nb>` | Start the session and its kernel without running anything |
| `kernel status <nb>` | Whether a session is running, and what the kernel is doing |
| `kernel interrupt <nb>` | Interrupt the running cell, keeping in-memory state |
| `kernel restart <nb>` | Restart the kernel; `--clear-outputs` also drops recorded outputs |
| `kernel stop <nb>` | Stop the session and its kernel |
!!! note "Why a supervisor, not `--existing`"
A supervisor process owns the kernel, subscribes to iopub continuously,
and serves a control socket under `$XDG_RUNTIME_DIR`. Reconnecting to a
kernel's connection file per invocation, the way `jupyter console
--existing` does, loses any output produced while no client was
attached — ZMQ PUB drops messages with no subscriber — which is exactly
the long-running cell output that matters most.
## Editing without running
```console
$ h5i-db nb edit research.ipynb set 3 --code "fixed()"
$ h5i-db nb edit research.ipynb insert --at 2 --kind markdown --code "## Findings"
$ h5i-db nb edit research.ipynb delete 4
$ h5i-db nb edit research.ipynb move 4 1
$ h5i-db nb edit research.ipynb clear-outputs
```
`set` drops the cell's now-stale outputs. `insert` appends when `--at` is
omitted and takes `--kind code|markdown|raw` (default `code`). Edits are
routed through the running session, so they are safe while a kernel is
alive.
## Showing a human what you are doing
```console
$ h5i-db nb view research.ipynb # editable TUI
$ h5i-db nb watch research.ipynb --split right # live, read-only
$ h5i-db nb ls # sessions running on this machine
```
`view` is the full terminal UI: Jupyter's key bindings (`Esc`/`Enter` for
command and edit mode, `a`/`b` to insert, `dd` to delete), a completion
popup from `complete_request`, an inspection overlay from `inspect_request`,
and inline images through the kitty and iTerm2 graphics protocols.
`Ctrl-C` interrupts the running cell rather than killing the UI, because the
notebook is the state.
`watch` is a live read-only view. It writes nothing, holds no lock, and
starts no kernel, so any number of them can follow a session someone else is
driving; from it a human can interrupt the cell and nothing else. Both take
`--split right|left|down|up`, which opens the view in a new pane beside the
current one (tmux, zellij, WezTerm, kitty) and returns immediately.
`ls` reports every notebook session on the machine with its kernel, state,
cell count, pid and idle time, and clears up after sessions whose supervisor
died.
!!! note "`Shift+Enter` may not reach the TUI"
Most terminals cannot send it: without the kitty keyboard protocol it
arrives as a plain `Enter`. The protocol is requested where it is
supported, and `e` / `E` are the run bindings that always work.
## Exporting
```console
$ h5i-db nb export research.ipynb --to md # a readable summary for a PR
$ h5i-db nb export research.ipynb --to py # a runnable script, magics commented out
$ h5i-db nb export research.ipynb --to html # one self-contained file, images inlined
$ h5i-db nb export research.ipynb --to md -o notes.md --without-outputs
```
`--without-outputs` leaves the outputs out, for a clean diff or a script
meant to be run rather than read.
## Introspection from a shell
The two requests the TUI uses are also commands, which is what lets an
editor or a script ask the kernel what it knows:
```console
$ h5i-db nb inspect research.ipynb "pd.read_parquet" # what `?` does in IPython
$ h5i-db nb complete research.ipynb "df.gro" # completions at the cursor
```
Both take `--cursor <offset>` when the cursor is not at the end of the code.
## Errors
Failures print the usual [structured envelope](cli.html#errors-and-exit-codes)
on stderr. Branch on `code`, not on the wording.
| `code` | Exit | Retryable | Meaning |
|---|---|---|---|
| `cell_raised` | 2 | no | The code raised; the traceback is on stdout and in the cell |
| `execute_timeout` | 4 | yes | Hit `--timeout`; the cell was interrupted and the kernel still works |
| `session_busy` | 3 | yes | Another cell is running; wait, or interrupt it |
| `kernel_died` | 5 | yes | The kernel is gone; restart, and the state is lost |
| `kernel_not_found` | 2 | no | No such kernelspec; `nb kernel list` shows what exists |
| `cell_index_out_of_range` | 2 | no | `nb cells` shows what exists |
`cell_raised` is the ordinary outcome of exploring, not a tool failure. The
traceback is the answer.
## What does not render
ipywidgets do not draw. Comm traffic is accepted so a widget-producing
library does not crash the session, but nothing interactive appears.
Markdown in exported HTML is shown as preformatted text rather than parsed.
Syntax highlighting is a per-line lexer, so a multi-line string highlights
only on its first line.
---
# SQL reference (https://db.h5i.dev/manual/sql/)
h5i-db speaks full DataFusion SQL (joins, CTEs, window functions,
`date_trunc`, `stddev`, `corr`, `approx_percentile_cont`, `INTERVAL`
arithmetic), plus the time-series extensions documented here. String literals
are single-quoted; identifiers are case-insensitive.
The idiomatic OHLCV query exercises most of the library at once:
```sql
SELECT time_bucket('5m', ts) AS bar, symbol,
first_value(price ORDER BY ts) AS open,
max(price) AS high,
min(price) AS low,
last_value(price ORDER BY ts) AS close,
sum(size) AS volume,
vwap(price, size) AS vwap
FROM trades
GROUP BY bar, symbol
ORDER BY bar;
```
!!! note "Raw time units"
Numeric time arguments (the `gapfill` step, the ASOF tolerance) are **raw
integers in the time column's unit**. For the common `timestamp[us]`
column: `5000000` is 5 seconds, `60000000` is one minute.
## Reading tables & time travel
### Plain names vs `h5i()`
| Form | Resolves |
|---|---|
| `FROM trades` | Snapshot-bound when the session opens; every query in a session sees one consistent set of versions |
| `FROM h5i('trades')` | Latest version, re-resolved at each query |
| `FROM h5i('trades', 42)` | Exact version number |
| `FROM h5i('trades', '2026-07-01T00:00:00Z')` | As-of: latest version whose **commit time** ≤ the RFC3339 timestamp |
| `FROM h5i('trades', 'eod-2026-07-18')` | Version pinned by a named snapshot |
`h5i()` is a standard table function with no special grammar, so it composes
with everything:
```sql
-- diff two versions
SELECT count(*) FROM h5i('trades', 2) b
JOIN h5i('trades', 1) a ON a.ts = b.ts;
```
Any string second argument that does not parse as RFC3339 is treated as a
snapshot name, so avoid naming snapshots like timestamps.
## Table functions
### `asof_join`
```sql
asof_join('left', 'right', 'left_on', 'right_on'
[, 'by_cols' [, 'backward'|'forward' [, tolerance]]])
```
For each left row, find the most recent right row at or before it
(`'backward'`, the default) or the first at or after it (`'forward'`),
optionally matching equality keys. This is the canonical trades-vs-quotes join:
```sql
SELECT * FROM asof_join('trades', 'quotes', 'ts', 'ts', 'symbol');
SELECT * FROM asof_join('trades', 'quotes', 'ts', 'ts', 'symbol', 'backward', 5000000);
```
- `by_cols` is comma-separated; each entry is `'col'` (same name both sides)
or `'lcol=rcol'`.
- `tolerance` is an integer in raw time units: the maximum allowed
`|left.ts − right.ts|`.
- The join is always **LEFT and 1:1 with the left side**: unmatched left rows
keep NULLs. A useful invariant to assert: `len(output) == len(left)`.
- Right-side columns that collide with left names get a `_right` suffix.
- The right side is buffered in memory (charged to the query memory budget);
the left side streams. Left-only filters and `LIMIT` push down into the
left scan.
- Both tables are read at **latest**; to ASOF-join historical versions, use
the keyword form over session-bound names, or materialize first.
The keyword syntax is also supported (bare table names only, no aliases):
```sql
SELECT * FROM trades ASOF JOIN quotes
MATCH_CONDITION (trades.ts >= quotes.ts) -- >= backward, <= forward
ON trades.symbol = quotes.symbol;
```
### `gapfill` / `resample`
```sql
gapfill('table', 'time_column', step [, 'null'|'locf'|'interpolate'])
```
Turn an irregular series into a regular grid from the first to the last
observed timestamp, stepping by `step` raw time units. `resample(...)` is an
exact alias.
```sql
SELECT ts, price FROM gapfill('bars_1m', 'ts', 60000000, 'locf') ORDER BY ts;
```
Fill modes for synthesized instants:
| Mode | Behavior |
|---|---|
| `'null'` (default) | Non-time columns are NULL |
| `'locf'` | Last observation carried forward (NULL before the first) |
| `'interpolate'` | Linear interpolation for numeric columns (ints rounded); non-numeric falls back to previous value |
!!! warning "gapfill is per-table, not per-key"
There is **no per-key grouping**: on a multi-symbol table, `locf` carries
whichever symbol last ticked. Gapfill single-instrument tables, or filter
to one key first. Also note: observations that don't land exactly on the
grid are dropped from the output; duplicate timestamps collapse to the
last row; at most 1,000,000 rows are generated (`limit_exceeded` beyond).
### `forks`
```sql
forks('table' [, 'fork-a,fork-b'])
```
Read a table from **every fork at once**, each row labelled with a `__fork`
column (`''` for the base). This is the cross-fork aggregation step of a
branch-per-hypothesis sweep: N agents write N forks, one query compares
them.
```sql
SELECT __fork, count(*), avg(price) FROM forks('trades') GROUP BY __fork;
SELECT __fork, vwap(price, size) FROM forks('trades', 'exp-a,exp-b') GROUP BY __fork;
```
- Segments shared between forks are opened **once** no matter how many forks
reference them; you pay for distinct data, not for fork count.
- All included forks must agree on the table's schema; a mismatch errors and
names the fork rather than unioning loosely.
- The second argument narrows to a comma-separated fork list; omit it for
base plus every fork. See [Forks](concepts.html#forks) for the model.
### `tail`
```sql
tail('table' [, after_version [, poll_ms]])
```
Stream rows appended after a version: a message-log view of an append-only
table. With no version it starts after the current head (future appends
only). `poll_ms` defaults to 250 (minimum 10).
```sql
SELECT ts, price FROM tail('trades', 812) LIMIT 500;
```
- The result is **unbounded**, so always apply `LIMIT` (or cancel the query).
`tail` blocks until `LIMIT` rows arrive; pass a query timeout as a backstop.
- Requires a **pure-append version chain** after `after_version`; any
delete/replace/restore/write in the range errors with a hint.
- Size `LIMIT` from `versions` row deltas to fetch "exactly what's new since
version N", with no timestamp-cursor guesswork.
### `latest_on`
```sql
latest_on('table', 'by_column')
```
One row per group: the most recent row per symbol, per instrument, per
whatever `by_column` names. Both arguments are string literals, and the group
column must be string-like.
```sql
SELECT ts, symbol, price FROM latest_on('trades', 'symbol');
```
It precomputes each immutable segment's last row per group and caches that as
a checksummed sidecar, so an append-only table reuses every prior segment's
contribution and scans only what is new — segments × groups rather than rows.
The cache is a pure accelerator: a miss, a corrupt entry or a version
mismatch rebuilds from the segment and never changes the answer. Sidecar
writing follows the session's
[`--predicate-cache`](cli.html#h5i-db-query) setting, so a default query reads
caches without writing any.
## Scalar, aggregate & window functions
### `time_bucket`
```sql
time_bucket(interval, ts)
time_bucket(interval, ts, origin_or_timezone)
time_bucket(interval, ts, origin, timezone)
```
Floor timestamps into fixed buckets, following DuckDB/TimescaleDB semantics. The
interval is a literal: an SQL `INTERVAL` or a string like `'30s'`, `'5m'`,
`'1.5h'`, `'1d'`, `'1w'`, `'1mo'`, `'1y'`. Fixed widths align to the origin
`2000-01-03T00:00:00Z` (a Monday, so weeks start Monday); month/year widths
use calendar bucketing.
```sql
SELECT time_bucket('5m', ts) AS bar, … GROUP BY bar;
SELECT time_bucket('1d', ts, 'America/New_York') AS session_day, … -- local-time days
```
The third argument is a timezone when it parses as an IANA name (or contains
`/`), otherwise an origin timestamp; use the 4-argument form to pass both.
With a timezone, bucketing happens in local wall time and handles DST
(ambiguous → earliest, gap → first valid instant). Out-of-range inputs yield
NULL buckets rather than errors.
### `vwap` / `wavg`
```sql
vwap(price, size) -- value first, weight second
wavg(size, price) -- kdb argument order: weight first
```
Weighted mean as a streaming, mergeable aggregate; the two spellings are the
same computation with different argument order. Returns `Float64`; NULL when
the group is empty or the weight sum is zero; rows with a NULL in either
argument are skipped.
Supports retraction, so sliding-window use is O(n):
```sql
SELECT vwap(price, size) OVER (ORDER BY ts ROWS BETWEEN 99 PRECEDING AND CURRENT ROW)
FROM trades;
```
### `ewma`
```sql
ewma(value, alpha) OVER (PARTITION BY … ORDER BY ts)
```
Exponentially weighted moving average, one ordered pass per partition:
`y₀ = x₀; yᵢ = α·xᵢ + (1−α)·yᵢ₋₁`. `alpha` must be a constant in `[0, 1]`.
NULL inputs carry the previous smoothed value forward. Matches
`pandas.ewm(alpha=…, adjust=False)`.
```sql
SELECT ewma(price, 0.06) OVER (PARTITION BY symbol ORDER BY ts) AS px_smooth
FROM trades;
```
### `rolling_avg` / `rolling_sum` / `rolling_min` / `rolling_max`
```sql
rolling_avg(value, order_by, rows)
rolling_avg(value, order_by, rows, partition_by)
```
Convenience sugar, expanded before parsing into the standard window frame
`AVG(value) OVER (PARTITION BY partition_by ORDER BY order_by ROWS BETWEEN
rows−1 PRECEDING AND CURRENT ROW)`. `rows` must be an integer literal in
1…1,000,000. The `PARTITION BY` is emitted only when the fourth argument is
given.
!!! warning "The three-argument form is not partitioned"
Without a fourth argument the window is a trailing n-row window in
**global** order, so on a multi-symbol table it averages across symbols
and returns a plausible, wrong number. Pass the partition column, use the
sugar on single-key subsets, or write the window out in full. Either form
still cannot take its own `OVER` clause.
### Rolling window functions
Eight window functions DataFusion does not have. Unlike the `rolling_avg`
sugar above these are real window functions, so they take their own `OVER`
clause and any frame you like:
| Function | Over the frame |
|---|---|
| `mad(x)` | Mean absolute deviation |
| `skew(x)` | Sample skewness |
| `kurt(x)` | Excess kurtosis |
| `ts_rank(x)` | The current row's rank within the frame, scaled to `(0, 1]` |
| `idxmax(x)` / `idxmin(x)` | 1-based position of the frame's maximum / minimum |
| `ts_corr(x, y)` / `ts_cov(x, y)` | Correlation and covariance of two columns |
```sql
SELECT ts, symbol,
mad(price) OVER w AS mad_5,
ts_rank(price) OVER w AS rank_5,
ts_corr(price, size) OVER w AS corr_5
FROM trades
WINDOW w AS (PARTITION BY symbol ORDER BY ts ROWS 4 PRECEDING);
```
The rest of the rolling family (`rolling_mean`, `rolling_std`,
`rolling_var`, `rolling_sum`, `rolling_min`, `rolling_max`, `rolling_count`)
is reachable through stock DataFusion aggregates in an `OVER` clause; the
[DataFrame builder](../api/dataframe.html#rolling-and-cross-sectional-operators)
names them uniformly and compiles to exactly that.
### Cross-sectional window functions
Two operators for the "against everything else at this instant" shape.
Partition by the timestamp, not by the asset:
| Function | Result |
|---|---|
| `cs_rank(x)` | Rank within the partition, scaled to `(0, 1]` |
| `cs_winsorize(x, lower_pct, upper_pct)` | `x` clipped to the partition's percentile bounds |
```sql
SELECT ts, symbol,
cs_rank(signal) OVER (PARTITION BY ts) AS rank,
cs_winsorize(signal, 0.05, 0.95) OVER (PARTITION BY ts) AS clipped
FROM signals;
```
`cs_demean` and `cs_zscore` are plain SQL over the same partition
(`x - avg(x) OVER (PARTITION BY ts)`), and the DataFrame builder spells all
four the same way.
### `first_value` / `last_value`
Stock DataFusion, but the idiom is easy to miss: `last_value(x ORDER BY ts)`
inside a `GROUP BY` is how you take "closing" values without a self-join. See
the OHLCV query at the top of this page.
## Sessions, pruning & performance
- Narrow time-range predicates prune segments via manifest statistics before
any I/O; verify with `h5i-db query … --stats` or the UI's SQL scratchpad.
- Select only the columns you need; Parquet projection pushdown is
column-granular.
- Memory budgets (`--memory-limit-mb` / `sql(memory_limit=…)`) enable disk
spilling instead of OOM; `--max-rows` and timeouts turn runaway queries
into clean, typed errors.
- `information_schema` is available for introspection
(`SELECT * FROM information_schema.tables`).
For a guided tour with real data, see the cookbook:
[A SQL tour for quants](../cookbook/00_fundamentals/04_sql_tour_for_quants.html)
and [Performance tuning](../cookbook/03_risk_and_production/10_performance_tuning.html).
---
# Agents & automation (https://db.h5i.dev/manual/agents/)
h5i-db needs no MCP server or custom protocol to be driven by AI agents,
schedulers, or CI: agents use the same CLI and Python API as everyone else.
What makes that safe is a deliberate machine contract: structured output,
structured errors, hard resource limits, and a write path that policy can
gate behind human review.
```console
$ h5i-db query market.db "SELECT symbol, vwap(price, size) FROM trades GROUP BY symbol" \
--format json --max-rows 1000 --timeout 30s # machine formats + hard limits
$ h5i-db delete-range market.db trades --start 09:30… --end 09:31… --plan
{"plan_id": "5c41…", "summary": {"rows_affected": 12481, "segments_reused": 127}}
$ h5i-db policy set market.db direct_delete=false # agents must preview; humans can too
```
## Machine-readable everything
- **Output**: `--format json | jsonl | csv | arrow` on every command
([formats](cli.html#output-formats)). `jsonl` is the natural choice for
streaming consumers.
- **Errors**: a single JSON envelope on stderr,
`{"code", "message", "retryable", "hint"}`. `code` is a stable identifier
(`version_conflict`, `table_not_found`, `limit_exceeded`, …), `retryable`
says whether backing off and retrying can help, and `hint` names the next
thing to try.
- **Exit codes** are stable and branchable: `0` ok, `2` user error,
`3` conflict, `4` limit exceeded, `5` internal
([details](cli.html#errors-and-exit-codes)).
In Python the same envelope arrives as a
[typed exception hierarchy](../api/exceptions.html): every `H5iError` carries
`.code`, `.hint`, and `.retryable`.
```python
try:
db.append("trades", batch)
except h5i_db.ConflictError:
... # retryable: another writer won; re-read and retry
except h5i_db.InvalidInputError as e:
print(e.hint) # not retryable: fix the call
```
## Resource limits as flags
A supervisor can hard-cap any call without touching the database:
| CLI | Python | Effect |
|---|---|---|
| `--max-rows N` | `sql(max_rows=N)` | Stop as soon as the result exceeds N rows, with a clean `limit_exceeded` error instead of silent truncation |
| `--timeout 30s` | `sql(timeout=30)` | Deadline; cancels execution on expiry |
| `--memory-limit-mb N` | `sql(memory_limit=N)` | Memory budget with disk spilling under pressure |
| `--max-bytes N` | — | Cap output bytes at batch boundaries |
| (open read-only) | `Database(path, read_only=True)` | Reject every write at the handle level |
### The agent output profile
Passing a limit on every call only works if the caller remembers, and one
forgotten flag can end a session. `H5I_DB_PROFILE=agent` moves the budget into
the environment instead:
```console
$ export H5I_DB_PROFILE=agent
$ h5i-db query market.db "SELECT * FROM trades" --format jsonl
{"ts":"2026-07-01T09:30:00Z","symbol":"AAPL","price":210.5}
… 1000 rows …
```
stdout stops at 1000 rows or 1 MiB, and a JSON summary on stderr reports what
was withheld:
```json
{"profile":"agent","truncated":true,"total_rows":2841193,"returned_rows":1000,
"full_result_path":"/tmp/h5i-db-results/result-….parquet","full_result_rows":2841193}
```
Nothing is lost, only withheld: the rows that did not fit are in that Parquet
file. `H5I_DB_RESULT_DIR` moves where those land. Two properties hold in this
mode. Output content never depends on whether stdout is a terminal, so a piped
run produces the same bytes as an interactive one. And an explicit
`--max-bytes` you pass yourself stays a hard `limit_exceeded` error rather
than a soft truncation.
## Policy-gated review
The [mutation policy](concepts.html#the-mutation-policy) forces chosen
operations through the previewable plan/apply flow:
```console
$ h5i-db policy set market.db direct_delete=false direct_write=false direct_replace=false
```
An agent that then tries a direct delete gets a `policy_violation` error whose
hint points at `--plan`. The staged plan carries exact affected-row counts and
before/after samples; a human reviews it in the
[UI](cli.html#h5i-db-ui) (`h5i-db ui market.db`) or via `plan show`, and
applies or discards it. Every committed manifest records its
`execution_mode` and plan hash, so the audit trail distinguishes reviewed
from direct writes forever.
Where the *mutation* policy gates who may write directly, a per-table
[data policy](cli.html#h5i-db-data-policy) gates *what data may be written*:
typed constraints (`not_null`, `compare`, `in_set`, composed with
`and`/`or`/`not`) checked fail-closed on every write and at plan time. A
violating batch is refused with `data_policy_violation` before it can land, so
an agent can't quietly commit malformed rows.
## Patterns that work
- **Branch per hypothesis:** give each agent its own
[fork](concepts.html#forks) instead of a table-name convention:
`fork create <db> sweep --count 100` opens a hundred isolated, writable
workspaces in one catalog pass, `--fork <name>` scopes every subsequent
command to one of them, and `--meta` attaches the run's parameters where
`fork show` and the UI's fork monitor surface them. Compare results across
the whole sweep with [`forks('table')`](sql.html#forks), promote the
winner, and `fork drop` the rest in one batch. A human can watch the
sweep live in `h5i-db ui` (the Forks tab) while it runs.
- **Idempotent retries:** appends racing another writer raise
`version_conflict` (exit 3 / `ConflictError`, `retryable: true`). The CLI
retries pure appends itself (`ingest --retries`, default 5); in Python,
`append()` retries internally as well. For read-modify-write flows, pass
`--expected-version` and re-derive on conflict rather than blindly retrying.
- **Pin what you read:** have agents record the version they computed from
(`versions`, or read via `h5i('t', v)`), so every downstream artifact is
attributable to an exact input state. The cookbook works this through in
[reproducible backtests](../cookbook/03_risk_and_production/02_reproducible_backtests.html)
and the [paper-trading loop](../cookbook/03_risk_and_production/05_live_paper_trading_loop.html).
- **Bound the pull, not the loop:** a cutoff applied when the data leaves the
database survives the trip into pandas, where nothing else can enforce it.
[`query --decision-time <ts>`](cli.html#point-in-time-reads) hides rows
stamped later and `--as-of` pins which commits exist; set
`H5I_DB_DECISION_TIME` to bound a whole session.
- **Measure what the data did afterwards.** No cutoff can prevent a vendor
restating history, so measure it instead:
[`arrival-delta … --as-of <decision-time>`](cli.html#h5i-db-arrival-delta)
re-runs a query at both read points and reports how much the answer moved.
Read `vacuous` before the number: on a database with no arrival history the
zero is arithmetic rather than evidence.
- **Orient in one call:** [`context`](cli.html#h5i-db-context) answers what
`tables`, `schema`, `sample` and `versions` answer, in one deterministic
document that also names any staged plan and the operations policy gates.
- **Notes are for provenance:** `--note` / `note=` lands in the version
manifest; make agents write *why* ("re-mark after vendor restatement,
ticket DX-142"), and `versions` becomes your change log.
- **Incremental consumers use `tail`:** strictly ordered commits mean
"give me exactly the rows since version N" is
[`tail('t', N)`](sql.html#tail), with no timestamp cursors.
---
# Quant workflows (https://db.h5i.dev/manual/quant/)
`h5i_db.quant` runs the standard quant research loop against the engine:
factor evaluation in the shape of `alphalens`, performance statistics in the
shape of `pyfolio` (arithmetic from `empyrical`), and reports that record
which version of the data produced them.
Two things make it different from the pandas libraries it mirrors. Every
computation runs against a **pinned read**, so a number can be reproduced
later or refused as unreproducible. And every panel-scale computation
compiles to SQL, so the data stays in the engine and only the aggregate
comes back.
```python
from h5i_db import quant
panel = quant.build_panel(
db, "signals", "prices",
periods=(1, 5, 10), # forward-return horizons, in bars
quantiles=5,
snapshot="2024-q1", # the pin
)
panel.ic().to_pandas() # per-date rank IC, one column per horizon
panel.quantile_returns() # mean forward return per bucket
quant.factor_report(panel, path="factor.html")
```
## Inputs
Both sources are long format and may be a table name or a `LazyFrame`:
| Source | Columns |
|---|---|
| factor | `ts`, `asset`, `factor`, optionally a group column |
| prices | `ts`, `asset`, `price` |
Column names are arguments (`ts=`, `asset=`, `factor_column=`,
`price_column=`); pass a `LazyFrame` when the shape needs more than renaming.
## Pinning and the embargo
`version=`, `as_of=` and `snapshot=` are the storage read point, and
`event_time_cutoff=` is the decision-time embargo: every source read is
restricted to `ts <= cutoff`, so a forward return that would need a price
from after the cutoff is dropped rather than computed. The result is
identical to never having had the later data, which is asserted in the test
suite.
**Versions are per table.** `version=2` means "version 2 of every source",
which is only meaningful when the sources really do share a lineage. To pin
several tables to one instant use `snapshot=` or `as_of=`; to pin them
independently pass a mapping:
```python
quant.build_panel(db, "signals", "prices",
version={"signals": 2, "prices": 1})
```
## Reproducibility
`deterministic=True` (the default) runs every query single-partition.
Floating-point addition is not associative, so a parallel plan may combine
partial aggregates in a different order on each run and move a result by a
few units in the last place. That is invisible in a chart and fatal to a
number you intend to cite, so the default trades intra-query parallelism for
bit-stability. Parallelism across runs (a fork sweep) is unaffected.
`quant.verify(subject, rerun=...)` re-executes a computation and checks the
provenance digest still matches. An unpinned computation is *refused* rather
than passed: reproducing it is not something its header can promise.
## Divergences from the reference implementations
Every difference from `alphalens` / `empyrical` is listed here and pinned by
a test. A divergence that is not in this table is a bug.
| # | Where | h5i-db | Reference | Why |
|---|---|---|---|---|
| D1 | Quantile assignment | `ntile(q)`, equal count, remainder to the earliest buckets | `pd.qcut`, quantile value edges | Identical whenever the cross-section divides evenly by `q`. When it does not, both give equal-count buckets and differ only in *which* bucket absorbs the remainder. Bucket sizes never differ by more than one, and quantiles stay monotone in the factor: both asserted. |
| D2 | Forward-return labels | `fwd_1`, `fwd_5` — bar counts | `'1D'`, `'5D'` — pandas frequency strings | Horizons are bar counts here, so no trading calendar has to be inferred to name a column. A 5-bar horizon is 5 bars whether the bars are days or minutes. |
| D3 | Calendar inference | none; horizons are positional | infers a `CustomBusinessDay` calendar from the observed index | Calendar inference is what makes alphalens fragile on irregular data. Where a real calendar is needed, resample first. |
| D4 | `alpha_beta` annualization | `annualization / period`, with `annualization` an argument (252 by default) | `pd.Timedelta('252Days') / pd.Timedelta(period)` | Follows from D2. Pass `annualization=` for non-daily bars. |
| D5 | Percentiles (`tail_ratio`) | exact rank interpolation | `np.percentile`, linear | Matches numpy exactly. DataFusion's own `percentile_cont` is approximate and disagrees around the eighth significant digit, which is enough to break parity. |
| D6 | Rolling statistics | null until the window is full | pandas `min_periods=window` | Same behaviour; noted because a SQL frame's default is the opposite, and an unguarded frame reports a "63-bar Sharpe" from two observations. |
| D7 | Omega with no winning bars | `0.0` numerator | `sum([]) == 0` | Same value; reached by coalescing a SQL `NULL` rather than by summing an empty list. |
| D8 | Event-study family | not implemented | `average_cumulative_return_by_quantile` etc. | Needs a windowed range join around each signal date, which is a tracked engine gap. The cookbook covers the manual pattern meanwhile. |
## Performance statistics
```python
series = quant.returns(db, "strategy_returns", annualization=quant.DAILY)
series.stats() # the headline set, as one SQL row
series.drawdown_table(top=10) # worst non-overlapping episodes
series.rolling_sharpe(63)
quant.tearsheet(series, path="tearsheet.html")
```
`stats()` returns annual return and volatility, cumulative return, Sharpe,
Sortino, downside risk, max drawdown, Calmar, Omega, stability, tail ratio,
skew, kurtosis and daily VaR, plus alpha and beta when a `benchmark=` series
is passed. Values match `empyrical` to 1e-9; skew and kurtosis follow
`scipy` (biased), which is what pyfolio reports.
`annualization` is bars per year: `quant.DAILY` (252), `quant.WEEKLY`,
`quant.MONTHLY`, `quant.YEARLY`, or any number — `24 * 365` for hourly
crypto bars.
Both constructors take the same pins as `build_panel`. `quant.returns` reads
a returns series (one row per bar, simple decimal returns);
`quant.from_levels` reads an equity *level* series and differences it, which
is what a backtest's `bt_equity` table is. Alongside `stats()` the series
carries `equity_curve()`, `underwater()`, `drawdown_table(top=…)`,
`rolling_sharpe(w)`, `rolling_volatility(w)` and `rolling_beta(w, benchmark)`.
## Selection bias gets first-class statistics
A number found by searching is worth less than the same number found once, so
these are not optional footnotes.
```python
rets = db.sql("SELECT ret FROM strategy_returns ORDER BY ts").to_arrow()["ret"].to_pylist()
quant.deflated_sharpe(rets, trials=40) # .sharpe .benchmark .probability
quant.minimum_track_record_length(rets) # observations still needed
trials = [[r * (1 + i / 10) for r in rets] for i in range(8)]
quant.probability_of_backtest_overfitting(list(zip(*trials))) # (observations, trials)
```
`deflated_sharpe` discounts a Sharpe by the size of the search that produced
it, and reports `probability`: the chance the true Sharpe beats the
benchmark. Below 0.95 the result is indistinguishable from the best of that
many coin flips. When the variance of the trials' Sharpes is unknown it
substitutes the returns' own sampling variance, which is conservative rather
than an assumption of zero; pass `trial_sharpe_variance=` when you have it.
`trials_source` records whether the trial count was declared or measured, so
a report cannot quietly pass off a guess as a count.
`minimum_track_record_length` returns `inf` when the observed Sharpe sits
below the deflated benchmark. That is not a bug: it means no amount of
further data makes *this* result significant, and the honest report is that
the search found nothing.
`probability_of_backtest_overfitting` takes a matrix of shape
`(observations, trials)` — one column per trial's returns over the same
period — and splits it into `partitions` (default 8). A PBO near 0.5 means
the in-sample winner carried no information.
Read the moments first. A hold-to-resolution book has an equity curve that is
flat and then jumps, so its returns are one outlier surrounded by noise, and
Sharpe assumes something much closer to normal. High skew and kurtosis mean
the Sharpe is the wrong summary, not that the strategy is bad.
## Purged cross-validation
```python
n = 90
list(quant.purged_kfold(n, folds=5, horizons=[10] * n, embargo=0.01))
list(quant.combinatorial_purged(n, groups=6, test_groups=2, horizons=[10] * n))
list(quant.walk_forward(n, train_size=40, test_size=10, horizons=[10] * n))
```
Each yields `Split(train, test, purged)` index arrays. `horizons[i]` is how
many observations forward observation *i*'s label depends on, so a label
needing the next ten bars cannot leak into its own training fold. Omitting
`horizons` says labels are instantaneous, which is rarely true and is never
assumed silently; `embargo` is an additional gap as a fraction of `n`.
`walk_forward` takes `step=` (defaults to `test_size`) and `expanding=True`
for a growing rather than rolling training window.
## Cost calibration
```python
samples = [
quant.SlippageSample(direction=1, fill_price=100.0 + 0.02 * q,
reference_price=100.0, quantity=q, reference_size=500.0)
for q in (10.0, 25.0, 50.0, 80.0, 120.0, 200.0)
]
fit = quant.fit_impact(samples, shape="sqrt") # or "linear"
fit.predict(0.1), fit.is_usable, fit.r_squared
```
Calibrates a slippage model from realised fills instead of assuming a
constant. A backtest run writes `calibration_samples` ready for this;
`fit.is_usable` is the guard against fitting a curve to four fills.
## Scoring a forecast against the market's own
Event contracts quote a probability, so a strategy trading them is making a
competing forecast. This is the one comparison an equity curve cannot make,
and sizing cannot inflate it.
```python
strategy = [0.7, 0.4, 0.6, 0.2, 0.9]
market = [0.6, 0.5, 0.5, 0.3, 0.8]
outcomes = [1.0, 0.0, 1.0, 0.0, 1.0]
adv = quant.brier_advantage(strategy, market, outcomes)
adv.advantage # market_brier - strategy_brier; positive means you were closer
adv.skill_score # as a fraction of the market's own Brier
adv.cumulative # the path, for plotting
quant.brier_decomposition(strategy, outcomes) # reliability - resolution + uncertainty
quant.reliability_curve(strategy, outcomes) # bucketed, with the signed gap
```
The decomposition says *why* a score is what it is: sitting off the diagonal
(reliability) is a different failure from not separating outcomes at all
(resolution). Reliability is small in absolute terms — bucket gaps of five to
nine points square down to thousandths — so the *sign structure* of the
reliability curve is the tradeable finding, not the magnitude of that term.
## Comparing many runs at once
A sweep leaves one fork per trial, and opening twenty tearsheets is not
comparing them. `basket_report` assembles one document from stored tables
only, with no re-simulation:
```
quant.basket_report(db, {"th50": result_a, "th60": result_b},
path="basket.html",
panels=quant.PORTFOLIO_PANELS + ("equity", "price"),
snapshot="panel-v1")
```
(`result_a` and `result_b` are [backtest results](backtest.html#opening-a-run-again);
the [backtesting page](backtest.html#comparing-many-runs-at-once) has the
worked version.)
`quant.PORTFOLIO_PANELS` (`total_equity`, `total_drawdown`,
`total_rolling_sharpe`, `total_cash_equity`, `periodic_pnl`, `leaderboard`)
are safe at any basket size. Per-run panels draw one series each and are
dropped past `per_run_limit` with the reason recorded in `report.skipped`,
because silently thinning lines misrepresents the basket. `quant.PANELS` is
every panel name. The `price` panel puts fill markers on the book the fills
actually met, read at the same pin the runs used, and `brier_advantage` is
the one panel needing an input the report cannot derive — your strategy's own
probability — so it is passed in and skipped with a reason when absent.
`quant.basket_payload(...)` returns the same content as a `BasketReport`
object without rendering HTML.
## Restatement impact
```python
quant.restatement_impact(
lambda pin: quant.build_panel(db, "signals", "prices", periods=(1,), **pin),
db,
before={"version": {"signals": 1, "prices": 1}},
after={"version": {"signals": 2, "prices": 1}},
metric=lambda panel: {"mean_ic": panel.mean_ic()},
)
```
Runs one computation at two read points and reports what a vendor's revision
moved — a question only a versioned store can answer. `build(pin)` receives
the pin kwargs (`version`, `as_of`, `snapshot`) and produces the computation;
`metric(built)` reduces it to a mapping of scalars, and the result carries
`before`, `after`, `delta` and `changed` per key, with `tolerance` (default
`1e-9`) deciding what counts as moved. Omitting `after` compares against the
unpinned read.
## Sweeps
A sweep runs a parameter grid with one fork per combination. Trials cannot
contaminate each other or the base data, and they compare in one query
because forks share their base's segments.
```python
def trial(fork_db, params):
panel = quant.build_panel(fork_db, "signals", "prices",
quantiles=params["quantiles"], periods=(1,))
row = panel.ic_decay().to_arrow().to_pylist()[0]
return {"mean_ic": row["mean_ic"], "icir": row["icir"]}
result = quant.sweep(db, {"quantiles": [3, 5, 10]}, trial)
result.compare().to_pandas() # every trial, one cross-fork query
result.best("icir")
result.drop() # forks and their results go together
```
## Reports
`quant.factor_report(panel)`, `quant.tearsheet(series)` and
`quant.backtest_report(result)` render one self-contained HTML file: inline
CSS and JS, data embedded as JSON, no network access at view time. Section
one is always the provenance header, so a reader sees the data version before
the numbers. `quant.report_payload()` (and the per-kind
`quant.backtest_payload` / `quant.basket_payload`) returns the same content
as a dict, which is what agents and scripts should read instead of scraping
HTML.
## From the shell
```bash
python -m h5i_db.quant factor --db market.db --factor signals --prices prices \
--snapshot 2024-q1 --format html --out factor.html
python -m h5i_db.quant tearsheet --db market.db --returns strategy_returns --out run.html
python -m h5i_db.quant stats --db market.db --returns strategy_returns --format json
python -m h5i_db.quant verify --db market.db --factor signals --prices prices
```
Every verb takes the same pin flags (`--version`, `--as-of`, `--snapshot`,
`--event-time-cutoff`) and `--format json|html|table`. `stats` prints the
headline set without rendering a document, and `verify` re-runs a factor
panel and checks it still produces its numbers.
## Provenance objects
Every computation carries a `Provenance` (`digest`, `warnings`) and the `Pin`
it ran under (`version`, `as_of`, `snapshot`, `event_time_cutoff`, and
`is_pinned`). `quant.verify(subject, rerun=…)` checks both halves: the
provenance digest must be unchanged *and* the recomputed values must match,
because a digest over the SQL cannot notice an engine that computes the same
query differently. An unpinned subject raises `VerificationError` (or, with
`strict=False`, is reported unverifiable) rather than passing: two runs
against "latest" agreeing proves only that nothing changed in the seconds
between them.
The objects these return are part of the API, not implementation detail:
| Type | Returned by | Carries |
|---|---|---|
| `FactorPanel` | `build_panel` | `ic`, `ic_decay`, `mean_ic`, `quantile_returns`, `spread`, `turnover`, `rank_autocorrelation`, `alpha_beta`, `weights`, `loss_report` |
| `ReturnSeries` | `returns`, `from_levels` | `stats`, `equity_curve`, `underwater`, `drawdown_table`, `rolling_sharpe`, `rolling_volatility`, `rolling_beta` |
| `SweepResult` | `sweep` | `compare`, `best`, `to_pandas`, `drop` |
| `BasketReport` | `basket_payload` | `drawn`, `skipped`, `to_dict`, `to_html` |
| `DeflatedSharpe` | `deflated_sharpe` | `sharpe`, `benchmark`, `probability`, `trials`, `skew`, `kurtosis`, `is_significant` |
| `PBOResult` | `probability_of_backtest_overfitting` | `pbo`, `ranks`, `splits`, `strategies`, `is_overfit` |
| `CostFit` | `fit_impact` | `intercept`, `coefficient`, `shape`, `r_squared`, `observations`, `predict`, `is_usable` |
| `BrierAdvantage` | `brier_advantage` | `strategy_brier`, `market_brier`, `advantage`, `cumulative`, `win_rate`, `skill_score` |
Both `FactorPanel` and `ReturnSeries` expose `sql()`, so the SQL a number was
computed from is inspectable rather than implied.
`build_panel` raises `MaxLossExceededError` when more of the input is dropped
than `max_loss` allows (default 0.35), mirroring alphalens, so a panel built
from a third of the data cannot be mistaken for a panel built from all of it.
`panel.loss_report()` gives the same accounting alphalens prints: what was
lost joining to forward returns, and what was lost assigning quantiles.
---
# Operations guide (https://db.h5i.dev/manual/operations/)
How to run an h5i-db database in production: backup and restore, vacuum and
compaction cadence, plan hygiene, disk-usage math, filesystem caveats, and
the torn-HEAD recovery runbook.
Everything below refers to the on-disk layout (see `crates/h5i-db-core/src/layout.rs`):
```text
<root>/
FORMAT # format + minimum reader version
catalog/tables/<hash-of-name>.json # table name -> table UUID
snapshots/<hash-of-name>.json # snapshot name -> {table uuid: version}
tables/<table-uuid>/
HEAD # the ONLY mutable object per table
spec/<revision>.json # schema revisions
manifests/<seq>.json # one immutable manifest per version
segments/<segment-uuid>.parquet # immutable data
```
Two properties make operations simple:
- **Everything except `HEAD` is immutable.** Segments, manifests, specs,
catalog entries, and snapshots are write-once. Only `HEAD` (one small JSON
file per table) ever changes, and it changes by atomic rename.
- **History is a hash chain.** Each manifest records the blake3 checksum of
its parent's bytes, and `HEAD` records the checksum of the manifest it
points at, so any prefix of history is self-verifying
(`h5i-db verify <db> <table> [--deep]`).
---
## Backup
Immutable objects mean a plain file copy is a correct backup **if you copy in
the right order and don't run destructive maintenance concurrently**.
### Procedure
1. **Don't run `vacuum --apply` (or `drop-table`) during the backup window.**
Vacuum is the only thing that deletes objects; a copy that races it can
miss files it already indexed. Plain writers are safe to leave running.
2. Copy in this order (older references first; `HEAD` before the objects it
references is the one order that is *wrong*, so copy `HEAD` **first**, per
table, then its immutable objects):
```bash
DB=/data/market.db BK=/backup/market.db-$(date +%F)
mkdir -p "$BK"
cp "$DB/FORMAT" "$BK/"
cp -r "$DB/catalog" "$DB/snapshots" "$DB/forks" "$BK/" 2>/dev/null || true
for t in "$DB"/tables/*/; do
b="$BK/tables/$(basename "$t")"; mkdir -p "$b"
cp "$t/HEAD" "$b/" # 1. pin the version to back up
cp -r "$t/spec" "$t/manifests" "$b/" # 2. immutable metadata
cp -r "$t/segments" "$b/" # 3. immutable data
done
```
Why this order works: the copied `HEAD` names some sequence *S*. Every
manifest `0..=S` and every segment they reference already existed when
`HEAD` was copied, and (with vacuum paused) nothing deletes them, so all
of them are present in the later copy steps. Commits that land *during*
the backup produce manifests `> S`; they may be half-copied, which is
harmless: they are unreachable from the copied `HEAD` and are exactly what
`vacuum` classifies as debris.
3. Skip transient files if you meet them: `HEAD.lock`, `HEAD.tmp.*`.
4. Validate the backup before trusting it:
```bash
h5i-db tables "$BK"
h5i-db verify "$BK" <table> --deep # re-reads every segment checksum
```
Filesystem/LVM/ZFS snapshots are also fine (crash-consistent is enough: the
commit protocol fsyncs data before `HEAD` moves, so any point-in-time image
is a valid database).
### Restore
A backup **is** a database. Point the CLI at it, or copy it back into place
and run `h5i-db verify` per table. There is no replay/WAL step.
Note that restoring an old backup rewinds *all* tables to the backup time;
to rewind a single table inside a live database, prefer
`h5i-db restore <db> <table> <version>`, which is what versioning is for.
---
## Vacuum
`h5i-db vacuum <db> [table] [--grace-seconds N] [--apply]` removes
unreachable objects: segments referenced by no committed manifest and no
live mutation plan, manifests above `HEAD` (crashed-writer leftovers), and
`*.lock` / `HEAD.tmp.*` debris. Without `--apply` it is a dry run.
Vacuum runs from the base database and treats every fork as a root: it
consults every fork's pins and every fork's catalog, so a fork's tables and
the base segments its shadows reference are never candidates. It refuses to
run through a `--fork` handle, because reachability is a database-wide
question.
Guidance:
- **Always review a dry run first** in scripted maintenance
(`vacuum` then `vacuum --apply` on the same candidate list you inspected).
- **Grace period** (default 3600 s): objects younger than this are never
touched. Set it comfortably above your *longest* ingest or plan-prepare
duration: staged segments exist on disk before the commit that references
them, and a grace period shorter than a slow bulk load can delete a
commit-in-progress out from under it.
- **Cadence**: daily or weekly is plenty. Debris accrues only from crashed
or conflicted writers and discarded/expired plans; a healthy append-only
workload generates almost none.
- **Never run two `vacuum --apply` concurrently**, and don't run it during
backups (above).
## Forks
A fork is a named, writable workspace over a pinned view of the database. It
costs one small JSON object and copies no data: its tables are ordinary
tables, and the versions it forked from are shared by reference. Three things
follow that an operator needs to know.
**A fork is a GC root.** Its pin holds the base's retention floor down, so
`set-retention` refuses to expire a version a fork pins, and `drop-table`
refuses on a table a fork pins. Both name the fork in the error. The fix is
always to promote or drop that fork, never to force the floor.
**Forks nest.** `fork create <db> <child> --fork <parent>` creates a fork
inside a fork, and a child pins its parent's tables the same way a top-level
fork pins the database's. Dropping a parent that a child still pins is refused
and names the children; `fork drop` one level at a time, or drop the whole
subtree. Depth is capped at 32.
**`FORK_INDEX.json` is a cache.** It sits at the database root and answers
"which forks pin what" in one read instead of one per fork. It is rebuilt from
the fork objects whenever it disagrees with them, so it is safe to delete at
any time and **does not need to be backed up**: a restore without it simply
rebuilds it on first use. Every other object is authoritative.
Routine checks:
```bash
h5i-db fork list <db> # age, tables owned, bytes_own, bytes_pinned
h5i-db fork diff <db> <f> # what it changed (manifests only, no segments)
```
Abandoned forks are the usual cause of a database that will not shrink; see
Disk-usage math below.
## Compaction
Frequent small appends produce many small segments; queries then pay
per-segment open/prune cost. `h5i-db compact <db> <table>` rewrites them
into target-sized segments as a new version (row count is verified to be
preserved; the commit aborts otherwise).
- Compact when a table accumulates hundreds of small segments, or when
`versions` shows the segment count growing much faster than data volume.
- Compaction does **not** free disk: the pre-compaction segments remain
pinned by historical versions (see disk math below). It is a *query
performance* tool, not a space reclaimer.
## Mutation-plan hygiene
Plans (`--plan` on `delete-range` / `replace-range`) stage their segments at
plan time and protect them from vacuum until applied, discarded, or expired
(TTL: **7 days**, `PLAN_TTL_SECONDS`).
- List pending plans per table: `h5i-db plan list <db> <table>`.
- Discard plans you won't apply (`plan discard`). An applied-or-discarded
plan's staged segments become vacuum candidates immediately; an abandoned
plan holds its staged bytes for the full 7 days.
- Applying a plan after the table head moved fails with a conflict (409 from
the UI); re-plan instead of retrying.
## Disk-usage math
Nothing is ever deleted except by vacuum, and vacuum only deletes
*unreachable* objects; every committed version pins its segments forever
(version retention/GC is roadmap work). Practical consequences:
- **Append-only tables**: disk ≈ total data ever appended, plus one manifest
per commit. The manifest lists every live segment, so manifest overhead is
O(segments) per commit, which is another reason to compact and to batch
appends.
- **`replace-range` / `delete-range` / `compact` / `write`**: each rewrites
or re-references segments; the *old* segments stay pinned by history. A
daily full `write` of a 1 GiB table costs ~365 GiB/year until retention
exists.
- Quick audit: `du -sh <db>/tables/*/segments` vs.
`h5i-db tables <db>` row counts shows how much is history vs. head.
### "Vacuum ran and disk did not shrink"
The usual answer is a fork. Reclamation is *deferred*, not lost: a live fork
pins the versions it forked from, the retention floor cannot rise past them,
and so the segments they reference stay reachable, including the
pre-compaction copies of segments main has since rewritten. Main pays nothing
for a fork until main tries to reclaim.
```bash
h5i-db fork list <db> # bytes_pinned per fork
```
`bytes_pinned` is everything in the pinned version, so it is an **upper
bound** on what dropping that fork could release, not a measure of waste:
main's current head usually shares most of those bytes. Two forks pinning the
same version each report it, so the column does not sum.
Order of operations to actually reclaim:
1. `h5i-db fork drop <db> <name>` (or promote it first, if the work is
wanted). Nested forks must go from the leaves up.
2. `h5i-db set-retention …`, now free to move past the released pin.
3. `h5i-db vacuum <db> --apply`.
A fork's *own* tables are not vacuumed piecemeal: debris inside them is
reclaimed wholesale when the fork is dropped, which is why an abandoned fork
costs more than an active one.
## Filesystem caveats
- **Local ext4/xfs/apfs/NTFS**: the supported case. Durability relies on
fsync-before-`HEAD`-swap plus atomic rename, which is standard semantics on
all of these.
- **NFS and other network filesystems**: not recommended for multi-host
access. Writer exclusion uses an OS-level `flock` on an open descriptor;
its cross-host semantics depend on the NFS version and lock-daemon setup
(NFSv3 needs a working `lockd`; some mounts silently downgrade locks to
local-only). Close-to-open cache consistency can also delay another host's
view of a renamed `HEAD`. Single-host access to an NFS mount works but
still trusts the server's fsync honesty.
- **WSL2**: keep databases on the Linux filesystem (e.g. `~/data/…`,
ext4). On `/mnt/c` (drvfs/9p) fsync and rename atomicity are not
faithfully passed through to Windows, which voids the crash-safety
guarantees. (This repository's own benchmarks are run from the ext4 side
for the same reason.)
- **Containers**: overlayfs upper layers are fine; bind-mount the database
directory to a real volume for anything you care about.
---
## Runbook: torn or corrupt HEAD
**Should not happen** on a supported filesystem: `HEAD` is replaced by
write-temp → fsync → rename → directory-fsync. Treat an occurrence as a signal
of filesystem misbehavior (see caveats above), not as routine wear.
### Symptoms
- Any command fails with `Corruption { object: ".../HEAD", ... }`
("HEAD parse error"), or
- `HEAD` parses but `verify` reports `manifest missing` /
`checksum mismatch` at the head sequence, or readers fail opening the
manifest `HEAD` points at.
### Diagnosis
```bash
h5i-db verify <db> <table> # walks the checksum chain from HEAD back
cat <db>/tables/<uuid>/HEAD # {"format":1,"table_id":"…","sequence":N,
# "manifest_checksum":"<blake3-hex>"}
ls <db>/tables/<uuid>/manifests/ # zero-padded sequence-numbered JSON
```
Find the table's UUID via the catalog: `h5i-db tables <db>` then match, or
`grep -l '<name>' <db>/catalog/tables/*.json`.
### Recovery
1. **Stop writers** for the affected table.
2. **Find the newest intact manifest.** Starting from the highest file in
`manifests/`, compute each candidate's checksum and walk its parent
chain:
```bash
b3sum <db>/tables/<uuid>/manifests/<seq>.json # blake3 of file bytes
```
A manifest is a good recovery point if it parses, its `parent_checksum`
matches the blake3 of the parent file, and every segment `path` it lists
exists with the recorded byte size. (This is exactly the check `verify`
runs from `HEAD`; you are doing it from a candidate sequence instead.)
3. **Rewrite HEAD** to point at that manifest. `HEAD` is four fields of JSON,
and `manifest_checksum` must be the blake3 hex of the chosen manifest's
exact bytes:
```bash
SEQ=…; UUID=…; DB=…
SUM=$(b3sum --no-names "$DB/tables/$UUID/manifests/$(printf %012d $SEQ).json")
printf '{"format":1,"table_id":"%s","sequence":%d,"manifest_checksum":"%s"}' \
"$UUID" "$SEQ" "$SUM" > /tmp/HEAD.new
mv /tmp/HEAD.new "$DB/tables/$UUID/HEAD"
```
4. **Re-verify**: `h5i-db verify <db> <table> --deep` must be clean.
5. **Clean up**: manifests above the recovered sequence and any
`HEAD.tmp.*` / `HEAD.lock` files are debris; a later
`vacuum` (dry-run first) removes them. Do **not** delete them by hand
before verify is clean.
If no manifest verifies, restore the table from backup (above). Rolling
`HEAD` back this way discards the commits after the recovery point; check
`committed_at_ns` / `note` in the recovered manifest to know exactly where
the table now stands.
---
# Data on-ramp (https://db.h5i.dev/manual/data-onramp/)
`h5i_db.venues` turns vendor files **already on disk** into the canonical
tables a [backtest](backtest.html) reads. It does not fetch. Downloading
belongs in a script, where credentials, retries and rate limits belong; this
layer is the part that has to be reproducible and testable offline.
The canonical tables it writes are `book_deltas`, `trades`, `instruments`,
`resolutions`, `bars`, `funding`, `references` and `corporate_actions`.
`venues.CANONICAL_SCHEMAS` maps each name to its Arrow schema (the
individual `TRADES_SCHEMA`, `BOOK_DELTAS_SCHEMA`, … constants are the same
objects), and `venues.ensure_tables(db, names)` creates any that are
missing.
## Three steps, each usable alone
```python
from h5i_db import venues
specs = venues.polymarket_markets_from_json(payloads) # slug -> outcomes, tokens
venues.write_markets(db, specs) # instruments, resolutions
report = venues.ingest_archive( # book_deltas, trades
db,
files=venues.discover("/mnt/pmxt"),
markets=specs,
layout=venues.PMXT_LAYOUT,
window=(start_ns, end_ns),
)
report.coverage, report.gaps, report.replayed, report.skipped
```
The same from a shell, with market definitions travelling as a JSON file
rather than as flags:
```bash
python -m h5i_db.venues markets market.db specs.json
python -m h5i_db.venues ingest market.db specs.json --root /mnt/pmxt \
--start-ns 1777000000000000000 --end-ns 1777003600000000000 --min-coverage 0.95
python -m h5i_db.venues bars market.db --root /mnt/klines \
--layout binance-klines --instrument BINANCE:BTCUSDT
python -m h5i_db.venues inspect market.db
```
The same four verbs are on the `h5i-venues` console script, and
`h5i-backtest` / `h5i-capture` are the equivalents for the other two
modules.
`--min-coverage` exits non-zero rather than letting a short load pass
quietly, which is what makes this usable in a scheduled backfill. Gating
without a window is an error (exit `2`), not a silent pass; falling short of
the threshold is `3`.
## Market identity is positional, and refused when ambiguous
`MarketSpec` pairs `outcome_labels` with `tokens` by index: index *i* of each
describes the same outcome. Getting that backwards attributes every fill to
the wrong side, so the constructor refuses every loose way of expressing it.
Refused, each with a named reason: fewer than two outcomes; a token list that
does not match the outcome list; duplicate tokens; a token two markets both
claim; a `winner_outcome` with no `settlement_observable_ns` (settlement is
gated on when the result became knowable, so a resolution without that
instant is unusable); an observability instant before expiry.
`polymarket_markets_from_json` handles the awkward real shapes: list fields
arriving as JSON-encoded strings, a resolution expressed as settled
`outcomePrices` plus a `closed` flag, ISO-8601 or epoch times. Pass
`require_resolution=True` when a settlement study needs resolved markets
only.
## A vendor dialect is data, not a code path
`ArchiveLayout` carries the column names, event vocabulary, timestamp unit
and level shape. `PMXT_LAYOUT`, `TELONEX_LAYOUT`, `KALSHI_PMXT_LAYOUT`,
`LIMITLESS_PMXT_LAYOUT`, `OPINION_PMXT_LAYOUT`,
`KAGGLE_POLYMARKET_LAYOUT` and `KAGGLE_POLYMARKET_TRADES_LAYOUT` are
literals of that type, and a new vendor is another literal rather than
another module.
Three level shapes exist:
| `LevelLayout(style=…)` | Shape |
|---|---|
| `nested` | `bids`/`asks` are list columns of structs, one row per book state |
| `flat` | One row per level, side in its own column |
| `payload` | The whole event is a JSON string in one column — what a websocket capture written straight to Parquet looks like |
Under `payload` the token usually lives inside the JSON, so filtering
happens in two passes: cheaply on the instrument column, then on the decoded
token.
```python
layout = venues.ArchiveLayout(
name="house-feed", timestamp_column="recv_ns", timestamp_unit="ns",
token_column="token", event_type_column="channel", snapshot_events=("depth",),
levels=venues.LevelLayout(style="nested", bids_column="buys", asks_column="sells",
price_field="px", size_field="qty"),
max_levels=1, # keep top of book; the drop count is reported
)
```
No directory convention is assumed. Pass explicit `files=`, or a root plus a
glob to `venues.discover(root, pattern=…)`: a mirror layout is not a data
contract, and people mirror differently.
## Four properties worth knowing before pointing it at a mirror
**Re-running is a replay, not a duplicate.** Every commit is keyed by the
hash of the *normalised* rows, so identical inputs produce identical keys and
h5i-db recognises them. An interrupted backfill is safe to restart, and two
sources serving the same hour converge on one commit. `report.replayed` says
whether the whole ingest was already present.
**Requested and loaded windows stay separate facts.** `report.coverage` is
the loaded span over the requested one, and `None` when no window was asked
for, because a ratio against an unbounded request means nothing. It says
nothing about holes *inside* the span; those are `report.gaps`.
**Nothing is guessed.** An event type present in the file but absent from the
layout is counted in `report.skipped`; a file missing required columns is
skipped with its name and the missing columns; an unparseable payload is
counted; and `max_levels` records how many levels it dropped. A truncation
nobody can see is how a wrong conclusion arrives three steps later.
**Zero size means delete.** These venues spell "this level is gone" as a
size-zero change, so the importer writes `delete`, not a level with no
quantity.
`IngestReport` carries `vendor`, `tables` (a `TableWrite` per table: rows,
chunks, replayed chunks, idempotency keys), `sources` (a `SourceFile` each:
path, size, rows read, rows kept), `requested_window`, `loaded_window`,
`gaps`, `skipped` and `unknown_instruments`, plus the derived `coverage`,
`rows` and `replayed`. `to_dict()` renders the lot.
## Bars
Anything shaped like OHLCV goes through one on-ramp, whatever produced it.
```python
# a vendor dump on disk
venues.ingest_bars(db, files=[...], layout=venues.BINANCE_KLINES_LAYOUT,
instrument_id="BINANCE:BTCUSDT")
# anything already in memory: a broker export, yfinance, your own frame
bars = venues.bars_from_dataframe(frame, instrument_id="AAPL")
# a venue that publishes no candles at all
venues.bars_from_trades(db, interval="1m")
```
`ts_init` is the bar **close** and `ts_event` the open, because a bar is not
knowable until its interval ends. That is why a `BarLayout` must supply
either a close-time column or an `interval`: there is no safe default, so
there is no default. The same rule is why `references_from_series` makes you
state `published_after` — a daily rate for Monday is published on Tuesday,
and stamping it at Monday lets a strategy read it a day early.
`GENERIC_OHLCV_LAYOUT` and `BINANCE_KLINES_LAYOUT` cover the common files;
`read_bars_csv` and `bars_from_table` are the pieces underneath, for a file
you want to inspect before writing it. `parse_interval("1m")` is the same
interval parser the layouts use.
Yahoo has had no official API since 2017, so `yfinance` is a scraper that
breaks when Yahoo changes internals. Fetch with it if you like, then hand the
frame to `bars_from_dataframe`: that keeps the breakage in your script rather
than in a parser here. Stooq downloads now sit behind a browser check, so
fetch those by hand and point `read_bars_csv` at the file.
## Trades and corporate actions
A venue that publishes bulk trade files gives you real microstructure for
free.
```python
venues.ingest_trades(db, files=[...], layout=venues.BINANCE_TRADES_LAYOUT,
instrument_id="BINANCE:BTCUSDT")
```
The one field worth reading twice is the aggressor. Binance ships
`isBuyerMaker`, which is true when the **buyer** was resting, so the taker
was the seller. Read straight through, it inverts every trade sign, and the
result still balances and still sums to the right volume. `TradeLayout`
therefore takes either `buyer_is_maker_column` or `aggressor_column`, never
both. `BINANCE_TRADES_LAYOUT` and `BINANCE_AGG_TRADES_LAYOUT` are the
shipped literals, and `read_trades_csv` / `trades_from_table` are the
in-memory halves for a file you want to inspect first.
Corporate actions are what make an equity backtest correct rather than
plausible. Without them a 2-for-1 split reads as a 50% overnight crash.
```python
venues.ingest_corporate_actions(
db,
actions=[{"instrument_id": "AAPL", "kind": "split", "ratio": 2.0,
"effective": "2026-03-02", "announced": "2026-02-01"}],
known_by=simulated_now_ns, # drop what had not been announced yet
)
```
`effective` is the replay clock, because that is when positions and resting
orders change. Past prices are never rewritten: nobody traded the adjusted
price, and a strategy that bought at 50 the day before a 2-for-1 bought at
50. `announced` is kept separately so `known_by` can reproduce what was
knowable on a past date; rows with no announcement are dropped under a cutoff
rather than assumed early enough, since assuming is how a run ends up
trading a split nobody had heard of.
`corporate_actions_from_rows` and `references_from_series` build the Arrow
tables without writing them, and `ingest_references` appends a reference
series (a benchmark, a rate, a vendor mark) that a strategy may read only
after its `published_after` instant.
## Kalshi and Predexon
Kalshi's hourly archive quotes both outcomes as bids, and its deltas are
signed changes in resting size, so it needs its own layout. Everything else
is the same three steps. A market needs no `tokens=`: the files name the
instrument and pick the outcome with a label, so the outcome order comes from
`outcome_labels`.
```python
specs = [venues.MarketSpec(instrument_id="KXBTC15M-26JUN100815-15",
venue="kalshi", outcome_labels=("yes", "no"))]
report = venues.ingest_archive(
db,
files=venues.discover("/mnt/kalshi", pattern="kalshi_orderbook_*.parquet"),
markets=specs,
layout=venues.KALSHI_PMXT_LAYOUT,
)
```
Read two numbers out of the report before trusting the result:
- `report.gaps` carries a `snapshot_divergence` entry saying how many vendor
snapshots the reconstruction reproduced exactly. This feed has no sequence
numbers, so that comparison is the only integrity check available. A low
share almost always means the window's deltas are incomplete, and the fix
is to load the neighbouring hours, not to lower expectations.
- `report.skipped` counts changes that arrived with no book to apply them to
(`delta_before_snapshot`). An hour that was never snapshotted for a market
contributes nothing rather than inventing a base of zero.
Predexon serves snapshots instead of an archive:
```python
snapshots = fetch_pages(...) # your script, your API key
report = venues.ingest_predexon_orderbooks(db, snapshots=snapshots, markets=specs)
```
Check `report.gaps` for `snapshot_cadence` before trusting a window: it
carries the measured median and worst gap between samples, and the worst gap
is the number that matters. The vendor's `sequence` field is deliberately
unread, because it is not a per-market counter — it steps by a median of 45,
jumps by millions, and runs backwards — so differencing it invents holes that
are not there.
### One book, two sides
Kalshi publishes `yes_bids` and `no_bids`, two books of bids, because an ask
on YES is a bid on NO. Both the archive layout and the Predexon reader fold
them into a single two-sided book on outcome 0, at `1 - price` for the NO
side. A capture, an archive and a Predexon pull therefore give the same
canonical shape.
That fold is not cosmetic. Storing the two as separate one-sided books leaves
a market with no asks at all, and an order that cannot fill is **cancelled**
rather than rejected, so the run completes and the strategy simply reads as
having declined to trade.
## Manifold
Manifold publishes markets and bets as JSON rather than as book files, so it
has its own pair of readers:
```python
specs = venues.manifold_markets_from_json(payloads)
venues.write_markets(db, specs)
venues.ingest_manifold_bets(db, bets=bets, markets=specs)
```
`manifold_trades_from_json` is the same conversion without the write. Both
take a `skipped=` collector, so payloads they refused are countable rather
than silently absent.
## Recording a live feed
`h5i-capture` (from `pip install h5i-db[capture]`, or
`python -m h5i_db.capture`) records a venue websocket to lz4-compressed
newline-delimited JSON — the same format the archive readers consume, so a
capture and a vendor archive load through one path.
```bash
export KALSHI_API_KEY_ID=… # never a flag: a flag lands in ps output
export KALSHI_PRIVATE_KEY_PATH=/path/to/key.pem
h5i-capture --venue kalshi --out ./capture --market KXBTCD-25DEC31
```
| Flag | Meaning |
|---|---|
| `--venue kalshi\|polymarket` | Kalshi needs credentials; Polymarket is public |
| `--out <dir>` | Files land in `<out>/<venue>/<date>/<hour>` |
| `--market <id>` | Repeatable, or comma-separated |
| `--channel <name>` | Override the venue's default channels |
| `--url <ws>` | Point at a demo or staging endpoint |
| `--flush-secs <n>` | How often completed lz4 blocks reach the file — this bounds what a `kill -9` can destroy |
| `--keepalive-secs`, `--max-backoff-secs` | Keepalive cadence and reconnect ceiling |
It stamps arrival in nanoseconds and writes the payload verbatim. Both rules
exist for the same reason: an arrival stamp cannot be reconstructed later,
and parsing on the write path means a parser bug costs the data rather than
an afternoon.
The same thing as a library, for a feed the CLI does not speak:
```python
from h5i_db.capture import CaptureWriter, archive_line, now_nanos, read_hour
with CaptureWriter("./capture", "kalshi", flush_after=5.0) as writer:
received_at = now_nanos()
writer.write_line(received_at, archive_line(received_at, frame))
read_hour("./capture/kalshi/2026-07-31", "14") # or read_capture(path)
```
Credentials are read from `KALSHI_API_KEY_ID` + `KALSHI_PRIVATE_KEY_PATH`
(or `KALSHI_API_TOKEN`), never from an argument; `sign_kalshi` and
`kalshi_headers` are the signing pieces, and a missing credential raises
`MissingCredential` rather than connecting anonymously and failing later.
This is a separate package from `h5i_db.venues` on purpose. That package is
parse-only — every function takes bytes the caller already downloaded, which
is what makes the mapping a pure function testable offline against recorded
payloads. Sockets, credentials and reconnect policy would end that, so they
live here. The two meet at a file format, not at a function call. The extra's
dependencies (`websockets`, `cryptography`, `lz4`) are imported inside the
functions that need them, so a user who only reads archives never pays for a
TLS stack.
Record only what an archive cannot give you. For Kalshi after January 2026,
`predexon_book_from_snapshots` is usually the better answer, and recording
buys sub-cent precision, your own arrival clock, and independence from a free
service rather than access to otherwise-missing data.
## Replaying an account's ledger
The strictest realism question available: given the trades an account
actually took, does the engine reproduce the same portfolio? Usually not, and
that is the point — a forced-fill simulator would reproduce the ledger by
construction and test nothing.
```python
rows = [venues.LedgerRow(ts_ns=…, instrument_id=…, outcome=0,
side="buy", quantity=…, price=…), …]
commands = venues.commands_from_ledger(rows, specs) # limit-IOC at the ledger price
db.append("commands", commands) # sells are reduce_only
result = backtest.execute(db, config) # DataConfig(commands="commands", …)
venues.compare_to_ledger(result, typed_rows) # per-market reconciliation
```
Compiled into *intent*, not fills, so the historical book accepts or refuses
each order on its merits. `reduce_only` on sells stops a replay inventing
short exposure the ledger never showed. The comparison reports per-market
shortfalls rather than one pass/fail, because *where* the book refused is the
finding. `ledger_table(rows)` writes the ledger itself as a table when you
want the two side by side in SQL.
## Fetching, when you have network
Fetchers are scripts, not core API surface. Two limits are properties of the
public APIs rather than of the tooling: Polymarket's book endpoint serves
**live markets only**, so a resolved-market list is the right input for
definitions and the wrong one for books, and historical coverage is price
*points*, not books, so any depth or queue study needs a captured archive.
One practical trap: these hosts answer `403` to the default `Python-urllib`
user agent, which reads exactly like a blocked network and is not one. Set
any `User-Agent`.
---
# Backtesting (https://db.h5i.dev/manual/backtest/)
`h5i-db-backtest` simulates venues against recorded market data. It never
routes a live order.
Three properties shape the design, and each is a test rather than an
intention:
- **Determinism.** A run is a pure function of (data pin, strategy, config).
No wall clock, no unseeded randomness, no iteration over a hash map
without sorting first.
- **No look-ahead, structurally.** Records carry `ts_event` and `ts_init`
and replay in `ts_init` order, so late data arrives late. A strategy has
no route to a market's resolution, because resolutions are read after the
run finishes.
- **Data honesty.** Windows are half-open and owned in one place; a gap in
incremental data invalidates the book rather than being replayed across;
requested and loaded windows stay separate facts.
## The tables
Market data is venue-neutral. A Polymarket loader and a Hyperliquid loader
both produce the same tables, and the kernel never learns which vendor a row
came from.
| Table | Holds |
|---|---|
| `book_deltas` | snapshots, incremental level changes, and explicit gaps |
| `trades` | prints, with an optional aggressor |
| `bars` | aggregates |
| `instruments` | one row per outcome, so a categorical market is N rows |
| `resolutions` | how a market ended; never read on the strategy path |
Every market-data table is time-indexed on `ts_init`, the column replay
sorts by, so a range scan prunes on exactly the right column.
Numbers are fixed point in the kernel and `Float64` on disk by default. The
conversion keeps all nine decimal places and is exact up to a magnitude of
about nine million, which covers every price a venue quotes; a test walks the
entire 0.0001 tick grid to confirm it rather than assuming. For a book that
will hold larger numbers than that, see [Precision and range](#precision-and-range).
### Snapshots are grouped
A snapshot is many rows sharing an `event_index`, with `is_last` on the
final one. Applying half a snapshot would leave a crossed or hollow book, so
a reader that meets a truncated one refuses it instead of reconstructing
something plausible from the fragment.
One event is one book: every row under an `event_index` must carry the same
`instrument_id` and `outcome`. A feed that puts two outcomes under one event is
refused for the same reason a truncated snapshot is. The alternative is worse
than an error, because the levels would merge into a single book whose best ask
belongs to the other side of the market, and a buy would fill against it
without complaint.
The index values themselves only have to *change* between events. They are not
required to increase with `ts_init`, since grouping follows row order and ends
at `is_last`, so a recorder that writes instrument-major is fine.
## Precision and range
Two independent choices decide how large a number can get. Both default to
the cheaper option, and most books never need to change either.
**On disk**, a fixed-point column is `Float64` by default. That is exact to
about nine million units; past that an `f64` mantissa stops holding
consecutive values and writes begin to round. A table that will hold more is
created with the decimal encoding instead, which stores the number itself and
rounds nothing:
```rust
use h5i_db_backtest::{FixedEncoding, store};
store::create_market_data_tables_with(&db, FixedEncoding::Decimal).await?;
```
The encoding belongs to the table rather than to your binary: it is recorded
in the table's schema, and every reader follows what it finds. Two tables in
one database can differ, and any build reads both. The call above skips
tables that already exist, so running it before an ingest is enough -- the
loaders write whichever encoding the table already declares, and existing
tables keep the one they were created with.
**In the kernel**, arithmetic is `i64` scaled by 1e-9, which tops out at
±9,223,372,036. The `wide` feature makes it `i128`:
```toml
h5i-db-backtest = { version = "0.1", features = ["wide"] }
```
The scale does not move when the integer widens, so nothing already written
changes meaning and there is nothing to migrate.
| | exact to | kernel cost |
|---|---:|---:|
| default | 9.0e6 units | -- |
| decimal columns | 9.2e9 units | none |
| decimal columns + `wide` | 1.7e29 units | ~50% slower |
The middle row is the one worth knowing about. On an ordinary build, decimal
storage is exact across the entire range the default arithmetic can produce,
and costs nothing at run time because the kernel never sees a column. What it
does cost is disk: sixteen bytes a value instead of eight.
### Filtering a decimal column in SQL
Write the comparison value as a cast, not a bare literal:
```sql
-- exact
SELECT * FROM trades WHERE price > CAST('9007199.254740993' AS DECIMAL(38,9));
-- lossy above ~9e6: the literal is Float64, so the column is coerced to f64
SELECT * FROM trades WHERE price > 9007199.254740993;
```
A bare decimal literal is typed `Float64`, so comparing a decimal column
against one converts the column to `f64` for the comparison and gives up the
exactness the column was chosen for. It is exact below about nine million,
which is where a `Float64` column would have been fine anyway; past that,
equality can miss the row it names. The cast keeps the comparison in decimal.
Filtering on time is unaffected, which is the path a replay takes.
Reach for `wide` only when you need to *compute* past 9.2e9 units -- a
yen-denominated book, or token quantities in the trillions. It buys range and
pays for it in arithmetic, because an `i128` add spans two registers.
Nothing is silently truncated in either direction. A default build reading a
decimal value too large for its `i64` refuses it, the same way arithmetic
overflow is refused rather than allowed to wrap.
## A run
```rust
use h5i_db_backtest::{run_in_fork, RunSpec, Money};
let mut strategy = SignalReplay::new(intents)?;
let report = run_in_fork(
&db,
RunSpec::new("momentum-001", Money::from_units(10_000)?)
.window(window)
.read_at(ReadAt::Snapshot("2024-q1".into()))
.minimum_coverage(0.95),
&mut strategy,
|engine| engine.fee_model(Box::new(PredictionMarketFees::new(0.07)?)),
).await?;
```
The run creates a fork, replays inside it, and writes its results there:
| Table | Holds |
|---|---|
| `bt_run` | the manifest: pin, config digest, cash, how far it simulated |
| `bt_orders` | every order and its final status |
| `bt_fills` | every execution |
| `bt_positions` | where it finished, with settlement attribution |
| `bt_equity` | the equity curve |
Because results are ordinary tables on a branch, everything else already
works: `fork_diff` compares two runs at fill level, a cross-fork scan
aggregates a sweep, `promote` publishes a blessed run, and `drop_fork_tree`
disposes of the rest.
`bt_fills` is authoritative. Positions are a fold over it and nothing else,
so a stored run can be rebuilt and checked rather than trusted.
## From a run to a report
```python
result.report("run.html")
```
One self-contained HTML file: no dependencies, no network access at view
time, so it can be attached to a review, committed beside the run, or opened
years later. It opens with the evidence that says how far the numbers can be
trusted (replay fidelity, the pin, coverage, preflight findings), then the
performance panels, then the execution record: order lifecycle, rejection
reasons, every fill. The configuration is carried verbatim at the end, so the
page is enough to re-run the run it describes. In a notebook, the result
renders as that page.
A run that produced no equity curve still reports. The performance panels
drop out and the execution evidence stays, because a tearsheet of nothing is
worse than a manifest.
For the performance statistics alone, the equity curve is an ordinary table:
```python
from h5i_db import quant
fork = db.fork("bt-momentum-001")
series = quant.from_levels(fork, "bt_equity")
quant.tearsheet(series, path="tearsheet.html")
```
The statistics are the same empyrical-parity set documented in
[Quant workflows](quant.html); nothing about them is backtest-specific.
## From the shell
The Python bindings carry a typed configuration, `BacktestConfig`, whose
sections are `data`, `execution`, `portfolio`, `risk` and `output`. It
round-trips through JSON, so a config file is a complete reproduction recipe
and the same contract drives both `backtest.execute(db, config)` and a
command line:
```bash
python -m h5i_db.backtest inspect market.db config.json
python -m h5i_db.backtest run market.db config.json
python -m h5i_db.backtest list market.db
python -m h5i_db.backtest report market.db momentum-001 --output run.html
python -m h5i_db.backtest verify market.db momentum-001
```
| Verb | Prints | Exit |
|---|---|---|
| `inspect` | the preflight inspection: replay fidelity, per-table stats, errors and warnings | `2` when the config is refused |
| `run` | the run summary, after refusing on preflight errors | |
| `list` | one run summary per `bt-` fork, in fork-name order | |
| `report` | the path it wrote | |
| `verify` | whether re-executing the stored config reproduced it | `3` when it did not |
Two flags are worth knowing. `run --allow-preflight-errors` records the
findings and runs anyway, for the case where you know why the data is thin.
`report` writes the run report described above; `report --tearsheet` renders
the equity tearsheet alone instead, and falls back to the run report when a
run produced no equity curve, because a tearsheet of nothing is worse than a
manifest. (`--execution-only`, which used to select a bare manifest, still
works and now selects the run report, which contains that manifest.)
Exit codes are distinct on purpose: a refused config (`2`) and a run that
failed to reproduce (`3`) call for different responses, and a script should not
have to parse stdout to tell them apart.
## The config, and what comes back
```python
config = backtest.BacktestConfig(
run_id="momentum-001",
portfolio=backtest.PortfolioConfig(starting_cash=10_000.0),
data=backtest.DataConfig(signals="signals", snapshot="2024-q1",
minimum_coverage=0.95),
execution=backtest.ExecutionConfig(fee_rate=0.02, queue_position=True,
latency_nanos=1_000_000),
risk=backtest.RiskConfig(max_abs_position=500.0),
output=backtest.OutputConfig(equity_interval_nanos=60_000_000_000),
)
config.to_json() # a complete reproduction recipe
config.trial_digest # identity for the trial ledger; excludes run_id and metadata
```
| Section | Carries |
|---|---|
| `PortfolioConfig` | `starting_cash` |
| `DataConfig` | `signals`, `commands`, `strategy_id`, the read pin (`snapshot`, `version`, `as_of`), `window`, `minimum_coverage` |
| `ExecutionConfig` | `fee_kind`, `fee_rate`, `maker_rebate`, `maker_fee_rate`, `queue_position`, `optimistic_queue`, `latency_nanos`, `slippage_ticks`, `margin_kind`, `leverage`, `maintenance_margin_rate` |
| `RiskConfig` | `max_order_quantity`, `max_abs_position`, `max_open_orders` |
| `OutputConfig` | `equity_interval_nanos` |
`backtest.inspect(db, config)` runs preflight alone and returns a
`PreflightInspection` with the `config_digest`, the `ReplayFidelity` the data
supports (`TICK_L2`, `SNAPSHOT_L2`, `TRADES_ONLY`, `BARS_SYNTHETIC`, `NONE`),
per-table statistics, and `issues` split into `errors()` and `warnings()`.
`raise_for_errors()` is the one-line gate; `execute()` calls it for you unless
`preflight=False`. A config that changed shape across versions loads with a
`ConfigCompatibilityWarning` rather than silently meaning something else.
`backtest.run(db, run_id, starting_cash=…, …)` is the flat-keyword form of
the same thing, for a run you are writing by hand rather than storing.
### Opening a run again
Every run lands in a `bt-<run_id>` fork holding the tables in
`backtest.RESULT_TABLES` (`bt_run`, `bt_orders`, `bt_fills`, `bt_positions`,
`bt_equity`), read from the market-data tables in
`backtest.MARKET_DATA_TABLES`. The result is addressable long after the
process that made it exited:
```python
result = backtest.open_result(db, "momentum-001")
backtest.list_runs(db) # one summary per bt- fork
backtest.find_trial(db, digest) # the run with this trial digest, if any
result.stats() result.summary() result.table("bt_run")
result.equity result.fills result.orders result.positions
result.fork_name result.run_id
result.verify() result.explain(order_id) result.compare(other)
result.report(path) result.tearsheet(path)
result.promote(["bt_equity"]) result.drop()
```
`BacktestResult` is also a mapping over the run summary, so `result["cached"]`
and `result["final_cash"]` work directly. `drop()` removes the fork and
everything it owns; `promote()` lands one of its tables on the base.
## Settlement
Settlement is not an event in the replay stream. It is a policy applied
after the run, gated on one question: did the run reach the instant the
resolution became observable?
A three-day replay of a six-month market ends holding a position. Marking it
to the eventual winner books a profit nobody trading those three days could
have collected, so settlement applies only when
`simulated_through >= observable_at`. Otherwise the mark-to-market result
stands and the report says why.
Both numbers survive. `market_exit_pnl` is what the position was worth at
the last mark, `settled_pnl` is what it became at resolution, and the
difference is reported as an explicit adjustment rather than folded in
silently.
### Not every market picks a winner
A `Resolution` carries a `Payout`, which is one of three things:
| Payout | What happened |
|---|---|
| `Winner(outcome)` | one outcome took the whole dollar |
| `Split(payouts)` | a scalar or partial settlement, payouts summing to one |
| `Void { outcomes }` | the question was unanswerable; a complete set refunds at cost |
The last two are not edge cases worth skipping. A voided binary pays both
sides fifty cents; recording it as a winner is wrong by the full notional on
each side, in opposite directions. `Resolution::split` refuses a payout
vector that does not sum to exactly one, because a settlement that does not
conserve a complete set mints or burns cash.
## Trading stops before resolving
A market's `expiration` is when trading stops, and the engine enforces it:
an order submitted after it is rejected, an order whose latency carries it
past it is rejected on arrival, and a resting order is cancelled at the
bell rather than left working against a book that no longer exists.
Data keeps arriving after a market closes -- late prints, a book teardown,
the settlement itself -- and observing it is fine. Filling against it is not.
## Outcomes cannot be borrowed
There is no stock loan for a share of "YES". A venue will not let you sell
an outcome you do not hold, so neither will the engine: a sell beyond the
held position is rejected with a message naming the trade that expresses the
same view. To be short YES, buy NO. It costs `1 - p` and pays the same.
This matters twice over. A short's worst case is `1 - p`, not `p`, so
collateralising it at the mark understates the requirement by up to the
whole notional -- selling a three-cent longshot posts three cents against
ninety-seven cents of risk. `CashMargin` charges the complement on a
probability short for that reason.
`EngineBuilder::allow_naked_shorts(true)` lifts the constraint. It is for
measuring what the constraint costs, not for producing a result to act on.
## Complete sets
Prediction-market venues will exchange a complete set of outcomes against
one unit of cash, in both directions. That is the contract that makes the
sum-to-one relationship *transactable* rather than merely observable, and
without it complete-set market making and the arbitrage that pins a book to
a dollar are both unreachable.
```rust
ctx.mint(&market, sets); // pay one per set, receive one of every outcome
ctx.redeem(&market, sets); // hand the set back, receive one per set
```
A Python callback strategy returns the same operations as actions:
```python
return [
{"action": "mint", "instrument_id": market, "quantity": 100.0},
{"action": "redeem", "instrument_id": market, "quantity": 100.0},
{"action": "convert", "instrument_id": market,
"outcomes": [0, 1], "quantity": 50.0},
]
```
Three things about how this is modelled:
- **Legs are fills.** A mint emits one fill per outcome, priced so the legs
sum to exactly one per set. `Portfolio::replay` rebuilds every position
from `bt_fills` alone, and a position that moved without a fill to explain
it is a run whose stored result and audit disagree.
- **It is not instant.** Set operations queue behind the same insertion
latency as an order. A mint that lands immediately is an arbitrage nobody
could have taken.
- **It costs a flat fee, not a rate.** `SetOperationCosts` is charged once
per operation, because a mint is one chain transaction. That is what makes
a one-cent complete-set edge unprofitable at small size and profitable at
large; a model without it reports every such edge as free money.
Minting is allowed only where the venue actually offers it:
`Instrument::supports_complete_set` is true for any two-outcome market, and
for a wider one only when `neg_risk` says the venue wired the outcomes into
a single exclusive set. A group of independent conditions displayed under
one heading is several instruments here, not one with many outcomes, and
minting across them would create a dollar out of nothing.
### Negative-risk conversion
`ctx.convert(market, held, quantity)` is Polymarket's conversion, addressed
the way its adapter is: `held` names the outcomes whose NO side you hold.
In this crate's N-outcome model it is provably a redemption. NO(i) is
"everything except i", so a basket of NO over `k` outcomes holds each named
outcome `k - 1` times and each unnamed one `k` times -- that is `k - 1`
complete sets plus the residual. The venue hands back `k - 1` in cash and
keeps the residual, which is exactly what redeeming `k - 1` sets does. The
primitive is offered under its own name because strategies reason in NO
contracts and the derivation is not obvious; a test pins the equivalence.
## Scoring a forecast
A market price on a prediction market *is* a forecast, so the natural
question about a strategy is whether it forecast better. Fills cannot answer
it: they record what a strategy did, not what it believed.
```rust
ctx.record_forecast(&market, outcome, Price::from_f64(0.65)?)?;
```
```python
return [{"action": "forecast", "instrument_id": market,
"outcome": 0, "probability": 0.65}]
```
`RunReport::calibration_samples()` joins those statements to the market's
own price at the same instant and to what the outcome actually paid, giving
the triples a Brier score, a reliability curve or an advantage-over-market
series is computed from. The Python report returns them as
`calibration_samples`, ready for `h5i_db.quant.calibration`.
Two kinds of forecast are dropped rather than scored, and both are reported
by `unscored_forecasts()` so the sample is never quietly smaller than it
looks: a market with no known resolution, and a market that paid every
outcome the same. Against a void, every forecast scores identically --
including a confidently wrong one -- so including it does not measure a
forecaster, it dilutes the sample that does.
The market's own probabilities are sampled onto the equity curve's clock as
`mark_curve`, for the comparison series. Turn it off with
`record_mark_curve(false)` on a run spanning thousands of markets.
## Venue models
Four small traits carry every behavioural variation:
| Trait | Decides |
|---|---|
| `FeeModel` | what a fill costs |
| `FillModel` | what book an order meets |
| `LatencyModel` | when the venue hears about an order |
| `VenueModule` | periodic processes (funding, liquidation) |
New behaviour is a new implementation, never a new flag. `FillModel` has one
escape hatch worth knowing: `book_for_fill` may return a *synthetic* book,
and matching runs against it unchanged, so slippage, bar-derived quotes and
synthetic depth all reuse one matching path.
`PredictionMarketFees` implements the curved fee these venues actually
charge, `rate · quantity · p · (1 - p)`, which peaks at even odds and
vanishes at certainty. A flat `notional × rate` is the wrong shape and
overcharges the tails, which is where these markets trade most.
## Perpetuals
A derivatives venue does not value your position at the mid, and modelling
it as if it did produces losses that look like strategy results.
### Three prices, not one
Hyperliquid publishes an **oracle** built from spot exchanges and a **mark**
derived from it and the book. Margin, unrealised PnL and liquidation read
the mark; funding is charged on the oracle; the book's mid is neither.
`MarketEvent::Reference` carries both, in a `references` table alongside
`trades` and `funding`. The engine keeps the book-derived, venue-mark and
oracle prices apart and exposes one effective mark, so `MarkSource` is a
policy rather than a rewrite. It defaults to using the venue's mark where
one exists, which is a no-op on data that carries none.
Two consequences worth stating:
- A thin book or a one-print wick moves the mid far enough to liquidate a
position the venue was still valuing calmly. On the mark it does not.
- Funding on the mid reintroduces exactly the manipulation the oracle
exists to prevent, and at hourly settlement that compounds over a carry.
Reference records replay at priority 7, ahead of the book, for the same
reason corporate actions do: every per-record check that prices against the
mark must already have it.
### Prices the venue will actually accept
Hyperliquid caps a price at five significant figures **and** at
`6 - szDecimals` decimal places, per coin. A flat tick cannot say that: it
accepts prices the venue refuses at the top of a range and refuses ones it
accepts at the bottom. `PriceRule::SignificantFigures` says it, and
`hyperliquid::parse_meta` derives it per coin along with `maxLeverage`,
`onlyIsolated` and the lot size.
Delisted coins are kept, not dropped. Dropping them is survivorship bias
applied at ingestion, where it is hardest to notice.
### Data
`candleSnapshot` returns bars and the REST `l2Book` returns only the book as
it is right now, so the archive is the only source of book history the venue
offers. `read_archive_lz4` reads the hourly files; `parse_ws_message`
handles a live capture, dispatching `l2Book` and `trades` by the coin in the
payload so one call covers a capture spanning many markets.
`s3://hyperliquid-archive` (us-east-1) is a **Requester Pays** bucket:
anonymous reads get a 403 whatever headers you send, and you need an AWS
account and pay the transfer. Pull from us-east-1 and the transfer is free;
you pay only GET requests, which are a fraction of a cent.
```bash
aws s3 ls s3://hyperliquid-archive/market_data/20250101/0/l2Book/ \
--request-payer requester
aws s3 cp s3://hyperliquid-archive/market_data/20250101/0/l2Book/BTC.lz4 . \
--request-payer requester
```
Measured on `20250101/0`: 166 coins, 77 MB compressed for the hour, so
roughly 1.8 GB a day and 670 GB a year. BTC alone is 700 KB an hour, 6,301
snapshots, one every 0.57 seconds.
Two things the archive is **not**:
- **It has no trades.** `market_data/<date>/<hour>/` contains `l2Book/` and
nothing else. Prints have to come from a live websocket capture, and
without them the queue-position fill model has nothing to work with. This
is the single biggest remaining gap for a Hyperliquid market-making
backtest.
- **It is not bucketed by venue time.** Files are grouped by when the
archiver received a message, so the `00` hour file opens with a message
the venue stamped at `23:59:59.877` the day before. Load a window with an
hour of slack on each side.
Each line is `{"time": <archiver receive, ISO nanoseconds>, "ver_num": 1,
"raw": <the live websocket envelope>}`, and the venue's own stamp is inside
at `raw.data.time`. **Both matter.** The archiver trails the venue by 57 ms
at the median and 3.2 seconds at the worst, so the outer stamp is `ts_init`
and the inner one is `ts_event`; reading only the inner one hands a strategy
up to three seconds of look-ahead on every book update. `read_archive` keeps
them apart, and clamps `ts_init` to at least `ts_event` so a skewed clock
cannot claim a message was known before it was sent.
The live endpoints need no credentials, which is where
`tests/fixtures/hyperliquid` came from. Refresh them with:
```bash
curl -sX POST https://api.hyperliquid.xyz/info \
-H 'Content-Type: application/json' -d '{"type":"metaAndAssetCtxs"}'
```
Fixtures captured from the venue are worth the bytes. A hand-written one
tests that a parser matches what its author believed; a real one tests that
it matches what the venue sends. The `fundingHistory` request shape here was
wrong -- it nested its arguments under `req`, which the API rejects with a
422 that names no field -- for exactly as long as only the first kind
existed.
Two shapes to know, both caught this way: `fundingHistory` takes its
arguments flat while `candleSnapshot` nests them under `req`, and funding
timestamps carry tens of milliseconds of jitter around the hour rather than
landing on it exactly.
`ArchiveRead` counts what produced nothing and `require_yield` refuses a
file that is mostly junk -- usually the wrong channel or the wrong
decompression, and worse to replay as a thin book than to refuse outright.
Trades matter more than they look: without prints the queue model has
nothing to consume the size ahead of a resting order, so every touched
limit fills immediately.
`asset_ctxs/<date>.csv.lz4` is the historical mark and oracle — a daily CSV
at one-minute cadence across the universe. Its `funding` column is the
standing rate sampled once a minute, **not** a payment, so
`asset_context_records` deliberately emits reference prices and no funding
events; sixty samples an hour would charge a carry sixty times. Settlements
come from `fundingHistory`.
`hyperliquid_archive::load_archive` walks a synced directory and commits it,
so the whole path is one call:
```rust
let spec = ArchiveSpec::new("./archive").date("20250101").coin("BTC");
load_archive(&db, &spec, &universe, known_at).await?;
```
Coins filter on the *file name*, so a one-coin load reads 700 KB instead of
77 MB. A date, hour or coin that is not on disk lands in `ArchiveLoad::missing`
rather than reading as no data — an incomplete sync found at replay time
looks like a thin book. Instruments come from a supplied `meta` universe,
not from the archive, which carries prices and no metadata.
### Post-only
`OrderRequest::post_only()` is Hyperliquid's ALO. It is checked on arrival,
not on submission, because what matters is the book the venue sees when the
order gets there -- an order sent into a wide market and delivered into a
crossed one is rejected, which is the risk a maker takes. A post-only order
that later fills does so as a maker, since by construction it could never
have taken.
### Stops and take-profits
`OrderRequest::with_trigger` holds an order off the book until a price
reaches it. That is the difference from a limit at the same price: a limit
is liquidity someone can trade against, and a stop is not — so resting one
would invent depth that was never there. Untriggered orders carry their own
`OrderStatus::Untriggered`.
They fire on the **mark**, not the book, for the same reason margin does: a
one-print wick should not stop you out of a position the venue still values
calmly. Once fired the order is ordinary and meets the book from there, so a
stop can and does fill well below its trigger — the gap it exists to protect
against is the gap it suffers.
```rust
ctx.submit(
OrderRequest::market(market, outcome, Side::Sell, size)
.with_trigger(Trigger::stop_loss(Side::Sell, price)),
);
```
```python
return [{"action": "submit", "client_order_id": "stop", "instrument_id": m,
"side": "sell", "quantity": 1.0,
"trigger_price": 90.0, "trigger_direction": "stop_loss"}]
```
### TWAP
Hyperliquid's TWAP is a native order type, not a client-side loop: the venue
slices it into equal children on a fixed cadence (thirty seconds) and works
them until the duration is up.
```rust
ctx.twap(TwapRequest::new(market, outcome, Side::Buy, size, duration_nanos));
```
Modelling it as one large market order gets the answer wrong in the
direction that flatters. A size worth slicing is a size that moves the book,
and the whole reason to slice is that it does — so the children here are
ordinary market orders crossing through the same matching path, and a TWAP
into a thin book suffers exactly the slippage that book implies. The last
slice carries the rounding, so the schedule works exactly the size it was
given rather than quietly working less and reporting a better average.
### Fees that fall with volume
`TieredFees` prices on rolling traded notional. Every serious venue does
this and most simulators do not, which decides whether a market-making
strategy has a positive expectancy at all: at the top of a real schedule the
maker fee is *negative*.
A fill is priced at the tier reached before it, and volume ages out of a
trailing window (`hyperliquid::FEE_VOLUME_WINDOW_NANOS` is fourteen days).
The schedule is supplied rather than baked in -- venues republish theirs, and
a stale table compiled into a backtester is worse than none because it looks
authoritative.
### Margin
`PerInstrumentMargin` grants leverage per coin, which is how the venue
grants it: forty times on the majors and three on the long tail.
`hyperliquid::margin_from_meta` builds one from the universe, falling back
for an unlisted coin to the *tightest* leverage in it rather than the
loosest.
Two policies that both default to the previous behaviour:
- `LiquidationPolicy::Partial` closes positions, largest maintenance
requirement first, only until the account is above maintenance again. A
venue closes what it must, not what it can, and the two produce materially
different results from the same data.
- `EngineBuilder::isolate(instrument)` margins one instrument on its own
collateral. The bucket is sized at the position's entry and stays there,
so it does not top itself up from cross cash as the trade moves against
you -- an isolated position can lose exactly its bucket, and a cross
account in profit will not rescue it.
## Kalshi data
`h5i-db-venues::kalshi` converts Kalshi market metadata, REST order-book
snapshots, websocket snapshots and deltas, trades, and candlesticks into the
canonical tables above. It normalises NO bids into YES asks, so strategies see
one binary-contract book. The stateful websocket decoder enforces sequence
numbers, emits a `Gap` when one is missing, and refuses further deltas until a
new snapshot arrives.
An exact queue-aware Kalshi backtest requires data captured prospectively from
the authenticated `orderbook_delta` websocket:
1. use one decoder per market ticker;
2. record local receipt time separately from exchange event time;
3. persist the initial snapshot and every ordered delta;
4. on a gap, persist the gap, resubscribe or request a snapshot, and do not
treat the stale interval as continuous coverage.
Kalshi's historical API supplies trades and minute-or-coarser candlesticks, not
historical L2 deltas. Those records support trade-driven or bar research, but
cannot reconstruct queue position and must not be presented as exact L2
replay. REST snapshots likewise describe only the instant at which they were
requested.
Two third-party sources now carry historical Kalshi order books, so this
research no longer has to wait for a capture to accumulate.
`h5i_db.venues.predexon_book_from_snapshots` reads the one to prefer for
anything after January 2026: full book snapshots rather than accumulated
changes, so a lost record costs one sample instead of corrupting every level
after it, and finalized daily. Read its report before trusting a window. It
measures the sampling cadence, which on a liquid market runs to a median of a
few seconds with occasional holes of over an hour, and a strategy that reads
one of those holes as continuous will fill at prices nobody quoted. Its free
endpoint also rounds prices to whole cents, which is a real loss at the touch
now that Kalshi quotes sub-cent.
`h5i_db.venues.KALSHI_PMXT_LAYOUT` reads the other, which reaches further back.
It is not a substitute for prospective capture where queue accuracy matters.
Its deltas are
signed size changes replayed into absolute levels at ingest, its snapshots
carry no venue timestamp and so seed the book rather than being replayed at
their own stamp, and it has no per-market sequence numbers, so the ingest
reports divergence against later snapshots instead of detecting a dropped
message directly. The report says how many vendor snapshots the reconstruction
reproduced exactly; treat a low number as a signal to load the neighbouring
hours rather than as a reason to trust the book less than it deserves. Coverage
on that host also lags the present by weeks, which is its own reason to keep
recording.
Use `KalshiFees` (or Python's `fee_kind="kalshi"`) with rates pinned from the
applicable series fee schedule. It implements the quadratic curve, centicent
trade-fee rounding, whole-cent cash movement, and per-order partial-fill
rounding accumulator. The adapter accepts uniform tick schedules and rejects
variable or tapered schedules rather than silently snapping them to the wrong
grid.
See Kalshi's
[order-book websocket](https://docs.kalshi.com/websockets/orderbook-updates),
[historical-data](https://docs.kalshi.com/getting_started/historical_data),
[fixed-point](https://docs.kalshi.com/getting_started/fixed_point_migration),
and [fee-rounding](https://docs.kalshi.com/getting_started/fee_rounding)
documentation before operating a recorder.
## Strategies
**Tier 1, signal replay** is the strategy as data: a list of timestamped
order intents replayed through the full matching, fee and latency path. It
covers most systematic research with no callback code and no language
boundary in the loop.
**Tier 2** is the `Strategy` trait, for path-dependent logic. Its callbacks
receive a `Context` that exposes the clock, books, positions and cash --
and nothing else. There is no route from it to a resolution.
Two orderings inside the loop are load-bearing:
- the venue sees data **before** the strategy, so a strategy cannot act on a
price the matching engine has not processed;
- strategy commands are **queued**, never executed inside the callback that
produced them, which removes reentrancy and makes latency a property of
the queue rather than of every call site.
### Tier 2 from Python
`backtest.EventStrategy` is the Python side of the same trait. Callbacks
receive mappings and return one command mapping, an iterable of them, or
`None`; the supported actions are `submit`, `amend`, `cancel`, `timer`,
`mint`, `redeem`, `convert`, `twap` and `forecast`.
```python
from h5i_db import backtest
class Momentum(backtest.EventStrategy):
def on_start(self, context):
self.last = {}
def on_event(self, context, event):
prev = self.last.get(event["instrument_id"])
self.last[event["instrument_id"]] = event.get("price")
if prev and event["price"] > prev * 1.01:
return {"action": "submit", "instrument_id": event["instrument_id"],
"outcome": 0, "side": "buy", "quantity": 10.0,
"kind": "market"}
result = backtest.run_strategy(db, "momentum-001", Momentum(),
starting_cash=10_000.0,
data=backtest.DataConfig(...))
```
`context` is optional, and it is the parameter's **name** (`context` or
`ctx`) that asks for it. Declare `def on_event(self, event)` and no context
is built, which saves an object and a snapshot of every open position on
every event; declare `def on_event(self, context, event)` and it is. Both
forms work everywhere — the shorter one is simply not charged for what it
does not ask for. Any further parameter needs a default, and a signature the
engine cannot call is refused when the run starts rather than misbound during
it. Callbacks left at the base class's do-nothing versions are never called.
Declarative signals or command tables stay preferable when callbacks are not
needed: they avoid crossing the Python boundary on every event, and only they
have a complete identity in the typed config, which is what makes a trial
reusable (see [the trial ledger](#agent-trial-ledger)).
### Building the signals table
A signals table is ordinary data, so anything that produces the right columns
works. The builders exist so the common shapes do not have to be spelled out
row by row:
```python
backtest.create_signal_table(db, "signals") # or create_command_table(db, "commands")
db.append("signals", backtest.signal_table(rows)) # from dicts
db.append("commands", backtest.command_table(rows)) # the richer stream
db.append("signals", backtest.from_signals( # from boolean arrays
timestamps, instrument_id="AAPL", entries=entries, exits=exits, size=10.0))
db.append("signals", backtest.target_positions( # from a target series
timestamps, targets, instrument_id="AAPL"))
```
`backtest.SIGNAL_SCHEMA` is `ts`, `instrument_id`, `outcome`, `side`,
`quantity`, `kind`, `limit_price`, `time_in_force`, `tag`, `reduce_only`,
`post_only`; `COMMAND_SCHEMA` adds `action` and `client_order_id` for the
richer command stream (`amend`, `cancel`, `twap`, set operations).
`from_signals` takes two aligned boolean arrays over one timeline and stamps
exits `reduce_only`, so a replay cannot invent short exposure the signals
never asked for. It never shifts a signal or assumes bar-close execution: the
timestamps you pass must already be the instants the features were available.
`target_positions` is the other common shape — a desired position per
timestamp — and emits the minimum sequence of orders that reaches it, so a
flat target produces nothing.
### Stamp a signal after the quote it came from
Market data is merged on the total order `(ts_init, stream priority, stream,
arrival)`, and the priorities are explicit: gaps before corporate actions
before snapshots before deltas before prints. Signals are not part of that
order. An intent is released on the first record whose timestamp reaches it,
which puts one detail in the analyst's hands.
A signal timestamped *exactly* on a book instant is released while the venue is
partway through that instant. Whether its own instrument has been updated yet
depends on where that instrument falls among the records sharing the timestamp,
so with one market the signal sees the new book and with sixty it may match
against the previous one. The replay stays deterministic, because the merge
order is total and the same data always produces the same answer; what varies
is which book a same-timestamp signal meets, and that varies with the shape of
the panel rather than with anything the strategy did.
Stamp the intent strictly after the quote it was decided from and the question
disappears:
```python
signals = backtest.signal_table([
{"ts": decision_ts + datetime.timedelta(microseconds=1),
"instrument_id": market, "outcome": 0, "side": "buy", "quantity": 20.0},
])
```
The order then fills at exactly the bid or ask carried by the decision
snapshot, stamped at the next event. That is also the honest reading of a
backtest: you transacted at a price that was knowable when you chose to trade.
Tier 2 strategies are unaffected, because a callback is already invoked after
the venue has processed the record it is reacting to.
## Agent trial ledger
`backtest.execute(db, config)` treats every pinned, declarative
`BacktestConfig` as one score-producing trial. Its `trial_digest` hashes every
replay input but excludes `run_id` and descriptive `metadata`. Re-submitting
the same semantic config returns the recorded result with
`result["cached"] == True`; it does not create another fork or increase
`backtest.trial_count(db)`. Lookup plus creation is serialized per database,
including across local agent processes.
Unpinned configs and Python callback strategies still create normal recorded
runs, but are not reused: current table heads and callback implementations do
not have a complete identity in the typed config. Use a snapshot, version, or
as-of pin and a signals/commands strategy when retry-safe deduplication matters.
The `h5i-db-ui` experiments view is an attention router rather than a
leaderboard wrapper. Its default tab orders trials as:
1. human decision required;
2. failed or warned;
3. finished and unseen;
4. running;
5. seen.
The experiment sidebar rolls up the maximum child priority and counts unseen
warnings. Merely scanning a list does not mark work reviewed:
`StudyResult.open_trial(n)` marks in-process state, while `h5i-db-ui` marks a
trial seen only when its detail is opened and persists that review state in
the browser. The leaderboard remains a separate tab.
The same ordering is available without the UI, so a script can triage a
sweep the way the tab does:
```python
result.attention() # trials in the order a reviewer should visit them
result.attention_state # the group's own state, rolled up
result.warning_badge # how many finished trials carry unseen warnings
result.leaderboard("realized_pnl")
```
Underneath, `backtest.attention_for_trial(row)` turns one trial row into a
`TrialAttention` (`state`, `seen`, `warnings`, `needs_decision`, `priority`)
and `backtest.rollup_attention(items)` takes the maximum. `AttentionState` is
the five-value ordering above: `NEEDS_DECISION`, `FAILED_WARNED`,
`FINISHED_UNSEEN`, `RUNNING`, `SEEN`.
## What is not here
No live order routing, no brokerage adapters, no portfolio optimisation, no
plotting API. The boundary is simulation and evaluation; see
`ROADMAP_QUANT.md` §11 for the full list and the reasoning.
## Bringing your own data
`h5i_db.venues` turns vendor archives already on disk into the canonical
tables. It does not fetch: downloading belongs in a script, where credentials
and rate limits belong, and this layer is the part that must be testable
offline and byte-reproducible.
```python
from h5i_db import venues
specs = venues.polymarket_markets_from_json(payloads) # slug -> outcomes, tokens
venues.write_markets(db, specs) # instruments, resolutions
report = venues.ingest_archive( # book_deltas, trades
db,
files=venues.discover("/mnt/pmxt"),
markets=specs,
layout=venues.PMXT_LAYOUT,
window=(start_ns, end_ns),
)
report.coverage, report.gaps, report.replayed
```
The same three steps run from a shell, with market definitions travelling as a
JSON file rather than as flags:
```bash
python -m h5i_db.venues markets market.db specs.json
python -m h5i_db.venues ingest market.db specs.json --root /mnt/pmxt \
--start-ns 1777000000000000000 --end-ns 1777003600000000000 --min-coverage 0.95
python -m h5i_db.venues inspect market.db
```
Four properties are worth knowing before pointing it at a mirror.
**Re-running is a replay, not a duplicate.** Every commit is keyed by the hash
of the normalised rows it carries, so identical inputs produce identical keys
and h5i-db recognises them. An interrupted backfill is safe to restart, and two
sources serving the same hour converge on one commit rather than two.
**Requested and loaded windows stay separate facts.** `report.coverage` is the
loaded span over the requested one, and it is `None` when no window was asked
for, because a ratio against an unbounded request would be meaningless.
`--min-coverage` exits non-zero rather than letting a short load pass quietly.
**A vendor dialect is data, not a code path.** `ArchiveLayout` carries the
column names, event vocabulary, timestamp unit and level shape.
`PMXT_LAYOUT` and `TELONEX_LAYOUT` are literals of that type, and a third
vendor is a new literal. An event type present in the file but absent from the
layout is counted and reported, never guessed at.
**Outcome order is positional and refused when ambiguous.** A market spec pairs
`outcome_labels` with `tokens` by index, a token claimed by two markets is an
error, and a resolution with no observability instant is an error too, since
settlement is gated on when the result became knowable.
## Searching without fooling yourself
`backtest.study` runs a `GridSearch` by default. Three additions make the
search shape explicit when it matters. (`backtest.BacktestStudy` is the same
thing as an object, for a study you want to build once and `run(db)` later.)
```python
from h5i_db import backtest
result = backtest.study(
db,
study_id="threshold",
base=config,
parameters={"execution.fee_rate": backtest.Range(0.0, 0.08)},
search=backtest.RandomSearch(trials=40, seed=7),
validation=backtest.WalkForward.of(fold_one, fold_two, fold_three),
selection=backtest.TopK(k=5, metric="final_cash"),
)
result.ranked() # holdout median, train score as the tie-break
result.selected # only the trials that reached the holdout
```
`WalkForward` scores a candidate on several folds and reports the median, so one
lucky window cannot carry it; per-fold columns are `fold{i}_train_*` and
`fold{i}_holdout_*`, with `train_median_*` and `holdout_median_*` alongside. A
single `ValidationWindows` keeps the flat `train_*` / `holdout_*` names.
`TopK` makes the holdout a second stage: candidates are ranked on train, only
`k` are run out of sample, and nothing else ever touches it. A holdout every
candidate touched is a second training set with a different name.
`RandomSearch` beats a grid when the space is wide and most axes do not matter.
`TPESearch` needs the optional `optuna` extra and runs sequentially, because
each point is proposed from the results so far. Duplicate draws are kept rather
than resampled: dropping them would change the trial count that the
deflated-Sharpe correction in `quant.deflated_sharpe` depends on.
Subprocess isolation per trial is deliberately absent. A study refuses callback
strategies, so a trial is a declarative config that cannot crash the driver;
the isolation the reference stacks need is buying safety this API already has.
## Comparing many runs at once
A sweep produces one fork per trial, and opening twenty tearsheets is not
comparing them. `quant.basket_report` assembles one document from stored tables
only, with no re-simulation:
```python
from h5i_db import quant
quant.basket_report(
db,
{"th50": result_a, "th60": result_b},
path="basket.html",
panels=quant.PORTFOLIO_PANELS + ("equity", "price"),
snapshot="panel-v1",
)
```
Portfolio panels (`total_equity`, `total_drawdown`, `total_rolling_sharpe`,
`total_cash_equity`, `periodic_pnl`, `leaderboard`) are safe at any size.
Per-run panels draw one series each and are dropped, with a reason recorded in
`report.skipped`, once the basket exceeds `per_run_limit`: silently thinning
lines to fit would misrepresent the basket. The `price` panel puts fill markers
on the book the fills actually met, read at the same pin the runs used.
The charts are inline SVG with no external requests, because a report that needs
a plotting library installed to be *read* is not a report.
`brier_advantage` is the one panel that needs an input the report cannot
derive: your strategy's own probability. `market_brier - strategy_brier` says
whether the forecast beat the price it paid, which is a comparison an equity
curve cannot make and which cannot be inflated by sizing.
## A pack of strategies, as data
`backtest.strategies` ships the standard rules as signal *generators*: each
takes a quote panel and returns a signals table, so the trial ledger can
identify it and `verify()` can reproduce it.
```python
panel = backtest.quote_panel(db, snapshot="panel-v1")
plan = backtest.strategies.late_favorite_hold(panel, min_price=0.75)
db.append("signals", plan.signals)
```
`quote_panel` stops at `expiration_ns`, so no rule can read the resolution jump
as a price move. Every generator stamps its orders a microsecond after the quote
they were decided from. `STRATEGIES` maps name to generator for sweeping the
pack itself; `pair_arbitrage` is outside it because it reads both outcomes from
the database rather than one side's panel.
A generator returns a `SignalPlan`: the `signals` table plus the `strategy`
name and `parameters` that produced it, and `to_metadata()` folds those into
a config's `metadata` so a run records what generated its intents.
## Replaying an account's ledger
The strictest realism question available: given the trades an account actually
took, does the engine reproduce the same portfolio? Usually not, and that is the
point. `venues.commands_from_ledger` compiles a ledger into *intent* rather than
into fills, so the historical book accepts or refuses each order on its merits:
limit orders at the ledger's own price, immediate-or-cancel, and sells as
`reduce_only` so a replay cannot invent short exposure the ledger never showed.
```python
commands = venues.commands_from_ledger(rows, specs)
db.append("commands", commands)
result = backtest.execute(db, config) # data=DataConfig(commands="commands", ...)
venues.compare_to_ledger(result, typed_rows) # per-market reconciliation
```
A forced-fill simulator would reproduce the ledger by construction and test
nothing. The comparison reports per-market shortfalls rather than one
pass/fail, because *where* the book refused is the finding.
---
# Overview (https://db.h5i.dev/api/)
The <code>h5i_db</code> package is an ergonomic wrapper over the
native Rust engine. All tabular data crosses the boundary as Arrow, so it plugs
directly into pyarrow, pandas, and Polars.
<div class="doc-divider"></div>
```console
$ pip install h5i-db
```
The only required dependency is `pyarrow >= 14`. `to_pandas()` /
`to_polars()` activate when pandas / Polars are installed.
## The five-minute tour
```python
import pyarrow as pa
import h5i_db
db = h5i_db.Database("market.db", create=True)
schema = pa.schema([
pa.field("ts", pa.timestamp("us", tz="UTC"), nullable=False),
pa.field("symbol", pa.string()),
pa.field("price", pa.float64()),
pa.field("size", pa.int64()),
])
db.create_table("trades", schema, time_column="ts")
db.append("trades", table) # pyarrow Table / RecordBatch(es)
df = db.sql("SELECT * FROM trades").to_pandas()
old = db.read("trades", version=3) # time travel
plan = db.plan_delete_range("trades", t0_us, t1_us) # previewable mutation
plan.apply() # or plan.discard()
db.close() # or use `with h5i_db.Database(...) as db:`
```
## The pieces
## Data in, data out
Everything tabular is Arrow:
- **In**: `write()` / `append()` accept a `pyarrow.Table`, a `RecordBatch`,
or a sequence of batches (`TableLike`). Coming from pandas or Polars:
`pa.Table.from_pandas(df)` / `pl_df.to_arrow()`.
- **Out**: `read()` returns a `pyarrow.Table`; `sql()` returns a
[`QueryResult`](results-and-plans.html#queryresult) with `.to_arrow()`,
`.to_pandas()`, `.to_polars()`.
Because the interchange is Arrow IPC, there is no per-row conversion cost and
no type fidelity loss.
## Error handling
Every failure raises a subclass of `h5i_db.H5iError` carrying the same
structured envelope the CLI prints: `.code` (stable string), `.hint` (what to
try next), `.retryable` (whether a retry can help).
```python
try:
db.sql("SELECT * FROM h5i('trades')", timeout=30, max_rows=1_000_000)
except h5i_db.TimeoutError:
... # raise the timeout or narrow the query
except h5i_db.LimitError as e:
print(e.code) # "limit_exceeded"
except h5i_db.ConflictError:
... # retryable: another writer won the race
```
See [Exceptions](exceptions.html) for the full hierarchy and code table.
## The subpackages
Four public subpackages sit on top of `Database`. Each has its own manual
page, because each is a workflow rather than a class:
| Import | What it does | Guide |
|---|---|---|
| `h5i_db.backtest` | Typed run configs, signal and command tables, Python callback strategies, studies and stored results | [Backtesting](../manual/backtest.html) |
| `h5i_db.quant` | Factor panels, performance statistics, selection-bias corrections, purged CV, reports | [Quant workflows](../manual/quant.html) |
| `h5i_db.venues` | Vendor archives, bars, trades and corporate actions into canonical tables | [Data on-ramp](../manual/data-onramp.html) |
| `h5i_db.capture` | Recording a venue websocket to the same format the archive readers consume | [Data on-ramp](../manual/data-onramp.html#recording-a-live-feed) |
`h5i_db.backtest` and `h5i_db.venues` import with the package;
`h5i_db.quant` and `h5i_db.capture` are imported explicitly, and `capture`
needs the `h5i-db[capture]` extra.
## Versioning note
`h5i_db.__version__` reports the installed engine version. The pure-Python
wrapper (`Database`, `QueryResult`, `MutationPlan`) sits on the private
native module `h5i_db._native`; treat anything not exported from `h5i_db` or
one of the subpackages above as internal.
---
# Database (https://db.h5i.dev/api/database/)
An h5i-db database directory: the top-level handle everything hangs off. A
database is a plain directory on disk; there is no server. The handle is a
context manager, so the idiomatic form is:
```python
import h5i_db
with h5i_db.Database("market.db", create=True) as db:
...
```
Many methods return plain `dict`s decoded from the engine; commit results
carry keys like `version`, `rows`, `bytes`, `segments`, and are made to be
logged.
## Constructor
### `h5i_db.Database`
```python
Database(path, create=False, read_only=False)
```
Open (or create) a database directory.
**Parameters**
`path` (`str`)
: Filesystem path to the database directory.
`create` (`bool`, default `False`)
: Open-or-create: make the directory if it does not exist.
`read_only` (`bool`, default `False`)
: Reject every write at the handle level; write calls raise
[`PolicyError`](exceptions.html).
**Raises**
`NotFoundError`
: The directory does not exist and `create` is `False`.
## Lifecycle
### `Database.close`
```python
close() -> None
```
Release the native handle. Idempotent, and also called by `__exit__`.
In-flight operations on other threads finish normally; later calls on this
object raise `H5iError` with `code == "closed"`.
### `Database.closed`
```python
closed -> bool
```
Whether the handle has been closed.
### `Database.path`
```python
path -> str
```
The directory this handle was opened on.
## Tables
### `Database.create_table`
```python
create_table(name, schema, time_column=None, sort_key=None) -> dict
```
Create a table from an Arrow schema.
**Parameters**
`name` (`str`)
: Table name, unique within the database.
`schema` (`pyarrow.Schema`)
: The Arrow schema. Field order is preserved.
`time_column` (`str`, optional)
: The time-axis column, strongly recommended for time-series tables. It
enables segment pruning, ASOF joins, range plans, and `tail`, and is
forced non-nullable.
`sort_key` (`Iterable[str]`, optional)
: Columns the table is sorted by on disk. Defaults to `[time_column]`.
**Returns**
`dict` with creation metadata (table id, schema revision).
```python
db.create_table("trades", schema, time_column="ts", sort_key=["ts", "symbol"])
```
### `Database.tables`
```python
tables() -> list[str]
```
Names of all tables in the database.
### `Database.schema`
```python
schema(name, version=None, as_of=None, snapshot=None) -> pyarrow.Schema
```
Schema of a table at a read point (latest by default).
**Parameters**
`name` (`str`)
: Table name.
`version` (`int`, optional)
: Read the schema as of this exact version.
`as_of` (`str`, optional)
: RFC3339 timestamp; the schema as of the latest commit at or before it.
`snapshot` (`str`, optional)
: Named snapshot to resolve the version from.
!!! note "One read point"
Pass at most one of `version` / `as_of` / `snapshot`; more than one raises
[`InvalidInputError`](exceptions.html). This rule holds for every
read-point method below.
### `Database.versions`
```python
versions(name) -> list[dict]
```
Committed versions, oldest first: one dict per version with the version
number, operation, commit time, and row / byte / segment counts, plus any
`note`.
### `Database.drop_table`
```python
drop_table(name) -> None
```
Permanently drop the table and its data.
**Raises**
`ConflictError`
: A snapshot pins the table; delete the snapshot first.
## Writing
Every write is one atomic, durable commit that produces a new version.
### `Database.append`
```python
append(name, data, *, expected_version=None, note=None) -> dict
```
Strict ordered append.
**Parameters**
`name` (`str`)
: Table name.
`data` (`TableLike`)
: A `pyarrow.Table`, `RecordBatch`, or sequence of batches. Rows must
respect the table's sort order.
`expected_version` (`int`, optional)
: Optimistic guard: commit only if the head is exactly this version, else
[`ConflictError`](exceptions.html). Use it when the append depends on
what you last read.
`note` (`str`, optional)
: Free-text note recorded in the version manifest.
**Returns**
`dict` with commit metadata (`version`, `rows`, `bytes`, `segments`).
**Raises**
`InvalidInputError`
: `sort_order_violation` if rows are out of order, or `schema_mismatch`.
`ConflictError`
: Another writer moved the head; retryable. Pure appends are retried
internally (up to 5 times) before this surfaces.
### `Database.write`
```python
write(name, data, *, expected_version=None, note=None) -> dict
```
Replace the table's contents in one commit. It is a restatement rather than an
overwrite: the previous state stays readable as its own version. Parameters match
[`append`](#databaseappend).
### `Database.restore`
```python
restore(name, version) -> dict
```
Make a historical version current by committing a new version with its
contents. History only moves forward, and nothing is erased.
**Parameters**
`name` (`str`)
: Table name.
`version` (`int`)
: The version to restore.
## Reading & SQL
### `Database.sql`
```python
sql(query, memory_limit=None, timeout=None, max_rows=None) -> QueryResult
```
Run SQL: full DataFusion plus the [h5i extensions](../manual/sql.html).
Returns a [`QueryResult`](results-and-plans.html#queryresult).
**Parameters**
`query` (`str`)
: The SQL text.
`memory_limit` (`int`, optional)
: Query memory budget in **bytes**; enables disk spilling under pressure.
`timeout` (`float`, optional)
: Deadline in seconds. On expiry, raises
[`TimeoutError`](exceptions.html) and cancels execution.
`max_rows` (`int`, optional)
: Raise [`LimitError`](exceptions.html) as soon as the result exceeds this;
execution stops early rather than truncating silently.
**Returns**
`QueryResult`, with `.to_arrow()`, `.to_pandas()`, `.to_polars()`, `len()`.
```python
df = db.sql(
"SELECT * FROM h5i('trades', 42)", timeout=30, max_rows=1_000_000
).to_pandas()
```
### `Database.read`
```python
read(name, version=None, as_of=None, snapshot=None, columns=None,
time_start=None, time_end=None, limit=None, timeout=None) -> pyarrow.Table
```
Direct scan of one table version, with no SQL layer and minimal overhead.
**Parameters**
`name` (`str`)
: Table name.
`version` / `as_of` / `snapshot`
: Read point (latest by default); at most one. `as_of` is an RFC3339 string.
`columns` (`list[str]`, optional)
: Project to these columns.
`time_start` (`int`, optional)
: Inclusive lower time bound, in **raw time units** (µs for `timestamp[us]`).
Prunes segments before I/O.
`time_end` (`int`, optional)
: Exclusive upper time bound, same units.
`limit` (`int`, optional)
: Stop after this many rows.
`timeout` (`float`, optional)
: Deadline in seconds.
**Returns**
`pyarrow.Table`
```python
window = db.read("trades", columns=["ts", "price"],
time_start=t0_us, time_end=t1_us)
```
### `Database.arrival_delta`
```python
arrival_delta(query, version=None, as_of=None, snapshot=None,
tolerance=None) -> dict
```
Look-ahead-bias diagnostic (the Python surface of the CLI
[`arrival-delta`](../manual/cli.html#h5i-db-arrival-delta)). Runs `query` twice,
against the current head (*leaking*: every commit, including rows that only
became available after the decision instant) and against a decision read point
(*non-leaking*), and returns the delta between the two results.
**Parameters**
`query` (`str`)
: The SQL to evaluate under both read points.
`version` / `as_of` / `snapshot`
: The decision point; **exactly one is required**. `as_of` is an RFC3339
string matched by commit *availability* time.
`tolerance` (`float`, optional)
: Per-cell numeric noise floor below which a difference is ignored
(default `1e-9`).
**Returns**
`dict` with the arrival-delta report: `changed`, per-column
`head → asof (delta)`, `max_abs_delta`, `row_count_differs`, and
`withheld_versions` (per table, the head-vs-as-of version gap).
**Raises**
`InvalidInputError`
: No decision point was given, or more than one.
```python
report = db.arrival_delta(
"SELECT symbol, vwap(price, size) AS vwap FROM trades GROUP BY symbol",
as_of="2026-07-01T16:00:00Z",
)
if report["changed"] and not report["vacuous"]:
print("moved by:", report["max_abs_delta"])
```
!!! note "Scope: a measurement, not a verdict"
A non-zero delta means late-arriving or restated rows moved the answer.
That is one shape of look-ahead; a signal reading its own bar leaves this
unmoved, so no value of the delta clears a query. Read `vacuous` first:
when both read points resolve to the same version, which is the normal
state of a bulk-loaded database, the zero is arithmetic rather than
evidence. `notes` carries both caveats on every run.
## Snapshots
### `Database.snapshot`
```python
snapshot(name, tables=None, note=None) -> dict
```
Pin current table versions under a name. Address it later from SQL as
`h5i('t', 'name')` or `read(snapshot=…)`.
**Parameters**
`name` (`str`)
: Snapshot name.
`tables` (`list[str]`, optional)
: Tables to pin. Defaults to **all** tables.
`note` (`str`, optional)
: Free-text note.
## Forks
A fork is a writable workspace over a pinned view of every table: one small
metadata object, no data copied, so one dataset can back many parallel lines
of work. See [Forks](../manual/concepts.html#forks) for the model and
[`h5i-db fork`](../manual/cli.html#h5i-db-fork) for the command-line
equivalent.
```python
db.create_fork("agent-01", meta={"hypothesis": "momentum"})
work = db.fork("agent-01") # a Database handle scoped to the fork
work.append("features", batch) # copy-on-write; the base never moves
db.fork_diff("agent-01") # what it changed, from manifests alone
db.promote("agent-01", "features") # land one table on the base
db.drop_fork("agent-01")
```
### `Database.create_fork`
```python
create_fork(name, note=None, as_of=None, meta=None) -> dict
```
Create a fork pinning every table at its current version.
**Parameters**
`as_of` (`str` | `int` | `datetime`, optional)
: Pin each table at its last version committed at or before this instant,
giving a workspace over a frozen past; tables that did not exist then are
not pinned. Strings and datetimes carry microsecond resolution while
commits are stamped in nanoseconds, so pass an int of nanoseconds when
you need to name one exact commit rather than a moment.
`meta` (`dict`, optional)
: Carried verbatim and never interpreted. Use it to tie a fork back to the
run or hypothesis that produced it; the review UI surfaces it.
### `Database.create_forks` / `Database.fork_many`
```python
create_forks(names, note=None, as_of=None, meta=None) -> list[dict]
fork_many(prefix, count, note=None, as_of=None, meta=None) -> list[dict]
```
Create many forks over a **single** resolution of the base. Every fork of one
base at one instant pins the same versions, so a wide fanout costs one pass
over the catalog rather than one per branch — which is what makes a few
hundred short-lived branches reasonable to create and throw away.
```python
db.create_forks([f"trial-{i}" for i in range(500)])
db.fork_many("trial", 500) # trial-0000 … trial-0499
```
Names must be distinct and none may already exist; both are checked before
anything is written, so the usual mistakes leave no partial fanout behind.
`fork_many`'s zero-padded suffix keeps name order equal to creation order,
which is the order every listing sorts by.
### `Database.fork`
```python
fork(name) -> Database
```
A `Database` handle scoped to the fork. Table lookups resolve to the fork's
own tables first and fall back to its pinned view of the base, so existing
code runs unchanged inside a fork; writes to a base table's name
transparently copy-on-write into the fork, and the base is never modified.
`Database.fork_name` is the fork a handle is scoped to, or `None` on a base
handle.
### `Database.forks` / `Database.fork_names` / `Database.fork_info`
```python
forks() -> list[dict]
fork_names() -> list[str]
fork_info(name) -> dict
```
`forks()` reports every fork with its lineage, what it owns, and what it
holds back from reclamation (`bytes_pinned`). `fork_names()` is one read of
the fork index, where `forks()` reads a manifest per table per fork to report
sizes — use it when you only need the names.
### `Database.fork_diff`
```python
fork_diff(name, table=None) -> dict
```
What a fork changed, computed from manifests alone: no segment is read.
### `Database.fork_scan`
```python
fork_scan(name, forks=None) -> LazyFrame
```
A lazy query reading one table **across forks at once**, with a `__fork`
column naming the source. Forks share their base's segments, so a segment
several forks can see is read once.
```python
db.fork_scan("bt_equity").group_by("__fork").agg(...).collect()
db.fork_scan("trades", ["agent-01", "agent-02"]).sql()
```
Schemas are not coerced; use `fork_diff` to see how they differ.
### `Database.promote`
```python
promote(fork, table) -> dict
```
Promote one of a fork's tables into the base. Compare-and-swap against the
version the fork was created from: the first promote wins and a later one
raises rather than merging, because the work was computed against a base that
no longer exists. The conflict unit is the whole table.
### `Database.drop_fork` / `Database.drop_forks`
```python
drop_fork(name) -> int
drop_forks(names) -> int
```
Delete forks and everything they own, releasing their pins; both return the
table count. `drop_forks` takes the batch under one metadata lock, which is
the shape mass pruning wants. A name that does not exist stops the batch
rather than being skipped, so a typo in a list you believe you deleted is
reported instead of swallowed; forks dropped before the failure stay dropped,
and re-running with what `fork_names()` still reports completes the job.
## Mutation plans
The previewable plan/apply flow. These return a
[`MutationPlan`](results-and-plans.html#mutationplan): the staged segments
already exist on disk, and publishing is a metadata-only swap.
### `Database.plan_replace_range`
```python
plan_replace_range(name, start, end, data=None, note=None) -> MutationPlan
```
Stage a previewable replacement of the half-open time range `[start, end)`.
**Parameters**
`name` (`str`)
: Table name.
`start` (`int`)
: Inclusive range start, in **raw time units** (µs for `timestamp[us]`).
`end` (`int`)
: Exclusive range end, same units.
`data` (`TableLike`, optional)
: Replacement rows. Omit (or `None`) to **delete** the range.
`note` (`str`, optional)
: Free-text note carried onto the resulting version.
**Returns**
`MutationPlan`; inspect `.summary` / `.before_sample`, then `.apply()`.
### `Database.plan_delete_range`
```python
plan_delete_range(name, start, end, note=None) -> MutationPlan
```
Sugar for `plan_replace_range(name, start, end, None, note)`, staging a
range deletion.
### `Database.list_plans`
```python
list_plans(name) -> list[MutationPlan]
```
Pending (not yet applied or discarded) plans for a table.
## Policy
### `Database.policy`
```python
policy() -> dict
```
The [mutation policy](../manual/concepts.html#the-mutation-policy) as a dict
of boolean flags: `direct_append`, `direct_write`, `direct_replace`,
`direct_delete`, `direct_restore`, `direct_compact`.
### `Database.set_policy`
```python
set_policy(policy=None, **flags) -> dict
```
Update the mutation policy; unspecified flags keep their value. The merge is
atomic (read-modify-write under the metadata lock).
**Parameters**
`policy` (`dict`, optional)
: Flags to set, as a dict.
`**flags` (`bool`)
: Flags to set, as keyword arguments, e.g. `db.set_policy(direct_delete=False)`.
**Returns**
`dict` holding the merged policy that was stored.
**Raises**
`InvalidInputError`
: An unknown flag name.
## Data-safety policy
Where the [mutation policy](#databasepolicy) gates *who* may write directly, a
per-table **data policy** gates *what data* may be written: typed constraints
checked fail-closed on every write and at plan time
([CLI reference](../manual/cli.html#h5i-db-data-policy)). A table with no policy
is unconstrained and pays no read-path cost.
### `Database.data_policy`
```python
data_policy(table) -> dict | None
```
The table's data-safety policy as a dict, or `None` when unset.
### `Database.set_data_policy`
```python
set_data_policy(table, policy) -> dict
```
Install (overwrite) a table's data-safety policy. Returns the stored policy.
**Parameters**
`table` (`str`)
: Table name.
`policy` (`dict`)
: A typed policy document. Predicates compose `not_null`, `compare`, and
`in_set` with `and` / `or` / `not`; each constraint's `on_fail` is
`"reject"` (fail the write) or `"warn"`.
```python
db.set_data_policy("trades", {"constraints": [
{"name": "positive_price",
"predicate": {"compare": {"column": "price", "op": "gt",
"value": {"float": 0.0}}},
"on_fail": "reject"}]})
```
**Raises**
`InvalidInputError`
: A malformed policy document, or (when a later write breaks a constraint)
a `data_policy_violation` (the write is refused before it lands).
### `Database.clear_data_policy`
```python
clear_data_policy(table) -> None
```
Remove a table's data-safety policy (writes become unconstrained).
## Maintenance
### `Database.compact`
```python
compact(name, note=None) -> dict
```
Rewrite small segments into target-sized ones as a new version. It is a
query-speed tool; old segments stay pinned by history.
### `Database.vacuum`
```python
vacuum(table=None, grace_seconds=3600, apply=False) -> dict
```
Remove unreachable objects (crashed-writer debris, discarded plans). Committed
history is never touched.
**Parameters**
`table` (`str`, optional)
: Restrict to one table. Defaults to the whole database.
`grace_seconds` (`int`, default `3600`)
: Never touch objects newer than this; keep it above your longest ingest.
`apply` (`bool`, default `False`)
: Actually delete. The default is a dry run.
**Returns**
`dict` holding the candidate (or deleted) object list.
### `Database.verify`
```python
verify(name, deep=False) -> dict
```
Structural integrity check: checksum chain and object existence.
**Parameters**
`name` (`str`)
: Table name.
`deep` (`bool`, default `False`)
: Also re-read every segment and verify content checksums.
**Returns**
`dict` report; problems are listed in it rather than raised.
---
# DataFrame builder (https://db.h5i.dev/api/dataframe/)
`db.table(...)` starts a **lazy** query you build up with methods instead of
writing a SQL string. Nothing runs until a terminal method like `.collect()`.
```python
from h5i_db import col
(db.table("trades", as_of="2026-07-01T00:00:00Z")
.filter(col("symbol").is_in(["AAPL", "MSFT"]))
.group_by("symbol")
.agg(col("price").mean().alias("px"))
.collect())
```
The builder is a **compiler, not a second engine**. Every verb lowers to SQL
run through [`Database.sql()`](database.html#databasesql), so a built query
sees the same session, the same [table functions](../manual/sql.html) and the
same version pins as the equivalent string. `.sql()` shows exactly what it
produced:
```sql
SELECT "symbol", avg("price") AS "px"
FROM h5i('trades', '2026-07-01T00:00:00Z')
WHERE "symbol" IN ('AAPL', 'MSFT')
GROUP BY "symbol"
```
!!! note "When to reach for it"
For a query you write once, SQL is usually shorter and clearer. The
builder pays off when queries are **generated** (a factor library sweeping
windows and columns in a loop), where f-string SQL means quoting bugs, and
when you want a partially-built pipeline you can reuse and extend. Neither
surface is second-class; `.sql()` is the door between them.
## Reading a table
### `Database.table`
```python
table(name, version=None, as_of=None, snapshot=None) -> LazyFrame
```
Start a query. Pass at most one read point; passing two raises
`InvalidInputError`.
| Call | Reads |
|---|---|
| `db.table("trades")` | The bare table name, snapshot-bound for the query, so two references to it inside one query always agree |
| `db.table("trades", version=42)` | `h5i('trades', 42)` |
| `db.table("trades", as_of="2026-07-01T00:00:00Z")` | `h5i('trades', '…')`, the latest version committed at or before that instant |
| `db.table("trades", snapshot="eod-2026-07-18")` | `h5i('trades', 'eod-…')` |
Because pins lower to `h5i()`, a pinned builder query is bound at the source
exactly like hand-written SQL, including under a research-mode pin.
## Verbs
Every verb returns a **new** frame, so a partial pipeline is safe to reuse as
a base for several queries.
| Verb | Does |
|---|---|
| `.filter(*preds)` | Keep matching rows; several predicates are ANDed |
| `.select(*exprs, **named)` | Replace the projection |
| `.with_columns(*exprs, replace=None, **named)` | Add columns, keeping the rest |
| `.group_by(*keys).agg(...)` | Aggregate; keys are projected alongside |
| `.group_by(*keys).count()` | Rows per group |
| `.sort(by, descending=False)` | Order the result |
| `.limit(n, offset=0)` / `.head(n)` | Take rows (still lazy) |
| `.unique()` | `SELECT DISTINCT` |
| `.join(other, on=…, how=…)` | Join two frames |
| `.join_asof(other, on=…, by=…)` | ASOF join via `asof_join` |
| `.pipe(fn, *args)` | `fn(frame, *args)`, for reusable helpers |
Strings are column names wherever an expression is accepted, so
`.group_by("symbol")` and `.group_by(col("symbol"))` are the same thing.
Keyword arguments name the result: `with_columns(ret=col("close") - 1)`.
`with_columns` **adds**. Naming a column that already exists is an error,
because `SELECT *` would then carry two of it. Say so explicitly to
overwrite one:
```python
db.table("trades").with_columns(price=col("price") * 2, replace="price")
```
```sql
SELECT * EXCEPT ("price"), "price" * 2 AS "price"
FROM "trades"
```
The builder never reads the schema, which is what keeps it lazy, so a
`replace` name that does not exist is caught by the engine rather than at
build time.
### Terminal methods
| Method | Returns |
|---|---|
| `.collect(memory_limit=, timeout=, max_rows=)` | [`QueryResult`](results-and-plans.html) |
| `.to_arrow()` / `.to_pandas()` / `.to_polars()` | The frame, converted |
| `.sql()` | The generated SQL, as a string |
| `.explain(analyze=False)` | `EXPLAIN` / `EXPLAIN ANALYZE` of it |
| `.schema()` | Result schema via a `LIMIT 0` run; no data read |
`.collect()` takes the same guardrails as `db.sql()`: `max_rows` raises
`LimitError` as soon as the result exceeds it, and `timeout` raises
`TimeoutError`.
## Expressions
`col(name)` references a column, `lit(value)` a constant. The name is one
identifier and is never split on `.`, so a column really named `a.b` works;
pass `col("price", relation="l")` to qualify a side of a join.
```python
from h5i_db import col, lit, when
col("price") * col("size") # arithmetic
(col("price") > 100) & (col("size") < 5) # & | ~ , not and/or/not
col("symbol").is_in(["AAPL", "MSFT"])
col("px").is_null() # and .is_not_null()
col("ts").between(t0, t1)
col("symbol").like("A%") # .not_like(), .ilike() for case-insensitive
col("size").cast("DOUBLE")
when(col("price") > 100).then(lit(1)).otherwise(lit(0))
```
!!! warning "Use `&`, `|`, `~`"
Python cannot overload `and` / `or` / `not`, so an Expr raises
`TypeError` if used as a truth value. Mind the precedence too: `&` binds
tighter than `>`, so the comparisons need parentheses.
!!! note "Operators mean what SQL means"
Expressions compile to SQL and keep SQL's semantics, not Python's. Most
visibly, `/` between two integer columns is **integer** division, so
`col("size") / 4` truncates. Cast for true division:
`col("size").cast("DOUBLE") / 4`. The rule is deliberate: the same
expression must not mean one thing here and another in `db.sql()`.
Identifiers and literals are quoted at a single site, and identifiers are
always quoted so case survives (`col("Symbol")` finds a field named
`Symbol`, which bare SQL would fold to lowercase). A value containing
`'; DROP TABLE trades; --` is a string, never syntax.
Aggregates are methods: `.sum()`, `.mean()`, `.min()`, `.max()`, `.count()`,
`.n_unique()`, `.std()`, `.var()`, `.median()`, `.quantile(q)`,
`.first(order_by=)`, `.last(order_by=)`. So are scalars: `.abs()`, `.log()`,
`.log10()`, `.exp()`, `.sqrt()`, `.sign()`, `.round(n)`, `.floor()`,
`.ceil()`, `.coalesce(...)`, `.greatest(...)`, `.least(...)`. Plus
`count_star()`, `vwap(price, size)`, `wavg(weight, value)` and
`time_bucket(interval, ts)` as functions.
A `when(...).then(...)` chain is already a complete expression (a `CASE`
with no `ELSE` yields NULL), so it can be aliased without `.otherwise()`.
`.alias(name)` renames; `.output_name()` reports the name an expression will
carry in the result, which is what `select()` and `agg()` use when nothing
was aliased.
The idiomatic OHLCV query, built:
```python
from h5i_db import col, time_bucket, vwap
(db.table("trades")
.group_by(time_bucket("5m", col("ts")).alias("bar"), "symbol")
.agg(col("price").first("ts").alias("open"),
col("price").max().alias("high"),
col("price").min().alias("low"),
col("price").last("ts").alias("close"),
col("size").sum().alias("volume"),
vwap(col("price"), col("size")).alias("vwap"))
.sort("bar"))
```
```sql
SELECT time_bucket('5m', "ts") AS "bar", "symbol",
first_value("price" ORDER BY "ts") AS "open", max("price") AS "high",
min("price") AS "low", last_value("price" ORDER BY "ts") AS "close",
sum("size") AS "volume", vwap("price", "size") AS "vwap"
FROM "trades"
GROUP BY "bar", "symbol"
ORDER BY "bar"
```
## Rolling and cross-sectional operators
Rolling methods take a `window` and an `order_by`, and optionally a
`partition_by`. The window is either a row count or a duration string
(`'30s'`, `'5m'`, `'1.5h'`, `'1d'`, `'1w'`, `'1mo'`, `'1y'`).
```python
col("close").rolling_mean(20, order_by="ts", partition_by="symbol")
```
```sql
avg("close") OVER (PARTITION BY "symbol" ORDER BY "ts"
ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
```
Unlike the [`rolling_avg` SQL sugar](../manual/sql.html), these carry a
`PARTITION BY`, so they do not mix symbols on a multi-symbol table.
| Method | Lowers to |
|---|---|
| `.rolling_mean` `.rolling_sum` `.rolling_min` `.rolling_max` | `avg` `sum` `min` `max` |
| `.rolling_std` `.rolling_var` `.rolling_count` | `stddev` `var_samp` `count` |
| `.rolling_mad` `.rolling_skew` `.rolling_kurt` | `mad` `skew` `kurt` |
| `.rolling_rank` | `ts_rank`, the percentile of the current value in the window |
| `.rolling_idxmax` `.rolling_idxmin` | `idxmax` `idxmin`, 1-based position |
| `.rolling_corr(other, …)` `.rolling_cov(other, …)` | `ts_corr` `ts_cov` |
| `.ewma(alpha, order_by, partition_by)` | `ewma` |
Cross-sectional methods rank a value against its peers *at the same instant*,
so they take the bucket to compare within:
| Method | Lowers to |
|---|---|
| `.cs_rank(partition_by)` | `cs_rank(x) OVER (PARTITION BY …)` |
| `.cs_winsorize(lower, upper, partition_by)` | `cs_winsorize(x, lo, hi) OVER (…)` |
| `.cs_demean(partition_by)` | `x - avg(x) OVER (…)`, plain SQL |
| `.cs_zscore(partition_by)` | `(x - avg(x) OVER (…)) / stddev(x) OVER (…)` |
For a raw window frame, `.over(partition_by=, order_by=, rows=, duration=)`
applies to a single aggregate, where `rows` is a trailing count or a
`(preceding, following)` pair with `None` for unbounded. SQL attaches `OVER`
to one function call, so window each part of a compound expression
separately: `col("a").sum().over(...) / count_star().over(...)`, not
`(col("a").sum() / count_star()).over(...)`, which is rejected.
## Joins
`.join()` renders both sides as subqueries aliased `l` and `r`. Those aliases
are part of the contract: reach a specific side with
`col("price", relation="l")`, or an arbitrary condition with
`predicate=sql_expr('l."a" > r."b"')`.
`how=` is `inner` (default), `left`, `right`, `full`, `cross`, `semi` or
`anti`. Keys come from `on=` (same name both sides) or `left_on=`/`right_on=`.
!!! warning "Column names are not deduplicated"
`SELECT *` over a join of two tables sharing a column name yields both
copies. Project explicitly to avoid the ambiguity.
Comparing one table at two pinned versions, the "same query across N
versions" pattern:
```python
def mean_px(version):
return (db.table("trades", version=version)
.group_by("symbol")
.agg(col("price").mean().alias("px")))
drift = (mean_px(1).join(mean_px(2), on="symbol")
.select(col("symbol", relation="l").alias("symbol"),
(col("px", relation="r") - col("px", relation="l")).alias("drift")))
```
```sql
SELECT "l"."symbol" AS "symbol", "r"."px" - "l"."px" AS "drift"
FROM (
SELECT "symbol", avg("price") AS "px"
FROM h5i('trades', 1)
GROUP BY "symbol"
) AS "l"
INNER JOIN (
SELECT "symbol", avg("price") AS "px"
FROM h5i('trades', 2)
GROUP BY "symbol"
) AS "r"
ON "l"."symbol" = "r"."symbol"
```
### `join_asof`
```python
join_asof(other, on=None, by=None, direction="backward", tolerance=None,
left_on=None, right_on=None) -> LazyFrame
```
For each left row, take the most recent right row at or before it
(`"backward"`) or the first at or after it (`"forward"`). The result is LEFT
and 1:1 with the left side. `tolerance` is an integer in the time column's
raw units; for a `timestamp[us]` column, `5000000` is five seconds.
```python
db.table("trades").join_asof(db.table("quotes"), on="ts", by="symbol",
tolerance=5_000_000)
```
```sql
SELECT *
FROM asof_join('trades', 'quotes', 'ts', 'ts', 'symbol', 'backward', 5000000)
```
!!! warning "Both sides must be plain, unpinned tables"
The `asof_join` table function takes table names and reads both at
**latest**. So `join_asof` refuses a side that already has verbs applied,
and refuses a pinned side outright rather than silently ignoring the pin.
Filter *after* the join, or for a pinned ASOF use `db.sql()` with the
[`ASOF JOIN` keyword form](../manual/sql.html#asof_join) over
session-bound names.
## How pipelines become SQL
Most pipelines compile to one flat `SELECT`. A stage that reads a column an
earlier stage *computed* gets its own level, because SQL resolves `WHERE` and
`SELECT` against the `FROM`, not against sibling projections:
```python
(db.table("bars")
.with_columns(ret=col("close") / col("open") - 1)
.filter(col("ret") > 0) # reads a computed column -> subquery
.sort("ret", descending=True)
.limit(10))
```
```sql
SELECT *
FROM (
SELECT *, "close" / "open" - 1 AS "ret"
FROM "bars"
) AS "_s1"
WHERE "ret" > 0
ORDER BY "ret" DESC
LIMIT 10
```
Filtering a *base* column instead stays flat, and independent `with_columns`
calls coalesce into one projection. Aggregation, `LIMIT` and `DISTINCT`
always close a level, since whatever follows acts on their output. So does a
projection holding a **window function or aggregate**: SQL evaluates `WHERE`
before the select list, so filtering in the same level would recompute the
window over only the surviving rows.
Because levels close, a stage can only see what the stage before it emitted.
Filtering or sorting by a column an earlier `.select()` dropped is an error
naming that column, as it would be for a DataFrame.
The generated SQL is deterministic, so it is safe to snapshot-test or diff.
## Escape hatch
`sql_expr()` embeds a raw fragment anywhere an expression is accepted. Full
SQL coverage through builder verbs is deliberately not a goal, so reach for
this rather than waiting for a method:
```python
from h5i_db import sql_expr
(db.table("trades")
.group_by("symbol")
.agg(sql_expr("approx_percentile_cont(price, 0.99)").alias("p99")))
```
Its text is inserted verbatim, so it is the one place quoting is yours to get
right; never build it from untrusted input. Because its column references are
opaque to the builder, it conservatively forces a subquery when the current
stage defines any computed name.
When a pipeline outgrows the builder, `.sql()` gives you the query to paste
into `db.sql()` and keep going from there.
---
# QueryResult & MutationPlan (https://db.h5i.dev/api/results-and-plans/)
Two small result objects returned by [`Database`](database.html): the holder a
finished query produces, and the staged handle a mutation plan produces.
## QueryResult
Returned by [`Database.sql()`](database.html#databasesql). The query has
already run; this holds its Arrow result and converts on demand, so data stays
in Arrow until you ask for a specific frame type. For a query that has *not*
run yet, see the [DataFrame builder](dataframe.html).
```python
res = db.sql("SELECT symbol, vwap(price, size) AS v FROM trades GROUP BY symbol")
res.to_pandas() # -> pandas.DataFrame
len(res) # -> row count
```
### `QueryResult.to_arrow`
```python
to_arrow() -> pyarrow.Table
```
The underlying Arrow table, types preserved exactly (timestamps keep unit and
timezone). Zero-copy.
### `QueryResult.to_pandas`
```python
to_pandas() -> pandas.DataFrame
```
Convert to a pandas DataFrame.
### `QueryResult.to_polars`
```python
to_polars() -> polars.DataFrame
```
Convert to a Polars DataFrame.
**Raises**
`ImportError`
: Polars is not installed (it is an optional dependency).
### Dunder methods
```python
len(res) # row count
repr(res) # repr of the underlying Arrow table
```
## MutationPlan
A previewable, not-yet-published mutation, returned by
[`plan_replace_range` / `plan_delete_range`](database.html#databaseplan_replace_range)
and [`list_plans`](database.html#databaselist_plans). The staged segments
already exist on disk; publishing is a metadata-only atomic swap.
```python
plan = db.plan_delete_range("trades", t0_us, t1_us, note="strip bad ticks")
plan.summary # {"rows_affected": 12481, "segments_reused": 127, …}
plan.before_sample # pyarrow.Table of rows as they are now
plan.after_sample # pyarrow.Table of rows as they would become
plan.apply() # publish (or plan.discard())
```
### Attributes
`table` (`str`)
: Table the plan targets.
`plan_id` (`str`)
: UUID, also usable from the CLI (`h5i-db plan apply …`) and the review UI.
`summary` (`dict`)
: Machine-readable impact: affected rows, segments rewritten vs. reused.
`raw` (`dict`)
: The full plan document as stored.
### `MutationPlan.before_sample`
```python
before_sample -> pyarrow.Table | None
```
A property holding a sample of the affected rows **before** the mutation, or
`None` when the plan carries no sample.
### `MutationPlan.after_sample`
```python
after_sample -> pyarrow.Table | None
```
A property holding the same rows **after** the mutation would apply.
### `MutationPlan.apply`
```python
apply() -> dict
```
Publish the plan as a new version.
**Returns**
`dict` with commit metadata for the new version.
**Raises**
`ConflictError`
: The table head moved since the plan was made. **Re-plan instead of
retrying**: the plan was computed against a base version that no longer
reflects reality.
### `MutationPlan.discard`
```python
discard() -> None
```
Drop the plan; its staged segments become vacuum candidates immediately.
Abandoned plans (neither applied nor discarded) expire after 7 days; see
[plan hygiene](../manual/operations.html#mutation-plan-hygiene).
!!! tip "Policy interaction"
With the [mutation policy](../manual/concepts.html#the-mutation-policy)
gating direct deletes/writes, the plan flow is the *only* way to mutate.
That is the point: every destructive change gets a previewed, auditable
checkpoint.
---
# Exceptions (https://db.h5i.dev/api/exceptions/)
Every h5i-db failure raises a subclass of `h5i_db.H5iError`, and every
instance carries the same structured envelope the CLI prints on stderr:
```python
try:
db.read("nope")
except h5i_db.NotFoundError as e:
e.code # "table_not_found" (stable, branchable identifier)
e.hint # what to try next (e.g. how to list tables)
e.retryable # False (retrying without a change won't help)
```
Messages are formatted `"[{code}] {message} (hint: {hint})"`.
## Hierarchy
Everything subclasses `H5iError`, which subclasses `Exception`:
| Exception | Meaning | Typical codes |
|---|---|---|
| `H5iError` | Base class for all h5i-db errors (attributes: `code`, `hint`, `retryable`) | `closed`, `query` |
| `NotFoundError` | Database, table, version or snapshot does not exist | `database_not_found`, `table_not_found`, `version_not_found`, `snapshot_not_found` |
| `ConflictError` | Concurrent-writer conflict or already-exists collision; usually retryable | `version_conflict`, `table_exists`, `database_exists`, `lock_timeout` |
| `InvalidInputError` | Bad argument, schema mismatch, sort-order violation or unsupported operation | `invalid_input`, `schema_mismatch`, `sort_order_violation`, `unsupported` |
| `PolicyError` | Operation forbidden by the mutation policy or a read-only handle | `policy_violation`, `read_only` |
| `CorruptionError` | Checksum/format verification failed; data may be damaged or written by a newer h5i-db | `corruption`, `format_too_new` |
| `LimitError` | A configured limit (memory, max_rows, segment count) was exceeded | `limit_exceeded` |
| `TimeoutError` | The operation exceeded its deadline | `timeout` |
| `StorageError` | Underlying storage / IO / encoding failure | `storage`, `io`, `arrow`, `parquet`, `metadata` |
!!! note "`h5i_db.TimeoutError` shadows the builtin"
It subclasses `H5iError`, **not** Python's builtin `TimeoutError`, so catch
`h5i_db.TimeoutError` (or `H5iError`) specifically.
## Patterns
**Branch on type for control flow, on `.code` for precision:**
```python
try:
db.append("trades", batch, expected_version=v)
except h5i_db.ConflictError:
v = db.versions("trades")[-1]["version"] # re-read, re-derive, retry
except h5i_db.InvalidInputError as e:
if e.code == "sort_order_violation":
batch = batch.sort_by("ts") # fix and retry once
else:
raise
```
**Respect `retryable`:** it encodes whether backing off can help.
`ConflictError` and `TimeoutError` generally can be retried;
`InvalidInputError` and `PolicyError` cannot, so fix the call (or get the plan
reviewed) instead.
**Surface `hint`:** hints are written to be shown to a user, a log, or an
LLM agent deciding its next step. Don't swallow them.
**Catch-all:** `except h5i_db.H5iError` catches every h5i-db failure while
letting genuine bugs (`TypeError`, …) propagate. A closed handle raises
`H5iError` with `code == "closed"`.
Discussion
Did this work in your project? Say what you used it for and what you changed. People and their agents can both post here.
Posts are public.Sign in to post
No one has posted yet. Be the first.