caring-voice

Tables (12)

fcm_token

  • token_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • fcm_token — VARCHAR(255) — NOT NULL
  • device_id — VARCHAR(255)
  • platform — VARCHAR(20)
  • is_active — TINYINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • INDEX — `idx_fcm_token`
  • INDEX — `idx_user_active`
  • INDEX — `idx_device_token`

frequency_result

  • frequency_result_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • latest_voice_composite_id — BIGINT
  • message — TEXT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • INDEX — `idx_freq_user`
  • INDEX — `idx_freq_latest_vc`

notification

  • notification_id — BIGINT
  • voice_id — BIGINT — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • top_emotion — VARCHAR(16)
  • created_at — TIMESTAMP — NOT NULL
  • INDEX — `idx_notification_voice`
  • INDEX — `idx_notification_created`

question

  • question_id — BIGINT — Primary key — NOT NULL
  • question_category — VARCHAR(50) — NOT NULL — 질문 카테고리 (emotion, stress, physical, social, self_reflection)
  • content — TEXT — NOT NULL — 질문 내용
  • created_at — TIMESTAMP — NOT NULL
  • INDEX — `idx_question_category`

user

  • PK (user_id) — BIGINT — Primary key — NOT NULL
  • 로그인 아이디 (username) — VARCHAR(64) — NOT NULL
  • 비밀번호 (password) — VARBINARY(72) — NOT NULL
  • 역할 (role) — VARCHAR(20) — NOT NULL
  • 이름 (name) — VARCHAR(50) — NOT NULL
  • 생년월일 (birthdate) — DATE — NOT NULL
  • 계정생성일 (created_at) — TIMESTAMP — NOT NULL
  • 계정수정일 (updated_at) — TIMESTAMP — NOT NULL
  • CHECK — (role IN ('USER','GUARDIAN','ADMIN'))

voice

  • PK (voice_id) — BIGINT — Primary key — NOT NULL
  • 음성파일 키값 (voice_key) — VARCHAR(1024) — NOT NULL
  • 음성파일제목 (voice_title) — VARCHAR(255) — NOT NULL
  • 음성파일 길이 (duration_ms) — INT — NOT NULL
  • Hz (sample_rate) — INT
  • bps (bit_rate) — INT
  • 생성 시각 (created_at) — TIMESTAMP — NOT NULL
  • FK (user_id) — BIGINT — Foreign key → user.PK — NOT NULL

voice_analyze

  • PK (voice_analyze_id) — BIGINT — Primary key — NOT NULL
  • happy_bps — SMALLINT — NOT NULL
  • sad_bps — SMALLINT — NOT NULL
  • neutral_bps — SMALLINT — NOT NULL
  • angry_bps — SMALLINT — NOT NULL
  • fear_bps — SMALLINT — NOT NULL
  • top_emotion — VARCHAR(16)
  • top_confidence_bps — SMALLINT
  • model_version — VARCHAR(32)
  • analyzed_at — TIMESTAMP — NOT NULL
  • FK (voice_id) — BIGINT — Foreign key → voice.PK — NOT NULL

voice_composite

  • voice_composite_id — BIGINT — Primary key
  • text_score_bps — SMALLINT
  • text_magnitude_x1000 — INT
  • alpha_bps — SMALLINT
  • beta_bps — SMALLINT
  • valence_x1000 — INT — NOT NULL
  • arousal_x1000 — INT — NOT NULL
  • intensity_x1000 — INT — NOT NULL
  • happy_bps — SMALLINT — NOT NULL
  • sad_bps — SMALLINT — NOT NULL
  • neutral_bps — SMALLINT — NOT NULL
  • angry_bps — SMALLINT — NOT NULL
  • fear_bps — SMALLINT — NOT NULL
  • surprise_bps — SMALLINT — NOT NULL
  • top_emotion — VARCHAR(16)
  • top_emotion_confidence_bps — SMALLINT
  • created_at — TIMESTAMP — NOT NULL
  • FK (voice_id) — BIGINT — Foreign key → voice.PK — NOT NULL

voice_content

  • PK (voice_content_id) — BIGINT — Primary key — NOT NULL
  • 텍스트 (content) — MEDIUMTEXT — NOT NULL
  • 스코어 (score_bps) — SMALLINT
  • 강도 (magnitude_x1000) — INT
  • locale — VARCHAR(10)
  • 제공자 (provider) — VARCHAR(32)
  • model_version — VARCHAR(32)
  • confidence_bps — SMALLINT
  • 생성시각 (created_at) — TIMESTAMP — NOT NULL
  • FK (voice_id) — BIGINT — Foreign key → voice.PK — NOT NULL

voice_job_process

  • PK (voice_id) — BIGINT — Foreign key → voice.PK — NOT NULL
  • text_done — TINYINT — NOT NULL
  • audio_done — TINYINT — NOT NULL
  • locked — TINYINT — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

voice_question

  • voice_question_id — BIGINT — Primary key — NOT NULL
  • 생성날짜 (created_at) — TIMESTAMP — NOT NULL
  • FK (question_id) — BIGINT — Foreign key → question.question_id — NOT NULL
  • FK (voice_id) — BIGINT — Foreign key → voice.PK — NOT NULL

weekly_result

  • weekly_result_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • latest_voice_composite_id — BIGINT
  • message — TEXT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • INDEX — `idx_weekly_user`
  • INDEX — `idx_weekly_latest_vc`