ERD
Tables (29)
account
- account_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- account_type — VARCHAR(20)
- institution_name — VARCHAR(50)
- account_number — VARCHAR(30)
- deposit_balance — DECIMAL(15,0)
- existing_account — TINYINT(1)
- opened_at — DATE
asset_class
- asset_class_id — INT — Primary key — NOT NULL
- asset_class_name — VARCHAR(50) — NOT NULL
- risk_level — INT — NOT NULL
- display_order — INT
asset_connection
- asset_connection_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- institution_name — VARCHAR(50) — NOT NULL
- category — VARCHAR(20) — NOT NULL
- connection_status — VARCHAR(20) — NOT NULL
- last_synced_at — DATETIME — NOT NULL
bond_detail
- bond_detail_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id — NOT NULL
- ticker_code — VARCHAR(20)
- listed_market — VARCHAR(20)
- bond_exp_type — VARCHAR(20)
- bond_issue_type — VARCHAR(50)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
cash_flow_event
- event_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- event_date — DATE
- event_type — VARCHAR(20)
- title — VARCHAR(100)
- amount — DECIMAL(15,0)
- flow_type — VARCHAR(10)
- status — VARCHAR(10)
- recurring — TINYINT(1) — NOT NULL
- source — VARCHAR(30)
consultation
- consultation_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- title — VARCHAR(100)
- consult_type — VARCHAR(20) — NOT NULL
- status — VARCHAR(20) — NOT NULL
- scheduled_at — DATETIME — NOT NULL
- method — VARCHAR(20) — NOT NULL
- branch_name — VARCHAR(100)
- counselor_name — VARCHAR(50)
- plan_id — BIGINT
- user_memo — VARCHAR(1000)
- created_at — DATETIME
- updated_at — DATETIME
consultation_summary
- consultation_summary_id — BIGINT — Primary key — NOT NULL
- consultation_id — BIGINT — Foreign key → consultation.consultation_id — NOT NULL
- diagnosis — VARCHAR(1000)
- created_at — DATETIME
- updated_at — DATETIME
consultation_summary_next_step
- consultation_summary_id — BIGINT — Foreign key → consultation_summary.consultation_summary_id — NOT NULL
- content — VARCHAR(500)
consultation_summary_recommendation
- consultation_summary_id — BIGINT — Foreign key → consultation_summary.consultation_summary_id — NOT NULL
- content — VARCHAR(500)
daily_price
- price_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id — NOT NULL
- price_date — DATE — NOT NULL
- closing_price — DECIMAL(15,2)
- open_price — DECIMAL(15,2)
- high_price — DECIMAL(15,2)
- low_price — DECIMAL(15,2)
- price_change — DECIMAL(15,2)
- change_rate — DECIMAL(7,2)
- nav — DECIMAL(15,2)
- trading_volume — BIGINT
- trading_value — DECIMAL(20,0)
- market_cap — DECIMAL(20,0)
- net_asset_total — DECIMAL(20,0)
- listed_shares — BIGINT
- index_price — DECIMAL(15,2)
- index_change — DECIMAL(15,2)
- index_change_rate — DECIMAL(7,2)
- closing_yield — DECIMAL(10,4)
- open_yield — DECIMAL(10,4)
- high_yield — DECIMAL(10,4)
- low_yield — DECIMAL(10,4)
- per — DECIMAL(10,2)
- pbr — DECIMAL(10,2)
- upper_limit_price — DECIMAL(15,2)
- lower_limit_price — DECIMAL(15,2)
debt
- debt_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- institution_name — VARCHAR(50) — NOT NULL
- loan_type — VARCHAR(30) — NOT NULL
- balance — DECIMAL(15,0) — NOT NULL
- monthly_repayment — DECIMAL(15,0) — NOT NULL
- interest_rate — DECIMAL(5,2)
- maturity_date — DATE
deposit_detail
- deposit_detail_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id — NOT NULL
- interest_rate — DECIMAL(5,2)
- maturity_months — INT
- subscription_limit — DECIMAL(15,0)
- deposit_insurance — TINYINT(1)
dividend_history
- dist_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id — NOT NULL
- dividend_type — VARCHAR(20)
- ex_dividend_date — DATE
- payment_date — DATE
- amount_per_unit — DECIMAL(15,2)
etf_detail
- etf_detail_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id — NOT NULL
- ticker_code — VARCHAR(20) — NOT NULL
- asset_manager — VARCHAR(50)
- brand_name — VARCHAR(20)
- retirement_pension_limit — VARCHAR(20)
- personal_pension_available — TINYINT(1)
- latest_dividend_rate — DECIMAL(7,2)
- annual_dividend_rate — DECIMAL(7,2)
- high_52w — DECIMAL(15,2)
- low_52w — DECIMAL(15,2)
- distribution_cycle — VARCHAR(20)
- distribution_interval_months — INT
- risk_grade — INT
financial_product
- product_id — BIGINT — Primary key — NOT NULL
- asset_class_id — INT — Foreign key → asset_class.asset_class_id — NOT NULL
- product_type — VARCHAR(20) — NOT NULL
- product_name — VARCHAR(200) — NOT NULL
- currency — VARCHAR(10)
- updated_at — DATETIME
holding
- holding_id — BIGINT — Primary key — NOT NULL
- account_id — BIGINT — Foreign key → account.account_id — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id
- evaluation_amount — DECIMAL(15,0)
- quantity — DECIMAL(18,4)
- avg_purchase_price — VARCHAR(255)
- unrealized_gain_loss — VARCHAR(255)
- frozen — TINYINT(1)
insurance_detail
- insurance_detail_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id — NOT NULL
- insurance_type — VARCHAR(50)
- coverage_amount — BIGINT
- self_pay_ratio — INT
- product_code — VARCHAR(50)
- sales_channel — VARCHAR(30)
- sales_start_date — DATE
- sales_end_date — DATE
- summary_pdf_url — TEXT
- business_method_pdf_url — TEXT
- terms_pdf_url — TEXT
insurance_policy
- insurance_policy_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- institution_name — VARCHAR(50) — NOT NULL
- insurance_type — VARCHAR(30) — NOT NULL
- monthly_premium — DECIMAL(15,0) — NOT NULL
- active — TINYINT(1) — NOT NULL
- medical_reserve — DECIMAL(15,0) — NOT NULL
notification
- notification_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- notification_type — VARCHAR(20) — NOT NULL
- title — VARCHAR(100) — NOT NULL
- content — VARCHAR(300) — NOT NULL
- link_target — VARCHAR(100)
- is_read — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
notification_setting
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- balance_enabled — TINYINT(1) — NOT NULL
- pension_enabled — TINYINT(1) — NOT NULL
- dividend_enabled — TINYINT(1) — NOT NULL
- report_enabled — TINYINT(1) — NOT NULL
pension
- pension_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- pension_type — VARCHAR(20)
- expected_monthly_amount — DECIMAL(15,0)
- variable — TINYINT(1)
- start_age — INT
pension_saving_detail
- pension_saving_detail_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id — NOT NULL
- pension_saving_type — VARCHAR(20)
- detail_type — VARCHAR(30)
- avg_return_rate — DECIMAL(5,2)
- return_rate_1y — DECIMAL(5,2)
- return_rate_2y — DECIMAL(5,2)
- return_rate_3y — DECIMAL(5,2)
- subscription_method — VARCHAR(50)
- provider_name — VARCHAR(100)
salary_asset_exclusion
- exclusion_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- asset_key — VARCHAR(50) — NOT NULL
- excluded_at — DATETIME — NOT NULL
stability_score
- stability_score_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- total_score — INT — NOT NULL
- grade — VARCHAR(30) — NOT NULL
- cashflow_coverage_rate — DECIMAL(8,2)
- essential_expense_rate — DECIMAL(8,2)
- medical_preparedness_months — DECIMAL(8,2)
- liquidity_months — DECIMAL(8,2)
- debt_burden_rate — DECIMAL(8,2)
- risk_asset_dependency_rate — DECIMAL(8,2)
- growth_plan_allowed — TINYINT(1) — NOT NULL
- recommended_plan_type — VARCHAR(30) — NOT NULL
- summary_message — VARCHAR(500)
- created_at — DATETIME
- updated_at — DATETIME
stock_detail
- stock_detail_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id — NOT NULL
- ticker_code — VARCHAR(20)
- listed_market — VARCHAR(20)
- dividend_yield — DECIMAL(5,2)
- sector — VARCHAR(50)
- available_account_types — VARCHAR(100)
survey_response
- response_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- question_code — VARCHAR(40) — NOT NULL
- answer_value — VARCHAR(40)
- answered_at — DATETIME
trade_history
- order_id — BIGINT — Primary key — NOT NULL
- account_id — BIGINT — Foreign key → account.account_id — NOT NULL
- product_id — BIGINT — Foreign key → financial_product.product_id
- trade_type — VARCHAR(10)
- traded_at — DATETIME
- quantity — DECIMAL(18,4)
- avg_price — DECIMAL(15,2)
- trade_amount — DECIMAL(15,2)
- fee — DECIMAL(10,2)
user_goal
- user_goal_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- monthly_target_living_cost — DECIMAL(15,0)
- monthly_expected_medical_cost — DECIMAL(15,0)
- created_at — DATETIME
- updated_at — DATETIME
users
- user_id — BIGINT — Primary key — NOT NULL
- name — VARCHAR(50)
- age — INT
- password — VARCHAR(255)
- retired — TINYINT(1)
- national_pension_receiving — TINYINT(1)
- phone — VARCHAR(20)
- id_number — VARCHAR(255)
- onboarding_completed — TINYINT(1) — NOT NULL
- investment_propensity — VARCHAR(20)
- created_at — DATETIME
- updated_at — DATETIME