piggyback_erd

Tables (14)

branch

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL — 지점명
  • address — VARCHAR(200) — NOT NULL
  • phone — VARCHAR(20)
  • lat — DECIMAL(10, 7) — NOT NULL — 위도
  • lng — DECIMAL(10, 7) — NOT NULL — 경도
  • region_code — VARCHAR(10) — NOT NULL — 법정동 코드 (폴백 검색용)

branch_task

  • branch_id — BIGINT — Foreign key → branch.id — NOT NULL
  • task_type_code — VARCHAR(40) — Foreign key → task_type.code — NOT NULL

checklist_item

  • id — BIGINT — Primary key — NOT NULL
  • task_type_code — VARCHAR(40) — Foreign key → task_type.code — NOT NULL
  • item_code — VARCHAR(30) — NOT NULL — ID_CARD | SEAL | POA | FAMILY_CERT ...
  • name — VARCHAR(100) — NOT NULL — 준비물명
  • easy_description — VARCHAR(300) — NOT NULL — 쉬운 말 설명
  • required — BOOLEAN — NOT NULL — true=필수, false=조건부
  • item_condition — VARCHAR(300) — 조건부일 때 조건 설명
  • display_order — INT — NOT NULL

congestion_slot

  • id — BIGINT — Primary key — NOT NULL
  • branch_id — BIGINT — Foreign key → branch.id — NOT NULL
  • day_of_week — TINYINT — NOT NULL — 1=월 ~ 7=일
  • time_slot — VARCHAR(20) — NOT NULL — 예: 10:00-11:00
  • expected_wait_minutes — INT — NOT NULL — 예상 대기시간(분)

consultation

  • id — CHAR(36) — Primary key — NOT NULL — UUID, consultationId
  • user_id — BIGINT — Foreign key → user.id — NOT NULL — v1.1: 로그인 필수
  • utterance — VARCHAR(1000) — NOT NULL — 발화 원문
  • input_method — VARCHAR(10) — NOT NULL — VOICE | TEXT
  • stt_confidence — DECIMAL(3, 2) — STT 인식 확신도 0~1
  • status — VARCHAR(30) — NOT NULL — FRAUD_WARNING | TASK_CONFIRMED | CANDIDATES_SUGGESTED | UNCLASSIFIED
  • confidence — DECIMAL(3, 2) — LLM 분류 확신도 0~1
  • task_type_code — VARCHAR(40) — Foreign key → task_type.code — 확정된 업무 (nullable)
  • created_at — DATETIME — NOT NULL

consultation_candidate

  • id — BIGINT — Primary key — NOT NULL
  • consultation_id — CHAR(36) — Foreign key → consultation.id — NOT NULL
  • task_type_code — VARCHAR(40) — Foreign key → task_type.code — NOT NULL
  • display_order — INT — NOT NULL — 1~3, 버튼 표시 순서

fraud_detection

  • id — BIGINT — Primary key — NOT NULL
  • consultation_id — CHAR(36) — Foreign key → consultation.id — NOT NULL
  • pattern_type — VARCHAR(20) — NOT NULL — IMPERSONATION | SAFE_ACCOUNT | SECRECY | REMOTE_CONTROL | URGENCY
  • evidence — VARCHAR(500) — NOT NULL — 감지된 발화 문구
  • explanation — VARCHAR(500) — NOT NULL — 쉬운 말 근거 설명

guardian

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → user.id — NOT NULL
  • name — VARCHAR(50) — NOT NULL — 자녀/보호자 이름
  • phone_number — VARCHAR(11) — NOT NULL
  • relation — VARCHAR(20) — NOT NULL — 아들 | 딸 | 배우자 | 기타
  • created_at — DATETIME — NOT NULL

guardian_notification

  • id — BIGINT — Primary key — NOT NULL
  • consultation_id — CHAR(36) — Foreign key → consultation.id — NOT NULL
  • guardian_id — BIGINT — Foreign key → guardian.id — NOT NULL
  • message — VARCHAR(300) — NOT NULL — Mock SMS 내용
  • sent_at — DATETIME — NOT NULL

recommendation

  • id — BIGINT — Primary key — NOT NULL
  • consultation_id — CHAR(36) — Foreign key → consultation.id — NOT NULL
  • branch_id — BIGINT — Foreign key → branch.id — NOT NULL
  • rank — INT — NOT NULL — 1~3
  • visit_date — DATE — NOT NULL
  • time_slot — VARCHAR(20) — NOT NULL — 예: 10:00-11:00
  • expected_wait_minutes — INT — NOT NULL
  • score — DECIMAL(5, 1) — NOT NULL — 가중 점수 0~100
  • sentence — VARCHAR(300) — NOT NULL — 자연어 추천 문장
  • created_at — DATETIME — NOT NULL

sms_verification

  • id — BIGINT — Primary key — NOT NULL
  • phone_number — VARCHAR(11) — NOT NULL
  • code — VARCHAR(6) — NOT NULL — 인증번호 (Mock 발송)
  • purpose — VARCHAR(20) — NOT NULL — LOGIN | SIGNUP
  • expires_at — DATETIME — NOT NULL — 발송 후 180초
  • verified — BOOLEAN — NOT NULL

task_type

  • code — VARCHAR(40) — Primary key — NOT NULL — 업무유형 코드 (예: PASSBOOK_REISSUE)
  • name — VARCHAR(100) — NOT NULL — 업무명
  • easy_description — VARCHAR(300) — NOT NULL — 쉬운 말 병기 설명
  • default_visit_decision — VARCHAR(20) — NOT NULL — NO_VISIT | CHECK_NEEDED | VISIT_REQUIRED

task_visit_rule

  • task_type_code — VARCHAR(40) — Foreign key → task_type.code — NOT NULL — 업무유형 코드 (1:1)
  • decision — VARCHAR(20) — NOT NULL — NO_VISIT | CHECK_NEEDED | VISIT_REQUIRED
  • reason — VARCHAR(300) — NOT NULL — 쉬운 말 사유
  • remote_methods — JSON — NO_VISIT일 때 비대면 처리 방법 목록
  • official_channels — JSON — CHECK_NEEDED일 때 공식 확인 채널 목록

user

  • id — BIGINT — Primary key — NOT NULL
  • phone_number — VARCHAR(11) — NOT NULL — 휴대폰 번호 (숫자만), 계정 식별자
  • name — VARCHAR(50) — NOT NULL — 사용자 이름
  • created_at — DATETIME — NOT NULL