5조 백엔드&프론트엔드rev3

테이블 (14)

Chat_Message

  • message_id — BIGINT — NOT NULL
  • room_id — BIGINT — 외래 키 → Chat_Room.room_id — NOT NULL
  • sender_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • content — TEXT — NOT NULL
  • msg_type — VARCHAR(20)
  • created_at — DATETIME

Chat_Room

  • room_id — BIGINT — NOT NULL
  • last_message — TEXT
  • last_sent_at — DATETIME
  • created_at — DATETIME

Chat_Room_Member

  • room_member_id — BIGINT — NOT NULL
  • room_id — BIGINT — 외래 키 → Chat_Room.room_id — NOT NULL
  • user_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • last_read_at — DATETIME
  • is_active — TINYINT(1)

Email_Verification

  • verification_id — BIGINT — NOT NULL
  • user_id — BIGINT — 외래 키 → User.user_id — 기존 유저 본인인증 시 사용(Nullable)
  • email — VARCHAR(100) — NOT NULL
  • auth_code — CHAR(6) — NOT NULL
  • attempt_count — INT — 인증 시도 횟수
  • is_verified — TINYINT(1)
  • expired_at — DATETIME — NOT NULL
  • created_at — DATETIME

Friend

  • friend_id — BIGINT — NOT NULL
  • requester_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • receiver_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • status — VARCHAR(20) — PENDING/ACCEPTED
  • created_at — DATETIME

Group

  • group_id — BIGINT — NOT NULL
  • group_name — VARCHAR(20) — NOT NULL
  • intro — VARCHAR(50)
  • apply_reason — TEXT
  • invite_code — VARCHAR(20)
  • creator_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • status — VARCHAR(20) — PENDING/APPROVED/REJECTED
  • reject_reason — TEXT
  • created_at — DATETIME

Group_Member

  • group_member_id — BIGINT — NOT NULL
  • group_id — BIGINT — 외래 키 → Group.group_id — NOT NULL
  • user_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • group_nickname — VARCHAR(20) — NOT NULL
  • joined_at — DATETIME

Music_Analysis

  • analysis_id — BIGINT — NOT NULL
  • user_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • artist — VARCHAR(255) — NOT NULL
  • album_cover — VARCHAR(500)
  • bpm — FLOAT — 유사도 계산용
  • energy — FLOAT — 유사도 계산용
  • genre — VARCHAR(50)
  • mood — VARCHAR(50)
  • instruments — VARCHAR(255)
  • rank_score — INT — 최애곡 순위(1~10)
  • created_at — DATETIME

Notification

  • noti_id — BIGINT — NOT NULL
  • user_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • noti_type — VARCHAR(20) — NOT NULL
  • message — VARCHAR(255) — NOT NULL
  • is_read — TINYINT(1)
  • created_at — DATETIME

Report

  • report_id — BIGINT — NOT NULL
  • reporter_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • target_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • reason — TEXT — NOT NULL
  • created_at — DATETIME

User

  • user_id — BIGINT — NOT NULL — 사용자 PK
  • email — VARCHAR(100) — NOT NULL — 이메일(아이디)
  • password — VARCHAR(255) — NOT NULL — 비밀번호(암호화)
  • kakao_id — VARCHAR(255) — 카카오 소셜 UID
  • gender — CHAR(1) — 성별 (M/F)
  • age — INT — 나이
  • profile_img — VARCHAR(500) — 프로필 이미지 경로
  • role — VARCHAR(20) — 권한 (ADMIN/USER)
  • status — VARCHAR(20) — 상태 (ACTIVE/SUSPENDED/WITHDRAWN)
  • is_onboarded — TINYINT(1) — 온보딩 완료 여부(ONBOARD_001)
  • created_at — DATETIME — 가입일

User_Setting

  • setting_id — BIGINT — NOT NULL
  • user_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • msg_alarm — TINYINT(1)
  • friend_alarm — TINYINT(1)

User_Suspension

  • suspension_id — BIGINT — NOT NULL
  • user_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • ended_at — DATETIME — NOT NULL — 정지 종료일

User_Token

  • token_id — BIGINT — NOT NULL
  • user_id — BIGINT — 외래 키 → User.user_id — NOT NULL
  • refresh_token — VARCHAR(500) — NOT NULL
  • expired_at — DATETIME — NOT NULL