Trade Engine — v1 Spec#

A front-to-back trading system built in Python + SQLAlchemy with a Flask front end. The schema is designed for multiple asset classes (FX, crypto, bonds and equities), but v1 rolls out with one simulated FX pair (``EURUSD``). v1 is the first release of a production-intent system: its scope is deliberately small, but its design (layering, schema, validation, tests) is meant to be built on rather than thrown away.

This file is the overview and the source of truth for scope, architecture and status. Detail lives in the documents below.

Documentation map#

Topic	Document
Data tiers, why SQLAlchemy, why four databases	Data architecture
Connection settings, PostgreSQL, running Redis in Docker on Windows	Setup: configuration, PostgreSQL and Redis
Schema conventions, cross-cutting decisions, indexing and scalability	Database
Reference data schema (instruments, books, traders)	Reference database (reference.db)
Market data schema (daily prices)	Market database (market.db)
Trade data schema (trades, EOD P&L)	Trade database (trade.db)
Risk data schema (limits, metrics, breaches)	Risk database (risk.db)
Extending the schema to FX, crypto, bonds and equities	Multi-asset design
Schema changes required before real use	Schema hardening roadmap
Why and how data is simulated	Simulated data: why and how
P&L method and the EOD batch	P&L and the EOD batch
Risk calculations (notional, VaR, limits)	Risk calculations
Reporting (the read side)	Reporting
Web layer (Flask API)	Web layer
How the repo, environment and GitHub repository were set up (GitHub CLI, venv)	How this repo was built
How to build, view and edit these docs (Sphinx, Book theme)	How to build these docs

Quality bar#

“Not a prototype” means every number can be explained, reproduced and traced. Status uses Implemented (in the code today) and Required (must be done before the system is relied on for real positions; see the roadmap and Known limitations).

Concern	Commitment	Status
Correctness	Formulas documented and unit-tested in isolation (`domain/`)	Implemented
Correctness	Money and quantities stored and computed as exact decimals	Required
Data labelling	Every price row is explicitly `OBSERVED` or `SIMULATED`; the database rejects any other value and the loader refuses to guess	Implemented
Data labelling	The origin label propagates to derived results (P&L, risk)	Required
Reproducibility	EOD batches are idempotent per `calc_date` (replace, not append)	Implemented
Reproducibility	Every EOD run recorded (inputs, status) and re-creatable as-of a date	Required
Auditability	Trades append-only; amendments and cancels are new records with who, when and why	Required
Data integrity	Input validated at the boundary (CSV, requests, CLI)	Implemented
Data integrity	Invariants enforced by the database, not only by Python	Partly implemented (asset class, price multiplier, data origin)
Security	Web CSV loads restricted to `data/`, localhost bind, debug off by default	Implemented
Security	Authentication and authorization, CSRF, TLS, secrets management	Required
Operability	Logging; per-database connection strings from the environment	Implemented
Operability	Scheduler with run locking, alerting, backups with tested restores, CI	Required
Change control	Versioned schema migrations (Alembic)	Required

Scope (v1)#

Asset: one simulated FX pair, configurable (EURUSD by default). The schema can represent FX, crypto, bonds and equities (Multi-asset design); only FX is implemented and seeded.
Market data: daily OHLC loaded from CSV, staged raw in Redis, then validated and committed to the market database, and labelled SIMULATED or OBSERVED at load time. The shipped data/raw/EURUSD.csv is simulated (Simulated data: why and how). No live feed.
Trade entry: manual, via the Flask API (you act as trader and booker).
P&L: end-of-day batch, weighted-average cost, realized plus unrealized, in the instrument’s quote currency (P&L and the EOD batch).
Risk: position and notional limits plus simple historical 1-day 95% VaR; breaches are logged. USD-quoted instruments only (Risk calculations).
Blotter: not a separate database; a reporting query over the trade database with reference data joined in (Reporting).

Architecture#

web / scripts  ->  services  ->  repositories  ->  models / db
                       |
                       +------>  domain (pure logic: P&L, risk, validation)

Layer	Package	Responsibility
Entry points	`web/`, `scripts/`	Parse input, call services, present output. No business rules.
Services	`services/`	Use cases: orchestrate repositories and domain logic across the databases.
Domain	`domain/`	Pure logic with no database or Flask imports; unit-tested directly.
Repositories	`repositories/`	Data access, one per database.
Persistence	`db/`, `models/`	One engine and declarative base per database; SQLAlchemy models.
Wiring	`config.py`, `container.py`	Settings from the environment; composition root. No import-time side effects.

Data and databases#

Market data moves through three stores, and data only flows forward:

Redis stages raw, unvalidated CSV batches.
PostgreSQL (SQLite files by default for local development and tests) holds the validated, typed, provenance-labelled data in four logical databases: reference, market, trade and risk.
DuckDB holds a read-only analytics snapshot exported from the clean data.

See Data architecture for the tiers and the reasons for the design, and Setup: configuration, PostgreSQL and Redis for configuration, PostgreSQL and running Redis in Docker on Windows.

Project layout#

Installable application code: src/trade_engine/ (domain/, db/, models/, repositories/, services/, staging/, analytics/, web/, quant/).
Executable batch jobs and the Flask launcher: scripts/.
Original input files: data/raw/; generated data: data/generated/. Note that the shipped data/raw/EURUSD.csv is itself simulated.
SQLite and DuckDB files: db/ (not in source control).
PostgreSQL and Redis setup: docker-compose.yml, docker-compose.redis.yml and docker/postgres/.
Documentation: docs/, with this file as the entry point.
Tests: tests/.

Batch jobs (`scripts/`)#

init_db.py: create all tables across the four databases and seed one instrument, book, trader and limit.
load_market_data.py: stage a price CSV in Redis and load it into the market database; --origin OBSERVED|SIMULATED is required. --stage-only stops after staging.
process_staging.py: validate and commit staged batches (one pass, or --watch SECONDS to keep polling); invalid batches go to the rejected stream.
export_analytics.py: rebuild the DuckDB analytics file from the clean databases.
seed_sample_trades.py: insert sample trades once.
run_eod.py: compute and persist P&L and risk for a given (or the latest) date.
run_app.py: start the Flask development server on localhost.

Known limitations (v1)#

Deliberate scope limits, recorded as decisions rather than shortcuts:

Only FX is implemented and seeded, with a single simulated pair. The schema supports crypto, bonds and equities, but their subtype tables, fees, cash flows and corporate actions are not built.
No currency conversion: P&L is per currency, and risk accepts USD-quoted instruments only.
Weighted-average-cost P&L only; no FIFO or lot-level and no intraday P&L.
Daily CSV prices only; no real-time or streaming market data.
One price series per symbol with an unstated basis. Bid, ask and mid are not stored separately, so fills, liquidation-value marks and spread costs cannot be modelled yet. The design is in Market database (market.db) (roadmap item 15).
Redis staging covers market data only (trades are written directly), uses a single consumer, and has no tooling to replay dead-lettered batches yet.
The analytics export is a full refresh, not incremental, and the DuckDB file allows only one writer at a time.
Single-asset historical VaR only; no parametric or Monte Carlo VaR, stress testing or VaR backtesting.
No authentication or authorization on the Flask app; JSON only, no HTML views.
No pre-trade limit checks.
Schema managed with create_all; see the roadmap for migrations and the production-required schema changes.

Trade Engine — v1 Spec

Contents