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