soon
Tables (10)
api_settings
- id — CHAR(36)
- setting_key — VARCHAR(100) — NOT NULL
- setting_value — TEXT
- description — TEXT
- is_active — TINYINT(1) — NOT NULL
- updated_by — CHAR(36) — Foreign key → users.id
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
audio_files
- id — CHAR(36)
- project_id — CHAR(36) — Foreign key → projects.id — NOT NULL
- chapter_number — INT — NOT NULL
- sub_section_number — INT — NOT NULL
- question_text — TEXT
- file_url — VARCHAR(500)
- file_path — VARCHAR(500)
- file_size — BIGINT
- duration_seconds — DECIMAL(10,2)
- stt_status — ENUM('pending','processing','completed','failed') — NOT NULL
- stt_result — LONGTEXT
- stt_confidence — DECIMAL(5,4)
- follow_up_question — TEXT
- revisit_needed — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
family_cheers
- id — CHAR(36)
- project_id — CHAR(36) — Foreign key → projects.id — NOT NULL
- family_member_id — CHAR(36) — Foreign key → family_members.id — NOT NULL
- message — TEXT — NOT NULL
- is_read — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
family_members
- id — CHAR(36)
- project_id — CHAR(36) — Foreign key → projects.id — NOT NULL
- user_id — CHAR(36) — Foreign key → users.id
- relationship — ENUM( 'spouse', 'son', 'daughter', 'son_in_law', 'daughter_in_law', 'grandson', 'granddaughter', 'other' ) — NOT NULL
- full_name — VARCHAR(100) — NOT NULL
- phone — VARCHAR(255)
- email — VARCHAR(255)
- display_order — INT — NOT NULL
- password_hash — VARCHAR(255)
- note — TEXT
- invitation_status — ENUM('pending','sent','accepted','declined') — NOT NULL
- invited_at — DATETIME
- accepted_at — DATETIME
- is_active — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
manuscript_versions
- id — CHAR(36)
- project_id — CHAR(36) — Foreign key → projects.id — NOT NULL
- version — INT — NOT NULL
- content — LONGTEXT — NOT NULL
- note — VARCHAR(200)
- created_by — CHAR(36) — Foreign key → users.id
- created_at — DATETIME — NOT NULL
notifications
- id — CHAR(36)
- user_id — CHAR(36) — Foreign key → users.id — NOT NULL
- type — ENUM('info','crisis_alert','payment','recording','cheer','publish_done') — NOT NULL
- message — TEXT — NOT NULL
- project_id — CHAR(36) — Foreign key → projects.id
- is_read — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
project_question_snapshots
- id — CHAR(36)
- project_id — CHAR(36) — Foreign key → projects.id — NOT NULL
- questionnaire_version — VARCHAR(20) — NOT NULL
- source — ENUM('base','tailored') — NOT NULL
- content — JSON — NOT NULL
- created_at — DATETIME — NOT NULL
projects
- id — CHAR(36)
- project_code — VARCHAR(20) — NOT NULL
- elder_id — CHAR(36) — Foreign key → users.id — NOT NULL
- family_lead_id — CHAR(36) — Foreign key → users.id — NOT NULL
- package_type — ENUM('A','B','C','D') — NOT NULL
- status — ENUM( 'payment_pending', 'payment_confirmed', 'recording', 'stt_processing', 'llm_editing', 'reviewing', 'approved', 'publishing_requested', 'publishing', 'printing', 'shipped', 'completed', 'canceled' ) — NOT NULL
- payment_amount — DECIMAL(12,0)
- payment_date — DATETIME
- questionnaire_version — VARCHAR(20)
- progress_percentage — INT — NOT NULL
- total_audio_sessions — INT — NOT NULL
- completed_audio_sessions — INT — NOT NULL
- persona — JSON
- pending_topics — JSON
- emotion_hooks — JSON
- manuscript_text — LONGTEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
questionnaires
- id — CHAR(36)
- version — VARCHAR(20) — NOT NULL
- title — VARCHAR(200)
- description — TEXT
- content — JSON — NOT NULL
- is_active — TINYINT(1) — NOT NULL
- created_by — CHAR(36) — Foreign key → users.id
- created_at — DATETIME — NOT NULL
users
- id — CHAR(36)
- email — VARCHAR(255)
- password_hash — VARCHAR(255) — NOT NULL
- role — ENUM('admin','elder','family_lead','family_member') — NOT NULL
- full_name — VARCHAR(100) — NOT NULL
- phone — VARCHAR(255)
- birth_date — VARCHAR(255)
- is_active — TINYINT(1) — NOT NULL
- last_login — DATETIME
- refresh_token — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME