erd V2

Tables (29)

age_ranges

  • age_range_id — INTEGER — Primary key — NOT NULL
  • min_age — INTEGER — NOT NULL
  • max_age — INTEGER — NOT NULL
  • persona_type — TEXT

chatbot_sessions

  • chatbot_session_id — TEXT — Primary key — NOT NULL
  • session_id — TEXT — Foreign key → word_selection_sessions.session_id — NOT NULL
  • chatbot_tone — TEXT
  • status — TEXT
  • started_at — DATETIME
  • ended_at — DATETIME

chatbot_turns

  • turn_id — INTEGER — Primary key — NOT NULL
  • chatbot_session_id — TEXT — Foreign key → chatbot_sessions.chatbot_session_id — NOT NULL
  • turn_order — INTEGER
  • bot_question — TEXT
  • presented_options — TEXT
  • user_response — TEXT

content_filter_logs

  • filter_log_id — INTEGER — Primary key — NOT NULL
  • diary_id — TEXT — Foreign key → diary_entries.diary_id — NOT NULL
  • filter_result — TEXT
  • filter_reason — TEXT
  • attempt_number — INTEGER
  • filtered_at — DATETIME — NOT NULL

crisis_signal_detections

  • detection_id — INTEGER — Primary key — NOT NULL
  • session_id — TEXT — Foreign key → word_selection_sessions.session_id — NOT NULL
  • risk_level — TEXT
  • intervention_shown — BOOLEAN
  • detected_at — DATETIME — NOT NULL

daily_conversation_logs

  • daily_log_id — INTEGER — Primary key — NOT NULL
  • account_id — TEXT — Foreign key → user_accounts.account_id — NOT NULL
  • log_date — DATE — NOT NULL
  • full_conversation — TEXT
  • session_count — INTEGER
  • created_at — DATETIME — NOT NULL

daily_emotion_summary

  • summary_id — INTEGER — Primary key — NOT NULL
  • account_id — TEXT — Foreign key → user_accounts.account_id — NOT NULL
  • summary_date — DATE — NOT NULL
  • emotion_type_id — INTEGER — Foreign key → emotion_types.emotion_type_id — NOT NULL
  • avg_score — REAL
  • session_count — INTEGER

deletion_requests

  • deletion_request_id — INTEGER — Primary key — NOT NULL
  • account_id — TEXT — Foreign key → user_accounts.account_id — NOT NULL
  • requested_at — DATETIME — NOT NULL
  • status — TEXT
  • completed_at — DATETIME

diary_entries

  • diary_id — TEXT — Primary key — NOT NULL
  • chatbot_session_id — TEXT — Foreign key → chatbot_sessions.chatbot_session_id — NOT NULL
  • free_text — TEXT
  • visibility — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

disposal_history

  • disposal_id — INTEGER — Primary key — NOT NULL
  • diary_id — TEXT — Foreign key → diary_entries.diary_id — NOT NULL
  • disposal_type — TEXT
  • disposed_at — DATETIME — NOT NULL

emotion_score_details

  • score_id — INTEGER — Primary key — NOT NULL
  • session_id — TEXT — Foreign key → word_selection_sessions.session_id — NOT NULL
  • emotion_type_id — INTEGER — Foreign key → emotion_types.emotion_type_id — NOT NULL
  • score_value — REAL
  • analyzed_at — DATETIME — NOT NULL

emotion_types

  • emotion_type_id — INTEGER — Primary key — NOT NULL
  • emotion_name — TEXT — NOT NULL
  • emotion_category — TEXT

feed_pats

  • pat_id — INTEGER — Primary key — NOT NULL
  • post_id — INTEGER — Foreign key → feed_posts.post_id — NOT NULL
  • account_id — TEXT — Foreign key → user_accounts.account_id
  • patted_at — DATETIME — NOT NULL

feed_posts

  • post_id — INTEGER — Primary key — NOT NULL
  • diary_id — TEXT — Foreign key → diary_entries.diary_id — NOT NULL
  • posted_at — DATETIME — NOT NULL
  • moderation_status — TEXT

feed_reports

  • report_id — INTEGER — Primary key — NOT NULL
  • post_id — INTEGER — Foreign key → feed_posts.post_id — NOT NULL
  • reporter_account_id — TEXT — Foreign key → user_accounts.account_id
  • report_reason — TEXT
  • reported_at — DATETIME — NOT NULL

feed_views

  • view_id — INTEGER — Primary key — NOT NULL
  • post_id — INTEGER — Foreign key → feed_posts.post_id — NOT NULL
  • viewer_account_id — TEXT — Foreign key → user_accounts.account_id
  • viewed_at — DATETIME — NOT NULL

llm_response_validations

  • validation_id — INTEGER — Primary key — NOT NULL
  • test_run_id — INTEGER — Foreign key → test_runs.test_run_id — NOT NULL
  • target_type — TEXT
  • target_id — INTEGER
  • validation_result — TEXT
  • validated_at — DATETIME — NOT NULL

pattern_reports

  • report_id — INTEGER — Primary key — NOT NULL
  • account_id — TEXT — Foreign key → user_accounts.account_id — NOT NULL
  • report_period — TEXT
  • summary_text — TEXT
  • notified — BOOLEAN
  • viewed_at — DATETIME
  • generated_at — DATETIME — NOT NULL

report_resolutions

  • resolution_id — INTEGER — Primary key — NOT NULL
  • report_id — INTEGER — Foreign key → feed_reports.report_id — NOT NULL
  • action_taken — TEXT
  • resolved_at — DATETIME — NOT NULL

report_source_logs

  • report_id — INTEGER — Foreign key → pattern_reports.report_id — NOT NULL
  • daily_log_id — INTEGER — Foreign key → daily_conversation_logs.daily_log_id — NOT NULL

selected_word_items

  • selection_item_id — INTEGER — Primary key — NOT NULL
  • session_id — TEXT — Foreign key → word_selection_sessions.session_id — NOT NULL
  • word_id — INTEGER — Foreign key → words.word_id — NOT NULL
  • selected_order — INTEGER

stability_test_logs

  • stability_log_id — INTEGER — Primary key — NOT NULL
  • test_run_id — INTEGER — Foreign key → test_runs.test_run_id — NOT NULL
  • endpoint — TEXT
  • response_time_ms — INTEGER
  • error_occurred — BOOLEAN
  • logged_at — DATETIME — NOT NULL

test_runs

  • test_run_id — INTEGER — Primary key — NOT NULL
  • run_type — TEXT
  • triggered_at — DATETIME — NOT NULL
  • status — TEXT

user_accounts

  • account_id — TEXT — Primary key — NOT NULL
  • nickname — TEXT — NOT NULL
  • pin_hash — TEXT
  • birth_year — INTEGER
  • age_range_id — INTEGER — Foreign key → age_ranges.age_range_id
  • consent_agreed_at — DATETIME
  • auth_verified_at — DATETIME
  • auth_provider — TEXT
  • created_at — DATETIME — NOT NULL

user_events

  • event_id — INTEGER — Primary key — NOT NULL
  • account_id — TEXT — Foreign key → user_accounts.account_id
  • event_type — TEXT — NOT NULL
  • event_payload — TEXT
  • occurred_at — DATETIME — NOT NULL

word_categories

  • category_id — INTEGER — Primary key — NOT NULL
  • category_name — TEXT — NOT NULL
  • target_persona — TEXT

word_pool_presentations

  • presentation_id — INTEGER — Primary key — NOT NULL
  • session_id — TEXT — Foreign key → word_selection_sessions.session_id — NOT NULL
  • word_id — INTEGER — Foreign key → words.word_id — NOT NULL
  • display_order — INTEGER

word_selection_sessions

  • session_id — TEXT — Primary key — NOT NULL
  • account_id — TEXT — Foreign key → user_accounts.account_id — NOT NULL
  • created_at — DATETIME — NOT NULL

words

  • word_id — INTEGER — Primary key — NOT NULL
  • category_id — INTEGER — Foreign key → word_categories.category_id — NOT NULL
  • word_text — TEXT — NOT NULL
  • intensity_level — INTEGER