db-migrations

数据库迁移指南

Schema 约定

生成任何迁移之前，请先遵循以下约定——它们改变的是 schema 文件的样子，而不只是 SQL。

• 对于会不断扩展的领域，不要使用 pg 枚举（也不要使用固定值集合）。 对于值集合会持续扩大的列（资源类型、状态、提供方等），请使用普通的 text 列，并通过 .$type<UnionType>() 指定类型。每新增一个字面值，pgEnum 都要求执行一次 ALTER TYPE ... ADD VALUE 迁移；即使是 Drizzle 的 text('col', { enum: [...] }) 选项，也会将值列表硬编码在 schema 文件中。使用 .$type<>() 时，新增一个值只需修改类型——完全不需要迁移。

  // ✅ Good — type lives in @lobechat/types, column stays plain text
  resourceType: text('resource_type').$type<TransferResourceType>().notNull(),
  
  // ❌ Bad — pg enum, needs ALTER TYPE per new value
  resourceType: resourceTypeEnum('resource_type').notNull(),
  
  // ❌ Avoid — value list hardwired into the schema file
  resourceType: text('resource_type', { enum: TRANSFER_RESOURCE_TYPES }).notNull(),
  

• 不要将领域常量放在 schema 文件中。 在 packages/database/src/schemas/ 下新建或修改的 schema 文件中，共享的领域字面值数组、联合类型和选项接口应放在 @lobechat/types 中（每个领域一个模块，并从其 index.ts 重新导出）；schema（.$type<>()）和使用方（通过 z.enum(...) 定义的路由、服务、UI）都应从该处导入。此规则仅针对领域常量——表对象、推断出的行类型、Drizzle 关联对象以及 zod 插入／查询 schema（insertAgentSchema 等）仍由 schema 文件负责，留在原处。已导出此类常量的现有 schema 文件（例如 resourcePermission.ts）不受此规则追溯影响；下次修改该文件时再酌情迁移，不要一次性批量迁移。

• 只有需要逐条寻址的行才使用带前缀的 ID。 当 ID 会出现在供人查看的地方（URL 路径段、API 参数、工具调用载荷、支持工单）时，idGenerator 前缀（task_、cmt_、brf_）才有价值。若某个子表的行只会按其父记录批量获取，则改用普通 UUID，例如 task_dependencies / task_documents / task_topics；这些表使用前缀只会在 idGenerator.ts 中增加一条登记记录，却毫无收益。

  // ✅ Addressed individually — deleteComment(commentId), /task/:id
  id: text('id')
    .primaryKey()
    .$defaultFn(() => idGenerator('taskComments'))
    .notNull(),
  
  // ✅ Only ever read in bulk for one parent
  id: uuid('id').defaultRandom().primaryKey().notNull(),
  

• 列顺序：标识 → 作用范围 → 表自身的列 → visibility → 时间戳。 首先放置 id 和父表外键，然后是所有权列（user_id、workspace_id），接着是该表的用途相关列，再放置 visibility，最后放置 ...timestamps / createdAt() 展开项。这样，读者查看不熟悉的表时，就能在相同位置找到相同字段。将 visibility 放在末尾是有意安排，而非历史遗留：目前包含此列的 22 张表中，有 17 张将其放在最后三列内，没有一张将其紧接在 id 之后。新表若将它提前到前面，就会成为唯一的例外——若确实值得调整其位置，应在整个代码库中统一修改并更新此规则，而不要逐表调整。

选择发布策略

生成或编辑迁移之前，请将每项数据库变更归入以下三种发布路径之一。

在实际 Dev 数据库上验证部署假设

不要根据“这项迁移可能很慢”或“安装这些触发器可能会阻塞部署”之类的假设性说法来选择部署路径。在决定某项架构变更是否需要手动执行生产环境步骤、延后安装或专门的数据回填之前，请先在项目的实际 Dev 数据库上测试相关操作。

• 首先使用项目批准的数据库访问工具对数据库目标进行分类；切勿直接读取包含机密信息的 .env 文件。
• 测量实际操作或最接近的安全等效操作，例如使用临时名称创建定义完全相同的探测索引，或在随后回滚的事务中安装临时触发器。
• 记录测试的 SQL 或操作、具有代表性的行数和表大小、耗时以及清理验证结果。
• 确保探测操作可撤销，并在测量后移除所有临时数据库对象。
• 将单次 Dev 测试结果视为有关已观测 Dev 规模的证据，而不是生产环境行为的证明。明确说明生产环境在规模、负载、缓存状态和锁竞争方面的重大差异，并将由此得出的任何生产环境结论标记为推断。

部署决策必须结合代码仓库的部署事实与这些测量结果。不要仅仅为了防范未经测量的性能问题而添加运维表、延迟激活路径或手动发布步骤。

1. 常规 Drizzle 迁移

对于可在部署期间安全执行的架构变更，例如创建小型表或添加可空列，请使用常规 Drizzle 工作流：

1. 更新 Drizzle 架构。
2. 运行 bun run db:generate。
3. 使用以下步骤检查并完善生成的产物。

在将手动迁移命令列为发布步骤之前，请检查目标代码仓库的构建和部署脚本。如果其部署流水线已经自动应用迁移，请勿要求重复手动运行。

2. 在线创建索引

在大型或频繁访问的表上以常规方式创建索引可能会阻塞写入和查询，长时间运行的语句也可能导致部署停滞。在这种情况下：

1. 部署应用程序之前，在目标数据库的 SQL 编辑器中使用 CONCURRENTLY 手动创建索引：

   CREATE INDEX CONCURRENTLY IF NOT EXISTS "table_column_idx"
   ON "table" USING btree ("column");
   

2. 在 Drizzle 迁移中保留幂等的非 CONCURRENTLY 版本：

   CREATE INDEX IF NOT EXISTS "table_column_idx"
   ON "table" USING btree ("column");
   

手动执行在线操作可以避免阻塞生产流量。之后部署运行迁移时，IF NOT EXISTS 会使该语句成为空操作；而新的或可自托管的数据库仍可通过正常重放迁移来达到一致状态。不要将 CREATE INDEX CONCURRENTLY 放在事务中。

3. 数据回填

回填和历史数据协调必须作为专门的幂等脚本运行，而不是放在 Drizzle 迁移中。将架构变更保留在 Drizzle 中，但把逐行或分批的数据处理移到单独的脚本中，以免阻塞部署。

根据兼容性决定在应用程序部署之前还是之后运行脚本：

• 当新代码或新约束要求立即填充现有行时，在部署前运行。
• 当应用程序可以安全处理新旧两种行结构，且回填可以逐步完成时，在部署后运行。

回填脚本应可恢复、安全重试、按有界批次处理，并且可观测。将可选的清理或主动协调视为可选操作，而不是发布阻塞项。

开发阶段的架构变更

功能开发期间，架构会频繁变动。如果迁移尚未发布时架构发生变化，不要手动编辑现有迁移 SQL 来追赶新的架构形态。删除本分支新增的草稿迁移文件（SQL 文件、对应的快照和对应的日志条目），然后重新运行生成器，并重新执行下文的常规迁移审查步骤。

例如，如果本分支的草稿迁移为 NNNN_add_verify_tables（替换为本分支的实际编号，绝不要使用已发布的迁移编号）：

# 1. Delete the draft SQL and its snapshot
rm packages/database/migrations/NNNN_add_verify_tables.sql
rm packages/database/migrations/meta/NNNN_snapshot.json

# 2. Remove the matching NNNN entry from the journal's "entries" array
#    packages/database/migrations/meta/_journal.json

# 3. Regenerate from the current schema
bun run db:generate

这样可以使生成的 SQL、快照和日志与实际架构保持一致。手动编辑 SQL 仅用于审查阶段的强化，例如添加幂等子句、自定义扩展 SQL，以及有意义的文件名／标签更新。

发布前，如果功能分支累积了多条仅用于开发的迁移，应尽可能将它们合并为一条迁移。生产环境不需要重放每个中间草稿形态，减少迁移数量也能降低部署风险。

例如，如果本分支新增了三个草稿 AAAA、BBBB 和 CCCC，请删除这三条草稿并重新生成一条迁移：

# 1. Delete every draft SQL and snapshot this branch added
rm packages/database/migrations/{AAAA,BBBB,CCCC}_*.sql
rm packages/database/migrations/meta/{AAAA,BBBB,CCCC}_snapshot.json

# 2. Remove those entries from the journal's "entries" array
#    packages/database/migrations/meta/_journal.json

# 3. Regenerate one migration covering the full schema delta
bun run db:generate

不要让迁移兼容本分支更早的、仅用于开发的版本。迁移尚未发布时，无需保留生产历史。直接用最简单的 SQL 修复本地／开发数据库（删除草稿表、重命名列、删除草稿行），然后根据当前架构重新生成本分支的迁移。

例如，如果本分支较早的草稿创建了 signup_attempt_id，而你后来已将其重命名为 user_signup_log_id，不要在迁移中添加兼容性的 ALTER ... RENAME。直接修复开发数据库，然后重新生成：

# Fix the dev DB to match the new schema (simplest SQL wins)
set -a && source .env && set +a && bun -e '
import pg from "pg";
const client = new pg.Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
await client.query("ALTER TABLE user_signup_logs DROP COLUMN signup_attempt_id");
await client.end();
'

# Regenerate so the migration reflects only the final shape
bun run db:generate

迁移进入生产环境或目标默认分支后，应将其视为不可变；需要修改时，应添加后续迁移，而不是改写现有迁移。

变基冲突

迁移文件发生变基冲突时，保留上游／默认分支的迁移，并删除当前功能分支引入的所有迁移。完成变基后，根据变基后的架构重新生成本分支的迁移。这样可以避免合并两个独立快照或手动拼接日志条目。

步骤 1：生成迁移

bun run db:generate

这会生成：

• packages/database/migrations/0046_meaningless_file_name.sql

并更新：

• packages/database/migrations/meta/_journal.json
• docs/development/database-schema.dbml

自定义迁移（例如 CREATE EXTENSION）

对于不涉及 Drizzle 架构变更的迁移（例如启用 PostgreSQL 扩展），请使用 --custom 标志：

bunx drizzle-kit generate --custom --name=enable_pg_search

这会生成一个空 SQL 文件，并正确更新 _journal.json 和快照。然后编辑生成的 SQL 文件，添加自定义 SQL：

-- Custom SQL migration file, put your code below! --
CREATE EXTENSION IF NOT EXISTS pg_search;

不要手动创建迁移文件或编辑 _journal.json——始终使用 drizzle-kit generate，以确保日志条目和快照正确。

步骤 2：优化迁移 SQL 文件名

将自动生成的文件名重命名为有意义的名称：

0046_meaningless_file_name.sql → 0046_user_add_avatar_column.sql

步骤 3：使用幂等子句（防御式编程）

始终使用防御性子句，使迁移具备幂等性（可安全地重复运行）：

CREATE TABLE

-- ✅ Good
CREATE TABLE IF NOT EXISTS "agent_eval_runs" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL
);

-- ❌ Bad
CREATE TABLE "agent_eval_runs" (...);

ALTER TABLE - 列

-- ✅ Good
ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "avatar" text;
ALTER TABLE "posts" DROP COLUMN IF EXISTS "deprecated_field";

-- ❌ Bad
ALTER TABLE "users" ADD COLUMN "avatar" text;

ALTER TABLE - 外键约束

PostgreSQL 不支持 ADD CONSTRAINT IF NOT EXISTS。请使用 DROP IF EXISTS + ADD：

-- ✅ Good: Drop first, then add (idempotent)
ALTER TABLE "agent_eval_datasets" DROP CONSTRAINT IF EXISTS "agent_eval_datasets_user_id_users_id_fk";
ALTER TABLE "agent_eval_datasets" ADD CONSTRAINT "agent_eval_datasets_user_id_users_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;

-- ❌ Bad: Will fail if constraint already exists
ALTER TABLE "agent_eval_datasets" ADD CONSTRAINT "agent_eval_datasets_user_id_users_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;

DROP TABLE / INDEX

-- ✅ Good
DROP TABLE IF EXISTS "old_table";
CREATE INDEX IF NOT EXISTS "users_email_idx" ON "users" ("email");
CREATE UNIQUE INDEX IF NOT EXISTS "users_email_unique" ON "users" USING btree ("email");

-- ❌ Bad
DROP TABLE "old_table";
CREATE INDEX "users_email_idx" ON "users" ("email");

步骤 4：更新日志标签

完成步骤 2，重命名迁移 SQL 文件后，更新 packages/database/migrations/meta/_journal.json 中的 tag 字段，使其与新文件名一致（不包含 .sql 扩展名）。