zicgo

Tables (29)

audit_logs

  • audit_log_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id
  • action_type — VARCHAR(50) — NOT NULL
  • target_type — VARCHAR(50) — NOT NULL
  • target_id — BIGINT
  • result_status — VARCHAR(20) — NOT NULL
  • ip_address — VARCHAR(45)
  • user_agent — VARCHAR(500)
  • detail_json — JSON
  • created_at — DATETIME — NOT NULL
  • CHECK — (result_status IN ('success', 'failed'))

auto_classification_rules

  • rule_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • rule_name — VARCHAR(100) — NOT NULL
  • channel_type — VARCHAR(10) — NOT NULL
  • match_type — VARCHAR(20) — NOT NULL
  • match_value — VARCHAR(255) — NOT NULL
  • target_folder_id — BIGINT — Foreign key → inbox_folders.inbox_folder_id — NOT NULL
  • assigned_user_id — BIGINT — Foreign key → users.user_id
  • priority — INT — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • CHECK — (channel_type IN ('sms', 'fax', 'all'))
  • CHECK — (match_type IN ('sender_number', 'receive_number', 'keyword'))

blocked_numbers

  • blocked_number_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • channel_type — VARCHAR(10) — NOT NULL
  • phone_number — VARCHAR(30) — NOT NULL
  • reason — VARCHAR(255)
  • created_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (channel_type IN ('sms', 'fax', 'all'))

companies

  • company_id — BIGINT
  • company_name — VARCHAR(100) — NOT NULL
  • business_no — VARCHAR(20)
  • status — VARCHAR(20) — NOT NULL
  • free_point — BIGINT — NOT NULL
  • paid_point — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (status IN ('active', 'suspended', 'deleted'))
  • CHECK — (free_point >= 0)
  • CHECK — (paid_point >= 0)

contact_group_members

  • contact_group_id — BIGINT — Foreign key → contact_groups.contact_group_id — NOT NULL
  • contact_id — BIGINT — Foreign key → contacts.contact_id — NOT NULL
  • created_at — DATETIME — NOT NULL

contact_groups

  • contact_group_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.user_id
  • group_name — VARCHAR(100) — NOT NULL
  • is_default — TINYINT(1) — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id — 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
  • owner_user_id — BIGINT — Foreign key → users.user_id
  • contact_name — VARCHAR(80) — NOT NULL
  • company_name — VARCHAR(100)
  • mobile_no — VARCHAR(30)
  • fax_no — 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_no IS NOT NULL OR fax_no IS NOT NULL OR email IS NOT NULL) — NOT NULL

export_jobs

  • export_job_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • requested_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • export_type — VARCHAR(30) — NOT NULL
  • job_status — VARCHAR(20) — NOT NULL
  • search_condition_json — JSON
  • result_file_id — BIGINT — Foreign key → file_uploads.file_id
  • created_at — DATETIME — NOT NULL
  • completed_at — DATETIME
  • CHECK — (export_type IN ('contacts', 'sms_results', 'fax_results', 'inbox_items'))
  • CHECK — (job_status IN ('pending', 'processing', 'completed', 'failed'))

faqs

  • faq_id — BIGINT
  • category — VARCHAR(50) — NOT NULL
  • question — VARCHAR(300) — NOT NULL
  • answer — TEXT — NOT NULL
  • sort_order — INT — NOT NULL
  • is_published — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

fax_attachments

  • fax_attachment_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • fax_message_id — BIGINT — Foreign key → fax_messages.fax_message_id — NOT NULL
  • file_id — BIGINT — Foreign key → file_uploads.file_id — NOT NULL
  • page_count — INT
  • sort_order — INT — NOT NULL
  • conversion_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • CHECK — (conversion_status IN ('pending', 'converted', 'failed'))

fax_messages

  • fax_message_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • created_by — 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_type — VARCHAR(20) — NOT NULL
  • request_status — VARCHAR(20) — NOT NULL
  • title — VARCHAR(200)
  • cover_message — TEXT
  • reserved_at — DATETIME
  • requested_at — DATETIME
  • completed_at — DATETIME
  • total_count — INT — NOT NULL
  • success_count — INT — NOT NULL
  • fail_count — INT — NOT NULL
  • import_job_id — BIGINT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (fax_type IN ('normal', 'bulk', 'ad'))
  • CHECK — (send_type IN ('immediate', 'reserved'))
  • CHECK — (request_status IN ('draft', 'requested', 'queued', 'sending', 'completed', 'failed', 'cancelled'))

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)
  • can_send — TINYINT(1) — NOT NULL
  • can_receive — TINYINT(1) — NOT NULL
  • verification_status — VARCHAR(20) — NOT NULL
  • is_default — TINYINT(1) — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (verification_status IN ('pending', 'verified', 'rejected', 'disabled'))

fax_recipients

  • fax_recipient_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • fax_message_id — BIGINT — Foreign key → fax_messages.fax_message_id — NOT NULL
  • contact_id — BIGINT — Foreign key → contacts.contact_id
  • recipient_name — VARCHAR(80)
  • recipient_company_name — VARCHAR(100)
  • fax_no — VARCHAR(30) — NOT NULL
  • validation_status — VARCHAR(20) — NOT NULL
  • validation_message — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • CHECK — (validation_status IN ('valid', 'invalid', 'duplicate', 'blocked'))

fax_send_results

  • fax_result_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • fax_recipient_id — BIGINT — Foreign key → fax_recipients.fax_recipient_id — NOT NULL
  • external_request_id — VARCHAR(100)
  • external_fax_id — VARCHAR(100)
  • result_status — VARCHAR(20) — NOT NULL
  • result_code — VARCHAR(50)
  • result_message — VARCHAR(255)
  • sent_page_count — INT
  • requested_at — DATETIME
  • sent_at — DATETIME
  • completed_at — DATETIME
  • updated_at — DATETIME — NOT NULL
  • CHECK — (result_status IN ('pending', 'queued', 'sending', 'success', '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_category — VARCHAR(30) — NOT NULL
  • original_file_name — VARCHAR(255) — NOT NULL
  • stored_file_name — VARCHAR(255) — NOT NULL
  • storage_path — VARCHAR(500) — NOT NULL
  • mime_type — VARCHAR(100)
  • file_size — BIGINT — NOT NULL
  • checksum — VARCHAR(128)
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (file_category IN ('fax_attachment', 'sms_import', 'contact_import', 'inbox_preview', 'export', 'support'))
  • CHECK — (status IN ('uploaded', 'processing', 'ready', 'failed', 'deleted'))

import_jobs

  • import_job_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • requested_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • file_id — BIGINT — Foreign key → file_uploads.file_id — NOT NULL
  • import_type — VARCHAR(30) — NOT NULL
  • job_status — VARCHAR(20) — NOT NULL
  • total_count — INT — NOT NULL
  • success_count — INT — NOT NULL
  • fail_count — INT — NOT NULL
  • error_file_id — BIGINT — Foreign key → file_uploads.file_id
  • created_at — DATETIME — NOT NULL
  • completed_at — DATETIME
  • CHECK — (import_type IN ('contacts', 'sms_recipients', 'fax_recipients'))
  • CHECK — (job_status IN ('pending', 'processing', 'completed', 'failed'))

inbox_folders

  • inbox_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_system_folder — TINYINT(1) — NOT NULL
  • created_by — BIGINT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (folder_type IN ('inbox', 'shared', 'user', 'trash'))

inbox_items

  • inbox_item_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • inbox_folder_id — BIGINT — Foreign key → inbox_folders.inbox_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
  • preview_file_id — BIGINT — Foreign key → file_uploads.file_id
  • assigned_user_id — BIGINT — Foreign key → users.user_id
  • process_status — VARCHAR(20) — NOT NULL
  • page_count — INT
  • is_read — TINYINT(1) — NOT NULL
  • trashed_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • FULLTEXT — KEY
  • CHECK — (channel_type IN ('sms', 'fax'))
  • CHECK — (process_status IN ('new', 'in_progress', 'done', 'archived', 'deleted'))

permissions

  • permission_id — BIGINT
  • permission_code — VARCHAR(100) — NOT NULL
  • menu_code — VARCHAR(50) — NOT NULL
  • action_code — VARCHAR(30) — NOT NULL
  • description — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • CHECK — (action_code IN ('create', 'read', 'update', 'delete', 'download', 'send', 'manage'))

role_permissions

  • role_id — BIGINT — Foreign key → roles.role_id — NOT NULL
  • permission_id — BIGINT — Foreign key → permissions.permission_id — NOT NULL
  • created_at — DATETIME — NOT NULL

roles

  • role_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id
  • role_code — VARCHAR(50) — NOT NULL
  • role_name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(255)
  • is_system_role — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • CHECK — (role_code IN ('admin', 'user', 'viewer')

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)
  • verification_status — VARCHAR(20) — NOT NULL
  • is_default — TINYINT(1) — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (verification_status IN ('pending', 'verified', 'rejected', 'disabled'))

sms_messages

  • sms_message_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • sender_number_id — BIGINT — Foreign key → sender_numbers.sender_number_id — NOT NULL
  • message_title — VARCHAR(200)
  • message_body — TEXT — NOT NULL
  • sms_type — VARCHAR(20) — NOT NULL
  • send_type — VARCHAR(20) — NOT NULL
  • request_status — VARCHAR(20) — NOT NULL
  • reserved_at — DATETIME
  • requested_at — DATETIME
  • completed_at — DATETIME
  • is_advertisement — TINYINT(1) — NOT NULL
  • unsubscribe_text — VARCHAR(255)
  • expected_point — BIGINT — NOT NULL
  • total_count — INT — NOT NULL
  • success_count — INT — NOT NULL
  • fail_count — INT — NOT NULL
  • import_job_id — BIGINT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (sms_type IN ('sms', 'lms', 'mms', 'bulk', 'ad'))
  • CHECK — (send_type IN ('immediate', 'reserved'))
  • CHECK — (request_status IN ('draft', 'requested', 'queued', 'sending', 'completed', 'failed', 'cancelled'))
  • CHECK — (expected_point >= 0)
  • CHECK — (total_count >= 0 AND success_count >= 0 AND fail_count >= 0)

sms_recipients

  • sms_recipient_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • sms_message_id — BIGINT — Foreign key → sms_messages.sms_message_id — NOT NULL
  • contact_id — BIGINT — Foreign key → contacts.contact_id
  • recipient_name — VARCHAR(80)
  • recipient_company_name — VARCHAR(100)
  • mobile_no — VARCHAR(30) — NOT NULL
  • personalized_body — TEXT
  • validation_status — VARCHAR(20) — NOT NULL
  • validation_message — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • CHECK — (validation_status IN ('valid', 'invalid', 'duplicate', 'blocked'))

sms_send_results

  • sms_result_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • sms_recipient_id — BIGINT — Foreign key → sms_recipients.sms_recipient_id — NOT NULL
  • external_request_id — VARCHAR(100)
  • external_message_id — VARCHAR(100)
  • result_status — VARCHAR(20) — NOT NULL
  • result_code — VARCHAR(50)
  • result_message — VARCHAR(255)
  • requested_at — DATETIME
  • sent_at — DATETIME
  • delivered_at — DATETIME
  • updated_at — DATETIME — NOT NULL
  • CHECK — (result_status IN ('pending', 'queued', 'sending', 'success', 'failed', 'cancelled'))

support_notices

  • notice_id — BIGINT
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • is_pinned — TINYINT(1) — NOT NULL
  • is_published — TINYINT(1) — NOT NULL
  • created_by — BIGINT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_access_scope

  • access_scope_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • scope_type — VARCHAR(20) — NOT NULL
  • target_id — BIGINT — NOT NULL
  • can_send — TINYINT(1) — NOT NULL
  • can_receive — TINYINT(1) — NOT NULL
  • can_view_result — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • CHECK — (scope_type IN ('sms_sender_number', 'fax_number'))

user_menu_permissions

  • user_menu_permission_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • menu_code — VARCHAR(50) — NOT NULL
  • can_access — TINYINT(1) — NOT NULL
  • can_download — TINYINT(1) — NOT NULL
  • can_manage — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

users

  • user_id — BIGINT
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • role_id — BIGINT — Foreign key → roles.role_id — NOT NULL
  • login_id — VARCHAR(80) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • user_name — VARCHAR(80) — NOT NULL
  • email — VARCHAR(150)
  • mobile_no — VARCHAR(30)
  • status — VARCHAR(20) — NOT NULL
  • last_login_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • CHECK — (status IN ('active', 'locked', 'inactive', 'deleted'))