zipsAI ERD
Tables (22)
ai_insight_complaints
- insight_id — BIGINT — Foreign key → ai_insights.id — NOT NULL
- complaint_id — BIGINT — Foreign key → complaints.id — NOT NULL
- similarity_score — DECIMAL(5,4)
- is_representative — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
ai_insights
- id — BIGINT — Primary key — NOT NULL — AI 인사이트 ID
- analysis_run_id — BIGINT — Foreign key → insight_analysis_runs.id — NOT NULL
- building_id — BIGINT — Foreign key → buildings.id — NOT NULL
- insight_type — VARCHAR(20) — NOT NULL — URGENT/REPEATED
- urgency — VARCHAR(20) — URGENT/CAUTION
- title — VARCHAR(255) — NOT NULL
- summary — TEXT — NOT NULL
- inferred_cause — TEXT
- evidence_summary — TEXT
- complaint_count — INT — NOT NULL
- period_start — DATETIME
- period_end — DATETIME
- rank_order — INT — NOT NULL
- created_at — DATETIME — NOT NULL
audit_logs
- id — BIGINT — Primary key — NOT NULL
- actor_user_id — BIGINT — Foreign key → users.id
- action_type — VARCHAR(100) — NOT NULL
- target_type — VARCHAR(50) — NOT NULL
- target_id — BIGINT
- request_id — VARCHAR(100)
- before_data — JSON
- after_data — JSON
- created_at — DATETIME — NOT NULL
building_managers
- id — BIGINT — Primary key — NOT NULL
- building_id — BIGINT — Foreign key → buildings.id — NOT NULL
- manager_user_id — BIGINT — Foreign key → users.id — NOT NULL
- status — VARCHAR(20) — NOT NULL — ACTIVE/INACTIVE
- started_at — DATETIME — NOT NULL
- ended_at — DATETIME
- created_at — DATETIME — NOT NULL
buildings
- id — BIGINT — Primary key — NOT NULL — 건물 ID
- name — VARCHAR(100) — NOT NULL — 건물명
- postal_code — VARCHAR(10) — NOT NULL — 우편번호
- road_address — VARCHAR(255) — NOT NULL — 도로명 주소
- detail_address — VARCHAR(255) — 상세 주소
- building_type — VARCHAR(30) — NOT NULL — ONE_ROOM/GOSHIWON/OFFICETEL/MULTI_FAMILY
- total_room_count — INT — NOT NULL — 등록 세대 수
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
complaint_attachments
- id — BIGINT — Primary key — NOT NULL
- complaint_id — BIGINT — Foreign key → complaints.id — NOT NULL
- message_id — BIGINT — Foreign key → messages.id — 첨부가 포함된 원본 메시지
- uploaded_by_user_id — BIGINT — Foreign key → users.id — NOT NULL
- file_url — VARCHAR(1000) — NOT NULL
- file_type — VARCHAR(30) — NOT NULL — IMAGE/VIDEO/FILE
- mime_type — VARCHAR(100) — NOT NULL
- file_size — BIGINT — NOT NULL
- display_order — INT — NOT NULL
- created_at — DATETIME — NOT NULL
- deleted_at — DATETIME
complaint_status_histories
- id — BIGINT — Primary key — NOT NULL
- complaint_id — BIGINT — Foreign key → complaints.id — NOT NULL
- from_status — VARCHAR(20)
- to_status — VARCHAR(20) — NOT NULL
- changed_by_type — VARCHAR(20) — NOT NULL — ADMIN/AI/SYSTEM
- changed_by_user_id — BIGINT — Foreign key → users.id
- reason — VARCHAR(500)
- request_key — VARCHAR(64) — NOT NULL — 중복 상태 변경 방지 키
- changed_at — DATETIME — NOT NULL
complaints
- id — BIGINT — Primary key — NOT NULL — 민원 ID
- request_key — VARCHAR(64) — NOT NULL — 중복 접수 방지 키
- resident_user_id — BIGINT — Foreign key → users.id — NOT NULL
- building_id — BIGINT — Foreign key → buildings.id — NOT NULL
- room_id — BIGINT — Foreign key → rooms.id — NOT NULL
- source_conversation_id — BIGINT — Foreign key → conversations.id
- title — VARCHAR(255) — NOT NULL
- category — VARCHAR(50)
- location_text — VARCHAR(255)
- occurrence_text — VARCHAR(255) — 사용자가 표현한 발생 시점
- symptom — TEXT
- status — VARCHAR(20) — NOT NULL — RECEIVED/IN_PROGRESS/RESOLVED
- urgency — VARCHAR(20) — NORMAL/CAUTION/URGENT
- ai_summary — TEXT
- resolved_by_type — VARCHAR(20) — ADMIN/AI/SYSTEM
- resolved_by_user_id — BIGINT — Foreign key → users.id
- resolved_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
conversations
- id — BIGINT — Primary key — NOT NULL — 대화 ID
- resident_user_id — BIGINT — Foreign key → users.id — NOT NULL
- building_id — BIGINT — Foreign key → buildings.id — NOT NULL
- room_id — BIGINT — Foreign key → rooms.id — NOT NULL
- conversation_type — VARCHAR(20) — NOT NULL — QUESTION/COMPLAINT
- status — VARCHAR(30) — NOT NULL — ACTIVE/RESOLVED/CLOSED/COMPLAINT_CREATED
- title — VARCHAR(255) — 첫 메시지 기반 제목
- last_message_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
idempotency_requests
- id — BIGINT — Primary key — NOT NULL
- request_key — VARCHAR(100) — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- request_type — VARCHAR(50) — NOT NULL — COMPLAINT_CREATE/STATUS_CHANGE/INVITE_ISSUE/NOTICE_PUBLISH
- resource_type — VARCHAR(50)
- resource_id — BIGINT
- processing_status — VARCHAR(20) — NOT NULL — PROCESSING/SUCCEEDED/FAILED
- response_code — INT
- expires_at — DATETIME — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
insight_analysis_runs
- id — BIGINT — Primary key — NOT NULL — 분석 실행 ID
- building_id — BIGINT — Foreign key → buildings.id — NOT NULL
- status — VARCHAR(20) — NOT NULL — PENDING/RUNNING/SUCCEEDED/FAILED
- model_version — VARCHAR(100)
- analysis_basis_at — DATETIME — 인사이트 분석 기준 시각
- started_at — DATETIME
- completed_at — DATETIME
- error_message — VARCHAR(1000)
- created_at — DATETIME — NOT NULL
invite_codes
- id — BIGINT — Primary key — NOT NULL — 초대코드 ID
- room_id — BIGINT — Foreign key → rooms.id — NOT NULL
- issued_by_user_id — BIGINT — Foreign key → users.id — NOT NULL — 발급 관리자
- code_hash — VARCHAR(255) — NOT NULL — 초대코드 해시
- status — VARCHAR(20) — NOT NULL — ACTIVE/USED/EXPIRED/REVOKED
- failed_attempt_count — INT — NOT NULL
- locked_until — DATETIME
- expires_at — DATETIME — NOT NULL
- used_by_user_id — BIGINT — Foreign key → users.id
- used_at — DATETIME
- revoked_at — DATETIME
- created_at — DATETIME — NOT NULL
messages
- id — BIGINT — Primary key — NOT NULL — 메시지 ID
- conversation_id — BIGINT — Foreign key → conversations.id — NOT NULL
- sender_type — VARCHAR(20) — NOT NULL — RESIDENT/AI/SYSTEM/ADMIN
- sender_user_id — BIGINT — Foreign key → users.id
- sequence_no — INT — NOT NULL — 대화 내 순서
- content — TEXT — NOT NULL
- created_at — DATETIME — NOT NULL
- deleted_at — DATETIME
notice_building_targets
- notice_id — BIGINT — Foreign key → notices.id — NOT NULL
- building_id — BIGINT — Foreign key → buildings.id — NOT NULL
notice_room_targets
- notice_id — BIGINT — Foreign key → notices.id — NOT NULL
- room_id — BIGINT — Foreign key → rooms.id — NOT NULL
notices
- id — BIGINT — Primary key — NOT NULL — 공지 ID
- author_user_id — BIGINT — Foreign key → users.id — NOT NULL
- title — VARCHAR(255) — NOT NULL
- content — TEXT — NOT NULL
- importance — VARCHAR(20) — NOT NULL — NORMAL/IMPORTANT/URGENT
- publish_status — VARCHAR(20) — NOT NULL — DRAFT/SCHEDULED/PUBLISHED/CANCELLED
- target_scope — VARCHAR(30) — NOT NULL — ALL_MANAGED/SPECIFIC_BUILDINGS/SPECIFIC_ROOMS
- scheduled_at — DATETIME
- published_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
notifications
- id — BIGINT — Primary key — NOT NULL — 알림 ID
- recipient_user_id — BIGINT — Foreign key → users.id — NOT NULL
- notification_type — VARCHAR(30) — NOT NULL — NOTICE/COMPLAINT_STATUS/CHAT/SYSTEM
- event_key — VARCHAR(100) — NOT NULL — 중복 알림 방지 키
- notice_id — BIGINT — Foreign key → notices.id
- complaint_id — BIGINT — Foreign key → complaints.id
- conversation_id — BIGINT — Foreign key → conversations.id
- title — VARCHAR(255) — NOT NULL
- body — VARCHAR(1000) — NOT NULL
- read_at — DATETIME
- created_at — DATETIME — NOT NULL
room_occupancies
- id — BIGINT — Primary key — NOT NULL — 입주 관계 ID
- room_id — BIGINT — Foreign key → rooms.id — NOT NULL
- resident_user_id — BIGINT — Foreign key → users.id — NOT NULL
- status — VARCHAR(20) — NOT NULL — ACTIVE/MOVED_OUT
- moved_in_at — DATETIME — NOT NULL
- moved_out_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
rooms
- id — BIGINT — Primary key — NOT NULL — 호실 ID
- building_id — BIGINT — Foreign key → buildings.id — NOT NULL
- floor_label — VARCHAR(20) — NOT NULL — 층 표기값: B1, 1, 2
- room_number — VARCHAR(30) — NOT NULL — 호실 번호
- status — VARCHAR(20) — NOT NULL — VACANT/INVITING/OCCUPIED
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
terms
- id — BIGINT — Primary key — NOT NULL — 약관 ID
- terms_type — VARCHAR(30) — NOT NULL — SERVICE/PRIVACY/MARKETING
- version — VARCHAR(30) — NOT NULL — 약관 버전
- title — VARCHAR(100) — NOT NULL
- content_url — VARCHAR(500) — NOT NULL — 약관 본문 URL
- is_required — BOOLEAN — NOT NULL
- effective_at — DATETIME — NOT NULL — 시행 일시
- created_at — DATETIME — NOT NULL
user_terms_agreements
- id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- terms_id — BIGINT — Foreign key → terms.id — NOT NULL
- agreed_at — DATETIME — NOT NULL
- withdrawn_at — DATETIME
users
- id — BIGINT — Primary key — NOT NULL — 사용자 ID
- email — VARCHAR(254) — NOT NULL — 로그인 이메일
- password_hash — VARCHAR(255) — NOT NULL — 비밀번호 해시
- name — VARCHAR(30) — NOT NULL — 사용자 이름
- phone — VARCHAR(20) — 연락처
- role — VARCHAR(20) — NOT NULL — UNASSIGNED/ADMIN/RESIDENT
- status — VARCHAR(20) — NOT NULL — ACTIVE/SUSPENDED/WITHDRAWN
- role_selected_at — DATETIME — 역할 선택 시각
- last_login_at — DATETIME — 최근 로그인 시각
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME