CLI reference#

--format is global and defaults to table.

Errors are a single JSON envelope on stderr:

{
  "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"}
  ]
}

Exit codes are stable and branchable:

Diagnostics volume is controlled with RUST_LOG (default warn).

Commands that commit a version (ingest, restore, replace-range, delete-range, compact) accept:

--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.

h5i-db ingest market.db trades day.parquet --idempotency-key load-2026-07-01

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.

$ 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

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.

Create a new database directory.

$ h5i-db init market.db

Create a table. The schema comes from --schema JSON or --like a data file (exactly one required).

$ 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

List tables with row counts and time ranges. Columns: table, version, rows, bytes, segments, time_range, time_column.

Show a table's schema and options (schema_revision, time_column, sort_key, fields with types and nullability).

Show the first rows of a table.

Rename a table: a catalog edit, no data moves.

$ h5i-db rename market.db trades trades_raw

Drop a table and its data. Refuses if the table is pinned by a snapshot, and requires --yes:

$ h5i-db drop-table market.db scratch --yes

Run SQL. The query comes from the argument, or stdin when -.

$ 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

See the SQL reference 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.

# 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.

List a table's committed versions: version, op, committed_at, rows, bytes, segments, note.

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.

$ 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

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 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.

Ingest Parquet/CSV/Arrow into a table, from a file or from stdin with -.

$ h5i-db ingest market.db trades ticks.parquet
$ curl -s https://…/ticks.csv | h5i-db ingest market.db trades - --input-format csv

Plus the 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.

Make a historical version current. History only moves forward, so restore commits a new version holding the old contents.

$ h5i-db restore market.db trades 42 --note "roll back bad load"

Replace all rows in [start, end) of the time column with the given input.

$ h5i-db replace-range market.db trades \
    --start 2026-07-01T09:30:00Z --end 2026-07-01T16:00:00Z \
    --input corrected.parquet --plan

Delete all rows in [start, end), shorthand for replace-range with no input. Same --start/--end/--plan flags.

$ h5i-db delete-range market.db trades --start 09:30… --end 09:31… --plan
{"plan_id": "5c41…", "summary": {"rows_affected": 12481, "segments_reused": 127}}

Manage previewable-mutation plans (created by --plan above).

Plans expire after 7 days; see plan hygiene.

The mutation policy decides which operations may commit without a reviewed plan.

$ 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.

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.

$ 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).

Pin table versions under a name.

Writable workspaces over a pinned base, for running several lines of work against one dataset at once. See Forks for the model.

$ 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, set-retention) 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).

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.

Rewrite small segments into target-sized ones, as a new version. Row count is verified preserved.

Remove unreachable objects, dry run unless --apply.

$ h5i-db vacuum market.db                    # inspect candidates
$ h5i-db vacuum market.db --apply            # actually delete

Read the cadence guidance before scripting this.

Check structural integrity: checksums, hash chain, object existence.

$ 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.

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.

$ h5i-db demo
$ h5i-db demo --dir ./scratch --keep      # keep the database it builds

Launch the local review UI, loopback only.

The UI shows pending plans with previews, a live fork monitor, the version timeline with audit badges, version diffs, and an SQL scratchpad that reports pruning per query.

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).