mini-project3

Tables (10)

analyses

  • id — BIGSERIAL — Primary key — NOT NULL
  • recording_id — BIGINT — Foreign key → recordings.id — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • failure_code — VARCHAR(50) — STT_TIMEOUT 등 실패 사유
  • retry_count — INTEGER — NOT NULL — 자동 재시도 횟수. 3 초과 시 MAX_RETRY_EXCEEDED
  • retryable — BOOLEAN — NOT NULL — 사용자 재시도 가능 여부 (음성 재첨부 필요)
  • started_at — TIMESTAMPTZ
  • completed_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

analysis_basic_results

  • id — BIGSERIAL — Primary key — NOT NULL
  • analysis_id — BIGINT — Foreign key → analyses.id — NOT NULL
  • speech_duration_sec — INTEGER — NOT NULL
  • silence_duration_sec — INTEGER — NOT NULL
  • filler_total_count — INTEGER — NOT NULL
  • long_silence_count — INTEGER — NOT NULL — STT_012 장기 침묵 구간 수
  • repeated_expression_count — INTEGER — NOT NULL — STT_014 반복 표현 수
  • created_at — TIMESTAMPTZ — NOT NULL

analysis_pro_results

  • id — BIGSERIAL — Primary key — NOT NULL
  • analysis_id — BIGINT — Foreign key → analyses.id — NOT NULL
  • words_per_minute — INTEGER — PRO_005 발화 속도
  • total_speech_duration_sec — INTEGER
  • total_word_count — INTEGER
  • coaching_summary — TEXT
  • coaching_practice_recommendation — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL

coaching_action_items

  • id — BIGSERIAL — Primary key — NOT NULL
  • pro_result_id — BIGINT — Foreign key → analysis_pro_results.id — NOT NULL
  • sort_order — INTEGER — NOT NULL
  • content — TEXT — NOT NULL

filler_breakdowns

  • id — BIGSERIAL — Primary key — NOT NULL
  • analysis_id — BIGINT — Foreign key → analyses.id — NOT NULL
  • expression — VARCHAR(50) — NOT NULL
  • count — INTEGER — NOT NULL

filler_timeline_events

  • id — BIGSERIAL — Primary key — NOT NULL
  • analysis_id — BIGINT — Foreign key → analyses.id — NOT NULL
  • time_sec — NUMERIC(7, 3) — NOT NULL
  • expression — VARCHAR(50) — NOT NULL

recordings

  • id — BIGSERIAL — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • duration_sec — INTEGER — NOT NULL — 녹음 길이(초). 최대 60초
  • mime_type — VARCHAR(50) — NOT NULL
  • file_size_bytes — BIGINT — 업로드 시점 용량. 폐기 후에도 통계용으로 유지
  • recorded_at — TIMESTAMPTZ — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

segment_analyses

  • id — BIGSERIAL — Primary key — NOT NULL
  • analysis_id — BIGINT — Foreign key → analyses.id — NOT NULL
  • segment — VARCHAR(10) — NOT NULL
  • filler_count — INTEGER — NOT NULL
  • habit_word_count — INTEGER — NOT NULL

users

  • id — BIGSERIAL — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL — BCrypt 해시
  • name — VARCHAR(50) — NOT NULL
  • profile_image_url — VARCHAR(500)
  • plan — VARCHAR(10) — NOT NULL — FREE | PRO — PRO_001 서버 권한 검증 기준
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

waveform_points

  • id — BIGSERIAL — Primary key — NOT NULL
  • analysis_id — BIGINT — Foreign key → analyses.id — NOT NULL
  • time_sec — NUMERIC(7, 3) — NOT NULL
  • amplitude — NUMERIC(5, 4) — NOT NULL
  • type — VARCHAR(10) — NOT NULL