0706 PP2

Tables (18)

comments

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

community_activity_logs

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • action — ENUM('POST_CREATE', 'COMMENT', 'LIKE_POST', 'LIKE_COMMENT', 'REPORT') — NOT NULL
  • post_id — BIGINT
  • comment_id — BIGINT
  • tag — VARCHAR(50)
  • detail — TEXT
  • created_at — DATETIME — NOT NULL

messages

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

notification_logs

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • channel — ENUM('EMAIL', 'PUSH', 'SMS') — NOT NULL
  • action — ENUM('SENT', 'OPENED', 'CLICKED') — NOT NULL
  • campaign_id — VARCHAR(100)
  • metadata — JSON
  • occurred_at — DATETIME — NOT NULL

policies

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

policy_alerts

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • policy_id — BIGINT — NOT NULL
  • is_enabled — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

policy_favorites

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • policy_id — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL

policy_interaction_logs

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

posts

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

recommendation_logs

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

recommendations

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • region_id — BIGINT — NOT NULL
  • score — INT — NOT NULL
  • reasons — TEXT
  • created_at — DATETIME — NOT NULL

region_view_logs

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • region_id — BIGINT — NOT NULL
  • view_type — ENUM('RANKING', 'DETAIL', 'RECOMMENDED') — NOT NULL
  • device_type — VARCHAR(50)
  • created_at — DATETIME — NOT NULL

regions

  • id — BIGINT
  • name — VARCHAR(100) — NOT NULL
  • level — INT — NOT NULL
  • parent_id — BIGINT
  • timezone — VARCHAR(50)
  • country_code — VARCHAR(3)
  • INDEX — idx_region_parent

reports

  • id — BIGINT
  • target_type — ENUM('POST','COMMENT') — NOT NULL
  • target_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • reason — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL

search_query_logs

  • id — BIGINT
  • user_id — BIGINT
  • keyword — VARCHAR(100)
  • category_filter — VARCHAR(50)
  • region_filter — VARCHAR(50)
  • result_count — INT
  • searched_at — DATETIME — NOT NULL

surveys

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • housing — VARCHAR(10)
  • transport — VARCHAR(10)
  • other_answers — TEXT
  • created_at — DATETIME — NOT NULL

user_regions

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • region_id — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL

users

  • id — BIGINT
  • email — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255)
  • nickname — VARCHAR(50) — NOT NULL
  • age_group — VARCHAR(20)
  • role — ENUM('USER','MENTOR','ADMIN') — NOT NULL
  • is_social — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • CHECK — (is_social = TRUE OR password IS NOT NULL) — NOT NULL