Data architecture ================= How data is stored and moved: the three tiers (Redis, PostgreSQL, DuckDB), why the code uses SQLAlchemy, and why the clean tier is split into four logical databases. Table-level detail is in the per-database pages listed in :doc:`/database/index`; configuration and running the servers is in :doc:`/database/setup`. Data tiers ---------- Market data moves through three stores. Each has one job and data only flows forward (Redis -> PostgreSQL -> DuckDB). .. list-table:: :header-rows: 1 * - Tier - Store - Holds - Written by * - Raw / staging - Redis Stream (``trade_engine:staging:market``) - Unvalidated CSV rows exactly as read (strings), one stream entry per file - ``scripts/load_market_data.py``, ``POST /market`` * - Clean / structured - PostgreSQL (four databases); SQLite files by default for local development and tests - Validated, typed, provenance-labelled data: reference, market, trades, P&L, risk - ``scripts/process_staging.py`` (market), the services (everything else) * - Local analytics - DuckDB file (``db/analytics.duckdb``) - A read-only snapshot of the clean tables for research and backtests - ``scripts/export_analytics.py`` How the staging step behaves: - **Staging does not validate prices.** Only the file's origin, source and readability are checked up front. Prices are validated when the batch is processed, using the same rules as before (see :doc:`/database/market`). - **A batch is committed as a unit.** Processing validates the whole file, inserts new dates in one transaction, and only then acknowledges the batch. A crash before the acknowledgement re-delivers the batch after 60 seconds; reloading is safe because dates already stored are skipped. - **Invalid batches are dead-lettered.** A batch that fails validation moves, with its raw rows and the reason, to ``trade_engine:staging:market:rejected`` and is never retried automatically. - **Redis is not a system of record.** Anything that matters is in PostgreSQL once processed. Redis persistence (AOF/RDB) only protects batches that are staged but not yet processed. - **The analytics export replaces its tables each run** and never writes back to the clean databases. It keeps ``data_origin`` on prices so simulated and observed data stay distinguishable in research. Why SQLAlchemy -------------- SQLAlchemy is the layer the code talks to, so the same code runs on SQLite (local development and tests) and PostgreSQL (the clean layer). Plain ``sqlite3`` would work for a single local file, but the design is meant to be built on (see the :doc:`quality bar `), so SQLAlchemy is used for these reasons: - **The engine owns the connection.** One engine per connection URL (for example ``sqlite:///db/trade.db``) holds the connection pool; sessions, ``create_all`` and queries all go through it. - **The database stays swappable.** Code sees ORM models and sessions, not SQLite-specific SQL, so moving a database to PostgreSQL is a connection-string change (see :doc:`/database/setup`). - **One engine per logical database.** The four databases below each need their own engine and declarative base, whichever server hosts them. - **Explicit transactions.** Sessions make every write an explicit, rolled-back-on-failure unit of work, as the EOD batch and trade booking require. Why 4 databases --------------- Each logical database has its own SQLAlchemy engine and declarative base (SQLite file under ``db/`` by default). .. list-table:: :header-rows: 1 * - DB - File - Owns * - Reference - ``reference.db`` - Instruments (all asset classes), books, traders; static and slow-changing * - Market - ``market.db`` - Daily OHLC prices per instrument * - Trade - ``trade.db`` - Trades (the blotter) and computed EOD P&L * - Risk - ``risk.db`` - Position limits, EOD risk metrics, limit breaches **Reasons for the split** (each maps to a real operational need): .. list-table:: :header-rows: 1 * - Reason - Effect * - Different owners and change rates - Reference data is edited rarely by ops; market data is loaded by a feed job; trades are written by users; risk is written by a batch. Each can be deployed, migrated and versioned independently. * - Different growth and retention - Market and EOD tables grow with time and can be archived or partitioned without touching the small, critical trade and reference tables. Adding crypto or equities multiplies market-data volume, not trade or reference volume. * - Independent scaling - A database can move to its own host, or get read replicas for reporting, without moving the others. * - Access control - Grants can differ per database (e.g. risk can read trades but not write them). * - Failure isolation - A market-data reload or risk recalculation cannot lock the trade tables. **Trade-offs accepted** - **No cross-database foreign keys** (SQLite cannot enforce them, and most multi-system deployments cannot either). Linkage is by business keys (``symbol``, ``book``, ``trader``), joined at the application and reporting layer. Referential checks live in services (e.g. a trade is only booked for an existing, active instrument, book and trader). Foreign keys *within* one database are used. - **No cross-database transactions.** The EOD batch writes P&L (trade DB) and then risk (risk DB) in separate commits. Each step is safe to re-run because it replaces that ``calc_date``'s rows, but a crash between the two leaves them temporarily inconsistent. The planned ``eod_runs`` ledger (roadmap item 8) makes this explicit and detectable. If operational simplicity later outweighs the isolation benefits, the databases can be consolidated into one Postgres database with four schemas; the per-database engines and the repository layer make that a configuration change. Details per database: :doc:`/database/index`.