민원처리 웹 사이트

테이블 (15)

anomaly_alert

  • alert_id — BIGINT — 기본 키 — NOT NULL
  • alert_type — VARCHAR(50) — NOT NULL
  • severity — VARCHAR(10) — NOT NULL
  • triggered_at — TIMESTAMP — NOT NULL
  • is_acknowledged — BOOLEAN — NOT NULL
  • cluster_id (cluster_id2) — BIGINT — 외래 키 → complaint_cluster.cluster_id — NOT NULL

attachment

  • attachment_id — BIGINT — 기본 키 — NOT NULL
  • complaint_id — BIGINT — NOT NULL
  • file_path — VARCHAR(500) — NOT NULL
  • file_type — VARCHAR(50)
  • uploaded_at — TIMESTAMP — NOT NULL

blacklist (Untitled)

  • blacklist_id — bigint — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • reason — bigint
  • blacklisted_at — tmestamp
  • expires_at — timestamp

category

  • category_id — INT — 기본 키 — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • template_title — VARCHAR(255)
  • template_body — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

chat

  • chat_id — BIGINT — 기본 키 — NOT NULL
  • complaint_id — BIGINT — NOT NULL
  • sender — VARCHAR(10) — NOT NULL
  • message — TEXT — NOT NULL
  • is_read — BOOLEAN — NOT NULL
  • sent_at — TIMESTAMP — NOT NULL
  • complaint_id (complaint_id2) — BIGINT — 외래 키 → complaint.complaint_id — NOT NULL

cluster_member

  • cluster_id — BIGINT — 기본 키 — NOT NULL
  • complaint_id — BIGINT — 기본 키 — NOT NULL
  • added_at — TIMESTAMP — NOT NULL
  • cluster_id (cluster_id2) — BIGINT — 외래 키 → complaint_cluster.cluster_id — NOT NULL

complaint

  • complaint_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — NOT NULL
  • category_id — INT — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • gps_lat — DECIMAL(10,7)
  • gps_lng — DECIMAL(10,7)
  • address_city — VARCHAR(50)
  • address_district — VARCHAR(50)
  • address_neighborhood — VARCHAR(50)
  • is_offensive — BOOLEAN — NOT NULL
  • is_spam — BOOLEAN — NOT NULL
  • priority_tag — VARCHAR(50)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • category_id (category_id2) — INT — 외래 키 → category.category_id — NOT NULL

complaint_cluster

  • cluster_id — BIGINT — 기본 키 — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • method — VARCHAR(100)
  • created_at — TIMESTAMP — NOT NULL

complaint_tag

  • complaint_id — BIGINT — 기본 키 — NOT NULL
  • tag_id — INT — 외래 키 → tag.tag_id — NOT NULL
  • assigned_at — TIMESTAMP — NOT NULL
  • attachment_id — BIGINT — 외래 키 → attachment.attachment_id — NOT NULL

faq

  • faq_id — INT — 기본 키 — NOT NULL
  • question — VARCHAR(500) — NOT NULL
  • answer — TEXT — NOT NULL
  • view_count — INT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

notice

  • notice_id — INT — 기본 키 — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • body — TEXT — NOT NULL
  • author_id — BIGINT — NOT NULL
  • published_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • is_visible — BOOLEAN — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL

notification

  • notification_id — BIGINT — 기본 키 — NOT NULL
  • complaint_id — BIGINT
  • type — VARCHAR(10) — NOT NULL
  • status — VARCHAR(10) — NOT NULL
  • sent_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL

process_log

  • log_id — BIGINT — 기본 키 — NOT NULL
  • complaint_id — BIGINT — NOT NULL
  • admin_id — BIGINT — NOT NULL
  • action — VARCHAR(20) — NOT NULL
  • comment — TEXT — EX
  • prev_status — VARCHAR(20)
  • new_status — VARCHAR(20)
  • logged_at — TIMESTAMP — NOT NULL
  • complaint_id (complaint_id2) — BIGINT — 외래 키 → complaint.complaint_id — NOT NULL

tag

  • tag_id — INT — 기본 키 — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

users

  • user_id — BIGINT — 기본 키 — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • password_salt — VARCHAR(32) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • address — VARCHAR(500)
  • role — VARCHAR(10) — NOT NULL
  • captcha_fail_cnt — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • blacklist_id — bigint — 외래 키 → blacklist.blacklist_id — NOT NULL
  • user_id (user_id2) — BIGINT — 외래 키 → blacklist.user_id — NOT NULL
  • log_id — BIGINT — 외래 키 → process_log.log_id — NOT NULL