58 lines
3.2 KiB
SQL
58 lines
3.2 KiB
SQL
-- AI对话表
|
||
CREATE TABLE IF NOT EXISTS `ai_conversations` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
||
`tenant_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '租户ID',
|
||
`title` VARCHAR(255) NOT NULL COMMENT '对话标题',
|
||
`model` VARCHAR(100) DEFAULT NULL COMMENT 'AI模型',
|
||
`service` VARCHAR(50) DEFAULT NULL COMMENT 'AI服务提供商',
|
||
`status` VARCHAR(20) DEFAULT 'active' COMMENT '状态: active, archived, deleted',
|
||
`message_count` INT UNSIGNED DEFAULT 0 COMMENT '消息数量',
|
||
`total_tokens` BIGINT UNSIGNED DEFAULT 0 COMMENT '总Token数',
|
||
`last_message_at` DATETIME DEFAULT NULL COMMENT '最后消息时间',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT UNSIGNED DEFAULT 0 COMMENT '逻辑删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_tenant_id` (`tenant_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_last_message_at` (`last_message_at`),
|
||
KEY `idx_created_at` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI对话表';
|
||
|
||
-- AI消息表
|
||
CREATE TABLE IF NOT EXISTS `ai_messages` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`conversation_id` BIGINT UNSIGNED NOT NULL COMMENT '对话ID',
|
||
`role` VARCHAR(20) NOT NULL COMMENT '角色: user, assistant, system',
|
||
`content` TEXT NOT NULL COMMENT '消息内容',
|
||
`tokens` INT UNSIGNED DEFAULT 0 COMMENT 'Token数量',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`deleted` TINYINT UNSIGNED DEFAULT 0 COMMENT '逻辑删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_conversation_id` (`conversation_id`),
|
||
KEY `idx_created_at` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI消息表';
|
||
|
||
-- AI使用记录表（可选，用于更详细的统计）
|
||
CREATE TABLE IF NOT EXISTS `ai_usage_logs` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
||
`tenant_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '租户ID',
|
||
`service` VARCHAR(50) NOT NULL COMMENT 'AI服务',
|
||
`model` VARCHAR(100) NOT NULL COMMENT '模型',
|
||
`request_type` VARCHAR(50) NOT NULL COMMENT '请求类型: chat, task, test',
|
||
`input_tokens` INT UNSIGNED DEFAULT 0 COMMENT '输入Token数',
|
||
`output_tokens` INT UNSIGNED DEFAULT 0 COMMENT '输出Token数',
|
||
`total_tokens` INT UNSIGNED DEFAULT 0 COMMENT '总Token数',
|
||
`response_time_ms` INT UNSIGNED DEFAULT 0 COMMENT '响应时间(毫秒)',
|
||
`status` VARCHAR(20) DEFAULT 'success' COMMENT '状态: success, failed',
|
||
`error_message` TEXT DEFAULT NULL COMMENT '错误信息',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_service_model` (`service`, `model`),
|
||
KEY `idx_created_at` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI使用记录表';
|