-- ============================================================================= -- Shared Inbox / CRM de Atendimento Multiusuário -- Schema -- Motor: MySQL 5.7+ / MariaDB 10.3+ Charset: utf8mb4 -- Importe via phpMyAdmin do cPanel ou: mysql -u USER -p DBNAME < schema.sql -- ============================================================================= SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO'; -- ----------------------------------------------------------------------------- -- 1. Organizações (tenants) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS organizations ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(160) NOT NULL, slug VARCHAR(80) NOT NULL, status ENUM('active','suspended','deleted') NOT NULL DEFAULT 'active', max_users SMALLINT UNSIGNED NOT NULL DEFAULT 10, max_mailboxes SMALLINT UNSIGNED NOT NULL DEFAULT 5, timezone VARCHAR(64) NOT NULL DEFAULT 'America/Sao_Paulo', language VARCHAR(10) NOT NULL DEFAULT 'pt-BR', settings JSON NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_org_slug (slug), KEY idx_org_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 2. Usuários (super_admin global, admin e agentes por organização) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NULL, -- NULL apenas para super_admin name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('super_admin','admin','agent') NOT NULL DEFAULT 'agent', status ENUM('active','invited','disabled') NOT NULL DEFAULT 'active', avatar_color VARCHAR(9) NOT NULL DEFAULT '#4f46e5', signature TEXT NULL, timezone VARCHAR(64) NOT NULL DEFAULT 'America/Sao_Paulo', must_change_password TINYINT(1) NOT NULL DEFAULT 0, last_login_at DATETIME NULL, last_seen_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_user_email (email), KEY idx_user_org (organization_id, status), CONSTRAINT fk_user_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 3. Caixas postais (contas de e-mail corporativas do cliente) -- As senhas de SMTP/IMAP são gravadas cifradas (ver Core\Crypto). -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS mailboxes ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, email VARCHAR(190) NOT NULL, display_name VARCHAR(160) NOT NULL DEFAULT '', signature TEXT NULL, is_shared TINYINT(1) NOT NULL DEFAULT 1, status ENUM('active','paused','disabled') NOT NULL DEFAULT 'active', smtp_host VARCHAR(190) NULL, smtp_port SMALLINT UNSIGNED NOT NULL DEFAULT 587, smtp_username VARCHAR(190) NULL, smtp_password TEXT NULL, -- criptografado smtp_encryption ENUM('none','ssl','tls') NOT NULL DEFAULT 'tls', smtp_auth TINYINT(1) NOT NULL DEFAULT 1, imap_host VARCHAR(190) NULL, imap_port SMALLINT UNSIGNED NOT NULL DEFAULT 993, imap_username VARCHAR(190) NULL, imap_password TEXT NULL, -- criptografado imap_encryption ENUM('ssl','tls','none') NOT NULL DEFAULT 'ssl', imap_enabled TINYINT(1) NOT NULL DEFAULT 0, -- fallback quando pipe não existe imap_last_uid INT UNSIGNED NOT NULL DEFAULT 0, last_sync_at DATETIME NULL, last_error TEXT NULL, auto_assign ENUM('none','round_robin','least_busy') NOT NULL DEFAULT 'none', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_mailbox_email (email), KEY idx_mailbox_org (organization_id, status), CONSTRAINT fk_mailbox_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 4. Contatos (Mini-CRM) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS contacts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL DEFAULT '', email VARCHAR(190) NOT NULL, phone VARCHAR(60) NULL, company VARCHAR(160) NULL, job_title VARCHAR(120) NULL, timezone VARCHAR(64) NULL, notes TEXT NULL, custom_fields JSON NULL, conversation_count INT UNSIGNED NOT NULL DEFAULT 0, last_contacted_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_contact_email (organization_id, email), KEY idx_contact_name (organization_id, name), KEY idx_contact_company (organization_id, company), CONSTRAINT fk_contact_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 5. Etiquetas (tags) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS tags ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, name VARCHAR(60) NOT NULL, color VARCHAR(9) NOT NULL DEFAULT '#64748b', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_tag (organization_id, name), CONSTRAINT fk_tag_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 6. Conversas (threads de atendimento) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS conversations ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, mailbox_id INT UNSIGNED NOT NULL, contact_id INT UNSIGNED NULL, assignee_id INT UNSIGNED NULL, subject VARCHAR(500) NOT NULL DEFAULT '(sem assunto)', normalized_subject VARCHAR(500) NOT NULL DEFAULT '', status ENUM('open','pending','resolved','closed') NOT NULL DEFAULT 'open', priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', folder ENUM('inbox','archive','spam','trash') NOT NULL DEFAULT 'inbox', is_unread TINYINT(1) NOT NULL DEFAULT 1, is_spam TINYINT(1) NOT NULL DEFAULT 0, has_attachments TINYINT(1) NOT NULL DEFAULT 0, contains_internal TINYINT(1) NOT NULL DEFAULT 0, root_message_id VARCHAR(320) NULL, -- Message-ID da primeira mensagem thread_key VARCHAR(64) NOT NULL DEFAULT '', -- hash p/ dedupe de thread source ENUM('pipe','imap','manual','outbound') NOT NULL DEFAULT 'pipe', spam_score DECIMAL(6,2) NOT NULL DEFAULT 0, message_count INT UNSIGNED NOT NULL DEFAULT 0, inbound_count INT UNSIGNED NOT NULL DEFAULT 0, outbound_count INT UNSIGNED NOT NULL DEFAULT 0, note_count INT UNSIGNED NOT NULL DEFAULT 0, first_message_at DATETIME NULL, last_message_at DATETIME NULL, last_inbound_at DATETIME NULL, last_outbound_at DATETIME NULL, status_changed_at DATETIME NULL, resolved_at DATETIME NULL, first_response_at DATETIME NULL, response_time_secs INT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_conv_org_status (organization_id, status, last_message_at), KEY idx_conv_org_assignee (organization_id, assignee_id, status), KEY idx_conv_mailbox (mailbox_id, last_message_at), KEY idx_conv_contact (contact_id, last_message_at), KEY idx_conv_thread (thread_key), KEY idx_conv_subject (organization_id, normalized_subject(191)), FULLTEXT KEY ft_conv_subject (subject), CONSTRAINT fk_conv_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE, CONSTRAINT fk_conv_mailbox FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE, CONSTRAINT fk_conv_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL, CONSTRAINT fk_conv_assignee FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 7. Mensagens (e-mails + notas internas + eventos de sistema) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS messages ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, conversation_id BIGINT UNSIGNED NOT NULL, mailbox_id INT UNSIGNED NULL, contact_id INT UNSIGNED NULL, author_user_id INT UNSIGNED NULL, -- quando direction = outbound/internal direction ENUM('inbound','outbound','internal') NOT NULL DEFAULT 'inbound', kind ENUM('email','note','system') NOT NULL DEFAULT 'email', message_id_header VARCHAR(320) NULL, -- <...> exato como recebido/gerado in_reply_to_header VARCHAR(320) NULL, references_header TEXT NULL, from_name VARCHAR(190) NOT NULL DEFAULT '', from_email VARCHAR(190) NOT NULL DEFAULT '', to_emails JSON NULL, cc_emails JSON NULL, bcc_emails JSON NULL, reply_to VARCHAR(190) NULL, recipient_mailbox VARCHAR(190) NULL, -- conta que recebeu/receberá subject VARCHAR(500) NOT NULL DEFAULT '', body_text MEDIUMTEXT NULL, body_html MEDIUMTEXT NULL, preview VARCHAR(300) NOT NULL DEFAULT '', headers_snapshot JSON NULL, raw_size_bytes INT UNSIGNED NOT NULL DEFAULT 0, is_read TINYINT(1) NOT NULL DEFAULT 0, read_at DATETIME NULL, read_by_user_id INT UNSIGNED NULL, delivery_status ENUM('draft','queued','sending','sent','failed','received','bounced') NOT NULL DEFAULT 'received', delivery_error TEXT NULL, delivery_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, delivered_at DATETIME NULL, external_uid INT UNSIGNED NULL, -- UID IMAP (fallback) sent_at DATETIME NULL, received_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_msgid (organization_id, message_id_header), KEY idx_msg_conv (conversation_id, id), KEY idx_msg_org_created (organization_id, created_at), KEY idx_msg_status (delivery_status, created_at), KEY idx_msg_contact (contact_id, created_at), FULLTEXT KEY ft_msg_body (body_text), CONSTRAINT fk_msg_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE, CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE, CONSTRAINT fk_msg_mailbox FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE SET NULL, CONSTRAINT fk_msg_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL, CONSTRAINT fk_msg_author FOREIGN KEY (author_user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 8. Anexos (arquivos salvos fora do banco) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS attachments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, message_id BIGINT UNSIGNED NOT NULL, conversation_id BIGINT UNSIGNED NULL, filename VARCHAR(255) NOT NULL, mime_type VARCHAR(190) NOT NULL DEFAULT 'application/octet-stream', size_bytes INT UNSIGNED NOT NULL DEFAULT 0, storage_path VARCHAR(500) NOT NULL, -- relativo a STORAGE_PATH sha256 CHAR(64) NULL, content_id VARCHAR(190) NULL, -- imagens embutidas (cid:) is_inline TINYINT(1) NOT NULL DEFAULT 0, is_blocked TINYINT(1) NOT NULL DEFAULT 0, -- extensão perigosa download_count INT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_att_msg (message_id), KEY idx_att_conv (conversation_id), CONSTRAINT fk_att_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE, CONSTRAINT fk_att_msg FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 9. Relacionamento conversa <-> tags -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS conversation_tags ( conversation_id BIGINT UNSIGNED NOT NULL, tag_id INT UNSIGNED NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (conversation_id, tag_id), KEY idx_ctag_tag (tag_id), CONSTRAINT fk_ctag_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE, CONSTRAINT fk_ctag_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 10. Atividades (timeline de colaboração: atribuições, status, edições) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS activities ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, conversation_id BIGINT UNSIGNED NULL, user_id INT UNSIGNED NULL, type VARCHAR(60) NOT NULL, -- assigned, status_changed, tag_added... body VARCHAR(500) NOT NULL DEFAULT '', metadata JSON NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_act_conv (conversation_id, id), KEY idx_act_org (organization_id, created_at), CONSTRAINT fk_act_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE, CONSTRAINT fk_act_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE, CONSTRAINT fk_act_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 11. Fila de trabalhos baseada em banco (substitui Redis) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS queue_jobs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, queue VARCHAR(40) NOT NULL DEFAULT 'default', job_type VARCHAR(80) NOT NULL, payload JSON NOT NULL, status ENUM('pending','running','failed','done') NOT NULL DEFAULT 'pending', attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, max_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 5, available_at DATETIME NOT NULL, reserved_at DATETIME NULL, last_error TEXT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_job_claim (status, available_at), KEY idx_job_type (job_type, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 12. Sessões (revogação de acesso + "dispositivos conectados") -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sessions ( id CHAR(64) NOT NULL, user_id INT UNSIGNED NOT NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(255) NULL, payload TEXT NULL, last_seen_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_sess_user (user_id), KEY idx_sess_exp (expires_at), CONSTRAINT fk_sess_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------------------------------- -- 13. Configurações por organização (chave/valor) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS org_settings ( organization_id INT UNSIGNED NOT NULL, setting_key VARCHAR(80) NOT NULL, setting_value TEXT NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (organization_id, setting_key), CONSTRAINT fk_os_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1;