A306_jin

Tables (47)

adoption_participants

  • participant_role — VARCHAR(20) — Primary key — NOT NULL
  • relationship_id — BIGINT — Foreign key → adoption_relationships.relationship_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • joined_at — DATETIME — NOT NULL
  • left_at — DATETIME

adoption_relationships

  • relationship_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • relationship_status — VARCHAR(20) — NOT NULL
  • started_at — DATETIME
  • ended_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

ai_report_records

  • ai_report_id — BIGINT — Foreign key → ai_reports.ai_report_id — NOT NULL
  • care_record_id — BIGINT — Foreign key → care_records.care_record_id — NOT NULL

ai_report_sections

  • ai_report_section_id — BIGINT — Primary key — NOT NULL
  • ai_report_id — BIGINT — Foreign key → ai_reports.ai_report_id — NOT NULL
  • section_type — VARCHAR(30) — NOT NULL
  • section_content — TEXT — NOT NULL
  • display_order — INT — NOT NULL

ai_reports

  • ai_report_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • report_type — VARCHAR(20) — NOT NULL
  • period_start — DATE — NOT NULL
  • period_end — DATE — NOT NULL
  • report_status — VARCHAR(20) — NOT NULL
  • model_name — VARCHAR(100)
  • prompt_version — VARCHAR(50)
  • generated_at — DATETIME — NOT NULL

breeds

  • breed_id — BIGINT — Primary key — NOT NULL
  • breed_name_ko — VARCHAR(100) — NOT NULL
  • breed_name_en — VARCHAR(100)
  • created_at — DATETIME — NOT NULL

care_answer_options

  • care_answer_id — BIGINT — Foreign key → care_answers.care_answer_id — NOT NULL
  • checklist_option_id — BIGINT — Foreign key → checklist_item_options.checklist_option_id — NOT NULL

care_answers

  • care_answer_id — BIGINT — Primary key — NOT NULL
  • care_record_id — BIGINT — Foreign key → care_records.care_record_id — NOT NULL
  • checklist_item_id — BIGINT — Foreign key → checklist_items.checklist_item_id — NOT NULL
  • value_text — TEXT
  • value_number — DECIMAL(15,4)
  • value_boolean — TINYINT(1)
  • value_datetime — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

care_record_media

  • care_record_id — BIGINT — Foreign key → care_records.care_record_id — NOT NULL
  • media_id — BIGINT — Foreign key → media_files.media_id — NOT NULL
  • display_order — INT — NOT NULL

care_records

  • care_record_id — BIGINT — Primary key — NOT NULL
  • checklist_version_id — BIGINT — Foreign key → checklist_versions.checklist_version_id — NOT NULL
  • author_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • record_date — DATE — NOT NULL
  • record_status — VARCHAR(20) — NOT NULL
  • submitted_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

chat_message_media

  • chat_message_id — BIGINT — Foreign key → chat_messages.chat_message_id — NOT NULL
  • media_id — BIGINT — Foreign key → media_files.media_id — NOT NULL
  • display_order — INT — NOT NULL

chat_messages

  • chat_message_id — BIGINT — Primary key — NOT NULL
  • chat_room_id — BIGINT — Foreign key → chat_rooms.chat_room_id — NOT NULL
  • sender_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • message_type — VARCHAR(20) — NOT NULL
  • content — TEXT
  • created_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

chat_room_members

  • chat_room_id — BIGINT — Foreign key → chat_rooms.chat_room_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • joined_at — DATETIME — NOT NULL
  • left_at — DATETIME

chat_rooms

  • chat_room_id — BIGINT — Primary key — NOT NULL
  • relationship_id — BIGINT — Foreign key → adoption_relationships.relationship_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • closed_at — DATETIME

checklist_item_options

  • checklist_option_id — BIGINT — Primary key — NOT NULL
  • checklist_item_id — BIGINT — Foreign key → checklist_items.checklist_item_id — NOT NULL
  • option_text — VARCHAR(255) — NOT NULL
  • option_value — VARCHAR(100) — NOT NULL
  • display_order — INT — NOT NULL
  • risk_score — INT — NOT NULL

checklist_items

  • checklist_item_id — BIGINT — Primary key — NOT NULL
  • checklist_version_id — BIGINT — Foreign key → checklist_versions.checklist_version_id — NOT NULL
  • question_text — VARCHAR(1000) — NOT NULL
  • answer_type — VARCHAR(30) — NOT NULL
  • metric_code — VARCHAR(50)
  • is_required — TINYINT(1) — NOT NULL
  • display_order — INT — NOT NULL
  • unit — VARCHAR(50)
  • created_at — DATETIME — NOT NULL

checklist_templates

  • checklist_template_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • created_by_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • created_at — DATETIME — NOT NULL

checklist_versions

  • checklist_version_id — BIGINT — Primary key — NOT NULL
  • checklist_template_id — BIGINT — Foreign key → checklist_templates.checklist_template_id — NOT NULL
  • version_no — INT — NOT NULL
  • source_type — VARCHAR(20) — NOT NULL
  • version_status — VARCHAR(20) — NOT NULL
  • frequency_type — VARCHAR(20) — NOT NULL
  • approved_by_user_id — BIGINT — Foreign key → users.user_id
  • approved_at — DATETIME
  • effective_from — DATE
  • effective_to — DATE
  • created_at — DATETIME — NOT NULL

dog_allergies

  • allergy_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • allergen_name — VARCHAR(200) — NOT NULL
  • reaction_description — TEXT
  • severity — VARCHAR(20)
  • created_at — DATETIME — NOT NULL

dog_breeds

  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • breed_id — BIGINT — Foreign key → breeds.breed_id — NOT NULL
  • is_primary — TINYINT(1) — NOT NULL

dog_conditions

  • condition_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • condition_name — VARCHAR(200) — NOT NULL
  • diagnosed_at — DATE
  • condition_status — VARCHAR(20) — NOT NULL
  • notes — TEXT
  • created_at — DATETIME — NOT NULL

dog_custom_field_values

  • custom_field_id — BIGINT — Foreign key → dog_custom_fields.custom_field_id — NOT NULL
  • value_text — TEXT
  • value_number — DECIMAL(15,4)
  • value_date — DATE
  • value_boolean — TINYINT(1)
  • updated_at — DATETIME — NOT NULL

dog_custom_fields

  • custom_field_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • field_label — VARCHAR(100) — NOT NULL
  • data_type — VARCHAR(20) — NOT NULL
  • display_order — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

dog_followers

  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • follower_status — VARCHAR(20) — NOT NULL
  • followed_at — DATETIME — NOT NULL
  • ended_at — DATETIME

dog_forbidden_foods

  • forbidden_food_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • food_name — VARCHAR(100) — NOT NULL
  • reason — TEXT

dog_lifestyle_profiles

  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • eating_habit — TEXT
  • bowel_habit — TEXT
  • walking_habit — TEXT
  • sleeping_habit — TEXT
  • personality — TEXT
  • separation_anxiety_notes — TEXT
  • aggression_notes — TEXT
  • special_notes — TEXT
  • updated_at — DATETIME — NOT NULL

dog_medications

  • medication_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • medication_name — VARCHAR(200) — NOT NULL
  • dosage — VARCHAR(100)
  • medication_frequency — VARCHAR(100)
  • started_at — DATE
  • ended_at — DATE
  • notes — TEXT
  • created_at — DATETIME — NOT NULL

dog_nose_prints

  • nose_print_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • media_id — BIGINT — Foreign key → media_files.media_id — NOT NULL
  • registered_by_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • model_version — VARCHAR(100)
  • nose_print_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL

dog_schedules

  • schedule_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • created_by_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • schedule_type — VARCHAR(30) — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT
  • starts_at — DATETIME — NOT NULL
  • ends_at — DATETIME
  • recurrence_rule — VARCHAR(500)
  • schedule_status — VARCHAR(20) — NOT NULL
  • completed_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

dog_vaccinations

  • vaccination_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • vaccination_name — VARCHAR(100) — NOT NULL
  • vaccinated_at — DATE
  • next_due_date — DATE
  • hospital_name — VARCHAR(200)
  • notes — TEXT
  • created_at — DATETIME — NOT NULL

dogs

  • dog_id — BIGINT — Primary key — NOT NULL
  • created_by_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • profile_media_id — BIGINT — Foreign key → media_files.media_id
  • name — VARCHAR(50) — NOT NULL
  • birth_date — DATE
  • birth_date_is_estimated — TINYINT(1) — NOT NULL
  • sex — VARCHAR(20) — NOT NULL
  • neutered — TINYINT(1)
  • animal_registration_no — VARCHAR(100)
  • dog_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

media_files

  • media_id — BIGINT — Primary key — NOT NULL
  • uploaded_by_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • original_file_name — VARCHAR(255) — NOT NULL
  • stored_url — VARCHAR(1000) — NOT NULL
  • mime_type — VARCHAR(100) — NOT NULL
  • file_size_bytes — BIGINT
  • created_at — DATETIME — NOT NULL

missions

  • mission_id — BIGINT — Primary key — NOT NULL
  • ai_report_id — BIGINT — Foreign key → ai_reports.ai_report_id — NOT NULL
  • assignee_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT — NOT NULL
  • start_date — DATE
  • due_date — DATE
  • mission_status — VARCHAR(20) — NOT NULL
  • completed_at — DATETIME
  • created_at — DATETIME — NOT NULL

nose_verifications

  • nose_verification_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • requested_by_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • reference_nose_print_id — BIGINT — Foreign key → dog_nose_prints.nose_print_id
  • input_media_id — BIGINT — Foreign key → media_files.media_id — NOT NULL
  • similarity_score — DECIMAL(7,6)
  • verification_result — VARCHAR(20) — NOT NULL
  • model_version — VARCHAR(100)
  • verified_at — DATETIME — NOT NULL

notification_recipients

  • notification_id — BIGINT — Foreign key → notifications.notification_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • delivery_status — VARCHAR(20) — NOT NULL
  • read_at — DATETIME
  • created_at — DATETIME — NOT NULL

notifications

  • notification_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id
  • notification_type — VARCHAR(50) — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • link_path — VARCHAR(1000)
  • created_at — DATETIME — NOT NULL

post_comments

  • post_comment_id — BIGINT — Primary key — NOT NULL
  • post_id — BIGINT — Foreign key → posts.post_id — NOT NULL
  • author_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • parent_comment_id — BIGINT — Foreign key → post_comments.post_comment_id
  • content — VARCHAR(1000) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

post_media

  • post_id — BIGINT — Foreign key → posts.post_id — NOT NULL
  • media_id — BIGINT — Foreign key → media_files.media_id — NOT NULL
  • display_order — INT — NOT NULL

posts

  • post_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • author_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • content — TEXT
  • visibility — VARCHAR(20) — NOT NULL
  • post_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

question_categories

  • question_category_id — BIGINT — Primary key — NOT NULL
  • category_name — VARCHAR(100) — NOT NULL
  • display_order — INT — NOT NULL

question_comments

  • question_comment_id — BIGINT — Primary key — NOT NULL
  • question_id — BIGINT — Foreign key → questions.question_id — NOT NULL
  • author_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • parent_comment_id — BIGINT — Foreign key → question_comments.question_comment_id
  • content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

questions

  • question_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id
  • author_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • question_category_id — BIGINT — Foreign key → question_categories.question_category_id
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • question_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

risk_event_care_records

  • risk_event_id — BIGINT — Foreign key → risk_events.risk_event_id — NOT NULL
  • care_record_id — BIGINT — Foreign key → care_records.care_record_id — NOT NULL

risk_event_reports

  • risk_event_id — BIGINT — Foreign key → risk_events.risk_event_id — NOT NULL
  • ai_report_id — BIGINT — Foreign key → ai_reports.ai_report_id — NOT NULL

risk_events

  • risk_event_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • resolved_by_user_id — BIGINT — Foreign key → users.user_id
  • risk_type — VARCHAR(50) — NOT NULL
  • severity — VARCHAR(20) — NOT NULL
  • detection_source — VARCHAR(20) — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT — NOT NULL
  • risk_status — VARCHAR(20) — NOT NULL
  • detected_at — DATETIME — NOT NULL
  • resolved_at — DATETIME

users

  • user_id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(50)
  • account_role — VARCHAR(20) — NOT NULL
  • account_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

video_calls

  • video_call_id — BIGINT — Primary key — NOT NULL
  • chat_room_id — BIGINT — Foreign key → chat_rooms.chat_room_id — NOT NULL
  • caller_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • callee_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • call_status — VARCHAR(20) — NOT NULL
  • requested_at — DATETIME — NOT NULL
  • started_at — DATETIME
  • ended_at — DATETIME