somi v1

Tables (28)

_data_migrations

  • version — text — Primary key — NOT NULL
  • name — text — NOT NULL
  • started_at — timestamptz — NOT NULL
  • finished_at — timestamptz — NOT NULL

audit_event

  • id — uuid — Primary key — NOT NULL
  • seq — bigint
  • command — text — NOT NULL
  • actor_id — text — NOT NULL
  • after_state — jsonb
  • occurred_at — timestamptz — NOT NULL
  • prev_hash — text — NOT NULL
  • row_hash — text — NOT NULL

catalog_release

  • id — uuid — Primary key — NOT NULL
  • package_version — text — NOT NULL
  • released_at — timestamptz — NOT NULL
  • applied_at — timestamptz
  • total_patient_count — integer
  • applied_patient_count — integer
  • failed_patient_count — integer
  • created_at — timestamptz — NOT NULL

checkin_record

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → research_enrollment.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research_enrollment.research_id — NOT NULL
  • checkin_id — uuid — NOT NULL
  • content_version — text — NOT NULL
  • answers — jsonb — NOT NULL
  • checkin_date — date — NOT NULL
  • completed_at — timestamptz — NOT NULL

component_level_change_event

  • id — uuid — Primary key — NOT NULL
  • seq — bigint
  • external_patient_id — uuid — Foreign key → research_enrollment.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research_enrollment.research_id — NOT NULL
  • trigger — text — NOT NULL
  • week_index — integer
  • verdict — text
  • level_before — integer
  • level_after — integer — NOT NULL
  • risk_track — text — NOT NULL
  • ratings_snapshot — jsonb
  • ratings_sum — integer
  • sessions_count — integer
  • algorithm_version — text — NOT NULL
  • supersedes_event_id — uuid — Foreign key → component_level_change_event.id
  • decided_at — timestamptz — NOT NULL

diary_record

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → research_enrollment.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research_enrollment.research_id — NOT NULL
  • diary_id — uuid — NOT NULL
  • content_version — text — NOT NULL
  • answers — jsonb — NOT NULL
  • diary_for_at — date — NOT NULL
  • completed_at — timestamptz — NOT NULL

domain_content_record

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → domain_serve_log.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → domain_serve_log.research_id — NOT NULL
  • domain — text — Foreign key → domain_serve_log.domain — NOT NULL
  • queue_version_id — uuid — Foreign key → domain_serve_log.queue_version_id — NOT NULL
  • queue_position — integer — Foreign key → domain_serve_log.position — NOT NULL
  • content_type — text — NOT NULL
  • content_id — integer — NOT NULL
  • level_before — integer — NOT NULL
  • level_after — integer — NOT NULL
  • attributed_day_index — integer — NOT NULL
  • actual_day_index — integer — NOT NULL
  • entrypoint — text
  • score — integer
  • result — jsonb
  • result_schema_version — text
  • difficulty_rating — integer
  • feedback_text — text
  • catalog_version — text — NOT NULL
  • app_version — text
  • served_from_log_id — uuid — Foreign key → domain_serve_log.id
  • completed_at — timestamptz — NOT NULL

domain_level_change_event

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → domain_content_record.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → domain_content_record.research_id — NOT NULL
  • domain — text — Foreign key → domain_content_record.domain — NOT NULL
  • level_before — integer — NOT NULL
  • level_after — integer — NOT NULL
  • occurred_at — timestamptz — NOT NULL
  • content_record_id — uuid — Foreign key → domain_content_record.id
  • created_at — timestamptz — NOT NULL

domain_queue_content

  • id — uuid — Primary key — NOT NULL
  • queue_version_id — uuid — Foreign key → domain_queue_version.id — NOT NULL
  • position — integer — NOT NULL
  • content_type — text — NOT NULL
  • content_id — integer — NOT NULL

domain_queue_version

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → research_enrollment.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research_enrollment.research_id — NOT NULL
  • domain — text — NOT NULL
  • version — integer — NOT NULL
  • status — text — NOT NULL
  • seed — text — NOT NULL
  • algorithm_version — text — NOT NULL
  • catalog_version — text — NOT NULL
  • catalog_release_id — uuid — Foreign key → catalog_release.id
  • created_at — timestamptz — NOT NULL

domain_recommendation_board

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → research_enrollment.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research_enrollment.research_id — NOT NULL
  • initial_rank_seed — text — NOT NULL
  • created_at — timestamptz — NOT NULL

domain_serve_log

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → domain_queue_version.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → domain_queue_version.research_id — NOT NULL
  • domain — text — Foreign key → domain_queue_version.domain — NOT NULL
  • queue_version_id — uuid — Foreign key → domain_queue_content.queue_version_id — NOT NULL
  • position — integer — Foreign key → domain_queue_content.position — NOT NULL
  • content_type — text — NOT NULL
  • content_id — integer — NOT NULL
  • level_at_serve — integer — NOT NULL
  • entrypoint — text
  • app_version — text
  • served_at — timestamptz — NOT NULL

domain_state

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → domain_recommendation_board.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → domain_recommendation_board.research_id — NOT NULL
  • domain — text — NOT NULL
  • level — integer — NOT NULL
  • rank — integer — NOT NULL
  • cursor — integer — NOT NULL
  • last_completed_at — timestamptz
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

mindfulness_record

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → research_enrollment.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research_enrollment.research_id — NOT NULL
  • day_index — integer — NOT NULL
  • content_type — text — NOT NULL
  • content_id — integer — NOT NULL
  • catalog_version — text
  • selection_algorithm_version — text
  • difficulty_rating — integer
  • feedback_text — text
  • app_version — text
  • completed_at — timestamptz — NOT NULL
  • created_at — timestamptz — NOT NULL

onboarding_education_record

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → research_enrollment.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research_enrollment.research_id — NOT NULL
  • education_type — text — NOT NULL
  • result — jsonb
  • app_version — text
  • completed_at — timestamptz — NOT NULL

organization

  • id — uuid — Primary key — NOT NULL
  • name — varchar — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL
  • deleted_at — timestamptz

patient

  • external_patient_id — uuid — Primary key — NOT NULL
  • nickname — text
  • education_level — text
  • phone_number — text
  • phone_verified_at — timestamptz
  • hashed_pin — text — NOT NULL
  • is_pin_changed — boolean — NOT NULL
  • first_login_at — timestamptz
  • created_at — timestamptz — NOT NULL

research

  • id — uuid — Primary key — NOT NULL
  • organization_id — uuid — Foreign key → organization.id — NOT NULL
  • code — text — NOT NULL
  • name — text — NOT NULL
  • status — text — NOT NULL
  • start_date — text
  • end_date — text
  • duration_weeks — integer — NOT NULL
  • created_at — timestamptz — NOT NULL
  • deleted_at — timestamptz

research_course_connection

  • id — uuid — Primary key — NOT NULL
  • research_id — uuid — Foreign key → research.id — NOT NULL
  • course_type — text — NOT NULL
  • kind — text — NOT NULL
  • priority_group — integer
  • sessions_per_week — integer
  • created_at — timestamptz — NOT NULL

research_enrollment

  • external_patient_id — uuid — Foreign key → patient.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research.id — NOT NULL
  • registration_no — text — NOT NULL
  • status — text — NOT NULL
  • started_at — timestamptz
  • enrolled_at — timestamptz — NOT NULL

staff

  • id — uuid — Primary key — NOT NULL
  • auth_subject — text — NOT NULL
  • name — text
  • role — text — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

staff_research_access

  • id — uuid — Primary key — NOT NULL
  • staff_id — uuid — Foreign key → staff.id — NOT NULL
  • research_id — uuid — Foreign key → research.id — NOT NULL
  • revoked_at — timestamptz
  • created_at — timestamptz — NOT NULL

terms_agreement_record

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → research_enrollment.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research_enrollment.research_id — NOT NULL
  • terms_type — text — NOT NULL
  • terms_version — text — NOT NULL
  • agreed_at — timestamptz — NOT NULL
  • created_at — timestamptz — NOT NULL

training_adverse_event

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → training_session_record.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → training_session_record.research_id — NOT NULL
  • session_record_id — uuid — Foreign key → training_session_record.id
  • event_type — text — NOT NULL
  • severity — text
  • is_serious — boolean — NOT NULL
  • detail — jsonb
  • occurred_at — timestamptz — NOT NULL
  • created_at — timestamptz — NOT NULL

training_exercise_completion

  • id — uuid — Primary key — NOT NULL
  • session_record_id — uuid — Foreign key → training_session_record.id — NOT NULL
  • physical_exercise_id — integer — NOT NULL
  • physical_exercise_version — integer
  • catalog_version — text
  • section — text
  • order — integer — NOT NULL
  • result — jsonb
  • result_schema_version — text
  • completed_at — timestamptz — NOT NULL

training_screening_record

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → research_enrollment.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → research_enrollment.research_id — NOT NULL
  • answers — jsonb — NOT NULL
  • score — integer — NOT NULL
  • risk_track — text — NOT NULL
  • raw_safety_flags — jsonb — NOT NULL
  • screening_algorithm_version — text — NOT NULL
  • app_version — text
  • completed_at — timestamptz — NOT NULL

training_session_record

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → training_state.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → training_state.research_id — NOT NULL
  • risk_track — text — NOT NULL
  • routine_id — text — NOT NULL
  • matrix_version — text — NOT NULL
  • component_level — integer — NOT NULL
  • training_exercise_type — text — NOT NULL
  • training_exercise_version — integer — NOT NULL
  • week_index — integer — NOT NULL
  • day_index — integer — NOT NULL
  • session_no — integer
  • status — text — NOT NULL
  • difficulty_rating — integer
  • feedback_text — text
  • result — jsonb
  • result_schema_version — text
  • app_version — text
  • started_at — timestamptz — NOT NULL
  • aborted_at — timestamptz
  • completed_at — timestamptz

training_state

  • id — uuid — Primary key — NOT NULL
  • external_patient_id — uuid — Foreign key → component_level_change_event.external_patient_id — NOT NULL
  • research_id — uuid — Foreign key → component_level_change_event.research_id — NOT NULL
  • risk_track — text — NOT NULL
  • component_level — integer — NOT NULL
  • last_level_change_event_id — uuid — Foreign key → component_level_change_event.id
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL