가상 투자 ERD

테이블 (9)

accounts

  • id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • account_number — VARCHAR(30) — NOT NULL
  • balance — NUMERIC(18,2) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • seed_money — NUMERIC(18,2) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

daily_portfolio_summary

  • id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • account_id — BIGINT — 외래 키 → accounts.id — NOT NULL
  • summary_date — DATE — NOT NULL
  • total_asset — NUMERIC(18,2) — NOT NULL
  • total_profit — NUMERIC(18,2) — NOT NULL
  • profit_rate — NUMERIC(10,4) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

holdings

  • id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • account_id — BIGINT — 외래 키 → accounts.id — NOT NULL
  • stock_id — BIGINT — 외래 키 → stocks.id — NOT NULL
  • quantity — INT — NOT NULL
  • average_buy_price — NUMERIC(18,2) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

idempotency_keys

  • id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • order_id — BIGINT — 외래 키 → orders.id
  • request_key — VARCHAR(100) — NOT NULL
  • api_name — VARCHAR(50) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • expires_at — TIMESTAMP — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

orders

  • id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • account_id — BIGINT — 외래 키 → accounts.id — NOT NULL
  • stock_id — BIGINT — 외래 키 → stocks.id — NOT NULL
  • order_type — VARCHAR(20) — NOT NULL
  • order_status — VARCHAR(20) — NOT NULL
  • quantity — INT — NOT NULL
  • price — NUMERIC(18,2) — NOT NULL
  • total_amount — NUMERIC(18,2) — NOT NULL
  • fail_reason — VARCHAR(100)
  • requested_at — TIMESTAMP — NOT NULL
  • executed_at — TIMESTAMP
  • canceled_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

stock_price_daily

  • id — BIGINT — 기본 키 — NOT NULL
  • stock_id — BIGINT — 외래 키 → stocks.id — NOT NULL
  • trade_date — DATE — NOT NULL
  • open_price — NUMERIC(18,2) — NOT NULL
  • high_price — NUMERIC(18,2) — NOT NULL
  • low_price — NUMERIC(18,2) — NOT NULL
  • close_price — NUMERIC(18,2) — NOT NULL
  • volume — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

stocks

  • id — BIGINT — 기본 키 — NOT NULL
  • stock_code — VARCHAR(20) — NOT NULL
  • stock_name — VARCHAR(100) — NOT NULL
  • market_type — VARCHAR(20) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

transaction_history

  • id — BIGINT — 기본 키 — NOT NULL
  • account_id — BIGINT — 외래 키 → accounts.id — NOT NULL
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • order_id — BIGINT — 외래 키 → orders.id
  • transaction_type — VARCHAR(30) — NOT NULL
  • amount — NUMERIC(18,2) — NOT NULL
  • balance_after — NUMERIC(18,2) — NOT NULL
  • description — VARCHAR(255)
  • created_at — TIMESTAMP — NOT NULL

users

  • id — BIGINT — 기본 키 — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • role — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL