5why-analyzer/docs/db_schema.md

# 資料庫架構文件 (Database Schema)

**專案**: 5 Why Root Cause Analyzer
**資料庫**: db_A102
**版本**: 1.0.0
**建立日期**: 2025-12-05
**最後更新**: 2025-12-05

---

## 目錄

- [概述](#概述)
- [資料表清單](#資料表清單)
- [資料表詳細說明](#資料表詳細說明)
- [關聯圖](#關聯圖)
- [索引策略](#索引策略)
- [視圖說明](#視圖說明)
- [資料字典](#資料字典)

---

## 概述

本系統使用 MySQL 8.0+ 資料庫，採用 InnoDB 引擎，支援交易處理和外鍵約束。資料庫設計遵循第三正規化（3NF），確保資料一致性和完整性。

### 連線資訊
- **主機**: mysql.theaken.com
- **Port**: 33306
- **資料庫**: db_A102
- **字元集**: utf8mb4
- **排序規則**: utf8mb4_unicode_ci

---

## 資料表清單

| # | 資料表名稱 | 說明 | 記錄數（預估） |
|---|-----------|------|--------------|
| 1 | `users` | 使用者資料表 | 100-1000 |
| 2 | `analyses` | 分析記錄表 | 10000+ |
| 3 | `analysis_perspectives` | 分析角度詳細表 | 30000+ |
| 4 | `analysis_whys` | 5 Why詳細記錄表 | 150000+ |
| 5 | `llm_configs` | LLM API配置表 | 10-50 |
| 6 | `system_settings` | 系統設定表 | 50-100 |
| 7 | `audit_logs` | 稽核日誌表 | 100000+ |
| 8 | `sessions` | Session表 | 100-1000 |

---

## 資料表詳細說明

### 1. users (使用者資料表)

儲存系統使用者的基本資料和認證資訊。

**欄位說明**:

| 欄位名稱 | 資料型態 | 限制 | 說明 |
|---------|---------|------|------|
| `id` | INT | PK, AUTO_INCREMENT | 使用者ID（主鍵） |
| `employee_id` | VARCHAR(50) | UNIQUE, NOT NULL | 工號 |
| `username` | VARCHAR(100) | NOT NULL | 使用者名稱 |
| `email` | VARCHAR(255) | UNIQUE, NOT NULL | Email |
| `password_hash` | VARCHAR(255) | NOT NULL | 密碼雜湊（bcrypt） |
| `role` | ENUM | DEFAULT 'user' | 權限等級（user/admin/super_admin） |
| `department` | VARCHAR(100) | NULL | 部門 |
| `position` | VARCHAR(100) | NULL | 職位 |
| `is_active` | BOOLEAN | DEFAULT TRUE | 帳號啟用狀態 |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 建立時間 |
| `updated_at` | TIMESTAMP | AUTO UPDATE | 更新時間 |
| `last_login_at` | TIMESTAMP | NULL | 最後登入時間 |

**索引**:
- PRIMARY KEY: `id`
- UNIQUE KEY: `employee_id`, `email`
- INDEX: `idx_employee_id`, `idx_email`, `idx_role`

**權限等級說明**:
- `user`: 一般使用者（可執行分析、查看自己的記錄）
- `admin`: 管理者（可管理使用者、查看所有記錄）
- `super_admin`: 最高權限管理者（完整系統控制權）

---

### 2. analyses (分析記錄表)

儲存每次 5 Why 分析的主要資料。

**欄位說明**:

| 欄位名稱 | 資料型態 | 限制 | 說明 |
|---------|---------|------|------|
| `id` | INT | PK, AUTO_INCREMENT | 分析記錄ID |
| `user_id` | INT | FK, NOT NULL | 使用者ID（外鍵） |
| `finding` | TEXT | NOT NULL | Finding描述 |
| `job_content` | TEXT | NOT NULL | 工作內容 |
| `output_language` | VARCHAR(10) | DEFAULT 'zh-TW' | 輸出語言 |
| `problem_restatement` | TEXT | NULL | 問題重述（5W1H） |
| `analysis_result` | JSON | NULL | 完整分析結果（JSON格式） |
| `status` | ENUM | DEFAULT 'pending' | 分析狀態 |
| `error_message` | TEXT | NULL | 錯誤訊息 |
| `processing_time` | INT | NULL | 處理時間（秒） |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 建立時間 |
| `updated_at` | TIMESTAMP | AUTO UPDATE | 更新時間 |

**索引**:
- PRIMARY KEY: `id`
- FOREIGN KEY: `user_id` REFERENCES `users(id)`
- INDEX: `idx_user_id`, `idx_status`, `idx_created_at`

**狀態說明**:
- `pending`: 待處理
- `processing`: 處理中
- `completed`: 完成
- `failed`: 失敗

---

### 3. analysis_perspectives (分析角度詳細表)

儲存每個分析的不同角度（流程面、系統面、管理面等）的詳細資料。

**欄位說明**:

| 欄位名稱 | 資料型態 | 限制 | 說明 |
|---------|---------|------|------|
| `id` | INT | PK, AUTO_INCREMENT | 分析角度ID |
| `analysis_id` | INT | FK, NOT NULL | 分析記錄ID（外鍵） |
| `perspective` | VARCHAR(100) | NOT NULL | 分析角度名稱 |
| `perspective_icon` | VARCHAR(10) | NULL | Emoji圖示 |
| `root_cause` | TEXT | NULL | 根本原因 |
| `permanent_solution` | TEXT | NULL | 永久性對策 |
| `logic_check_forward` | TEXT | NULL | 順向邏輯檢核 |
| `logic_check_backward` | TEXT | NULL | 逆向邏輯檢核 |
| `logic_valid` | BOOLEAN | DEFAULT TRUE | 邏輯是否有效 |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 建立時間 |

**索引**:
- PRIMARY KEY: `id`
- FOREIGN KEY: `analysis_id` REFERENCES `analyses(id)`
- INDEX: `idx_analysis_id`

---

### 4. analysis_whys (5 Why詳細記錄表)

儲存每個分析角度的 5 Why 問答詳細記錄。

**欄位說明**:

| 欄位名稱 | 資料型態 | 限制 | 說明 |
|---------|---------|------|------|
| `id` | INT | PK, AUTO_INCREMENT | Why記錄ID |
| `perspective_id` | INT | FK, NOT NULL | 分析角度ID（外鍵） |
| `level` | INT | NOT NULL | Why層級（1-5） |
| `question` | TEXT | NOT NULL | 問題 |
| `answer` | TEXT | NOT NULL | 答案 |
| `is_verified` | BOOLEAN | DEFAULT FALSE | 是否已驗證 |
| `verification_note` | TEXT | NULL | 驗證說明 |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 建立時間 |

**索引**:
- PRIMARY KEY: `id`
- FOREIGN KEY: `perspective_id` REFERENCES `analysis_perspectives(id)`
- INDEX: `idx_perspective_id`, `idx_level`

---

### 5. llm_configs (LLM API配置表)

儲存不同 LLM 提供商的 API 配置資訊。

**欄位說明**:

| 欄位名稱 | 資料型態 | 限制 | 說明 |
|---------|---------|------|------|
| `id` | INT | PK, AUTO_INCREMENT | 配置ID |
| `provider` | VARCHAR(50) | NOT NULL | LLM提供商 |
| `api_url` | VARCHAR(255) | NULL | API URL |
| `api_key` | VARCHAR(255) | NULL | API Key（加密） |
| `model_name` | VARCHAR(100) | NULL | 模型名稱 |
| `is_active` | BOOLEAN | DEFAULT FALSE | 是否啟用 |
| `max_tokens` | INT | DEFAULT 6000 | 最大Token數 |
| `temperature` | DECIMAL(3,2) | DEFAULT 0.7 | 溫度參數 |
| `timeout` | INT | DEFAULT 120000 | Timeout（毫秒） |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 建立時間 |
| `updated_at` | TIMESTAMP | AUTO UPDATE | 更新時間 |
| `created_by` | INT | FK, NULL | 建立者ID |

**索引**:
- PRIMARY KEY: `id`
- FOREIGN KEY: `created_by` REFERENCES `users(id)`
- INDEX: `idx_provider`, `idx_is_active`
- UNIQUE KEY: `unique_active_provider` (provider, is_active)

**支援的 Provider**:
- `ollama`: Ollama API
- `gemini`: Google Gemini
- `deepseek`: DeepSeek API
- `openai`: OpenAI API

---

### 6. system_settings (系統設定表)

儲存系統的各種設定參數。

**欄位說明**:

| 欄位名稱 | 資料型態 | 限制 | 說明 |
|---------|---------|------|------|
| `id` | INT | PK, AUTO_INCREMENT | 設定ID |
| `setting_key` | VARCHAR(100) | UNIQUE, NOT NULL | 設定鍵 |
| `setting_value` | TEXT | NULL | 設定值 |
| `setting_type` | VARCHAR(50) | DEFAULT 'string' | 設定類型 |
| `description` | TEXT | NULL | 說明 |
| `is_public` | BOOLEAN | DEFAULT FALSE | 是否公開（前端可見） |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 建立時間 |
| `updated_at` | TIMESTAMP | AUTO UPDATE | 更新時間 |
| `updated_by` | INT | FK, NULL | 更新者ID |

**索引**:
- PRIMARY KEY: `id`
- UNIQUE KEY: `setting_key`
- FOREIGN KEY: `updated_by` REFERENCES `users(id)`
- INDEX: `idx_setting_key`, `idx_is_public`

**設定類型**:
- `string`: 字串
- `number`: 數字
- `boolean`: 布林值
- `json`: JSON 物件

---

### 7. audit_logs (稽核日誌表)

記錄所有重要操作的稽核日誌，用於安全追蹤和問題排查。

**欄位說明**:

| 欄位名稱 | 資料型態 | 限制 | 說明 |
|---------|---------|------|------|
| `id` | INT | PK, AUTO_INCREMENT | 日誌ID |
| `user_id` | INT | FK, NULL | 使用者ID |
| `action` | VARCHAR(100) | NOT NULL | 動作類型 |
| `entity_type` | VARCHAR(50) | NULL | 實體類型 |
| `entity_id` | INT | NULL | 實體ID |
| `old_value` | JSON | NULL | 舊值 |
| `new_value` | JSON | NULL | 新值 |
| `ip_address` | VARCHAR(45) | NULL | IP位址 |
| `user_agent` | TEXT | NULL | User Agent |
| `status` | ENUM | DEFAULT 'success' | 執行狀態 |
| `error_message` | TEXT | NULL | 錯誤訊息 |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 建立時間 |

**索引**:
- PRIMARY KEY: `id`
- FOREIGN KEY: `user_id` REFERENCES `users(id)`
- INDEX: `idx_user_id`, `idx_action`, `idx_created_at`, `idx_entity`

**常見動作類型**:
- `login`: 登入
- `logout`: 登出
- `create_analysis`: 建立分析
- `update_user`: 更新使用者
- `delete_user`: 刪除使用者
- `update_llm_config`: 更新 LLM 配置
- `update_setting`: 更新系統設定

---

### 8. sessions (Session表)

儲存使用者的 Session 資料（使用 express-session）。

**欄位說明**:

| 欄位名稱 | 資料型態 | 限制 | 說明 |
|---------|---------|------|------|
| `session_id` | VARCHAR(128) | PK | Session ID |
| `expires` | BIGINT UNSIGNED | NOT NULL | 過期時間（Unix timestamp） |
| `data` | TEXT | NULL | Session 資料 |

**索引**:
- PRIMARY KEY: `session_id`
- INDEX: `idx_expires`

---

## 關聯圖

```
users (1) ──< (N) analyses
                    │
                    └──< (N) analysis_perspectives
                                │
                                └──< (N) analysis_whys

users (1) ──< (N) llm_configs (created_by)
users (1) ──< (N) system_settings (updated_by)
users (1) ──< (N) audit_logs
```

**關聯說明**:
- 一個使用者可以有多筆分析記錄
- 一筆分析記錄有多個分析角度（通常3個）
- 一個分析角度有多個 Why 記錄（1-5個）
- 所有外鍵使用 `ON DELETE CASCADE` 或 `ON DELETE SET NULL` 確保資料完整性

---

## 索引策略

### 主要索引
1. **主鍵索引**: 所有資料表的 `id` 欄位
2. **唯一索引**: `users.employee_id`, `users.email`, `system_settings.setting_key`
3. **外鍵索引**: 所有外鍵欄位自動建立索引

### 查詢最佳化索引
1. `analyses.idx_user_id`: 加速查詢使用者的分析記錄
2. `analyses.idx_status`: 加速查詢特定狀態的分析
3. `analyses.idx_created_at`: 加速時間範圍查詢
4. `audit_logs.idx_action`: 加速查詢特定動作的日誌
5. `llm_configs.idx_is_active`: 快速找到啟用的 LLM 配置

---

## 視圖說明

### 1. user_analysis_stats

使用者分析統計視圖，用於管理後台儀表板。

```sql
SELECT
    u.id AS user_id,
    u.username,
    u.employee_id,
    u.department,
    COUNT(a.id) AS total_analyses,
    COUNT(CASE WHEN a.status = 'completed' THEN 1 END) AS completed_analyses,
    COUNT(CASE WHEN a.status = 'failed' THEN 1 END) AS failed_analyses,
    AVG(a.processing_time) AS avg_processing_time,
    MAX(a.created_at) AS last_analysis_at
FROM users u
LEFT JOIN analyses a ON u.id = a.user_id
GROUP BY u.id;
```

### 2. recent_analyses

最近分析記錄視圖，顯示最近 100 筆分析。

```sql
SELECT
    a.id,
    a.finding,
    u.username,
    u.employee_id,
    a.output_language,
    a.status,
    a.processing_time,
    a.created_at
FROM analyses a
JOIN users u ON a.user_id = u.id
ORDER BY a.created_at DESC
LIMIT 100;
```

---

## 資料字典

### 語言代碼對照表

| 代碼 | 語言 |
|------|------|
| zh-TW | 繁體中文 |
| zh-CN | 简体中文 |
| en | English |
| ja | 日本語 |
| ko | 한국어 |
| vi | Tiếng Việt |
| th | ภาษาไทย |

### 權限等級對照表

| 等級 | 說明 | 權限 |
|------|------|------|
| user | 一般使用者 | 執行分析、查看自己的記錄 |
| admin | 管理者 | 使用者管理、查看所有記錄 |
| super_admin | 最高權限管理者 | 完整系統控制權 |

---

## 維護建議

### 定期維護
1. **清理過期 Sessions**: 每日清理過期的 session 記錄
2. **稽核日誌歸檔**: 每月將舊日誌移至歸檔表
3. **索引最佳化**: 每週執行 `OPTIMIZE TABLE`
4. **備份策略**: 每日全備份，每小時增量備份

### 效能監控
1. 監控慢查詢（> 1秒）
2. 監控資料表大小成長
3. 監控索引使用率
4. 監控連線數

---

**文件版本**: 1.0.0
**最後更新**: 2025-12-05
**維護者**: System Administrator