Trading & Orders
Trading & Orders
Order management, trade execution, positions, and trading strategies.
17 tables in this group.
orders
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
user_id | uuid | No | |
portfolio_id | uuid | Yes | |
symbol | varchar(20) | No | |
side | varchar(10) | No | |
type | varchar(20) | No | |
status | varchar(20) | No | |
quantity | numeric | No | |
price | numeric | Yes | |
stop_price | numeric | Yes | |
executed_quantity | numeric | Yes | |
average_price | numeric | Yes | |
remaining_quantity | numeric | Yes | |
time_in_force | varchar(10) | Yes | |
expires_at | timestamp | Yes | |
reduce_only | bool | Yes | |
post_only | bool | Yes | |
exchange | varchar(50) | Yes | |
exchange_order_id | varchar(100) | Yes | |
created_at | timestamp | No | |
updated_at | timestamp | Yes | |
filled_at | timestamp | Yes | |
cancelled_at | timestamp | Yes | |
trading_metadata | json | Yes | |
client_order_id | varchar(100) | Yes | |
source | varchar(20) | No | 'manual'::character varying |
agent_id | uuid | Yes |
RLS Policies:
orders_delete_own— DELETE for {public}orders_insert_own— INSERT for {public}orders_select_own— SELECT for {public}orders_update_own— UPDATE for {public}
Indexes:
idx_orders_agent_ididx_orders_portfolio_ididx_orders_sourceidx_orders_user_idix_orders_statusix_orders_symbolorders_pkey
positions
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
user_id | uuid | No | |
portfolio_id | uuid | Yes | |
symbol | varchar(20) | No | |
side | varchar(10) | No | |
status | varchar(20) | No | |
size | numeric | No | |
entry_price | numeric | No | |
mark_price | numeric | Yes | |
liquidation_price | numeric | Yes | |
unrealized_pnl | numeric | Yes | |
realized_pnl | numeric | Yes | |
roi | numeric | Yes | |
margin_used | numeric | Yes | |
leverage | numeric | Yes | |
opened_at | timestamp | No | |
closed_at | timestamp | Yes | |
updated_at | timestamp | Yes | |
exchange | varchar(50) | Yes | |
exchange_position_id | varchar(100) | Yes | |
trading_metadata | json | Yes | |
source | varchar(20) | No | 'manual'::character varying |
agent_id | uuid | Yes |
RLS Policies:
positions_delete_own— DELETE for {public}positions_insert_own— INSERT for {public}positions_select_own— SELECT for {public}positions_update_own— UPDATE for {public}
Indexes:
idx_positions_agent_ididx_positions_portfolio_ididx_positions_sourceidx_positions_user_idix_positions_statusix_positions_symbolpositions_pkey
trades
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
user_id | uuid | No | |
portfolio_id | uuid | Yes | |
order_id | uuid | Yes | |
symbol | varchar(20) | No | |
side | varchar(10) | No | |
type | varchar(20) | No | |
status | varchar(20) | No | |
quantity | numeric | No | |
price | numeric | Yes | |
executed_quantity | numeric | Yes | |
executed_price | numeric | Yes | |
remaining_quantity | numeric | Yes | |
fee_amount | numeric | Yes | |
fee_currency | varchar(10) | Yes | |
total_cost | numeric | Yes | |
exchange | varchar(50) | Yes | |
exchange_order_id | varchar(100) | Yes | |
exchange_trade_id | varchar(100) | Yes | |
created_at | timestamp | No | |
updated_at | timestamp | Yes | |
executed_at | timestamp | Yes | |
trading_metadata | json | Yes | |
tags | json | Yes | |
source | varchar(20) | No | 'manual'::character varying |
agent_id | uuid | Yes |
RLS Policies:
trades_delete_own— DELETE for {public}trades_insert_own— INSERT for {public}trades_select_own— SELECT for {public}trades_update_own— UPDATE for {public}
Indexes:
idx_trades_agent_ididx_trades_order_ididx_trades_portfolio_ididx_trades_sourceidx_trades_user_idix_trades_statusix_trades_symboltrades_pkey
trade_orders
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
portfolio_id | uuid | No | |
position_id | uuid | Yes | |
asset_symbol | varchar(20) | No | |
trade_action | varchar(10) | No | |
order_type | varchar(20) | Yes | |
target_quantity | numeric | No | |
executed_quantity | numeric | Yes | |
remaining_quantity | numeric | Yes | |
target_price_usd | numeric | Yes | |
executed_price_usd | numeric | Yes | |
target_value_usd | numeric | No | |
executed_value_usd | numeric | Yes | |
estimated_fees_usd | numeric | Yes | |
actual_fees_usd | numeric | Yes | |
slippage_percent | numeric | Yes | |
execution_status | varchar(20) | Yes | |
priority | int4 | Yes | |
max_slippage_percent | numeric | Yes | |
timeout_minutes | int4 | Yes | |
exchange_order_id | varchar(100) | Yes | |
exchange_name | varchar(50) | Yes | |
client_order_id | varchar(100) | Yes | |
created_at | timestamp | No | |
submitted_at | timestamp | Yes | |
completed_at | timestamp | Yes | |
cancelled_at | timestamp | Yes | |
order_metadata | json | Yes |
RLS Policies:
trade_orders_service_all— ALL for {public}
Indexes:
idx_trade_orders_portfolio_ididx_trade_orders_position_idix_trade_orders_asset_symboltrade_orders_pkey
trade_executions
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
order_id | uuid | No | |
portfolio_id | uuid | No | |
asset_symbol | varchar(20) | No | |
trade_action | varchar(10) | No | |
quantity | numeric | No | |
price_usd | numeric | No | |
value_usd | numeric | No | |
fee_amount | numeric | Yes | |
fee_currency | varchar(10) | Yes | |
gas_fee_usd | numeric | Yes | |
total_cost_usd | numeric | No | |
expected_price_usd | numeric | Yes | |
slippage_percent | numeric | Yes | |
execution_latency_ms | int4 | Yes | |
exchange_trade_id | varchar(100) | Yes | |
exchange_name | varchar(50) | Yes | |
block_hash | varchar(100) | Yes | |
transaction_hash | varchar(100) | Yes | |
executed_at | timestamp | No | |
block_timestamp | timestamp | Yes | |
execution_metadata | json | Yes |
RLS Policies:
trade_executions_service_all— ALL for {public}
Indexes:
idx_trade_executions_order_ididx_trade_executions_portfolio_idix_trade_executions_asset_symboltrade_executions_pkey
trade_logs
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | gen_random_uuid() |
symbol | varchar(20) | No | |
action | text | No | |
confidence | numeric | No | |
risk_level | text | No | |
position_usd | numeric | No | |
reasoning | text | Yes | |
source | text | Yes | |
executed | bool | No | false |
order_id | text | Yes | |
blocked_reason | text | Yes | |
decision_timestamp | timestamptz | No | |
created_at | timestamptz | No | now() |
RLS Policies:
Authenticated users can read trade_logs— SELECT for {public}Service role full access to trade_logs— ALL for {public}
Indexes:
idx_trade_logs_created_atidx_trade_logs_executedidx_trade_logs_symboltrade_logs_pkey
trading_strategies
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
user_id | uuid | No | |
portfolio_id | uuid | Yes | |
name | varchar(100) | No | |
description | text | Yes | |
strategy_type | varchar(30) | No | |
status | varchar(20) | Yes | |
symbols | json | Yes | |
timeframes | json | Yes | |
entry_conditions | json | Yes | |
exit_conditions | json | Yes | |
risk_level | varchar(20) | Yes | |
max_position_size | numeric | Yes | |
stop_loss_percentage | numeric | Yes | |
take_profit_percentage | numeric | Yes | |
max_drawdown_percentage | numeric | Yes | |
position_sizing_method | varchar(30) | Yes | |
base_position_size | numeric | Yes | |
risk_per_trade | numeric | Yes | |
auto_execute | bool | Yes | |
max_open_positions | int4 | Yes | |
cool_down_period | int4 | Yes | |
total_trades | int4 | Yes | |
winning_trades | int4 | Yes | |
total_pnl | numeric | Yes | |
best_trade | numeric | Yes | |
worst_trade | numeric | Yes | |
win_rate | numeric | Yes | |
profit_factor | numeric | Yes | |
sharpe_ratio | numeric | Yes | |
max_consecutive_losses | int4 | Yes | |
model_type | varchar(50) | Yes | |
model_parameters | json | Yes | |
training_data_period | int4 | Yes | |
retrain_frequency | int4 | Yes | |
confidence_threshold | numeric | Yes | |
backtest_start_date | timestamp | Yes | |
backtest_end_date | timestamp | Yes | |
backtest_results | json | Yes | |
last_backtest_at | timestamp | Yes | |
started_at | timestamp | Yes | |
stopped_at | timestamp | Yes | |
last_signal_at | timestamp | Yes | |
last_trade_at | timestamp | Yes | |
next_evaluation_at | timestamp | Yes | |
created_at | timestamp | No | |
updated_at | timestamp | Yes | |
strategy_metadata | json | Yes | |
tags | json | Yes | |
pipeline_state | text | No | 'draft'::text |
RLS Policies:
trading_strategies_delete_own— DELETE for {public}trading_strategies_insert_own— INSERT for {public}trading_strategies_select_own— SELECT for {public}trading_strategies_update_own— UPDATE for {public}
Indexes:
idx_trading_strategies_pipeline_stateidx_trading_strategies_portfolio_idix_trading_strategies_statusix_trading_strategies_strategy_typeix_trading_strategies_user_idtrading_strategies_pkey
trading_signals
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
strategy_id | uuid | No | |
user_id | uuid | No | |
symbol | varchar(20) | No | |
signal_type | varchar(20) | No | |
strength | numeric | Yes | |
confidence | numeric | Yes | |
entry_price | numeric | Yes | |
stop_loss_price | numeric | Yes | |
take_profit_price | numeric | Yes | |
current_price | numeric | Yes | |
suggested_quantity | numeric | Yes | |
risk_reward_ratio | numeric | Yes | |
expected_duration | int4 | Yes | |
source | varchar(50) | Yes | |
indicators_used | json | Yes | |
analysis_data | json | Yes | |
is_executed | bool | Yes | |
executed_at | timestamp | Yes | |
execution_price | numeric | Yes | |
order_id | uuid | Yes | |
is_valid | bool | Yes | |
invalidation_reason | text | Yes | |
expires_at | timestamp | Yes | |
created_at | timestamp | No | |
strategy_metadata | json | Yes |
RLS Policies:
trading_signals_delete_own— DELETE for {public}trading_signals_insert_own— INSERT for {public}trading_signals_select_own— SELECT for {public}trading_signals_update_own— UPDATE for {public}
Indexes:
idx_trading_signals_strategy_ididx_trading_signals_user_idix_trading_signals_created_atix_trading_signals_symboltrading_signals_pkey
strategy_executions
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
strategy_id | uuid | No | |
user_id | uuid | No | |
execution_type | varchar(30) | No | |
symbol | varchar(20) | No | |
timeframe | varchar(10) | No | |
side | varchar(10) | No | |
quantity | numeric | No | |
entry_price | numeric | No | |
exit_price | numeric | Yes | |
realized_pnl | numeric | Yes | |
unrealized_pnl | numeric | Yes | |
fees_paid | numeric | Yes | |
signal_id | uuid | Yes | |
order_ids | json | Yes | |
execution_reason | text | Yes | |
market_conditions | json | Yes | |
opened_at | timestamp | No | |
closed_at | timestamp | Yes | |
duration_minutes | int4 | Yes | |
status | varchar(20) | Yes | |
created_at | timestamp | No | |
updated_at | timestamp | Yes | |
strategy_metadata | json | Yes |
RLS Policies:
strategy_executions_delete_own— DELETE for {public}strategy_executions_insert_own— INSERT for {public}strategy_executions_select_own— SELECT for {public}strategy_executions_update_own— UPDATE for {public}
Indexes:
idx_strategy_executions_strategy_ididx_strategy_executions_user_idix_strategy_executions_symbolstrategy_executions_pkey
user_trading_profiles
Extended trading profiles with risk management and compliance tracking
| Column | Type | Nullable | Default |
|---|---|---|---|
id | int8 | No | |
user_id | uuid | No | |
trading_tier | trading_tier | No | 'retail'::trading_tier |
risk_profile | risk_profile | No | 'low'::risk_profile |
api_rate_limit_per_minute | int4 | No | 60 |
max_open_positions | int4 | No | 10 |
max_order_size | numeric | No | 10000.00 |
allowed_trading_pairs | _text | Yes | ARRAY['BTC/USD'::text, 'ETH/USD'::text] |
leverage_enabled | bool | No | false |
max_leverage | numeric | No | 1.0 |
margin_trading_enabled | bool | No | false |
daily_loss_limit | numeric | Yes | |
weekly_loss_limit | numeric | Yes | |
requires_manual_approval | bool | No | false |
trading_suspended | bool | No | false |
suspension_reason | text | Yes | |
suspended_until | timestamptz | Yes | |
aml_score | int4 | No | 0 |
pep_status | bool | No | false |
sanctions_checked_at | timestamptz | Yes | |
enhanced_due_diligence | bool | No | false |
last_trade_at | timestamptz | Yes | |
total_trades_count | int8 | No | 0 |
total_volume_usd | numeric | No | 0.00 |
created_at | timestamptz | No | now() |
updated_at | timestamptz | No | now() |
updated_by | uuid | Yes |
RLS Policies:
Admins can update trading profiles— UPDATE for {public}Admins or users can view trading profiles— SELECT for {public}
Indexes:
idx_user_trading_profiles_updated_byuser_trading_profiles_pkeyuser_trading_profiles_user_id_key
strategy_pipeline_history
Audit trail of strategy transitions through pipeline stages (backtest -> paper -> live)
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | gen_random_uuid() |
strategy_id | uuid | No | |
user_id | uuid | No | |
from_state | text | No | |
to_state | text | No | |
transition_type | text | No | |
validations_passed | jsonb | Yes | '[]'::jsonb |
validations_failed | jsonb | Yes | '[]'::jsonb |
performance_snapshot | jsonb | Yes | |
metadata | jsonb | Yes | '{}'::jsonb |
notes | text | Yes | |
timestamp | timestamptz | No | now() |
created_at | timestamptz | No | now() |
RLS Policies:
System can insert pipeline history— INSERT for {public}Users can view own pipeline history— SELECT for {public}
Indexes:
idx_pipeline_history_from_stateidx_pipeline_history_strategy_ididx_pipeline_history_strategy_timestampidx_pipeline_history_timestampidx_pipeline_history_to_stateidx_pipeline_history_transition_typeidx_pipeline_history_user_idstrategy_pipeline_history_pkey
strategy_pipeline_snapshots
Performance snapshots at each pipeline stage for side-by-side comparison
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | gen_random_uuid() |
strategy_id | uuid | No | |
user_id | uuid | No | |
stage | text | No | |
snapshot_timestamp | timestamptz | No | now() |
total_trades | int4 | No | 0 |
winning_trades | int4 | No | 0 |
losing_trades | int4 | No | 0 |
win_rate | numeric | No | 0 |
total_pnl | numeric | No | 0 |
total_pnl_percentage | numeric | No | 0 |
max_drawdown | numeric | No | 0 |
sharpe_ratio | numeric | Yes | |
profit_factor | numeric | Yes | |
average_win | numeric | No | 0 |
average_loss | numeric | No | 0 |
largest_win | numeric | No | 0 |
largest_loss | numeric | No | 0 |
duration_hours | numeric | Yes | |
start_date | timestamptz | Yes | |
end_date | timestamptz | Yes | |
metadata | jsonb | Yes | '{}'::jsonb |
created_at | timestamptz | No | now() |
updated_at | timestamptz | No | now() |
RLS Policies:
System can insert pipeline snapshots— INSERT for {public}System can update pipeline snapshots— UPDATE for {public}Users can view own pipeline snapshots— SELECT for {public}
Indexes:
idx_pipeline_snapshots_stageidx_pipeline_snapshots_strategy_ididx_pipeline_snapshots_strategy_stageidx_pipeline_snapshots_timestampidx_pipeline_snapshots_user_idstrategy_pipeline_snapshots_pkey
backtests
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
portfolio_id | uuid | No | |
name | varchar(100) | No | |
description | varchar(500) | Yes | |
status | varchar(20) | Yes | |
start_date | date | No | |
end_date | date | No | |
initial_capital | numeric | No | |
benchmark_symbol | varchar(20) | Yes | |
final_value | numeric | Yes | |
total_return | numeric | Yes | |
annualized_return | numeric | Yes | |
volatility | numeric | Yes | |
max_drawdown | numeric | Yes | |
sharpe_ratio | numeric | Yes | |
sortino_ratio | numeric | Yes | |
total_trades | int4 | Yes | |
winning_trades | int4 | Yes | |
losing_trades | int4 | Yes | |
win_rate | numeric | Yes | |
average_win | numeric | Yes | |
average_loss | numeric | Yes | |
profit_factor | numeric | Yes | |
started_at | timestamp | Yes | |
completed_at | timestamp | Yes | |
execution_time_seconds | int4 | Yes | |
created_at | timestamp | No | |
updated_at | timestamp | Yes | |
strategy_config | json | Yes | |
detailed_results | json | Yes |
RLS Policies:
backtests_service_all— ALL for {public}
Indexes:
backtests_pkeyidx_backtests_portfolio_id
pattern_recognition
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
symbol | varchar(20) | No | |
timeframe | varchar(10) | No | |
pattern_type | varchar(50) | No | |
pattern_category | varchar(30) | No | |
signal_direction | varchar(20) | No | |
signal_strength | varchar(20) | No | |
confidence_score | numeric | No | |
completion_percentage | numeric | Yes | |
reliability_score | numeric | Yes | |
entry_price | numeric | Yes | |
target_price | numeric | Yes | |
stop_loss_price | numeric | Yes | |
breakout_price | numeric | Yes | |
pattern_start_date | timestamp | No | |
pattern_end_date | timestamp | Yes | |
detection_date | timestamp | No | |
risk_reward_ratio | numeric | Yes | |
max_risk_percentage | numeric | Yes | |
expected_move_percentage | numeric | Yes | |
status | varchar(20) | Yes | |
is_confirmed | bool | Yes | |
is_invalidated | bool | Yes | |
pattern_metadata | json | Yes | |
created_at | timestamp | No | |
updated_at | timestamp | Yes |
RLS Policies:
pattern_recognition_public_select— SELECT for {public}pattern_recognition_service_delete— DELETE for {public}pattern_recognition_service_insert— INSERT for {public}pattern_recognition_service_update— UPDATE for {public}
Indexes:
ix_pattern_recognition_symbolpattern_recognition_pkey
successful_patterns
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | gen_random_uuid() |
pattern_type | varchar(100) | No | |
error_type | varchar(255) | Yes | |
trigger_context | text | No | |
file_patterns | _text | Yes | |
solution_approach | text | No | |
code_diff | text | Yes | |
success_rate | numeric | Yes | 1.0 |
times_used | int4 | Yes | 1 |
times_successful | int4 | Yes | 1 |
avg_resolution_time_ms | int4 | Yes | |
source_pr_url | text | Yes | |
source_request_id | uuid | Yes | |
created_at | timestamptz | No | now() |
updated_at | timestamptz | No | now() |
RLS Policies:
Service role full access on successful_patterns— ALL for {public}
Indexes:
idx_patterns_erroridx_patterns_typesuccessful_patterns_pkey
financial_transactions
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | |
user_id | uuid | No | |
transaction_type | varchar(30) | No | |
external_transaction_id | varchar(100) | Yes | |
reference_number | varchar(100) | Yes | |
symbol | varchar(20) | Yes | |
base_currency | varchar(10) | No | |
quote_currency | varchar(10) | Yes | |
amount | numeric | No | |
price | numeric | Yes | |
total_value | numeric | No | |
fee_amount | numeric | Yes | |
fee_currency | varchar(10) | Yes | |
fee_type | varchar(20) | Yes | |
exchange_fee | numeric | Yes | |
network_fee | numeric | Yes | |
exchange | varchar(50) | Yes | |
wallet_address | varchar(100) | Yes | |
transaction_hash | varchar(100) | Yes | |
block_number | int4 | Yes | |
confirmations | int4 | Yes | |
counterparty_id | uuid | Yes | |
counterparty_name | varchar(200) | Yes | |
counterparty_type | varchar(50) | Yes | |
aml_status | varchar(20) | Yes | |
sanction_check_status | varchar(20) | Yes | |
risk_score | numeric | Yes | |
is_suspicious | bool | Yes | |
reporting_currency | varchar(10) | Yes | |
reporting_value | numeric | Yes | |
tax_jurisdiction | varchar(100) | Yes | |
is_taxable | bool | Yes | |
originating_country | varchar(2) | Yes | |
receiving_country | varchar(2) | Yes | |
status | varchar(20) | Yes | |
initiated_at | timestamptz | No | |
confirmed_at | timestamptz | Yes | |
completed_at | timestamptz | Yes | |
created_at | timestamptz | No | |
updated_at | timestamptz | Yes | |
audit_metadata | jsonb | Yes |
RLS Policies:
financial_transactions_delete_own— DELETE for {public}financial_transactions_insert_own— INSERT for {public}financial_transactions_select_own— SELECT for {public}financial_transactions_update_own— UPDATE for {public}
Indexes:
financial_transactions_pkeyidx_fintx_aml_statusidx_fintx_amountidx_fintx_created_atidx_fintx_exchangeidx_fintx_metadataidx_fintx_statusidx_fintx_transaction_typeidx_fintx_type_dateidx_fintx_user_dateidx_fintx_user_idix_financial_transactions_reference_numberix_financial_transactions_transaction_typeix_financial_transactions_user_id
withdrawal_tracking
Tracks daily withdrawal amounts for limit enforcement
| Column | Type | Nullable | Default |
|---|---|---|---|
id | uuid | No | gen_random_uuid() |
user_id | uuid | No | |
amount_usd | numeric | No | |
withdrawal_date | date | No | CURRENT_DATE |
created_at | timestamptz | No | now() |
RLS Policies:
Users can view own withdrawals— SELECT for {public}
Indexes:
withdrawal_tracking_pkeywithdrawal_tracking_user_id_withdrawal_date_key