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.

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, the version timeline with audit badges, version diffs, and an SQL scratchpad that reports pruning per query.