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 ---------------------- .. list-table:: :header-rows: 1 * - Document - Covers * - :doc:`/database/architecture` - Data tiers (Redis, PostgreSQL, DuckDB), why SQLAlchemy, why four databases * - :doc:`/database/setup` - Connection settings, running on PostgreSQL, running Redis in Docker on Windows Schemas ------- .. list-table:: :header-rows: 1 * - Database - Document - Owns * - Reference - :doc:`/database/reference` - Instruments (all asset classes), books, traders * - Market - :doc:`/database/market` - Daily OHLC prices, labelled observed or simulated * - Trade - :doc:`/database/trade` - Trades (the blotter) and EOD P&L * - Risk - :doc:`/database/risk` - Position limits, EOD risk metrics, limit breaches Related documents: - :doc:`/database/multi_asset`: how the schema extends to FX, crypto, bonds and equities. - :doc:`/database/hardening_roadmap`: schema changes required before real use. .. toctree:: :hidden: architecture setup reference market trade risk multi_asset hardening_roadmap Conventions ----------- - snake_case names, plural table names, singular model class names. - ``id`` or ``_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 ------------------------------ .. list-table:: :header-rows: 1 * - 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** .. list-table:: :header-rows: 1 * - 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** 1. **Indexes and query shape.** All hot queries are bounded by ``calc_date`` or ``(symbol, date)``, so they stay index seeks as tables grow. 2. **Read replicas for reporting.** The dashboard, blotter and history views only read, and the reporting service is the only read-side entry point (see :doc:`/reporting`). 3. **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. 4. **Move databases to separate hosts.** Possible today by changing connection strings; the design already assumes no shared transactions. 5. **Intraday or tick data** (out of scope; likely for crypto and equities) belongs in a time-series store, not in ``prices_eod``.