Database Schema and Models
Comprehensive reference of all PostgreSQL tables, SQLAlchemy ORM models, relationships, indexes, and constraints managed by Alembic migrations.
DepthSight uses PostgreSQL 15 as its persistent data store. Schemas are defined in api/models.py (1,460 lines, 50 ORM models) using SQLAlchemy ORM 2.0 with declarative mapping, and migrated using Alembic. The database spans these models covering user management, strategy storage, trading history, gamification, community features, and ML pipeline artifacts.
Core Entity-Relationship Diagram
User & Account Models
User (users table, lines 27–128)
The central entity linking all platform features:
| Column | Type | Constraints | Description |
|---|---|---|---|
id | Integer | PK | Auto-increment user ID |
username | String | UNIQUE, INDEX, NOT NULL | Display name |
email | String | UNIQUE, INDEX, NOT NULL | Login credential |
hashed_password | String | NOT NULL | bcrypt hash |
plan | String | DEFAULT "free" | Subscription tier |
plan_expires_at | DateTime | NULLABLE | Plan expiration |
xp | Integer | DEFAULT 0 | Gamification XP |
level | Integer | DEFAULT 1 | Gamification level |
role | String | DEFAULT "user" | "user" or "admin" |
referral_code | String | UNIQUE, INDEX | Affiliate tracking |
tradingview_webhook_token | String | UNIQUE, INDEX | TV integration |
referred_by_user_id | Integer | FK -> users.id | Referrer tree |
symbol_selection_config | JSON | NULLABLE | DYNAMIC/STATIC/ORACLE config |
is_active | Boolean | DEFAULT False | Account status |
Relationships:
config→AppConfig(one-to-one)api_keys→ApiKey(one-to-many)strategy_configs→StrategyConfig(one-to-many)backtest_runs→BacktestRun(one-to-many)referrer→User(self-referential viareferred_by_user_id)achievements→UserAchievement(one-to-many)
AppConfig (app_configs table, lines 270–287)
Stores global user settings as JSON documents for schema flexibility:
| Column | Type | Description |
|---|---|---|
risk_management | JSON | Daily stop loss, max drawdown, blacklist rules |
backtest_risk_management | JSON | Backtest-specific risk overrides |
notifications | JSON | Telegram chat IDs, notification toggles |
data_sources | JSON | Preferred exchange data feeds |
exchange_settings | JSON | Exchange-specific config (testnet, leverage) |
ApiKey (api_keys table, lines 336–356)
Stores encrypted exchange API credentials:
| Column | Type | Description |
|---|---|---|
encrypted_api_key | String | Fernet-encrypted API key |
encrypted_api_secret | String | Fernet-encrypted API secret |
api_key_hash | String(64) | SHA-256 hash for deduplication (UNIQUE) |
key_prefix | String | First 4 chars of raw key (for UI display) |
exchange | String | "binance", "bybit", etc. |
status | String | "untested", "valid", "invalid" |
is_active | Boolean | Whether key is enabled for trading |
Strategy & Trading Models
StrategyConfig (strategy_configs table, lines 290–333)
The core strategy definition created in the visual builder:
| Column | Type | Description |
|---|---|---|
config_data | JSON | Complete nested block tree (filters, entry conditions, params) |
symbol_selection_mode | String | "STATIC", "DYNAMIC", or "ORACLE" |
symbols | JSON | Whitelist of trading symbols |
use_ml_confirmation | Boolean | Whether to gate signals with ML pipeline |
foundation_weights | JSON | Weight configuration for foundation system |
oracle_regime | Integer (nullable) | Current GMM regime (updated live) |
oracle_confidence | Float (nullable) | GMM prediction confidence |
parent_strategy_id | String (nullable) | FK → strategy_configs.id (version tree) |
generation | Integer | DEFAULT 1 (version counter) |
Trade (trades table, lines 376–443)
Records every completed trade:
| Column | Type | Description |
|---|---|---|
trade_uuid | String | UNIQUE, INDEX — Exchange/client order ID |
symbol | String | INDEX — e.g., "BTCUSDT" |
direction | String | "LONG" or "SHORT" |
entry_price | Float | Average entry fill price |
exit_price | Float | Average exit fill price |
pnl | Float | Realized PnL |
commission | Float | Total fees paid |
exit_reason | String | "STOP_LOSS", "TAKE_PROFIT", "MANUAL", etc. |
trade_mode | String(10) | INDEX — "LIVE" or "PAPER" |
signal_details_json | JSON | Full signal context (conditions triggered) |
max_floating_profit | Float | Best unrealized PnL during trade |
max_floating_loss | Float | Worst unrealized PnL during trade |
TradeAnalytics (trade_analytics table, lines 449–469)
Denormalized analytics for fast querying without computing from raw trades:
| Column | Type | Description |
|---|---|---|
used_foundations | JSON | Array of triggered foundation block names |
used_filters | JSON | Array of active filter blocks |
used_indicators | JSON | Array of indicators used in the strategy |
win_rate_contribution | Integer | Impact of this trade on overall win rate |
profit_factor_gross_profit / profit_factor_gross_loss | Float | Aggregate tracking |
Backtesting Models
BacktestRun (backtest_runs table, lines 488–518)
| Column | Type | Description |
|---|---|---|
strategy_name | String | INDEX — Human-readable strategy name |
symbol | String | INDEX — Target trading pair |
market_type | String | "futures_usdtm" or "spot" |
start_date / end_date | DateTime | Backtest window |
initial_balance | Float | Starting capital |
parameters_json | JSON | Strategy parameters used |
kpi_results_json | JSON | Full KPI results (Sharpe, PnL, drawdown) |
equity_curve_json | JSON | Equity curve for chart rendering |
analytics_report_json | JSON | Detailed analytics report |
BacktestTrade (backtest_trades table, lines 542–581)
Tick-level trade records with L2 slippage tracking:
| Column | Description |
|---|---|
client_order_id | UNIQUE, INDEX |
l2_ideal_entry_price | Theoretical fill without slippage |
l2_entry_slippage_usd | Slippage incurred at entry |
l2_entry_filled_quantity | Actual filled quantity at entry |
GeneticRun & FoundStrategy (genetic_runs, found_strategies)
Tracks genetic optimization sessions and their top-performing results:
| GeneticRun Column | Description |
|---|---|
celery_task_id | INDEX |
config_json | Full optimization config |
progress | JSON |
| FoundStrategy Column | Description |
|---|---|
rank | Position in Hall of Fame |
strategy_json | Complete evolved strategy JSON |
fitness_score | Numeric fitness value |
kpis_json | In-Sample and Out-of-Sample KPIs |
ML Pipeline Models
DatasetRun & TrainingRun (lines 627–689)
Tracks ML dataset creation and model training:
| DatasetRun | TrainingRun |
|---|---|
file_path — Parquet dataset location | dataset_id — FK to DatasetRun |
feature_list — JSON of columns used | model_path — Saved model file location |
dataset_shape — (rows, cols) | report_path — Training metrics report |
SymbolStrategyPerformance (lines 692–713)
Persists the dynamic risk multiplier state across restarts:
Sources:Key columns: trade_results_buffer_json (serialized deque of recent trade results), current_risk_multiplier_index, last_penalty_timestamp, total_trades_for_assessment.
Gamification & Community Models
Achievement / UserAchievement
38 mining achievements with XP rewards and rarity tiers, defined in api/gamification.py as MINING_ACHIEVEMENTS and seeded by the Alembic migration a2b3c4d5e6f7_add_mining_achievements.py:
| Rarity | XP Reward | Examples |
|---|---|---|
| COMMON | 50–150 XP | "Genesis", "First Spark" |
| RARE | 200–500 XP | "Golden Ticket", "Quarry Titan" |
| EPIC | 500–1,500 XP | "Crypto Whale", "Market Tempest" |
| LEGENDARY | 2,000–5,000 XP | "King of the Hill", "Market Maker" |
Gene / UserGene
Encodes reusable strategy patterns ("genes") that users can discover, collect, and combine:
| Gene Column | Description |
|---|---|
components | JSON array of block configurations |
rarity | Discovery probability (100 = common, 1 = legendary) |
first_discovered_by | FK → users.id |
PhantomTrade (phantom_trades, lines 904–975)
Tracks missed opportunity analysis — what would have happened if a breakeven exit was not triggered:
| Column | Description |
|---|---|
real_pnl_pct / real_pnl_usd | Actual trade PnL |
phantom_pnl_pct / phantom_pnl_usd | PnL if BE exit was ignored |
mfe_after_be / mae_after_be | Max favorable/adverse excursion post-BE |
candles_to_resolution | How many candles until exit |
Federated Mining Models
HubNode (hub_nodes, lines 1128–1173)
A federated node identity in the mining network — a miner or a mining server:
| Column | Type | Description |
|---|---|---|
node_uuid | String(36) UNIQ IX | Node identity (deterministic UUIDv5 for wallet nodes) |
name | String(100) | Display name |
secret_hash | String(255) | Hash of the telemetry secret |
ip_address, latitude, longitude, city, country | – | Geolocation captured at registration/ping |
version | String(50) | Client version |
last_ping / latency_ms | – | Heartbeat metadata |
is_banned | Boolean | Banned nodes are rejected at auth |
node_referral_code | String(50) UNIQUE IX | DSN-REF-... referral code |
referrer_node_uuid | FK→hub_nodes | Optional referrer link |
total_mined | Float | Cumulative $DEPTH earned |
has_welcome_bonus | Boolean | Welcome-bonus milestone granted |
weex_uid | String(50) IX | Weex identity for bonus/UUID caps |
bybit_uid / okx_uid / bitget_uid | String IX | Exchange-account binding for broker verification |
is_operator | Boolean | Fee-root (operator) node flag |
is_mining_server | Boolean | Only servers may be a telemetry source_node_uuid |
public_domain / wallet_address | String | Public host / bound EVM wallet (unique) |
public_plans | JSON | Publicly visible node plans |
HubTelemetryReport (hub_telemetry_reports, lines 1176–1236)
One row per mined trade:
| Column | Type | Description |
|---|---|---|
symbol, direction, entry_price, exit_price | String/Float | Trade facts |
pnl_percent, trade_duration_sec, exit_reason | – | Outcome metadata |
trade_mode | String | LIVE required for mining |
strategy_blocks / market_context | JSON | Strategy + market context (insights) |
node_uuid | FK IX | Attributed miner node |
source_node_uuid | FK IX | Mining server (commission target) |
exchange_id, market_type | – | Rebate lookup key |
broker_trade_id | String(100) UNIQUE | Dedup key |
entry_broker_trade_ids / close_broker_trade_ids | JSON | Order-id trail |
trade_volume_usdt, estimated_rebate_usdt | Float | Rebate basis |
score | Float | Anti-abuse score |
is_verified, verification_status, verified_at | – | PENDING/VERIFIED/SKIPPED/LOCAL_ONLY/SENT |
verified_volume_usdt, verification_error | – | Broker verification result |
is_mining_eligible | Boolean | Eligible for rewards |
reward_tokens / epoch_date | – | Epoch attribution |
MiningEpoch (mining_epochs, lines 1266–1275)
One row per finalized day: epoch_date PK, daily_emission, total_rebate_pool, total_distributed, participating_nodes, status (open/finalized), processed_at.
MiningLedger (mining_ledger, lines 1239–1263)
Per-node daily reward breakdown — node_uuid + epoch_date, base_reward, referral_bonus, welcome_bonus, boost_multiplier, total_reward, total_rebate_usdt, verified_trades_count (UNIQUE node_uuid,epoch_date).
MiningConfig (mining_config, lines 1278–1313)
Global mining parameters: is_mining_enabled, eligible_exchanges JSON, daily_emission_base (547945.21), halving_interval_days, launch_date, min_trade_duration_sec, min_trade_pnl_abs, min_price_movement_percent (default 0.15), quality_gate_operator (default "AND"), referral_mining_boost, rebate_rates JSON, exchange_multipliers JSON, total_operator_fee_collected.
NodeMiningConfig (node_mining_config, lines 1316–1328)
id=1 singleton making the user/server reward share: is_global_mining_enabled, user_reward_share_percent (default 75).
LocalUserMiningStats (local_user_mining_stats, lines 1331–1345)
Per-user local aggregates: total_trade_volume_usdt, estimated_rebate_usdt (updated as trades close).
HubServerConfig (hub_server_configs, lines 1348–1361)
node_uuid (PK, FK) + user_reward_share_percent — the per-server reward-share override used when the server commissions a report.
Index Summary
| Table | Key Indexes |
|---|---|
| users | username UNIQ, email UNIQ, referral_code UNIQ, tradingview_webhook_token UNIQ |
| strategy_configs | name, user_id, parent_strategy_id |
| trades | trade_uuid UNIQ, symbol, timestamp_close, trade_mode |
| backtest_runs | user_id, strategy_name, symbol, status |
| backtest_trades | client_order_id UNIQ, backtest_run_id |
| api_keys | api_key_hash UNIQ |
| SymbolStrategyPerformance | UNIQUE(user_id, symbol, strategy_name) |
| PaperWallet | UNIQUE(user_id, asset) |
| SharedBacktest | public_slug UNIQ |
| UserAchievement | UNIQUE(user_id, achievement_id) |
| HubNode | node_uuid UNIQ |
| hub_telemetry_reports | node_uuid, source_node_uuid, broker_trade_id UNIQ, epoch_date, verification_status, created_at |
| mining_ledger | UNIQUE(node_uuid, epoch_date) |
| mining_epochs | epoch_date PK |
| mining_config | singleton id=1 |
| node_mining_config | singleton id=1 |
| local_user_mining_stats | user_id PK |
| hub_server_configs | node_uuid PK |
Mobile PWA Client
Comprehensive technical guide to the DepthSight mobile PWA — state-machine navigation, strategy editor, AI chat, offline support via service worker, push notifications, and Google OAuth flow.
Exchange Executor Abstraction
Deep dive into the ExchangeExecutor Protocol, CCXT integration with exchange-specific adaptations, rate limiting, order placement pipeline, and the Paper Trading sandbox simulator.