Themis_DB_설계

테이블 (24)

activity_logs

  • id — BIGINT
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • activity_type — ENUM('계약서분석', '증거기록', '퀘스트완료', '리포트생성', '상담요청') — NOT NULL
  • title — VARCHAR(200)
  • description — VARCHAR(500)
  • related_id — INT
  • created_at — DATETIME

ai_chat_messages

  • id — BIGINT
  • case_id — INT — 외래 키 → cases.id — NOT NULL
  • sender — ENUM('user', 'ai') — NOT NULL
  • content — TEXT — NOT NULL
  • suggested_quests — JSON
  • sent_at — DATETIME

case_matches

  • id — INT
  • case_id_a — INT — 외래 키 → cases.id — NOT NULL
  • case_id_b — INT — 외래 키 → cases.id — NOT NULL
  • similarity_score — FLOAT
  • matched_keywords — JSON
  • matched_at — DATETIME

cases

  • id — INT
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • category — ENUM('전세사기', '임금체불', '데이트폭력', '계약분쟁', '기타') — NOT NULL
  • stage — ENUM('예방', '기록', '대응', '연대')
  • status — ENUM('진행중', '대응중', '종결')
  • evidence_count — INT
  • created_at — DATETIME

chat_messages

  • id — BIGINT
  • chatroom_id — INT — 외래 키 → chatrooms.id — NOT NULL
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • message_type — ENUM('text', 'image', 'pdf', 'system')
  • content — TEXT
  • attachment_url — VARCHAR(500)
  • sent_at — DATETIME

chatroom_members

  • id — INT
  • chatroom_id — INT — 외래 키 → chatrooms.id — NOT NULL
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • role — ENUM('방장', '멤버', '전문가')
  • joined_at — DATETIME

chatrooms

  • id — INT
  • origin_post_id — INT — 외래 키 → community_posts.id
  • name — VARCHAR(200) — NOT NULL
  • room_type — ENUM('피해자모임', '전문가상담', '1대1')
  • member_count — INT
  • is_sos_eligible — BOOLEAN
  • created_at — DATETIME

clause_risks

  • id — INT
  • clause_id — INT — 외래 키 → contract_clauses.id — NOT NULL
  • risk_level — ENUM('위험', '주의', '양호') — NOT NULL
  • risk_title — VARCHAR(200)
  • reason — TEXT
  • recommendation — TEXT
  • analyzed_at — DATETIME

community_comments

  • id — INT
  • post_id — INT — 외래 키 → community_posts.id — NOT NULL
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • content — TEXT — NOT NULL
  • is_expert_answer — BOOLEAN
  • created_at — DATETIME

community_posts

  • id — INT
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • board_type — ENUM('hot', '질문', '공론화')
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT
  • is_hot — BOOLEAN
  • is_anonymous — BOOLEAN
  • answer_count — INT
  • view_count — INT
  • created_at — DATETIME

consultations

  • id — INT
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • expert_id — INT — 외래 키 → experts.id — NOT NULL
  • case_id — INT — 외래 키 → cases.id
  • status — ENUM('답변 대기', '답변 완료', '취소')
  • question — TEXT
  • requested_at — DATETIME
  • answered_at — DATETIME

contract_clauses

  • id — INT
  • contract_id — INT — 외래 키 → contracts.id — NOT NULL
  • clause_number — VARCHAR(20)
  • clause_text — TEXT — NOT NULL
  • order_index — INT

contracts

  • id — INT
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • contract_type — ENUM('전세', '월세', '매매', '근로', '프리랜서', '기타') — NOT NULL
  • original_image_url — VARCHAR(500)
  • ocr_text — TEXT
  • risk_score — INT
  • risk_percentile — INT
  • risk_label — VARCHAR(100)
  • analyzed_at — DATETIME
  • created_at — DATETIME

deadman_switches

  • id — INT
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • check_interval_minutes — INT
  • last_check_in — DATETIME
  • is_active — BOOLEAN
  • triggered_at — DATETIME

emergency_contacts

  • id — INT
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20) — NOT NULL
  • relation — ENUM('가족', '친구', '변호사', '기타')
  • is_primary — BOOLEAN

evidence_hashes

  • id — BIGINT
  • evidence_log_id — BIGINT — 외래 키 → evidence_logs.id — NOT NULL
  • hash_value — VARCHAR(64) — NOT NULL
  • hash_algorithm — VARCHAR(20)
  • created_at — DATETIME

evidence_logs

  • id — BIGINT
  • case_id — INT — 외래 키 → cases.id — NOT NULL
  • evidence_type — ENUM('photo', 'audio', 'video', 'text') — NOT NULL
  • file_url — VARCHAR(500)
  • thumbnail_url — VARCHAR(500)
  • duration_seconds — INT
  • gps_latitude — DECIMAL(10, 8)
  • gps_longitude — DECIMAL(11, 8)
  • location_name — VARCHAR(200)
  • recorded_at — DATETIME
  • vlm_status — ENUM('대기', '분석중', '완료', '실패')
  • vlm_tags — JSON
  • stt_text — TEXT
  • memo — TEXT

experts

  • id — INT
  • name — VARCHAR(50) — NOT NULL
  • specialty — ENUM('변호사', '법무사', '노무사', '상담사') — NOT NULL
  • license_number — VARCHAR(50)
  • profile_image_url — VARCHAR(500)
  • contact — VARCHAR(100)
  • rating — DECIMAL(2, 1)

notifications

  • id — INT
  • user_id — INT — 외래 키 → users.id — NOT NULL
  • type — ENUM('퀘스트', '데드맨', '상담답변', '댓글', '시스템') — NOT NULL
  • message — VARCHAR(500) — NOT NULL
  • is_read — BOOLEAN
  • created_at — DATETIME

quest_cards

  • id — INT
  • title — VARCHAR(200) — NOT NULL
  • category — ENUM('전세사기', '임금체불', '데이트폭력', '공통') — NOT NULL
  • description — TEXT
  • total_steps — INT

quest_steps

  • id — INT
  • user_quest_id — INT — 외래 키 → user_quests.id — NOT NULL
  • step_order — INT — NOT NULL
  • step_title — VARCHAR(200) — NOT NULL
  • is_done — BOOLEAN
  • done_at — DATETIME

reports

  • id — INT
  • case_id — INT — 외래 키 → cases.id — NOT NULL
  • pdf_url — VARCHAR(500) — NOT NULL
  • evidence_count — INT
  • generated_at — DATETIME

user_quests

  • id — INT
  • case_id — INT — 외래 키 → cases.id — NOT NULL
  • quest_card_id — INT — 외래 키 → quest_cards.id — NOT NULL
  • current_step — INT
  • status — ENUM('진행중', '완료', '보류')
  • started_at — DATETIME
  • completed_at — DATETIME

users

  • id — INT
  • email — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20)
  • profile_image_url — VARCHAR(500)
  • joined_at — DATE
  • created_at — DATETIME