SparkyFitness 新数据库迁移与新增数据表完整操作清单（New Migration / New Table Checklist 实战指南）

• 后端
• 前端
• 移动开发

导读

本文是 SparkyFitness 开源仓库内部沉淀的工程规范 —— agent-docs/new-migration-checklist.md 的深度实战指南。它面向所有需要在SparkyFitnessServer中添加或修改数据库迁移（尤其是新建数据表、或改变用户可见访问行为）的开发者：从创建迁移文件、配置 PostgreSQL 行级安全（RLS）策略、重启服务应用迁移，到 Zod 契约、文档维护、下游前后端同步与最终验证，覆盖一次完整 schema 变更的全部流程。读完本文，你将掌握该仓库"迁移 + RLS 重放 + 契约 + 验证"四段式的工作流，并理解每一步背后的源码原理，从而避免提交一个"漏了 RLS 策略的新表"这类最常见的 Review 失败。

仓库是只读的，本文只介绍查看、运行与配置方式，不涉及对仓库内容的修改。

一、工作流总览：为什么这张清单存在

该清单在文档开头就给出了一条明确警告："本仓库最常见的 Review 失败，就是一张新表漏掉了清单中的某一项"。它的全部条目都在回答一个问题——"当你在 SparkyFitness 里新增/变更一张数据库表时，如何保证 schema、安全、契约、文档与测试五者同步？"

整张清单可以压缩为一条流水线：

创建迁移 SQL → 配置 RLS 策略 → 重启服务验证 → （CI 自动）schema 备份 → Zod schema 契约 → 文档更新 → 下游路由/服务/前端/移动端同步 → 验证命令

在深入每一步之前，先理解仓库的两个关键机制：迁移如何被应用、RLS 策略如何被重放。这两个机制决定了清单第 1、2 步的写法。

1.1 迁移与 RLS 的启动时序（源码证据）

清单第 1 步强调"不要发明替代的迁移机制"，这是因为服务器启动时会自动完成"先应用未执行的迁移、再重放 RLS 策略"两件事。

入口在 SparkyFitnessServer/index.ts：initializeDatabase()必须在任何业务模块被 import 之前执行（注释明确说明 Better Auth 会在auth.ts模块作用域内急切地校验数据库 schema，并在进程生命周期内缓存不匹配结果，只有它自己的迁移 runner 才能使其失效——因此原始 SQL 迁移如果延后执行，升级后首次启动会出现/api/auth全部请求失败的问题，对应 issues #2469 / #2470）。同时这些 import 必须保持动态：db/poolManager.ts在模块加载时就从process.env构建两个 pg 连接池，静态 import 会被提升到 dotenv/loadSecrets 之上，导致连接池带着空配置被冻结。

具体的初始化逻辑在 SparkyFitnessServer/utils/initializeDatabase.ts：

1. 通过getSystemClient()取得 owner 连接；
2. pg_advisory_lock(hashtext('sparkyfitness:schema-initialization'))获取实例间的互斥锁，序列化多个服务器实例的 schema 初始化（锁名即lockName，见该文件第 7 行）；
3. 依次执行applyMigrations(client)与applyRlsPolicies(client)；
4. pg_advisory_unlock释放锁，任何一步失败都会销毁客户端连接。

迁移执行器 SparkyFitnessServer/utils/dbMigrations.ts 的具体行为：

• 先CREATE SCHEMA IF NOT EXISTS system; CREATE TABLE IF NOT EXISTS system.schema_migrations(...)，用system.schema_migrations表记录已应用的迁移；
• 读取SparkyFitnessServer/db/migrations/下所有.sql文件并按文件名排序（readdirSync(...).filter(file => file.endsWith('.sql')).sort()）；
• 对每个不在已应用集合中的文件：执行 SQL → 写入system.schema_migrations记录；
• 全部迁移完成后调用grantPermissions(client)（见 SparkyFitnessServer/db/grantPermissions.ts），为应用角色授予 public/auth/system schema 的 USAGE、表与序列的增删改查、函数的 EXECUTE 权限，以及system.schema_migrations的 SELECT 权限。

关键推论：因为迁移按文件名（而非文件内声明）去重且严格排序，所以文件名里的时间戳必须唯一且单调递增；一旦一个迁移文件被记录进schema_migrations，改名或改内容都不会再被重新执行，只能靠追加新的迁移来修正。

1.2 RLS 策略的"单一事实来源"（源码证据）

清单第 2 步指向 SparkyFitnessServer/db/rls_policies.sql。该文件头注释自称"所有 RLS 策略的单一事实来源"，且"每次服务器启动在迁移之后执行，以保证一致的安全状态"。

它的执行结构（对应applyRlsPolicies）分为四个阶段：

• Step 1：一次性 purge public schema 下所有既有策略（遍历pg_policies逐条DROP POLICY）；
• Step 2：对文件内硬编码的约 100 张表逐一ALTER TABLE ... ENABLE ROW LEVEL SECURITY（完整清单见该文件第 24-120 行）；
• Step 3：定义公共辅助函数，包括current_user_id()、authenticated_user_id()、set_app_context(UUID, UUID)（见第 369-379 行）、can_access_user_data(targetUserId, permissionType, authUserId)（第 381-418 行）、has_diary_access/has_checkin_read_access等域级助手、is_admin()；
• Step 4：定义并调用通用策略生成器（见下文第 2.2 节）。

1.3 双客户端模型：getClient与getSystemClient

清单第 2 步还强调了一个仓库特有的约定，其实现位于 SparkyFitnessServer/db/poolManager.ts：

• 模块加载时构建两个连接池：ownerPool（SPARKY_FITNESS_DB_USER）与appPool（SPARKY_FITNESS_APP_DB_USER），配置项包括host/database/password/port（默认 5432）、max: 10、idleTimeoutMillis: 30000、connectionTimeoutMillis: 5000；
• getClient(userId, authenticatedUserId?)（第 72-95 行）：从 app 连接池借出客户端，并执行SELECT public.set_app_context($1, $2)写入app.user_id与app.authenticated_user_id两个会话变量，让后续查询在该用户的 RLS 上下文内执行；authenticatedUserId为空时回退到AsyncLocalStorage中记录的认证用户，再回退到userId本身；调用方必须在finally中释放客户端，上下文设置失败则release(true)销毁它；
• getSystemClient()（第 96-99 行）：从 owner 连接池借出裸客户端，不做任何 RLS 上下文设置，绕过 RLS，只用于 admin/启动/迁移工作——这也正是initializeDatabase、grantPermissions使用它的原因。

二、逐条执行清单：从迁移文件到验证命令

2.1 第 1 步：创建迁移文件

• 在SparkyFitnessServer/db/migrations/下创建名为YYYYMMDDHHMMSS_description.sql的迁移文件。
• 不要发明其他迁移机制；服务器启动会按文件名顺序应用待执行迁移，随后重放 RLS 策略。

命名规范可以从仓库现有迁移得到印证，例如：

20250703170640_InitialDB.sql 202508201215_add_garmin_integration_schema.sql 20250831063900_create_mood_entries_table.sql 20260912150000_preserve_data_on_user_and_library_deletes.sql

一个典型的新表迁移（以mood_entries为例，见 SparkyFitnessServer/db/migrations/20250831063900_create_mood_entries_table.sql）长这样：

-- 1) 定义 updated_at 自动刷新的触发器函数（若库里还没有） CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; -- 2) 建表：主键默认 gen_random_uuid()，user_id 外键级联删除 CREATE TABLE mood_entries ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE, mood_value INTEGER NOT NULL, notes TEXT, entry_date DATE NOT NULL DEFAULT CURRENT_DATE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- 3) 挂上自动更新时间戳触发器 CREATE TRIGGER set_timestamp BEFORE UPDATE ON mood_entries FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();

从源码可以归纳出仓库迁移的几条约定，新表迁移应遵循：

• 用户数据表一律带user_id列，且REFERENCES auth.users(id) ON DELETE CASCADE（而不是public.user——auth.users是 Better Auth 管理的认证表，见 数据库安全分层文档）；
• 时间戳统一为TIMESTAMPTZ NOT NULL DEFAULT NOW()，需要自动更新的表挂set_timestamp触发器；
• 文档 docs/src/developer/database.md 提示：库表删除与日记历史的语义由迁移20260912150000_preserve_data_on_user_and_library_deletes.sql引入，库表行（如exercises、foods）被删除时，日记条目里的exercise_id/food_id采用可空 +ON DELETE SET NULL，保持历史日志快照可读。新建"可被日记引用"的库表时需沿用这一约定。

2.2 第 2 步：配置行级安全（每一张新表都必须做）

• 在SparkyFitnessServer/db/rls_policies.sql中添加或更新策略。
• 决定谁能读写行：仅 owner（如 cycle/pregnancy）、家庭共享（用哪种可委派权限：diary、checkin、medications还是reports？）、还是系统/admin。优先使用rls_policies.sql中现成的create_*_policy(...)生成器。
• 牢记getClient(userId, authenticatedUserId?)设置 RLS 上下文；getSystemClient()绕过 RLS，仅供 admin/启动/迁移使用。

仓库在rls_policies.sql的 Step 3 定义了三个会话级身份函数，所有策略都以它们为基准（security-tiers 文档 有系统化说明）：

Step 4 提供的通用策略生成器（rls_policies.sql第 432-559 行附近）是给新表选型时最该优先使用的工具：

• create_owner_policy(table_name, id_column DEFAULT 'user_id')：FOR ALL ... USING (id_col = authenticated_user_id())—— 仅 owner 读写，cycle/pregnancy 系列使用（如create_owner_policy('cycles')、create_owner_policy('pregnancies')），不可委派；
• create_shared_owner_policy(table_name, id_column)：SELECT 用current_user_id()（上下文可见），增删改用authenticated_user_id()—— 适合 Tier 2 的"只读共享"资料表；
• create_diary_policy(table_name)：select_policy走has_diary_read_access(user_id)，modify_policy走has_diary_access(user_id)—— 对应can_manage_diary委派权限，用于food_entries、exercise_entries、water_intake等日志表；
• create_checkin_policy(table_name)：读走has_checkin_read_access，写要求authenticated_user_id() = user_id OR has_family_access(user_id, 'can_manage_checkin')—— 对应can_manage_checkin，用于mood_entries、sleep_entries、check_in_photos等健康检查类表；
• create_medication_policy(table_name)/create_symptom_policy(table_name)：分别对应can_manage_medications与can_manage_symptoms权限域；
• create_library_policy(table_name, shared_column, permissions text[])：面向公共/共享库表，如create_library_policy('exercises', 'shared_with_public', ARRAY['can_view_exercise_library', 'can_manage_diary'])、create_library_policy('foods', 'shared_with_public', ARRAY['can_view_food_library', 'can_manage_diary'])；shared_column传'false'表示不共享。

决策路径（结合 database-security-tiers.md 的三层分级）：先判断新表属于哪一层 ——

• Tier 1 严格私有（凭证、API Key、SSO 令牌、2FA、私密日志）：仅 owner 可读写，绝不可委派，用create_owner_policy；
• Tier 2 只读共享（profile、偏好、自定义库表）：owner 可写、被切换的委派人可读，用create_shared_owner_policy或带权限数组的create_library_policy；
• Tier 3 委派可写（日记/日程/健康日志）：按业务域选create_diary_policy/create_checkin_policy/create_medication_policy/create_symptom_policy。

此外，还有一类"System-Only"表（如passkey_registration_tickets、openfoodfacts_product_read_rate_limit、rate_limit）：启用 RLS 但不挂任何策略，只有getSystemClient()能访问。清单中"系统/admin"选项对应的正是这种模式。

配套测试佐证：仓库用 SparkyFitnessServer/tests/rlsPermissionMatrix.integration.test.ts 把整张 RLS 权限矩阵钉死成测试。测试内的DOMAIN: Record<string, Domain>映射是"唯一事实来源"：每一个启用了 RLS 的表必须恰好出现一次（如第 217 行mood_entries: 'checkin'）。完整性守卫测试会反向扫描数据库 —— 若有 RLS 表没被归类（新表没人分类），或映射里出现了已不再启用 RLS 的表，测试直接失败。因此新建表后必须同步更新这个映射，否则pnpm exec vitest run tests/rlsPermissionMatrix.integration.test.ts会挂掉。

2.3 第 3 步：启动服务器让迁移生效

• 重启服务器（在SparkyFitnessServer/下执行pnpm start）。
• 确认迁移干净地应用、RLS 策略无错地重放。
• 检查服务器日志中的任何迁移失败。

pnpm start对应nodemon（见 SparkyFitnessServer/package.json）。启动时index.ts会执行上文 1.1 节的初始化序列。判定标准是日志里依次出现：

• Ensured schema_migrations table exists.
• Applying migration: <你的迁移文件名>与Successfully applied migration: <文件名>
• Permissions granted to application user.（grantPermissions成功）
• RLS 策略重放无报错（applyRlsPolicies完成）

若迁移 SQL 本身有语法错误或引用了不存在的列，initializeDatabase会捕获异常并process.exit(1)（见 index.ts），服务器直接拒绝启动——这是仓库刻意设计的"快速失败"。

2.4 第 4 步：Schema 备份（自动完成，无需手动操作）

• 不要在 PR 中更新根目录的db_schema_backup.sql。
• 合并后 CI 会从迁移重新生成它，并通过.github/workflows/schema-backup.yml开一个自动同步 PR。
• 永远不要手工编辑它，也不要提交从本地数据库导出的副本 —— 本地库会偏离"由迁移推导出的 schema"。

这一步的要点是负向清单：db_schema_backup.sql是迁移推导产物而非源，任何手工改动都会在下次 CI 重生成时被覆盖，或引入与迁移不一致的状态。提交 PR 时该文件应保持未修改。

2.5 第 5 步：添加 Zod schema（跨包共享契约）

• 在shared/src/schemas/database/<Table>.zod.ts添加或更新该表的 Zod schema。
• 从shared/src/index.ts导出它。
• 如果该表支撑 API，在shared/src/schemas/api/<DomainName>.api.zod.ts创建/更新请求/响应 schema。

shared包是前端、移动端、后端三方共享的类型契约层。数据库层 schema 的写法以 shared/src/schemas/database/MoodEntries.zod.ts 为范本：

// Generated by ts-to-zod import { z } from "zod"; export const moodEntriesIdSchema = z.string().and( z.object({ __brand: z.literal("public.mood_entries") }), ); const userIdSchema = z.any(); export const moodEntriesSchema = z.object({ id: moodEntriesIdSchema, user_id: userIdSchema, mood_value: z.number(), mood_tags: z.array(z.string()), notes: z.string().nullable(), entry_date: z.date(), created_at: z.date(), updated_at: z.date(), created_by_user_id: userIdSchema.nullable(), updated_by_user_id: userIdSchema.nullable(), }); export const moodEntriesInitializerSchema = z.object({ id: moodEntriesIdSchema.optional(), user_id: userIdSchema, mood_value: z.number(), mood_tags: z.array(z.string()).optional(), notes: z.string().optional().nullable(), // ... });

可以从该文件归纳出的约定：

• 主键列生成带__brand的品牌化 schema（如z.literal("public.mood_entries")），用于在类型层面区分不同表的主键；
• user_id等外键统一走userIdSchema；
• 每个表通常导出三件套：<Table>Schema（完整行）、<Table>InitializerSchema（插入用，必填/可选与 DB 默认值对齐）、以及created_by_user_id/updated_by_user_id这类审计列的可空处理。

导出：在 shared/src/index.ts 中以export * from "./schemas/database/MoodEntries.zod.ts";（第 79 行）的形式导出，并同步导出对应的 API schema（第 1-10 行可见export * from "./schemas/api/..."模式）。API 层 schema 目录shared/src/schemas/api/中每个领域一个文件（如CheckInMeasurements.api.zod.ts、DailyGoals.api.zod.ts）。

2.6 第 6 步：文档同步

• 更新docs/src/features/family-friends-sharing.md（面向用户的共享行为）。
• 更新docs/src/developer/database-security-tiers.md：把新表连同其权限类型加入，并归类为 Tier 1 / Tier 2 / Tier 3。
• 如果新增了领域分类，更新docs/src/developer/database.md的表索引。

这步把"代码事实"沉淀为"团队认知"。database-security-tiers.md以三层分级表格 + 每张表的"写权限 / 读权限"两列组织（例如mood_entries属 Tier 3-C 检查类：写入需can_manage_checkin，读取需can_view_reports或can_manage_checkin）。新表要做的就是在对应层级表格中补一行，并把rlsPermissionMatrix测试中选定的域（owner/diary/checkin/medication/symptom/library）与文档描述对齐。

2.7 第 7 步：下游契约

• 如果新表支撑 API：创建/更新路由（v2 路由的 Zod 路由 schema 放在SparkyFitnessServer/schemas/）、service、repository、测试和 Swagger JSDoc。
• 检查 Web 端（SparkyFitnessFrontend/）与移动端（SparkyFitnessMobile/）是否消费该契约，并同步更新。

仓库的纵向分层模式是：

routes/（HTTP 入口 + Zod 路由校验）→ services/（业务编排）→ models/（repository，经 getClient 访问 DB）

• v2 路由的请求/响应校验 schema 集中在SparkyFitnessServer/schemas/（如measurementSchemas.ts、waterContainerSchemas.ts、foodSchemas.ts等），同时结合shared包的 API schema；
• 每个路由文件都带 Swagger JSDoc 注释（仓库的 Swagger 配置见 SparkyFitnessServer/config/swagger.ts），新接口必须同步补注释，否则 API 文档会缺条目；
• repository 层通过getClient(userId, authenticatedUserId?)获取带 RLS 上下文的客户端（例如models/measurementRepository.ts、models/foodEntry.ts等），而foodCoreService等启动/内部流程在合适场景才使用系统连接——业务代码严禁随意绕过 RLS。

2.8 第 8 步：验证

• 在SparkyFitnessServer/下运行pnpm run validate—— typecheck、lint、format 三连。
• 运行贴近改动面的测试：pnpm exec vitest run tests/<domain>*.test.ts。
• 如果 API 变了，还要在消费方（前端、移动端）验证。

与清单对应的脚本定义见 SparkyFitnessServer/package.json：

针对"新增表"场景，至少要跑的两类测试是：

1. RLS 完整性测试：pnpm exec vitest run tests/rlsPermissionMatrix.integration.test.ts—— 它会校验"所有启用 RLS 的表都被 DOMAIN 映射归类、每张表的策略引用了预期的辅助函数"，新表若忘了更新映射会在这里失败；
2. 初始化/启动顺序测试：pnpm exec vitest run tests/initializeDatabase.test.ts与tests/bootOrder.test.ts，它们守卫"迁移先于 Better Auth 校验"的启动时序。

三、把清单串成一次真实变更的落地过程

假设你要新增一张"睡前补水打卡"表bedtime_water_entries（仅 owner 私有、不参与家庭共享），完整的落地顺序应该是：

1. 写迁移SparkyFitnessServer/db/migrations/20261009120000_create_bedtime_water_entries_table.sql（带user_id外键、时间戳默认值、必要触发器）；
2. 在rls_policies.sql的 Step 2 表清单中加入bedtime_water_entries，Step 4 调用create_owner_policy('bedtime_water_entries')；
3. 在tests/rlsPermissionMatrix.integration.test.ts的DOMAIN映射中登记bedtime_water_entries: 'owner'；
4. 重启pnpm start，观察日志确认迁移应用、权限授予、RLS 重放均成功；
5. 在shared/src/schemas/database/BedtimeWaterEntries.zod.ts建 schema，并在shared/src/index.ts导出（如果暴露 API，再建BedtimeWaterEntries.api.zod.ts）；
6. 在docs/src/developer/database-security-tiers.md的 Tier 1 表格补一行，若有用户可见的共享行为变化则更新docs/src/features/family-friends-sharing.md，若引入新领域分类则更新docs/src/developer/database.md；
7. 若提供 API：补routes（含 Swagger JSDoc）、services、modelsrepository 与测试，并在前端/移动端按需消费；
8. 运行pnpm run validate+pnpm exec vitest run tests/bedtimeWater*.test.ts+ RLS 完整性测试。

四、常见踩坑与快速自查

对照清单最后自检，以下是仓库中反复出现的问题（部分来自 agent-docs/anti-patterns.md 与文档注释）：

• 新表没进rls_policies.sql的启用清单→ 表完全裸奔，RLS 完整性测试直接失败；
• 策略写死user_id = current_user_id()而非authenticated_user_id()→ 家庭委派/上下文切换场景出现越权或不可见问题；记住"current_user_id是资料上下文，authenticated_user_id才是真实 actor"；
• 忘了在DOMAIN映射登记新表→rlsPermissionMatrix.integration.test.ts的 completeness guard 失败；
• 手工编辑/提交db_schema_backup.sql→ 与迁移推导结果漂移，CI 自动生成的 sync PR 会再次覆盖；
• 迁移文件改名或复用时间戳→ 已被system.schema_migrations记录的文件不会再执行，时间戳撞名会导致排序不稳定；
• 新 API 缺 Swagger JSDoc / 缺 v2 Zod 路由 schema→ 文档与契约断裂；
• 业务代码直接用 owner 连接→ 绕过 RLS 的通道，应一律通过getClient(...)走 app 池并在finally释放，只有 admin/启动/迁移才用getSystemClient()。

五、参考资料（仓库内）

• 清单原文：agent-docs/new-migration-checklist.md
• 迁移执行器：SparkyFitnessServer/utils/dbMigrations.ts
• 启动初始化：SparkyFitnessServer/utils/initializeDatabase.ts 与 SparkyFitnessServer/index.ts
• RLS 单一事实来源：SparkyFitnessServer/db/rls_policies.sql
• 连接池与双客户端模型：SparkyFitnessServer/db/poolManager.ts
• 权限授予：SparkyFitnessServer/db/grantPermissions.ts
• 安全层级说明：docs/src/developer/database-security-tiers.md
• 数据库总览：docs/src/developer/database.md
• 家庭共享行为：docs/src/features/family-friends-sharing.md
• RLS 权限矩阵测试：SparkyFitnessServer/tests/rlsPermissionMatrix.integration.test.ts
• Zod 契约示例：shared/src/schemas/database/MoodEntries.zod.ts 与 shared/src/index.ts
• 迁移示例：SparkyFitnessServer/db/migrations/20250831063900_create_mood_entries_table.sql

• 后端
• 前端
• 移动开发