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'))