311 lines
16 KiB
SQL
311 lines
16 KiB
SQL
-- ============================================================
|
||
-- 智能客服系统 - 完整数据库迁移
|
||
-- 数据库：MySQL 5.7+
|
||
-- 作者：OpenClaw Team
|
||
-- 日期：2026-03-01
|
||
-- ============================================================
|
||
|
||
-- 创建数据库
|
||
CREATE DATABASE IF NOT EXISTS `openclaw`
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE `openclaw`;
|
||
|
||
-- ============================================================
|
||
-- 租户管理表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `tenants` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`name` VARCHAR(200) NOT NULL COMMENT '租户名称',
|
||
`slug` VARCHAR(100) UNIQUE NOT NULL COMMENT '租户唯一标识',
|
||
`email` VARCHAR(255) NOT NULL COMMENT '管理员邮箱',
|
||
`phone` VARCHAR(50) DEFAULT NULL COMMENT '联系电话',
|
||
`logo` VARCHAR(500) DEFAULT NULL COMMENT 'Logo URL',
|
||
`website` VARCHAR(500) DEFAULT NULL COMMENT '官方网站',
|
||
`description` TEXT DEFAULT NULL COMMENT '租户描述',
|
||
`status` ENUM('pending', 'active', 'suspended', 'archived') DEFAULT 'pending',
|
||
`plan` ENUM('free', 'basic', 'pro', 'enterprise') DEFAULT 'free',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` TIMESTAMP DEFAULT NULL,
|
||
|
||
UNIQUE KEY `uk_slug` (`slug`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='租户表';
|
||
|
||
-- ============================================================
|
||
-- 用户表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `users` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`tenant_id` BIGINT UNSIGNED NOT NULL COMMENT '所属租户 ID',
|
||
`username` VARCHAR(100) NOT NULL COMMENT '用户名',
|
||
`email` VARCHAR(255) NOT NULL COMMENT '邮箱',
|
||
`password_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希',
|
||
`full_name` VARCHAR(100) DEFAULT NULL COMMENT '全名',
|
||
`avatar` VARCHAR(500) DEFAULT NULL COMMENT '头像 URL',
|
||
`phone` VARCHAR(50) DEFAULT NULL COMMENT '手机号',
|
||
`role` ENUM('owner', 'admin', 'member', 'guest') DEFAULT 'member',
|
||
`status` ENUM('active', 'inactive', 'suspended', 'deleted') DEFAULT 'active',
|
||
`last_login_at` TIMESTAMP DEFAULT NULL,
|
||
`last_login_ip` VARCHAR(50) DEFAULT NULL COMMENT '最后登录 IP',
|
||
`remember_token` VARCHAR(100) DEFAULT NULL COMMENT '长期登录 Token',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` TIMESTAMP DEFAULT NULL,
|
||
|
||
UNIQUE KEY `uk_username` (`username`),
|
||
UNIQUE KEY `uk_email` (`email`),
|
||
KEY `idx_tenant_status` (`tenant_id`, `status`) USING BTREE,
|
||
CONSTRAINT `fk_users_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
|
||
|
||
-- ============================================================
|
||
-- 角色表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `roles` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`tenant_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '租户 ID（NULL 为全局角色）',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '角色名称',
|
||
`code` VARCHAR(100) NOT NULL COMMENT '角色代码',
|
||
`description` TEXT DEFAULT NULL COMMENT '角色描述',
|
||
`is_global` BOOLEAN DEFAULT FALSE COMMENT '是否为全局角色',
|
||
`is_system` BOOLEAN DEFAULT FALSE COMMENT '是否为系统内置角色',
|
||
`status` ENUM('active', 'inactive', 'archived') DEFAULT 'active',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` TIMESTAMP DEFAULT NULL,
|
||
|
||
UNIQUE KEY `uk_code` (`code`),
|
||
KEY `idx_tenant` (`tenant_id`),
|
||
CONSTRAINT `fk_roles_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色表';
|
||
|
||
-- 初始化系统内置角色
|
||
INSERT INTO `roles` (`name`, `code`, `description`, `is_global`, `is_system`, `status`) VALUES
|
||
('系统管理员', 'system_admin', '拥有系统全部权限', TRUE, TRUE, 'active'),
|
||
('超级管理员', 'super_admin', '租户超级管理员', FALSE, TRUE, 'active'),
|
||
('普通用户', 'user', '普通用户角色', FALSE, TRUE, 'active'),
|
||
('访客', 'guest', '访客角色（只读）', FALSE, TRUE, 'active');
|
||
|
||
-- ============================================================
|
||
-- 资源/权限表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `resources` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`tenant_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '租户 ID',
|
||
`parent_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '父资源 ID',
|
||
`name` VARCHAR(200) NOT NULL COMMENT '资源名称',
|
||
`code` VARCHAR(200) NOT NULL COMMENT '资源代码（唯一）',
|
||
`type` ENUM('api', 'page', 'button', 'data') NOT NULL COMMENT '资源类型',
|
||
`group` VARCHAR(100) DEFAULT NULL COMMENT '资源分组',
|
||
`path` VARCHAR(500) DEFAULT NULL COMMENT 'API/页面路径',
|
||
`method` VARCHAR(10) DEFAULT NULL COMMENT 'API 请求方法',
|
||
`description` TEXT DEFAULT NULL COMMENT '资源描述',
|
||
`icon` VARCHAR(100) DEFAULT NULL COMMENT '图标',
|
||
`sort_order` INT DEFAULT 0 COMMENT '排序',
|
||
`level` INT DEFAULT 0 COMMENT '层级深度',
|
||
`lft` INT DEFAULT NULL COMMENT '左值（用于树查询）',
|
||
`rgt` INT DEFAULT NULL COMMENT '右值（用于树查询）',
|
||
`is_system` BOOLEAN DEFAULT FALSE COMMENT '是否为系统资源',
|
||
`status` ENUM('active', 'inactive', 'archived') DEFAULT 'active',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` TIMESTAMP DEFAULT NULL,
|
||
|
||
UNIQUE KEY `uk_code` (`code`),
|
||
KEY `idx_tenant_parent` (`tenant_id`, `parent_id`),
|
||
KEY `idx_group` (`group`),
|
||
KEY `idx_type` (`type`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='资源/权限表';
|
||
|
||
-- 初始化系统资源（示例）
|
||
INSERT INTO `resources` (`name`, `code`, `type`, `group`, `path`, `method`, `description`, `is_system`, `status`) VALUES
|
||
('租户管理', 'tenant:manage', 'api', 'tenant', '/api/v1/tenants', '', '租户列表查询', TRUE, 'active'),
|
||
('租户创建', 'tenant:create', 'api', 'tenant', '/api/v1/tenants', 'POST', '创建租户', TRUE, 'active'),
|
||
('租户更新', 'tenant:update', 'api', 'tenant', '/api/v1/tenants/*', 'PUT', '更新租户', TRUE, 'active'),
|
||
('租户删除', 'tenant:delete', 'api', 'tenant', '/api/v1/tenants/*', 'DELETE', '删除租户', TRUE, 'active'),
|
||
('用户管理', 'user:manage', 'api', 'user', '/api/v1/users', '', '用户列表查询', TRUE, 'active'),
|
||
('用户创建', 'user:create', 'api', 'user', '/api/v1/users', 'POST', '创建用户', TRUE, 'active'),
|
||
('角色管理', 'role:manage', 'api', 'role', '/api/v1/roles', '', '角色管理', TRUE, 'active'),
|
||
('资源管理', 'resource:manage', 'api', 'resource', '/api/v1/resources', '', '资源管理', TRUE, 'active'),
|
||
('工单管理', 'ticket:manage', 'api', 'ticket', '/api/v1/tickets', '', '工单管理', TRUE, 'active'),
|
||
('会话管理', 'conversation:manage', 'api', 'conversation', '/api/v1/conversations', '', '会话管理', TRUE, 'active'),
|
||
('知识库管理', 'knowledge:manage', 'api', 'knowledge', '/api/v1/knowledge', '', '知识库管理', TRUE, 'active');
|
||
|
||
-- ============================================================
|
||
-- 角色资源关联表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `role_resources` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`role_id` BIGINT UNSIGNED NOT NULL,
|
||
`resource_id` BIGINT UNSIGNED NOT NULL,
|
||
`actions` VARCHAR(255) DEFAULT NULL COMMENT '允许的操作 (JSON)',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
UNIQUE KEY `uk_role_resource` (`role_id`, `resource_id`),
|
||
KEY `idx_resource` (`resource_id`),
|
||
CONSTRAINT `fk_role_resources_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_role_resources_resource` FOREIGN KEY (`resource_id`) REFERENCES `resources`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色资源关联表';
|
||
|
||
-- ============================================================
|
||
-- 用户角色关联表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `user_roles` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`user_id` BIGINT UNSIGNED NOT NULL,
|
||
`role_id` BIGINT UNSIGNED NOT NULL,
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
UNIQUE KEY `uk_user_role` (`user_id`, `role_id`),
|
||
KEY `idx_role` (`role_id`),
|
||
CONSTRAINT `fk_user_roles_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_user_roles_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户角色关联表';
|
||
|
||
-- ============================================================
|
||
-- 会话表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `conversations` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`tenant_id` BIGINT UNSIGNED NOT NULL COMMENT '租户 ID',
|
||
`user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '用户 ID',
|
||
`title` VARCHAR(500) DEFAULT NULL COMMENT '会话标题',
|
||
`topic` VARCHAR(200) DEFAULT NULL COMMENT '会话主题',
|
||
`status` ENUM('active', 'closed', 'archived') DEFAULT 'active',
|
||
`metadata` JSON DEFAULT NULL COMMENT '元数据',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` TIMESTAMP DEFAULT NULL,
|
||
|
||
KEY `idx_tenant` (`tenant_id`),
|
||
KEY `idx_user` (`user_id`),
|
||
CONSTRAINT `fk_conversations_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会话表';
|
||
|
||
-- ============================================================
|
||
-- 消息表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `messages` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`conversation_id` BIGINT UNSIGNED NOT NULL COMMENT '会话 ID',
|
||
`tenant_id` BIGINT UNSIGNED NOT NULL COMMENT '租户 ID',
|
||
`user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '用户 ID',
|
||
`role` ENUM('user', 'assistant', 'system') NOT NULL COMMENT '消息角色',
|
||
`content` TEXT NOT NULL COMMENT '消息内容',
|
||
`content_type` ENUM('text', 'image', 'file') DEFAULT 'text',
|
||
`content_url` VARCHAR(1000) DEFAULT NULL COMMENT '内容 URL',
|
||
`metadata` JSON DEFAULT NULL COMMENT '消息元数据',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
KEY `idx_conversation` (`conversation_id`),
|
||
KEY `idx_tenant` (`tenant_id`),
|
||
CONSTRAINT `fk_messages_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `conversations`(`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_messages_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息表';
|
||
|
||
-- ============================================================
|
||
-- 工单表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `tickets` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`ticket_number` VARCHAR(50) UNIQUE NOT NULL COMMENT '工单编号',
|
||
`tenant_id` BIGINT UNSIGNED NOT NULL COMMENT '租户 ID',
|
||
`user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '用户 ID',
|
||
`title` VARCHAR(500) NOT NULL COMMENT '工单标题',
|
||
`description` TEXT NOT NULL COMMENT '工单描述',
|
||
`category` VARCHAR(100) DEFAULT NULL COMMENT '工单分类',
|
||
`priority` ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
|
||
`status` ENUM('open', 'pending', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
|
||
`assigned_to` BIGINT UNSIGNED DEFAULT NULL COMMENT '指配给',
|
||
`due_date` DATETIME DEFAULT NULL COMMENT '截止日期',
|
||
`resolution` TEXT DEFAULT NULL COMMENT '解决方案',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` TIMESTAMP DEFAULT NULL,
|
||
|
||
KEY `idx_tenant` (`tenant_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_assigned` (`assigned_to`),
|
||
CONSTRAINT `fk_tickets_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工单表';
|
||
|
||
-- ============================================================
|
||
-- 知识库分类表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `knowledge_bases` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`tenant_id` BIGINT UNSIGNED NOT NULL COMMENT '租户 ID',
|
||
`name` VARCHAR(200) NOT NULL COMMENT '知识库名称',
|
||
`slug` VARCHAR(100) NOT NULL COMMENT '知识库唯一标识',
|
||
`description` TEXT DEFAULT NULL COMMENT '知识库描述',
|
||
`icon` VARCHAR(100) DEFAULT NULL COMMENT '图标',
|
||
`sort_order` INT DEFAULT 0 COMMENT '排序',
|
||
`status` ENUM('draft', 'published', 'archived') DEFAULT 'draft',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` TIMESTAMP DEFAULT NULL,
|
||
|
||
UNIQUE KEY `uk_slug` (`slug`),
|
||
KEY `idx_tenant` (`tenant_id`),
|
||
CONSTRAINT `fk_kb_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='知识库分类';
|
||
|
||
-- ============================================================
|
||
-- 知识库项表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `knowledge_items` (
|
||
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`knowledge_base_id` BIGINT UNSIGNED NOT NULL COMMENT '知识库 ID',
|
||
`tenant_id` BIGINT UNSIGNED NOT NULL COMMENT '租户 ID',
|
||
`title` VARCHAR(500) NOT NULL COMMENT '标题',
|
||
`slug` VARCHAR(200) NOT NULL COMMENT '唯一标识',
|
||
`content` LONGTEXT NOT NULL COMMENT '内容（Markdown）',
|
||
`summary` TEXT DEFAULT NULL COMMENT '摘要',
|
||
`tags` JSON DEFAULT NULL COMMENT '标签',
|
||
`metadata` JSON DEFAULT NULL COMMENT '元数据',
|
||
`author_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '作者 ID',
|
||
`status` ENUM('draft', 'review', 'published', 'archived') DEFAULT 'draft',
|
||
`views` INT DEFAULT 0 COMMENT '浏览次数',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` TIMESTAMP DEFAULT NULL,
|
||
|
||
KEY `idx_kb` (`knowledge_base_id`),
|
||
KEY `idx_tenant` (`tenant_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_tags` (`tags`),
|
||
CONSTRAINT `fk_ki_kb` FOREIGN KEY (`knowledge_base_id`) REFERENCES `knowledge_bases`(`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_ki_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='知识库项';
|
||
|
||
-- ============================================================
|
||
-- 全表索引优化
|
||
-- ============================================================
|
||
|
||
-- 用户角色查询优化
|
||
CREATE INDEX `idx_users_roles_composite` ON `user_roles` (`user_id`, `created_at`);
|
||
|
||
-- 消息时间查询优化
|
||
CREATE INDEX `idx_messages_created` ON `messages` (`created_at`);
|
||
|
||
-- 会话活跃状态优化
|
||
CREATE INDEX `idx_conversations_active` ON `conversations` (`status`, `created_at`);
|
||
|
||
-- 工单状态查询优化
|
||
CREATE INDEX `idx_tickets_status_priority` ON `tickets` (`status`, `priority`, `created_at`);
|
||
|
||
-- 知识库全文索引（可选）
|
||
ALTER TABLE `knowledge_items` ADD FULLTEXT INDEX `idx_content_fulltext` (`content`);
|
||
ALTER TABLE `knowledge_items` ADD FULLTEXT INDEX `idx_title_content` (`title`, `content`);
|
||
|
||
-- ============================================================
|
||
-- 完成迁移
|
||
-- ============================================================
|
||
|
||
-- 输出迁移信息
|
||
SELECT '✅ 数据库迁移完成！' AS status;
|