PostgreSQL 数据建模

本章解决什么问题#

前端状态对象能轻松增加字段，但数据库会保存旧数据，接受多个服务和任务同时写入，还要支持未来查询。如果把页面 JSON 原样存成一个大字段，租户隔离、去重、关联和迁移会越来越难。本章目标是把知识库业务拆成稳定实体，并把“不允许发生什么”写进数据库约束。前置是 HTTP 请求与业务层的边界；不要求先掌握 ORM。

先问四个问题：一条记录代表什么，它属于谁，什么使它唯一，什么变化必须同时发生。知识库中，用户可以加入多个租户；文档属于一个租户并由该租户成员创建；一篇文档拆成有顺序的片段。页面可能把这些信息拼成一个树形对象，但持久化模型应表达实体间关系，而不是照搬组件层级。

从前端对象走向关系与不变量#

主键标识一行，外键保护关联存在，唯一约束保护业务去重，检查约束保护单行规则。前端显示列表需要 key，数据库主键也用于稳定识别，但它还承担关联与并发访问的基础。不能把数组下标当业务标识，也不能用标题做主键，因为标题会改且可能重名。

“一个用户在一个租户中只有一个成员关系”可用复合主键表达。“文档创建者必须是本租户成员”不能只让 owner_id 引用用户表，否则其他租户的用户也能成为 owner；应让 (tenant_id, owner_id) 一起引用成员关系。这种复合外键同时验证存在性与归属，是把资源隔离落到存储层的具体方法。PostgreSQL 约束文档 解释了复合键及引用关系。

约束与权限并不相同。复合外键能阻止一条文档指向不存在的本租户成员，却不能证明发起请求的人有权创建文档；权限章还会检查会话身份、成员角色和资源归属。数据库负责保存结构上合法的数据，应用还要决定谁能执行哪些操作。

类型选择和查询合同#

文本优先使用 text，再通过 CHECK 表达业务长度。时间点用 timestamptz，表示绝对时间并按会话时区展示；它并不保存用户最初提交的时区名称。日历生日、账期日期等只需要 date。金额可用明确精度的 numeric 或最小货币单位整数，避免二进制浮点用于精确结算。ID 使用 uuid 或 bigint 都可以，选型应服从规模、生成方式与已有约定，不必为每个表追求同一种流行写法。PostgreSQL 数据类型 列出了类型语义。

pg.Client 表示一条连接；connect() 建立连接，query(sql, values) 返回 Promise，查询结果在 rows 中，变更数量可读 rowCount，end() 关闭连接。参数用 $1、$2 表达，业务值放在独立数组中；不要把标题、用户输入或租户编号拼接进 SQL。数据库错误包含 code，例如 23503 为外键违规、23505 为唯一违规、23514 为检查违规。对外应翻译成业务错误，不泄漏数据库结构细节。

pg 对某些类型保留字符串表示，例如超出 JavaScript 安全整数可能性的 bigint，以及需要保留精度的 numeric。不要看到数字外观就无条件 Number 转换。JSON API 可将大 ID 与精确十进制值约定为字符串，前端按合同显示与提交。

完整示例：让数据库拒绝跨租户关联#

环境：Node 22.22 或 24、PostgreSQL 16 及以上、pg 8。先准备专用练习数据库与具有连接和临时表权限的账号。进入空练习目录后执行 npm.cmd init -y、npm.cmd install pg@8；Bash 使用 npm。PowerShell 设置 $env:DATABASE_URL='postgresql://course:course_password@127.0.0.1:5432/ai_course'，这里的账号密码仅为示例，替换成自己的本地练习账号。保存为 data-model.mjs，执行 node data-model.mjs。代码只创建当前连接的临时表，关闭连接后消失。

// data-model.mjs
import pg from 'pg';
import { randomUUID } from 'node:crypto';
import assert from 'node:assert/strict';

if (!process.env.DATABASE_URL) throw new Error('请设置 DATABASE_URL');
const client = new pg.Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
try {
  // 静态 DDL 没有用户输入；所有业务写入仍使用参数化 SQL。
  await client.query(`
    CREATE TEMP TABLE tenants (
      id uuid PRIMARY KEY,
      name text NOT NULL
    );
    CREATE TEMP TABLE app_users (
      id uuid PRIMARY KEY,
      email text NOT NULL UNIQUE
    );
    CREATE TEMP TABLE memberships (
      tenant_id uuid NOT NULL REFERENCES tenants(id),
      user_id uuid NOT NULL REFERENCES app_users(id),
      role text NOT NULL CHECK (role IN ('member', 'admin')),
      PRIMARY KEY (tenant_id, user_id)
    );
    CREATE TEMP TABLE documents (
      id uuid PRIMARY KEY,
      tenant_id uuid NOT NULL REFERENCES tenants(id),
      owner_id uuid NOT NULL,
      title text NOT NULL CHECK (char_length(title) BETWEEN 1 AND 80),
      status text NOT NULL CHECK (status IN ('draft', 'ready', 'failed')),
      created_at timestamptz NOT NULL DEFAULT now(),
      UNIQUE (tenant_id, id),
      FOREIGN KEY (tenant_id, owner_id)
        REFERENCES memberships(tenant_id, user_id)
    );
    CREATE TEMP TABLE chunks (
      id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      tenant_id uuid NOT NULL,
      document_id uuid NOT NULL,
      position integer NOT NULL CHECK (position >= 0),
      content text NOT NULL CHECK (char_length(content) > 0),
      UNIQUE (document_id, position),
      FOREIGN KEY (tenant_id, document_id)
        REFERENCES documents(tenant_id, id)
    );
  `);
  const tenantA = randomUUID();
  const tenantB = randomUUID();
  const user = randomUUID();
  const document = randomUUID();
  await client.query('INSERT INTO tenants VALUES ($1, $2), ($3, $4)',
    [tenantA, '甲团队', tenantB, '乙团队']);
  await client.query('INSERT INTO app_users VALUES ($1, $2)',
    [user, 'learner@example.test']);
  await client.query('INSERT INTO memberships VALUES ($1, $2, $3)',
    [tenantA, user, 'member']);
  const insertDocument = `INSERT INTO documents
    (id, tenant_id, owner_id, title, status)
    VALUES ($1, $2, $3, $4, $5) RETURNING id, title, status`;
  const result = await client.query(insertDocument,
    [document, tenantA, user, '我的知识库', 'draft']);
  assert.equal(result.rows[0].status, 'draft');

  await assert.rejects(() => client.query(insertDocument,
    [randomUUID(), tenantB, user, '错误归属', 'draft']), { code: '23503' });
  await client.query(`INSERT INTO chunks
    (tenant_id, document_id, position, content) VALUES ($1, $2, $3, $4)`,
    [tenantA, document, 0, '这是第一个片段']);
  const count = await client.query('SELECT count(*)::integer AS count FROM documents');
  assert.equal(count.rows[0].count, 1);
  console.log('合法文档保存成功，跨租户归属被外键拒绝，文档总数为 1');
} finally {
  await client.end();
}

预期输出一条验证说明。连接拒绝、密码错误、缺少 pg 或权限不足都属于环境未满足，应先修复环境；不要通过移除外键让示例“跑通”。这份教材未假设机器上已经安装数据库，数据库示例须在读者准备的练习环境实际执行后才能宣称通过。

逐段说明模型背后的选择#

成员关系是独立表，因为同一用户可以属于多个租户，而角色依赖具体租户。把 role 放到全局用户表，会让“甲团队管理员、乙团队普通成员”难以表达。文档同时存 tenant_id 与 owner_id，避免依赖当前登录用户去猜测历史归属；用户离开团队时，业务必须明确文档移交或保留策略。

chunks 的顺序用 position，而不是依赖查询返回的自然顺序。唯一约束保证同一文档不会产生两个相同位置的片段；重新切分时可以引入版本维度，例如 document_version_id，避免新旧片段混杂。这是业务演进时新增实体的理由，比继续向一个 JSON 数组塞历史数据更清晰。

示例没有自动级联删除文档，是为了让删除策略成为显式决定。知识库删除可能还要清理对象文件、向量索引和审计记录，数据库级联并不能完成这些外部副作用。只在从属对象确实没有独立生命周期时考虑 CASCADE，并配套验证清理范围。

建模从业务事实开始，而不是从页面字段开始#

“团队文档与任务助手”的列表页可能展示标题、团队名、上传者、片段数量和最近任务状态，但它们不一定属于同一张表。标题描述文档，团队名描述租户，上传者来自成员与用户，片段数量可以由解析版本计算，最近任务状态属于任务。把列表展示对象直接当持久表，会让团队改名需要修改每篇文档，让一次任务失败覆盖文档本身的稳定身份。

可以先描述实体的生命周期。用户注册后可以存在于多个团队，退出一个团队不必删除全局账号；文档重命名后仍是同一份文档；上传新版本后旧解析结果可能仍需保留；任务完成后应留下结果和用量。生命周期不同，通常说明它们值得拥有独立记录。页面聚合可以通过查询或服务层完成，不必为了读取方便把所有信息复制成一个无法解释的大对象。

去规范化不是禁止事项，而是需要明确维护责任。缓存片段总数能够减少列表聚合成本，但要知道什么时候更新、失败后如何校正、它是否影响计费或权限。如果只是展示提示，短暂不一致可能可接受；如果用它判断额度，错误成本就更高。先保存可追溯的原始事实，再为已观察到的读取瓶颈增加冗余，通常更容易验证。

一对多、多对多与复合键的推导#

一个团队有多个文档，一个文档属于一个团队，是一对多；一个用户加入多个团队，一个团队又有多个用户，是多对多。成员关系不是只保存两列的技术连接表，它还拥有角色、加入时间、邀请来源和状态等业务属性。把它当实体之后，权限查询与离职流程就有了自然位置。

复合唯一约束表达“在什么范围内唯一”。文档编号可以全局唯一，但版本号通常只在一篇文档内部唯一；片段位置只在一个解析版本内部唯一；成员关系只在一个租户与用户组合内唯一。如果随手给每张表增加 id 而不保留这些业务唯一约束，数据库仍会接受重复成员和重复版本，应用的先查后插在并发中也会失败。

外键列顺序必须与被引用键的定义对应。引用 (tenant_id, document_id) 的目的不是重复保存租户名称，而是让数据库拒绝把甲团队的片段挂到乙团队文档。单列 document_id 外键只能证明文档存在；它不能验证同一行额外保存的 tenant_id 与文档一致。冗余字段一旦用于隔离，就应该有约束维护其一致性。

API 与 SQL 合同：默认值不是已经存在的数据#

CREATE TABLE 定义列类型和约束，默认值只在插入时未提供该列或显式使用 DEFAULT 时参与；用户显式提交 NULL，不会自动替换成默认值。NOT NULL 才限制空值。更新其他字段也不会重新执行 created_at 的默认表达式，修改时间通常需要明确更新或专门机制，不能把两个时间字段都写成 DEFAULT now() 后期待以后自动变化。

GENERATED ALWAYS AS IDENTITY 让数据库生成身份值，通常不接受调用者随意覆盖，和仅仅给整数列写默认值具有不同合同。身份序列不保证没有空洞，失败事务或预分配都可能让编号跳跃，因此不能拿最大 id 减最小 id 来计算总记录数，也不能把连续编号当作业务完成顺序。

CHECK 约束在表达式为 false 时拒绝记录，NULL 产生的未知结果需要配合 NOT NULL。普通 UNIQUE 对 NULL 的处理与“所有空值都一样”这种直觉不同；需要把缺失值视为相同或在部分条件下唯一时，应明确选择相应数据库能力。更简单的设计是先问业务字段是否真的允许缺失，而不是用 NULL 同时表示未知、未填写、已删除和不适用。

pg.Client.query 的参数类型转换是边界的一部分。JavaScript Date 与数据库时间精度不完全相同，大整数和精确小数也不能无损塞进普通 Number。接口可约定 ID 和金额为字符串，时间使用统一格式，服务层再做明确转换。不能因为 ORM 或驱动“帮忙返回了值”就跳过精度验证，尤其不能让展示层格式化后的文本重新作为结算输入。

最小实验：让 NULL 和约束行为变得可见#

保存为 model-invariants.mjs，安装 pg 8，配置专用练习数据库的 DATABASE_URL 后执行 node model-invariants.mjs。它创建一张临时表，用错误代码区分唯一、非空和检查失败，不依赖本机语言下的数据库错误文案。

// model-invariants.mjs
import pg from 'pg';
import assert from 'node:assert/strict';
if(!process.env.DATABASE_URL)throw new Error('请设置 DATABASE_URL');
const db=new pg.Client({connectionString:process.env.DATABASE_URL});await db.connect();
try{
  await db.query(`CREATE TEMP TABLE model_demo(
    id integer PRIMARY KEY,title text NOT NULL CHECK(char_length(title)>0),
    status text NOT NULL DEFAULT 'draft' CHECK(status IN ('draft','ready')),
    external_ref text UNIQUE)`);
  await db.query('INSERT INTO model_demo(id,title) VALUES($1,$2)',[1,'团队文档']);
  assert.equal((await db.query('SELECT status FROM model_demo WHERE id=$1',[1])).rows[0].status,'draft');
  await assert.rejects(()=>db.query('INSERT INTO model_demo(id,title) VALUES($1,$2)',[1,'重复编号']),{code:'23505'});
  await assert.rejects(()=>db.query('INSERT INTO model_demo(id,title) VALUES($1,$2)',[2,null]),{code:'23502'});
  await assert.rejects(()=>db.query('INSERT INTO model_demo(id,title) VALUES($1,$2)',[2,'']),{code:'23514'});
  await db.query('INSERT INTO model_demo(id,title) VALUES($1,$2)',[2,'第二份文档']);
  const semantics=(await db.query('SELECT NULL = NULL AS equal, NULL IS NULL AS missing')).rows[0];
  assert.equal(semantics.equal,null);assert.equal(semantics.missing,true);
  console.log('默认状态为 draft；三种违规被拒绝；NULL 比较需要 IS NULL');
}finally{await db.end();}

预期输出一条说明。两条合法记录都没有 external_ref，普通唯一约束不会因此拒绝第二条；这个事实提示你必须定义外部编号缺失的语义。错误代码比本地化 message 稳定，但对外仍应翻译成用户能理解的领域错误。日志可保留约束名称，帮助定位到底是哪条规则被触发。

时间、金额与 JSONB 的取舍#

timestamptz 表示时间点，数据库可以按会话时区显示同一时刻；它不是存储“北京时间字符串”的专用类型。预约规则可能还需要保存 IANA 时区名称，因为下次执行时间与夏令时规则有关。文档创建时间通常只需要时间点，生日或账期则可能更适合 date。先判断业务概念，再选择类型，避免所有时间都用一串任意格式文本。

额度如果以整数 token 或点数计量，可以采用明确范围的整数；货币涉及小数和币种，适合精确十进制或最小货币单位，并单独保存币种。不能把不同币种金额相加后只返回一个数字。数据库 numeric 能保存精确十进制，不意味着 JavaScript Number 自动获得同等精度，跨语言合同必须保持这种区别。

JSONB 适合确实灵活的附加元数据，如不同解析器返回的可选统计，但不适合替代所有核心关联。经常按 tenant_id、status、owner_id 查询和约束的字段，应让类型和索引容易表达。把权限角色藏在不受约束的 JSON 中，会让拼写错误或类型变化绕过预期逻辑。混合设计可以同时拥有稳定关系字段和有限扩展区，关键是记录扩展区的版本与允许结构。

原练习的完整参考：版本与片段的独立身份#

保存为 document-versions.mjs，安装 pg 8 并设置 DATABASE_URL 后执行。示例允许两个版本都从位置零开始，同时拒绝同一版本重复位置，并让当前版本外键不能指向另一篇文档的版本。全部使用临时表。

完整代码已收录在本章末尾的练习参考答案中；可先阅读说明，再展开复制运行。

这里先创建允许 current_version_id 为空的文档，再创建版本，最后发布指针，解决了“版本需要文档、文档又指向版本”的建立顺序。生产发布还应验证解析成功与索引就绪，再在事务中切换指针。外键只证明版本关联正确，不证明索引服务已经完成，因此数据库结构与任务状态仍需要合作。

模型迁移与旧客户端并存#

数据库已经有真实数据时，修改 schema 不是简单保存一个接口类型。给有历史记录的表新增必填字段，需要决定旧行如何填充；删除或重命名列，需要考虑旧服务实例是否还在读取。一个常见过程是先增加兼容字段，让新代码能够读写，分批回填旧数据，验证完成后再收紧约束或删除旧字段。具体步骤取决于数据库版本、表规模和部署方式，不能把开发库一次 ALTER 成功当作线上无风险。

迁移文件应描述从旧状态到新状态的动作，目标模型文档则描述完成后的结构。两者目的不同：重新创建空库可以按最终结构建表，升级已有库必须处理历史数据。回滚也不是总能简单执行反向 SQL，特别是删除列以后原数据可能已经无法恢复。对重要数据应先设计备份与恢复验证，再执行不可逆变化。

软删除会改变唯一性与默认查询。文档删除后是否允许同团队重新使用相同外部编号，历史任务是否仍能引用旧记录，恢复删除是否与新记录冲突，都应写清。可以使用合适的部分唯一索引表达“仅活动记录唯一”，但应用查询也必须一致处理删除状态。不能只加 deleted_at 就认为生命周期已经完整。

调试与模型验收#

建模验收不止插入一条合法记录。要主动插入重复成员、跨租户片段、负数位置、未知状态和缺失必填字段，确认由预期约束拒绝。然后检查错误发生前是否已经写入其他记录，必要时用事务保护整体动作。约束名称清楚时，异常日志可以直接说明触发了哪条业务规则。

观察查询需求也能反向检验模型。如果每个页面都需要从几十个 JSON 路径里抽同一字段，可能说明稳定字段被错误地藏起来；如果更新团队名要修改很多文档记录，可能是冗余事实缺少维护方案。不要为了追求“完全规范化”把所有展示都变成难以维护的超长关联，也不要为了一个列表方便放弃长期一致性。模型是围绕业务变化与访问模式的选择，不是背几条数据库口号。

引用当前事实还是保存历史快照#

任务记录关联用户编号，可以在查询时显示用户当前名字；但审计有时需要保留操作发生时的显示名、角色或文档版本。两种要求不同，不能简单认为重复保存名称一定错误。应把快照字段命名清楚，并说明它是当时的事实，不随着后续改名自动更新。反过来，不应把快照角色拿来判断用户现在是否有权访问，当前授权仍来自现有成员关系。

团队文档助手的模型调用记录还可能保存模型标识、参数版本、提示模板版本与文档版本。只有保存足够的输入身份，后来才知道摘要为何变化。未必需要无限保留完整私有正文，可以根据产品保留政策保存受控引用和必要摘要。可追踪性与数据最小化需要共同设计，而不是默认把每次请求的全部内容永久复制一份。

枚举约束并不自动实现状态机#

CHECK(status IN ('pending','running','done','failed')) 只能限制允许出现哪些值，不能限制 pending 是否可以直接跳到 done，也不能保证已经完成的任务不会被旧工作者改回 running。状态转换需要带当前状态或版本的条件更新，必要时配合租约令牌和事务。数据模型定义合法状态集合，业务操作定义合法转换路径。

可以给关键状态记录发生时间与原因，让异常恢复有证据。例如 failed 应有可解释的错误代码，done 应关联实际结果，cancelled 应说明由谁发起。不要为了满足非空字段随意填一个当前时间；字段的存在应对应真实事件。这样后台任务监控、用户状态页和后续统计才能描述同一件事。

边界、练习与参考解答#

CHECK 表达式遇到 NULL 可能得到未知值，不能替代 NOT NULL。外键不会自动给引用方每个列建立索引，应结合实际查询和删除路径设计。软删除也不是万能默认值：加 deleted_at 后，唯一约束是否允许重新创建、默认查询是否排除删除行、审计是否仍能定位，都需要明确规则。

练习：允许一篇文档多个解析版本，每个版本的片段位置从零开始。提示：先定义“版本”是否独立被引用，再决定唯一约束中需要哪些字段；不要简单删除原来的去重约束。

// document-versions.mjs
import pg from 'pg';
import assert from 'node:assert/strict';
if(!process.env.DATABASE_URL)throw new Error('请设置 DATABASE_URL');
const db=new pg.Client({connectionString:process.env.DATABASE_URL});await db.connect();
try{
  await db.query(`
    CREATE TEMP TABLE version_documents(tenant_id integer,id integer,current_version_id integer,
      PRIMARY KEY(tenant_id,id));
    CREATE TEMP TABLE document_versions(tenant_id integer,document_id integer,id integer,
      version_number integer NOT NULL CHECK(version_number>0),
      PRIMARY KEY(tenant_id,id),UNIQUE(tenant_id,document_id,version_number),
      UNIQUE(tenant_id,document_id,id),
      FOREIGN KEY(tenant_id,document_id) REFERENCES version_documents(tenant_id,id));
    CREATE TEMP TABLE version_chunks(tenant_id integer,version_id integer,position integer CHECK(position>=0),
      content text NOT NULL CHECK(char_length(content)>0),PRIMARY KEY(tenant_id,version_id,position),
      FOREIGN KEY(tenant_id,version_id) REFERENCES document_versions(tenant_id,id));
    ALTER TABLE version_documents ADD FOREIGN KEY(tenant_id,id,current_version_id)
      REFERENCES document_versions(tenant_id,document_id,id);
    INSERT INTO version_documents VALUES(1,42,NULL),(2,99,NULL);
    INSERT INTO document_versions VALUES(1,42,100,1),(1,42,101,2),(2,99,200,1);
  `);
  const insert='INSERT INTO version_chunks(tenant_id,version_id,position,content) VALUES($1,$2,$3,$4)';
  await db.query(insert,[1,100,0,'旧版第一段']);
  await db.query(insert,[1,101,0,'新版第一段']);
  await assert.rejects(()=>db.query(insert,[1,101,0,'重复位置']),{code:'23505'});
  await assert.rejects(()=>db.query('UPDATE version_documents SET current_version_id=$3 WHERE tenant_id=$1 AND id=$2',[1,42,200]),{code:'23503'});
  await db.query('UPDATE version_documents SET current_version_id=$3 WHERE tenant_id=$1 AND id=$2',[1,42,101]);
  const row=(await db.query(`SELECT c.content FROM version_documents d
    JOIN version_chunks c ON c.tenant_id=d.tenant_id AND c.version_id=d.current_version_id
    WHERE d.tenant_id=$1 AND d.id=$2 ORDER BY c.position`,[1,42])).rows[0];
  assert.equal(row.content,'新版第一段');
  console.log('不同版本可复用位置；重复片段被拒绝；当前版本只指向本篇文档');
}finally{await db.end();}

可验证的验收标准#

合法文档插入成功；跨租户 owner 被拒绝；重复成员关系或片段位置被唯一约束拒绝；空标题与负位置被检查约束拒绝；表中不存模型密钥或明文密码。能解释每个约束维护哪一条业务不变量，而不是只会复制建表语句。

三个自测问题与答案#

1. 有外键是否就有访问权限？答案：没有，外键维护关联合法性，权限仍需验证发起者身份与允许动作。
2. 为什么数字 ID 有时从 pg 返回字符串？答案：数据库整数范围可能超过 JavaScript 安全整数，保留字符串可避免精度损失。
3. CHECK(length > 0) 是否能拒绝 NULL？答案：不能据此保证，必须同时声明 NOT NULL。

本章验证记录#

编写时使用 Node 22.22.0 对本章全部 3 个 JavaScript 完整文件执行了语法检查。以下文件未连接 PostgreSQL 实际执行，SQL 行为、查询计划与并发保证仍需在专用练习数据库验证：data-model.mjs、model-invariants.mjs、document-versions.mjs。

本章官方参考#

• PostgreSQL Constraints：主键、唯一约束、检查与外键。
• PostgreSQL Data Types：时间、精确数值与 JSON。
• node-postgres Queries：参数化查询。
• node-postgres Data Types：数据库类型与 JavaScript 表示。
• PostgreSQL Error Codes：约束与类型错误代码。