05-数据库设计

# 数据库设计

> **文档版本**: v1.0.0
> **创建日期**: 2024-01-25
> **最后更新**: 2024-01-25
> **维护人**: 未来鸟FutureBird码巢企业服务引擎开发组
> **状态**: [最新]

> **搜索标签**: 开放平台, 数据库设计, 表结构, 索引优化

---

## &#128203; 目录

- [1. 表结构设计](#1-表结构设计)
- [2. 数据字典](#2-数据字典)
- [3. 索引设计](#3-索引设计)
- [4. 分区策略](#4-分区策略)

---

## 1. 表结构设计

### 1.1 应用表 (open_apps)

```sql
CREATE TABLE open_apps (
  -- 主键
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- 基本信息
  company_id UUID NOT NULL,
  name VARCHAR(100) NOT NULL,
  app_code VARCHAR(50) UNIQUE,
  app_type VARCHAR(50) NOT NULL,
  description TEXT,
  icon_url VARCHAR(500),
  
  -- 回调配置
  callback_url VARCHAR(500),
  notify_email VARCHAR(255),
  
  -- 安全配置
  ip_whitelist TEXT[],
  domain_whitelist TEXT[],
  
  -- 授权配置
  authorized_companies UUID[],
  authorized_scopes TEXT[],
  
  -- 状态
  status VARCHAR(20) DEFAULT 'pending',
  
  -- 审核信息
  reviewed_at TIMESTAMPTZ,
  reviewed_by UUID,
  review_comment TEXT,
  
  -- 时间戳
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_by UUID NOT NULL
);
```

**字段说明**:

| 字段 | 类型 | 说明 | 约束 |
|------|------|------|------|
| id | UUID | 主键 | PK |
| company_id | UUID | 所属公司 | NOT NULL |
| name | VARCHAR | 应用名称 | NOT NULL |
| app_code | VARCHAR | 应用唯一标识 | UNIQUE |
| app_type | VARCHAR | 应用类型 | NOT NULL |
| description | TEXT | 应用描述 | |
| callback_url | VARCHAR | 回调地址 | |
| ip_whitelist | TEXT[] | IP白名单 | |
| authorized_companies | UUID[] | 授权公司 | |
| status | VARCHAR | 状态 | DEFAULT pending |
| created_at | TIMESTAMPTZ | 创建时间 | DEFAULT NOW() |

### 1.2 API密钥表 (open_api_keys)

```sql
CREATE TABLE open_api_keys (
  -- 主键
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- 关联应用
  app_id UUID NOT NULL REFERENCES open_apps(id) ON DELETE CASCADE,
  
  -- 密钥信息
  key_id VARCHAR(64) NOT NULL,
  key_hash VARCHAR(128) NOT NULL,
  key_encrypted TEXT NOT NULL,
  secret_encrypted TEXT NOT NULL,
  
  -- 环境标识
  environment VARCHAR(20) NOT NULL,
  
  -- 状态
  status VARCHAR(20) DEFAULT 'active',
  
  -- 有效期
  expires_at TIMESTAMPTZ,
  
  -- 使用统计
  last_used_at TIMESTAMPTZ,
  total_requests BIGINT DEFAULT 0,
  
  -- 密钥轮换
  rotated_from UUID,
  rotated_at TIMESTAMPTZ,
  
  -- 时间戳
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

**字段说明**:

| 字段 | 类型 | 说明 | 约束 |
|------|------|------|------|
| id | UUID | 主键 | PK |
| app_id | UUID | 关联应用 | FK |
| key_id | VARCHAR | 密钥ID | NOT NULL, UNIQUE |
| key_hash | VARCHAR | 密钥哈希 | NOT NULL, UNIQUE |
| key_encrypted | TEXT | 加密的API Key | NOT NULL |
| secret_encrypted | TEXT | 加密的API Secret | NOT NULL |
| environment | VARCHAR | 环境 | NOT NULL |
| status | VARCHAR | 状态 | DEFAULT active |
| expires_at | TIMESTAMPTZ | 过期时间 | |

### 1.3 权限配置表 (open_permissions)

```sql
CREATE TABLE open_permissions (
  -- 主键
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- 关联应用
  app_id UUID NOT NULL REFERENCES open_apps(id) ON DELETE CASCADE,
  
  -- 权限信息
  resource VARCHAR(100) NOT NULL,
  action VARCHAR(50) NOT NULL,
  
  -- 权限范围
  scope VARCHAR(20) DEFAULT 'company',
  
  -- 数据约束
  constraints JSONB,
  
  -- 时间戳
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### 1.4 API调用日志表 (open_api_logs)

```sql
CREATE TABLE open_api_logs (
  -- 主键
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- 关联信息
  app_id UUID NOT NULL,
  api_key_id UUID,
  company_id UUID,
  
  -- 请求信息
  request_id VARCHAR(64) NOT NULL,
  method VARCHAR(10) NOT NULL,
  path VARCHAR(500) NOT NULL,
  query_string TEXT,
  request_headers JSONB,
  request_body TEXT,
  
  -- 响应信息
  status_code INT NOT NULL,
  response_body TEXT,
  response_time_ms INT NOT NULL,
  
  -- 客户端信息
  ip_address VARCHAR(45),
  user_agent VARCHAR(500),
  
  -- 错误信息
  error_code VARCHAR(20),
  error_message TEXT,
  
  -- 时间戳
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### 1.5 Webhook订阅表 (open_webhooks)

```sql
CREATE TABLE open_webhooks (
  -- 主键
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- 关联应用
  app_id UUID NOT NULL REFERENCES open_apps(id) ON DELETE CASCADE,
  
  -- 回调配置
  name VARCHAR(100) NOT NULL,
  url VARCHAR(500) NOT NULL,
  
  -- 事件订阅
  events TEXT[] NOT NULL,
  
  -- 安全配置
  secret_encrypted TEXT,
  
  -- 重试配置
  retry_count INT DEFAULT 3,
  retry_interval INT DEFAULT 60,
  
  -- 状态
  status VARCHAR(20) DEFAULT 'active',
  
  -- 统计信息
  success_count BIGINT DEFAULT 0,
  failure_count BIGINT DEFAULT 0,
  last_triggered_at TIMESTAMPTZ,
  
  -- 时间戳
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

### 1.6 Webhook推送日志表 (open_webhook_logs)

```sql
CREATE TABLE open_webhook_logs (
  -- 主键
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- 关联Webhook
  webhook_id UUID NOT NULL REFERENCES open_webhooks(id) ON DELETE CASCADE,
  
  -- 事件信息
  event_id VARCHAR(64) NOT NULL,
  event_type VARCHAR(100) NOT NULL,
  payload JSONB NOT NULL,
  
  -- 推送信息
  status VARCHAR(20) NOT NULL,
  retry_count INT DEFAULT 0,
  next_retry_at TIMESTAMPTZ,
  
  -- 响应信息
  http_status INT,
  response_body TEXT,
  
  -- 错误信息
  error_message TEXT,
  
  -- 时间戳
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

---

## 2. 数据字典

### 2.1 应用类型

| 编码 | 名称 | 说明 |
|------|------|------|
| erp | ERP系统 | 企业资源规划系统 |
| e-commerce | 电商平台 | 电商销售平台 |
| oa | OA系统 | 办公自动化系统 |
| hr | HR系统 | 人力资源管理系统 |
| finance | 财务系统 | 财务核算系统 |
| geo | 地理服务 | 地理位置服务 |
| other | 其他 | 其他类型系统 |

### 2.2 应用状态

| 编码 | 名称 | 说明 |
|------|------|------|
| pending | 待审核 | 新创建，等待审核 |
| approved | 已通过 | 审核通过，可以使用 |
| rejected | 已拒绝 | 审核拒绝 |
| suspended | 已停用 | 管理员停用 |
| deleted | 已删除 | 逻辑删除 |

### 2.3 密钥状态

| 编码 | 名称 | 说明 |
|------|------|------|
| active | 正常 | 可以正常使用 |
| disabled | 已禁用 | 手动禁用 |
| expired | 已过期 | 超过有效期 |
| revoked | 已撤销 | 密钥轮换后撤销 |

### 2.4 权限资源

| 编码 | 名称 | 说明 |
|------|------|------|
| company | 企业 | 企业信息 |
| employee | 员工 | 员工信息 |
| payroll | 工资单 | 工资单数据 |
| order | 订单 | 订单信息 |
| product | 商品 | 商品信息 |
| invoice | 发票 | 发票信息 |

### 2.5 权限操作

| 编码 | 名称 | 说明 |
|------|------|------|
| read | 读取 | 查看数据 |
| write | 写入 | 创建/更新数据 |
| delete | 删除 | 删除数据 |
| admin | 管理 | 完全管理权限 |

---

## 3. 索引设计

### 3.1 应用表索引

```sql
CREATE INDEX idx_open_apps_company ON open_apps(company_id);
CREATE INDEX idx_open_apps_status ON open_apps(status);
CREATE INDEX idx_open_apps_code ON open_apps(app_code);
```

### 3.2 密钥表索引

```sql
CREATE INDEX idx_open_api_keys_app ON open_api_keys(app_id);
CREATE INDEX idx_open_api_keys_env ON open_api_keys(environment);
CREATE INDEX idx_open_api_keys_status ON open_api_keys(status);
```

### 3.3 日志表索引

```sql
CREATE INDEX idx_open_api_logs_app_time ON open_api_logs(app_id, created_at DESC);
CREATE INDEX idx_open_api_logs_request ON open_api_logs(request_id);
CREATE INDEX idx_open_api_logs_status ON open_api_logs(status_code);
```

### 3.4 Webhook索引

```sql
CREATE INDEX idx_open_webhooks_app ON open_webhooks(app_id);
CREATE INDEX idx_open_webhook_logs_webhook ON open_webhook_logs(webhook_id, created_at DESC);
```

---

## 4. 分区策略

### 4.1 日志表分区

```sql
-- 按月创建日志分区
CREATE TABLE open_api_logs_2024_01 PARTITION OF open_api_logs
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE open_api_logs_2024_02 PARTITION OF open_api_logs
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 自动创建分区的函数
CREATE OR REPLACE FUNCTION create_next_month_partition()
RETURNS void AS $$
DECLARE
  next_month_start DATE;
  next_month_end DATE;
  partition_name TEXT;
BEGIN
  next_month_start := DATE_TRUNC('month', NOW() + INTERVAL '1 month');
  next_month_end := DATE_TRUNC('month', NOW() + INTERVAL '2 month');
  partition_name := 'open_api_logs_' || TO_CHAR(next_month_start, 'YYYY_MM');
  
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS %I PARTITION OF open_api_logs 
     FOR VALUES FROM (%L) TO (%L)',
    partition_name, next_month_start, next_month_end
  );
END;
$$ LANGUAGE plpgsql;
```

### 4.2 分区维护

```sql
-- 定时任务：每月1日创建下月分区
SELECT cron.schedule('create_api_log_partition', '0 0 1 * *', 
  'SELECT create_next_month_partition()');
```