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