CoremateMVP

테이블 (15)

개념 관계 (concept_relation)

  • relation_id — BIGSERIAL
  • source_concept_id — BIGINT — NOT NULL
  • target_concept_id — BIGINT — NOT NULL
  • relation_type — VARCHAR(30) — NOT NULL
  • relation_note — TEXT
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

개념 관련 공통 오해 (common_mistake)

  • common_mistake_id — BIGSERIAL
  • concept_id — BIGINT — 외래 키 → 하위 개념.concept_id — NOT NULL
  • mistake_title — VARCHAR(200) — NOT NULL
  • mistake_text — TEXT — NOT NULL
  • correction_text — TEXT — NOT NULL
  • review_status — VARCHAR(20)
  • review_note — TEXT
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

과목명 (subject)

  • subject_id — BIGSERIAL
  • subject_code — VARCHAR(50) — NOT NULL
  • subject_name — VARCHAR(100) — NOT NULL
  • subject_name_en — VARCHAR(100)
  • sort_order — INT
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

대분류 (category_lv1)

  • category_lv1_id — BIGSERIAL
  • subject_id — BIGINT — 외래 키 → 과목명.subject_id — NOT NULL
  • category_lv1_code — VARCHAR(50) — NOT NULL
  • category_lv1_name — VARCHAR(100) — NOT NULL
  • category_lv1_name_en — VARCHAR(100)
  • description — TEXT
  • sort_order — INT
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • UNIQUE(subject_id, — category_lv1_code)
  • UNIQUE(subject_id, — category_lv1_name)

문제 (question)

  • question_id — BIGSERIAL
  • concept_id — BIGINT — 외래 키 → 하위 개념.concept_id — NOT NULL
  • hint_common_mistake_id — BIGINT — 외래 키 → 개념 관련 공통 오해.common_mistake_id
  • question_format — VARCHAR(30)
  • question_type — VARCHAR(30) — NOT NULL
  • question_text — TEXT — NOT NULL
  • choices_json — JSONB
  • correct_answer — VARCHAR(255) — NOT NULL
  • explanation_text — TEXT — NOT NULL
  • difficulty_level — SMALLINT
  • difficulty_source — VARCHAR(20)
  • expected_solve_sec — INT
  • content_hash — CHAR(64)
  • review_status — VARCHAR(20)
  • review_note — TEXT
  • source_type — VARCHAR(20)
  • source_refs — JSONB
  • is_active — BOOLEAN
  • version_no — INT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • approved_at — TIMESTAMP

문제별 풀이 로그 (solve_logs)

  • solve_log_id — BIGSERIAL
  • solve_session_id — BIGINT — 외래 키 → 문제집 풀이 회차.solve_session_id — NOT NULL
  • user_id — BIGINT — 외래 키 → 사용자.user_id — NOT NULL
  • question_id — BIGINT — 외래 키 → 문제.question_id — NOT NULL
  • question_order — INT — NOT NULL
  • selected_answer — VARCHAR(255)
  • correct_answer_snapshot — VARCHAR(255) — NOT NULL
  • is_correct — BOOLEAN — NOT NULL
  • time_spent_sec — INT
  • expected_solve_sec_snapshot — INT
  • hint_used — BOOLEAN
  • hint_count — INT
  • option_change_count — INT
  • is_answer_changed — BOOLEAN
  • is_bookmarked — BOOLEAN
  • solved_at — TIMESTAMP
  • created_at — TIMESTAMP
  • UNIQUE(solve_session_id, — question_id)
  • UNIQUE(solve_session_id, — question_order)

문제집 풀이 회차 (solve_sessions)

  • solve_session_id — BIGSERIAL
  • user_id — BIGINT — 외래 키 → 사용자.user_id — NOT NULL
  • session_type — VARCHAR(30) — NOT NULL
  • subject_id — BIGINT — 외래 키 → 과목명.subject_id
  • category_lv1_id — BIGINT — 외래 키 → 대분류.category_lv1_id
  • category_lv2_id — BIGINT — 외래 키 → 중분류.category_lv2_id
  • concept_id — BIGINT — 외래 키 → 하위 개념.concept_id
  • title — VARCHAR(200) — NOT NULL
  • total_question_count — INT
  • correct_count — INT
  • wrong_count — INT
  • total_time_spent_sec — INT
  • status — VARCHAR(20)
  • started_at — TIMESTAMP
  • completed_at — TIMESTAMP
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

사용자 (users)

  • user_id — BIGSERIAL
  • email — VARCHAR(255)
  • password_hash — VARCHAR(255)
  • nickname — VARCHAR(50) — NOT NULL
  • level — INT
  • total_xp — INT
  • created_at — TIMESTAMP

사용자 약점 분석표 (weakness_analysis_reports)

  • analysis_report_id — BIGSERIAL
  • user_id — BIGINT — 외래 키 → 사용자.user_id
  • analysis_text — TEXT
  • created_at — TIMESTAMP

유저 재화 (user_currency)

  • user_id — BIGINT — 외래 키 → 사용자.user_id
  • coin — INT

재화 로그 (currency_logs)

  • currency_log_id — BIGSERIAL
  • user_id — BIGINT — 외래 키 → 사용자.user_id — NOT NULL
  • amount — INT — NOT NULL
  • balance_after — INT — NOT NULL
  • reason — VARCHAR(50)
  • created_at — TIMESTAMP

중분류 (category_lv2)

  • category_lv2_id — BIGSERIAL
  • category_lv1_id — BIGINT — 외래 키 → 대분류.category_lv1_id — NOT NULL
  • category_lv2_code — VARCHAR(50) — NOT NULL
  • category_lv2_name — VARCHAR(100) — NOT NULL
  • category_lv2_name_en — VARCHAR(100)
  • description — TEXT
  • sort_order — INT
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • UNIQUE(category_lv1_id, — category_lv2_code)
  • UNIQUE(category_lv1_id, — category_lv2_name)

즐겨찾기 (bookmarks)

  • bookmark_id — BIGSERIAL
  • user_id — BIGINT — 외래 키 → 사용자.user_id — NOT NULL
  • question_id — BIGINT — 외래 키 → 문제.question_id — NOT NULL
  • UNIQUE(user_id, — question_id)

하위 개념 (concept)

  • concept_id — BIGSERIAL
  • concept_code — VARCHAR(100) — NOT NULL
  • category_lv2_id — BIGINT — 외래 키 → 중분류.category_lv2_id — NOT NULL
  • concept_name — VARCHAR(150) — NOT NULL
  • concept_name_en — VARCHAR(150)
  • aliases_json — JSONB
  • one_line_definition — TEXT — NOT NULL
  • core_summary — TEXT — NOT NULL
  • core_keywords_json — JSONB — NOT NULL
  • key_properties_json — JSONB — NOT NULL
  • source_refs — JSONB
  • sort_order — INT
  • review_status — VARCHAR(20)
  • review_note — TEXT
  • is_active — BOOLEAN
  • version_no — INT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • approved_at — TIMESTAMP
  • UNIQUE(category_lv2_id, — concept_name)

하위 개념 완료 상태 (user_concept_progress)

  • user_concept_progress_id — BIGSERIAL
  • user_id — BIGINT — 외래 키 → 사용자.user_id — NOT NULL
  • concept_id — BIGINT — 외래 키 → 하위 개념.concept_id — NOT NULL
  • is_completed — BOOLEAN
  • UNIQUE(user_id, — concept_id)