# Unified DB-backed backtest architecture (Phase 0a)

**Date:** 2026-07-13
**Status:** Approved (design)
**Scope:** sub-project 0a of the "uniform Bybit+IBKR, DB-backed foundation" — the unified professional
backtest system. Sibling 0b (Binance/Alpaca-execution deprecation) is a separate, simpler spec.
**Depends on:** the forward engine (deterministic-forward-state, single-Postgres SSOT), the reconciliation
gate (`reconciliation_gate.py`), the `AnalyticalStore` port (`ports/repository.py`) + its two implementations
(`TimescaleFeatureStore` live / `DuckDbFeatureStore`), the nightly Dagster asset graph.

## Problem / Goal

The platform produces its `backtest_summary` reconciliation-gate reference through **three fragmented
mechanisms**, and that fragmentation causes real, currently-live bugs:

1. **Inline** `_persist_track_backtest_ref(context, name, build_strategy)` (`assets.py:363`) — replays the
   strategy over full history in the nightly `*_nav` asset when `persist_backtest_ref=True`. Used by
   `multistrat`, `vrp`, `crypto_tstrend`, `stablecoin_rotation`, `unlock`.
2. **The `bybit-persist-sleeve-ret` Job** (`cli.py:740`, `backtest_summaries_from_report`) — the ONLY writer
   of the `backtest_summary` ref for the deploy-tier `bybit_4edge` / `bybit_4edge_levered` / `positioning`.
   It is a plain `batch/v1` Job, **not scheduled** (run by hand/weekly; it OOM-killed the 4Gi Dagster daemon,
   so it was deliberately pulled out of the nightly graph).
3. **The B3 `equity_backtest_runner`** (`application/equity_backtest_runner.py`) — a survivorship-free
   equity-factor backtest against a **separate DuckDB warehouse on its own PVC**, whose verdicts are keyed to
   equity-factor constructions that have since been **retired from the registry**. Orphaned: no live gated
   consumer.

**Confirmed live bug (A):** `xsfunding` — a flagship crypto edge — has `gate_type: reconciliation` but its
`xsfunding_nav` asset does NOT set `persist_backtest_ref`, and nothing else writes a `backtest_summary` row
for it. `reconciliation_gate.py` therefore permanently returns WAIT ("no backtest reference to reconcile
against"). The edge can never gate-PASS. *(`sixtyforty` uses an **absolute** gate — `{"min_days":20,...}` —
so it needs no ref; it is NOT affected.)*

**Confirmed live bug (B):** the deploy-tier Bybit book's gate reference comes only from the unscheduled Job
(#2). If it lapses, `bybit_4edge` / `positioning` silently cannot gate-PASS.

**Persistence is split** between Timescale (the live operational + feature SSOT — the live nav assets already
read via `get_feature_store()` → `TimescaleFeatureStore`) and file-based stores (DuckDB warehouse files,
`worst_basis.parquet`) used by orphaned/retired research.

**Goal:** a uniform, professional backtest system where
1. **every reconciliation-gated strategy produces its `backtest_summary` via ONE consistent path** — so a
   gated strategy without a ref cannot exist (a structural *invariant*, not per-strategy patches). Fixes A,
   B, and the F-orphaning by construction.
2. **Timescale is the single durable store**; DuckDB is repositioned as an **ephemeral columnar engine** that
   reads from Timescale and writes verdicts back — no persistent DuckDB files / PVC in the live path.
3. heavy backtests run in a **dedicated, resourced, scheduled** step (own memory), never the OOM-prone shared
   Dagster daemon.

## Non-Goals / Hard Constraints

- **Real money errs false-WAIT:** a backtest that fails or produces no ref → the gate WAITs, NEVER
  false-PASSes. Absence of a ref is safe (WAIT); the invariant just makes absence impossible for a properly
  onboarded strategy.
- **Single-SSOT / deterministic-forward-state** preserved — Timescale remains the one truth; the DuckDB
  engine is stateless (ephemeral per run).
- **Out of scope:** reviving equity-factor research now (the architecture *supports* it — an equity strat
  would land its data in Timescale and plug into the unified path — but we do NOT migrate the Tiingo equity
  DuckDB warehouse in 0a); the Binance/Alpaca-execution cleanup (0b); the cockpit Ops/Fleet UI (Phase 2).
- Verify the cockpit **locally** (the gate un-sticks for `xsfunding`) per `feedback_verify_cockpit_locally_not_prod`.
- Every review Minor is a latent bug — fix in-loop.

## Architecture

```
Dagster ingest ─────────────►  TIMESCALE  ◄──────────── the ONE durable store
   (bars/features/funding/       (SSOT: raw data + backtest_summary verdicts
    xsp_option_bars, unchanged)         + forward_nav/summary + allocation/funding/book_state)
                                   ▲            │
   unified backtest step ─────────┤            │  reconciliation gate reads the ref
     (registry-driven dispatch)   │            ▼
        │  recompute-replay ──────┘        forward_ingest: gate → promotion → allocation
        │    (cheap: replay ForwardStrategy over the Timescale panel)
        │  report-based ──── ephemeral in-mem DuckDB: ATTACH Timescale (postgres ext,
        │    (heavy)          verified OK on duckdb 1.5.3) → columnar compute → verdict
        └──────────────────► upsert_backtest_summary → TIMESCALE
                                   │
                                   ▼
                              COCKPIT (read-only) — gate PASS-able for every edge
```

The **`AnalyticalStore` port is the seam**: strategies and backtests ask the port for panels; the port is
backed by Timescale live, or by an ephemeral DuckDB-over-Timescale for heavy compute. Swapping the backend is
an adapter choice, not a rewrite.

## Component 1 — The unified backtest→ref contract (the heart)

A single registry-driven dispatch replaces the three mechanisms:

- Each `STRATEGY_REGISTRY` entry declares a **backtest kind** in its `definition` (new field
  `backtest: {"kind": "recompute-replay" | "report" | "none"}`; `none` = absolute-gated benchmarks like
  `sixtyforty` that need no ref, made EXPLICIT rather than silently ref-less).
- A `backtest_ref(strategy_id, store: AnalyticalStore) -> BacktestSummary | None` function dispatches on the
  kind:
  - **`recompute-replay`** (multistrat, vrp, crypto_tstrend, stablecoin_rotation, unlock, **xsfunding**):
    build the strategy, `advance(None, {})` over full history off the Timescale panel → `derive_nav` →
    `BacktestSummary`. This generalizes today's `_persist_track_backtest_ref`. **Fixes A**: `xsfunding` gets a
    ref via this path (add `backtest.kind = "recompute-replay"` + a `build_strategy` entry).
  - **`report`** (bybit_4edge, bybit_4edge_levered, positioning): compute the sleeve-return report and
    `backtest_summaries_from_report` → `BacktestSummary`. Generalizes the `bybit-persist-sleeve-ret` logic,
    moved INTO the unified path and scheduled. **Fixes B**.
  - **`none`** (sixtyforty): no ref; the gate is absolute — asserted explicitly.
- **The invariant:** a test iterates `STRATEGY_REGISTRY` and asserts every `gate_type: reconciliation`
  strategy has a non-`none` backtest kind + a working provider. A new reconciliation-gated strategy without a
  backtest declaration FAILS the test — so onboarding a strategy that can never gate-PASS is impossible.
  This is the structural fix that turns "POC with silent gaps" into a professional invariant.

## Component 2 — DuckDB as an ephemeral engine (no persistent files)

- `report`-kind (heavy) backtests use an **ephemeral in-memory DuckDB**: `duckdb.connect()` →
  `INSTALL postgres; LOAD postgres; ATTACH '<timescale dsn>'` (feasibility verified: duckdb 1.5.3 postgres
  extension loads) → columnar scan/aggregate the needed slice from Timescale → verdict → written back to
  Timescale via `upsert_backtest_summary`. The DuckDB connection is per-run and discarded — no file, no PVC.
- The `AnalyticalStore` port already abstracts panel reads; a thin `TimescaleDuckDbEngine` adapter provides
  the ATTACH-and-scan path for heavy report backtests while the cheap recompute-replay stays on the plain
  Timescale reads it already uses.
- **Retire** the persistent DuckDB files (`warehouse.duckdb`, `analytical.duckdb`) and `worst_basis.parquet`
  from the live path (see Component 3 for the classification).

## Component 3 — File-store inventory + migrate/park classification

| store / module | role | classification | action in 0a |
|---|---|---|---|
| `TimescaleFeatureStore` (crypto panels, `bybit_features`, Yahoo-PIT, `xsp_option_bars`) | LIVE feature/data reads | already Timescale ✓ | none |
| `equity_backtest_runner` + B3 DuckDB warehouse (`/backtest-data/*.duckdb` PVC) | equity-factor backtest | ORPHANED (no live strat; retired constructions) | **park** — keep code; equity strats plug into the unified path (data→Timescale) when actually pursued. Retire the PVC/Job. |
| `worst_basis*` / `bybit_carry_revalidate` / `bybit_worst_basis_ingest` + `worst_basis.parquet` | cross-venue carry research | tied to the RETIRED `xvenue_carry` | **park** — research-only, revivable; migrate to Timescale only if carry revives |
| `warehouse.duckdb` / `analytical.duckdb` (dev/opt-in `DuckDbFeatureStore`) | dev/test embedded backend | opt-in (prod uses postgres) | keep as the dev/test backend behind the port; not in the live path |
| `paper_book` / `paper_sleeves` / `paper_backfill` DuckDB imports | paper-book/replay services | **VERIFY** (prod uses `get_feature_store()`=postgres; the DuckDB import may be a dev/fallback) | the plan MUST confirm whether these read DuckDB in the LIVE path; if live-adjacent → migrate to the Timescale port; if dev-only → leave |

Net: **no persistent DuckDB file / PVC in the live path**; Timescale is the one store. Parked research keeps
its code (revivable) but its file-stores are decommissioned from the running system.

## Component 4 — Orchestration + the OOM lesson

- The unified backtest step runs in **dedicated, own-memory, scheduled** compute — NOT the 4Gi Dagster daemon
  (which OOM'd on the bybit sleeve-ret). Two acceptable homes: (a) a resourced Dagster op with a k8s/
  per-op-executor, or (b) a scheduled `CronJob` running `fxhnt backtest-refs --all` writing to Timescale
  (mirrors the batched-OPRA-backfill / compare-precompute pattern). **Fixes B**: the ref becomes a scheduled
  guarantee, not a manual Job.
- **Cheap `recompute-replay` refs stay inline** in the nightly `*_nav` assets (they already run there and are
  light) — only the **heavy `report` refs** move to the resourced scheduled step. So the nightly daemon load
  is unchanged; the OOM-prone work is where it belongs (own memory).
- Dagster ingest → Timescale is unchanged. The gate/promotion/allocation chain in `cockpit_forward` reads the
  refs exactly as today — it just now finds one for every gated strategy.

## Data Flow

```
nightly Dagster job (unchanged ingest → *_nav → cockpit_forward → gate/promote/allocation)
  + recompute-replay refs inline (xsfunding NOW included → gate un-sticks)
scheduled unified-backtest step (own memory):
  for each registry strategy with backtest.kind == "report":
    ephemeral DuckDB ATTACH Timescale → compute report → upsert_backtest_summary (Timescale)
gate (cockpit_forward): reconciliation vs the ref → PASS-able for every edge → cockpit
```

## Error Handling

- A backtest provider that raises / returns None → NO ref written → the gate WAITs (safe, false-WAIT). The
  step logs the failure per strategy; one bad strategy never aborts the others.
- The invariant test guarantees a *declared* provider exists for every reconciliation-gated strategy; a
  runtime failure still degrades to WAIT, never false-PASS.
- The ephemeral DuckDB ATTACH failing (extension/DSN) → the report backtest raises → WAIT for those
  strategies (never a silent partial ref).
- `sixtyforty` (`backtest.kind == "none"`, absolute gate) is explicitly exempt — asserted, not silently
  ref-less.

## Testing

- **Unit:** `backtest_ref` dispatch produces a `BacktestSummary` for a `recompute-replay` strategy (replay a
  synthetic ForwardStrategy) and for a `report` strategy (synthetic report); returns None/ skips for `none`.
  A regression test: `xsfunding`'s provider yields a ref (bug A). The `TimescaleDuckDbEngine` ATTACH-and-scan
  reads a panel from a seeded (sqlite/duck) store and computes.
- **Invariant test (integration):** iterate `STRATEGY_REGISTRY`; assert every `gate_type: reconciliation`
  entry declares a non-`none` `backtest.kind` AND its provider is constructible. This test makes a
  gate-un-passable strategy a CI failure.
- **Integration:** the scheduled backtest step writes `backtest_summary` rows for the `report` strategies;
  the gate evaluates PASS-able (not stuck WAIT) for `xsfunding` and the bybit book.
- **Local cockpit (Playwright):** `/strategy/xsfunding` no longer shows a permanent "no backtest reference"
  WAIT; read the gate state. Never claim off synthetic/prod.

## File Touchpoints

| File | Change |
|---|---|
| `src/fxhnt/registry.py` | add `definition.backtest.kind` per entry (`recompute-replay`/`report`/`none`); `xsfunding` → `recompute-replay` |
| `src/fxhnt/application/backtest_refs.py` (new) | `backtest_ref(strategy_id, store) -> BacktestSummary\|None` dispatch (recompute-replay / report / none) |
| `src/fxhnt/adapters/warehouse/timescale_duckdb_engine.py` (new) | `TimescaleDuckDbEngine` — ephemeral DuckDB ATTACH Timescale for heavy report backtests |
| `src/fxhnt/adapters/orchestration/assets.py` | route inline recompute refs through `backtest_ref`; add `xsfunding` ref; keep heavy report out of the daemon |
| `src/fxhnt/adapters/orchestration/definitions.py` | schedule the resourced unified-backtest step (op or CronJob), replacing the manual bybit-sleeve-ret Job |
| `src/fxhnt/cli.py` | `backtest-refs --all` command (for the scheduled step); retire/redirect `bybit-persist-sleeve-ret` into it |
| `infra/k8s/jobs/` | new scheduled `fxhnt-backtest-refs` CronJob (own memory); retire the B3 backtest DuckDB PVC + the manual sleeve-ret Job |
| tests | `test_backtest_refs.py` (dispatch + xsfunding regression), the registry invariant test, `test_timescale_duckdb_engine.py`, cockpit-local |

## Deploy Notes

- The ephemeral DuckDB `INSTALL postgres` runs at runtime (already verified on duckdb 1.5.3); the resourced
  backtest step needs its own k8s memory (like the OPRA backfill / compare-precompute).
- Retiring the B3 DuckDB PVC + the manual sleeve-ret Job is a decommission step; the parked research code
  stays in the tree (revivable) but its Jobs/PVCs are removed from the running system.
- No forward-state migration; `xsfunding` gets its first ref on the next run and its gate begins reconciling.
