[Ops]: 支持外部数据库或增强 SQLite 锁竞争诊断与缓解

### 可观测性场景

认证文件 / 配额 / 账号健康；请求监控 / Request Events；系统健康检查 / Doctor；备份 / 维护 / 数据保留

### 当前无法回答的问题

CPAMP Manager Server 目前看起来只能使用本地 SQLite（`USAGE_DB_PATH=/data/usage.sqlite`）。在实际运行中，后台 rollup / account-action / request history 同时写入时会出现 SQLite 锁竞争日志：

```text
[usage-rollup] account history catch-up failed: database is locked (517)
[usage-rollup] dashboard hourly catch-up failed: database is locked (5) (SQLITE_BUSY)
[account-action] failed to upsert pending candidate for auth file "<redacted-auth-file>": database is locked (5) (SQLITE_BUSY)
```

我想确认/请求：是否可以支持 PostgreSQL/MySQL 等外部数据库后端，或提供官方推荐的 SQLite 锁竞争缓解方案。

> 说明：业务请求卡顿的直接原因并不一定是 SQLite。本次排查中，`/v1/chat/completions` 的 30s 客户端超时主要与上游 provider/account pool 有关。但 CPAMP 自身同时出现 `SQLITE_BUSY`，会影响 rollup、account-action candidate、自动禁用/巡检结果写入的及时性，因此希望有更可靠的数据库/锁竞争方案。

### 需要支持的筛选 / 聚合维度

- [x] 时间范围
- [x] AI 提供商 / Provider
- [x] 模型
- [x] 认证文件 / Auth File
- [x] 账号
- [x] 配额 / Quota
- [x] 失败原因 / 错误类型
- [x] Header / 响应元数据
- [x] 队列 / Collector 状态

### 数据来源

- CPA / CLIProxyAPI usage queue events
- CPAMP Manager Server SQLite (`usage.sqlite`)
- CPAMP Manager Server stdout logs
- CPA request/error log files under `logs/`
- CPAMP `/status` and `/usage-service/info`

Sanitized environment/context:

```yaml
Deployment: Docker Compose full mode
CPAMP image: seakee/cpa-manager-plus:latest
CPA version observed in request logs: v7.2.95
CPA Manager Server: cpa-manager-plus on :18317
CPA upstream: http://cliproxyapi:8317
Usage collector transport: subscribe
Usage DB path: /data/usage.sqlite
```

Runtime status excerpt:

```json
{
  "usageServiceInfo": {
    "mode": "embedded",
    "configured": true,
    "migrationStatus": "migrated"
  },
  "status": {
    "collector": {
      "collector": "running",
      "upstream": "http://cliproxyapi:8317",
      "mode": "auto",
      "transport": "subscribe",
      "queue": "usage",
      "totalInserted": 1759,
      "totalSkipped": 0,
      "deadLetters": 0
    },
    "dbPath": "/data/usage.sqlite"
  }
}
```

Sanitized SQLite table context:

```text
usage_events: 5140 rows
usage_account_model_rollups: 2823 rows
usage_dashboard_hourly_rollups: 249 rows
account_action_candidates: 1280 rows
codex_inspection_results: 5988 rows
codex_inspection_logs: 6179 rows
```

### 期望展示 / 导出结果

期望 CPAMP 能提供下面任一官方方案：

1. **支持外部数据库后端**
   - 例如 PostgreSQL 优先，MySQL 可选。
   - 类似配置：
     ```yaml
     USAGE_DB_TYPE: postgres
     USAGE_DATABASE_URL_FILE: /run/secrets/cpamp_database_url
     ```
   - 保留 `data.key` 加密模型，避免 CPA Management Key / token 明文落库。
   - 提供 SQLite -> PostgreSQL 的迁移/导入命令，至少支持 request events、settings、model prices、api key aliases、account-action / inspection 状态。

2. **如果暂不计划外部 DB，则提供官方 SQLite 锁竞争缓解方案**
   - 在文档中明确推荐配置：WAL、busy timeout、连接池参数、rollup worker 开关、checkpoint/vacuum 维护方式。
   - 在 `/status` 或 Doctor 中暴露：最近 `SQLITE_BUSY` 次数、最近失败的 worker、last_error、checkpoint/rollup lag。
   - 对 rollup/account-action 写入增加更稳健的 retry/backoff，避免一次锁冲突导致 catch-up/action candidate 丢失或延迟。

### 数据规模 / 性能背景

这次复现实例规模并不大（`usage_events` 约 5k），但账号巡检与 account-action 写入较频繁，仍然出现多次锁竞争：

```text
2026-07-23 09:50:45 [usage-rollup] account history catch-up failed: database is locked (5) (SQLITE_BUSY)
2026-07-23 09:55:35 [usage-rollup] dashboard hourly catch-up failed: database is locked (5) (SQLITE_BUSY)
2026-07-23 09:56:17 [account-action] failed to upsert pending candidate for auth file "<redacted-auth-file>": database is locked (5) (SQLITE_BUSY)
2026-07-23 09:56:46 [account-action] failed to upsert pending candidate for auth file "<redacted-auth-file>": database is locked (5) (SQLITE_BUSY)
2026-07-23 10:01:48 [usage-rollup] account history catch-up failed: database is locked (5) (SQLITE_BUSY)
2026-07-23 11:43:40 [usage-rollup] account history catch-up failed: database is locked (517)
2026-07-23 12:23:31 [usage-rollup] dashboard hourly catch-up failed: database is locked (5) (SQLITE_BUSY)
2026-07-23 13:03:42 [usage-rollup] dashboard hourly catch-up failed: database is locked (517)
```

同一时间窗口内 CPAMP 服务本身健康：

```text
cpa-manager-plus: Up, healthy, RestartCount=0, OOMKilled=false
/health: 200, sub-ms
/usage-service/info: configured=true, migrationStatus=migrated
/status: collector=running, transport=subscribe, deadLetters=0
```

与业务请求卡顿相关的 CPA 侧日志（用于说明为什么希望 Doctor/diagnostics 能区分 provider/account pool 问题与 CPAMP DB 问题）：

```text
2026-07-23 12:26:01 200 | 30.153s | POST "/v1/chat/completions"
2026-07-23 13:03:45 500 | 30.13s  | POST "/v1/chat/completions"
2026-07-23 13:40:57 200 | 26.708s | POST "/v1/chat/completions"
```

Sanitized CPAMP usage summary for recent 2h:

```text
provider=xai model=grok-4.5 status=403 count=73 avg=185.7ms max=256ms
provider=xai model=grok-4.5 status=402 count=63 avg=183.6ms max=232ms
provider=xai model=grok-4.5 status=401 count=47 avg=86.5ms max=135ms
provider=xai model=grok-4.5 status=200 count=18 avg=7788.5ms max=27109ms
```

Failure summaries were redacted to remove tokens, keys, emails, auth indexes, trace bodies, and account details. Examples:

```text
403: Access to the chat endpoint is denied. Please ensure you're using the correct credentials.
402: You have run out of credits or need a Grok subscription.
401: Invalid or expired credentials (... reason=no auth context)
500: context canceled
```

### 安全与隐私

- [x] 结果中不能包含 CPA Management Key、API Key、token 或账号敏感信息
- [x] 如果涉及导出或诊断报告，需要明确脱敏字段

Redacted fields in this report:

```text
CPA Management Key: <redacted>
CPAMP Admin Key: <redacted>
API keys / bearer tokens: <redacted>
Auth file names / email addresses: <redacted-auth-file> / <email>
Auth indexes / token-like IDs / long hex strings: <redacted>
Public domain / IP / client IP: omitted or generalized
```

### 期望验收结果

这个需求可以按以下任一方式视为完成：

- CPAMP 文档明确说明当前仅支持 SQLite，并给出官方锁竞争排障/缓解手册；或
- `/status` / Doctor 能直接报告 SQLite lock contention、rollup lag、account-action write failures，并给出建议；或
- 支持 PostgreSQL/MySQL 等外部数据库配置，并提供 SQLite 数据迁移路径；或
- rollup/account-action 写入在 `SQLITE_BUSY` 时具备可靠 retry/backoff，不再让后台 catch-up/action candidate 因短暂写锁失败而丢失或长期延迟。

### 检查清单

- [x] 我已经搜索过现有 Issues，没有找到重复需求
  - Searched: `database is locked`, `SQLITE_BUSY`, `PostgreSQL`, `MySQL`, `external database`, `USAGE_DB_PATH`, `rollup`.
  - Related but not duplicate: #430 and #370 focus on Monitoring/Usage Analytics timeout/query performance; this issue focuses on database backend configurability and SQLite write-lock resilience.
- [x] 我已经确认敏感信息已经脱敏，例如 token、key、账号信息


Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Ops]: 支持外部数据库或增强 SQLite 锁竞争诊断与缓解 #435

可观测性场景

当前无法回答的问题

需要支持的筛选 / 聚合维度

数据来源

期望展示 / 导出结果

数据规模 / 性能背景

安全与隐私

期望验收结果

检查清单

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

[Ops]: 支持外部数据库或增强 SQLite 锁竞争诊断与缓解 #435

Description

可观测性场景

当前无法回答的问题

需要支持的筛选 / 聚合维度

数据来源

期望展示 / 导出结果

数据规模 / 性能背景

安全与隐私

期望验收结果

检查清单

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions