#41 — Central trades DB + infrastructure

2026-05-12 (Session 046)

Verdict: positive · ✅ positive (engineering)

Made trades.duckdb the single source of truth for all live and paper trades — idempotent trade_id-deduplicated writes, broker backfill, and a dashboard that reads only from the DB.

Hypothesis — what it tests

Engineering: can all trade records — live and paper, across every strategy container — flow into one deduplicated DuckDB so the ledger is queryable, broker-reconciled, and immune to per-container log divergence?

Description

An infrastructure build that fixed a measurement problem: every strategy container had been logging trades to its own JSONL file, so there was no single queryable P&L history and the dashboard had nothing structured to plot. lib/db.py gained is_paper and label columns, an idempotent schema migration, and write_trade_direct() — a direct write path with WHERE-NOT-EXISTS dedup on trade_id and retry on lock contention. A missing fx-db volume mount was found to be the root cause of an empty dashboard (each container had been writing to its own invisible overlay-filesystem copy of the DB). A broker backfill script reconstructed 84 Zone Recovery trades from OANDA transaction history (net +109.7 pips, broker-reconciled), and 106 historical paper trades were imported from JSONL. The dashboard gained a live-vs-paper split equity chart and a paper-strategies section, with the strategy registry made authoritative for enabled/disabled status.

Key result

84 broker-reconciled ZR trades backfilled (net +109.7 pips, matching OANDA); 106 paper JSONL records imported; dedup by trade_id, 0 double-counts

Algorithms

DuckDB single-writer via ZMQ write proxytrade_id deduplication (idempotent backfill)broker reconciliation

Code

lib/db.py services/dashboard/main.py services/portfolio_mgr/main.py docker-compose.yml

Interactive version (search, filters, figures): the experiment explorer.

← #40 Multiscale Price Shock (MSP) propagation #42 Grid-trail strategy →

This page is one experiment from the audited record behind the book The Edge That Wasn't — get the book · about the project.