wmah-db

Tables (26)

aftercare_rules

  • rule_id — UUID — Primary key — NOT NULL
  • trigger_type — VARCHAR(40) — NOT NULL
  • rule_name — VARCHAR(120) — NOT NULL
  • description — TEXT
  • enabled — BOOLEAN — NOT NULL
  • config — JSONB — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

agent_run_logs

  • request_id — UUID — Primary key — NOT NULL
  • agent_name — VARCHAR(80) — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id
  • input_hash — VARCHAR(128)
  • status — VARCHAR(20) — NOT NULL
  • latency_ms — INTEGER
  • error_message — TEXT
  • data_as_of — DATE
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

alert_candidates

  • alert_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • trigger_type — VARCHAR(40) — NOT NULL
  • reason — TEXT — NOT NULL
  • detected_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • action_status — VARCHAR(20) — NOT NULL
  • draft_message — TEXT
  • scheduled_at — TIMESTAMP WITH TIME ZONE
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

cashflow_statement

  • cashflow_statement_id — UUID — Primary key — NOT NULL
  • consult_id — UUID — Foreign key → wm_cnslt_manage.consult_id
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • as_of — DATE — NOT NULL
  • total_income — NUMERIC(20, 2)
  • total_expense — NUMERIC(20, 2)
  • savings_capacity — NUMERIC(20, 2)
  • annual_irregular_income — NUMERIC(20, 2)
  • annual_irregular_expense — NUMERIC(20, 2)
  • income — JSONB — NOT NULL
  • saving — JSONB — NOT NULL
  • expense_fixed — JSONB — NOT NULL
  • expense_variable — JSONB — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

chat_messages

  • message_id — UUID — Primary key — NOT NULL
  • chat_session_id — UUID — Foreign key → chat_sessions.chat_session_id
  • related_proposal_id — UUID — Foreign key → rebalancing_proposals.proposal_id
  • role — VARCHAR(10) — NOT NULL
  • message_type — VARCHAR(40) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • content — TEXT — NOT NULL
  • payload — JSONB — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

chat_sessions

  • chat_session_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • session_status — VARCHAR(20) — NOT NULL
  • title — VARCHAR(160)
  • ticker_context — VARCHAR(30)
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

compliance_rules

  • rule_id — UUID — Primary key — NOT NULL
  • rule_code — VARCHAR(40) — NOT NULL
  • category — VARCHAR(40) — NOT NULL
  • pattern — TEXT — NOT NULL
  • action — VARCHAR(20) — NOT NULL
  • severity — VARCHAR(20) — NOT NULL
  • message — TEXT
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

customer_financial_statements

  • statement_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • consult_id — UUID — Foreign key → wm_cnslt_manage.consult_id
  • total_assets — NUMERIC(20, 2) — NOT NULL
  • total_liabilities — NUMERIC(20, 2) — NOT NULL
  • net_worth — NUMERIC(20, 2) — NOT NULL
  • liquidity_ratio — NUMERIC(8, 4)
  • debt_ratio — NUMERIC(8, 4)
  • income_expense_ratio — NUMERIC(8, 4)
  • investment_asset_ratio — NUMERIC(8, 4)
  • saving_rate — NUMERIC(8, 4)
  • as_of — DATE — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

customer_investment_profiles

  • investment_profile_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • risk_profile — VARCHAR(40) — NOT NULL
  • target_allocation — JSONB — NOT NULL
  • constraints — JSONB — NOT NULL
  • goal_description — TEXT
  • goal_period_months — INTEGER
  • goal_target_amount — NUMERIC(20, 2)
  • qualitative — JSONB — NOT NULL
  • effective_from — DATE — NOT NULL
  • effective_to — DATE
  • is_current — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

customer_profiles

  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • segment — VARCHAR(40)
  • grade — VARCHAR(20)
  • birth_date — DATE
  • occupation — VARCHAR(200)
  • income_type — VARCHAR(40)
  • marital_status — VARCHAR(20)
  • region — VARCHAR(120)
  • family — VARCHAR(200)
  • housing_status — VARCHAR(40)
  • life_stage — VARCHAR(80)
  • contact_detail — JSONB — NOT NULL
  • contract_start_date — DATE
  • last_contact_at — DATE
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

customers

  • customer_id — UUID — Primary key — NOT NULL
  • customer_code — VARCHAR(40) — NOT NULL
  • masked_name — VARCHAR(80) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

employee_aftercare_settings

  • rule_id — UUID — Primary key — NOT NULL
  • employee_id — UUID — Foreign key → employees.employee_id — NOT NULL
  • trigger_type — VARCHAR(40) — NOT NULL
  • rule_name — VARCHAR(120) — NOT NULL
  • description — TEXT
  • enabled — BOOLEAN — NOT NULL
  • config — JSONB — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

employee_customers

  • employee_customer_id — UUID — Primary key — NOT NULL
  • customer_id — UUID — Foreign key → customers.customer_id — NOT NULL
  • employee_id — UUID — Foreign key → employees.employee_id — NOT NULL
  • relationship_role — VARCHAR(40) — NOT NULL
  • is_primary — BOOLEAN — NOT NULL
  • assigned_from — DATE — NOT NULL
  • assigned_to — DATE
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

employees

  • employee_id — UUID — Primary key — NOT NULL
  • employee_code — VARCHAR(40) — NOT NULL
  • employee_name — VARCHAR(80) — NOT NULL
  • branch_name — VARCHAR(120)
  • role — VARCHAR(20) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

holdings

  • holding_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • product_id — UUID — Foreign key → product_master.product_id — NOT NULL
  • quantity — NUMERIC(20, 6)
  • buy_price — NUMERIC(20, 4)
  • principal_amount — NUMERIC(20, 2)
  • current_value — NUMERIC(20, 2)
  • account_type — VARCHAR(40) — NOT NULL
  • acquired_at — DATE
  • maturity_at — DATE
  • applied_interest_rate — NUMERIC(8, 4)
  • details — JSONB — NOT NULL
  • source_as_of — TIMESTAMP WITH TIME ZONE
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

house_views

  • house_view_id — UUID — Primary key — NOT NULL
  • target_type — VARCHAR(20) — NOT NULL
  • target_key — VARCHAR(120) — NOT NULL
  • signal — VARCHAR(40) — NOT NULL
  • major_opinion — TEXT — NOT NULL
  • minor_opinion — TEXT
  • conflict_summary — TEXT
  • risk_flags — JSONB — NOT NULL
  • confidence — NUMERIC(5, 4)
  • is_seed — BOOLEAN — NOT NULL
  • source_name — VARCHAR(80) — NOT NULL
  • source_as_of — DATE — NOT NULL
  • valid_until — DATE
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

liabilities

  • liability_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • liability_type — VARCHAR(40) — NOT NULL
  • amount — NUMERIC(20, 2) — NOT NULL
  • interest_rate — NUMERIC(8, 4)
  • started_at — DATE
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

macro_indicators

  • macro_indicator_id — UUID — Primary key — NOT NULL
  • indicator_code — VARCHAR(40) — NOT NULL
  • indicator_name — VARCHAR(120) — NOT NULL
  • value — NUMERIC(20, 6) — NOT NULL
  • unit — VARCHAR(20) — NOT NULL
  • as_of — DATE — NOT NULL
  • change_rate — NUMERIC(12, 6)
  • source — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

market_briefings

  • briefing_id — UUID — Primary key — NOT NULL
  • briefing_type — VARCHAR(40) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • content — JSONB — NOT NULL
  • sources — JSONB — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • as_of — TIMESTAMP WITH TIME ZONE — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

portfolio_analysis_snapshots

  • analysis_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • total_eval_amount — NUMERIC(20, 2) — NOT NULL
  • total_buy_amount — NUMERIC(20, 2) — NOT NULL
  • total_profit_amount — NUMERIC(20, 2) — NOT NULL
  • total_profit_rate — NUMERIC(12, 6)
  • asset_allocation — JSONB — NOT NULL
  • target_gap — JSONB — NOT NULL
  • holdings_detail — JSONB — NOT NULL
  • quote_summary — JSONB — NOT NULL
  • analysis_status — VARCHAR(20) — NOT NULL
  • warnings — JSONB — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

product_master

  • product_id — UUID — Primary key — NOT NULL
  • product_code — VARCHAR(40) — NOT NULL
  • product_name — VARCHAR(200) — NOT NULL
  • product_type — VARCHAR(20) — NOT NULL
  • issuer — VARCHAR(120)
  • is_managed — BOOLEAN — NOT NULL
  • currency — VARCHAR(10) — NOT NULL
  • meta — JSONB — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

proposal_documents

  • document_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • rebalance_id — UUID — Foreign key → rebalancing_proposals.proposal_id — NOT NULL
  • template — VARCHAR(80) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • sections — JSONB — NOT NULL
  • wm_comment — TEXT
  • status — VARCHAR(30) — NOT NULL
  • version — INTEGER — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

rebalancing_proposals

  • proposal_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • analysis_id — UUID — Foreign key → portfolio_analysis_snapshots.analysis_id — NOT NULL
  • proposal_status — VARCHAR(20) — NOT NULL
  • house_view_status — VARCHAR(20) — NOT NULL
  • compliance_status — VARCHAR(20) — NOT NULL
  • diagnosis_result — JSONB — NOT NULL
  • actions — JSONB — NOT NULL
  • final_response — JSONB — NOT NULL
  • warnings — JSONB — NOT NULL
  • blocked_reason — TEXT
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

stock_info

  • product_id — UUID — Foreign key → product_master.product_id — NOT NULL
  • ticker — VARCHAR(30) — NOT NULL
  • market — VARCHAR(30) — NOT NULL
  • stock_name — VARCHAR(120) — NOT NULL
  • sector — VARCHAR(80)
  • currency — VARCHAR(10) — NOT NULL
  • kis_market_code — VARCHAR(20)
  • current_price — NUMERIC(20, 4) — NOT NULL
  • change_rate — NUMERIC(12, 6)
  • volume — BIGINT
  • source — VARCHAR(40) — NOT NULL
  • source_as_of — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

wm_cnslt_manage

  • consult_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • consult_date — DATE — NOT NULL
  • consult_type — VARCHAR(40) — NOT NULL
  • content — TEXT — NOT NULL
  • result — VARCHAR(80)
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

wm_comments

  • comment_id — UUID — Primary key — NOT NULL
  • employee_customer_id — UUID — Foreign key → employee_customers.employee_customer_id — NOT NULL
  • proposal_id — UUID — Foreign key → rebalancing_proposals.proposal_id
  • content — TEXT — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL