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