# 数据库设计规范

> **版本：** v2.0  
> **最后更新：** 2025-11-06  
> **数据库：** PostgreSQL 15+  
> **ORM：** Prisma  
> **适用范围：** 平台层 + 能力层 + 业务模块层

---

## 📋 核心原则

本规范是所有模块数据库设计的基础规范，必须严格遵守。

**设计原则：**
- ✅ 遵循第三范式（3NF）
- ✅ 使用SERIAL作为主键（整数自增，性能更好）
- ✅ 所有表包含created_at和updated_at时间戳
- ✅ 重要表使用软删除（保留deleted_at字段）
- ✅ 外键约束使用ON DELETE CASCADE
- ✅ 敏感字段加密存储（密码使用bcrypt）

---

## 🏗️ Schema隔离策略 ⭐ 最重要

### 为什么需要Schema隔离

**核心原因：**
1. ✅ **模块独立性**：每个业务模块有独立的Schema
2. ✅ **支持独立部署**：可以单独导出某个模块的数据
3. ✅ **支持独立销售**：可以单独交付某个模块
4. ✅ **权限隔离**：可以为不同Schema设置不同权限
5. ✅ **避免命名冲突**：不同模块可以有相同的表名

### Schema命名规范

```
platform_schema       # 平台基础层（全局共享）
aia_schema           # AI智能问答
asl_schema           # AI智能文献
pkb_schema           # 个人知识库
dc_schema            # 数据清洗整理
ssa_schema           # 智能统计分析
st_schema            # 统计分析工具
rvw_schema           # 稿件审查系统
```

### Schema创建

```sql
-- 创建Schema
CREATE SCHEMA IF NOT EXISTS platform_schema;
CREATE SCHEMA IF NOT EXISTS asl_schema;
CREATE SCHEMA IF NOT EXISTS aia_schema;
-- ...其他Schema
```

### 跨Schema依赖规则 ⭐ 必须遵守

**允许的依赖：**
```sql
-- ✅ 允许：业务模块引用platform_schema
CREATE TABLE asl_schema.literature_projects (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES platform_schema.users(id) ON DELETE CASCADE,
  ...
);

-- ✅ 允许：通用能力引用platform_schema
CREATE TABLE platform_schema.llm_usage (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES platform_schema.users(id) ON DELETE CASCADE,
  ...
);
```

**禁止的依赖：**
```sql
-- ❌ 禁止：业务模块之间互相引用
CREATE TABLE ssa_schema.analysis_projects (
  id SERIAL PRIMARY KEY,
  -- 错误！不能引用其他业务模块
  literature_project_id INTEGER REFERENCES asl_schema.literature_projects(id)
);

-- ❌ 禁止：platform_schema反向依赖业务模块
CREATE TABLE platform_schema.users (
  id SERIAL PRIMARY KEY,
  -- 错误！platform_schema不能依赖业务模块
  current_project_id INTEGER REFERENCES asl_schema.literature_projects(id)
);
```

**正确做法：**
```
跨模块数据关联在应用层处理，不在数据库层！

方式1：通过user_id关联
- 两个模块都引用platform_schema.users
- 在应用层通过user_id查询两个模块的数据
- 在应用层组装数据

方式2：存储业务ID字符串
- 在表中存储其他模块的业务ID（VARCHAR）
- 不建立外键关系
- 在应用层验证ID的有效性
```

---

## 📝 命名规范

### 表命名

**规则：**
- 小写字母
- 下划线分隔
- 复数形式
- Schema前缀（查询时使用）

**示例：**
```sql
-- ✅ 正确
CREATE TABLE asl_schema.literature_projects (...);
CREATE TABLE platform_schema.users (...);
CREATE TABLE pkb_schema.knowledge_bases (...);

-- ❌ 错误
CREATE TABLE ASLSchema.LiteratureProject (...);  -- 驼峰
CREATE TABLE asl_schema.project (...);           -- 单数
CREATE TABLE literature-projects (...);          -- 使用连字符
```

### 字段命名

**规则：**
- 小写字母
- 下划线分隔
- 语义清晰

**示例：**
```sql
-- ✅ 正确
user_id
created_at
project_name
is_active

-- ❌ 错误
userId        -- 驼峰
createdat     -- 没有下划线
prjName       -- 缩写不清晰
```

### 索引命名

**规则：** `idx_表名_字段名`

**示例：**
```sql
-- ✅ 正确
CREATE INDEX idx_users_email ON platform_schema.users(email);
CREATE INDEX idx_projects_user_id ON asl_schema.literature_projects(user_id);
CREATE INDEX idx_projects_user_status ON asl_schema.literature_projects(user_id, status);

-- ❌ 错误
CREATE INDEX user_email_idx ...        -- 顺序错误
CREATE INDEX index_on_email ...        -- 名称不清晰
```

### 外键命名

**规则：** `fk_表名_关联表名`

**示例：**
```sql
-- ✅ 正确
CONSTRAINT fk_projects_users 
  FOREIGN KEY (user_id) REFERENCES platform_schema.users(id);

CONSTRAINT fk_items_projects
  FOREIGN KEY (project_id) REFERENCES asl_schema.literature_projects(id);

-- ❌ 错误
CONSTRAINT user_fk ...                -- 名称不清晰
CONSTRAINT foreign_key_users ...      -- 太长
```

---

## 📊 通用字段 ⭐ 必须包含

### 所有表必须包含

```sql
CREATE TABLE xxx_schema.table_name (
  -- 主键（必须）
  id SERIAL PRIMARY KEY,
  
  -- 业务字段
  ...
  
  -- 时间戳（必须）
  created_at TIMESTAMP DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW() NOT NULL
);
```

### 重要表应包含（软删除）

```sql
CREATE TABLE xxx_schema.important_table (
  id SERIAL PRIMARY KEY,
  
  -- 业务字段
  ...
  
  -- 软删除字段（可选，重要表建议添加）
  deleted_at TIMESTAMP,
  
  -- 时间戳（必须）
  created_at TIMESTAMP DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW() NOT NULL
);

-- 查询时过滤已删除数据
SELECT * FROM xxx_schema.important_table WHERE deleted_at IS NULL;
```

### 用户关联表必须包含

```sql
CREATE TABLE xxx_schema.user_related_table (
  id SERIAL PRIMARY KEY,
  
  -- 用户外键（必须）
  user_id INTEGER REFERENCES platform_schema.users(id) ON DELETE CASCADE NOT NULL,
  
  -- 业务字段
  ...
  
  -- 时间戳（必须）
  created_at TIMESTAMP DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW() NOT NULL
);
```

---

## 🔍 索引设计规范

### 必须添加索引的字段

**1. 主键**
```sql
-- 自动创建，无需手动添加
id SERIAL PRIMARY KEY
```

**2. 外键字段**
```sql
-- 必须添加，提高JOIN性能
CREATE INDEX idx_projects_user_id ON asl_schema.literature_projects(user_id);
```

**3. 常用查询字段**
```sql
-- status（状态字段，常用于WHERE）
CREATE INDEX idx_projects_status ON asl_schema.literature_projects(status);

-- created_at（时间字段，常用于排序）
CREATE INDEX idx_projects_created_at ON asl_schema.literature_projects(created_at DESC);
```

**4. 唯一约束字段**
```sql
-- email等唯一字段
CREATE UNIQUE INDEX idx_users_email ON platform_schema.users(email);
```

### 复合索引

**规则：**
- 高频组合查询使用复合索引
- 最常查询的字段放在前面
- 复合索引最多3个字段

**示例：**
```sql
-- ✅ 正确：user_id + status 组合查询
CREATE INDEX idx_projects_user_status 
  ON asl_schema.literature_projects(user_id, status);

-- 可以优化以下查询：
-- WHERE user_id = ? AND status = ?
-- WHERE user_id = ?  （只用前缀）

-- ❌ 错误：字段过多
CREATE INDEX idx_projects_complex
  ON asl_schema.literature_projects(user_id, status, created_at, updated_at);
```

---

## 🔗 外键约束规范

### ON DELETE策略

**规则：**
```sql
-- 用户删除时，级联删除关联数据
FOREIGN KEY (user_id) 
  REFERENCES platform_schema.users(id) 
  ON DELETE CASCADE;

-- 父记录删除时，级联删除子记录
FOREIGN KEY (project_id) 
  REFERENCES asl_schema.literature_projects(id) 
  ON DELETE CASCADE;

-- 特殊情况：不能删除
FOREIGN KEY (parent_id) 
  REFERENCES xxx_schema.parent_table(id) 
  ON DELETE RESTRICT;  -- 有子记录时禁止删除
```

### 外键索引

**规则：** 所有外键必须添加索引

```sql
-- 创建外键
ALTER TABLE asl_schema.literature_items
  ADD CONSTRAINT fk_items_projects
  FOREIGN KEY (project_id) REFERENCES asl_schema.literature_projects(id)
  ON DELETE CASCADE;

-- 必须添加索引
CREATE INDEX idx_items_project_id ON asl_schema.literature_items(project_id);
```

---

## ⚡ 性能优化规范

### 大表分区（可选）

**适用场景：** 年增长 > 100万记录

```sql
-- 按月分区（如llm_usage表）
CREATE TABLE platform_schema.llm_usage (
  id SERIAL,
  user_id INTEGER NOT NULL,
  created_at TIMESTAMP NOT NULL,
  ...
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE platform_schema.llm_usage_2025_11 
  PARTITION OF platform_schema.llm_usage
  FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
```

### 数据归档策略

```sql
-- 历史数据归档（如1年前的日志）
CREATE TABLE platform_schema.admin_logs_archive (
  LIKE platform_schema.admin_logs INCLUDING ALL
);

-- 定期归档
INSERT INTO platform_schema.admin_logs_archive
SELECT * FROM platform_schema.admin_logs
WHERE created_at < NOW() - INTERVAL '1 year';

DELETE FROM platform_schema.admin_logs
WHERE created_at < NOW() - INTERVAL '1 year';
```

---

## 🔒 安全规范

### 敏感字段加密

```sql
-- 密码字段
password VARCHAR(255) NOT NULL  -- 使用bcrypt加密，存储哈希值

-- API Key字段
api_key_encrypted TEXT NOT NULL  -- 使用AES-256加密

-- 个人敏感信息
phone_encrypted VARCHAR(255)     -- 手机号加密
id_card_encrypted VARCHAR(255)   -- 身份证号加密
```

### 数据脱敏

```sql
-- 日志中不记录敏感字段
-- 开发/测试环境使用脱敏数据
UPDATE platform_schema.users 
SET 
  email = CONCAT('user', id, '@example.com'),
  phone = '138****0000'
WHERE environment = 'development';
```

---

## 📋 数据类型选择

### 常用字段类型

| 用途 | 推荐类型 | 说明 |
|------|---------|------|
| 主键 | SERIAL | 整数自增 |
| 外键 | INTEGER | 与主键类型一致 |
| 短文本 | VARCHAR(N) | N<500，如姓名、标题 |
| 长文本 | TEXT | 无长度限制，如描述、内容 |
| 布尔值 | BOOLEAN | true/false |
| 日期时间 | TIMESTAMP | 精确到毫秒 |
| 金额 | DECIMAL(10,2) | 避免精度问题 |
| JSON | JSONB | 支持索引，性能更好 |

### 字段长度建议

```sql
-- 短文本
name VARCHAR(100)           -- 姓名
title VARCHAR(200)          -- 标题
email VARCHAR(255)          -- 邮箱
phone VARCHAR(20)           -- 手机号

-- 状态枚举
status VARCHAR(20)          -- active, inactive, deleted
role VARCHAR(20)            -- user, admin

-- 长文本
description TEXT            -- 描述
content TEXT               -- 内容
```

---

## ✅ 检查清单

**设计新表时必须检查：**
- [ ] 表名符合命名规范（小写+下划线+复数）
- [ ] 使用正确的Schema（platform/aia/asl/pkb等）
- [ ] 包含id主键（SERIAL PRIMARY KEY）
- [ ] 包含created_at和updated_at时间戳
- [ ] 用户关联表包含user_id外键
- [ ] 所有外键都有ON DELETE策略
- [ ] 所有外键都添加了索引
- [ ] 常用查询字段添加了索引
- [ ] 外键约束符合跨Schema依赖规则
- [ ] 敏感字段已加密存储

---

## 🔗 相关文档

**总览：**
- [数据库全局视图](./03-数据库全局视图.md) ⭐ 查看所有Schema和表

**模板：**
- [数据库设计模板](../_templates/数据库设计-模板.md)

**各模块设计：**
- [平台基础层](../01-平台基础层/README.md)
- [通用能力层](../02-通用能力层/README.md)
- [业务模块层](../03-业务模块/README.md)

---

**最后更新：** 2025-11-06  
**维护人：** 技术架构师  
**版本：** v2.0