mini-project4

Tables (8)

analyses

  • id — BIGSERIAL — Primary key — NOT NULL
  • recording_id — BIGINT — Foreign key → recordings.id — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL — recordings.user_id 의 불변 사본. /analyses/{id} 계열 엔드포인트의 소유권 검증에서 recordings 조인을 없앤다
  • status — VARCHAR(20) — NOT NULL
  • failure_code — VARCHAR(50)
  • retry_count — INTEGER — NOT NULL — 자동 재시도 횟수. 3 초과 시 MAX_RETRY_EXCEEDED
  • retryable — BOOLEAN — NOT NULL
  • started_at — TIMESTAMPTZ
  • completed_at — TIMESTAMPTZ
  • speech_duration_sec — INTEGER
  • silence_duration_sec — INTEGER
  • filler_total_count — INTEGER — status=COMPLETED 이전에는 NULL
  • long_silence_count — INTEGER
  • repeated_expression_count — INTEGER
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_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 발화 속도. PRO_008 비교에서도 사용
  • total_speech_duration_sec — INTEGER
  • total_word_count — INTEGER
  • waveform — JSONB — v1의 waveform_points 테이블. {"timeSec":0.0,"amplitude":0.02,"type":"SILENCE"}, ... ' '한 번에 전량 조회되고 시점 검색 조건이 없어 행으로 쪼갤 이유가…
  • 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
  • occurrence_count — INTEGER — NOT NULL — v1의 count 컬럼. COUNT 함수명과 겹쳐 JPQL/네이티브 쿼리에서 이스케이프가 필요해 개명

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
  • mime_type — VARCHAR(50) — NOT NULL
  • file_size_bytes — BIGINT
  • recorded_at — TIMESTAMPTZ — NOT NULL — 불변. analyses 와 조인해 DASH_009 추이를 산출한다
  • 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