Nexus engineering · Deep dives
A market-data store has to preserve what a value means, where it came from and when it could have been known. Nexus combines relational modelling, controlled schema changes, time-series storage and recovery procedures to make that data usable by research and trading.
The core platform uses hand-written SQL. Hermes, the alert service, uses SQLAlchemy's object-relational mapper and has its own PostgreSQL deployment.
Queries, bulk writes and explicit transaction boundaries.
marketPrice bars in time-series tables
referenceInstruments & calendarseventsEarnings, filings & newsopsWork board & deployment recordsSeparate databases hold service state:
nexus_sim · researchnexus_trader · executionnexus_pms · portfoliosnexus_risk · riskEach service connects with its own database role and permissions.
Python models, relationships and sessions.
messagesAccepted alert requestsdeliveriesAttempts and delivery stateoutboxWork waiting to be sentHermes owns this separate store. Its ORM models also support SQLite for local use.
Direct SQL keeps bulk upserts, time-series functions and locking clauses visible in the data path. Hermes's ORM models suit messages and their related deliveries, and support SQLite for local use as well as PostgreSQL. SQLAlchemy can also run SQL without the ORM; the choice is about how each service expresses its work.
A database contains schemas, and a schema contains tables. These are four examples within market_data; nexus_sim and nexus_trader are separate databases. Schemas group related objects, while database grants control access.
| Schema | What it holds |
|---|---|
market | Price bars across providers and frequencies, including TimescaleDB hypertables such as market.prices_1min. |
reference | Instruments, trading calendars, corporate actions, futures specifications and roll rules. |
events | Earnings calendars and results, filings, news and ratings changes. |
ops | Operational records, including agent work items, leases and deployment history. |
A price bar's identity includes its ticker, timestamp, price side, provider and feed. Conflict rules determine whether a repeat is ignored or an allowed update. Foreign keys connect datasets, cleaning operations and derived series; constraints protect the meaning of a row.
Currency, price convention, provider and calendar matter as much as the numeric value. Services write through their interfaces or allow-listed jobs with scoped database roles. A schema groups objects; permissions decide who can change them.
03 · Change management
The data service's migrations are hand-written and reviewed with the application code. Each revision names its predecessor; Alembic compares that history with the revision recorded in the database and applies the pending changes.
revision Arevision Brevision CCode carries the change history.Read the installed database revision and the incoming revision graph. A deploy with no pending migration skips the snapshot requirement.
For a migration-bearing update of the existing market store, the data-api preflight requires a recent pre-migration backup artifact before proceeding.
Run the pending revisions through Alembic and SQLAlchemy's database engine. Schema changes run in the deployment path, outside normal API requests.
Record the applied revision, restart the service through the deploy process and check its health against the new schema.
The data service applies a 30s default DDL lock timeout from connection establishment. A waiting schema change can otherwise queue later readers behind it. This bounds the wait to acquire a lock; it is not a thirty-second limit on the migration's execution.
The market-data service has its own revision chain. Research components, including the simulation ledger, audit log, job queue and model registry, use separate Alembic version tables. Their deployment applies the chains in order and stops if one fails.
A concrete example: the minute-bar migration widened future chunks from daily to monthly intervals. It explicitly left existing chunks alone; reorganising old history is a separate maintenance operation. Alembic versions that decision, while a backup protects the data. A downgrade() function alone cannot recover deleted rows.
The data service's Alembic environment uses a SQLAlchemy engine with the psycopg2 driver and target_metadata=None. Migrations express deliberate SQL changes rather than deriving the production schema automatically from ORM models. The snapshot and lock controls described here are the data service's controls; each service has its own migration setup.
04 · Historical knowledge
A contract specification can be announced weeks before it takes effect, or corrected after the fact. A historical query needs both an effective date and a knowledge cutoff. Reproducing what Nexus itself had captured adds a third clock.
valid_from · valid_toThe interval in which the fact applies. The end is exclusive; an open end is stored explicitly.
published_at · available_atPublication and availability are recorded separately. The as-known query bounds available_at.
observed_atAn independent cutoff can restrict the result to vintages Nexus had actually captured by then.
ICE Futures U.S. · Russell 2000 futures · symbol TF. ICE announced the multiplier reduction on 21 October 2016, effective for trade date 5 December 2016. The same index-point move then had half the dollar value per contract.
Russell 2000 is in Nexus's enabled IG market universe as RTY=F. This event concerns the historical ICE contract. CME's separate RTY contract launched in July 2017 with a $50 multiplier; these exchange multipliers do not set IG's stake per point.
Scroll the table to compare the terms and their effective dates →
| Terms | USD / index point | Applies to trade dates | Notice date | For this query |
|---|---|---|---|---|
| Before conversion | $100 | Before 5 Dec 2016 | 21 Oct 2016 | Selected |
| After conversion | $50 | From 5 Dec 2016 | 21 Oct 2016 | Outside effective date |
The 2 December trade date still uses $100 per index point, even when viewed after the conversion. Applying the later $50 multiplier would halve the historical per-contract exposure incorrectly.
Existing positions were doubled at conversion: one old contract became two new contracts, preserving the exposure at a given index level. Position history and specification history therefore have to agree.
Sources: ICE announcement, 21 October 2016; clearing conversion notice, 26 October 2016; rule filing, 8 November 2016; CME RTY launch notice, July 2017.
This example uses the terms in the dated ICE announcement. Before that notice, it has no eligible evidence; it does not infer what earlier sources would have said. The announcement was subject to regulatory review. The controls compare dates before or after publication, without inventing an intraday release time. No historical Nexus capture timestamp is evidenced here, so a captured-vintage replay remains unproven.
The futures-specification structure records source documents, document and content hashes, revision sequence and the previous revision's identifier. A database trigger rejects updates and deletes. Correcting a fact creates a new revision with its own provenance.
The reader filters by effective time and knowledge time, with an optional captured-vintage cutoff. It selects the latest eligible revision for each sourced interval and reports missing coverage, overlap, deletion, poor quality or disagreement instead of silently substituting today's value.
valid_from <= :as_of
AND (valid_to IS NULL OR :as_of < valid_to)
AND available_at <= :known_at
AND observed_at <= :vintage_at -- when replaying captured knowledgeFiltering is followed by revision selection and coverage checks. “Latest” is evaluated within the eligible history, not across every revision now in the database.
05 · Data management
The cleaning model preserves the source bars and records how a usable series was produced. Source selection, validation, correction and derivation are distinct operations with their own evidence.
Keep provider, feed, ticker, frequency and price side attached to each dataset. Repeated ingestion follows the table's explicit conflict rules.
Cleaning operations retain parameters, affected counts and before/after statistics. Quarantine links excluded bars to the operation that excluded them.
Materialised clean tables are derived from raw data and the recorded operations. Corrections and exclusions can be reversed through the cleaning workflow.
Registered lineage connects child datasets to their parents and resampling rules, so downstream series can be regenerated when their inputs change.
Which source supplied the bar? Which operation excluded or corrected it? Which derived series depends on it? Dataset identities, operation records, quarantine and lineage give those questions a place in the data model.
A row's ingest timestamp is not automatically its publication time or a complete revision history. An append, an in-place update and a rebuilt series can give the same timestamp column different meanings. Historical consumers must use the semantics of their actual writer.
The deeper data-quality workflow is covered in Validate and clean; instrument identity, calendars and corporate actions are covered in Reference data.
06 · Physical design
TimescaleDB keeps time-series tables inside PostgreSQL. The physical design uses different time intervals for different bar densities, with compression grouped around the series that queries read.
Scale figures measured on 28 October 2026 and maintained in the site's figure register. Bid, mid and offer are separate bars. The database footprint and compressed-chunk ratio measure different scopes.
| Price table | Chunk interval | Design consideration |
|---|---|---|
prices_1sec | 1 day | Dense live capture gets a shorter time partition. |
prices_1min | 30 days | Deep minute history needs fewer partitions than daily chunks would create. |
prices_hourly | 90 days | Coarser bars fit a longer span per chunk. |
prices_daily | 365 days | Long history stays in a manageable number of partitions. |
The inspected price tables segment compressed data by provider_code, ticker and order it by ts DESC. Their compression policy uses a seven-day threshold. Eligibility is based on the chunk's time range, so a bar in a still-recent chunk is not necessarily compressed on its eighth day.
Time predicates help exclude irrelevant chunks. Too many small chunks increase catalogue and lock overhead; broad updates of compressed history can require substantial decompression. Backfills and repairs use bounded ranges and transaction sizes appropriate to the dataset.
Chunk intervals and compression keys above were checked in the production catalogue on 2 November 2026. Changing a chunk interval affects new chunks; existing history needs an explicit maintenance plan. Compression saves space while retaining data, and is separate from any decision to archive or delete it.
07 · Recovery
The backup service takes online PostgreSQL dumps with daily, weekly and monthly retention, with more frequent protection for selected trading and capture data. Copies are shipped offsite and checked there; scheduled restores exercise the path back into a database.
Write timestamped backup artifacts and record their outcome. A failed job reports through the central alert path.
Record checksums and compare the offsite bytes with the shipped artifacts. Integrity checks answer whether the copy survived transfer.
A weekly database smoke restore and a monthly full TimescaleDB restore use scratch targets. The Timescale path pins the extension version and runs its pre- and post-restore hooks.
The Timescale drill compares hypertable and chunk inventories with the source. These structural checks provide evidence beyond a successful file copy or process exit.
A dump selecting only the parent table can omit the rows held in child chunks. The market-store recovery procedure therefore restores the full database through the Timescale-aware path and checks the restored inventory.
A checksum verifies bytes. A restore verifies that the artifact can be loaded; an inventory checks its structure. Application queries, roles and permissions still need checking before a recovered service is ready to run.
Historical as-of queries reconstruct knowledge from stored versions. Disaster recovery reconstructs a database from backups; its recovery granularity follows the available backup artifacts. The operating schedule and retention policy are detailed in Production: backups.