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