bitcain docs

Trading & Orders

Trading & Orders

Order management, trade execution, positions, and trading strategies.

17 tables in this group.

orders

ColumnTypeNullableDefault
iduuidNo
user_iduuidNo
portfolio_iduuidYes
symbolvarchar(20)No
sidevarchar(10)No
typevarchar(20)No
statusvarchar(20)No
quantitynumericNo
pricenumericYes
stop_pricenumericYes
executed_quantitynumericYes
average_pricenumericYes
remaining_quantitynumericYes
time_in_forcevarchar(10)Yes
expires_attimestampYes
reduce_onlyboolYes
post_onlyboolYes
exchangevarchar(50)Yes
exchange_order_idvarchar(100)Yes
created_attimestampNo
updated_attimestampYes
filled_attimestampYes
cancelled_attimestampYes
trading_metadatajsonYes
client_order_idvarchar(100)Yes
sourcevarchar(20)No'manual'::character varying
agent_iduuidYes

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_id
  • idx_orders_portfolio_id
  • idx_orders_source
  • idx_orders_user_id
  • ix_orders_status
  • ix_orders_symbol
  • orders_pkey

positions

ColumnTypeNullableDefault
iduuidNo
user_iduuidNo
portfolio_iduuidYes
symbolvarchar(20)No
sidevarchar(10)No
statusvarchar(20)No
sizenumericNo
entry_pricenumericNo
mark_pricenumericYes
liquidation_pricenumericYes
unrealized_pnlnumericYes
realized_pnlnumericYes
roinumericYes
margin_usednumericYes
leveragenumericYes
opened_attimestampNo
closed_attimestampYes
updated_attimestampYes
exchangevarchar(50)Yes
exchange_position_idvarchar(100)Yes
trading_metadatajsonYes
sourcevarchar(20)No'manual'::character varying
agent_iduuidYes

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_id
  • idx_positions_portfolio_id
  • idx_positions_source
  • idx_positions_user_id
  • ix_positions_status
  • ix_positions_symbol
  • positions_pkey

trades

ColumnTypeNullableDefault
iduuidNo
user_iduuidNo
portfolio_iduuidYes
order_iduuidYes
symbolvarchar(20)No
sidevarchar(10)No
typevarchar(20)No
statusvarchar(20)No
quantitynumericNo
pricenumericYes
executed_quantitynumericYes
executed_pricenumericYes
remaining_quantitynumericYes
fee_amountnumericYes
fee_currencyvarchar(10)Yes
total_costnumericYes
exchangevarchar(50)Yes
exchange_order_idvarchar(100)Yes
exchange_trade_idvarchar(100)Yes
created_attimestampNo
updated_attimestampYes
executed_attimestampYes
trading_metadatajsonYes
tagsjsonYes
sourcevarchar(20)No'manual'::character varying
agent_iduuidYes

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_id
  • idx_trades_order_id
  • idx_trades_portfolio_id
  • idx_trades_source
  • idx_trades_user_id
  • ix_trades_status
  • ix_trades_symbol
  • trades_pkey

trade_orders

ColumnTypeNullableDefault
iduuidNo
portfolio_iduuidNo
position_iduuidYes
asset_symbolvarchar(20)No
trade_actionvarchar(10)No
order_typevarchar(20)Yes
target_quantitynumericNo
executed_quantitynumericYes
remaining_quantitynumericYes
target_price_usdnumericYes
executed_price_usdnumericYes
target_value_usdnumericNo
executed_value_usdnumericYes
estimated_fees_usdnumericYes
actual_fees_usdnumericYes
slippage_percentnumericYes
execution_statusvarchar(20)Yes
priorityint4Yes
max_slippage_percentnumericYes
timeout_minutesint4Yes
exchange_order_idvarchar(100)Yes
exchange_namevarchar(50)Yes
client_order_idvarchar(100)Yes
created_attimestampNo
submitted_attimestampYes
completed_attimestampYes
cancelled_attimestampYes
order_metadatajsonYes

RLS Policies:

  • trade_orders_service_all — ALL for {public}

Indexes:

  • idx_trade_orders_portfolio_id
  • idx_trade_orders_position_id
  • ix_trade_orders_asset_symbol
  • trade_orders_pkey

trade_executions

ColumnTypeNullableDefault
iduuidNo
order_iduuidNo
portfolio_iduuidNo
asset_symbolvarchar(20)No
trade_actionvarchar(10)No
quantitynumericNo
price_usdnumericNo
value_usdnumericNo
fee_amountnumericYes
fee_currencyvarchar(10)Yes
gas_fee_usdnumericYes
total_cost_usdnumericNo
expected_price_usdnumericYes
slippage_percentnumericYes
execution_latency_msint4Yes
exchange_trade_idvarchar(100)Yes
exchange_namevarchar(50)Yes
block_hashvarchar(100)Yes
transaction_hashvarchar(100)Yes
executed_attimestampNo
block_timestamptimestampYes
execution_metadatajsonYes

RLS Policies:

  • trade_executions_service_all — ALL for {public}

Indexes:

  • idx_trade_executions_order_id
  • idx_trade_executions_portfolio_id
  • ix_trade_executions_asset_symbol
  • trade_executions_pkey

trade_logs

ColumnTypeNullableDefault
iduuidNogen_random_uuid()
symbolvarchar(20)No
actiontextNo
confidencenumericNo
risk_leveltextNo
position_usdnumericNo
reasoningtextYes
sourcetextYes
executedboolNofalse
order_idtextYes
blocked_reasontextYes
decision_timestamptimestamptzNo
created_attimestamptzNonow()

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_at
  • idx_trade_logs_executed
  • idx_trade_logs_symbol
  • trade_logs_pkey

trading_strategies

ColumnTypeNullableDefault
iduuidNo
user_iduuidNo
portfolio_iduuidYes
namevarchar(100)No
descriptiontextYes
strategy_typevarchar(30)No
statusvarchar(20)Yes
symbolsjsonYes
timeframesjsonYes
entry_conditionsjsonYes
exit_conditionsjsonYes
risk_levelvarchar(20)Yes
max_position_sizenumericYes
stop_loss_percentagenumericYes
take_profit_percentagenumericYes
max_drawdown_percentagenumericYes
position_sizing_methodvarchar(30)Yes
base_position_sizenumericYes
risk_per_tradenumericYes
auto_executeboolYes
max_open_positionsint4Yes
cool_down_periodint4Yes
total_tradesint4Yes
winning_tradesint4Yes
total_pnlnumericYes
best_tradenumericYes
worst_tradenumericYes
win_ratenumericYes
profit_factornumericYes
sharpe_rationumericYes
max_consecutive_lossesint4Yes
model_typevarchar(50)Yes
model_parametersjsonYes
training_data_periodint4Yes
retrain_frequencyint4Yes
confidence_thresholdnumericYes
backtest_start_datetimestampYes
backtest_end_datetimestampYes
backtest_resultsjsonYes
last_backtest_attimestampYes
started_attimestampYes
stopped_attimestampYes
last_signal_attimestampYes
last_trade_attimestampYes
next_evaluation_attimestampYes
created_attimestampNo
updated_attimestampYes
strategy_metadatajsonYes
tagsjsonYes
pipeline_statetextNo'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_state
  • idx_trading_strategies_portfolio_id
  • ix_trading_strategies_status
  • ix_trading_strategies_strategy_type
  • ix_trading_strategies_user_id
  • trading_strategies_pkey

trading_signals

ColumnTypeNullableDefault
iduuidNo
strategy_iduuidNo
user_iduuidNo
symbolvarchar(20)No
signal_typevarchar(20)No
strengthnumericYes
confidencenumericYes
entry_pricenumericYes
stop_loss_pricenumericYes
take_profit_pricenumericYes
current_pricenumericYes
suggested_quantitynumericYes
risk_reward_rationumericYes
expected_durationint4Yes
sourcevarchar(50)Yes
indicators_usedjsonYes
analysis_datajsonYes
is_executedboolYes
executed_attimestampYes
execution_pricenumericYes
order_iduuidYes
is_validboolYes
invalidation_reasontextYes
expires_attimestampYes
created_attimestampNo
strategy_metadatajsonYes

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_id
  • idx_trading_signals_user_id
  • ix_trading_signals_created_at
  • ix_trading_signals_symbol
  • trading_signals_pkey

strategy_executions

ColumnTypeNullableDefault
iduuidNo
strategy_iduuidNo
user_iduuidNo
execution_typevarchar(30)No
symbolvarchar(20)No
timeframevarchar(10)No
sidevarchar(10)No
quantitynumericNo
entry_pricenumericNo
exit_pricenumericYes
realized_pnlnumericYes
unrealized_pnlnumericYes
fees_paidnumericYes
signal_iduuidYes
order_idsjsonYes
execution_reasontextYes
market_conditionsjsonYes
opened_attimestampNo
closed_attimestampYes
duration_minutesint4Yes
statusvarchar(20)Yes
created_attimestampNo
updated_attimestampYes
strategy_metadatajsonYes

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_id
  • idx_strategy_executions_user_id
  • ix_strategy_executions_symbol
  • strategy_executions_pkey

user_trading_profiles

Extended trading profiles with risk management and compliance tracking

ColumnTypeNullableDefault
idint8No
user_iduuidNo
trading_tiertrading_tierNo'retail'::trading_tier
risk_profilerisk_profileNo'low'::risk_profile
api_rate_limit_per_minuteint4No60
max_open_positionsint4No10
max_order_sizenumericNo10000.00
allowed_trading_pairs_textYesARRAY['BTC/USD'::text, 'ETH/USD'::text]
leverage_enabledboolNofalse
max_leveragenumericNo1.0
margin_trading_enabledboolNofalse
daily_loss_limitnumericYes
weekly_loss_limitnumericYes
requires_manual_approvalboolNofalse
trading_suspendedboolNofalse
suspension_reasontextYes
suspended_untiltimestamptzYes
aml_scoreint4No0
pep_statusboolNofalse
sanctions_checked_attimestamptzYes
enhanced_due_diligenceboolNofalse
last_trade_attimestamptzYes
total_trades_countint8No0
total_volume_usdnumericNo0.00
created_attimestamptzNonow()
updated_attimestamptzNonow()
updated_byuuidYes

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_by
  • user_trading_profiles_pkey
  • user_trading_profiles_user_id_key

strategy_pipeline_history

Audit trail of strategy transitions through pipeline stages (backtest -> paper -> live)

ColumnTypeNullableDefault
iduuidNogen_random_uuid()
strategy_iduuidNo
user_iduuidNo
from_statetextNo
to_statetextNo
transition_typetextNo
validations_passedjsonbYes'[]'::jsonb
validations_failedjsonbYes'[]'::jsonb
performance_snapshotjsonbYes
metadatajsonbYes'{}'::jsonb
notestextYes
timestamptimestamptzNonow()
created_attimestamptzNonow()

RLS Policies:

  • System can insert pipeline history — INSERT for {public}
  • Users can view own pipeline history — SELECT for {public}

Indexes:

  • idx_pipeline_history_from_state
  • idx_pipeline_history_strategy_id
  • idx_pipeline_history_strategy_timestamp
  • idx_pipeline_history_timestamp
  • idx_pipeline_history_to_state
  • idx_pipeline_history_transition_type
  • idx_pipeline_history_user_id
  • strategy_pipeline_history_pkey

strategy_pipeline_snapshots

Performance snapshots at each pipeline stage for side-by-side comparison

ColumnTypeNullableDefault
iduuidNogen_random_uuid()
strategy_iduuidNo
user_iduuidNo
stagetextNo
snapshot_timestamptimestamptzNonow()
total_tradesint4No0
winning_tradesint4No0
losing_tradesint4No0
win_ratenumericNo0
total_pnlnumericNo0
total_pnl_percentagenumericNo0
max_drawdownnumericNo0
sharpe_rationumericYes
profit_factornumericYes
average_winnumericNo0
average_lossnumericNo0
largest_winnumericNo0
largest_lossnumericNo0
duration_hoursnumericYes
start_datetimestamptzYes
end_datetimestamptzYes
metadatajsonbYes'{}'::jsonb
created_attimestamptzNonow()
updated_attimestamptzNonow()

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_stage
  • idx_pipeline_snapshots_strategy_id
  • idx_pipeline_snapshots_strategy_stage
  • idx_pipeline_snapshots_timestamp
  • idx_pipeline_snapshots_user_id
  • strategy_pipeline_snapshots_pkey

backtests

ColumnTypeNullableDefault
iduuidNo
portfolio_iduuidNo
namevarchar(100)No
descriptionvarchar(500)Yes
statusvarchar(20)Yes
start_datedateNo
end_datedateNo
initial_capitalnumericNo
benchmark_symbolvarchar(20)Yes
final_valuenumericYes
total_returnnumericYes
annualized_returnnumericYes
volatilitynumericYes
max_drawdownnumericYes
sharpe_rationumericYes
sortino_rationumericYes
total_tradesint4Yes
winning_tradesint4Yes
losing_tradesint4Yes
win_ratenumericYes
average_winnumericYes
average_lossnumericYes
profit_factornumericYes
started_attimestampYes
completed_attimestampYes
execution_time_secondsint4Yes
created_attimestampNo
updated_attimestampYes
strategy_configjsonYes
detailed_resultsjsonYes

RLS Policies:

  • backtests_service_all — ALL for {public}

Indexes:

  • backtests_pkey
  • idx_backtests_portfolio_id

pattern_recognition

ColumnTypeNullableDefault
iduuidNo
symbolvarchar(20)No
timeframevarchar(10)No
pattern_typevarchar(50)No
pattern_categoryvarchar(30)No
signal_directionvarchar(20)No
signal_strengthvarchar(20)No
confidence_scorenumericNo
completion_percentagenumericYes
reliability_scorenumericYes
entry_pricenumericYes
target_pricenumericYes
stop_loss_pricenumericYes
breakout_pricenumericYes
pattern_start_datetimestampNo
pattern_end_datetimestampYes
detection_datetimestampNo
risk_reward_rationumericYes
max_risk_percentagenumericYes
expected_move_percentagenumericYes
statusvarchar(20)Yes
is_confirmedboolYes
is_invalidatedboolYes
pattern_metadatajsonYes
created_attimestampNo
updated_attimestampYes

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_symbol
  • pattern_recognition_pkey

successful_patterns

ColumnTypeNullableDefault
iduuidNogen_random_uuid()
pattern_typevarchar(100)No
error_typevarchar(255)Yes
trigger_contexttextNo
file_patterns_textYes
solution_approachtextNo
code_difftextYes
success_ratenumericYes1.0
times_usedint4Yes1
times_successfulint4Yes1
avg_resolution_time_msint4Yes
source_pr_urltextYes
source_request_iduuidYes
created_attimestamptzNonow()
updated_attimestamptzNonow()

RLS Policies:

  • Service role full access on successful_patterns — ALL for {public}

Indexes:

  • idx_patterns_error
  • idx_patterns_type
  • successful_patterns_pkey

financial_transactions

ColumnTypeNullableDefault
iduuidNo
user_iduuidNo
transaction_typevarchar(30)No
external_transaction_idvarchar(100)Yes
reference_numbervarchar(100)Yes
symbolvarchar(20)Yes
base_currencyvarchar(10)No
quote_currencyvarchar(10)Yes
amountnumericNo
pricenumericYes
total_valuenumericNo
fee_amountnumericYes
fee_currencyvarchar(10)Yes
fee_typevarchar(20)Yes
exchange_feenumericYes
network_feenumericYes
exchangevarchar(50)Yes
wallet_addressvarchar(100)Yes
transaction_hashvarchar(100)Yes
block_numberint4Yes
confirmationsint4Yes
counterparty_iduuidYes
counterparty_namevarchar(200)Yes
counterparty_typevarchar(50)Yes
aml_statusvarchar(20)Yes
sanction_check_statusvarchar(20)Yes
risk_scorenumericYes
is_suspiciousboolYes
reporting_currencyvarchar(10)Yes
reporting_valuenumericYes
tax_jurisdictionvarchar(100)Yes
is_taxableboolYes
originating_countryvarchar(2)Yes
receiving_countryvarchar(2)Yes
statusvarchar(20)Yes
initiated_attimestamptzNo
confirmed_attimestamptzYes
completed_attimestamptzYes
created_attimestamptzNo
updated_attimestamptzYes
audit_metadatajsonbYes

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_pkey
  • idx_fintx_aml_status
  • idx_fintx_amount
  • idx_fintx_created_at
  • idx_fintx_exchange
  • idx_fintx_metadata
  • idx_fintx_status
  • idx_fintx_transaction_type
  • idx_fintx_type_date
  • idx_fintx_user_date
  • idx_fintx_user_id
  • ix_financial_transactions_reference_number
  • ix_financial_transactions_transaction_type
  • ix_financial_transactions_user_id

withdrawal_tracking

Tracks daily withdrawal amounts for limit enforcement

ColumnTypeNullableDefault
iduuidNogen_random_uuid()
user_iduuidNo
amount_usdnumericNo
withdrawal_datedateNoCURRENT_DATE
created_attimestamptzNonow()

RLS Policies:

  • Users can view own withdrawals — SELECT for {public}

Indexes:

  • withdrawal_tracking_pkey
  • withdrawal_tracking_user_id_withdrawal_date_key

On this page