Database#

How data is stored, moved and configured, and the schema of each database. The clean tier is split across four logical databases, each with its own SQLAlchemy engine and declarative base. This folder documents the architecture and setup, each database’s schema, and the decisions that cut across them.

Architecture and setup#

Document	Covers
Data architecture	Data tiers (Redis, PostgreSQL, DuckDB), why SQLAlchemy, why four databases
Setup: configuration, PostgreSQL and Redis	Connection settings, running on PostgreSQL, running Redis in Docker on Windows

Schemas#

Database	Document	Owns
Reference	Reference database (reference.db)	Instruments (all asset classes), books, traders
Market	Market database (market.db)	Daily OHLC prices, labelled observed or simulated
Trade	Trade database (trade.db)	Trades (the blotter) and EOD P&L
Risk	Risk database (risk.db)	Position limits, EOD risk metrics, limit breaches

Conventions#

snake_case names, plural table names, singular model class names.
id or <entity>_id surrogate integer primary keys.
symbol is the business key that links data across databases.
No foreign keys across databases. Foreign keys within one database are used where they exist (e.g. fx_instruments.instrument_id).
Models live in src/trade_engine/models/, one module per database. SQLite files are created under db/.
Until Alembic is adopted, tables are created with create_all, which does not alter existing tables. After a schema change, delete the old db/*.db files and run scripts/init_db.py again.

Cross-cutting design decisions#

Decision	Reason	Consequence
Neutral core tables plus one-to-one subtype tables for class-specific attributes	Add asset classes without touching trades, prices, P&L or risk; each subtype enforces its own constraints	Reading class-specific attributes needs a join; only code that needs them pays for it.
Surrogate integer primary keys	Stable row identity that never changes even if a business attribute (name, symbol) is corrected; small, fast join/index key	Business uniqueness is enforced separately (unique constraints). Growth tables should use `BIGINT` on Postgres (roadmap).
Business keys (`symbol`, `book`, `trader`) repeated as strings in downstream tables instead of ids	No cross-database foreign keys, so ids from another database would be meaningless to join; the value is also a self-describing snapshot that stays readable in reports and exports	These keys are immutable codes; retire with `active = false` rather than rename or delete.
EOD results (`pnl_eod`, `risk_metrics_eod`, `limit_breaches`) are persisted, not computed on read	Reports and the dashboard are cheap reads; results are an auditable snapshot of what was calculated on that date, independent of later trade or price changes	Reruns replace that date’s rows. The history of reruns is not kept until the run ledger exists.
Prices and EOD results keyed by date, one row per `(symbol[, book], date)`	Matches the daily batch grain; makes idempotent replace-by-date trivial and keeps range scans by date cheap	Intraday data needs a different table (time-series), not extra columns here.
Provenance columns on prices (`source`, `data_origin`, `loaded_at`)	Observed, vendor and simulated data must never be confusable; `data_origin` is a closed set enforced by the database, `source` is free-form detail	Loaders must state the origin explicitly; there is no default.
P&L stored with its currency (`pnl_eod.ccy`)	Multi-asset books hold several currencies; an amount without a currency is ambiguous	Aggregating across currencies requires conversion (not built).
`server_default=now()` for audit timestamps	Timestamps are set by the database, not by a possibly skewed client	Currently timezone-naive; should become timezone-aware UTC (roadmap).
Money as `Float` in v1	Simple and adequate for the single-pair v1 workflow	Binary floating point cannot exactly represent decimal prices, and crypto needs far more decimal places; must change to `Numeric` before real use (roadmap).

Indexing and scalability#

Access patterns that drive the schema

Query	Served by
Latest close at or before date D for a symbol; closes up to D (mark and VaR)	unique `(symbol, px_date)` on `prices_eod`
Latest loaded date (default EOD date); recent prices	index on `prices_eod.px_date`
All non-cancelled trades up to D in replay order	indexes on `trades.symbol`, `trades.trade_date`; a composite replay index is on the roadmap
Blotter: most recent trades first	index on `trades.trade_date` (plus `trade_id`)
P&L, risk and breaches for one date; recent history; replace-by-date	indexes on `calc_date` (and `symbol`, `book`)
Instruments of one class	index on `instruments.asset_class`
Active limits	small table; scanned

Expected growth. Row counts follow the grain, not the code. Prices grow by roughly one row per symbol per business day (crypto: per calendar day). pnl_eod and risk_metrics_eod grow by one row per open (symbol, book) per day. trades grows with booking activity. At v1 scope (one pair, a few books) every table is small by database standards. Adding asset classes grows the instrument count, and with it prices and EOD results; reference and trade tables grow far more slowly, which is part of why they are separate databases.

Scaling levers, in order of cost

Indexes and query shape. All hot queries are bounded by calc_date or (symbol, date), so they stay index seeks as tables grow.
Read replicas for reporting. The dashboard, blotter and history views only read, and the reporting service is the only read-side entry point (see Reporting).
Partition the append-only EOD tables by ``calc_date`` (Postgres range partitioning) once retention and vacuum cost matter. Old partitions can be archived or dropped cheaply, which the date grain makes natural.
Move databases to separate hosts. Possible today by changing connection strings; the design already assumes no shared transactions.
Intraday or tick data (out of scope; likely for crypto and equities) belongs in a time-series store, not in prices_eod.

Database

Contents