# 数据库设计与迁移 (Database)

---

## 1. 技术选型

| 项 | 选择 | 说明 |
|----|------|------|
| 数据库 | PostgreSQL 15+ | JSONB 支持、RLS、分区表 |
| 连接池 | HikariCP（Spring Boot 默认） | 高性能、低开销 |
| 迁移工具 | Flyway | Forward-only、版本化迁移 |
| ORM | Spring Data JPA + Hibernate | 通过 MapStruct 转换 PO ↔ Domain |

---

## 2. Schema 设计原则

### 2.1 多租户隔离

所有业务表必须包含 `tenant_id BIGINT NOT NULL` 列。所有查询条件必须包含 `tenant_id`，通过 Repository 方法签名强制（如 `findByTenantIdAndId`）。

建议在生产环境启用 PostgreSQL Row-Level Security (RLS) 作为最后防线：

```sql
ALTER TABLE t_order ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON t_order
  USING (tenant_id = current_setting('app.current_tenant')::BIGINT);
```

### 2.2 审计字段

所有表通过 JPA `@MappedSuperclass`（`BaseAuditPO`）自动维护：

| 字段 | 类型 | 说明 |
|------|------|------|
| `create_time` | `TIMESTAMP NOT NULL DEFAULT now()` | 创建时间 |
| `update_time` | `TIMESTAMP NOT NULL DEFAULT now()` | 最后更新时间 |
| `deleted` | `BOOLEAN NOT NULL DEFAULT false` | 软删除标记 |

### 2.3 软删除

业务表使用 `deleted` 字段实现软删除。所有查询默认排除已删除记录（通过 Hibernate `@Where` 或手动条件）。物理删除仅在数据归档场景下使用。

---

## 3. 索引策略

### 3.1 通用规则

每张表的主键使用自增 `BIGSERIAL` 或 `UUID`（根据业务选择）。`tenant_id` 必须出现在复合索引的最左列。常用查询条件组合创建复合索引，遵循最左前缀原则。

### 3.2 t_order 表索引示例

```sql
-- 主键索引（自动）
-- 租户 + 订单号唯一索引
CREATE UNIQUE INDEX uk_order_tenant_no ON t_order(tenant_id, order_no);

-- 租户 + 状态（列表查询）
CREATE INDEX idx_order_tenant_status ON t_order(tenant_id, status);

-- 租户 + 创建时间（分页排序）
CREATE INDEX idx_order_tenant_create_time ON t_order(tenant_id, create_time DESC);
```

### 3.3 索引维护

定期检查未使用的索引：

```sql
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;
```

---

## 4. Flyway 迁移规范

### 4.1 文件命名

```
V{版本号}__{描述}.sql       -- 版本化迁移（不可修改）
R__{描述}.sql               -- 可重复迁移（如视图、存储过程）
```

版本号格式：`V1__create_order_table.sql`、`V2__add_order_status_index.sql`。

描述使用下划线分隔的蛇形命名（snake_case）。

### 4.2 编写规则

每个迁移文件只做一件事（一张表、一个索引、一次 ALTER）。所有 DDL 必须是幂等的（使用 `IF NOT EXISTS`、`IF EXISTS`）。禁止在迁移中使用 `DROP TABLE`（除非有审批的变更单号且附带数据备份计划）。DML 迁移（数据修复）必须包含 `WHERE` 条件，禁止无条件 `UPDATE` / `DELETE`。

### 4.3 不可逆性说明

Flyway 迁移是 forward-only 的。一旦迁移文件被合并到 `main` 分支并在任何环境执行过，就不得修改其内容（会导致 checksum 不匹配）。

若需要回退 schema 变更，必须创建新的迁移文件（新版本号）执行反向 DDL。建议采用 expand-then-contract 模式：先在新迁移中添加新列 → 部署新代码 → 确认稳定后在后续迁移中移除旧列。

### 4.4 迁移测试

所有迁移脚本在 CI 中通过 Testcontainers 验证：自动启动 PostgreSQL 15 容器 → 应用全部迁移 → 运行集成测试。参见 [testing.md](testing.md)。

---

## 5. 连接池配置

| 参数 | dev | staging | prod | 说明 |
|------|-----|---------|------|------|
| `maximumPoolSize` | 5 | 10 | 20 | 最大连接数 |
| `minimumIdle` | 2 | 5 | 10 | 最小空闲连接 |
| `connectionTimeout` | 30000ms | 20000ms | 10000ms | 获取连接超时 |
| `idleTimeout` | 600000ms | 300000ms | 300000ms | 空闲连接存活时间 |
| `maxLifetime` | 1800000ms | 1800000ms | 1800000ms | 连接最大生命周期 |

> **注意**：`maximumPoolSize` × Pod 副本数不得超过数据库 `max_connections`。
> 建议预留 20% 给运维工具和 Flyway 迁移。

---

## 6. 数据备份策略（建议）

| 类型 | 频率 | 保留期 |
|------|------|--------|
| 全量备份 (pg_dump) | 每日 | 30 天 |
| WAL 归档 | 持续 | 7 天 |
| 逻辑备份 (关键表) | 每周 | 90 天 |

建议使用 PgBackRest 或云厂商托管备份服务。恢复演练至少每季度执行一次。