6.0 KiB

Raw Permalink Blame History

资源服务数据库初始化设计

日期：2026-02-03
类型：文档创建
影响范围：资源服务（attachment-service, file-storage-service）

变更概述

完成资源服务的技术栈符合性检查和数据库初始化设计文档。

主要内容

1. 技术栈符合性检查

对 docs/requirements/backend/04-services/resource/ 目录下的服务文档进行全面检查：

检查范围：

attachment-service.md (v4.1)
file-storage-service.md (v3.0)

检查维度：

数据库版本与类型（PostgreSQL 17 + UUID v7）
枚举类型设计（SMALLINT + IntEnum）
时间戳字段（TIMESTAMPTZ）
主键策略（<table>_id）
软删除（deleted_at）
引用完整性（应用层验证）
命名规范（snake_case）
索引策略

符合性评分：95/100

扣分项：

file_checksums 主键设计可优化（-3 分）
storage_provider 使用 VARCHAR 而非枚举（-2 分，但灵活性更好）

2. 数据库表结构

file_checksums 表（文件去重表）

用途：全局文件去重，跟踪文件引用计数

核心字段：

id (UUID) - 主键
checksum (VARCHAR(64)) - SHA256 校验和（唯一）
file_url, file_size, mime_type - 文件信息
storage_provider, storage_path - 存储信息
reference_count - 引用计数
created_at, last_accessed_at, updated_at (TIMESTAMPTZ)

索引：

UNIQUE (checksum)
idx_file_checksums_file_url
idx_file_checksums_reference_count
idx_file_checksums_last_accessed
idx_file_checksums_created_at

attachments 表（附件表 - 多态关联）

用途：管理所有实体的附件（文档、图片、头像、封面等）

核心字段：

attachment_id (UUID) - 主键
name, original_name, file_url, file_size, mime_type - 基本信息
category (SMALLINT) - 文件类型（1=document, 2=image）
checksum - SHA256 校验和
related_id, related_type, attachment_purpose - 多态关联（核心设计）
is_public, access_count, download_count - 访问控制
uploaded_by (UUID) - 上传者（应用层验证）
created_at, updated_at, deleted_at, expires_at (TIMESTAMPTZ)

枚举类型：

category: 1=document, 2=image
related_type: 1=user, 2=project, 3=storyboard, 4=character, 5=scene, 6=prop, 7=location
attachment_purpose: 1=avatar, 2=cover, 3=thumbnail, 4=document, 5=reference, 6=attachment

索引：

idx_attachments_related (related_id, related_type, attachment_purpose) - 核心组合索引
idx_attachments_related_type (related_type, related_id)
idx_attachments_uploaded_by
idx_attachments_category
idx_attachments_created_at
idx_attachments_checksum
idx_attachments_uploader_created (uploaded_by, created_at DESC)
idx_attachments_name_trgm (GIN 全文搜索)
idx_attachments_original_name_trgm (GIN 全文搜索)

部分索引：所有索引（除 checksum 和 expires_at）都使用 WHERE deleted_at IS NULL 排除已删除记录

3. 表关联关系

file_checksums (文件去重表)
    ↑ (通过 checksum 关联，无物理外键)
    |
attachments (附件表)
    ↓ (多态关联，应用层验证)
    |
    ├─→ users (related_type=1)
    ├─→ projects (related_type=2)
    ├─→ storyboards (related_type=3)
    ├─→ screenplay_characters (related_type=4)
    ├─→ screenplay_scenes (related_type=5)
    ├─→ props (related_type=6)
    └─→ locations (related_type=7)

引用完整性保证：

应用层验证（Service/Repository 层）
无物理外键约束
所有关联字段创建索引

4. 初始化顺序

前置条件：

PostgreSQL 17 已安装
数据库已创建（jointo）
UUID v7 生成函数已创建（gen_random_uuid）
触发器函数已创建（update_updated_at_column）
pg_trgm 扩展已启用

建表顺序：

创建 pg_trgm 扩展
创建 file_checksums 表
创建 attachments 表
创建索引
创建触发器
添加表和列注释

5. 迁移建议

首次部署

提供完整的 Alembic 迁移脚本模板，包括：

表创建
索引创建
触发器创建
注释添加

后续演进迁移点

file_checksums 主键优化：将 checksum 改为主键（性能优化）
storage_provider 枚举化：从 VARCHAR 改为 SMALLINT 枚举
新增 related_type 类型：扩展多态关联支持的实体类型
新增 attachment_purpose 类型：扩展附件用途类型

6. 性能优化建议

索引优化：根据实际查询模式调整
分区策略：数据量超过 1000 万行时考虑按时间分区
查询优化：使用 EXPLAIN ANALYZE 分析查询计划

7. 数据完整性保证

应用层验证清单：

✅ 验证 uploaded_by 用户存在
✅ 验证 related_id 实体存在
✅ 验证用户对 related_id 的权限
✅ 验证文件类型和大小
✅ 计算并验证 checksum
✅ 检查 file_checksums 去重

定期检查脚本：

检查孤立的 attachments
检查 checksum 不存在的附件
检查引用计数不一致

文件清单

新增文件：

docs/requirements/backend/04-services/resource/database-init.md - 数据库初始化设计文档

技术亮点

多态关联设计：使用 related_id + related_type + attachment_purpose 实现高度可扩展的附件管理
全局文件去重：通过 file_checksums 表实现跨所有表的文件去重
应用层引用完整性：禁止物理外键，在 Service/Repository 层保证数据一致性
完善的索引策略：核心组合索引 + 部分索引 + 全文搜索索引
时间戳规范：所有时间字段使用 TIMESTAMPTZ（符合 ADR 006）

后续工作

根据此文档创建 Alembic 迁移脚本
实现 Model 层（SQLModel）
实现 Repository 层（应用层引用完整性验证）
实现 Service 层（业务逻辑）
实现 API 层（FastAPI 路由）
编写单元测试和集成测试

变更类型：文档创建
影响范围：资源服务
向后兼容：N/A（新功能）

6.0 KiB Raw Permalink Blame History