Epic: tiered backtest storage (rewrite the storage layer)

[MDUYN]

Epic: Tiered backtest storage (rewrite the storage layer)

Epic. Replace the single-file .iafbt storage primitive with a tiered backend (relational index + columnar Parquet bulk + content-addressed chunks). Demote .iafbt to a deterministic export format. Keep the OSS path local-first and self-contained.

Full design: docs/design/tiered-backtest-storage.md
Companion specs: docs/design/bundle-format-v2.md, docs/design/ohlcv-dedup-protocol.md

Why now

Empirical measurements on a real production-shape archive (12,500 bundles ≈ 64 GB, 10 May 2026) showed that per-file format work has hit its ceiling:

Configuration	Per bundle	Total	Notes
v1, zstd 7 (pre-v8.9)	569 KB	64.0 GB	baseline
v2 + zstd 19 (v8.9, shipped)	489 KB	~55 GB	per-file ceiling
Tiered store + content-addressed dedup	n/a (decomposed)	< 20 GB projected	this epic

Two structural problems remain after v8.9:

1. Per-file compression has hit its ceiling. zstd at level 22 saturates at the same size as level 19. Within a single .iafbt, no remaining headroom.
2. The .iafbt is the wrong primitive for two of the three real workloads — listing/ranking and cross-run analytics. Decoding 12,500 zstd payloads to read 50 scalar metrics each is a multi-minute loop instead of a 50 ms SQL query.

Cross-bundle redundancy (strategy params, symbol metadata, OHLCV slices, recurring trade patterns) is the dominant unexploited source of size, and zstd cannot see across files. The fix lives one layer up.

Architecture (target)

┌─────────────────────────────────────────────────────────────────┐
│ Tier 1 — Index   (SQLite locally, Postgres remotely)            │
│   One row per backtest run. Scalar metrics + provenance + refs. │
│   Indexed for ranking, filtering, sweep navigation.             │
├─────────────────────────────────────────────────────────────────┤
│ Tier 2 — Columnar bulk   (Parquet on local disk or object store)│
│   Per-project Parquet datasets, partitioned by run_id:          │
│     portfolio_snapshots/   trades/   orders/   metric_series/   │
├─────────────────────────────────────────────────────────────────┤
│ Tier 3 — Content-addressed chunks                               │
│   SHA-256-keyed, immutable:                                     │
│     ohlcv/  code/  params/  symbols/  metric-series-blobs/      │
└─────────────────────────────────────────────────────────────────┘

.iafbt v2 stays — but as backtest.export("x.iafbt") / Backtest.import_("x.iafbt"), deterministic and round-trippable through any store. It is no longer the storage primitive.

Phases

This epic ships in three phases, each independently mergeable. Each phase is purely additive until phase 3, which deprecates (does not remove) the directory-of-.iafbt default.

Phase 1 — BacktestSummary DTO + scalar-summary read path

Goal: make "list / rank by Sharpe over many bundles" cheap on the existing on-disk shape, without writing anything new.

• Define BacktestSummary dataclass — frozen schema for the ~50 scalar metrics + provenance + config columns from design doc §3.1.
• Backtest.scalar_summary() -> BacktestSummary — readable from a v2 bundle without decoding Parquet metric blobs (already supported by summary_only=True; this wraps it in a typed return).
• Tests: round-trip from v2 bundle → BacktestSummary → SQL row payload.
• Document the BacktestSummary schema as the authoritative Tier 1 row contract.

Risk: low. Pure additive read path. No write changes.

Phase 2 — iaf index CLI + SQLite index

Goal: turn a folder of .iafbt files into a queryable archive without changing how they're written.

• iaf index <dir> walks .iafbt files (recursive), populates <dir>/index.sqlite with one row per run from scalar_summary().
• Idempotent (re-runs only ingest changed bundles, keyed by bundle_id + mtime).
• iaf list <index.sqlite> --sort sharpe --limit 20
• iaf rank <index.sqlite> --by sortino --filter 'tag LIKE "sweep_%"'
• Tolerate partial corruption (skip + log, never crash).
• Acceptance: iaf index builds a SQLite from examples/batch_one/ in < 5 s; iaf list --sort sharpe --limit 20 returns in < 100 ms over 12,500-row index.

Risk: low. SQLite is a derived view — delete and rebuild is always safe.

Phase 3 — BacktestStore abstraction + LocalTieredStore

Goal: introduce the store interface so the framework can write into multiple backends; ship the local tiered backend; demote .iafbt to export format.

• BacktestStore Protocol with: put, get, list, delete, export, import_.
• LocalDirStore — wraps current save_bundle/open_bundle. Default for backwards compatibility.
• LocalTieredStore:
  • Tier 1: index.sqlite (schema = BacktestSummary from phase 1)
  • Tier 2: per-project Parquet datasets per design doc §3.2
  • Tier 3: chunks/ directory per design doc §3.3
• Backtest service constructors accept a store= kwarg, default LocalDirStore.
• iaf migrate-store --from local-dir --to local-tiered <src> <dst> one-shot migrator.
• backtest.export("x.iafbt") reassembles a v2 bundle deterministically from any store.
• Backtest.import_("x.iafbt") decomposes into the configured store (idempotent on bundle_id).
• All existing backtest tests pass against both stores via a parameterised fixture.
• Acceptance: on a 12,500-bundle workload, LocalTieredStore.list(sort='sharpe', limit=20) returns in < 100 ms; round-trip LocalDirStore → export → LocalTieredStore.import_ → export is byte-identical (modulo writer timestamp).

Risk: medium. Touches every backtest service constructor. Mitigated by keeping LocalDirStore the default for at least one minor release with a deprecation warning when the old kwargs are used.

Wire format note

The .iafbt v2 produced by phase 0 (already shipped in v8.9) is the only wire format the framework reads or writes for the archive-as-file use case. v1 is readable indefinitely; the v8.9 writer never emits v1.

A remote store implementation (Finterion-side, closed-source, not part of this epic) will negotiate-then-upload chunks via the protocol in docs/design/ohlcv-dedup-protocol.md generalized to all chunk types. The BacktestStore interface from phase 3 is the OSS-side contract that makes that possible.

Non-goals

• Per-bundle Parquet for everything (measured net-negative on real data — see design doc §9)
• Custom binary column format (Parquet is solved; leverage it)
• Lossy snapshot/trade compression (user's data, hands off)
• Schema-on-read JSON anywhere (the original (value, ISO-string) mistake)
• Removing LocalDirStore (stays as default for at least one minor cycle after phase 3)
• Cross-tenant or cross-project dedup at the OSS layer (a remote store's concern)
• Remote / cloud store implementation (closed-source, separate)

Tracking

Replaces (closed as superseded):

• v8.10: read-side index + scalar-summary access (tiered storage phase 1) #538 v8.10 read-side index
• v8.11: BacktestStore abstraction + LocalTieredStore (tiered storage phase 2) #539 v8.11 BacktestStore + LocalTieredStore

Why one epic, not three issues: the three phases share schemas, naming, and tests, and shipping them as a single coherent epic makes the deprecation path for .iafbt-as-storage one decision rather than three. Each phase is still independently mergeable as separate PRs against this epic.

Headline

Today's design treats a backtest as a file. The future design treats a backtest as a row that points to chunks, with the file as one of several possible views.

That single shift turns the 64 GB problem into the 20 GB problem, makes "list 12,500 backtests sorted by Sharpe" a 50 ms query instead of a 30-minute decode loop, and unlocks DuckDB/Polars analytics over the entire archive without writing any new code.

[MDUYN]

Progress update — 11 May 2026

Phase 1 — BacktestSummary DTO + scalar-summary read path — ✅ Complete

• Frozen scalar-only schema: BacktestSummaryMetrics (existing) + new BacktestIndexRow Tier-1 contract — see investing_algorithm_framework/domain/backtesting/backtest_index_row.py
• Read path without decoding Parquet metric blobs: Backtest.open(path, summary_only=True) + Backtest.index_row(bundle_path=...)
• Round-trip: bundle → BacktestIndexRow → SQL row payload (covered by tests/services/backtest_index/test_sqlite_index.py)
• Schema documented as the authoritative Tier-1 row contract (docstring on BacktestIndexRow, references docs/design/tiered-backtest-storage.md §3.1)

Phase 2 — iaf index CLI + SQLite index — 🟡 Partially complete

• iaf index <dir> walks *.iafbt recursively, builds <dir>/index.sqlite from Backtest.index_row() — see cli/index_command.py and the index Click command in cli/cli.py
• Idempotent: PRIMARY KEY on bundle_path + upsert / upsert_many (SqliteBacktestIndex)
• Tolerates partial corruption — bad bundles are logged and skipped, never crash
• SqliteBacktestIndex.query(where=..., params=...) returns typed BacktestIndexRow objects, with all BacktestSummaryMetrics fields promoted to summary_<name> columns (so WHERE summary_sharpe_ratio > 1.0 ORDER BY ... LIMIT ... works directly in SQL)
• WAL mode + schema version pragma for safe concurrent readers and additive migrations
• Tests: tests/cli/test_index_command.py, tests/services/backtest_index/test_sqlite_index.py
• iaf list <index.sqlite> --sort sharpe --limit 20 CLI subcommand — not yet wired; the underlying SQL is supported, only the Click command is missing
• iaf rank <index.sqlite> --by sortino --filter '...' CLI subcommand — same as above
• Formal acceptance benchmark: index examples/batch_one/ in < 5 s; list --sort sharpe --limit 20 over 12,500-row index in < 100 ms — not yet run/recorded

Phase 3 — BacktestStore abstraction + LocalTieredStore — 🔴 Not started

• BacktestStore Protocol (put / get / list / delete / export / import_)
• LocalDirStore (wraps current save_bundle / open_bundle)
• LocalTieredStore (Tier 1 SQLite + Tier 2 Parquet datasets + Tier 3 content-addressed chunks)
• Backtest service constructors accept store= kwarg
• iaf migrate-store --from local-dir --to local-tiered migrator (note: existing iaf migrate-backtests is the v1→v2 bundle migrator and is unrelated)
• backtest.export("x.iafbt") / Backtest.import_("x.iafbt") from any store
• Parametrised test fixture exercising every backtest test against both stores
• Acceptance: list/sort < 100 ms over 12,500-bundle store; round-trip LocalDirStore → export → LocalTieredStore.import_ → export byte-identical

Summary

Phase	Status	Notes
1. Scalar DTO + read path	✅ Complete	BacktestIndexRow + summary_only=True shipping
2. iaf index + SQLite	🟡 ~70%	core indexing + queryable schema done; iaf list / iaf rank CLI + perf benchmark still open
3. BacktestStore + LocalTieredStore	🔴 Not started	full design ready in docs/design/tiered-backtest-storage.md; no code yet

Suggested next concrete steps

1. Wire iaf list and iaf rank Click subcommands on top of SqliteBacktestIndex.query() (small — < 1 day).
2. Run and record the phase-2 acceptance benchmark on examples/batch_one/ so the perf budget is verified before phase 3 starts depending on it.
3. Open a phase-3 PR scaffold: BacktestStore Protocol + LocalDirStore wrapper around the current save/open path, no behavior change. That unblocks the LocalTieredStore work without touching every service constructor in one go.

[MDUYN]

Progress update — iaf list + iaf rank shipped

Phase 2 is now feature-complete except for the formal acceptance benchmark.

What landed

• iaf list <index|dir> — query the SQLite index with sort / limit / SQL WHERE filter / custom columns / JSON output. Accepts either an index.sqlite file or the directory it lives in.
• iaf rank <index|dir> --by <metric> — sugar over list with a column set tuned for strategy comparison (Sharpe / Sortino / Calmar / return / max drawdown).
• User-friendly metric names — --sort sharpe_ratio is auto-prefixed to summary_sharpe_ratio so users don't have to know the SQL column scheme.
• NULL-aware ordering — bundles missing a metric sort to the bottom regardless of direction.
• Public Python API: list_index(), rank_index(), format_table() in investing_algorithm_framework.cli.index_command.
• 12 new tests in tests/cli/test_index_command.py covering both the Python helpers and the Click surface (1710 / 1710 passing in the full suite).

Demo

A self-contained end-to-end demo of the new storage layer is now in examples/storage_layer_demo/ — run python examples/storage_layer_demo/demo.py to see save → index → list → rank → raw SQL in one go.

Sample output:

algorithm_id    tag   summary_sharpe_ratio  summary_sortino_ratio  summary_max_drawdown  ...
--------------  ----  --------------------  ---------------------  --------------------
momentum_v1     demo  1.8500                2.4000                 -0.0800
momentum_v2     demo  1.4200                1.9100                 -0.1200
mean_revert_v2  demo  1.1000                1.4500                 -0.1500

Updated phase 2 status

Item	Status
iaf index builder	✅
SQLite schema with promoted summary columns	✅
Idempotent upsert / WAL / partial-corruption tolerance	✅
iaf list subcommand	✅ (this PR)
iaf rank subcommand	✅ (this PR)
Acceptance benchmark on 12,500 bundles	⏳ next

Phase 3 (BacktestStore / LocalDirStore / LocalTieredStore) remains the next major chunk of work.

[MDUYN]

Phase 1 + Phase 2 — done ✅

Closing out the remaining gaps.

Phase 1 — complete

• BacktestSummary DTO — shipped as BacktestSummaryMetrics (scalar metrics) + BacktestIndexRow (full Tier-1 row: identity + provenance + config + summary).
• Backtest.scalar_summary() -> BacktestSummaryMetrics method (new alias; canonical spec name).
• Read path without decoding Parquet — Backtest.open(path, summary_only=True).
• Round-trip test bundle → BacktestIndexRow → SQL row.
• Schema documented (BacktestIndexRow docstring + docs/design/tiered-backtest-storage.md §3.1).

Phase 2 — complete

• iaf index <dir> walks .iafbt, populates <dir>/index.sqlite.
• Incremental rebuild keyed by bundle_path + bundle_mtime_ns + bundle_size — bundles whose mtime+size match an existing row are skipped. Force a full rebuild with iaf index --rebuild or build_index(..., incremental=False).
• iaf list <index|dir> --sort <metric> --limit N --where '...' --json.
• iaf rank <index|dir> --by <metric> -n N.
• Partial-corruption tolerance — bad bundles are logged and skipped.
• Acceptance benchmark — see numbers below.

Acceptance benchmark (measured 11 May 2026)

Generated synthetic .iafbt bundles by re-saving the test-suite fixture; ran on a 2024 MacBook Pro. Script: scripts/bench_540_phase2.py.

1,000 bundles

step	wall time
build_index (cold)	5,194 ms
build_index (incremental, all unchanged)	42 ms
list_index --sort sharpe_ratio --limit 20	9.0 ms
list_index --sort sharpe_ratio (full scan)	56.5 ms

12,500 bundles (the production-shape scale called out in the epic)

step	wall time
build_index (cold)	85.9 s
build_index (incremental, all unchanged)	536 ms
list_index --sort sharpe_ratio --limit 20	8.3 ms ✅ (acceptance: < 100 ms)
list_index --sort sharpe_ratio (full scan)	707 ms

Footprint at 12,500 bundles: index.sqlite = 2.5 MiB (~0.21 KiB per bundle).

The cold-build cost is dominated by per-bundle file I/O and is paid once; thereafter every refresh is sub-second. The headline ranking workload (list --limit 20) returns in 8 ms over 12.5k rows — 12× under the acceptance target and does not scale with bundle count.

Updated phase status

Phase	Status	Notes
1. Scalar DTO + read path	✅ Complete	+ scalar_summary() alias for spec parity
2. iaf index + SQLite + list/rank	✅ Complete	+ incremental refresh + measured benchmark
3. BacktestStore + LocalTieredStore	🔴 Not started	next

What's next (Phase 3)

The remaining work is the bundle-decomposition rewrite:

• BacktestStore Protocol + LocalDirStore (small — adapter over current save/open).
• LocalTieredStore Tier-2 Parquet datasets (snapshots / trades / orders / metric_series) — large.
• LocalTieredStore Tier-3 content-addressed chunks (ohlcv/, code/, params/, symbols/) — large. This is where the 64 GB → 20 GB headline number comes from.
• iaf migrate-store --from local-dir --to local-tiered.
• backtest.export() / Backtest.import_() byte-identical round-trip across stores.
• Parameterised test fixture exercising every backtest test against both stores.

I'll plan to split phase 3 into separate PRs (Protocol + LocalDirStore first; Tier-2 / Tier-3 / migrator as follow-ups) so each one stays mergeable on its own.