首次迁移前的 PostgreSQL 数据库结构验证

AI 构建工具可以生成有效的 PostgreSQL，却仍然推断出错误的数据库。语法并不难，真正危险的是那些看似合理的错误：可选关系变成必填，状态字符串配上不完整的 CHECK 约束，删除操作级联删除本该保留的记录，或者迁移重建表后悄悄丢失一列。

因此，PostgreSQL 数据库结构验证必须分别测试含义、迁移行为和恢复能力。只有当推断出的结构经得起已知数据集、明确的不变量、代表性查询、破坏性变更审查和恢复演练，我才会批准它。少了任何一项，迁移仍只是提案。

即使生产数据库还是空的，第一次迁移也值得这样严格审查。早期的结构错误会很快固化，因为应用代码、种子数据、报表和后续迁移都会开始依赖它们。首次运行前花十五分钟审查，通常比六个月后解释为什么两个不同概念共用一个可空文本列便宜得多。

推断出的数据库结构不是可信规范

应把推断出的结构当作规范草案，而不是可直接执行的事实。构建工具看到的是提示词、示例界面、导入记录或生成的应用代码。它没有经历过数据库最终会承载的每个业务例外、保留规则、批量导入、支持修复和支付失败。

先区分三个团队常常混淆的问题。结构正确性关注表和约束是否建模了业务领域。迁移安全性关注拟议操作是否保留现有数据，并在执行期间让数据库可用。恢复准备度关注发生部分失败或语义错误的变更后，能否回到已知状态。通过其中一项，对另外两项几乎说明不了什么。

一条 CREATE TABLE 语句可以描述预期的最终结构，却仍通过不安全的操作抵达那里。假设构建工具将 customer_name text 改为 customer_id bigint。最终外键可能合理，但如果迁移在将历史姓名匹配到客户之前就删除姓名列，就会毁掉匹配所需的唯一证据。结构审查批准目的地，迁移审查检查旅程。

用领域语言大声读出拟议模型。说每张发票恰好属于一个法定客户，而不是说 invoices.customer_id 引用了 customers.id。前一种说法能引出有价值的异议：草稿可能在选择客户前就存在，导入的发票可能指向已归档客户，法律记录也许必须在开具时冻结客户名称。SQL 术语会掩盖这些分歧。

我要求每张推断出的表旁都附上一份假设说明。它应写明一行数据代表什么、如何识别、归谁所有、是否能在看似父对象不存在时独立存在，以及删除意味着什么。如果团队无法回答这些问题，构建工具就是在猜一个团队尚未设计好的数据库。

已知记录会暴露错误的表映射

已知数据集应包含为语义覆盖而挑选的记录，因为随机大样本往往只是重复同一种简单情况。十条精心挑选的记录，可能比一万条几乎相同的顺利路径记录更能揭示问题。

执行 DDL 前先建立映射矩阵。矩阵中的每一行应追踪一个源概念如何进入拟议目标，并记录预期数量或值。对于订单应用，产物可能如下：

这样能在约束细节分散审查注意力前发现表映射错误。AI 构建工具常把重复对象规范化为独立表，这通常合理，但重复并不能证明它们是同一个实体。两份文字相同的配送地址可能是历史快照，而不是指向一个可编辑地址行的引用。把它们合并后，之后一次地址修改就会改写历史。

反向错误也会发生。构建工具可能把客户字段复制到每张订单中，因为界面把它们一起展示。有些值属于客户，另一些值必须保留为订单快照。正确设计可以同时包含 customer_id 和 billing_name 这类已开具文档字段。把它称为重复并删除一边，会失去当前身份信息或历史事实。

使用与应用相同的导入或种子数据路径，将已知数据集加载到可丢弃的数据库中。然后针对事实编写断言，而不是只核对行数：

SELECT
    (SELECT count(*) FROM orders WHERE external_id = 'ORDER-A') AS order_a,
    (SELECT count(*) FROM order_items i
       JOIN orders o ON o.id = i.order_id
      WHERE o.external_id = 'ORDER-A') AS order_a_items,
    (SELECT account_id IS NULL FROM orders
      WHERE external_id = 'ORDER-B') AS order_b_unassigned;

通过时的结果形状应明确：

 order_a | order_a_items | order_b_unassigned
---------+---------------+---------------------
       1 |             2 | t

不要因为生成的应用仍能渲染，就接受无法解释的差异。UI 可能会隐藏重复父记录、丢失的子记录、截断的代码和凭空生成的默认值。在讨论生产发布前，应核对每个刻意设计的测试数据。

约束必须表达领域事实

数据库约束应拒绝永远无效的状态，无论由哪个界面、API、导入程序或修复脚本写入该行。如果规则存在例外，或依赖会变化的外部事实，强行把它塞进简单约束，往往会导致工作受阻或数据失实。

主键用来识别行，但不会自动提供有业务意义的身份。内部 bigint ID 可以和租户范围内唯一的订单号并存。如果业务规定订单号在每个租户内唯一，UNIQUE (tenant_id, order_number) 就表达了这条规则。全局唯一约束会拒绝合法记录，而完全没有约束会在重试时引入歧义。

CHECK 约束适合 quantity > 0 或 finished_at >= started_at 这类稳定的行事实。PostgreSQL 手册说明，数据库假定 CHECK 表达式在约束存续期间不可变。因此，调用某个日后行为可能改变的函数的 CHECK，可能让旧行违反表面上的规则。固定事实用固定表达式。会变化的策略，例如管理员控制的当前允许集合，应放在被引用的表或应用工作流中。

对生成的状态约束要保持怀疑。构建工具可能检查当前示例后生成：

status text NOT NULL
    CHECK (status IN ('draft', 'active', 'closed'))

只有当这些是完整且长期有效的状态时，它才正确。还要询问失败、取消、暂停、导入和未知的历史记录。如果状态机仍在变化，查找表可以让新增状态更明确，但不能代替迁移规则验证。一行允许包含 closed，并不能说明它可否从 draft 直接变成 closed。

有意识地使用唯一性。PostgreSQL 用唯一 B-tree 索引实现唯一约束，但部分唯一索引表达的是不同规则。软删除通常只需要在存活记录中唯一：

CREATE UNIQUE INDEX users_tenant_email_live_uq
    ON users (tenant_id, lower(email))
    WHERE deleted_at IS NULL;

它不能与 UNIQUE (tenant_id, email, deleted_at) 互换。PostgreSQL 根据自己的唯一性规则处理 NULL 值，加入删除时间戳也改变了被强制的身份定义。应使用测试数据审查确切的重复情形，而不是从列清单推测行为。

可空性是业务决策

只有当领域要求每条合法记录都有值，并且每条写入路径都能提供该值时，才把列设为 NOT NULL。界面设计的证据很弱。当前表单的必填字段并不能说明导入、草稿、系统生成行或历史记录也是如此。

分别审查四种状态：源数据省略了字段，源数据明确发送 null，源数据发送空值，源数据提供有意义的值。JSON API、表单、CSV 导入和 PostgreSQL 对这些状态的处理可能不同。如果应用在插入前把四种状态合并了，结构审查应揭示这个决定，而不是假装数据库已经解决了问题。

默认值也需要同样关注。默认值会在 INSERT 省略列时提供一个值，它不会修复显式 NULL，也不能证明这个值为真。如果国家未知是可能的，country_code DEFAULT 'US' 就很危险。该行现在包含了报表和合规逻辑可能会相信的确定性谎言。

常见的生成式迁移会在一条语句中添加必填列：

ALTER TABLE customers
    ADD COLUMN account_type text NOT NULL DEFAULT 'standard';

语句可能会执行成功，但每位历史客户都会在没有证据的情况下变成标准类型。更安全的顺序是先添加可空列，根据已知数据推导值，统计未解决的行，在应用写入中阻止新的遗漏，只有在领域允许时才添加 NOT NULL。如果未知仍然合法，应保留 NULL 并定义查询和界面如何显示它。

PostgreSQL 为部分约束提供了很有用的分离方式。可以将 CHECK 或外键加为 NOT VALID，这样创建时无需验证所有现有行，之后再使用 VALIDATE CONSTRAINT 检查。手册将其说明为推迟初始表扫描的方法。这并不允许忽略旧违规：新写入仍受约束，而验证步骤仍必须通过才能批准。

收紧可空性前，执行分布查询以显示实际类别：

SELECT
    count(*) AS total,
    count(*) FILTER (WHERE account_type IS NULL) AS nulls,
    count(*) FILTER (WHERE account_type = '') AS empty_strings,
    count(*) FILTER (WHERE account_type NOT IN
        ('standard', 'partner', 'internal')) AS unexpected
FROM customers;

AI 生成的默认值可能让这条查询在迁移后看起来很干净。也应在回填前运行它并保存结果，否则会失去区分推导值和捏造值所需的证据。

索引应服务于已观察到的访问模式

只有当索引支持已知查询、实施已声明的唯一性规则，或满足运维要求时才应批准。为所有看似标识符的列建立索引会浪费存储和写入成本，而缺少一个关键复合索引可能让普通列表页变成不断扩大的扫描。

先从生成的应用实际发出的查询开始。记录筛选列、租户边界、连接列、排序方式和预期结果大小。对于最近订单页面，查询形状比表结构图更重要：

SELECT id, order_number, status, created_at
FROM orders
WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

仅在 tenant_id 上的索引仍可能检查许多租户行并排序。(tenant_id, status, created_at DESC) 索引更贴合这种访问模式。列顺序不是人气竞赛，它应遵循实际查询中的等值条件、范围条件、排序和选择性。

在代表性数据上运行 EXPLAIN (ANALYZE, BUFFERS)，但不要把一个小型测试数据集当作性能证明。PostgreSQL 在小表上正确地偏好顺序扫描。验证应确认预期索引存在，并且生产规模的演练能让规划器做出真实选择。绝不要为了批准而禁用顺序扫描来强行制造索引扫描。

外键还会带来一个常见意外：PostgreSQL 会为被引用的主键或唯一列建立索引，但不会自动为引用它们的子列建立索引。因此，删除或更新父行时，可能需要扫描子表来检查引用。从子表到父表的连接也可能需要这个子列索引。根据预期读取和父表变更检查每段关系。

在初始提案中拒绝重复和未使用的索引。当合适的 (tenant_id, status, created_at) 索引已存在时，(tenant_id, status) 可能多余，不过工作负载细节会改变这个判断。比较定义，不要比较名称。AI 构建工具常为每项功能创建一个索引，却没有发现几项功能都需要相同的前导列。

对于已有的繁忙数据库，请记住 CREATE INDEX CONCURRENTLY 不能在事务块中运行，工作量更大，失败后还可能留下无效索引。PostgreSQL 手册明确说明了这些运维差异。将每个迁移都包在事务中的迁移框架，需要明确的例外和清理流程，不能只抱着希望替换一个关键字。

外键需要明确归属与删除规则

只有当团队决定一段关系表达的是归属、引用、可选上下文还是历史归因后，外键才是正确的。外观相似的列可能需要完全相反的删除行为。

考虑 projects.owner_user_id、invoices.customer_id 和 audit_events.actor_user_id。项目可能转移所有者。发票可能必须在客户账户关闭后继续存在。审计事件可能需要在身份数据删除后保留行为人的旧标识。因为它们都引用 users 就对三者都使用 ON DELETE CASCADE，会把破坏性的虚构写入数据库。

当子对象脱离父对象就失去意义，且删除父对象确实意味着删除整个聚合体时，使用 CASCADE。订单明细常符合这一点。支付记录、已开具文档、导入记录、日志和审核证据通常不符合。对这些内容，拒绝删除、归档、受控匿名化，或搭配保留快照字段的可空引用，可能更合适。

SET NULL 也需要语义审查。它保留子行，却抹去了直接关系。如果工作人员之后需要解释哪个账户创建了报告，空引用可能不够。保留非识别性的历史标记或快照可以在不保留全部个人数据的情况下保持可追溯性，但具体保留选择应由产品政策决定，不能由 AI 猜测。

检查两个方向的基数。构建工具可能把外键放进去却没有唯一约束，从而把一对一关系建成静默允许多个子行。它也可能在历史需要多个版本的地方强制唯一性。为拥有零个、一个和多个子对象的父对象编写测试情形，再说明哪些插入应通过。

可延迟约束必须有具体理由。当一个事务必须暂时违反引用顺序，或更新相互依赖的行时，它们会有帮助；但将每个外键默认设为延迟，会把错误推迟到提交时，也更难定位失败。除非真实事务顺序需要延后，否则保持即时约束。

将迁移应用到演练数据库后，检查系统目录：

SELECT
    conname,
    contype,
    convalidated,
    pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass
ORDER BY conname;

代表性输出如下：

       conname        | contype | convalidated | definition
----------------------+---------+--------------+---------------------------------------
 orders_pkey          | p       | t            | PRIMARY KEY (id)
 orders_customer_fk   | f       | t            | FOREIGN KEY (customer_id) REFERENCES customers(id)
 orders_total_check   | c       | t            | CHECK ((total_cents >= 0))

将返回的定义与已批准的归属规则对比。迁移成功本身无法揭示缺失的操作、意外延迟或未验证的约束。

看似合理的 SQL 中藏着破坏性变更

应把迁移当作数据转换来审查，因为表面整洁的 DDL 即使没有明显的 DROP TABLE，也可能丢弃含义。先搜索直接破坏操作，再检查类型转换、回填、重写、重命名和约束替换。

即使最终结构图看起来完全相同，重命名和删除再新增在运维上也不同。如果 surname 改为 family_name，重命名能更忠实地保留数据和依赖关系。删除旧列再添加新列会得到相同的图，却清空所有值。生成的迁移常能推断最终状态，却不理解连续性。

类型变更需要示例转换和拒绝情形。将文本标识符改为整数可能去掉前导零，或拒绝混合标识符。降低数值精度可能发生舍入。转换时间戳需要明确时区假设。改列前，使用最小值、最大值、null、格式错误值和历史异常值测试实际 USING 表达式。

以下操作都应要求书面理由：删除表或列、通过有损转换改变类型、替换已填充的列、添加 CASCADE、在生成回填后设置 NOT NULL，以及用不同列重建唯一性。还要检查生成函数或迁移回调中嵌入的原始 SQL。文本搜索只是起点，不是完整审查。

一种失败模式反复出现。已知数据中包含可选公司的联系人，但示例界面只显示商务联系人。构建工具将 contacts.company_id 设为 NOT NULL，并为未匹配的行插入名为 Unknown 的生成公司。迁移通过，计数一致，所有外键都验证成功。数据仍然错误：个人联系人现在看起来属于某家公司，报表会把无关人员归到一起，删除占位公司还可能级联删除真实联系人。

修复方法不是再加一个默认值。应恢复源状态，让关系可空，只迁移有证据支持的匹配，并添加断言，确认未匹配集合等于已知的个人联系人。这就是为什么语义测试数据必须记录预期关系，而不只是预期行数。

结构差异工具很有帮助，但我不赞成只根据差异批准。这个建议很流行，因为差异紧凑且易于审查。把它当作唯一门槛是错误的，因为它展示结构变化，却不展示回填值的来源、事务边界、锁行为或迁移后的真实状态。

演练必须证明结果和失败行为

在已知数据集的可丢弃恢复副本上运行完整迁移，然后测试预期结果以及中断或被拒绝的路径。全新的空数据库有助于发现顺序错误，却无法暴露有损转换、无效历史行或缓慢验证。

把以下演练顺序作为发布产物：

1. 将变更前数据集恢复到隔离数据库，记录行数和语义断言。
2. 获取当前数据库结构，应用精确的迁移产物，并保存所有输出、耗时和事务边界。
3. 运行系统目录检查、映射断言、约束拒绝测试和代表性应用查询。
4. 将重要值与记录的预期对比，包括未匹配和 NULL 类别。
5. 演练文档化的恢复方法，然后在恢复后的数据库上重新运行迁移前断言。

前后各导出一次只含结构的转储：

pg_dump --schema-only --no-owner --no-privileges \
  --dbname "$DATABASE_URL" > schema.sql

审查与应用相关的表、序列、索引、约束、函数、触发器、扩展和权限。ORM 模型差异可能遗漏应用未建模的数据库对象，尤其是触发器、表达式索引、部分索引和手动安装的函数。

加入负向测试，证明约束会拒绝错误状态。测试事务可以尝试无效插入，无论结果如何都回滚：

BEGIN;

INSERT INTO order_items (order_id, quantity, unit_price_cents)
VALUES (1001, 0, 2500);

ROLLBACK;

预期输出应指出被违反的约束，例如：

ERROR:  new row for relation "order_items" violates check constraint "order_items_quantity_check"
DETAIL:  Failing row contains (..., 0, 2500, ...).

不要在每个环境中比较完整错误文本，因为细节可能不同。自动化测试应断言 SQLSTATE 或约束标识，并为审核者保留可读输出。

在足以接近目标部署规模的数据集上测量锁和耗时。一个在五十行数据上瞬间完成的操作，在验证数百万行时可能阻塞写入。对于初始为空的生产数据库，眼前风险较低，但演练仍能测试导入的种子数据，并为后续变更建立基准。

恢复不只是反向迁移

只有当恢复能在服务可容忍的时间内还原数据和应用兼容性时，它才可信。重新创建被删除列的反向迁移无法恢复它们原来的值。

执行前先选择恢复单位。对于空的初始数据库，如果尚未开始用户写入，删除并重建数据库可能可以接受。一旦有真实写入，恢复可能需要数据库快照、逻辑备份、保留旧列或向前修复。正确方法取决于迁移期间和迁移后可能出现多少新数据。

依赖恢复前先测试恢复命令和凭据。备份即使存在，如果部署操作员无法恢复，也不能算恢复计划。恢复到独立数据库，验证归属关系和扩展，然后运行与迁移前相同的已知断言。

快照和事务回滚解决的是不同故障。若迁移在提交前失败，且每项操作都参与该事务，事务可以撤销语句。快照可让整个数据库回到更早状态，但这样可能丢弃快照后的合法写入。两种机制都不会自动协调这些写入。

当不确定性仍存在时，优先采用增量变更。新增列或表，用可衡量的规则复制数据，必要时在受控阶段同时运行两条代码路径，只有验证后才移除旧结构。这种扩展和收缩方法需要更多工作，但能保留证据。将重命名后的旧列保留一个发布周期，往往比从日志中重建它便宜。

提前写好恢复触发条件。例如任一语义断言失败、出现意外未匹配记录、约束无效、迁移超过批准的锁定时间窗口，或应用因版本不匹配出现错误。用户在等待时，操作员不应临时发明决策。

记录这样一个时间点：超过它后，恢复旧数据库还需要恢复旧应用。新应用可能依赖新列，旧应用可能拒绝新的枚举值或写入旧结构。数据库和应用恢复必须使用兼容版本。

批准需要证据，不需要信心

只有当另一个人能根据保存的产物复现它为何安全时，才批准第一次迁移。干净的代码审查或精致的生成界面带来的信心，经不起第一次无法解释的数据差异。

批准记录应包含推断出的假设、表映射矩阵、已知数据集身份、结构差异、精确迁移、验证查询及结果、被拒绝输入测试、索引依据、破坏性操作理由和经过测试的恢复流程。注明审核者，并把未解决的决定作为阻塞项保留，不要埋在聊天记录里。

当应用在 Koder.ai 中生成时，应先使用规划模式写下这些数据库结构决定，再允许执行迁移；导出源代码以供审查，并把快照和回滚视为仍需针对已知数据集演练的恢复工具。

不要让构建工具在测试通过前通过重新生成代码来批准自己的推断。这个循环可能让应用适应错误结构，而不是纠正模型。人必须决定数据库是否符合领域，尤其是身份、删除、保留和未知值的问题。

最终批准查询应当平淡无奇。每个已知事实都映射到一个预期结果，每个约束都拒绝预期的反例，每项破坏性操作都有理由，恢复会重现迁移前断言。如果证据需要一段有说服力的解释才能为不匹配开脱，就停止迁移。PostgreSQL 会精确执行数据库结构，包括构建工具猜错的部分。