56 lines
5.4 KiB
SQL
56 lines
5.4 KiB
SQL
-- AI文章内容表
|
||
-- 存储由AI生成的文章内容及其生命周期状态
|
||
-- 支持多渠道发布（百度百家号、头条、微信等）
|
||
-- 记录文章从选题、生成、审核到发布的完整流程
|
||
|
||
CREATE TABLE `ai_articles` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`batch_id` bigint UNSIGNED NOT NULL DEFAULT 0 COMMENT '批次ID，用于批量生成文章的分组',
|
||
`topic_type_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '选题类型ID',
|
||
`prompt_workflow_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '提示词工作流ID，关联AI生成模板',
|
||
`topic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文章选题/主题',
|
||
`title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文章标题',
|
||
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文章正文内容',
|
||
`department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '部门名称（遗留字段）',
|
||
`departmentids` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '部门ID列表（遗留字段）',
|
||
`author_id` int NULL DEFAULT NULL COMMENT '作者ID，关联ai_authors.id（百家号账号）',
|
||
`author_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '作者名称（百家号账号名）',
|
||
`department_id` int NULL DEFAULT NULL COMMENT '部门ID',
|
||
`department_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '部门名称',
|
||
`created_user_id` int NOT NULL DEFAULT 0 COMMENT '创建用户ID，关联ai_users.id',
|
||
`review_user_id` int NULL DEFAULT NULL COMMENT '审核用户ID，关联ai_users.id',
|
||
`publish_user_id` int NULL DEFAULT NULL COMMENT '发布用户ID，关联ai_users.id',
|
||
`status` enum('topic','cover_image','generate','generate_failed','draft','pending_review','approved','rejected','published_review','published','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'draft' COMMENT '文章状态：topic=选题|cover_image=封面图|generate=生成中|generate_failed=生成失败|draft=草稿|pending_review=待审核(文章已生成)|approved=审核通过|rejected=审核拒绝|published_review=发布审核中|published=已发布|failed=发布失败',
|
||
`channel` tinyint(1) NOT NULL DEFAULT 1 COMMENT '发布渠道：1=百度百家号|2=今日头条|3=微信公众号',
|
||
`review_comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '审核意见/备注',
|
||
`publish_time` timestamp NULL DEFAULT NULL COMMENT '发布时间',
|
||
`baijiahao_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '百家号文章ID',
|
||
`baijiahao_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '百家号平台状态',
|
||
`word_count` int NULL DEFAULT 0 COMMENT '文章字数',
|
||
`image_count` int NULL DEFAULT 0 COMMENT '文章配图数量',
|
||
`coze_tag` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'Coze生成的标签',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
|
||
-- 索引定义
|
||
INDEX `created_user_id`(`created_user_id` ASC) USING BTREE COMMENT '创建用户索引',
|
||
INDEX `review_user_id`(`review_user_id` ASC) USING BTREE COMMENT '审核用户索引',
|
||
INDEX `publish_user_id`(`publish_user_id` ASC) USING BTREE COMMENT '发布用户索引',
|
||
INDEX `idx_articles_status_user_created`(`status` ASC, `created_user_id` ASC, `created_at` DESC) USING BTREE COMMENT '状态+创建用户+创建时间组合索引',
|
||
INDEX `idx_articles_status_created`(`status` ASC, `created_at` DESC) USING BTREE COMMENT '状态+创建时间索引',
|
||
INDEX `idx_articles_status`(`status` ASC) USING BTREE COMMENT '状态索引',
|
||
INDEX `idx_articles_created_at`(`created_at` DESC) USING BTREE COMMENT '创建时间索引',
|
||
INDEX `idx_status_id_author`(`status` ASC, `id` ASC, `author_id` ASC) USING BTREE COMMENT '状态+ID+作者组合索引',
|
||
INDEX `idx_articles_updated_at`(`updated_at` DESC) USING BTREE COMMENT '更新时间索引',
|
||
INDEX `idx_articles_status_prompt_topic_id`(`status` ASC, `prompt_workflow_id` ASC, `topic` ASC, `id` ASC) USING BTREE COMMENT '状态+工作流+选题+ID组合索引',
|
||
INDEX `idx_status_author_updated_id`(`status` ASC, `author_id` ASC, `updated_at` ASC, `id` ASC) USING BTREE COMMENT '状态+作者+更新时间+ID组合索引',
|
||
INDEX `idx_author_status_updated_id`(`author_id` ASC, `status` ASC, `updated_at` ASC, `id` ASC) USING BTREE COMMENT '作者+状态+更新时间+ID组合索引',
|
||
|
||
-- 外键约束
|
||
CONSTRAINT `ai_articles_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `ai_authors` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
||
CONSTRAINT `ai_articles_ibfk_2` FOREIGN KEY (`created_user_id`) REFERENCES `ai_users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
||
CONSTRAINT `ai_articles_ibfk_3` FOREIGN KEY (`review_user_id`) REFERENCES `ai_users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
||
CONSTRAINT `ai_articles_ibfk_4` FOREIGN KEY (`publish_user_id`) REFERENCES `ai_users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1115 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
|