157 lines
10 KiB
SQL
157 lines
10 KiB
SQL
-- 表1：用户快照表
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` INT NOT NULL COMMENT 'NewAPI 用户 ID，作为本站用户主键',
|
|
`username` VARCHAR(191) NOT NULL DEFAULT '' COMMENT '用户名快照',
|
|
`display_name` VARCHAR(191) NOT NULL DEFAULT '' COMMENT '展示名称快照',
|
|
`group` VARCHAR(191) NOT NULL DEFAULT '' COMMENT '用户分组快照',
|
|
`role` INT NOT NULL DEFAULT 0 COMMENT '用户角色快照',
|
|
`status` INT NOT NULL DEFAULT 0 COMMENT '用户状态快照',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户快照表';
|
|
|
|
-- 表2：生图批次记录表
|
|
CREATE TABLE IF NOT EXISTS `image_generations` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '生图批次记录 ID',
|
|
`user_id` INT NOT NULL COMMENT 'NewAPI 用户 ID',
|
|
`prompt` TEXT NOT NULL COMMENT '用户输入的生图提示词',
|
|
`status` ENUM('QUEUED', 'RUNNING', 'SUCCEEDED', 'FAILED') NOT NULL DEFAULT 'RUNNING' COMMENT '生图批次状态',
|
|
`model` VARCHAR(64) NOT NULL DEFAULT 'gpt-image-2' COMMENT '生图模型',
|
|
`batch_size` INT NOT NULL DEFAULT 1 COMMENT '本批次请求的图片数量',
|
|
`success_count` INT NOT NULL DEFAULT 0 COMMENT '本批次成功生成的图片数量',
|
|
`failed_count` INT NOT NULL DEFAULT 0 COMMENT '本批次生成失败的图片数量',
|
|
`started_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '生图开始时间',
|
|
`ended_at` DATETIME(3) NULL COMMENT '生图结束时间',
|
|
`duration_ms` INT NULL COMMENT '生图耗时，单位毫秒',
|
|
`error_message` TEXT NULL COMMENT '批次失败原因或本地泛化错误提示',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
`deleted_at` DATETIME(3) NULL COMMENT '软删除时间',
|
|
PRIMARY KEY (`id`),
|
|
INDEX `image_generations_user_id_created_at_idx` (`user_id`, `created_at`),
|
|
INDEX `image_generations_status_idx` (`status`),
|
|
INDEX `image_generations_deleted_at_idx` (`deleted_at`),
|
|
CONSTRAINT `image_generations_user_id_fkey`
|
|
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='生图批次记录表';
|
|
|
|
-- 表3：生图图片子记录表
|
|
CREATE TABLE IF NOT EXISTS `image_generation_images` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '生图图片记录 ID',
|
|
`image_generation_id` BIGINT UNSIGNED NOT NULL COMMENT '所属生图批次 ID',
|
|
`user_id` INT NOT NULL COMMENT 'NewAPI 用户 ID',
|
|
`image_index` INT NOT NULL COMMENT '图片在批次中的顺序，从 0 开始',
|
|
`status` ENUM('QUEUED', 'RUNNING', 'SUCCEEDED', 'FAILED') NOT NULL DEFAULT 'RUNNING' COMMENT '单张图片生成状态',
|
|
`image_url` VARCHAR(2048) NULL COMMENT '生成图片访问地址',
|
|
`hosted_image_url` VARCHAR(2048) NULL COMMENT '图床归档后的稳定图片地址',
|
|
`image_mime_type` VARCHAR(191) NULL COMMENT '图片 MIME 类型',
|
|
`revised_prompt` TEXT NULL COMMENT '修订提示词',
|
|
`upstream_response` JSON NULL COMMENT '完整上游生图响应，仅服务端排查使用',
|
|
`hosted_response` JSON NULL COMMENT '完整图床归档响应，仅服务端排查使用',
|
|
`error_message` TEXT NULL COMMENT '失败原因或图床归档失败提示',
|
|
`archive_status` ENUM('NOT_REQUIRED', 'PENDING', 'RUNNING', 'SUCCEEDED', 'FAILED') NOT NULL DEFAULT 'NOT_REQUIRED' COMMENT '图床归档任务状态',
|
|
`archive_attempts` INT NOT NULL DEFAULT 0 COMMENT '图床归档已尝试次数',
|
|
`archive_next_run_at` DATETIME(3) NULL COMMENT '图床归档下次可执行时间',
|
|
`archive_locked_at` DATETIME(3) NULL COMMENT '图床归档任务被 worker 锁定的时间',
|
|
`archive_locked_by` VARCHAR(191) NULL COMMENT '图床归档任务 worker 标识',
|
|
`archive_last_error` TEXT NULL COMMENT '图床归档最近一次失败摘要',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `image_generation_images_image_generation_id_image_index_key` (`image_generation_id`, `image_index`),
|
|
INDEX `image_generation_images_image_generation_id_idx` (`image_generation_id`),
|
|
INDEX `image_generation_images_user_id_created_at_idx` (`user_id`, `created_at`),
|
|
INDEX `image_generation_images_status_idx` (`status`),
|
|
INDEX `image_generation_images_archive_status_archive_next_run_at_idx` (`archive_status`, `archive_next_run_at`),
|
|
INDEX `image_generation_images_archive_locked_at_idx` (`archive_locked_at`),
|
|
CONSTRAINT `image_generation_images_image_generation_id_fkey`
|
|
FOREIGN KEY (`image_generation_id`) REFERENCES `image_generations` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `image_generation_images_user_id_fkey`
|
|
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='生图图片子记录表';
|
|
|
|
-- 表4：广场发布表
|
|
CREATE TABLE IF NOT EXISTS `plaza_posts` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '广场发布记录 ID',
|
|
`image_generation_id` BIGINT UNSIGNED NOT NULL COMMENT '关联的生图批次记录 ID',
|
|
`cover_image_id` BIGINT UNSIGNED NOT NULL COMMENT '广场封面图片 ID',
|
|
`user_id` INT NOT NULL COMMENT 'NewAPI 用户 ID',
|
|
`status` ENUM('PUBLIC', 'HIDDEN', 'REMOVED') NOT NULL DEFAULT 'PUBLIC' COMMENT '广场发布状态',
|
|
`published_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '发布时间',
|
|
`hidden_at` DATETIME(3) NULL COMMENT '用户取消公开时间',
|
|
`removed_at` DATETIME(3) NULL COMMENT '管理侧下架时间',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `plaza_posts_image_generation_id_key` (`image_generation_id`),
|
|
INDEX `plaza_posts_status_published_at_idx` (`status`, `published_at`),
|
|
INDEX `plaza_posts_user_id_published_at_idx` (`user_id`, `published_at`),
|
|
INDEX `plaza_posts_cover_image_id_idx` (`cover_image_id`),
|
|
CONSTRAINT `plaza_posts_image_generation_id_fkey`
|
|
FOREIGN KEY (`image_generation_id`) REFERENCES `image_generations` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `plaza_posts_cover_image_id_fkey`
|
|
FOREIGN KEY (`cover_image_id`) REFERENCES `image_generation_images` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `plaza_posts_user_id_fkey`
|
|
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='广场发布表';
|
|
|
|
-- 表5：广场公开图片关联表
|
|
CREATE TABLE IF NOT EXISTS `plaza_post_images` (
|
|
`plaza_post_id` BIGINT UNSIGNED NOT NULL COMMENT '广场发布记录 ID',
|
|
`image_generation_image_id` BIGINT UNSIGNED NOT NULL COMMENT '公开的生图图片记录 ID',
|
|
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '展示顺序',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
PRIMARY KEY (`plaza_post_id`, `image_generation_image_id`),
|
|
INDEX `plaza_post_images_image_generation_image_id_idx` (`image_generation_image_id`),
|
|
CONSTRAINT `plaza_post_images_plaza_post_id_fkey`
|
|
FOREIGN KEY (`plaza_post_id`) REFERENCES `plaza_posts` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `plaza_post_images_image_generation_image_id_fkey`
|
|
FOREIGN KEY (`image_generation_image_id`) REFERENCES `image_generation_images` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='广场公开图片关联表';
|
|
|
|
-- 表6：预设提示词表
|
|
CREATE TABLE IF NOT EXISTS `prompt_presets` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '预设提示词记录 ID',
|
|
`source_id` VARCHAR(191) NOT NULL COMMENT '来源 JSON 中的稳定 ID',
|
|
`name` VARCHAR(191) NOT NULL COMMENT '预设名称',
|
|
`prompt_raw` TEXT NOT NULL COMMENT '原始提示词',
|
|
`prompt` TEXT NOT NULL COMMENT '清洗后的提示词',
|
|
`negative_prompt` TEXT NOT NULL COMMENT '反向提示词',
|
|
`category` VARCHAR(191) NOT NULL DEFAULT '' COMMENT '分类名称',
|
|
`tags` JSON NOT NULL COMMENT '标签数组',
|
|
`aspect_ratio` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '建议图片比例',
|
|
`preview_url` VARCHAR(2048) NOT NULL DEFAULT '' COMMENT '预览图地址',
|
|
`compressed_preview_url` VARCHAR(2048) NOT NULL DEFAULT '' COMMENT '压缩预览图地址',
|
|
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '排序值，默认按 JSON 数组顺序',
|
|
`enabled` BOOLEAN NOT NULL DEFAULT true COMMENT '是否启用',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `prompt_presets_source_id_key` (`source_id`),
|
|
INDEX `prompt_presets_category_sort_order_idx` (`category`, `sort_order`),
|
|
INDEX `prompt_presets_enabled_sort_order_idx` (`enabled`, `sort_order`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='预设提示词表';
|
|
|
|
-- 表7：生图统计表
|
|
CREATE TABLE IF NOT EXISTS `generation_stats` (
|
|
`id` VARCHAR(32) NOT NULL COMMENT '统计行 ID，固定为 global',
|
|
`total_requests` INT NOT NULL DEFAULT 0 COMMENT '总生图请求数，包含成功和失败',
|
|
`success_requests` INT NOT NULL DEFAULT 0 COMMENT '成功生图请求数',
|
|
`failed_requests` INT NOT NULL DEFAULT 0 COMMENT '失败生图请求数',
|
|
`queued_requests` INT NOT NULL DEFAULT 0 COMMENT '排队中请求数，预留异步任务使用',
|
|
`running_requests` INT NOT NULL DEFAULT 0 COMMENT '进行中请求数',
|
|
`total_images` INT NOT NULL DEFAULT 0 COMMENT '成功生成图片总数',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='生图统计表';
|