zicgo_

Tables (15)

address_book_jobs

  • job_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • requested_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • job_type — VARCHAR(20) — NOT NULL
  • file_id — BIGINT — Foreign key → file_uploads.file_id
  • job_status — VARCHAR(20) — NOT NULL
  • total_count — INT — NOT NULL
  • success_count — INT — NOT NULL
  • fail_count — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • completed_at — DATETIME
  • CHECK — (job_type IN ('IMPORT', 'EXPORT'))
  • CHECK — (job_status IN ('READY', 'PROCESSING', 'COMPLETED', 'FAILED'))

companies

  • company_id — BIGINT
  • company_name — VARCHAR(100) — NOT NULL
  • business_no — VARCHAR(20) — 추가 정의 필요: 사업자번호 검증 규칙
  • free_point — INT — NOT NULL
  • paid_point — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (status IN ('ACTIVE', 'SUSPENDED', 'DELETED'))

contact_groups

  • contact_group_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • group_name — VARCHAR(100) — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.user_id
  • default_yn — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

contacts

  • contact_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • contact_group_id — BIGINT — Foreign key → contact_groups.contact_group_id
  • contact_name — VARCHAR(80) — NOT NULL
  • company_name — VARCHAR(100)
  • mobile_number — VARCHAR(30)
  • fax_number — VARCHAR(30)
  • email — VARCHAR(150)
  • memo — VARCHAR(500)
  • created_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (mobile_number IS NOT NULL OR fax_number IS NOT NULL) — NOT NULL

fax_numbers

  • fax_number_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • fax_number — VARCHAR(30) — NOT NULL
  • display_name — VARCHAR(100)
  • send_yn — TINYINT(1) — NOT NULL
  • receive_yn — TINYINT(1) — NOT NULL
  • default_yn — TINYINT(1) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • CHECK — (status IN ('ACTIVE', 'INACTIVE', 'DELETED'))

fax_recipients

  • fax_recipient_id — BIGINT
  • fax_send_id — BIGINT — Foreign key → fax_sends.fax_send_id — NOT NULL
  • recipient_name — VARCHAR(80)
  • company_name — VARCHAR(100)
  • fax_number — VARCHAR(30) — NOT NULL
  • result_status — VARCHAR(20) — NOT NULL
  • result_code — VARCHAR(50) — 추가 정의 필요: 외부 팩스 API 결과 코드
  • result_message — VARCHAR(255)
  • external_fax_id — VARCHAR(100)
  • sent_at — DATETIME
  • completed_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • CHECK — (result_status IN ('WAITING', 'SENDING', 'SUCCESS', 'FAIL', 'CANCELLED'))

fax_send_files

  • fax_send_file_id — BIGINT
  • fax_send_id — BIGINT — Foreign key → fax_sends.fax_send_id — NOT NULL
  • file_id — BIGINT — Foreign key → file_uploads.file_id — NOT NULL
  • page_count — INT — 추가 정의 필요: 문서 변환 후 페이지 수
  • sort_order — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

fax_sends

  • fax_send_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • sender_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • fax_number_id — BIGINT — Foreign key → fax_numbers.fax_number_id — NOT NULL
  • fax_type — VARCHAR(20) — NOT NULL
  • send_method — VARCHAR(20) — NOT NULL
  • title — VARCHAR(200)
  • request_status — VARCHAR(20) — NOT NULL
  • reserved_at — DATETIME
  • requested_at — DATETIME
  • completed_at — DATETIME
  • total_count — INT — NOT NULL
  • success_count — INT — NOT NULL
  • fail_count — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (fax_type IN ('NORMAL', 'BULK', 'AD'))
  • CHECK — (send_method IN ('IMMEDIATE', 'RESERVED'))
  • CHECK — (request_status IN ('READY', 'REQUESTED', 'SENDING', 'COMPLETED', 'FAILED', 'CANCELLED'))

file_uploads

  • file_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • uploaded_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • file_type — VARCHAR(30) — NOT NULL
  • original_name — VARCHAR(255) — NOT NULL
  • stored_name — VARCHAR(255) — NOT NULL
  • storage_path — VARCHAR(500) — NOT NULL — 추가 정의 필요: 파일 저장소 경로 정책
  • mime_type — VARCHAR(100)
  • file_size — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (file_type IN ('FAX_ATTACHMENT', 'SMS_EXCEL', 'ADDRESS_EXCEL', 'INBOX_FILE', 'EXPORT_FILE'))

inbox_folders

  • folder_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.user_id
  • folder_type — VARCHAR(20) — NOT NULL
  • folder_name — VARCHAR(100) — NOT NULL
  • is_default — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • CHECK — (folder_type IN ('INBOX', 'SHARED', 'USER', 'TRASH'))

inbox_items

  • inbox_item_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • folder_id — BIGINT — Foreign key → inbox_folders.folder_id — NOT NULL
  • channel_type — VARCHAR(10) — NOT NULL
  • received_at — DATETIME — NOT NULL
  • receive_number — VARCHAR(30) — NOT NULL
  • sender_number — VARCHAR(30)
  • sender_name — VARCHAR(100)
  • title — VARCHAR(200)
  • content_text — TEXT — 문자 본문 또는 팩스 OCR 텍스트. 추가 정의 필요: OCR 적용 범위
  • file_id — BIGINT — Foreign key → file_uploads.file_id
  • assigned_user_id — BIGINT — Foreign key → users.user_id
  • read_yn — TINYINT(1) — NOT NULL
  • process_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • FULLTEXT — KEY
  • CHECK — (channel_type IN ('SMS', 'FAX'))
  • CHECK — (process_status IN ('NEW', 'CHECKED', 'PROCESSING', 'DONE', 'DELETED'))

sender_numbers

  • sender_number_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • sender_number — VARCHAR(30) — NOT NULL
  • display_name — VARCHAR(100)
  • default_yn — TINYINT(1) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • CHECK — (status IN ('ACTIVE', 'PENDING', 'REJECTED', 'INACTIVE', 'DELETED'))

sms_recipients

  • sms_recipient_id — BIGINT
  • sms_send_id — BIGINT — Foreign key → sms_sends.sms_send_id — NOT NULL
  • recipient_name — VARCHAR(80)
  • company_name — VARCHAR(100)
  • mobile_number — VARCHAR(30) — NOT NULL
  • individual_message — TEXT — 번호별 개별문자 발송 시 사용
  • result_status — VARCHAR(20) — NOT NULL
  • result_code — VARCHAR(50) — 추가 정의 필요: 외부 문자 API 결과 코드
  • result_message — VARCHAR(255)
  • external_message_id — VARCHAR(100)
  • sent_at — DATETIME
  • completed_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • CHECK — (result_status IN ('WAITING', 'SENDING', 'SUCCESS', 'FAIL', 'CANCELLED'))

sms_sends

  • sms_send_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • sender_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • sender_number_id — BIGINT — Foreign key → sender_numbers.sender_number_id — NOT NULL
  • sms_type — VARCHAR(20) — NOT NULL
  • send_method — VARCHAR(20) — NOT NULL
  • title — VARCHAR(200)
  • message_body — TEXT — NOT NULL
  • ad_yn — TINYINT(1) — NOT NULL
  • unsubscribe_text — VARCHAR(255)
  • excel_file_id — BIGINT — Foreign key → file_uploads.file_id
  • request_status — VARCHAR(20) — NOT NULL
  • reserved_at — DATETIME
  • requested_at — DATETIME
  • completed_at — DATETIME
  • total_count — INT — NOT NULL
  • success_count — INT — NOT NULL
  • fail_count — INT — NOT NULL
  • expected_point — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (sms_type IN ('NORMAL', 'BULK_EXCEL', 'AD'))
  • CHECK — (send_method IN ('IMMEDIATE', 'RESERVED'))
  • CHECK — (request_status IN ('READY', 'REQUESTED', 'SENDING', 'COMPLETED', 'FAILED', 'CANCELLED'))

users

  • user_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • login_id — VARCHAR(80) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL — 추가 정의 필요: 비밀번호 해시 알고리즘
  • user_name — VARCHAR(80) — NOT NULL
  • email — VARCHAR(150)
  • phone_number — VARCHAR(30)
  • role_type — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (role_type IN ('ADMIN', 'USER', 'VIEWER'))
  • CHECK — (status IN ('ACTIVE', 'LOCKED', 'DELETED'))