> ## Documentation Index
> Fetch the complete documentation index at: https://docs.shannon.run/llms.txt
> Use this file to discover all available pages before exploring further.

# 数据库模式

> Shannon 的完整 PostgreSQL 数据库模式参考

## 概览

Shannon 使用 PostgreSQL 进行持久化存储。数据库组织为两个模式:

* `auth`: 认证、多租户、API 密钥和安全审计日志。
* `public`: 核心应用数据,包括任务、会话和使用指标。

**总表数**: 25 张表
**扩展**: uuid-ossp, pg\_trgm, btree\_gin, pgcrypto

## 实体关系图

```
┌─────────────────┐
│  auth.tenants   │
└────────┬────────┘
         │
         │ 1:N
         ▼
┌─────────────────┐        ┌──────────────────┐
│   auth.users    │◄───────│  auth.api_keys   │
└────────┬────────┘   N:1  └──────────────────┘
         │
         │ 1:N
         ▼
┌─────────────────┐        ┌──────────────────┐
│   public.users  │◄───────│   sessions       │
└────────┬────────┘   1:N  └──────────────────┘
         │
         │ 1:N
         ▼
┌──────────────────────────┐
│   task_executions        │
│  (workflow_id = PK)      │
└──────────┬───────────────┘
           │
           │ 1:N
           ▼
┌──────────────────────────┐        ┌──────────────────┐
│   agent_executions       │◄───────│  tool_executions │
└──────────┬───────────────┘   1:N  └──────────────────┘
           │
           │ 1:N
           ▼
┌──────────────────────────┐
│     tool_calls           │
└──────────────────────────┘

┌──────────────────────────┐        ┌──────────────────┐
│   event_logs             │        │  token_usage     │
│  (workflow_id)           │        └──────────────────┘
└──────────────────────────┘

┌──────────────────────────┐        ┌───────────────────┐
│  usage_daily_aggregates  │        │  learning_cases   │
└──────────────────────────┘        └───────────────────┘
```

## 模式: `auth` (认证与多租户)

### auth.tenants

**用途**: 多租户组织管理

| 列名                     | 类型           | 约束                              | 描述                           |
| ---------------------- | ------------ | ------------------------------- | ---------------------------- |
| id                     | UUID         | PK, DEFAULT gen\_random\_uuid() | 租户标识符                        |
| name                   | VARCHAR(255) | NOT NULL                        | 组织名称                         |
| slug                   | VARCHAR(100) | UNIQUE, NOT NULL                | URL 安全标识符                    |
| plan                   | VARCHAR(50)  | DEFAULT 'free'                  | 订阅计划 (free, pro, enterprise) |
| token\_limit           | INTEGER      | DEFAULT 10000                   | 每月 token 配额                  |
| monthly\_token\_usage  | INTEGER      | DEFAULT 0                       | 当前月使用量                       |
| rate\_limit\_per\_hour | INTEGER      | DEFAULT 1000                    | API 速率限制                     |
| is\_active             | BOOLEAN      | DEFAULT true                    | 租户状态                         |
| created\_at            | TIMESTAMP    | DEFAULT NOW()                   | 创建时间                         |
| updated\_at            | TIMESTAMP    | DEFAULT NOW()                   | 最后更新时间                       |
| metadata               | JSONB        | DEFAULT '{}'                    | 额外租户数据                       |

**索引**:

* `idx_tenants_slug` ON (slug)

**示例**:

```sql theme={null}
-- 获取所有活跃租户
SELECT id, name, plan, token_limit
FROM auth.tenants
WHERE is_active = true;

-- 检查 token 使用情况
SELECT name, monthly_token_usage, token_limit,
       (monthly_token_usage::FLOAT / token_limit * 100) as usage_percentage
FROM auth.tenants
WHERE monthly_token_usage > token_limit * 0.8;
```

### auth.users

**用途**: 用户认证和个人资料管理

| 列名                  | 类型           | 约束                              | 描述                      |
| ------------------- | ------------ | ------------------------------- | ----------------------- |
| id                  | UUID         | PK, DEFAULT gen\_random\_uuid() | 用户标识符                   |
| email               | VARCHAR(255) | UNIQUE, NOT NULL                | 用户邮箱                    |
| username            | VARCHAR(100) | UNIQUE, NOT NULL                | 用户名                     |
| password\_hash      | VARCHAR(255) | NOT NULL                        | Bcrypt 密码哈希             |
| full\_name          | VARCHAR(255) |                                 | 全名                      |
| tenant\_id          | UUID         | FK → auth.tenants, NOT NULL     | 所属组织                    |
| role                | VARCHAR(50)  | DEFAULT 'user'                  | 角色 (user, admin, owner) |
| is\_active          | BOOLEAN      | DEFAULT true                    | 账户状态                    |
| is\_verified        | BOOLEAN      | DEFAULT false                   | 邮箱验证状态                  |
| email\_verified\_at | TIMESTAMP    |                                 | 验证时间戳                   |
| created\_at         | TIMESTAMP    | DEFAULT NOW()                   | 账户创建时间                  |
| updated\_at         | TIMESTAMP    | DEFAULT NOW()                   | 最后更新时间                  |
| last\_login         | TIMESTAMP    |                                 | 最后登录时间                  |
| metadata            | JSONB        | DEFAULT '{}'                    | 额外用户数据                  |

**索引**:

* `idx_users_email` ON (email)
* `idx_users_tenant_id` ON (tenant\_id)

**示例**:

```sql theme={null}
-- 获取租户中的所有用户
SELECT u.email, u.username, u.role, u.last_login
FROM auth.users u
WHERE u.tenant_id = '...'
  AND u.is_active = true
ORDER BY u.last_login DESC NULLS LAST;

-- 查找不活跃用户
SELECT email, created_at, last_login
FROM auth.users
WHERE last_login < NOW() - INTERVAL '90 days'
   OR last_login IS NULL;
```

### auth.api\_keys

**用途**: 编程访问的 API 密钥管理

| 列名                     | 类型           | 约束                                                                    | 描述            |
| ---------------------- | ------------ | --------------------------------------------------------------------- | ------------- |
| id                     | UUID         | PK, DEFAULT gen\_random\_uuid()                                       | 密钥标识符         |
| key\_hash              | VARCHAR(255) | UNIQUE, NOT NULL                                                      | 密钥的 SHA256 哈希 |
| key\_prefix            | VARCHAR(20)  | NOT NULL                                                              | 前 8 个字符(用于显示) |
| user\_id               | UUID         | FK → auth.users                                                       | 密钥所有者         |
| tenant\_id             | UUID         | FK → auth.tenants, NOT NULL                                           | 所属组织          |
| name                   | VARCHAR(100) | NOT NULL                                                              | 密钥名称/描述       |
| description            | TEXT         |                                                                       | 详细描述          |
| scopes                 | TEXT\[]      | DEFAULT ARRAY\['workflows:read', 'workflows:write', 'agents:execute'] | 权限数组          |
| rate\_limit\_per\_hour | INTEGER      | DEFAULT 1000                                                          | 密钥特定速率限制      |
| last\_used             | TIMESTAMP    |                                                                       | 最后使用时间戳       |
| expires\_at            | TIMESTAMP    |                                                                       | 过期时间          |
| is\_active             | BOOLEAN      | DEFAULT true                                                          | 密钥状态          |
| created\_at            | TIMESTAMP    | DEFAULT NOW()                                                         | 创建时间          |
| metadata               | JSONB        | DEFAULT '{}'                                                          | 额外密钥数据        |

**索引**:

* `idx_api_keys_key_hash` ON (key\_hash)
* `idx_api_keys_user_id` ON (user\_id)

**默认作用域**:

* `workflows:read`: 允许读取工作流/任务状态和结果
* `workflows:write`: 允许提交和管理工作流/任务
* `agents:execute`: 允许执行代理

**示例**:

```sql theme={null}
-- 列出活跃的 API 密钥
SELECT key_prefix, name, last_used, expires_at
FROM auth.api_keys
WHERE is_active = true
  AND (expires_at IS NULL OR expires_at > NOW())
ORDER BY last_used DESC NULLS LAST;

-- 查找未使用的密钥
SELECT key_prefix, name, created_at, last_used
FROM auth.api_keys
WHERE last_used IS NULL
  AND created_at < NOW() - INTERVAL '30 days';

-- 检查即将过期的密钥
SELECT key_prefix, name, expires_at
FROM auth.api_keys
WHERE expires_at BETWEEN NOW() AND NOW() + INTERVAL '7 days'
  AND is_active = true;
```

### auth.refresh\_tokens

**用途**: JWT 刷新令牌存储和撤销

| 列名          | 类型           | 约束                              | 描述             |
| ----------- | ------------ | ------------------------------- | -------------- |
| id          | UUID         | PK, DEFAULT gen\_random\_uuid() | 令牌标识符          |
| token\_hash | VARCHAR(255) | UNIQUE, NOT NULL                | SHA256 哈希      |
| user\_id    | UUID         | FK → auth.users                 | 令牌所有者          |
| tenant\_id  | UUID         | FK → auth.tenants, NOT NULL     | 所属组织           |
| expires\_at | TIMESTAMP    | NOT NULL                        | 过期时间           |
| revoked     | BOOLEAN      | DEFAULT false                   | 撤销状态           |
| revoked\_at | TIMESTAMP    |                                 | 撤销时间           |
| ip\_address | INET         |                                 | 客户端 IP         |
| user\_agent | TEXT         |                                 | 客户端 user agent |
| created\_at | TIMESTAMP    | DEFAULT NOW()                   | 创建时间           |

**索引**:

* `idx_refresh_tokens_token_hash` ON (token\_hash)
* `idx_refresh_tokens_user_id` ON (user\_id)
* `idx_refresh_tokens_expires_at` ON (expires\_at)

**示例**:

```sql theme={null}
-- 撤销用户的所有令牌
UPDATE auth.refresh_tokens
SET revoked = true, revoked_at = NOW()
WHERE user_id = '...'
  AND revoked = false;

-- 清理过期令牌
DELETE FROM auth.refresh_tokens
WHERE expires_at < NOW() - INTERVAL '7 days';
```

### auth.audit\_logs

**用途**: 安全事件审计跟踪

| 列名          | 类型           | 约束                              | 描述             |
| ----------- | ------------ | ------------------------------- | -------------- |
| id          | UUID         | PK, DEFAULT gen\_random\_uuid() | 日志条目 ID        |
| event\_type | VARCHAR(100) | NOT NULL                        | 事件类型           |
| user\_id    | UUID         | FK → auth.users                 | 用户(可为空)        |
| tenant\_id  | UUID         | FK → auth.tenants               | 租户(可为空)        |
| ip\_address | INET         |                                 | 客户端 IP         |
| user\_agent | TEXT         |                                 | 客户端 user agent |
| details     | JSONB        | DEFAULT '{}'                    | 事件详情           |
| created\_at | TIMESTAMP    | DEFAULT NOW()                   | 事件时间           |

**索引**:

* `idx_audit_logs_event_type` ON (event\_type)
* `idx_audit_logs_user_id` ON (user\_id)
* `idx_audit_logs_tenant_id` ON (tenant\_id)
* `idx_audit_logs_created_at` ON (created\_at)

**事件类型**:

* `login_success`: 用户成功登录
* `login_failed`: 用户登录失败
* `password_change`: 密码已更改
* `api_key_created`: API 密钥已创建

**示例**:

```sql theme={null}
-- 最近的安全事件
SELECT event_type, user_id, ip_address, created_at
FROM auth.audit_logs
WHERE created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC
LIMIT 100;

-- 失败的登录尝试
SELECT user_id, ip_address, COUNT(*) as attempts
FROM auth.audit_logs
WHERE event_type = 'login_failed'
  AND created_at > NOW() - INTERVAL '1 hour'
GROUP BY user_id, ip_address
HAVING COUNT(*) > 5;
```

## 模式: `public` (核心应用)

### users

**用途**: 应用用户配置文件(旧版,关联到 auth.users)

| 列名           | 类型           | 约束                               | 描述      |
| ------------ | ------------ | -------------------------------- | ------- |
| id           | UUID         | PK, DEFAULT uuid\_generate\_v4() | 用户标识符   |
| external\_id | VARCHAR(255) | UNIQUE, NOT NULL                 | 外部系统 ID |
| email        | VARCHAR(255) |                                  | 邮箱地址    |
| tenant\_id   | UUID         |                                  | 租户引用    |
| metadata     | JSONB        | DEFAULT '{}'                     | 用户元数据   |
| created\_at  | TIMESTAMPTZ  | DEFAULT NOW()                    | 创建时间    |
| updated\_at  | TIMESTAMPTZ  | DEFAULT NOW()                    | 最后更新时间  |

**索引**:

* `idx_users_tenant_id` ON (tenant\_id)
* `idx_users_external_id` ON (external\_id)

**注意**: 此表用于向后兼容。新代码应使用 `auth.users`。

### sessions

**用途**: 用户会话管理和上下文

| 列名            | 类型          | 约束                               | 描述         |
| ------------- | ----------- | -------------------------------- | ---------- |
| id            | UUID        | PK, DEFAULT uuid\_generate\_v4() | 会话标识符      |
| user\_id      | UUID        | FK → users                       | 会话所有者      |
| tenant\_id    | UUID        |                                  | 租户引用       |
| context       | JSONB       | DEFAULT '{}'                     | 会话上下文数据    |
| token\_budget | INTEGER     | DEFAULT 10000                    | Token 配额   |
| tokens\_used  | INTEGER     | DEFAULT 0                        | 已消耗的 token |
| created\_at   | TIMESTAMPTZ | DEFAULT NOW()                    | 会话开始时间     |
| updated\_at   | TIMESTAMPTZ | DEFAULT NOW()                    | 最后活动时间     |
| expires\_at   | TIMESTAMPTZ |                                  | 过期时间       |

**索引**:

* `idx_sessions_user_id` ON (user\_id)
* `idx_sessions_tenant_id` ON (tenant\_id)
* `idx_sessions_expires_at` ON (expires\_at)
* `idx_sessions_external_id` ON ((context->>'external\_id')) WHERE context->>'external\_id' IS NOT NULL
* `idx_sessions_user_external_id` UNIQUE ON (user\_id, (context->>'external\_id')) WHERE context->>'external\_id' IS NOT NULL AND deleted\_at IS NULL
* `idx_sessions_not_deleted` ON (id) WHERE deleted\_at IS NULL

外部会话 ID（非 UUID）存储在 `context->>'external_id'`，用于双 ID 查询。

**示例**:

```sql theme={null}
-- 活跃会话
SELECT id, user_id, tokens_used, token_budget, created_at
FROM sessions
WHERE expires_at > NOW()
  OR expires_at IS NULL
ORDER BY created_at DESC;

-- 会话 token 使用情况
SELECT
    COUNT(*) as session_count,
    AVG(tokens_used) as avg_tokens,
    SUM(tokens_used) as total_tokens
FROM sessions
WHERE created_at > NOW() - INTERVAL '24 hours';
```

### task\_executions

**用途**: 任务/工作流执行历史和指标

| 列名                 | 类型            | 约束                               | 描述                               |
| ------------------ | ------------- | -------------------------------- | -------------------------------- |
| id                 | UUID          | PK, DEFAULT uuid\_generate\_v4() | 任务标识符                            |
| workflow\_id       | VARCHAR(255)  | UNIQUE, NOT NULL                 | Temporal 工作流 ID                  |
| user\_id           | UUID          | FK → users                       | 任务创建者                            |
| tenant\_id         | UUID          |                                  | 租户引用                             |
| session\_id        | VARCHAR(255)  |                                  | 会话标识符                            |
| query              | TEXT          | NOT NULL                         | 任务查询/提示                          |
| mode               | VARCHAR(50)   |                                  | 执行模式 (SIMPLE, STANDARD, COMPLEX) |
| status             | VARCHAR(50)   | NOT NULL                         | 任务状态                             |
| started\_at        | TIMESTAMPTZ   | NOT NULL, DEFAULT NOW()          | 开始时间                             |
| completed\_at      | TIMESTAMPTZ   |                                  | 完成时间                             |
| result             | TEXT          |                                  | 最终结果                             |
| response           | JSONB         | DEFAULT '{}'                     | 结构化响应                            |
| error\_message     | TEXT          |                                  | 错误详情                             |
| total\_tokens      | INTEGER       | DEFAULT 0                        | 使用的总 token 数                     |
| prompt\_tokens     | INTEGER       | DEFAULT 0                        | 输入 token 数                       |
| completion\_tokens | INTEGER       | DEFAULT 0                        | 输出 token 数                       |
| total\_cost\_usd   | DECIMAL(10,6) | DEFAULT 0                        | 总成本                              |
| duration\_ms       | INTEGER       |                                  | 执行时间(毫秒)                         |
| agents\_used       | INTEGER       | DEFAULT 0                        | 使用的代理数量                          |
| tools\_invoked     | INTEGER       | DEFAULT 0                        | 工具调用次数                           |
| cache\_hits        | INTEGER       | DEFAULT 0                        | 缓存命中次数                           |
| complexity\_score  | DECIMAL(3,2)  |                                  | 复杂度 (0.0-1.0)                    |
| metadata           | JSONB         |                                  | 额外元数据                            |
| created\_at        | TIMESTAMPTZ   | DEFAULT NOW()                    | 记录创建时间                           |

**索引**:

* `idx_task_executions_workflow_id` ON (workflow\_id)
* `idx_task_executions_user_id` ON (user\_id)
* `idx_task_executions_session_id` ON (session\_id)
* `idx_task_executions_status` ON (status)
* `idx_task_executions_started_at` ON (started\_at)
* `idx_task_executions_tenant_id_started_at` ON (tenant\_id, started\_at)

**状态值**:

* `PENDING`: 任务已创建但尚未开始
* `RUNNING`: 任务正在进行中
* `COMPLETED`: 任务成功完成
* `FAILED`: 任务失败

**示例**:

```sql theme={null}
-- 用户的最近任务
SELECT workflow_id, query, status, duration_ms, total_cost_usd
FROM task_executions
WHERE user_id = '...'
ORDER BY started_at DESC
LIMIT 20;

-- 失败任务分析
SELECT
    DATE(started_at) as date,
    COUNT(*) as failed_count,
    AVG(duration_ms) as avg_duration
FROM task_executions
WHERE status = 'FAILED'
  AND started_at > NOW() - INTERVAL '7 days'
GROUP BY DATE(started_at)
ORDER BY date DESC;

-- 成本分析
SELECT
    user_id,
    COUNT(*) as task_count,
    SUM(total_cost_usd) as total_cost,
    AVG(total_cost_usd) as avg_cost,
    SUM(total_tokens) as total_tokens
FROM task_executions
WHERE started_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
ORDER BY total_cost DESC;
```

### agent\_executions

**用途**: 任务中的单个代理执行详情

| 列名             | 类型           | 约束                               | 描述              |
| -------------- | ------------ | -------------------------------- | --------------- |
| id             | UUID         | PK, DEFAULT uuid\_generate\_v4() | 执行 ID           |
| workflow\_id   | VARCHAR(255) |                                  | Temporal 工作流 ID |
| task\_id       | VARCHAR(255) |                                  | 任务标识符           |
| agent\_id      | VARCHAR(255) | NOT NULL                         | 代理标识符           |
| input          | TEXT         | NOT NULL                         | 代理输入            |
| output         | TEXT         |                                  | 代理输出            |
| state          | VARCHAR(50)  |                                  | FSM 状态          |
| tokens\_used   | INTEGER      | DEFAULT 0                        | 消耗的 token       |
| model\_used    | VARCHAR(100) |                                  | 使用的 LLM 模型      |
| duration\_ms   | INTEGER      |                                  | 执行时间            |
| error\_message | TEXT         |                                  | 错误详情            |
| metadata       | JSONB        | DEFAULT '{}'                     | 附加元数据           |
| strategy       | VARCHAR(100) |                                  | 执行策略            |
| created\_at    | TIMESTAMPTZ  | DEFAULT NOW()                    | 开始时间            |
| updated\_at    | TIMESTAMPTZ  | DEFAULT NOW()                    | 最后更新            |

**索引**:

* `idx_agent_executions_workflow_id` ON (workflow\_id)
* `idx_agent_executions_created_at` ON (created\_at DESC)
* `idx_agent_executions_state` ON (state)

**状态值**:

* `IDLE` - 代理空闲
* `ANALYZING` - 分析输入
* `PLANNING` - 规划执行
* `RETRIEVING` - 检索信息
* `EXECUTING` - 执行任务
* `VALIDATING` - 验证结果
* `SYNTHESIZING` - 合成响应
* `COMPLETED` - 执行完成
* `FAILED` - 执行失败

**示例**:

```sql theme={null}
-- 任务的代理执行链
SELECT agent_id, state, duration_ms, tokens_used
FROM agent_executions
WHERE workflow_id = '...'
ORDER BY created_at;

-- 代理性能指标
SELECT
    agent_id,
    COUNT(*) as execution_count,
    AVG(duration_ms) as avg_duration,
    AVG(tokens_used) as avg_tokens
FROM agent_executions
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY agent_id
ORDER BY execution_count DESC;
```

### tool\_executions

**用途**: 工具调用历史和性能

| 列名                   | 类型           | 约束                               | 描述              |
| -------------------- | ------------ | -------------------------------- | --------------- |
| id                   | UUID         | PK, DEFAULT uuid\_generate\_v4() | 执行 ID           |
| agent\_execution\_id | UUID         |                                  | 父代理执行           |
| workflow\_id         | VARCHAR(255) |                                  | Temporal 工作流 ID |
| agent\_id            | VARCHAR(255) |                                  | 代理标识符           |
| tool\_name           | VARCHAR(255) | NOT NULL                         | 工具标识符           |
| input\_params        | JSONB        |                                  | 输入参数            |
| output               | TEXT         |                                  | 工具输出            |
| success              | BOOLEAN      | DEFAULT true                     | 成功状态            |
| error                | TEXT         |                                  | 错误详情            |
| duration\_ms         | INTEGER      |                                  | 执行时间            |
| tokens\_consumed     | INTEGER      | DEFAULT 0                        | 使用的 token       |
| metadata             | JSONB        | DEFAULT '{}'                     | 附加元数据           |
| created\_at          | TIMESTAMPTZ  | DEFAULT NOW()                    | 创建时间            |

**索引**:

* `idx_tool_executions_workflow_id` ON (workflow\_id)
* `idx_tool_executions_tool_name` ON (tool\_name)
* `idx_tool_executions_created_at` ON (created\_at DESC)
* `idx_tool_executions_success` ON (success)

**示例**:

```sql theme={null}
-- 工具使用统计
SELECT
    tool_name,
    COUNT(*) as invocation_count,
    COUNT(CASE WHEN success THEN 1 END) as successful,
    AVG(duration_ms) as avg_duration
FROM tool_executions
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY tool_name
ORDER BY invocation_count DESC;

-- 失败的工具执行
SELECT tool_name, error, created_at
FROM tool_executions
WHERE success = false
  AND created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC;

-- 工具性能
SELECT
    tool_name,
    AVG(duration_ms) as avg_ms,
    MAX(duration_ms) as max_ms,
    STDDEV(duration_ms) as stddev_ms
FROM tool_executions
WHERE success = true
GROUP BY tool_name
ORDER BY avg_ms DESC;
```

### event\_logs

**用途**: 流式事件存储,用于审计和重放

| 列名           | 类型           | 约束                               | 描述         |
| ------------ | ------------ | -------------------------------- | ---------- |
| id           | UUID         | PK, DEFAULT uuid\_generate\_v4() | 事件 ID      |
| workflow\_id | VARCHAR(255) | NOT NULL                         | 工作流标识符     |
| task\_id     | UUID         |                                  | 任务引用(可为空)  |
| type         | VARCHAR(100) | NOT NULL                         | 事件类型       |
| agent\_id    | VARCHAR(255) |                                  | 代理标识符      |
| message      | TEXT         |                                  | 事件消息       |
| payload      | JSONB        | DEFAULT '{}'                     | 事件有效载荷     |
| timestamp    | TIMESTAMPTZ  | NOT NULL, DEFAULT NOW()          | 事件时间       |
| seq          | BIGINT       |                                  | 序列号        |
| stream\_id   | VARCHAR(64)  |                                  | Redis 流 ID |
| created\_at  | TIMESTAMPTZ  | NOT NULL, DEFAULT NOW()          | 记录创建时间     |

**索引**:

* `idx_event_logs_workflow_id` ON (workflow\_id)
* `idx_event_logs_task_id` ON (task\_id)
* `idx_event_logs_type` ON (type)
* `idx_event_logs_agent_id` ON (agent\_id)
* `idx_event_logs_timestamp` ON (timestamp)
* `idx_event_logs_stream_id` ON (stream\_id)
* `idx_event_logs_payload_gin` USING GIN (payload)

**事件类型**:

* `task_started`
* `agent_started`
* `tool_invoked`
* `task_completed`

**示例**:

```sql theme={null}
-- 获取任务的所有事件
SELECT type, agent_id, message, timestamp
FROM event_logs
WHERE workflow_id = '...'
ORDER BY timestamp;

-- 事件频率分析
SELECT
    type,
    COUNT(*) as event_count,
    DATE_TRUNC('hour', timestamp) as hour
FROM event_logs
WHERE timestamp > NOW() - INTERVAL '24 hours'
GROUP BY type, hour
ORDER BY hour DESC, event_count DESC;
```

### token\_usage

**用途**: 每个任务的详细 token 使用跟踪

| 列名                 | 类型            | 约束                               | 描述       |
| ------------------ | ------------- | -------------------------------- | -------- |
| id                 | UUID          | PK, DEFAULT uuid\_generate\_v4() | 记录 ID    |
| user\_id           | UUID          | FK → users                       | 用户       |
| task\_id           | UUID          | FK → task\_executions            | 任务       |
| provider           | VARCHAR(50)   | NOT NULL                         | LLM 提供商  |
| model              | VARCHAR(255)  | NOT NULL                         | 模型名称     |
| prompt\_tokens     | INTEGER       | NOT NULL                         | 输入 token |
| completion\_tokens | INTEGER       | NOT NULL                         | 输出 token |
| total\_tokens      | INTEGER       | NOT NULL                         | 总 token  |
| cost\_usd          | DECIMAL(10,6) | NOT NULL                         | 美元成本     |
| created\_at        | TIMESTAMPTZ   | DEFAULT NOW()                    | 记录时间     |

**索引**:

* `idx_token_usage_user_id` ON (user\_id)
* `idx_token_usage_task_id` ON (task\_id)
* `idx_token_usage_provider_model` ON (provider, model)
* `idx_token_usage_created_at` ON (created\_at)

**示例**:

```sql theme={null}
-- 按提供商统计 token 使用
SELECT
    provider,
    model,
    COUNT(*) as call_count,
    SUM(total_tokens) as total_tokens,
    SUM(cost_usd) as total_cost
FROM token_usage
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY provider, model
ORDER BY total_cost DESC;

-- 每日 token 趋势
SELECT
    DATE(created_at) as date,
    SUM(total_tokens) as tokens,
    SUM(cost_usd) as cost
FROM token_usage
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date;
```

### usage\_daily\_aggregates

**用途**: 预计算的每日使用统计

| 列名                 | 类型            | 约束                               | 描述        |
| ------------------ | ------------- | -------------------------------- | --------- |
| id                 | UUID          | PK, DEFAULT uuid\_generate\_v4() | 记录 ID     |
| user\_id           | UUID          | FK → users                       | 用户        |
| date               | DATE          | NOT NULL                         | 聚合日期      |
| total\_tasks       | INTEGER       | DEFAULT 0                        | 总任务数      |
| successful\_tasks  | INTEGER       | DEFAULT 0                        | 成功任务数     |
| failed\_tasks      | INTEGER       | DEFAULT 0                        | 失败任务数     |
| total\_tokens      | INTEGER       | DEFAULT 0                        | 总 token 数 |
| total\_cost\_usd   | DECIMAL(10,6) | DEFAULT 0                        | 总成本       |
| model\_usage       | JSONB         |                                  | 模型分布      |
| tools\_invoked     | INTEGER       | DEFAULT 0                        | 工具调用次数    |
| tool\_distribution | JSONB         |                                  | 工具使用分布    |
| avg\_duration\_ms  | INTEGER       |                                  | 平均任务持续时间  |
| cache\_hit\_rate   | DECIMAL(3,2)  |                                  | 缓存命中百分比   |
| created\_at        | TIMESTAMPTZ   | DEFAULT NOW()                    | 记录时间      |

**索引**:

* `idx_usage_daily_aggregates_user_date` UNIQUE ON (user\_id, date)
* `idx_usage_daily_aggregates_date` ON (date)

**唯一约束**: (user\_id, date)

**示例**:

```sql theme={null}
-- 用户使用摘要
SELECT date, total_tasks, successful_tasks, total_cost_usd
FROM usage_daily_aggregates
WHERE user_id = '...'
  AND date > CURRENT_DATE - INTERVAL '30 days'
ORDER BY date DESC;

-- 聚合指标
SELECT
    SUM(total_tasks) as total_tasks,
    SUM(total_cost_usd) as total_cost,
    AVG(cache_hit_rate) as avg_cache_rate
FROM usage_daily_aggregates
WHERE date > CURRENT_DATE - INTERVAL '7 days';
```

## 其他表

### tool\_calls

**用途**: 旧版工具调用跟踪(请改用 tool\_executions)

### prompts

**用途**: 提示词版本控制和 A/B 测试

### learning\_cases

**用途**: 强化学习案例存储

### session\_archives

**用途**: 来自 Redis 的长期会话快照

### audit\_logs (public)

**用途**: 应用审计跟踪(与 auth.audit\_logs 分开)

## 数据库函数

### update\_updated\_at\_column()

**用途**: 行更改时自动更新 `updated_at`

**使用**: 作为触发器附加到 `users` 和 `sessions` 表

```sql theme={null}
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
```

### update\_daily\_aggregate(user\_id, date)

**用途**: 更新或创建用户的每日使用聚合

**使用**: 任务完成时通过触发器自动调用

```sql theme={null}
-- 手动调用
SELECT update_daily_aggregate(
    '00000000-0000-0000-0000-000000000002',
    '2025-10-22'
);
```

### trigger\_update\_daily\_aggregate()

**用途**: 在任务状态更改时更新聚合的触发器函数

**使用**: 在 task\_executions INSERT/UPDATE 时自动触发

## 示例查询

### 任务分析

```sql theme={null}
-- 按模式统计任务成功率
SELECT
    mode,
    COUNT(*) as total,
    COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) as completed,
    ROUND(100.0 * COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) / COUNT(*), 2) as success_rate
FROM task_executions
WHERE started_at > NOW() - INTERVAL '7 days'
GROUP BY mode;

-- 按复杂度统计平均执行时间
SELECT
    CASE
        WHEN complexity_score < 0.3 THEN 'Low'
        WHEN complexity_score < 0.7 THEN 'Medium'
        ELSE 'High'
    END as complexity,
    COUNT(*) as task_count,
    AVG(duration_ms) as avg_ms,
    AVG(total_cost_usd) as avg_cost
FROM task_executions
WHERE complexity_score IS NOT NULL
GROUP BY complexity;
```

### 成本分析

```sql theme={null}
-- 成本最高的用户
SELECT
    u.email,
    COUNT(t.id) as task_count,
    SUM(t.total_cost_usd) as total_cost,
    AVG(t.total_cost_usd) as avg_cost
FROM task_executions t
JOIN users u ON t.user_id = u.id
WHERE t.started_at > NOW() - INTERVAL '30 days'
GROUP BY u.email
ORDER BY total_cost DESC
LIMIT 10;

-- 按模型细分成本
SELECT
    tu.provider,
    tu.model,
    COUNT(*) as usage_count,
    SUM(tu.total_tokens) as total_tokens,
    SUM(tu.cost_usd) as total_cost,
    AVG(tu.cost_usd) as avg_cost_per_call
FROM token_usage tu
WHERE tu.created_at > NOW() - INTERVAL '7 days'
GROUP BY tu.provider, tu.model
ORDER BY total_cost DESC;
```

### 性能监控

```sql theme={null}
-- 慢查询(任务 > 60 秒)
SELECT
    workflow_id,
    query,
    duration_ms,
    agents_used,
    tools_invoked
FROM task_executions
WHERE duration_ms > 60000
  AND started_at > NOW() - INTERVAL '24 hours'
ORDER BY duration_ms DESC;

-- 工具执行成功率
SELECT
    tool_name,
    COUNT(*) as total,
    COUNT(CASE WHEN success THEN 1 END) as successful,
    ROUND(100.0 * COUNT(CASE WHEN success THEN 1 END) / COUNT(*), 2) as success_rate,
    AVG(duration_ms) as avg_duration
FROM tool_executions
GROUP BY tool_name
ORDER BY total DESC;
```

## 最佳实践

### 1. 索引

**始终为以下内容使用索引**:

* 外键 (`user_id`, `tenant_id` 等)
* WHERE 子句中使用的高基数列 (`status`, `mode`)。
* 时间序列数据 (`created_at`, `started_at`)。
* 使用 GIN 索引的 JSONB 列。

**当前索引覆盖率**: 优秀(所有外键和常见查询都已建立索引)

### 2. 分区

对于高容量表,考虑分区:

```sql theme={null}
-- task_executions 的月度分区
CREATE TABLE task_executions_2025_10 PARTITION OF task_executions
    FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
```

### 3. 数据保留

**推荐策略**:

* `event_logs`: 保留 90 天。
* `task_executions`: 保留 1 年,归档旧记录。
* `audit_logs`: 保留 2 年。
* `refresh_tokens`: 每周清除过期/已撤销的令牌。

**清理脚本**:

```sql theme={null}
-- 删除旧事件(每月运行)
DELETE FROM event_logs
WHERE created_at < NOW() - INTERVAL '90 days';

-- 归档旧任务(每年运行)
INSERT INTO task_executions_archive
SELECT * FROM task_executions
WHERE started_at < NOW() - INTERVAL '1 year';

DELETE FROM task_executions
WHERE started_at < NOW() - INTERVAL '1 year';
```

### 4. 查询优化

**使用 EXPLAIN ANALYZE**:

```sql theme={null}
EXPLAIN ANALYZE
SELECT * FROM task_executions
WHERE user_id = '...'
  AND started_at > NOW() - INTERVAL '7 days';
```

**使用覆盖索引优化**:

```sql theme={null}
CREATE INDEX idx_task_user_date_status
ON task_executions(user_id, started_at, status)
INCLUDE (total_cost_usd, duration_ms);
```

### 5. 连接池

**配置**(Shannon 中已配置):

```bash theme={null}
DB_MAX_OPEN_CONNS=50
DB_MAX_IDLE_CONNS=10
```

## 维护

### Vacuum 和 Analyze

```sql theme={null}
-- 手动 vacuum(每周运行)
VACUUM ANALYZE task_executions;
VACUUM ANALYZE event_logs;

-- 配置 autovacuum
ALTER TABLE task_executions SET (autovacuum_vacuum_threshold = 1000);
```

### 统计信息

```sql theme={null}
-- 更新统计信息
ANALYZE task_executions;

-- 检查表膨胀
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
```

## 相关文档

<CardGroup cols={2}>
  <Card title="环境变量" icon="gear" href="/cn/deployment/environment-variables">
    数据库配置
  </Card>

  <Card title="性能调优" icon="gauge" href="/cn/deployment/performance-tuning">
    查询优化
  </Card>

  <Card title="监控" icon="chart-line" href="/cn/deployment/monitoring">
    数据库监控
  </Card>
</CardGroup>