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)