The algo trading data model I wish existed when I started — 4 layers, 12 tables, 3 dashboards
u/vdorru ·
Reddit — r/algotrading
· May 14, 2026 at 16:26
· ⬆ 16 pts
· 💬 11 comments
| View on Reddit ↗
AI Summary
Summary
The post presents a structured SQL data model (4 layers, 12 tables) for algo trading, designed to track strategies, market data, trading lifecycle, and analytics.
The author advocates for a middle-ground schema that separates backtest, paper, and live fills via a strategy_run_id, enabling robust performance dashboards.
The content is well-researched and practical, focusing on data architecture rather than market predictions or specific trade recommendations.
Score16
Comments11
Upvote %81%
▶ Full Post Text
Full disclosure: I'm the author of [DataPallas](https://github.com/flowkraft/datapallas), the open-source data platform used in the walkthrough. The data model itself is plain SQL — you can implement it with any stack you prefer.
Most algo trading tutorials give you either `trades(symbol, price, qty)` — which collapses the moment you ask "which strategy placed this?" — or a 60-table sell-side OMS schema nobody actually learns from.
This is the middle ground I couldn't find, so I wrote it.
**The model: 4 layers, 12 tables**
* **Layer 1 — Reference:** `exchange`, `instrument`, `account`, `strategy`
* **Layer 2 — Market data:** just `bar_1m` as a TimescaleDB hypertable — 5m/1h/1d bars are continuous aggregates, not separate tables
* **Layer 3 — Trading lifecycle:** `strategy_run → signal → order → fill → position` — the append-only event log
* **Layer 4 — Analytics:** `trade` (round-trip P&L) and `equity_curve`
The important FK column: every fill carries *strategy\_run\_id*, which links back to *strategy\_run.mode (backtest | paper | live)*. That's what isolates your backtest fills from your live fills.
**Then 3 operational dashboards on top:** Strategy Performance (*does it work?*), Live Positions & Exposure (*what am I holding right now?*), Execution Quality (*am I getting filled at the prices I expect?*).
This model is **complementary to frameworks like NautilusTrader, freqtrade, vectorbt — not a substitute.** The frameworks execute strategies. This observes them — across runs, across versions, across strategies.
Crypto adaptation is one paragraph at the end (3 tweaks, everything else unchanged).
Full '*build-your-own algo trading dashboards*' walkthrough with SQL, seed script, and live dashboards: [datapallas.com/blog/algo-trading-data-model](https://datapallas.com/blog/algo-trading-data-model)
**I'd genuinely like to know:** does your model look different? Where did the 12-table version break down for you in production?
[https://github.com/flowkraft/datapallas](https://github.com/flowkraft/datapallas)