erd

Tables (11)

audio_cache

  • id — INTEGER — Primary key — NOT NULL
  • voice_profile_id — INTEGER — Foreign key → voice_profiles.id — NOT NULL
  • story_id — INTEGER — Foreign key → stories.id — NOT NULL
  • scene_id — INTEGER — Foreign key → story_scenes.id — NOT NULL
  • text_hash — VARCHAR(64) — NOT NULL
  • emotion_tag — VARCHAR(20)
  • voice_settings_hash — VARCHAR(64) — NOT NULL
  • model_id — VARCHAR(100) — NOT NULL
  • output_format — VARCHAR(50) — NOT NULL
  • audio_url — VARCHAR(500) — NOT NULL
  • duration_ms — INTEGER
  • provider — VARCHAR(50) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

child_profiles

  • id — INTEGER — Primary key — NOT NULL
  • user_id — INTEGER — Foreign key → users.id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • age — INTEGER — NOT NULL
  • preferred_style — VARCHAR(50)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

stories

  • id — INTEGER — Primary key — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • age_group — VARCHAR(10) — NOT NULL
  • cover_image_url — VARCHAR(500)
  • story_type — ENUM('linear','interactive') — NOT NULL
  • status — ENUM('draft','published') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

story_choices

  • id — INTEGER — Primary key — NOT NULL
  • scene_id — INTEGER — Foreign key → story_scenes.id — NOT NULL
  • choice_text — VARCHAR(300) — NOT NULL
  • next_scene_id — INTEGER — Foreign key → story_scenes.id
  • action_type — ENUM('go_to_scene','generate_scene','end_story') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

story_generation_jobs

  • id — INTEGER — Primary key — NOT NULL
  • session_id — INTEGER — Foreign key → user_story_sessions.id — NOT NULL
  • story_id — INTEGER — Foreign key → stories.id — NOT NULL
  • source_scene_id — INTEGER — Foreign key → story_scenes.id — NOT NULL
  • choice_id — INTEGER — Foreign key → story_choices.id — NOT NULL
  • generated_scene_id — INTEGER — Foreign key → story_scenes.id
  • status — ENUM('pending','processing','done','failed') — NOT NULL
  • provider — VARCHAR(50) — NOT NULL
  • model — VARCHAR(100) — NOT NULL
  • input_tokens — INTEGER
  • output_tokens — INTEGER
  • prompt_summary — TEXT
  • error_message — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

story_scenes

  • id — INTEGER — Primary key — NOT NULL
  • story_id — INTEGER — Foreign key → stories.id — NOT NULL
  • parent_scene_id — INTEGER — Foreign key → story_scenes.id
  • scene_key — VARCHAR(100) — NOT NULL
  • title — VARCHAR(200)
  • text — TEXT — NOT NULL
  • order_index — INTEGER — NOT NULL
  • next_scene_id — INTEGER — Foreign key → story_scenes.id
  • is_ending — BOOLEAN — NOT NULL
  • emotion_tag — VARCHAR(20)
  • generation_type — ENUM('static','llm_generated') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

tts_jobs

  • id — INTEGER — Primary key — NOT NULL
  • voice_profile_id — INTEGER — Foreign key → voice_profiles.id — NOT NULL
  • scene_id — INTEGER — Foreign key → story_scenes.id — NOT NULL
  • text — TEXT — NOT NULL
  • emotion_tag — VARCHAR(20)
  • model_id — VARCHAR(100) — NOT NULL
  • voice_settings — JSON — NOT NULL
  • output_format — VARCHAR(50) — NOT NULL
  • status — ENUM('pending','processing','done','failed') — NOT NULL
  • provider_job_id — VARCHAR(200)
  • audio_url — VARCHAR(500)
  • error_message — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

user_choice_logs

  • id — INTEGER — Primary key — NOT NULL
  • session_id — INTEGER — Foreign key → user_story_sessions.id — NOT NULL
  • scene_id — INTEGER — Foreign key → story_scenes.id — NOT NULL
  • choice_id — INTEGER — Foreign key → story_choices.id — NOT NULL
  • selected_at — TIMESTAMP — NOT NULL

user_story_sessions

  • id — INTEGER — Primary key — NOT NULL
  • user_id — INTEGER — Foreign key → users.id — NOT NULL
  • child_profile_id — INTEGER — Foreign key → child_profiles.id — NOT NULL
  • story_id — INTEGER — Foreign key → stories.id — NOT NULL
  • current_scene_id — INTEGER — Foreign key → story_scenes.id
  • voice_profile_id — INTEGER — Foreign key → voice_profiles.id — NOT NULL
  • status — ENUM('active','completed','abandoned') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

users

  • id — INTEGER — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • role — ENUM('user','admin') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

voice_profiles

  • id — INTEGER — Primary key — NOT NULL
  • user_id — INTEGER — Foreign key → users.id — NOT NULL
  • profile_name — VARCHAR(100) — NOT NULL
  • provider — VARCHAR(50) — NOT NULL
  • provider_voice_id — VARCHAR(200)
  • sample_audio_url — VARCHAR(500)
  • provider_metadata — JSON
  • status — ENUM('processing','ready','failed') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL