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