test

Tables (37)

attendance_penalties

  • attendance_penalty_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id
  • penalty_type — VARCHAR(20) — NOT NULL — EARLY_CANCEL,LATE_CANCEL,NO_SHOW
  • experience_delta — INT — NOT NULL
  • no_show_count_delta — INT — NOT NULL
  • expires_at — DATETIME — 6개월 후 소멸 정책
  • created_at — DATETIME — NOT NULL

chatbot_conversations

  • chatbot_conversation_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • chatbot_persona_id — BIGINT — Foreign key → chatbot_personas.chatbot_persona_id — NOT NULL
  • conversation_stage — VARCHAR(20) — NOT NULL — BEFORE_DATE,AFTER_DATE
  • status — VARCHAR(20) — NOT NULL — ACTIVE,CLOSED
  • last_user_message_at — DATETIME
  • proactive_message_sent_at — DATETIME — 12시간 무응답 선톡 1회
  • created_at — DATETIME — NOT NULL
  • closed_at — DATETIME

chatbot_messages

  • chatbot_message_id — BIGINT — Primary key — NOT NULL
  • chatbot_conversation_id — BIGINT — Foreign key → chatbot_conversations.chatbot_conversation_id — NOT NULL
  • sender_type — VARCHAR(20) — NOT NULL — USER,ASSISTANT,SYSTEM
  • message_text — TEXT — NOT NULL
  • is_proactive — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

chatbot_personas

  • chatbot_persona_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • speech_style — VARCHAR(100) — NOT NULL
  • difficulty — VARCHAR(20) — NOT NULL — EASY,NORMAL,REALISTIC
  • personality — VARCHAR(30) — NOT NULL — TALKATIVE,CALM,INTROVERTED,ACTIVE
  • reaction_level — VARCHAR(20) — NOT NULL — FRIENDLY,NEUTRAL,LOW
  • system_prompt — TEXT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

coaching_events

  • coaching_event_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • target_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • event_type — VARCHAR(40) — NOT NULL — GAZE,SPEAKING_RATIO,INTERRUPTION,SILENCE,QUESTION,REACTION,SAFETY
  • severity — VARCHAR(20) — NOT NULL
  • event_time_sec — INT — NOT NULL — 세션 시작 기준 초
  • message — VARCHAR(500) — NOT NULL
  • trigger_data — JSON
  • displayed_at — DATETIME
  • created_at — DATETIME — NOT NULL

consent_types

  • consent_type_id — BIGINT — Primary key — NOT NULL
  • code — VARCHAR(50) — NOT NULL — TERMS,FACE_ANALYSIS,EXPRESSION_ANALYSIS,VOICE_ANALYSIS,GROWTH_REPORT
  • name — VARCHAR(100) — NOT NULL
  • version — VARCHAR(20) — NOT NULL
  • is_required — BOOLEAN — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

contact_exchange_requests

  • contact_exchange_request_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • requester_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • target_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • requester_agreed — BOOLEAN — NOT NULL
  • target_agreed — BOOLEAN
  • status — VARCHAR(20) — NOT NULL — PENDING,MUTUAL_AGREED,DECLINED,EXPIRED,CANCELLED
  • extension_agreed — BOOLEAN — NOT NULL
  • requested_at — DATETIME — NOT NULL
  • responded_at — DATETIME
  • disclosed_at — DATETIME

contact_profiles

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • instagram_id — VARCHAR(100)
  • kakao_id — VARCHAR(100)
  • updated_at — DATETIME — NOT NULL

face_tag_catalog

  • face_tag_id — BIGINT — Primary key — NOT NULL
  • code — VARCHAR(50) — NOT NULL — DOG,CAT,RABBIT,FOX,TOFU,DINOSAUR 등
  • name — VARCHAR(50) — NOT NULL
  • description — VARCHAR(500)
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

face_tag_examples

  • face_tag_example_id — BIGINT — Primary key — NOT NULL
  • face_tag_id — BIGINT — Foreign key → face_tag_catalog.face_tag_id — NOT NULL
  • celebrity_name — VARCHAR(100) — NOT NULL — 태그 설명용 고정 예시
  • display_order — SMALLINT — NOT NULL
  • is_active — BOOLEAN — NOT NULL

level_histories

  • level_history_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — 세션 결과로 변경된 경우
  • change_type — VARCHAR(30) — NOT NULL — SESSION_RESULT,CANCEL,NO_SHOW,SAFETY,ADMIN
  • experience_delta — INT — NOT NULL
  • level_before — INT — NOT NULL
  • level_after — INT — NOT NULL
  • reason — VARCHAR(500)
  • created_at — DATETIME — NOT NULL

match_pairs

  • match_pair_id — BIGINT — Primary key — NOT NULL
  • requester_a_id — BIGINT — Foreign key → match_requests.match_request_id — NOT NULL
  • requester_b_id — BIGINT — Foreign key → match_requests.match_request_id — NOT NULL
  • user_a_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • user_b_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • total_score — DECIMAL(6, 3)
  • goal_score — DECIMAL(6, 3)
  • conversation_score — DECIMAL(6, 3)
  • schedule_score — DECIMAL(6, 3)
  • preference_score — DECIMAL(6, 3)
  • occupation_score — DECIMAL(6, 3)
  • status — VARCHAR(20) — NOT NULL — PROPOSED,ACCEPTED,REJECTED,EXPIRED,CANCELLED
  • accept_deadline_at — DATETIME
  • matched_at — DATETIME — NOT NULL

match_requests

  • match_request_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • track_type — VARCHAR(20) — NOT NULL — HUMAN_VIDEO,AI_CHATBOT
  • status — VARCHAR(20) — NOT NULL — WAITING,MATCHED,CANCELLED,EXPIRED
  • preferred_start_at — DATETIME
  • preferred_end_at — DATETIME
  • coaching_intensity — VARCHAR(20) — NOT NULL — FLOW,BALANCED,ACTIVE
  • coaching_interval_min — SMALLINT — 3,5,10
  • requested_at — DATETIME — NOT NULL
  • cancelled_at — DATETIME

match_responses

  • match_response_id — BIGINT — Primary key — NOT NULL
  • match_pair_id — BIGINT — Foreign key → match_pairs.match_pair_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • response — VARCHAR(20) — NOT NULL — PENDING,ACCEPTED,REJECTED,TIMEOUT
  • responded_at — DATETIME

notifications

  • notification_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • notification_type — VARCHAR(50) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • content — VARCHAR(1000) — NOT NULL
  • related_type — VARCHAR(30) — MATCH,SESSION,REPORT,CONTACT,CHATBOT
  • related_id — BIGINT
  • is_read — BOOLEAN — NOT NULL
  • sent_at — DATETIME — NOT NULL
  • read_at — DATETIME

oauth_accounts

  • oauth_account_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • provider — VARCHAR(20) — NOT NULL — GOOGLE,NAVER,KAKAO
  • provider_user_id — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL

peer_evaluations

  • peer_evaluation_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • evaluator_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • evaluatee_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • comfort_score — TINYINT — NOT NULL
  • question_connection_score — TINYINT — NOT NULL
  • listening_score — TINYINT — NOT NULL
  • reaction_score — TINYINT — NOT NULL
  • balance_score — TINYINT — NOT NULL
  • manner_score — TINYINT — NOT NULL
  • good_behavior_text — VARCHAR(1000)
  • comfortable_moment_text — VARCHAR(1000)
  • improvement_text — VARCHAR(1000)
  • submitted_at — DATETIME — NOT NULL

practice_goal_catalog

  • practice_goal_id — BIGINT — Primary key — NOT NULL
  • code — VARCHAR(50) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(500)
  • is_active — BOOLEAN — NOT NULL

reports

  • report_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id
  • reporter_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • reported_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • report_type — VARCHAR(50) — NOT NULL — SEXUAL,INSULT,HATE,PRIVACY,MEETING_PRESSURE,RECORDING,SENSITIVE_VIDEO,OTHER
  • description — VARCHAR(2000)
  • status — VARCHAR(20) — NOT NULL — RECEIVED,REVIEWING,RESOLVED,REJECTED
  • reported_at — DATETIME — NOT NULL
  • resolved_at — DATETIME

room_themes

  • room_theme_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • place_type — VARCHAR(30) — NOT NULL — CAFE,LUNCH_RESTAURANT,DINNER_RESTAURANT,LOUNGE
  • background_url — VARCHAR(1000)
  • ambience_audio_url — VARCHAR(1000)
  • start_time — TIME
  • end_time — TIME
  • is_active — BOOLEAN — NOT NULL

safety_events

  • safety_event_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL — 문제가 감지된 발화자
  • category — VARCHAR(50) — NOT NULL — SEXUAL,INSULT,HATE,PRIVACY,APPEARANCE,MARRIAGE_PRESSURE 등
  • severity — VARCHAR(20) — NOT NULL — LOW,MEDIUM,HIGH,CRITICAL
  • source_type — VARCHAR(20) — NOT NULL — RULE,LLM
  • event_time_sec — INT — NOT NULL
  • context_summary — VARCHAR(1000)
  • evidence_excerpt — TEXT — 동의 시 이슈 주변 발화 일부만 저장
  • alternative_expression — VARCHAR(1000)
  • manner_penalty — DECIMAL(6, 2) — NOT NULL
  • created_at — DATETIME — NOT NULL

sanctions

  • sanction_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • report_id — BIGINT — Foreign key → reports.report_id
  • sanction_type — VARCHAR(30) — NOT NULL — WARNING,MATCH_RESTRICTED,SUSPENDED,PERMANENT_BAN
  • reason — VARCHAR(1000) — NOT NULL
  • starts_at — DATETIME — NOT NULL
  • ends_at — DATETIME
  • created_by — BIGINT — Foreign key → users.user_id — 관리자 user_id
  • created_at — DATETIME — NOT NULL

session_goals

  • session_goal_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • practice_goal_id — BIGINT — Foreign key → practice_goal_catalog.practice_goal_id
  • custom_goal — VARCHAR(255)

session_metric_summaries

  • session_metric_summary_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • speaking_ratio — DECIMAL(5, 2) — 본인 발화 비율(%)
  • question_count — INT — NOT NULL
  • followup_question_count — INT — NOT NULL
  • interruption_count — INT — NOT NULL
  • overlap_count — INT — NOT NULL
  • average_utterance_sec — DECIMAL(8, 2)
  • silence_total_sec — INT — NOT NULL
  • filler_word_count — INT — NOT NULL
  • speaking_speed_wpm — DECIMAL(8, 2)
  • gaze_ratio — DECIMAL(5, 2)
  • smile_ratio — DECIMAL(5, 2)
  • nod_count — INT — NOT NULL
  • face_absence_sec — INT — NOT NULL
  • negative_expression_count — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

session_participants

  • session_participant_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • participant_role — VARCHAR(10) — NOT NULL — A,B
  • participation_status — VARCHAR(20) — NOT NULL — EXPECTED,JOINED,LEFT,NO_SHOW,KICKED
  • joined_at — DATETIME
  • left_at — DATETIME
  • expression_analysis_enabled — BOOLEAN — NOT NULL
  • voice_analysis_enabled — BOOLEAN — NOT NULL
  • coaching_intensity — VARCHAR(20) — NOT NULL
  • coaching_interval_min — SMALLINT

session_reports

  • session_report_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • report_status — VARCHAR(20) — NOT NULL — PENDING,GENERATING,COMPLETED,FAILED
  • ai_flow_score — DECIMAL(5, 2)
  • ai_question_score — DECIMAL(5, 2)
  • ai_listening_score — DECIMAL(5, 2)
  • ai_reaction_score — DECIMAL(5, 2)
  • ai_manner_score — DECIMAL(5, 2)
  • ai_nonverbal_score — DECIMAL(5, 2)
  • peer_average_score — DECIMAL(5, 2)
  • strengths_json — JSON — 잘한 점 목록
  • improvements_json — JSON — 개선점 목록
  • next_missions_json — JSON — 다음 세션 실천 미션
  • topic_summary_json — JSON — 주제별 체류시간·빈도
  • summary_text — TEXT
  • model_version — VARCHAR(100)
  • generated_at — DATETIME
  • created_at — DATETIME — NOT NULL

sessions

  • session_id — BIGINT — Primary key — NOT NULL
  • match_pair_id — BIGINT — Foreign key → match_pairs.match_pair_id — 실사용자 세션인 경우
  • room_theme_id — BIGINT — Foreign key → room_themes.room_theme_id
  • session_type — VARCHAR(20) — NOT NULL — HUMAN_VIDEO
  • status — VARCHAR(30) — NOT NULL — SCHEDULED,WAITING,IN_PROGRESS,COMPLETED,CANCELLED,NO_SHOW,TERMINATED
  • scheduled_start_at — DATETIME — NOT NULL
  • actual_start_at — DATETIME
  • actual_end_at — DATETIME
  • planned_duration_sec — INT — NOT NULL
  • extension_duration_sec — INT — NOT NULL
  • termination_reason — VARCHAR(500)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

trait_catalog

  • trait_id — BIGINT — Primary key — NOT NULL
  • trait_type — VARCHAR(30) — NOT NULL — SELF_IMAGE,PARTNER_IMAGE
  • code — VARCHAR(50) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • is_active — BOOLEAN — NOT NULL

user_availability_slots

  • availability_slot_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • day_of_week — TINYINT — NOT NULL — 1=월요일 ... 7=일요일
  • start_time — TIME — NOT NULL
  • end_time — TIME — NOT NULL
  • timezone — VARCHAR(50) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_blocks

  • user_block_id — BIGINT — Primary key — NOT NULL
  • blocker_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • blocked_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • reason — VARCHAR(500)
  • created_at — DATETIME — NOT NULL

user_consents

  • user_consent_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • consent_type_id — BIGINT — Foreign key → consent_types.consent_type_id — NOT NULL
  • consented — BOOLEAN — NOT NULL
  • consented_at — DATETIME
  • withdrawn_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_face_tags

  • user_face_tag_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • face_tag_id — BIGINT — Foreign key → face_tag_catalog.face_tag_id — NOT NULL
  • confidence_score — DECIMAL(5, 4) — 분류 신뢰도, 외모 점수 아님
  • rank_order — SMALLINT — NOT NULL — 최대 2개 태그 순위
  • analyzed_at — DATETIME — NOT NULL

user_levels

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • level_no — INT — NOT NULL
  • experience_point — INT — NOT NULL
  • manner_score — DECIMAL(6,2) — NOT NULL
  • completed_session_count — INT — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_practice_goals

  • user_practice_goal_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • practice_goal_id — BIGINT — Foreign key → practice_goal_catalog.practice_goal_id — 직접 입력 목표는 NULL 가능
  • custom_goal — VARCHAR(255)
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

user_profiles

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • nickname — VARCHAR(30) — NOT NULL
  • gender — VARCHAR(20) — 서비스 정책에 따라 선택
  • preferred_gender — VARCHAR(20) — 매칭 선호 대상
  • height_cm — SMALLINT
  • region_city — VARCHAR(50)
  • region_district — VARCHAR(50)
  • occupation — VARCHAR(100) — 내부 매칭 전용
  • min_preferred_age — SMALLINT
  • max_preferred_age — SMALLINT
  • conversation_type — VARCHAR(20) — LEADING,BALANCED,RESPONSIVE
  • face_tags_visible — BOOLEAN — NOT NULL
  • onboarding_completed — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_traits

  • user_trait_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • trait_id — BIGINT — Foreign key → trait_catalog.trait_id — NOT NULL
  • created_at — DATETIME — NOT NULL

users

  • user_id — BIGINT — Primary key — NOT NULL — 사용자 PK
  • email — VARCHAR(255) — NOT NULL — 로그인 이메일
  • password_hash — VARCHAR(255) — 소셜 로그인 사용자는 NULL 가능
  • real_name — VARCHAR(50) — NOT NULL — 운영 전용 실명
  • phone_number — VARCHAR(30) — 운영 전용 전화번호
  • birth_date — DATE — NOT NULL — 성인 여부 확인용 생년월일
  • account_status — VARCHAR(20) — NOT NULL — ACTIVE,SUSPENDED,WITHDRAWN
  • role — VARCHAR(20) — NOT NULL — USER,ADMIN
  • adult_verified_at — DATETIME — 성인 확인 시각
  • last_login_at — DATETIME
  • withdrawn_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL