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