mindfulness/spec_kit/Personalized Reco/modules/db-design/plan.md

DB Design & Migrations（数据库设计与迁移）｜Plan

对应规范：spec_kit/Personalized Reco/modules/db-design/spec.md

前置确认（已对齐）：

• content_id：MySQL 自增主键；文案微调时 content_id 不变（更新同一条记录）。
• need_suitability/context_suitability：JSON 存储。
• risk_flags：选择更强扩展性的方案（本计划采用 关联表，利于索引与过滤）。
• 安全池（L3）：方式 A（is_safe_pool）。
• 迁移：使用 Alembic，目录放 server/alembic/；dev/pro 两套库均可运行同一套迁移。
• 字符集：统一 utf8mb4。

---

1. 目标与交付物

1.1 目标

• 在“库为空”的前提下，落地推荐系统最小可用的数据模型。
• 保证后续推荐查询可实现：按画像条件召回、按 ID 批量查、按风险标记过滤、支持安全池兜底。

1.2 交付物

• server/alembic/：Alembic 初始化目录、alembic.ini（或等价配置）、迁移脚本。
• SQLAlchemy ORM 模型（建议放 server/app/db/models/）。
• 初始迁移：创建 contents、content_profiles、content_risk_flags（以及必要索引）。

---

2. 技术决策（V1）

2.1 表设计原则

• 分离主体与画像：contents 存文本与来源字段；content_profiles 存画像字段（便于未来画像重算/回填）。
• JSON 存 suitability：context_suitability、need_suitability 用 JSON，保持结构与规则文档一致。
• risk_flags 关联表：用 content_risk_flags(content_id, flag)，便于：
  • 快速 Hard Filter（block_health_medical 等）
  • 索引与统计（按 flag 计数）
  • 兼容旧 flag 映射（在写入/读取层）
• emotion_score 的 general 表示：用 NULL 表示 general（与规则文档“可为 general”语义等价）。
• personalization_power：存为 TINYINT（0/5/10）或 DECIMAL(2,1)（0/0.5/1）。本计划推荐 TINYINT（更易索引/更省空间），应用层做映射：
  • 0 → 0.0
  • 5 → 0.5
  • 10 → 1.0

2.2 字符集与排序规则

• 数据库与表：utf8mb4
• collation：建议 utf8mb4_0900_ai_ci（MySQL 8 默认更常见；若环境不同以实际为准，但必须 utf8mb4）

---

3. 表结构（V1 方案）

以下为“建议 schema”。实际字段名可调整，但语义必须严格对齐 句子文案打分规则。

3.1 contents（文案主体）

• content_id BIGINT UNSIGNED PK AUTO_INCREMENT
• text TEXT NOT NULL
• author_id VARCHAR(64) NULL
• template_id VARCHAR(64) NULL
• created_at DATETIME NOT NULL
• updated_at DATETIME NOT NULL

索引建议：

• idx_contents_author_id(author_id)
• idx_contents_template_id(template_id)

3.2 content_profiles（内容画像）

• content_id BIGINT UNSIGNED PK（FK → contents.content_id，ON DELETE CASCADE）
• stage ENUM('general','expecting','parenting','unknown') NOT NULL DEFAULT 'general'
• emotion_score DECIMAL(3,2) NULL
  • 约定：NULL 表示 general
• context_suitability_json JSON NOT NULL
• need_suitability_json JSON NOT NULL
• personalization_power TINYINT UNSIGNED NOT NULL DEFAULT 0
  • 约定：只允许 0/5/10
• review_confidence DECIMAL(3,2) NULL
  • 约定：NULL 由推荐模块按 0.7 兜底（对齐规则文档）
• is_safe_pool BOOLEAN NOT NULL DEFAULT FALSE
• updated_at DATETIME NOT NULL

索引建议：

• idx_profiles_stage(stage)
• idx_profiles_personalization_power(personalization_power)
• idx_profiles_is_safe_pool(is_safe_pool)

说明：suitability 放 JSON 后，V1 可以先不做 JSON 路径索引；当候选量上来后再加“生成列/函数索引”做加速（见 6.2）。

3.3 content_risk_flags（风险标记，关联表）

• id BIGINT UNSIGNED PK AUTO_INCREMENT
• content_id BIGINT UNSIGNED NOT NULL（FK → contents.content_id，ON DELETE CASCADE）
• flag VARCHAR(64) NOT NULL
• created_at DATETIME NOT NULL

约束与索引：

• UNIQUE：uniq_content_flag(content_id, flag)（同一 content 不重复插同 flag）
• 索引：idx_flag(flag)（用于 Hard Filter 与统计）
• 索引：idx_content_id(content_id)（用于按内容批量取 flags）

命名约束（应用层强制，DB 可选）：

• flag 必须以 unsafe_for_ / block_ / soft_ 开头（严格对齐规则文档）

---

4. Alembic 迁移落地步骤

4.1 依赖与目录

• 后端依赖：alembic（加入 server/requirements.txt，版本随项目统一管理）
• 目录：server/alembic/（包含 env.py、versions/）
• 连接串：复用现有 DATABASE_URL（mysql+aiomysql://...）

4.2 初始化与生成迁移（一次性）

• alembic init alembic（在 server/ 下）
• 配置 env.py：
  • 从 app/core/config.py 读取 DATABASE_URL
  • 引入 ORM Base 与 models，启用 autogenerate
• 创建初始迁移：
  • alembic revision --autogenerate -m "init content tables"
  • alembic upgrade head

4.3 dev/pro 一致性

• 迁移脚本保持同一套；通过不同环境的 DATABASE_URL 指向 mindfulness_dev 或 mindfulness。

---

5. 入库流程（写入契约）

5.1 一条文案最小入库数据

必须字段（V1 最小可用）：

• text
• content_profiles.stage
• content_profiles.emotion_score（可为 NULL 表示 general）
• content_profiles.context_suitability_json（必须包含 5 个 key：family/work/relationship/friends/health，值为 0/0.5/1）
• content_profiles.need_suitability_json（必须包含 5 个 key：emotional_support/parenting_pressure/self_worth/anxiety_relief/rest_balance，值为 0/0.5/1）
• content_profiles.personalization_power（0/5/10）
• content_risk_flags（可为空集合，但若存在必须按命名规范）

强烈建议字段：

• author_id、template_id
• review_confidence
• is_safe_pool（若要参与 L3 安全池）

5.2 写入策略

• 创建文案时：
  • 先写 contents 得到 content_id（自增）
  • 再写 content_profiles（同 content_id）
  • 再批量写 content_risk_flags
• 文案微调时（content_id 不变）：
  • 更新 contents.text 与 updated_at
  • 同步更新 content_profiles（若画像变更）
  • risk_flags 做“全量覆盖”或“差量更新”（plan 实现阶段定）

---

6. 查询与性能规划

6.1 V1 查询策略（先可用）

• 候选召回：
  • 先按 stage、personalization_power、is_safe_pool 等可索引字段进行粗过滤
  • 再在应用层结合 suitability JSON 与 risk_flags 做精过滤/打分
• Hard Filter：
  • 通过 content_risk_flags join 或子查询排除指定 flags（如 block_health_medical）
• 批量查：
  • content_id IN (...) join content_profiles + left join content_risk_flags

6.2 V1.1 性能增强（候选量上来后再做）

当候选池变大、应用层过滤成本上升时，优先做两类增强：

• 生成列/函数索引：为常用召回维度（例如 need/context 的某些 key）创建 generated columns（从 JSON_EXTRACT 取值并映射到 TINYINT），再加索引。
• 风险 flag 位图/派生列：对 block_health_medical 等强规则增加派生布尔列（或维护冗余表），降低 join 成本。

---

7. 测试与验收（DB 子模块）

7.1 迁移验收

• 在全新库执行 alembic upgrade head 成功。
• 执行 downgrade（若实现）可回滚（至少在开发环境可用）。

7.2 数据契约验收

插入一条最小文案记录后，能够查询并组装出推荐模块所需的 ContentProfile 字段集合：

• content_id/text/stage/emotion_score/context_suitability/need_suitability/personalization_power/risk_flags

7.3 规则口径验收（写入侧）

• 写入 risk_flags 时，若出现旧 flag（如 block_stage_unknown）：
  • 写入层需在入库前映射为新命名（或拒绝写入并提示）
  • 推荐侧读取层不得再出现旧 flag 名称

---

8. 与其他子模块的接口约定

• Content Repository 只依赖本模块提供的表与字段语义，不依赖具体迁移实现细节。
• 推荐引擎/打分模块对 review_confidence 的缺省值假设（0.7）在 DB 缺失时依然成立。