Database Schema
Ground truth for the schema lives in gordon-migrate/migrations/. This page is a navigable inventory — for exact column definitions, consult the migration files directly.
38 migrations as of 2026-05-17. search_path = trading, market_data, public.
market_data schema
Written exclusively by gordon-data. Other services read via named views (migration 0019). gordon_lab_reader role has INSERT/UPDATE/DELETE revoked — any write attempt fails with InsufficientPrivilege.
| Table | Purpose |
|---|---|
spot_klines | 1m canonical klines from Binance spot; higher TFs precomputed by gordon-data from this source |
perp_klines | Binance perpetual futures klines |
funding_rates | 8h Binance funding rates per symbol |
open_interest | Hourly open interest snapshots |
metrics | Derived metrics: VPIN, long/short ratio, liquidation volumes |
fear_greed | Daily Fear & Greed index from alternative.me |
stablecoin_supply | Daily stablecoin supply ratio (SSR) from DefiLlama |
gamma_exposure | GEX aggregate snapshots (flip point, call/put walls, regime) from Deribit |
gamma_exposure_strikes | GEX per-strike profile for replay |
source_freshness | Per-source ingest liveness. Written by gordon-data scheduler; consumed by console via manager WS source_freshness channel. |
macro_data | FRED macro series (DXY, VIX, etc.) |
Named reader views (migration 0019)
Direct SELECT on market_data.* underlying tables is revoked for gordon_manager and gordon_risk roles. Access is via:
| View | Underlying source | Used by |
|---|---|---|
v_klines_reader | spot_klines | gordon-manager (backtest, warmup BFF) |
v_metrics_reader | metrics | gordon-manager, gordon-risk (VPIN breaker) |
v_macro_reader | macro_data | gordon-risk (MacroBreaker) |
v_funding_rates_reader | funding_rates | gordon-manager |
v_open_interest_reader | open_interest | gordon-manager |
v_gex_reader | gamma_exposure | gordon-manager (migration 0028) |
trading schema
Backed up hourly to srv-core via restic. Multiple writers per table (see Database configuration for the role matrix).
Bot lifecycle
| Table | Purpose |
|---|---|
bot_configs | Declarative bot config: desired_state, image_tag, strategy_params. Full-row lifecycle owned by gordon-manager; desired_state column also updated by gordon-risk (pause/resume). |
bot_leases | Advisory-lock lease per bot. holder_bot_id, fence_token, lease_key |
bot_deploys | Green/blue deploy state machine records |
bot_strategy_state | Persisted strategy state per bot (serialized Rust struct) |
symbol_subscriptions | Per-bot symbol subscription records |
service_deploys | Service-level deploy records (migration 0029) |
Order flow
| Table | Purpose |
|---|---|
order_intents | Intent queue from gordon-bot to gordon-executor |
orders | Order state machine: submitted → acked → filled/rejected/cancelled |
order_events | State-transition events per order |
fill_events | Fill records from the exchange. Keyed on deterministic trade_fingerprint. |
shadow_orders | Shadow compare orders during green/blue deploys (migration 0030) |
agg_trades | Aggregated trade records (migration 0032) |
Runs and analytics
| Table | Purpose |
|---|---|
runs | Trading sessions: backtest, paper, and live. Carries strategy_code_revision (migration 0038). |
trades | Individual fills aggregated to trade level |
roundtrips | Entry-exit pairs |
signals | Strategy signal log |
equity_points | Portfolio equity curve time series |
performance_snapshots | Aggregate metrics snapshots per run |
Risk and control
| Table | Purpose |
|---|---|
risk_state | Halt latch: halted boolean. Flips TRUE on flatten/breaker trip; clears on POST /risk/resume. |
risk_events | Circuit-breaker trip audit log. Written by gordon-risk only. |
risk_audit_log | Operator action audit: flatten, resume, pause. Written by gordon-risk only. |
risk_config | Runtime-tunable breaker thresholds (key/value store) |
bot_commands | Commands from gordon-risk to executor/bot (flatten, pause). At-least-once via NOTIFY cursor. |
bot_events | Lifecycle events from bots and executor (heartbeat, drain, fill, flatten step) |
positions | Current open positions. Updated by positions_apply_trade() trigger on trading.trades. |
reconcile_runs | Executor reconcile outcomes per startup |
pipeline_state | LISTEN/NOTIFY consumer cursor offsets per service/channel |
Daily notional caps (migration 0036)
| Table | Purpose |
|---|---|
daily_notional_per_bot | Rolling UTC-day notional per bot (optional enforcement — see EXECUTOR_CAP_REJECT_PER_BOT_DAILY) |
daily_notional_global | Rolling UTC-day notional across all bots (optional enforcement — see EXECUTOR_CAP_REJECT_GLOBAL_DAILY) |
bus schema
| Table | Purpose |
|---|---|
bus.outbox | Dual-write buffer for NATS delivery. Drain worker atomically reads and publishes to JetStream. |
Source of truth
gordon-migrate/migrations/ is the authoritative source. Run cargo test -p gordon-migrate --test grant_matrix to verify role privileges match the documented matrix.