Database Schema and Models

Comprehensive reference of all PostgreSQL tables, SQLAlchemy ORM models, relationships, indexes, and constraints managed by Alembic migrations.

⏱️ 9 min read📊 Level: Intermediate

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

Rendering diagram...

User & Account Models

User (users table, lines 27–128)

The central entity linking all platform features:

ColumnTypeConstraintsDescription
idIntegerPKAuto-increment user ID
usernameStringUNIQUE, INDEX, NOT NULLDisplay name
emailStringUNIQUE, INDEX, NOT NULLLogin credential
hashed_passwordStringNOT NULLbcrypt hash
planStringDEFAULT "free"Subscription tier
plan_expires_atDateTimeNULLABLEPlan expiration
xpIntegerDEFAULT 0Gamification XP
levelIntegerDEFAULT 1Gamification level
roleStringDEFAULT "user""user" or "admin"
referral_codeStringUNIQUE, INDEXAffiliate tracking
tradingview_webhook_tokenStringUNIQUE, INDEXTV integration
referred_by_user_idIntegerFK -> users.idReferrer tree
symbol_selection_configJSONNULLABLEDYNAMIC/STATIC/ORACLE config
is_activeBooleanDEFAULT FalseAccount 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 via referred_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:

ColumnTypeDescription
risk_managementJSONDaily stop loss, max drawdown, blacklist rules
backtest_risk_managementJSONBacktest-specific risk overrides
notificationsJSONTelegram chat IDs, notification toggles
data_sourcesJSONPreferred exchange data feeds
exchange_settingsJSONExchange-specific config (testnet, leverage)

ApiKey (api_keys table, lines 336–356)

Stores encrypted exchange API credentials:

ColumnTypeDescription
encrypted_api_keyStringFernet-encrypted API key
encrypted_api_secretStringFernet-encrypted API secret
api_key_hashString(64)SHA-256 hash for deduplication (UNIQUE)
key_prefixStringFirst 4 chars of raw key (for UI display)
exchangeString"binance", "bybit", etc.
statusString"untested", "valid", "invalid"
is_activeBooleanWhether key is enabled for trading

Strategy & Trading Models

StrategyConfig (strategy_configs table, lines 290–333)

The core strategy definition created in the visual builder:

ColumnTypeDescription
config_dataJSONComplete nested block tree (filters, entry conditions, params)
symbol_selection_modeString"STATIC", "DYNAMIC", or "ORACLE"
symbolsJSONWhitelist of trading symbols
use_ml_confirmationBooleanWhether to gate signals with ML pipeline
foundation_weightsJSONWeight configuration for foundation system
oracle_regimeInteger (nullable)Current GMM regime (updated live)
oracle_confidenceFloat (nullable)GMM prediction confidence
parent_strategy_idString (nullable)FK → strategy_configs.id (version tree)
generationIntegerDEFAULT 1 (version counter)

Trade (trades table, lines 376–443)

Records every completed trade:

ColumnTypeDescription
trade_uuidStringUNIQUE, INDEX — Exchange/client order ID
symbolStringINDEX — e.g., "BTCUSDT"
directionString"LONG" or "SHORT"
entry_priceFloatAverage entry fill price
exit_priceFloatAverage exit fill price
pnlFloatRealized PnL
commissionFloatTotal fees paid
exit_reasonString"STOP_LOSS", "TAKE_PROFIT", "MANUAL", etc.
trade_modeString(10)INDEX — "LIVE" or "PAPER"
signal_details_jsonJSONFull signal context (conditions triggered)
max_floating_profitFloatBest unrealized PnL during trade
max_floating_lossFloatWorst unrealized PnL during trade

TradeAnalytics (trade_analytics table, lines 449–469)

Denormalized analytics for fast querying without computing from raw trades:

ColumnTypeDescription
used_foundationsJSONArray of triggered foundation block names
used_filtersJSONArray of active filter blocks
used_indicatorsJSONArray of indicators used in the strategy
win_rate_contributionIntegerImpact of this trade on overall win rate
profit_factor_gross_profit / profit_factor_gross_lossFloatAggregate tracking

Backtesting Models

BacktestRun (backtest_runs table, lines 488–518)

ColumnTypeDescription
strategy_nameStringINDEX — Human-readable strategy name
symbolStringINDEX — Target trading pair
market_typeString"futures_usdtm" or "spot"
start_date / end_dateDateTimeBacktest window
initial_balanceFloatStarting capital
parameters_jsonJSONStrategy parameters used
kpi_results_jsonJSONFull KPI results (Sharpe, PnL, drawdown)
equity_curve_jsonJSONEquity curve for chart rendering
analytics_report_jsonJSONDetailed analytics report

BacktestTrade (backtest_trades table, lines 542–581)

Tick-level trade records with L2 slippage tracking:

ColumnDescription
client_order_idUNIQUE, INDEX
l2_ideal_entry_priceTheoretical fill without slippage
l2_entry_slippage_usdSlippage incurred at entry
l2_entry_filled_quantityActual filled quantity at entry

GeneticRun & FoundStrategy (genetic_runs, found_strategies)

Tracks genetic optimization sessions and their top-performing results:

GeneticRun ColumnDescription
celery_task_idINDEX
config_jsonFull optimization config
progressJSON
FoundStrategy ColumnDescription
rankPosition in Hall of Fame
strategy_jsonComplete evolved strategy JSON
fitness_scoreNumeric fitness value
kpis_jsonIn-Sample and Out-of-Sample KPIs

ML Pipeline Models

DatasetRun & TrainingRun (lines 627–689)

Tracks ML dataset creation and model training:

DatasetRunTrainingRun
file_path — Parquet dataset locationdataset_id — FK to DatasetRun
feature_list — JSON of columns usedmodel_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:

RarityXP RewardExamples
COMMON50–150 XP"Genesis", "First Spark"
RARE200–500 XP"Golden Ticket", "Quarry Titan"
EPIC500–1,500 XP"Crypto Whale", "Market Tempest"
LEGENDARY2,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 ColumnDescription
componentsJSON array of block configurations
rarityDiscovery probability (100 = common, 1 = legendary)
first_discovered_byFK → users.id

PhantomTrade (phantom_trades, lines 904–975)

Tracks missed opportunity analysis — what would have happened if a breakeven exit was not triggered:

ColumnDescription
real_pnl_pct / real_pnl_usdActual trade PnL
phantom_pnl_pct / phantom_pnl_usdPnL if BE exit was ignored
mfe_after_be / mae_after_beMax favorable/adverse excursion post-BE
candles_to_resolutionHow 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:

ColumnTypeDescription
node_uuidString(36) UNIQ IXNode identity (deterministic UUIDv5 for wallet nodes)
nameString(100)Display name
secret_hashString(255)Hash of the telemetry secret
ip_address, latitude, longitude, city, country–Geolocation captured at registration/ping
versionString(50)Client version
last_ping / latency_ms–Heartbeat metadata
is_bannedBooleanBanned nodes are rejected at auth
node_referral_codeString(50) UNIQUE IXDSN-REF-... referral code
referrer_node_uuidFK→hub_nodesOptional referrer link
total_minedFloatCumulative $DEPTH earned
has_welcome_bonusBooleanWelcome-bonus milestone granted
weex_uidString(50) IXWeex identity for bonus/UUID caps
bybit_uid / okx_uid / bitget_uidString IXExchange-account binding for broker verification
is_operatorBooleanFee-root (operator) node flag
is_mining_serverBooleanOnly servers may be a telemetry source_node_uuid
public_domain / wallet_addressStringPublic host / bound EVM wallet (unique)
public_plansJSONPublicly visible node plans

HubTelemetryReport (hub_telemetry_reports, lines 1176–1236)

One row per mined trade:

ColumnTypeDescription
symbol, direction, entry_price, exit_priceString/FloatTrade facts
pnl_percent, trade_duration_sec, exit_reason–Outcome metadata
trade_modeStringLIVE required for mining
strategy_blocks / market_contextJSONStrategy + market context (insights)
node_uuidFK IXAttributed miner node
source_node_uuidFK IXMining server (commission target)
exchange_id, market_type–Rebate lookup key
broker_trade_idString(100) UNIQUEDedup key
entry_broker_trade_ids / close_broker_trade_idsJSONOrder-id trail
trade_volume_usdt, estimated_rebate_usdtFloatRebate basis
scoreFloatAnti-abuse score
is_verified, verification_status, verified_at–PENDING/VERIFIED/SKIPPED/LOCAL_ONLY/SENT
verified_volume_usdt, verification_error–Broker verification result
is_mining_eligibleBooleanEligible 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

TableKey Indexes
usersusername UNIQ, email UNIQ, referral_code UNIQ, tradingview_webhook_token UNIQ
strategy_configsname, user_id, parent_strategy_id
tradestrade_uuid UNIQ, symbol, timestamp_close, trade_mode
backtest_runsuser_id, strategy_name, symbol, status
backtest_tradesclient_order_id UNIQ, backtest_run_id
api_keysapi_key_hash UNIQ
SymbolStrategyPerformanceUNIQUE(user_id, symbol, strategy_name)
PaperWalletUNIQUE(user_id, asset)
SharedBacktestpublic_slug UNIQ
UserAchievementUNIQUE(user_id, achievement_id)
HubNodenode_uuid UNIQ
hub_telemetry_reportsnode_uuid, source_node_uuid, broker_trade_id UNIQ, epoch_date, verification_status, created_at
mining_ledgerUNIQUE(node_uuid, epoch_date)
mining_epochsepoch_date PK
mining_configsingleton id=1
node_mining_configsingleton id=1
local_user_mining_statsuser_id PK
hub_server_configsnode_uuid PK