260721_CMS

Tables (18)

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
  • answer_value — JSON — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

care_photos

  • care_photo_id — BIGINT — Primary key — NOT NULL
  • care_record_id — BIGINT — Foreign key — NOT NULL
  • photo_url — VARCHAR(2048) — NOT NULL
  • sort_order — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

care_records

  • care_record_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • writer_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • record_date — DATE — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — 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 — NOT NULL
  • content — TEXT — NOT NULL
  • sent_at — DATETIME — NOT NULL
  • read_at — DATETIME

chat_rooms

  • chat_room_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key — NOT NULL
  • created_at — DATETIME — NOT NULL

checklist_items

  • checklist_item_id — BIGINT — Primary key — NOT NULL
  • checklist_id — BIGINT — Foreign key → checklists.checklist_id — NOT NULL
  • category — VARCHAR(30) — NOT NULL
  • question_text — VARCHAR(500) — NOT NULL
  • answer_type — ENUM('BOOLEAN', 'NUMBER', 'TEXT') — NOT NULL
  • sort_order — INT — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

checklists

  • checklist_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • generated_by_ai — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

dog_members

  • dog_member_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • role — ENUM('BREEDER', 'ADOPTER', 'FOLLOWER') — NOT NULL
  • created_at — DATETIME — NOT NULL

dogs

  • dog_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • birth_date — DATE — NOT NULL
  • breed — VARCHAR(100) — NOT NULL
  • lifestyle_notes — TEXT — NOT NULL
  • profile_image_url — VARCHAR(2048) — NOT NULL
  • created_at — DATETIME — NOT NULL

feed_posts

  • feed_post_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key — NOT NULL
  • author_user_id — BIGINT — Foreign key — NOT NULL
  • content — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

post_media

  • post_media_id — BIGINT — Primary key — NOT NULL
  • feed_post_id — BIGINT — Foreign key → feed_posts.feed_post_id — NOT NULL
  • media_type — ENUM('PHOTO', 'VIDEO') — NOT NULL
  • media_url — VARCHAR(2048) — NOT NULL
  • thumbnail_url — VARCHAR(2048)
  • sort_order — INT — NOT NULL
  • created_at — DATETIME — 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 — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

questions

  • question_id — BIGINT — Primary key — NOT NULL
  • author_user_id — BIGINT — Foreign key — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

report_findings

  • report_finding_id — BIGINT — Primary key — NOT NULL
  • weekly_report_id — BIGINT — Foreign key → weekly_reports.weekly_report_id — NOT NULL
  • finding_type — ENUM('RISK', 'MANAGEMENT_GAP') — NOT NULL
  • severity — ENUM('LOW', 'MEDIUM', 'HIGH') — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • detail — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL

schedules

  • schedule_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key — NOT NULL
  • creator_user_id — BIGINT — Foreign key — NOT NULL
  • schedule_type — ENUM('VACCINATION', 'HOSPITAL', 'OTHER') — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • memo — TEXT
  • scheduled_at — DATETIME — NOT NULL
  • reminder_at — DATETIME — NOT NULL
  • status — ENUM('SCHEDULED', 'COMPLETED', 'CANCELED') — NOT NULL
  • created_at — DATETIME — NOT NULL

users

  • user_id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • created_at — DATETIME — NOT NULL

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 — NOT NULL
  • receiver_user_id — BIGINT — Foreign key — NOT NULL
  • status — ENUM('REQUESTED', 'ACCEPTED', 'REJECTED', 'ENDED', 'MISSED') — NOT NULL
  • requested_at — DATETIME — NOT NULL
  • started_at — DATETIME
  • ended_at — DATETIME

weekly_reports

  • weekly_report_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key — NOT NULL
  • period_start — DATE — NOT NULL
  • period_end — DATE — NOT NULL
  • summary — TEXT — NOT NULL
  • generated_at — DATETIME — NOT NULL