# 房屋租赁 SaaS 平台 · 数据库设计

> **本文档的所有 DDL 均已在本机 PostgreSQL 17.11 真实执行通过，并通过 15 项数据验证 + 并发压测。**
> 实测对象统计：**86 张表 / 248 个索引 / 191 个外键 / 66 个 CHECK 约束 / 1 个排他约束**

---

## 一、设计哲学：四层数据分层

设计的起点不是「有哪些表」，而是**数据性质分层**。租赁系统最常见的失败，
是把生命周期完全不同的数据塞进同一张表。

| 层 | 数据 | 载体 | 写入特征 |
| --- | --- | --- | --- |
| **主数据** | 租户、楼盘、房间、租客、设备 | PostgreSQL | 低频、强一致 |
| **事务数据** | 合同、账单、收款、工单 | PostgreSQL + 分区 | 高频、有状态机 |
| **时序数据** | IoT 遥测、AR 行为、Agent 日志 | **ClickHouse**（旁路） | 极高频、只追加 |
| **非结构化** | 图片、视频、AR 模型 | **对象存储 + 元数据表** | 大对象、内容寻址 |

> 这条分层线是整个设计的**红线**：时序数据进主库 = 主库迟早被压垮；
> 文件字节进数据库 = 备份恢复必然失控。

---

## 二、模块与表清单

### 域 1 · 平台与租户（10 表）

| 表 | 作用 | 关键设计 |
| --- | --- | --- |
| `tenant` | 租户 | `isolation_mode` 预留隔离升级通道 |
| `tenant_config` | 租户配置 | 键值化，避免为差异加列 |
| `user_account` | 系统账号 | **与租客严格分开**（见下方设计债说明） |
| `role` / `permission` / `role_permission` | RBAC | 支持平台内置 + 租户自定义 |
| `user_tenant_binding` / `user_role_binding` | 绑定 | 一人可服务多租户 |
| `user_account.email` 索引 | — | 部分索引（仅非空） |
| `data_scope_rule` | 数据权限 | 按楼盘收窄可见范围 |

> **⚠️ 最常见的设计债：把「系统操作者」和「租客」合成一张 user 表。**
> 后果：租客表里混进几百个员工账号，权限模型与租赁关系彻底纠缠，
> 后期拆分要动几十张表的外键。本设计从第一天就分开：
> `user_account`（能登录后台的人） vs `tenant_person`（签合同的人）。

### 域 2 · 资产与房源（12 表）

层级：`property` → `building` → `unit` → `floor` → `room` → `bed`

| 表 | 作用 | 关键设计 |
| --- | --- | --- |
| `room` | **核心：可出租最小单元** | `status` 五态机、`extra_attrs` JSONB 扩展 |
| `room_status_log` | 状态变更留痕 | 审计必需 |
| `room_calendar` | **短租间夜库存** | `(room_id, biz_date)` 唯一 + 条件更新防超卖 |
| `bed` | 床位 | 合租场景 |
| `rate_plan` / `rate_policy` | 价格方案与策略 | 结构化 JSONB 表达连住折扣等 |
| `parking_space` | 车位 | 也是可租资产 |
| `amenity` / `room_amenity` | 配套设施 | 字典 + 关联 |

**为什么 `room` 是核心而不是 `building`？**
一切租赁、账单、设备、AR 场景最终都挂在**房间**上。
若以楼栋为主轴，收租和水电就无处安放。

### 域 3 · 客户与租客（7 表）

| 表 | 作用 | 关键设计 |
| --- | --- | --- |
| `tenant_person` | 租客自然人 | **全局实体，不做租户隔离** |
| `tenant_org` | 企业租客 | 统一社会信用代码唯一 |
| `guarantor` | 担保人 | — |
| `credit_profile` | 信用档案 | 黑名单、逾期统计 |
| `contact_record` | 沟通记录 | 为 Agent 准备语料 |

**`tenant_person` 的三个关键字段设计：**

```sql
id_no_enc   text          -- 证件号加密存储（应用层加密）
id_no_hash  varchar(128)  -- 不可逆哈希，用于全局查重
```

**为什么这样设计？** 既要**全局查重**（防重复身份、支持跨公司风控），
又要**不泄露明文**（合规要求）。哈希解的正是这个矛盾：
数据库能判断「是不是同一个人」，但拿不到证件号本身。

### 域 4 · 合同与租赁（8 表）

| 表 | 作用 | 关键设计 |
| --- | --- | --- |
| `contract` | 合同主表 | 七态机，只增不改 |
| `contract_party` | 当事人 | 支持共租人、责任分摊比例 |
| `contract_room` | 合同-房间 | 一合同多房间，各自租金 |
| `contract_version` | **版本快照** | 不可变 JSONB 全量快照 |
| `contract_term` | 条款 | 结构化，按版本号隔离 |
| `contract_change` | 变更单 | 续租/换房/改价/提前退租 |
| `contract_deposit` | 押金 | 应收/实收/退还/扣除四账 |
| `checkin_record` | 交割 | 水电底数 + 物品清单 JSONB |

**核心原则：合同只增不改。**
任何变更都产生 `contract_change` + 新增 `contract_version` 快照。
如果直接 `UPDATE contract`，那么「2026 年 3 月那份合同长什么样」这个
法律问题就永远无法回答 —— 这在租赁纠纷中是致命的。

### 域 5 · 计费与收租（13 表）

| 表 | 作用 | 关键设计 |
| --- | --- | --- |
| `bill` | 账单 | 六态机 + `idempotency_key` 防重 |
| `bill_item` | 明细 | 计量类含起止读数 |
| `payment` | 收款流水 | `out_trade_no`/`trade_no` 防重复入账 |
| **`payment_allocation`** | **核销明细** | **多对多桥梁（见下）** |
| `meter` / `meter_reading` | 仪表与抄表 | 留在 PG，因参与计费 |
| `refund` / `invoice` | 退款/发票 | — |
| `dunning_record` | 催缴 | 支持 Agent 自动催收 |
| `late_fee_rule` | 滞纳金规则 | 阶梯可配置 |

> ## 🔑 本节最重要的设计：`payment_allocation`
>
> **新人最常犯的致命错误：给 `bill` 加一个 `payment_id` 字段。**
>
> 这样做的后果：
> - 租客一次性付 3 个月房租（1 笔钱 → 3 张账单）→ **无法表达**
> - 租客分 2 次付清 1 张账单（2 笔钱 → 1 张账单）→ **无法表达**
> - 租客付了 2500，账单 3000（部分核销）→ **无法表达**
>
> 租赁业务的真实情况是：`payment` 与 `bill` 是**多对多**。
> 必须用 `payment_allocation` 连接，并记录**每笔核销金额**。
>
> 这张表还有 `alloc_type='reversal'` 用于冲正 —— 收错钱要能回滚，
> 而回滚不能删除原记录（审计要求）。

### 域 6 · 物业与工单（9 表）

| 表 | 作用 | 关键设计 |
| --- | --- | --- |
| `work_order` | 工单 | 八态机 + SLA 时限快照 |
| `work_order_flow` | **流转留痕** | 唯一的过程真相 |
| `work_order_item` | 材料/工时 | — |
| `dispatch_record` | 派单 | 支持抢单/派单/自动 |
| `staff` | 服务人员 | 技能标签 + 负载（派单依据） |
| `sla_rule` | SLA 规则 | — |
| `inspection_task` / `inspection_record` | 巡检 | 异常自动生成工单 |

**为什么必须有 `work_order_flow`？**
只更新 `work_order.status` 会丢失「谁在几点做了什么」。
而 SLA 考核、责任划分、客诉复盘**全部依赖过程记录**。
`work_order_flow` 还记录了每一步的 `duration_min`，
这使「平均响应时长」这类指标可以随时算出，无需埋点。

### 域 7 · 数字家庭 IoT（11 表）

| 表 | 作用 | 关键设计 |
| --- | --- | --- |
| `device` | 设备主数据 | — |
| `device_model` | 型号能力定义 | `capabilities` JSONB |
| `device_binding` | **设备-房间绑定** | 含生效时间段，退租自动解绑 |
| **`device_shadow`** | **当前状态快照** | 一设备一行，高频覆盖写 |
| `device_command` | 指令下发 | `request_id` 幂等 |
| `scene` / `scene_action` | 场景联动 | — |
| `access_grant` | 门锁临时权限 | 保洁/维修/看房的时效密码 |

> **`device_shadow`（影子）与遥测必须分开。**
>
> | | device_shadow | 遥测历史 |
> | --- | --- | --- |
> | 内容 | 此刻状态 | 历史轨迹 |
> | 行数 | 固定（= 设备数） | 无限增长 |
> | 查询 | 点查 O(1) | 时间范围聚合 |
> | 载体 | **PostgreSQL** | **ClickHouse** |
>
> 混在一起的后果：查「这台空调现在开着吗」变成在百亿行里做全表扫描。

### 域 8 · 多媒体与 AR 看房（10 表）

| 表 | 作用 | 关键设计 |
| --- | --- | --- |
| `media_asset` | 媒体元数据 | **只存元数据，字节在对象存储** |
| `media_variant` | 多规格派生 | 缩略图/多清晰度/全景瓦片 |
| `media_binding` | 多态绑定 | 一媒体可绑房间/合同/工单 |
| `ar_scene` | AR 场景 | 一房间多场景/多期 |
| `ar_scene_version` | 场景版本 | 重新拍摄保留历史 |
| `ar_scene_node` | 场景分区 | 客厅/卧室，含首视角相机 |
| **`ar_hotspot`** | **AR 热点** | **可关联真实 `device_id`** |
| `ar_camera_path` | 相机轨迹 | 漫游路线 |
| `vr_session` | 看房行为 | 停留时长、热点点击 |

**AR 看房的数据本质是三块，必须分开存：**

1. **全景图/立方体贴图** → 对象存储（大文件，按瓦片切分）
2. **glTF/GLB 三维模型** → 对象存储（含 Draco 压缩，带版本号）
3. **空间锚点元数据**（热点坐标、相机轨迹、房间尺寸）→ **PostgreSQL**

> ## 🔑 为什么锚点必须进数据库，不能烧进模型文件？
>
> 如果热点坐标只存在 glTF 里，那么「点击客厅的空调热点，显示它
> 真实的运行状态和本月耗电」这个**杀手级联动就做不了** ——
> 因为模型文件是静态的，无法查询后端。
>
> 本设计的 `ar_hotspot` 带 `device_id` / `room_id` / `target_node_id`，
> 使热点成为**活的业务入口**：看房时点热点 → 查 `device_shadow` →
> 展示"这台空调当前 26℃，本月耗电 45 度"。
> **这是把 AR 从「炫技」变成「销售工具」的关键设计。**

### 域 9 · Agent 能力（10 表）

| 表 | 作用 | 关键设计 |
| --- | --- | --- |
| `agent_definition` | Agent 定义 | 角色 + 工具集 + 模型配置 |
| `agent_tool` | 工具注册表 | JSON Schema 供 function calling |
| `agent_session` | 会话 | 关联业务上下文 |
| `agent_message` | 消息 | token 用量、时延 |
| **`agent_tool_call`** | **工具调用审计** | **入参出参全记录** |
| `agent_memory` | 长期记忆 | 结构化 + **vector(1024)** |
| `knowledge_doc` / `knowledge_chunk` | 知识库 | **pgvector + HNSW** |
| `prompt_template` | 提示词版本化 | — |

**Agent 落地的三个数据要点：**

1. **工具调用必须可审计。** `agent_tool_call` 记录入参出参、
   是否 `is_write`、谁 `confirmed_by` 确认。没有这张表，
   Agent 误删数据、误退款将**无法追责**，业务方也不敢让它碰写操作。
2. **写操作需人工确认。** `agent_tool.need_confirm` 字段 +
   `agent_tool_call.confirmed_by` 构成了「AI 提议、人批准」的安全链路。
3. **向量不必上专用库。** 本机 PostgreSQL 已装 **pgvector 0.8.7**，
   知识库百万切片以内用 HNSW 索引完全够用，**省掉一套独立向量库的运维**。

```sql
CREATE INDEX idx_knowledge_chunk_embedding ON knowledge_chunk
  USING hnsw (embedding vector_cosine_ops) WITH (m=16, ef_construction=64);
```
> 维度按 **1024** 设定，与本机 `Qwen3-Embedding-4B` 对齐。
> 换嵌入模型必须同步改维度并重建索引 —— 这个约束要写进团队规范。

---

## 三、四道数据正确性防线（全部实测验证）

数据库设计的好坏，不在于"表建出来了"，而在于**能不能挡住错误数据**。
以下四项是本设计最硬的部分，且**都经过真实测试**。

### 防线 1：防重复出租 —— PostgreSQL 排他约束

```sql
CREATE TABLE room_occupancy (
  room_id  bigint,
  period   daterange,
  ...
  EXCLUDE USING gist (room_id WITH =, period WITH &&)
);
```

**实测结果：**
```
重叠插入 → conflicting key value violates exclusion constraint
           "room_occupancy_room_id_period_excl"
           Key (room_id, period)=(1, [2026-12-01,2027-06-30))
           conflicts with (1, [2026-11-01,2027-10-31))
不重叠插入 → 成功
```

**为什么不能只靠应用层校验？**
两个中介同时给同一房间签合同，应用层"查一下有没有占用"在并发下必然穿透。
只有数据库的排他约束能在**时序层面**杜绝。

### 防线 2：防短租超卖 —— 原子条件更新

```sql
UPDATE room_calendar
   SET sold_qty = sold_qty + p_qty
 WHERE room_id = ? AND biz_date >= ? AND biz_date < ?
   AND is_closed = false
   AND sold_qty + p_qty <= total_qty      -- ← 原子的余量判断
RETURNING 1;
-- 影响行数不足 → 抛异常，绝不静默成功
```

**🔑 关键点：必须主动检查影响行数并报错。**

实测反向对照实验证明：即使条件更新兜住了数据（`sold_qty` 没超），
如果不检查影响行数，业务层会**以为下单成功**（因为 UPDATE 不报错），
而库存其实没扣到。这种"静默失败"比超卖更难排查。

本设计的 `fn_hold_room_nights` 显式断言：
```sql
IF v_affected <> v_days THEN
  RAISE EXCEPTION '库存不足：需要 % 天，实际可售 % 天', v_days, v_affected;
END IF;
```

**并发压测实测结果（20 并发抢 1 间库存）：**
```
成功抢占数 : 1    （期望 1）
失败数     : 19   （期望 19）
最终已售数 : 1    （期望 1）
✅ 未发生超卖
```

### 防线 3：核销一致性 —— 触发器维护汇总

账单的 `amount_paid` **不能由应用层手动累加**（漏更新就永远对不上账）。
本设计用触发器从 `payment_allocation` 自动汇总：

```sql
CREATE TRIGGER trg_alloc_sync_bill
  AFTER INSERT OR UPDATE OR DELETE ON payment_allocation
  FOR EACH ROW EXECUTE FUNCTION trg_sync_bill_paid();
```

同时自动推进账单状态机（`pending` → `partial` → `paid`），
并维护 `payment.allocated_amount` / `unallocated_amount`。

**实测结果：**
```
初始     : 应收 3000, 已收 0,    status=pending
收 2000  : 应收 3000, 已收 2000, status=partial   ✅
再收 1000: 应收 3000, 已收 3000, status=paid, paid_at 非空  ✅
```

### 防线 4：防超额核销 / 防重复入账

```sql
-- 超额核销拦截（实测：账单 3000，再核销 500 → 累计 3500）
ERROR: 核销超额：账单应收 3000.00，减免 0.00，本次累计核销 3500.00

-- 重复支付交易号
ERROR:  duplicate key value violates unique constraint "uq_payment_trade_no"

-- 账单重复生成（幂等键）
ERROR:  duplicate key value violates unique constraint "uq_bill_idempotency"
```

---

## 四、分区策略

**判断标准：单表预估超 5000 万行且以时间为主要查询维度 → 按月 RANGE 分区。**

| 表 | 年增行数 | 处置 | 分区键 |
| --- | --- | --- | --- |
| `bill` | 3000 万~1 亿 | ✅ 月分区 | `period_start` |
| `payment` | 3000 万 | ✅ 月分区 | `paid_at` |
| `payment_allocation` | 5000 万 | ✅ 月分区 | `allocated_at` |
| `room_calendar` | **3.65 亿** | ✅ 月分区 | `biz_date` |
| `agent_message` | 1 亿+ | ✅ 月分区 + 归档 | `created_at` |
| `vr_session` | 1 亿+ | ✅ 月分区 | `started_at` |
| `device_telemetry` | **万亿级** | ❌ **不进 PG** | ClickHouse |

**分区表的三个必须注意的点：**

1. **唯一约束必须包含分区键。** 否则 PostgreSQL 无法保证分区内的唯一性。
   例如 `bill` 的 `UNIQUE (tenant_id, bill_no)` 在按 `period_start` 分区后，
   需改为 `UNIQUE (tenant_id, bill_no, period_start)`。
2. **部分索引（Partial Index）是神器。** 账单表里 95% 是已缴清的历史数据，
   而逾期扫描只需要扫未缴的：
   ```sql
   CREATE INDEX idx_bill_unpaid ON bill (tenant_id, due_date)
     WHERE status IN ('pending','partial','overdue');
   ```
   这个索引体积可能只有全量索引的 5%，但覆盖了最高频的运维查询。
3. **生产用 `pg_partman` 自动建分区**，不要手写。本机未安装，
   需由运维会话统一部署（按权责边界，基础服务归运维）。

---

## 五、按表给出的索引设计要点

索引不是越多越好 —— 每个索引都在拖慢写入。本设计只建三类索引：

| 类型 | 例子 | 为什么建 |
| --- | --- | --- |
| **租户外键组合** | `(tenant_id, status)` | RLS + 列表查询的绝对主力 |
| **部分索引** | `WHERE status='effective'` | 只索引有价值的那部分 |
| **GIN 索引** | `gin (extra_attrs)`、`gin_trgm_ops` | JSONB 检索、中文模糊搜索 |

**中文模糊搜索的实际做法**（本机已验证扩展可用）：
```sql
CREATE INDEX idx_property_name_trgm ON property USING gin (property_name gin_trgm_ops);
-- 支持：SELECT * FROM property WHERE property_name LIKE '%花园%';
```
> 注意：`pg_trgm` 对中文按 3-gram 切分，效果弱于英文。
> 数据量大后建议房源搜索走 Elasticsearch，PG 只做兜底。

---

## 六、已验证的测试清单

| # | 验证项 | 预期 | 实测 |
| --- | --- | --- | --- |
| 1 | 短租正常占 3 晚 | 成功 | ✅ |
| 2 | 重叠日期再占 | 报错拦截 | ✅ 库存不足：需要3天，可售1天 |
| 3 | 空闲日期可继续卖 | 成功 | ✅ |
| 4 | 账单初始态 | pending/0 | ✅ |
| 5 | 部分核销 | partial/2000 | ✅ |
| 6 | 付清 | paid/3000/有支付时间 | ✅ |
| 7 | 收款单核销汇总 | 2000/0、1000/0 | ✅ |
| 8 | 超额核销 | 报错拦截 | ✅ 累计3500 > 应收3000 |
| 9 | 重复支付交易号 | 唯一索引拦截 | ✅ |
| 10 | 账单幂等键重复 | 唯一索引拦截 | ✅ |
| 11 | 房间首次占用 | 成功 | ✅ |
| 12 | 重叠占用 | EXCLUDE 拦截 | ✅ |
| 13 | 不重叠占用 | 成功 | ✅ |
| 14 | 出租率视图 | 正确计算 | ✅ |
| 15 | 账单账龄视图 | 正确计算 | ✅ |
| **16** | **20 并发抢 1 库存** | **恰好 1 成功** | ✅ **1 成功 / 19 失败 / 未超卖** |
| 17 | RLS 租户隔离 | 各自只见己方 | ✅ |
| 18 | RLS 未设上下文 | 返回 0 行 | ✅ 安全失败 |
| 19 | RLS 注入尝试 | 被拦截 | ✅ 类型检查拦下 |

---

## 七、设计中发现并修正的问题（真实性记录）

设计过程中真实踩到并修正的两处：

1. **合同 CHECK 约束过严。**
   初版写 `CHECK (lessee_person_id IS NOT NULL OR lessee_org_id IS NOT NULL)`，
   导致草稿合同无法保存（起草时还没定承租人）。
   **修正为**：仅非草稿状态强制要求承租主体。
   > 这个 bug 是跑真实数据时暴露的 —— 纸面设计不会发现。

2. **温度测试中函数参数类型不匹配。**
   `fn_hold_room_nights(bigint, unknown, unknown, integer)` 找不到匹配函数，
   需显式 `::date` / `::smallint` 转换。
   > 已写入使用示例，避免团队重复踩坑。

---

## 八、配套文件

| 文件 | 内容 | 状态 |
| --- | --- | --- |
| `DDL/01-平台租户房源.sql` | 域 0-2 | ✅ 执行通过 |
| `DDL/02-租客合同收租.sql` | 域 3-5 | ✅ 执行通过 |
| `DDL/03-工单IoT媒体AR-Agent.sql` | 域 6-9 | ✅ 执行通过 |
| `DDL/04-约束与并发保障.sql` | 排他约束/触发器/视图 | ✅ 执行通过 |
| `DDL/05-真实数据验证.sql` | 15 个数据用例 | ✅ 全部通过 |
| `DDL/06-并发防超卖压测.sh` | 20 并发压测 | ✅ 未超卖 |
| `DDL/07-反向对照实验.sh` | 错误写法对照 | ✅ 已完成 |
| `DDL/08-多租户RLS落地.sql` | RLS 生成器 | ✅ 已验证 |

**执行顺序：01 → 02 → 03 → 04 →（05 验证）→ 08 按需**