AI Call Tutor

Tables (20)

admins

  • id — UUID
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • last_login_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

api_usage_logs

  • id — UUID
  • user_id — UUID — Foreign key → users.id
  • service — VARCHAR(20) — NOT NULL
  • tokens_used — INT
  • duration_ms — INT
  • cost_usd — NUMERIC(10, 6)
  • created_at — TIMESTAMPTZ — NOT NULL

call_reports

  • id — UUID
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • call_session_id — UUID — Foreign key → call_sessions.id — NOT NULL
  • strength — TEXT
  • improvement — TEXT
  • assessed_cefr_level — VARCHAR(10)
  • vocab_level — VARCHAR(10)
  • vocab_explanation — TEXT
  • suggested_expressions — TEXT
  • generated_at — TIMESTAMPTZ — NOT NULL

call_schedules

  • id — UUID
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • days_of_week — VARCHAR(20) — NOT NULL
  • scheduled_time — VARCHAR(10) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • alarm_id — VARCHAR(100)
  • created_at — TIMESTAMPTZ — NOT NULL

call_sessions

  • id — UUID
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • conversation_id — UUID — Foreign key → conversations.id — NOT NULL
  • situation_template_id — UUID — Foreign key → situation_templates.id
  • call_type — VARCHAR(20) — NOT NULL
  • started_at — TIMESTAMPTZ — NOT NULL
  • ended_at — TIMESTAMPTZ
  • duration_seconds — INT
  • turn_count — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL

conversations

  • id — UUID
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • type — VARCHAR(20) — NOT NULL
  • persona_id — UUID — Foreign key → personas.id
  • situation_category_id — UUID — Foreign key → situation_categories.id
  • situation_summary — TEXT
  • title — VARCHAR(255)
  • last_message_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

device_tokens

  • id — UUID
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • fcm_token — TEXT — NOT NULL
  • platform — VARCHAR(20) — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

events

  • id — UUID
  • title — VARCHAR(255) — NOT NULL
  • body — TEXT — NOT NULL
  • target — VARCHAR(20) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • starts_at — TIMESTAMPTZ
  • ends_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

messages

  • id — UUID
  • conversation_id — UUID — Foreign key → conversations.id — NOT NULL
  • call_session_id — UUID — Foreign key → call_sessions.id
  • role — VARCHAR(20) — NOT NULL
  • content_en — TEXT — NOT NULL
  • content_ko — TEXT
  • has_correction — BOOLEAN — NOT NULL
  • correction — TEXT
  • correction_explanation — TEXT
  • source_type — VARCHAR(10) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

notices

  • id — UUID
  • title — VARCHAR(255) — NOT NULL
  • body — TEXT — NOT NULL
  • target — VARCHAR(20) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • popup_start_at — TIMESTAMPTZ
  • popup_end_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

payments

  • id — UUID
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • subscription_id — UUID — Foreign key → subscriptions.id — NOT NULL
  • payment_key — VARCHAR(255) — NOT NULL
  • amount_krw — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • fail_reason — TEXT
  • paid_at — TIMESTAMPTZ

personas

  • id — UUID
  • type — VARCHAR(20) — NOT NULL
  • gender — VARCHAR(10) — NOT NULL
  • mbti_type — VARCHAR(10)
  • personality_summary — TEXT
  • name — VARCHAR(100) — NOT NULL
  • role — VARCHAR(100)
  • voice_id — VARCHAR(100)
  • avatar — VARCHAR(255)
  • is_active — BOOLEAN — NOT NULL
  • sort_order — INT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

reports

  • id — UUID
  • reporter_id — UUID — Foreign key → users.id — NOT NULL
  • conversation_id — UUID — Foreign key → conversations.id
  • call_session_id — UUID — Foreign key → call_sessions.id
  • message_id — UUID — Foreign key → messages.id
  • reason — VARCHAR(50) — NOT NULL
  • description — TEXT
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

situation_categories

  • id — UUID
  • title_ko — VARCHAR(100) — NOT NULL
  • title_en — VARCHAR(100) — NOT NULL
  • icon — VARCHAR(255)
  • persona_id — UUID — Foreign key → personas.id
  • is_active — BOOLEAN — NOT NULL
  • sort_order — INT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

situation_templates

  • id — UUID
  • category_id — UUID — Foreign key → situation_categories.id — NOT NULL
  • title_ko — VARCHAR(100) — NOT NULL
  • title_en — VARCHAR(100) — NOT NULL
  • description — TEXT
  • prompt_hint — TEXT
  • is_active — BOOLEAN — NOT NULL
  • sort_order — INT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

subscriptions

  • id — UUID
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • plan — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • free_turns_used — INT — NOT NULL
  • free_situation_used — INT — NOT NULL
  • starts_at — TIMESTAMPTZ — NOT NULL
  • ends_at — TIMESTAMPTZ
  • cancelled_at — TIMESTAMPTZ

system_config

  • value — TEXT — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

user_personas

  • id — UUID
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • persona_id — UUID — Foreign key → personas.id — NOT NULL
  • added_at — TIMESTAMPTZ — NOT NULL

users

  • id — UUID
  • email — VARCHAR(255) — NOT NULL
  • provider — VARCHAR(50) — NOT NULL
  • provider_uid — VARCHAR(255)
  • nickname — VARCHAR(50) — NOT NULL
  • default_persona_id — UUID — Foreign key → personas.id
  • current_cefr_level — VARCHAR(10) — NOT NULL
  • level_goal — VARCHAR(10)
  • streak_days — INT — NOT NULL
  • total_call_count — INT — NOT NULL
  • total_study_seconds — INT — NOT NULL
  • total_sentence_count — INT — NOT NULL
  • daily_notification_time — VARCHAR(10)
  • last_studied_at — TIMESTAMPTZ
  • last_situation_summary — TEXT
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ

vocabulary

  • id — UUID
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • message_id — UUID — Foreign key → messages.id
  • original — TEXT — NOT NULL
  • correction — TEXT — NOT NULL
  • explanation — TEXT
  • source_type — VARCHAR(10) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL