ERD

Tables (9)

analyses

  • id — BIGSERIAL — Primary key — NOT NULL
  • recording_id — BIGINT — Foreign key → recordings.id — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • attempt_no — INTEGER — NOT NULL
  • auto_retry_count — INTEGER — NOT NULL
  • failure_code — VARCHAR(50)
  • worker_id — UUID
  • lease_expires_at — TIMESTAMPTZ
  • execution_deadline_at — TIMESTAMPTZ — NOT NULL
  • started_at — TIMESTAMPTZ
  • finished_at — TIMESTAMPTZ
  • speech_duration_ms — INTEGER
  • silence_duration_ms — INTEGER
  • filler_total_count — INTEGER
  • long_silence_count — INTEGER
  • repeated_expression_count — INTEGER
  • algorithm_version — VARCHAR(32) — NOT NULL
  • engine_type — VARCHAR(10) — NOT NULL
  • engine_version — VARCHAR(100) — NOT NULL
  • 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
  • total_word_count — INTEGER — NOT NULL
  • speech_intervals — JSONB — NOT NULL
  • waveform — JSONB — NOT NULL
  • coaching_summary — TEXT — NOT NULL
  • coaching_practice_recommendation — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

api_idempotency_keys

  • id — BIGSERIAL — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • idempotency_key — UUID — NOT NULL
  • operation — VARCHAR(20) — NOT NULL
  • request_fingerprint — VARCHAR(64) — NOT NULL
  • recording_id — BIGINT — Foreign key → recordings.id
  • analysis_id — BIGINT — Foreign key → analyses.id
  • response_status — INTEGER — NOT NULL
  • response_body — JSONB — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • expires_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

filler_timeline_events

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

recordings

  • id — BIGSERIAL — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • duration_ms — INTEGER — NOT NULL
  • mime_type — VARCHAR(50) — NOT NULL
  • file_size_bytes — BIGINT — NOT NULL
  • audio_sha256 — VARCHAR(64) — NOT NULL
  • submitted_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(254) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • profile_image_url — VARCHAR(500)
  • plan — VARCHAR(10) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL