member3

Tables (19)

adopter_pre_survey

  • survey_id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → adoption_cases.case_id — NOT NULL
  • housing_type — ENUM('APARTMENT','HOUSE','OFFICETEL','ETC')
  • family_members — INT
  • has_children — TINYINT(1)
  • pet_experience — ENUM('NONE','PAST','CURRENT')
  • daily_alone_hours — INT
  • monthly_budget — INT
  • responsibility_score — TINYINT
  • raw_answers_json — TEXT
  • submitted_at — DATETIME — NOT NULL

adoption_cases

  • case_id — BIGINT — Primary key — NOT NULL
  • dog_id — BIGINT — Foreign key → dogs.dog_id — NOT NULL
  • adopter_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • shelter_id — BIGINT — Foreign key → shelters.shelter_id — NOT NULL
  • current_status — ENUM('APPLIED', 'DOC_REVIEW', 'NEED_SUPPLEMENT', 'INTERVIEW_VISIT', 'APPROVED', 'REJECTED', 'CANCELLED', 'ADOPTED') — NOT NULL
  • applied_at — DATETIME — NOT NULL
  • decided_at — DATETIME
  • adopted_at — DATETIME
  • responsibility_agreed — TINYINT(1) — NOT NULL
  • note — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

case_documents

  • doc_id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → adoption_cases.case_id — NOT NULL
  • uploader_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • doc_type — ENUM('ID','CONSENT','HOUSING_PROOF','PHOTO','VIDEO','OTHER') — NOT NULL
  • file_url — VARCHAR(1000) — NOT NULL
  • file_name — VARCHAR(255)
  • uploaded_at — DATETIME — NOT NULL

case_request_replies

  • reply_id — BIGINT — Primary key — NOT NULL
  • request_id — BIGINT — Foreign key → case_requests.request_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • body — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL

case_requests

  • request_id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → adoption_cases.case_id — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • request_type — ENUM('SUPPLEMENT','QUESTION','SCHEDULE','INFO') — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • body — TEXT — NOT NULL
  • status — ENUM('OPEN','ANSWERED','CLOSED') — NOT NULL
  • created_at — DATETIME — NOT NULL
  • closed_at — DATETIME

case_status_history

  • history_id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → adoption_cases.case_id — NOT NULL
  • from_status — VARCHAR(30)
  • to_status — VARCHAR(30) — NOT NULL
  • changed_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • changed_at — DATETIME — NOT NULL
  • reason — VARCHAR(500)

dogs

  • dog_id — BIGINT — Primary key — NOT NULL
  • shelter_id — BIGINT — Foreign key → shelters.shelter_id — NOT NULL
  • name — VARCHAR(100)
  • sex — ENUM('M','F','UNKNOWN') — NOT NULL
  • estimated_age_years — INT
  • size — ENUM('SMALL','MEDIUM','LARGE','UNKNOWN') — NOT NULL
  • breed_guess — VARCHAR(120)
  • intake_date — DATE
  • status — ENUM('AVAILABLE','ON_HOLD','ADOPTED','NOT_AVAILABLE') — NOT NULL
  • profile — TEXT
  • created_at — DATETIME — NOT NULL

notifications

  • notification_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • case_id — BIGINT — Foreign key → adoption_cases.case_id
  • type — ENUM('MISSION_DUE','REQUEST','STATUS_CHANGED','GENERAL') — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • body — VARCHAR(800) — NOT NULL
  • is_read — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

oauth_user

  • oauth_user_id — BIGINT — Primary key — NOT NULL
  • provider — VARCHAR(30) — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • oauth_id — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL

post_mission_items

  • item_id — BIGINT — Primary key — NOT NULL
  • template_id — BIGINT — Foreign key → post_mission_template.template_id — NOT NULL
  • due_offset_days — INT — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • description — VARCHAR(800)
  • requires_media — TINYINT(1) — NOT NULL
  • sort_order — INT — NOT NULL

post_mission_media

  • media_id — BIGINT — Primary key — NOT NULL
  • mission_id — BIGINT — Foreign key → post_missions.mission_id — NOT NULL
  • uploader_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • media_type — ENUM('PHOTO','VIDEO','OTHER') — NOT NULL
  • file_url — VARCHAR(1000) — NOT NULL
  • uploaded_at — DATETIME — NOT NULL

post_mission_template

  • template_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL

post_missions

  • mission_id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → adoption_cases.case_id — NOT NULL
  • item_id — BIGINT — Foreign key → post_mission_items.item_id — NOT NULL
  • due_date — DATE — NOT NULL
  • status — ENUM('PENDING','SUBMITTED','REVIEWED','OVERDUE') — NOT NULL
  • submitted_at — DATETIME
  • reviewed_at — DATETIME
  • reviewer_id — BIGINT — Foreign key → users.user_id
  • feedback — VARCHAR(800)

pre_checklist_items

  • item_id — BIGINT — Primary key — NOT NULL
  • template_id — BIGINT — Foreign key → pre_checklist_template.template_id — NOT NULL
  • item_text — VARCHAR(500) — NOT NULL
  • sort_order — INT — NOT NULL
  • required — TINYINT(1) — NOT NULL

pre_checklist_submissions

  • submission_id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → adoption_cases.case_id — NOT NULL
  • item_id — BIGINT — Foreign key → pre_checklist_items.item_id — NOT NULL
  • checked — TINYINT(1) — NOT NULL
  • note — VARCHAR(500)
  • submitted_at — DATETIME — NOT NULL

pre_checklist_template

  • template_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

shelter_admins

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • shelter_id — BIGINT — Foreign key → shelters.shelter_id — NOT NULL
  • created_at — DATETIME — NOT NULL

shelters

  • shelter_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • phone — VARCHAR(30)
  • address — VARCHAR(300)
  • created_at — DATETIME — NOT NULL

users

  • user_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • phone — VARCHAR(30)
  • address — VARCHAR(255)
  • user_type — ENUM('admin', 'adopter', 'shelter') — NOT NULL
  • is_email_verified — BOOLEAN
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL