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 ----------------- .. list-table:: :header-rows: 1 * - Topic - Document * - Data tiers, why SQLAlchemy, why four databases - :doc:`/database/architecture` * - Connection settings, PostgreSQL, running Redis in Docker on Windows - :doc:`/database/setup` * - Schema conventions, cross-cutting decisions, indexing and scalability - :doc:`/database/index` * - Reference data schema (instruments, books, traders) - :doc:`/database/reference` * - Market data schema (daily prices) - :doc:`/database/market` * - Trade data schema (trades, EOD P&L) - :doc:`/database/trade` * - Risk data schema (limits, metrics, breaches) - :doc:`/database/risk` * - Extending the schema to FX, crypto, bonds and equities - :doc:`/database/multi_asset` * - Schema changes required before real use - :doc:`/database/hardening_roadmap` * - Why and how data is simulated - :doc:`/simulation` * - P&L method and the EOD batch - :doc:`/pnl` * - Risk calculations (notional, VaR, limits) - :doc:`/risk` * - Reporting (the read side) - :doc:`/reporting` * - Web layer (Flask API) - :doc:`/web` * - How the repo, environment and GitHub repository were set up (GitHub CLI, venv) - :doc:`/appendix/repo_setup` * - How to build, view and edit these docs (Sphinx, Book theme) - :doc:`/appendix/build_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 :doc:`roadmap ` and :ref:`Known limitations `). .. list-table:: :header-rows: 1 * - 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 (:doc:`/database/multi_asset`); 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 (:doc:`/simulation`). 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 (:doc:`/pnl`). - **Risk:** position and notional limits plus simple historical 1-day 95% VaR; breaches are logged. USD-quoted instruments only (:doc:`/risk`). - **Blotter:** not a separate database; a reporting query over the trade database with reference data joined in (:doc:`/reporting`). Architecture ------------ .. code:: text web / scripts -> services -> repositories -> models / db | +------> domain (pure logic: P&L, risk, validation) .. list-table:: :header-rows: 1 * - 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 :doc:`/database/architecture` for the tiers and the reasons for the design, and :doc:`/database/setup` 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: 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 :doc:`/database/market` (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 :doc:`roadmap ` for migrations and the production-required schema changes.