mindbot-backend

Tables (6)

emotion_daily_summary

  • summary_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • summary_date — DATE — NOT NULL
  • dominant_emotion — VARCHAR(30) — NOT NULL — 그날 가장 많이 감지된 감정
  • emotion_distribution — JSON — NOT NULL — {"happy":5,"sad":3,"neutral":12}
  • total_detections — INT — NOT NULL
  • total_interventions — INT — NOT NULL
  • avg_confidence — DECIMAL(5, 4)
  • created_at — DATETIME — NOT NULL

emotion_logs

  • emotion_log_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • primary_emotion — VARCHAR(30) — NOT NULL — happy, sad, angry, neutral, fear, surprise, disgust
  • confidence_score — DECIMAL(5, 4) — NOT NULL — 0.0000 ~ 1.0000
  • face_emotion — VARCHAR(30) — DeepFace 표정 감정
  • face_confidence — DECIMAL(5, 4)
  • speech_text — TEXT — Whisper STT 결과
  • speech_sentiment — VARCHAR(30) — 텍스트 기반 감성 분석
  • sigh_detected — BOOLEAN — NOT NULL — YAMNet 한숨/호흡 감지
  • motion_pattern — VARCHAR(30) — restless, still, fidgeting 등
  • intervention_triggered — BOOLEAN — NOT NULL
  • detected_at — DATETIME — NOT NULL
  • INDEX — idx_emotion_user_date
  • INDEX — idx_emotion_primary

interventions

  • intervention_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • emotion_log_id — BIGINT — Foreign key → emotion_logs.emotion_log_id — NOT NULL — 개입을 트리거한 감정 기록
  • intervention_type — VARCHAR(20) — NOT NULL — proactive, reactive
  • agent_message — TEXT — NOT NULL — ReAct Agent 생성 멘트
  • tts_played — BOOLEAN — NOT NULL
  • cooldown_expires_at — DATETIME — NOT NULL — 다음 개입 가능 시점
  • intervention_rating — ENUM('HELPFUL', 'NEUTRAL', 'UNNECESSARY', 'ANNOYING') — 개입 자체에 대한 평가
  • emotion_accuracy — ENUM('CORRECT', 'PARTIALLY_CORRECT', 'WRONG') — 감정 인식 정확도 평가
  • intervention_feedback_text — VARCHAR(500) — 선택적 한줄 코멘트
  • feedback_at — DATETIME — 피드백 제출 시점
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_intervention_user
  • INDEX — idx_intervention_cooldown
  • INDEX — idx_intervention_rating

recommendations

  • recommendation_id — BIGINT — Primary key — NOT NULL
  • intervention_id — BIGINT — Foreign key → interventions.intervention_id — NOT NULL
  • category — ENUM('MUSIC', 'MOVIE', 'BOOK', 'PLACE') — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • artist_or_author — VARCHAR(200)
  • external_id — VARCHAR(100) — Spotify track ID, TMDB movie ID 등
  • external_url — VARCHAR(500)
  • thumbnail_url — VARCHAR(500)
  • reason — TEXT — LLM 생성 추천 이유
  • user_feedback — ENUM('LIKED', 'DISLIKED', 'SKIPPED') — 콘텐츠 자체에 대한 평가
  • played — BOOLEAN — NOT NULL — 실제 재생/열람 여부
  • relevance_rating — ENUM('PERFECT', 'RELEVANT', 'IRRELEVANT') — 감정 상태에 맞는 추천이었는가
  • feedback_text — VARCHAR(500) — 선택적 사유
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_rec_intervention
  • INDEX — idx_rec_category
  • INDEX — idx_rec_feedback
  • INDEX — idx_rec_relevance

user_preferences

  • preference_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • preferred_music_genres — JSON — "jazz","lo-fi","classical"
  • preferred_movie_genres — JSON — "drama","comedy"
  • preferred_book_genres — JSON — "self-help","fiction"
  • preferred_place_types — JSON — "cafe","park","library"
  • tts_enabled — BOOLEAN — NOT NULL
  • cooldown_minutes — INT — NOT NULL
  • updated_at — DATETIME — NOT NULL

users

  • user_id — BIGINT — Primary key — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • login_id — VARCHAR(50)
  • password — VARCHAR(255)
  • email — VARCHAR(100)
  • address — VARCHAR(255)
  • address_detail — VARCHAR(100)
  • zipcode — VARCHAR(10)