AI_Finance

Tables (26)

account_balance_snapshot

  • balance_snapshot_id — BIGSERIAL
  • account_id — BIGINT — NOT NULL
  • snapshot_time — TIMESTAMP — NOT NULL
  • available_cash — NUMERIC(24,6) — NOT NULL
  • withdrawable_cash — NUMERIC(24,6)
  • orderable_cash — NUMERIC(24,6)
  • created_at — TIMESTAMP — NOT NULL

account_master

  • account_id — BIGSERIAL
  • account_code — VARCHAR(50) — NOT NULL
  • account_name — VARCHAR(100) — NOT NULL
  • broker_code — VARCHAR(30) — NOT NULL
  • market_scope — VARCHAR(30) — NOT NULL
  • base_currency_code — VARCHAR(10) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

ai_decision_log

  • ai_log_id — BIGSERIAL
  • signal_id — BIGINT — Foreign key → signal_event.signal_id
  • asset_id — BIGINT — Foreign key → asset_master.asset_id
  • strategy_id — BIGINT — Foreign key → strategy_master.strategy_id
  • model_name — VARCHAR(100) — NOT NULL
  • prompt_version — VARCHAR(50)
  • system_context_summary — TEXT
  • input_payload — TEXT
  • ai_output_text — TEXT
  • confidence_adjustment — NUMERIC(10,6)
  • latency_ms — INTEGER
  • created_at — TIMESTAMP — NOT NULL

asset_master

  • asset_id — BIGSERIAL
  • symbol — VARCHAR(30) — NOT NULL
  • asset_name — VARCHAR(200) — NOT NULL
  • market_code — VARCHAR(20) — NOT NULL
  • exchange_code — VARCHAR(20) — NOT NULL
  • asset_type — VARCHAR(20) — NOT NULL
  • sector_name — VARCHAR(100)
  • currency_code — VARCHAR(10) — NOT NULL
  • tick_size — NUMERIC(18,6)
  • lot_size — NUMERIC(18,6) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • listed_at — DATE
  • delisted_at — DATE
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

broker_master

  • broker_id — BIGSERIAL
  • broker_code — VARCHAR(30) — NOT NULL
  • broker_name — VARCHAR(100) — NOT NULL
  • market_scope — VARCHAR(30) — NOT NULL
  • api_supported — BOOLEAN — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

market_daily_price

  • daily_price_id — BIGSERIAL
  • asset_id — BIGINT — Foreign key → asset_master.asset_id — NOT NULL
  • trade_date — DATE — NOT NULL
  • open_price — NUMERIC(18,6) — NOT NULL
  • high_price — NUMERIC(18,6) — NOT NULL
  • low_price — NUMERIC(18,6) — NOT NULL
  • close_price — NUMERIC(18,6) — NOT NULL
  • volume — BIGINT — NOT NULL
  • trade_value — NUMERIC(24,4)
  • change_amount — NUMERIC(18,6)
  • change_rate — NUMERIC(10,6)
  • created_at — TIMESTAMP — NOT NULL

market_indicator

  • indicator_id — BIGSERIAL
  • asset_id — BIGINT — Foreign key → asset_master.asset_id — NOT NULL
  • indicator_time — TIMESTAMP — NOT NULL
  • timeframe_code — VARCHAR(20) — NOT NULL
  • rsi_14 — NUMERIC(18,8)
  • macd — NUMERIC(18,8)
  • macd_signal — NUMERIC(18,8)
  • macd_hist — NUMERIC(18,8)
  • atr_14 — NUMERIC(18,8)
  • ma_5 — NUMERIC(18,8)
  • ma_20 — NUMERIC(18,8)
  • ma_60 — NUMERIC(18,8)
  • ma_120 — NUMERIC(18,8)
  • boll_upper — NUMERIC(18,8)
  • boll_middle — NUMERIC(18,8)
  • boll_lower — NUMERIC(18,8)
  • volatility_20 — NUMERIC(18,8)
  • created_at — TIMESTAMP — NOT NULL

market_intraday_price

  • intraday_price_id — BIGSERIAL
  • asset_id — BIGINT — Foreign key → asset_master.asset_id — NOT NULL
  • candle_time — TIMESTAMP — NOT NULL
  • interval_minute — INTEGER — NOT NULL
  • open_price — NUMERIC(18,6) — NOT NULL
  • high_price — NUMERIC(18,6) — NOT NULL
  • low_price — NUMERIC(18,6) — NOT NULL
  • close_price — NUMERIC(18,6) — NOT NULL
  • volume — BIGINT — NOT NULL
  • trade_value — NUMERIC(24,4)
  • created_at — TIMESTAMP — NOT NULL

market_news

  • news_id — BIGSERIAL
  • asset_id — BIGINT — Foreign key → asset_master.asset_id
  • source_name — VARCHAR(100) — NOT NULL
  • headline — TEXT — NOT NULL
  • summary_text — TEXT
  • original_url — TEXT
  • published_at — TIMESTAMP — NOT NULL
  • sentiment_score — NUMERIC(10,6)
  • relevance_score — NUMERIC(10,6)
  • news_category — VARCHAR(50)
  • created_at — TIMESTAMP — NOT NULL

order_execution

  • execution_id — BIGSERIAL
  • order_id — BIGINT — Foreign key → order_request.order_id — NOT NULL
  • executed_at — TIMESTAMP — NOT NULL
  • executed_quantity — NUMERIC(18,6) — NOT NULL
  • executed_price — NUMERIC(18,6) — NOT NULL
  • execution_amount — NUMERIC(24,6)
  • fee_amount — NUMERIC(18,6)
  • tax_amount — NUMERIC(18,6)
  • liquidity_type — VARCHAR(20)
  • created_at — TIMESTAMP — NOT NULL

order_request

  • order_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → user_account.user_id — NOT NULL
  • account_id — BIGINT — Foreign key → account_master.account_id — NOT NULL
  • signal_id — BIGINT — Foreign key → signal_event.signal_id
  • asset_id — BIGINT — Foreign key → asset_master.asset_id — NOT NULL
  • broker_code — VARCHAR(30) — NOT NULL
  • broker_account_no — VARCHAR(100)
  • order_side — VARCHAR(10) — NOT NULL
  • order_type — VARCHAR(20) — NOT NULL
  • order_price — NUMERIC(18,6)
  • order_quantity — NUMERIC(18,6) — NOT NULL
  • requested_at — TIMESTAMP — NOT NULL
  • sent_at — TIMESTAMP
  • order_status — VARCHAR(20) — NOT NULL
  • broker_order_no — VARCHAR(100)
  • reject_reason — TEXT
  • time_in_force — VARCHAR(20)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

order_status_history

  • order_status_history_id — BIGSERIAL
  • order_id — BIGINT — NOT NULL
  • from_status — VARCHAR(20)
  • to_status — VARCHAR(20) — NOT NULL
  • changed_at — TIMESTAMP — NOT NULL
  • change_reason — TEXT
  • created_at — TIMESTAMP — NOT NULL

portfolio_snapshot

  • snapshot_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → user_account.user_id — NOT NULL
  • account_id — BIGINT — Foreign key → account_master.account_id — NOT NULL
  • snapshot_time — TIMESTAMP — NOT NULL
  • total_equity — NUMERIC(24,6) — NOT NULL
  • cash_balance — NUMERIC(24,6) — NOT NULL
  • invested_amount — NUMERIC(24,6) — NOT NULL
  • unrealized_pnl — NUMERIC(24,6) — NOT NULL
  • realized_pnl — NUMERIC(24,6) — NOT NULL
  • daily_pnl — NUMERIC(24,6)
  • exposure_pct — NUMERIC(10,6)
  • created_at — TIMESTAMP — NOT NULL

position

  • position_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → user_account.user_id — NOT NULL
  • account_id — BIGINT — Foreign key → account_master.account_id — NOT NULL
  • asset_id — BIGINT — Foreign key → asset_master.asset_id — NOT NULL
  • position_side — VARCHAR(10) — NOT NULL
  • quantity — NUMERIC(18,6) — NOT NULL
  • avg_entry_price — NUMERIC(18,6) — NOT NULL
  • last_price — NUMERIC(18,6)
  • market_value — NUMERIC(24,6)
  • unrealized_pnl — NUMERIC(24,6)
  • realized_pnl — NUMERIC(24,6) — NOT NULL
  • opened_at — TIMESTAMP — NOT NULL
  • closed_at — TIMESTAMP
  • position_status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

risk_event

  • risk_event_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → user_account.user_id — NOT NULL
  • account_id — BIGINT — Foreign key → account_master.account_id — NOT NULL
  • asset_id — BIGINT — Foreign key → asset_master.asset_id
  • order_id — BIGINT — Foreign key → order_request.order_id
  • signal_id — BIGINT — Foreign key → signal_event.signal_id
  • event_time — TIMESTAMP — NOT NULL
  • event_type — VARCHAR(50) — NOT NULL
  • severity — VARCHAR(20) — NOT NULL
  • event_message — TEXT
  • created_at — TIMESTAMP — NOT NULL

risk_limit

  • risk_limit_id — BIGSERIAL
  • account_id — BIGINT — Foreign key → account_master.account_id — NOT NULL
  • max_position_pct — NUMERIC(10,6) — NOT NULL
  • max_etf_position_pct — NUMERIC(10,6)
  • max_total_exposure_pct — NUMERIC(10,6) — NOT NULL
  • max_daily_loss_pct — NUMERIC(10,6) — NOT NULL
  • max_asset_count — INTEGER — NOT NULL
  • block_new_entry — BOOLEAN — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

signal_event

  • signal_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → user_account.user_id — NOT NULL
  • account_id — BIGINT — Foreign key → account_master.account_id — NOT NULL
  • asset_id — BIGINT — Foreign key → asset_master.asset_id — NOT NULL
  • strategy_id — BIGINT — Foreign key → strategy_master.strategy_id — NOT NULL
  • signal_time — TIMESTAMP — NOT NULL
  • signal_type — VARCHAR(20) — NOT NULL
  • signal_score — NUMERIC(10,6) — NOT NULL
  • confidence_score — NUMERIC(10,6)
  • target_price — NUMERIC(18,6)
  • stop_loss_price — NUMERIC(18,6)
  • take_profit_price — NUMERIC(18,6)
  • expected_holding_days — INTEGER
  • signal_reason — TEXT
  • signal_status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

signal_score_detail

  • score_detail_id — BIGSERIAL
  • signal_id — BIGINT — Foreign key → signal_event.signal_id — NOT NULL
  • score_type — VARCHAR(50) — NOT NULL
  • score_value — NUMERIC(10,6) — NOT NULL
  • weight_value — NUMERIC(10,6)
  • detail_text — TEXT
  • created_at — TIMESTAMP — NOT NULL

strategy_master

  • strategy_id — BIGSERIAL
  • strategy_code — VARCHAR(50) — NOT NULL
  • strategy_name — VARCHAR(100) — NOT NULL
  • market_scope — VARCHAR(30) — NOT NULL
  • strategy_type — VARCHAR(30) — NOT NULL
  • version_no — VARCHAR(20) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • description — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

system_job_log

  • job_log_id — BIGSERIAL
  • job_name — VARCHAR(100) — NOT NULL
  • job_type — VARCHAR(50) — NOT NULL
  • started_at — TIMESTAMP — NOT NULL
  • ended_at — TIMESTAMP
  • job_status — VARCHAR(20) — NOT NULL
  • processed_count — INTEGER
  • error_message — TEXT
  • created_at — TIMESTAMP — NOT NULL

trade_universe

  • universe_id — BIGSERIAL
  • asset_id — BIGINT — Foreign key → asset_master.asset_id — NOT NULL
  • universe_group_code — VARCHAR(50) — NOT NULL
  • selection_score — NUMERIC(10,4)
  • enabled — BOOLEAN — NOT NULL
  • valid_from — DATE — NOT NULL
  • valid_to — DATE
  • memo — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

user_account

  • user_id — BIGSERIAL
  • login_id — VARCHAR(100) — NOT NULL
  • user_name — VARCHAR(100) — NOT NULL
  • email — VARCHAR(200)
  • role_code — VARCHAR(30) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

user_broker_account

  • user_broker_account_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → user_account.user_id — NOT NULL
  • account_id — BIGINT — Foreign key → account_master.account_id — NOT NULL
  • broker_id — BIGINT — Foreign key → broker_master.broker_id — NOT NULL
  • broker_account_no — VARCHAR(100) — NOT NULL
  • account_alias — VARCHAR(100)
  • is_primary — BOOLEAN — NOT NULL
  • api_enabled — BOOLEAN — NOT NULL
  • trading_mode — VARCHAR(20) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

user_profile

  • user_id — BIGINT — Foreign key → user_account.user_id
  • risk_grade — VARCHAR(30)
  • investment_style — VARCHAR(30)
  • base_currency_code — VARCHAR(10) — NOT NULL
  • timezone — VARCHAR(50) — NOT NULL
  • notification_email — BOOLEAN — NOT NULL
  • notification_sms — BOOLEAN — NOT NULL
  • memo — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

user_risk_profile

  • user_id — BIGINT — Foreign key → user_account.user_id
  • max_position_count — INTEGER
  • preferred_max_position_pct — NUMERIC(10,6)
  • preferred_daily_loss_pct — NUMERIC(10,6)
  • allow_high_volatility — BOOLEAN — NOT NULL
  • block_new_entry — BOOLEAN — NOT NULL
  • memo — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

user_strategy_subscription

  • user_strategy_subscription_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → user_account.user_id — NOT NULL
  • strategy_id — BIGINT — Foreign key → strategy_master.strategy_id — NOT NULL
  • is_enabled — BOOLEAN — NOT NULL
  • allocation_weight — NUMERIC(10,6)
  • parameter_override_json — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL