Data architecture

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 Database; configuration and running the servers is in Setup: configuration, PostgreSQL and Redis.

Data tiers#

Market data moves through three stores. Each has one job and data only flows forward (Redis -> PostgreSQL -> DuckDB).

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 Market database (market.db)).

  • 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 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 Setup: configuration, PostgreSQL and Redis).

  • 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).

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):

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: Database.

Contents