# Phase 1 DB Design — Operation Ollama-First v5.0

> **日期**：2026-05-03
> **作者**：A3 db-expert
> **產出**：3 個 migration（024/025/026）+ 設計理由 + 效能評估
> **依據**：`docs/phase0_audit_report_20260503.md` 34 個 LLM 呼叫點 / 11.8% 覆蓋率
> **狀態**：SQL 檔已產出於 `migrations/`，**未自動 apply**，待統帥 review 後手動執行

---

## TL;DR

| 交付物 | 路徑 | 影響 |
|--------|------|------|
| `ai_calls` 統一 LLM 遙測表 | `migrations/024_create_ai_calls_table.sql` | 接 30 個未覆蓋呼叫點 |
| `mcp_calls` MCP 遙測表 | `migrations/025_create_mcp_calls_and_budgets.sql` | Phase 10 預備 |
| `ai_call_budgets` 預算閾值 | 同上（含 5 筆種子） | Phase 9 預算告警 |
| `ai_insights.embedding_signature` | `migrations/026_add_embedding_signature.sql` | BGE-M3 一致性護欄 |

**結論**：Schema 設計已完備，無 schema 衝突。**A4 接 logger 工作可立即啟動**，唯一前置條件是統帥手動 apply 這 3 個 migration。

---

## Section 1 — Schema 設計理由

### 1.1 ai_calls 欄位選擇邏輯

| 欄位 | 為何必要 | 為何這個型別 |
|------|---------|-------------|
| `id BIGSERIAL` | 90 天 ~6.5M，年累積會超 INT4 21 億的 3% — 提早用 BIGSERIAL 避免將來改型別 | 與 mcp_calls 一致 |
| `called_at TIMESTAMPTZ` | 報表查詢核心欄位 | 用 TIMESTAMPTZ（不是 TIMESTAMP），因為 momo 三主機跨時區（GCP UTC / 188 Asia/Taipei） |
| `caller VARCHAR(64)` | 必白名單管控；新增需 ADR | 64 足夠（最長 `code_review_elephant` 20 字） |
| `provider VARCHAR(32)` | A1 audit 列舉的 7 種主機標籤 | 32 足夠 |
| `model VARCHAR(128)` | NIM 模型名可達 50+（如 `nvidia/llama-3.3-nemotron-super-49b-v1.5`） | 128 留 buffer |
| `input_tokens / output_tokens` | Token 日報核心；NOT NULL DEFAULT 0 確保 SUM() 不爆 | INT 足夠（單次最大 200K，一年累積一個 caller 也只到 ~10B，INT4 上限 21 億夠） |
| `duration_ms INT` | 監控 LLM 慢查；可為 NULL（AiderHeal 走 SSH 拿不到精確值） | INT |
| `status` | ok/fallback/error/timeout/cache_only — 串接 fallback 鏈關鍵 | VARCHAR(16) |
| `fallback_to` | 「主路徑失敗，下游 caller」串接邏輯；下游本身另寫一筆 | VARCHAR(64) 同 caller |
| `cost_usd NUMERIC(10,6)` | Phase 9 預算用；6 位小數可記到 $0.000001（OpenRouter 細粒計費需要） | NUMERIC 不用 FLOAT，避免累計誤差 |
| `cache_hit BOOLEAN` | Anthropic prompt cache / Gemini cache（成本降 90%）必追蹤 | 預設 FALSE |
| `rag_hit BOOLEAN` | Phase 11 RAG 攔截率核心 KPI | 預設 FALSE |
| `request_id VARCHAR(64)` | Code Review 三鏈、Q&A fallback 三層必須 trace 同一邏輯請求 | UUID4 takes 36, 加 prefix 也夠 |
| `error TEXT` | 錯誤原文，可長 | TEXT |
| `meta JSONB` | prompt_hash, temperature, top_p, fingerprint, embedding_signature 等彈性擴展 | JSONB（非 JSON）支援索引 |

### 1.2 索引設計理由（5 個）

| Idx | Cols | 用途 | partial? |
|-----|------|------|---------|
| `idx_ai_calls_called_at` | (called_at DESC) | 全表時間切片，日報週報必用 | 否 |
| `idx_ai_calls_caller_called_at` | (caller, called_at DESC) | TOP caller / 單 caller 趨勢 | 否 |
| `idx_ai_calls_provider_called_at` | (provider, called_at DESC) | by provider 統計 / 預算追蹤 | 否 |
| `idx_ai_calls_request_id` | (request_id) | trace 單一 request 全鏈 | **WHERE request_id IS NOT NULL** |
| `idx_ai_calls_status_called_at` | (status, called_at DESC) | 異常監控 | **WHERE status <> 'ok'**（90%+ 是 ok，partial 大幅縮體） |

**未建立的索引**：
- `meta JSONB` 的 GIN index — V1 不建。GIN 寫入放大 ~3-5x，且尚未確定查詢 pattern；Phase 5 報表穩定後再評估。
- `model` 單欄索引 — 報表需求都會帶 called_at，已含 idx_ai_calls_called_at，再加 `(model, called_at)` 在 V1 邊際效益低。

### 1.3 是否 partition by called_at — 決策：**V1 不分區**

| 評估面 | 數字 | 結論 |
|--------|------|------|
| 月寫入量 | 50 ins/min × 60 × 24 × 30 ≈ 2.16M | 中等 |
| 90 天保留量 | ~6.5M | PostgreSQL 14 單表健康範圍 |
| 索引大小估算（5 個） | ~800MB | 在 momo-db 容器資源內 |
| Partition 維護成本 | 須 cron 自動 CREATE 下月 partition + DROP 過期 | **+1 維護負擔** |

**決策**：V1 不分區，但留好觸發升級條件：
- **觸發升級門檻**：月寫入超 5M、單表超 30M、或日報查詢 latency p95 > 500ms
- **升級路徑**：DECLARATIVE PARTITIONING by RANGE(called_at) monthly，配合 `pg_partman`

### 1.4 保留策略 — 90 天 hot data，DELETE 不 archive

| 選項 | 優劣 | 結論 |
|------|------|------|
| 直接 DELETE | 簡單，free space 由 autovacuum 回收 | **採用** |
| 移到 ai_calls_archive 表 | 多一份儲存，需另寫查詢 | 否 |
| 匯出 JSON 到 S3/GCS | 完整保留，可重建 | Phase 5 後若有合規需求再加 |

**理由**：ai_calls 是遙測，30 天前的單筆價值低；trend 已在週報/月報沉澱到 ai_insights。
**清理任務**（scheduler 每日 03:00）：
```sql
DELETE FROM ai_calls WHERE called_at < NOW() - INTERVAL '90 days';
```
配合 `idx_ai_calls_called_at DESC` 倒序掃描，DELETE 範圍小（每日 ~72k），不會 lock。

---

## Section 2 — 是否與既有 schema 衝突

### 2.1 與 `ai_generation_history`（4 處 ai_routes.py）

- 用途不同：ai_generation_history 是 **產品功能側**（is_favorite / is_used / created_by 都是業務欄位）
- ai_calls 是 **基礎設施側遙測**
- **共存策略**：A4 接 logger 時，ai_routes.py 那 4 處 **同時雙寫** 兩張表（既有 history 不破壞），ai_calls 是 superset

### 2.2 與 `ai_usage_tracking`（database/ai_models.py L72）

- ai_usage_tracking 已存在但**完全沒被 30 個呼叫點接入**（A1 audit 已驗證）
- 設計欄位（service_type / request_type / user_id）與 v5.0 戰役所需（caller / provider / fallback_to / request_id）不符
- **建議**：A4 logger 統一寫 ai_calls，ai_usage_tracking **凍結**（不寫入但不刪表，避免 model import 鏈斷裂）；待 Phase 5 報表驗證 ai_calls 完整後，Phase 12 再 deprecate

### 2.3 與 `ai_insights.embedding_signature`

- 既有 ai_insights 表**沒有** embedding_signature 欄位（已驗證 `database/ai_models.py:111-151`）
- 新增為 NULL，**metadata-only ALTER TABLE**，不鎖表（PostgreSQL 11+ 安全）
- **無衝突**

---

## Section 3 — 三個查詢效能預估

模擬負載：90 天滿載 ~6.5M 筆，索引 warm。

### 查詢 1：過去 24h 某 caller 的 token 累計 + 成本（Telegram 日報）

```sql
SELECT
    caller,
    SUM(input_tokens + output_tokens) AS total_tokens,
    SUM(cost_usd)                     AS total_cost,
    COUNT(*)                          AS call_count,
    AVG(duration_ms)                  AS avg_latency
FROM ai_calls
WHERE called_at >= NOW() - INTERVAL '24 hours'
  AND caller = 'openclaw_daily'
GROUP BY caller;
```

**預期執行計畫**：
```
Aggregate
  └─ Index Scan using idx_ai_calls_caller_called_at on ai_calls
       Index Cond: (caller = 'openclaw_daily' AND called_at >= ...)
```

**預期 latency**：< 5ms（單 caller 24h ~144 筆，索引完全命中）
**鎖風險**：無，純 SELECT。
**OLTP 衝擊**：無。

### 查詢 2：過去 7 天 by provider 統計（週報）

```sql
SELECT
    provider,
    COUNT(*)                          AS call_count,
    SUM(input_tokens + output_tokens) AS total_tokens,
    SUM(cost_usd)                     AS total_cost,
    SUM(CASE WHEN status='error'    THEN 1 ELSE 0 END) AS error_cnt,
    SUM(CASE WHEN status='fallback' THEN 1 ELSE 0 END) AS fallback_cnt,
    SUM(CASE WHEN cache_hit         THEN 1 ELSE 0 END) AS cache_hits
FROM ai_calls
WHERE called_at >= NOW() - INTERVAL '7 days'
GROUP BY provider
ORDER BY total_cost DESC;
```

**預期執行計畫**：
```
Sort
  └─ HashAggregate
       └─ Index Scan using idx_ai_calls_provider_called_at on ai_calls
            Index Cond: (called_at >= ...)
```

**預期 latency**：~50-150ms（7 天 ~500k 筆，6 個 provider HashAggregate）
**鎖風險**：無。
**優化建議**：若 latency 退化到 > 200ms，可加 covering index `(provider, called_at, input_tokens, output_tokens, cost_usd)` — V1 先不做。

### 查詢 3：TOP 10 caller by token（日報 Section 3）

```sql
SELECT
    caller,
    SUM(input_tokens + output_tokens) AS total_tokens,
    SUM(cost_usd)                     AS total_cost
FROM ai_calls
WHERE called_at >= NOW() - INTERVAL '24 hours'
GROUP BY caller
ORDER BY total_tokens DESC
LIMIT 10;
```

**預期執行計畫**：
```
Limit
  └─ Sort  (top-N)
       └─ HashAggregate
            └─ Index Scan using idx_ai_calls_called_at on ai_calls
                 Index Cond: (called_at >= ...)
```

**預期 latency**：~30-80ms（24h ~72k 筆，35 個 caller）
**鎖風險**：無。

### 查詢效能總表

| 查詢 | 預期 latency | 主要索引 | 改善空間 |
|------|-------------|---------|---------|
| Q1 caller 24h | < 5ms | idx_ai_calls_caller_called_at | 已最佳 |
| Q2 provider 7d | 50-150ms | idx_ai_calls_provider_called_at | 可加 covering index |
| Q3 TOP-10 caller 24h | 30-80ms | idx_ai_calls_called_at | OK |

---

## Section 4 — 寫入吞吐評估

### 4.1 尖峰負載

- **峰值**：50 inserts/min ≈ 0.83 ins/sec
- **單筆 insert 預估**：5 個索引 × ~1ms WAL flush ≈ 3-8ms
- **目標**：p99 < 50ms ✅（極大 buffer）

### 4.2 風險點

| 風險 | 機率 | 影響 | 緩解 |
|-----|-----|------|-----|
| async fire-and-forget 失敗無告警 | 中 | log 漏寫 | logger 端用 try/except + 告警 channel；連續 5 次失敗觸發 Telegram |
| 5 個索引導致寫入放大 | 低 | 同步寫入慢 | partial index 已縮減；50 ins/min 下不會擠壓 OLTP |
| autovacuum 跟不上 90 天 DELETE | 低 | 表膨脹 | 每日 03:00 DELETE，配 autovacuum_scale_factor=0.05 |

### 4.3 connection pool 衝擊

A4 logger 採 **fire-and-forget**（非同步寫，不阻塞 caller），須使用獨立 thread + dedicated session pool（建議 size=2，與主應用 pool 隔離），避免擠壓 OLTP。

---

## Section 5 — 風險與限制

### 5.1 已知限制

1. **ai_calls 不分區（V1）**：月寫入超 5M 或日報 latency p95 > 500ms 時須升級到 monthly partition
2. **JSONB meta 無 GIN index**：未來若要 `WHERE meta->>'prompt_hash' = ...` 查詢，需另加 GIN
3. **保留策略硬刪除**：30+ 天前的個別呼叫無法回溯（trend 須先進 ai_insights）
4. **ai_call_budgets.provider NULL 唯一性**：靠部分索引強制（PostgreSQL 標準 UNIQUE 不認 NULL）

### 5.2 護欄缺口（待後續 phase 補）

- **Phase 5**：ai_calls 寫入失敗的告警通道未定（建議走 EventRouter L0）
- **Phase 9**：ai_call_budgets 的 alert_pct 預設 80% 是否合理待實測；budget 超標的 hard-stop 邏輯由應用層實作
- **Phase 11**：embedding_signature 既有 ~XX 萬筆需批次回填（待 SSH 188 跑統計）

### 5.3 ALTER TABLE 026 安全性確認

- PostgreSQL 14（momo-db 容器版本，待 SSH 確認）
- 11+ 之後 ADD COLUMN 無 DEFAULT 為 metadata-only：**不鎖表，不重寫**
- CREATE INDEX CONCURRENTLY 不阻塞既有寫入，但**不能在 transaction 內**（migration 026 註記已標明）

---

## Section 6 — 部署 Checklist（給統帥）

執行順序與檢查（憲法 ADR-008 — 部署前必驗）：

- [ ] **SSH 188 確認 PostgreSQL 版本** ≥ 14（migration 026 ALTER TABLE 安全前提）
- [ ] **SSH 188 確認 momo-db 磁碟剩餘空間** ≥ 5GB（90 天滿載 ~3GB + headroom）
- [ ] **SSH 188 確認 ai_insights 既有筆數**：`SELECT COUNT(*), COUNT(embedding) FROM ai_insights;`（評估 Phase 11 回填工作量）
- [ ] 跑 024：`psql -U momo -d momo_pro -f migrations/024_create_ai_calls_table.sql`
- [ ] 跑 025：`psql -U momo -d momo_pro -f migrations/025_create_mcp_calls_and_budgets.sql`
- [ ] **跑 026 須注意**：含 `CREATE INDEX CONCURRENTLY`，**不能用 BEGIN/COMMIT 包**；用 `psql -1` 會失敗，須用一般 `psql`
- [ ] 026 後驗證：`\d ai_insights` 看到 embedding_signature 欄位 + idx_ai_insights_embedding_signature 索引
- [ ] 跑 sanity：`SELECT * FROM ai_call_budgets ORDER BY id;`（確認 5 筆種子）
- [ ] 通報 A4：可開始接 logger

---

## Section 7 — Commit Message 草稿（不自動 commit）

```
db: ai_calls/mcp_calls/budgets schema + bge-m3 signature (Operation Ollama-First v5.0 P1)

- migrations/024: ai_calls 統一 LLM 遙測表 (5 indexes, partial idx for sparse cols)
- migrations/025: mcp_calls + ai_call_budgets (Phase 10/9 預備, 含 5 筆種子預算)
- migrations/026: ai_insights.embedding_signature + partial index (BGE-M3 護欄)
- docs/phase1_db_design_20260503.md: 設計理由 + 查詢效能預估 + 部署 checklist

無 schema 衝突；ai_usage_tracking 凍結待 Phase 12 deprecate；A4 logger 可啟動。

依據: docs/phase0_audit_report_20260503.md (34 LLM 呼叫點 / 11.8% 覆蓋率)
```

---

## DB Expert Report（最終結論）

### 審查範圍
- 新增檔案：`migrations/024_create_ai_calls_table.sql`、`migrations/025_create_mcp_calls_and_budgets.sql`、`migrations/026_add_embedding_signature.sql`
- 影響資料表：`ai_calls`（新）、`mcp_calls`（新）、`ai_call_budgets`（新）、`ai_insights`（ADD COLUMN）

### 問題清單
無 BLOCKER。

#### 🟡 NOTE 1 — ai_usage_tracking 重疊
- 位置：`database/ai_models.py:72-109`
- 說明：既有但未被 30 個呼叫點使用，欄位語意不對齊。
- 風險：A4 寫 logger 時若誤雙寫此表會造成數據混亂。
- 緩解：在設計文 Section 2.2 已明示「凍結，Phase 12 再 deprecate」。

#### 🟡 NOTE 2 — Migration 026 不能用 BEGIN/COMMIT 包
- 位置：`migrations/026_add_embedding_signature.sql`
- 說明：`CREATE INDEX CONCURRENTLY` 不能在 transaction block 內執行。
- 緩解：已在檔頭註記，部署 checklist 已標明不用 `psql -1`。

### 效能分析
- Q1 caller-24h：< 5ms（idx_ai_calls_caller_called_at）
- Q2 provider-7d：50-150ms（idx_ai_calls_provider_called_at + HashAggregate）
- Q3 TOP-10 caller：30-80ms（idx_ai_calls_called_at + Top-N Sort）
- 寫入：3-8ms p50，p99 < 50ms 達標

### 結論
**APPROVED WITH NOTES** — Schema 已備妥，無阻擋 A4 logger 啟動的問題。

### 回滾路徑
三份 migration 檔頭皆附完整回滾 SQL；測試環境可一鍵 DROP。