관계온도 ERD

테이블 (14)

analysis_jobs

  • analysis_job_id — BINARY(16) — 기본 키 — NOT NULL
  • relationship_id — BINARY(16) — 외래 키 — NOT NULL
  • conversation_file_id — BINARY(16) — 외래 키 → conversation_files.conversation_file_id — NOT NULL
  • status — ENUM('QUEUED','RUNNING','SUCCEEDED','FAILED','CANCELED') — NOT NULL
  • stage — ENUM('LOADING_CONVERSATION','ANALYZING_MESSAGE_PATTERNS','ANALYZING_EMOTIONAL_FLOW','CALCULATING_PRQC','CALCULATING_RELATIONSHIP_SCORE') — NOT NULL
  • progress — TINYINT — NOT NULL
  • estimated_seconds_remaining — INT
  • failure_code — VARCHAR(50)
  • failure_message — VARCHAR(500)
  • failure_retryable — BOOLEAN
  • started_at — DATETIME(3)
  • completed_at — DATETIME(3)
  • created_at — DATETIME(3) — NOT NULL
  • updated_at — DATETIME(3) — NOT NULL

analysis_reports

  • analysis_report_id — BINARY(16) — 기본 키 — NOT NULL
  • analysis_job_id — BINARY(16) — 외래 키 → analysis_jobs.analysis_job_id — NOT NULL
  • relationship_id — BINARY(16) — 외래 키 — NOT NULL
  • overall_score — TINYINT — NOT NULL
  • status_code — ENUM('HEALTHY','GOOD','NEEDS_ATTENTION','CHANGE_DETECTED') — NOT NULL
  • status_label — VARCHAR(50) — NOT NULL
  • model_version — VARCHAR(100) — NOT NULL
  • score_version — VARCHAR(100) — NOT NULL
  • disclaimer — VARCHAR(1000) — NOT NULL
  • analyzed_at — DATETIME(3) — NOT NULL
  • created_at — DATETIME(3) — NOT NULL

auth_sessions

  • session_id — BINARY(16) — 기본 키 — NOT NULL
  • user_id — BINARY(16) — 외래 키 — NOT NULL
  • session_token_hash — CHAR(64) — NOT NULL
  • csrf_token_hash — CHAR(64) — NOT NULL
  • expires_at — DATETIME(3) — NOT NULL
  • revoked_at — DATETIME(3)
  • last_accessed_at — DATETIME(3)
  • created_at — DATETIME(3) — NOT NULL

check_in_answers

  • id — BINARY(16) — 기본 키 — NOT NULL
  • check_in_id — BINARY(16) — 외래 키 → check_ins.id — NOT NULL
  • question_code — VARCHAR(100) — NOT NULL
  • score — TINYINT — NOT NULL
  • created_at — DATETIME(3) — NOT NULL

check_ins

  • id — BINARY(16) — 기본 키 — NOT NULL
  • user_id — BINARY(16) — 외래 키 — NOT NULL
  • relationship_id — BINARY(16) — 외래 키 → relationships.id — NOT NULL
  • week_start — DATE — NOT NULL
  • relationship_feeling — TINYINT
  • conversation_comfort — TINYINT
  • created_at — DATETIME(3) — NOT NULL
  • updated_at — DATETIME(3) — NOT NULL

consultations

  • consultation_id — BINARY(16) — 기본 키 — NOT NULL
  • user_id — BINARY(16) — 외래 키 — NOT NULL
  • relationship_id — BINARY(16) — 외래 키 — NOT NULL
  • analysis_report_id — BINARY(16) — 외래 키 → analysis_reports.analysis_report_id — NOT NULL
  • status — ENUM('ACTIVE','CLOSED','DELETING') — NOT NULL
  • title — VARCHAR(100)
  • last_message_preview — VARCHAR(300)
  • last_message_at — DATETIME(3)
  • unread_count — INT — NOT NULL
  • created_at — DATETIME(3) — NOT NULL
  • updated_at — DATETIME(3) — NOT NULL
  • deleted_at — DATETIME(3)

conversation_files

  • conversation_file_id — BINARY(16) — 기본 키 — NOT NULL
  • relationship_id — BINARY(16) — 외래 키 — NOT NULL
  • source — ENUM('KAKAO_TALK') — NOT NULL
  • original_file_name — VARCHAR(255) — NOT NULL
  • size_bytes — BIGINT — NOT NULL
  • content_hash — CHAR(64) — NOT NULL
  • object_storage_key — VARCHAR(1000) — NOT NULL
  • validation_status — ENUM('UPLOADED','VALIDATING','VALID','INVALID','EXPIRED','DELETED') — NOT NULL
  • message_count — INT — NOT NULL
  • conversation_started_at — DATETIME(3)
  • conversation_ended_at — DATETIME(3)
  • failure_code — VARCHAR(50)
  • failure_message — VARCHAR(500)
  • expires_at — DATETIME(3)
  • uploaded_at — DATETIME(3) — NOT NULL
  • updated_at — DATETIME(3) — NOT NULL
  • deleted_at — DATETIME(3)

deletion_jobs

  • deletion_job_id — BINARY(16) — 기본 키 — NOT NULL
  • user_id — BINARY(16) — 외래 키 — NOT NULL
  • relationship_id — BINARY(16) — 외래 키 — NOT NULL
  • status — ENUM('QUEUED','RUNNING','SUCCEEDED','FAILED') — NOT NULL
  • current_stage — ENUM('CANCEL_ANALYSES','DELETE_MONGODB','DELETE_OBJECTS','DELETE_MYSQL_DERIVED_DATA','FINALIZE_RELATIONSHIP') — NOT NULL
  • progress — TINYINT — NOT NULL
  • failure_code — VARCHAR(50)
  • failure_message — VARCHAR(500)
  • retry_count — INT — NOT NULL
  • started_at — DATETIME(3)
  • completed_at — DATETIME(3)
  • created_at — DATETIME(3) — NOT NULL
  • updated_at — DATETIME(3) — NOT NULL

idempotency_keys

  • idempotency_record_id — BINARY(16) — 기본 키 — NOT NULL
  • user_id — BINARY(16) — 외래 키 — NOT NULL
  • request_path — VARCHAR(500) — NOT NULL
  • idempotency_key — VARCHAR(255) — NOT NULL
  • request_hash — CHAR(64) — NOT NULL
  • response_status — SMALLINT — NOT NULL
  • response_body — JSON — NOT NULL
  • expires_at — DATETIME(3) — NOT NULL
  • created_at — DATETIME(3) — NOT NULL

relationships

  • id — BINARY(16) — 기본 키 — NOT NULL
  • user_id — BINARY(16) — 외래 키 — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • relationship_type — ENUM('ROMANTIC_PARTNER','FRIEND','FAMILY','COWORKER','OTHER') — NOT NULL
  • status — ENUM('DRAFT','ANALYZING','ACTIVE','ANALYSIS_FAILED','DELETING') — NOT NULL
  • latest_score — TINYINT
  • latest_change — SMALLINT
  • last_analyzed_at — DATETIME(3)
  • created_at — DATETIME(3) — NOT NULL
  • updated_at — DATETIME(3) — NOT NULL

report_evidences

  • report_evidence_id — BINARY(16) — 기본 키 — NOT NULL
  • analysis_report_id — BINARY(16) — 외래 키 → analysis_reports.analysis_report_id — NOT NULL
  • component — ENUM('satisfaction','commitment','intimacy','trust','passion','love') — NOT NULL
  • score — TINYINT — NOT NULL
  • summary — VARCHAR(2000) — NOT NULL
  • metric_name — VARCHAR(100)
  • current_value — DECIMAL(12,4)
  • previous_value — DECIMAL(12,4)
  • unit — VARCHAR(50)
  • period — VARCHAR(100)
  • source_message_refs — JSON
  • created_at — DATETIME(3) — NOT NULL

report_prqc_scores

  • report_prqc_score_id — BINARY(16) — 기본 키 — NOT NULL
  • analysis_report_id — BINARY(16) — 외래 키 → analysis_reports.analysis_report_id — NOT NULL
  • component — ENUM('satisfaction','commitment','intimacy','trust','passion','love') — NOT NULL
  • score — TINYINT — NOT NULL
  • created_at — DATETIME(3) — NOT NULL

support_resources

  • support_resource_id — BINARY(16) — 기본 키 — NOT NULL
  • region — VARCHAR(10) — NOT NULL
  • category — VARCHAR(50) — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • description — VARCHAR(1000) — NOT NULL
  • official_url — VARCHAR(1000)
  • phone_number — VARCHAR(50)
  • operating_hours — VARCHAR(500)
  • source — VARCHAR(500) — NOT NULL
  • verified_at — DATETIME(3) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME(3) — NOT NULL
  • updated_at — DATETIME(3) — NOT NULL

users

  • user_id — BIGINT — 기본 키 — NOT NULL
  • kakao_user_id — VARCHAR(100) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • profile_image_url — VARCHAR(1000)
  • provider — ENUM('KAKAO') — NOT NULL
  • status — ENUM('ACTIVE','WITHDRAWN') — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME