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,055 lines) using SQLAlchemy ORM 2.0 with declarative mapping, and migrated using Alembic. The database spans 28+ 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 24–105)
The central entity linking all platform features:
| Column | Type | Constraints | Description |
|---|---|---|---|
id | Integer | PK | Auto-increment user ID |
username | String(50) | UNIQUE, INDEX, NOT NULL | Display name |
email | String(255) | UNIQUE, INDEX, NOT NULL | Login credential |
hashed_password | String(255) | NOT NULL | bcrypt hash |
plan | String(20) | 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(20) | DEFAULT "user" | "user" or "admin" |
referral_code | String(20) | UNIQUE, INDEX | Affiliate tracking |
tradingview_webhook_token | String(255) | 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 True | 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)trades→Trade(one-to-many)referrer→User(self-referential viareferred_by_user_id)achievements→UserAchievement(one-to-many)
AppConfig (app_configs table, lines 198–213)
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 261–282)
Stores encrypted exchange API credentials:
| Column | Type | Description |
|---|---|---|
encrypted_api_key | String(512) | Fernet-encrypted API key |
encrypted_api_secret | String(1024) | Fernet-encrypted API secret |
api_key_hash | String(64) | SHA-256 hash for deduplication (UNIQUE) |
key_prefix | String(10) | First 4 chars of raw key (for UI display) |
exchange | String(50) | "binance", "bybit", etc. |
status | String(20) | "untested", "valid", "invalid" |
is_active | Boolean | Whether key is enabled for trading |
Strategy & Trading Models
StrategyConfig (strategy_configs table, lines 215–259)
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 | UUID (nullable) | FK → strategy_configs.id (version tree) |
generation | Integer | DEFAULT 1 (version counter) |
Trade (trades table, lines 284–352)
Records every completed trade:
| Column | Type | Description |
|---|---|---|
trade_uuid | String | UNIQUE, INDEX |
symbol | String(20) | INDEX |
direction | String(10) | "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(50) | "STOP_LOSS", "TAKE_PROFIT", "MANUAL", etc. |
trade_mode | String | INDEX |
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 357–378)
Denormalized analytics for fast querying without computing from raw trades:
| Column | 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 | Float |
profit_factor_gross_profit / gross_loss | Aggregate tracking |
Backtesting Models
BacktestRun (backtest_runs table, lines 396–427)
| Column | Type | Description |
|---|---|---|
strategy_name | String | INDEX |
symbol | String(20) | INDEX |
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 450–490)
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 535–598)
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 600–622)
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
30+ achievements with XP rewards and rarity tiers:
| Rarity | XP Reward | Examples |
|---|---|---|
| COMMON | 10 XP | "First Trade", "Run First Backtest" |
| RARE | 50 XP | "10 Profitable Trades in a Row" |
| EPIC | 100 XP | "100% Win Rate Month" |
| LEGENDARY | 500 XP | "Discover New Gene", "Top 10 Leaderboard" |
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 812–884)
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 1068–1106)
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(64) | SHA-256 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 |
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) |
HubTelemetryReport (hub_telemetry_reports, lines 1109–1160)
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 1194–1204)
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 1168–1192)
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 1207–1225)
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, referral_mining_boost, rebate_rates JSON, total_operator_fee_collected.
NodeMiningConfig (node_mining_config, lines 1228–1240)
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 1243–1257)
Per-user local aggregates: total_trade_volume_usdt, estimated_rebate_usdt (updated as trades close).
HubServerConfig (hub_server_configs, lines 1260–1273)
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.