ERD

Tables (19)

admin_profiles

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • role_type — ENUM('SUPER_ADMIN', 'REGIONAL_ADMIN', 'CONTENT_MANAGER') — NOT NULL
  • department — VARCHAR(100)
  • phone — VARCHAR(30)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

comments

  • id — BIGINT
  • post_id — BIGINT — NOT NULL
  • user_id — BIGINT
  • content — TEXT — NOT NULL
  • parent_id — BIGINT
  • is_deleted — BOOLEAN
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

community_activity_logs

  • id — BIGINT
  • action — ENUM('POST', 'COMMENT', 'LIKE', 'REPORT') — NOT NULL
  • target_post_id — BIGINT
  • target_comment_id — BIGINT
  • description — TEXT
  • created_at — DATETIME — NOT NULL
  • user_id — BIGINT
  • region_id — BIGINT

mentor_certification_requests

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • document_url — TEXT — NOT NULL
  • status — ENUM('PENDING', 'APPROVED', 'REJECTED')
  • reviewed_by — BIGINT
  • reviewed_at — DATETIME
  • rejection_reason — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

mentor_income_logs

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • month — VARCHAR(7) — NOT NULL
  • level — ENUM('Lv2_ASSISTANT', 'Lv3_SENIOR') — NOT NULL
  • activity_score — INT — NOT NULL
  • weight_multiplier — FLOAT — NOT NULL
  • calculated_ratio — FLOAT — NOT NULL
  • payout_amount — INT — NOT NULL
  • is_paid — BOOLEAN
  • paid_at — DATETIME
  • created_at — DATETIME — NOT NULL

mentor_profiles

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • region_id — BIGINT
  • level — ENUM('Lv1_CHALLENGER', 'Lv2_ASSISTANT', 'Lv3_SENIOR', 'Lv4_ADMIN')
  • level_acquired_at — DATETIME
  • current_score — INT
  • warning_count — INT
  • is_certified_by_public — BOOLEAN
  • is_active — BOOLEAN
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

mentor_promotions

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • region_id — BIGINT
  • promotion_level — ENUM('Lv2_ASSISTANT', 'Lv3_SENIOR', 'Lv4_ADMIN') — NOT NULL
  • approved_by — BIGINT
  • promoted_at — DATETIME — NOT NULL
  • note — TEXT

messages

  • id — BIGINT
  • sender_id — BIGINT
  • receiver_id — BIGINT — NOT NULL
  • content — TEXT — NOT NULL
  • sent_at — DATETIME — NOT NULL

policies

  • id — BIGINT
  • title — VARCHAR(200) — NOT NULL
  • summary — TEXT — NOT NULL
  • category — VARCHAR(50) — NOT NULL
  • support_amount — INT — NOT NULL
  • deadline — DATE
  • external_url — TEXT
  • is_deleted — BOOLEAN
  • region_id — BIGINT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

policy_favorites

  • user_id — BIGINT — Primary key — NOT NULL
  • policy_id — BIGINT — Primary key — NOT NULL
  • created_at — DATETIME — NOT NULL

policy_interaction_logs

  • id — BIGINT
  • policy_id — BIGINT
  • user_id — BIGINT
  • action — ENUM('VIEW', 'LIKE', 'SAVE', 'SHARE', 'CLICK_EXTERNAL') — NOT NULL
  • platform — ENUM('WEB', 'MOBILE')
  • detail — JSON
  • created_at — DATETIME — NOT NULL

post_like_types

  • id — TINYINT
  • code — VARCHAR(20) — NOT NULL
  • label — VARCHAR(50) — NOT NULL

post_likes

  • user_id — BIGINT — Primary key — NOT NULL
  • post_id — BIGINT — Primary key — NOT NULL
  • like_type_id — TINYINT — Primary key — NOT NULL
  • created_at — DATETIME — NOT NULL

posts

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • region_id — BIGINT
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • tag — VARCHAR(50)
  • likes — INT
  • views — INT
  • is_notice — BOOLEAN
  • is_mentor_only — BOOLEAN
  • is_deleted — BOOLEAN
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

recommendation_logs

  • id — BIGINT
  • recommended_type — ENUM('POLICY', 'REGION') — NOT NULL
  • target_id — BIGINT — NOT NULL
  • score — FLOAT — NOT NULL
  • reason — TEXT
  • algorithm_version — VARCHAR(20)
  • ab_test_group — VARCHAR(50)
  • experiment_name — VARCHAR(100)
  • created_at — DATETIME — NOT NULL
  • user_id — BIGINT

roles

  • id — BIGINT
  • name — ENUM('USER', 'MENTOR', 'ADMIN') — NOT NULL
  • description — VARCHAR(100)

search_query_logs

  • id — BIGINT
  • keyword — VARCHAR(100)
  • category_filter — VARCHAR(50)
  • region_filter — VARCHAR(50)
  • result_count — INT
  • search_source — ENUM('HOME', 'COMMUNITY', 'POLICY', 'RECOMMEND')
  • device_type — VARCHAR(30)
  • session_id — VARCHAR(100)
  • searched_at — DATETIME — NOT NULL
  • user_id — BIGINT

user_roles

  • user_id — BIGINT — Primary key — NOT NULL
  • role_id — BIGINT — Primary key — NOT NULL
  • assigned_at — DATETIME — NOT NULL

users

  • id — BIGINT
  • email — VARCHAR(100) — NOT NULL — AES256 암호화 전제
  • password — VARCHAR(255)
  • nickname — VARCHAR(50) — NOT NULL — 익명 마스킹 기반 표시
  • age — VARCHAR(20)
  • is_social — BOOLEAN — NOT NULL
  • is_deleted — BOOLEAN
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • deleted_at — DATETIME
  • user_type — ENUM('USER', 'MENTOR', 'ADMIN') — NOT NULL