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)