Database
Single PostgreSQL instance. All v7 services connect via GORDON_DATABASE_URL (or a service-scoped DSN using a least-privilege role).
GORDON_DATABASE_URL=postgresql://gordon:gordon_dev@localhost:5432/gordonsearch_path = trading, market_data, public — queries use bare table names.
Schemas
| Schema | Backed up | Writers | Notes |
|---|---|---|---|
market_data | No (re-fetchable, ~7 GB) | gordon-data only | Sole writer enforced via gordon_lab_reader role revoking INSERT/UPDATE/DELETE |
trading | Yes — hourly restic to srv-core | gordon-executor, gordon-risk, gordon-manager, gordon-bot | Core business data |
bus | No | gordon-bus (outbox) | bus.outbox for NATS dual-write |
public | — | sqlx internals | _sqlx_migrations only |
DB roles (least-privilege)
Roles are provisioned by gordon-migrate. Each service connects using its own role.
| Role | Privileges | Used by |
|---|---|---|
gordon_data_writer | INSERT/UPDATE on market_data.* | gordon-data |
gordon_executor | INSERT/UPDATE on trading.orders, trading.trades, trading.order_events, trading.fill_events; INSERT + UPDATE on bus.outbox; EXECUTE on increment_fence | gordon-executor |
gordon_risk | SELECT on trading.*; INSERT on trading.risk_events, trading.risk_audit_log; column-level UPDATE on bot_configs.desired_state only | gordon-risk |
gordon_manager | INSERT on trading.bot_configs, trading.runs, trading.equity_points, trading.bot_deploys; reads via named views (v_klines_reader, v_metrics_reader, v_macro_reader, v_funding_rates_reader, v_open_interest_reader) | gordon-manager |
gordon_bot | INSERT on trading.order_intents, trading.bot_leases, trading.signals; advisory-lock-based lease | gordon-bot |
gordon_lab_reader | SELECT only — INSERT/UPDATE/DELETE revoked on all schemas | gordon-lab |
gordon_drift_reader | SELECT only — for drift-check tooling | drift scripts |
Roles and grants are defined in migration 0008_roles_and_grants.sql and extended by later migrations. The grant matrix is enforced by cargo test -p gordon-migrate --test grant_matrix.
Named reader views (migration 0019)
Gordon-manager and gordon-risk read market_data.* through named views rather than direct table access. The gordon_manager and gordon_risk roles have SELECT revoked on the underlying tables; access goes through:
| View | Purpose |
|---|---|
v_klines_reader | 1m + precomputed klines for backtest and bot warmup |
v_metrics_reader | Derivatives metrics (OI, funding z-scores) |
v_macro_reader | FRED macro data (DXY, VIX) |
v_funding_rates_reader | 8h funding rates |
v_open_interest_reader | OI snapshots |
v_gex_reader | Gamma exposure snapshots (migration 0028) |
market_data tables
| Table | Purpose |
|---|---|
spot_klines | 1m canonical klines from Binance spot; higher TFs precomputed by gordon-data |
perp_klines | Perpetual futures klines |
funding_rates | 8h Binance funding rates per symbol |
open_interest | Hourly OI snapshots |
metrics | Derived metrics (VPIN, long/short ratio, etc.) |
fear_greed | Daily Fear & Greed index from alternative.me |
stablecoin_supply | Daily stablecoin supply ratio (SSR) |
gamma_exposure | GEX aggregate snapshots (flip, walls, regime) |
gamma_exposure_strikes | GEX per-strike profile |
source_freshness | Per-source ingest liveness (written by gordon-data scheduler) |
macro_data | FRED macro series (DXY, VIX, etc.) |
trading tables
| Table | Purpose |
|---|---|
runs | Backtest, paper, and live trading sessions |
trades | Individual exchange fills |
roundtrips | Entry-exit pairs derived from trades |
signals | Strategy signal log |
equity_points | Portfolio equity curve over time |
performance_snapshots | Aggregate performance metrics per run |
orders | Order state machine (submitted → acked → filled/rejected/cancelled) |
order_intents | Intent queue from gordon-bot to gordon-executor |
order_events | State-transition events per order |
fill_events | Fill records from the exchange |
bot_configs | Bot lifecycle config (desired_state, image_tag, strategy_params) |
bot_leases | Advisory-lock lease table (one row per active bot) |
bot_deploys | Green/blue deploy state machine |
bot_strategy_state | Persisted strategy state per bot |
reconcile_runs | Executor reconcile results on restart |
risk_state | Halt latch (halted boolean), cleared by POST /risk/resume |
risk_events | Circuit-breaker trip audit log |
risk_audit_log | Operator action audit (flatten, resume, pause) |
risk_config | Runtime-tunable breaker thresholds |
bot_commands | Commands from gordon-risk to gordon-executor/bot (flatten, pause) |
bot_events | Lifecycle events published by bots and executor |
positions | Current open positions (updated by positions_apply_trade() trigger) |
pipeline_state | LISTEN/NOTIFY consumer cursor offsets |
symbol_subscriptions | Per-bot symbol subscription records |
service_deploys | Service-level deploy records |
shadow_orders | Shadow compare orders during green/blue deploy |
agg_trades | Aggregated trade data |
bus tables
| Table | Purpose |
|---|---|
bus.outbox | Dual-write buffer for NATS delivery. Gordon-bus leader drains this. |
Migrations
Source of truth: gordon-migrate/migrations/. 38 migrations as of 2026-05-17.
Latest migrations:
0036_daily_notional_caps.sql— per-bot and global UTC-day rolling notional cap tables0037_audit_event_flatten_crashed_during.sql— extend audit event types0038_runs_strategy_code_revision.sql— strategy code revision column on runs
Run via gordon-migrate one-shot container (ghcr.io/dlepaux/gordon-migrate). Services gate on service_completed_successfully in _sqlx_migrations.
Conventions
| Category | Convention | Example |
|---|---|---|
| Timestamps | BIGINT, UTC milliseconds | 1672531200000 |
| Symbols | Binance-native, no slash | BTCUSDT |
| Prices | DOUBLE PRECISION | 42150.50 |
| Quantities, fees | NUMERIC | 0.00150000 |
| Enum strings | Lowercase with CHECK constraints | 'long', 'short', 'running' |
| Foreign keys | ON DELETE CASCADE to runs(id) | All per-run tables |