추리게임

테이블 (31)

ai_generation_logs

  • id — BIGINT
  • user_id — BIGINT — 외래 키 → users.id
  • scenario_id — BIGINT — 외래 키 → scenarios.id
  • request_type — VARCHAR(50) — NOT NULL
  • model_name — VARCHAR(100)
  • prompt_summary — TEXT
  • result_status — VARCHAR(30) — NOT NULL
  • input_tokens — INT — NOT NULL
  • output_tokens — INT — NOT NULL
  • cost_credit — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

credit_transactions

  • id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 — NOT NULL
  • wallet_id — BIGINT — 외래 키 → credit_wallets.id — NOT NULL
  • amount — INT — NOT NULL
  • transaction_type — VARCHAR(50) — NOT NULL
  • reference_type — VARCHAR(50)
  • reference_id — BIGINT
  • balance_after — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

credit_wallets

  • id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 — NOT NULL
  • balance — INT — NOT NULL
  • version — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

evidence_suspects

  • id — BIGINT
  • evidence_id — BIGINT — 외래 키 → evidences.id — NOT NULL
  • suspect_id — BIGINT — 외래 키 → suspects.id — NOT NULL
  • relation_type — VARCHAR(50) — NOT NULL
  • created_at — DATETIME — NOT NULL

evidences

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • location_id — BIGINT — 외래 키 → scenario_locations.id
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT — NOT NULL
  • evidence_type — VARCHAR(50) — NOT NULL
  • importance — VARCHAR(30) — NOT NULL
  • image_url — VARCHAR(500)
  • is_initial_public — TINYINT(1) — NOT NULL
  • unlock_type — VARCHAR(50) — NOT NULL
  • unlock_condition_json — TEXT
  • unlock_after_minutes — INT
  • sort_order — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

final_deduction_evidences

  • id — BIGINT
  • final_deduction_id — BIGINT — 외래 키 → final_deductions.id — NOT NULL
  • evidence_id — BIGINT — 외래 키 → evidences.id — NOT NULL
  • created_at — DATETIME — NOT NULL

final_deductions

  • id — BIGINT
  • play_session_id — BIGINT — 외래 키 → play_sessions.id — NOT NULL
  • selected_culprit_id — BIGINT — 외래 키 → suspects.id
  • motive_text — TEXT
  • method_text — TEXT
  • cover_up_text — TEXT
  • score — INT — NOT NULL
  • grade — VARCHAR(20)
  • feedback — TEXT
  • submitted_at — DATETIME — NOT NULL

hints

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • hint_level — INT — NOT NULL
  • content — TEXT — NOT NULL
  • unlock_after_minutes — INT
  • penalty_score — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

interrogation_logs

  • id — BIGINT
  • play_session_id — BIGINT — 외래 키 → play_sessions.id — NOT NULL
  • suspect_id — BIGINT — 외래 키 → suspects.id — NOT NULL
  • presented_evidence_id — BIGINT — 외래 키 → evidences.id
  • question_type — VARCHAR(50) — NOT NULL
  • question — TEXT — NOT NULL
  • answer — TEXT — NOT NULL
  • ai_model — VARCHAR(100)
  • created_at — DATETIME — NOT NULL

play_sessions

  • id — BIGINT
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • started_at — DATETIME — NOT NULL
  • ended_at — DATETIME
  • current_elapsed_seconds — INT — NOT NULL
  • score — INT — NOT NULL
  • grade — VARCHAR(20)
  • hint_count — INT — NOT NULL
  • interrogation_count — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

scenario_accesses

  • id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 — NOT NULL
  • scenario_id — BIGINT — 외래 키 — NOT NULL
  • access_type — VARCHAR(50) — NOT NULL
  • purchase_id — BIGINT — 외래 키 → scenario_purchases.id
  • granted_at — DATETIME — NOT NULL
  • revoked_at — DATETIME

scenario_bookmarks

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • created_at — DATETIME — NOT NULL

scenario_locations

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • map_x — INT
  • map_y — INT
  • sort_order — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

scenario_purchases

  • id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 — NOT NULL
  • scenario_id — BIGINT — 외래 키 — NOT NULL
  • price_credit — INT — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • purchased_at — DATETIME — NOT NULL
  • refunded_at — DATETIME

scenario_reports

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • reporter_id — BIGINT — 외래 키 → users.id — NOT NULL
  • reason — VARCHAR(255) — NOT NULL
  • detail — TEXT
  • status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • processed_at — DATETIME

scenario_reviews

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • rating — INT — NOT NULL
  • content — TEXT
  • is_spoiler — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

scenario_tags

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • tag_id — BIGINT — 외래 키 → tags.id — NOT NULL
  • created_at — DATETIME — NOT NULL

scenario_validation_results

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • validation_status — VARCHAR(30) — NOT NULL
  • validation_score — INT
  • problem_summary — TEXT
  • suggestion — TEXT
  • checked_at — DATETIME — NOT NULL

scenario_versions

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • version_number — INT — NOT NULL
  • case_graph_json — TEXT
  • change_note — TEXT
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

scenarios

  • id — BIGINT
  • creator_id — BIGINT — 외래 키 → users.id
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT
  • synopsis — TEXT
  • thumbnail_url — VARCHAR(500)
  • scenario_type — VARCHAR(30) — NOT NULL
  • visibility — VARCHAR(30) — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • difficulty — VARCHAR(30) — NOT NULL
  • player_count_min — INT — NOT NULL
  • player_count_max — INT — NOT NULL
  • estimated_play_time_minutes — INT
  • play_count — INT — NOT NULL
  • average_rating — DECIMAL(3,2) — NOT NULL
  • rating_count — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • published_at — DATETIME

solution_evidences

  • id — BIGINT
  • solution_id — BIGINT — 외래 키 → solutions.id — NOT NULL
  • evidence_id — BIGINT — 외래 키 → evidences.id — NOT NULL
  • reason — TEXT
  • created_at — DATETIME — NOT NULL

solutions

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • culprit_suspect_id — BIGINT — 외래 키 → suspects.id — NOT NULL
  • motive — TEXT — NOT NULL
  • method — TEXT — NOT NULL
  • cover_up — TEXT
  • full_explanation — TEXT
  • created_at — DATETIME — NOT NULL

suspect_response_policies

  • id — BIGINT
  • suspect_id — BIGINT — 외래 키 → suspects.id — NOT NULL
  • condition_key — VARCHAR(100) — NOT NULL
  • user_intent — VARCHAR(100)
  • policy_text — TEXT — NOT NULL
  • priority — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

suspect_secrets

  • id — BIGINT
  • suspect_id — BIGINT — 외래 키 → suspects.id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • secret_level — VARCHAR(30) — NOT NULL
  • unlock_condition_json — TEXT
  • is_core_secret — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

suspects

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • role — VARCHAR(100)
  • relation_to_victim — VARCHAR(255)
  • public_profile — TEXT
  • public_statement — TEXT
  • alibi — TEXT
  • personality_prompt — TEXT
  • response_policy_json — TEXT
  • suspicion_level — INT — NOT NULL
  • sort_order — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

tags

  • id — BIGINT
  • name — VARCHAR(50) — NOT NULL
  • created_at — DATETIME — NOT NULL

timeline_events

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • related_suspect_id — BIGINT — 외래 키 → suspects.id
  • related_evidence_id — BIGINT — 외래 키 → evidences.id
  • event_time — VARCHAR(50) — NOT NULL
  • event_order — INT — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT
  • event_type — VARCHAR(50) — NOT NULL
  • is_true_event — TINYINT(1) — NOT NULL
  • visibility — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL

unlocked_evidences

  • id — BIGINT
  • play_session_id — BIGINT — 외래 키 → play_sessions.id — NOT NULL
  • evidence_id — BIGINT — 외래 키 → evidences.id — NOT NULL
  • unlocked_reason — VARCHAR(255)
  • unlocked_at — DATETIME — NOT NULL

used_hints

  • id — BIGINT
  • play_session_id — BIGINT — 외래 키 → play_sessions.id — NOT NULL
  • hint_id — BIGINT — 외래 키 → hints.id — NOT NULL
  • used_at — DATETIME — NOT NULL

users

  • id — BIGINT
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • profile_image_url — VARCHAR(500)
  • role — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

victims

  • id — BIGINT
  • scenario_id — BIGINT — 외래 키 → scenarios.id — NOT NULL
  • found_location_id — BIGINT — 외래 키 → scenario_locations.id
  • name — VARCHAR(100) — NOT NULL
  • age — INT
  • role — VARCHAR(100)
  • description — TEXT
  • cause_of_death — VARCHAR(255)
  • found_condition — TEXT
  • created_at — DATETIME — NOT NULL