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