1b4f3a7bbe
Some checks failed
CD Pipeline / deploy (push) Failing after 59s
- 建立 Gitea Actions CD pipeline (.gitea/workflows/cd.yaml) - 部署模式: rsync Python 檔案至 188 → docker restart (volume mount) - Dockerfile/requirements 變動時自動重建 Docker image - 部署通知: Telegram (開始/成功/失敗) - 健康檢查: https://mo.wooo.work/health (最多 5 次重試) - 同步最新 CLAUDE.md / ADR-008 / memory (2026-04-19) Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
408 lines
9.6 KiB
Markdown
408 lines
9.6 KiB
Markdown
# MOMO Pro System - 資料庫結構
|
||
|
||
> 最後更新：2026-01-29
|
||
|
||
---
|
||
|
||
## 📊 ERD 關聯圖
|
||
|
||
```mermaid
|
||
erDiagram
|
||
%% 核心商品模組
|
||
categories ||--o{ products : has
|
||
products ||--o{ price_records : has
|
||
|
||
%% 用戶模組
|
||
users ||--o{ login_history : has
|
||
users ||--o{ user_permissions : has
|
||
roles ||--o{ role_permissions : has
|
||
permissions ||--o{ role_permissions : has
|
||
permissions ||--o{ user_permissions : has
|
||
|
||
%% 廠商模組
|
||
vendor_list ||--o{ vendor_emails : has
|
||
vendor_list ||--o{ email_send_log : has
|
||
vendor_stockout ||--o{ email_send_log : references
|
||
|
||
%% AI 模組
|
||
users ||--o{ ai_generation_history : creates
|
||
users ||--o{ ai_usage_tracking : creates
|
||
users ||--o{ ai_prompt_templates : creates
|
||
|
||
%% 匯入模組
|
||
import_jobs }o--|| import_config : uses
|
||
|
||
%% 通知模組
|
||
notification_templates ||--o{ notification_logs : uses
|
||
|
||
%% 核心表定義
|
||
categories {
|
||
int id PK
|
||
string name UK
|
||
}
|
||
|
||
products {
|
||
int id PK
|
||
string i_code UK
|
||
string name
|
||
string url
|
||
text image_url
|
||
string category
|
||
string status
|
||
int category_id FK
|
||
datetime created_at
|
||
datetime updated_at
|
||
}
|
||
|
||
price_records {
|
||
int id PK
|
||
int product_id FK
|
||
float price
|
||
datetime timestamp
|
||
}
|
||
|
||
%% 用戶表
|
||
users {
|
||
int id PK
|
||
string username UK
|
||
string email UK
|
||
string password_hash
|
||
string role
|
||
string display_name
|
||
bool is_active
|
||
datetime password_changed_at
|
||
datetime created_at
|
||
datetime updated_at
|
||
}
|
||
|
||
login_history {
|
||
int id PK
|
||
int user_id FK
|
||
string username_attempted
|
||
datetime login_time
|
||
string ip_address
|
||
string user_agent
|
||
string status
|
||
string failure_reason
|
||
}
|
||
|
||
%% 廠商表
|
||
vendor_stockout {
|
||
int id PK
|
||
string batch_id
|
||
date import_date
|
||
string department
|
||
string section
|
||
string pm_name
|
||
string product_code
|
||
string product_name
|
||
string vendor_code
|
||
string vendor_name
|
||
int monthly_sales_qty
|
||
int current_stock
|
||
string status
|
||
bool is_duplicate
|
||
datetime sent_date
|
||
}
|
||
|
||
vendor_list {
|
||
int id PK
|
||
string vendor_code UK
|
||
string vendor_name
|
||
bool is_active
|
||
}
|
||
|
||
vendor_emails {
|
||
int id PK
|
||
int vendor_id FK
|
||
string email
|
||
string contact_name
|
||
string email_type
|
||
bool is_active
|
||
}
|
||
|
||
email_send_log {
|
||
int id PK
|
||
int vendor_id FK
|
||
int stockout_id FK
|
||
string batch_id
|
||
string sender_email
|
||
string recipient_email
|
||
string subject
|
||
int product_count
|
||
string status
|
||
text error_message
|
||
datetime sent_at
|
||
}
|
||
|
||
%% AI 表
|
||
ai_generation_history {
|
||
int id PK
|
||
string generation_type
|
||
string product_name
|
||
text input_keywords
|
||
string input_style
|
||
text output_content
|
||
string model_name
|
||
float generation_duration
|
||
string ai_provider
|
||
int input_tokens
|
||
int output_tokens
|
||
int rating
|
||
bool is_favorite
|
||
bool is_used
|
||
int created_by FK
|
||
datetime created_at
|
||
}
|
||
|
||
ai_usage_tracking {
|
||
int id PK
|
||
string provider
|
||
string model_name
|
||
string usage_type
|
||
int input_tokens
|
||
int output_tokens
|
||
float total_cost
|
||
date request_date
|
||
int created_by FK
|
||
}
|
||
|
||
ai_prompt_templates {
|
||
int id PK
|
||
string name UK
|
||
string description
|
||
string template_type
|
||
text system_prompt
|
||
text user_prompt_template
|
||
float default_temperature
|
||
bool is_active
|
||
bool is_system
|
||
}
|
||
|
||
%% 匯入表
|
||
import_jobs {
|
||
int id PK
|
||
string job_type
|
||
string status
|
||
string drive_file_id
|
||
string drive_file_name
|
||
float progress_percent
|
||
int total_rows
|
||
int processed_rows
|
||
datetime created_at
|
||
datetime completed_at
|
||
}
|
||
|
||
%% 月份總表
|
||
monthly_summary_analysis {
|
||
int id PK
|
||
int year
|
||
int month
|
||
string department
|
||
string division
|
||
string pm_name
|
||
string brand_name
|
||
int vendor_id
|
||
int sales_amt_curr
|
||
int sales_amt_prev
|
||
int profit_amt_curr
|
||
float conv_rate
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 📋 資料表清單
|
||
|
||
### 核心模組 (4 表)
|
||
|
||
| 資料表 | 說明 | 記錄數 (估計) |
|
||
|--------|------|--------------|
|
||
| `categories` | 商品分類 | ~20 |
|
||
| `products` | 商品主檔 | ~6,000 |
|
||
| `price_records` | 價格歷史記錄 | ~500,000 |
|
||
| `monthly_summary_analysis` | 月份業績總表 | ~50,000 |
|
||
|
||
---
|
||
|
||
### 用戶模組 (2 表)
|
||
|
||
| 資料表 | 說明 |
|
||
|--------|------|
|
||
| `users` | 用戶帳號 |
|
||
| `login_history` | 登入歷史記錄 |
|
||
|
||
#### users 欄位詳情
|
||
|
||
| 欄位 | 類型 | 說明 |
|
||
|------|------|------|
|
||
| id | INTEGER PK | 用戶 ID |
|
||
| username | VARCHAR(50) UK | 帳號名稱 |
|
||
| email | VARCHAR(120) UK | 電子郵件 |
|
||
| password_hash | VARCHAR(256) | bcrypt 雜湊密碼 |
|
||
| role | VARCHAR(20) | 角色 (admin/manager/user) |
|
||
| display_name | VARCHAR(100) | 顯示名稱 |
|
||
| is_active | BOOLEAN | 是否啟用 |
|
||
| password_changed_at | DATETIME | 密碼變更時間 |
|
||
| created_at | DATETIME | 建立時間 |
|
||
| updated_at | DATETIME | 更新時間 |
|
||
|
||
---
|
||
|
||
### 廠商模組 (4 表)
|
||
|
||
| 資料表 | 說明 |
|
||
|--------|------|
|
||
| `vendor_stockout` | 廠商缺貨記錄 |
|
||
| `vendor_list` | 廠商清單 |
|
||
| `vendor_emails` | 廠商聯絡郵件 |
|
||
| `email_send_log` | 郵件發送記錄 |
|
||
|
||
#### vendor_stockout 欄位詳情
|
||
|
||
| 欄位 | 類型 | 說明 |
|
||
|------|------|------|
|
||
| id | INTEGER PK | 記錄 ID |
|
||
| batch_id | VARCHAR(50) | 批次編號 (YYYYMMDD_HHMMSS) |
|
||
| import_date | DATE | 匯入日期 |
|
||
| department | VARCHAR(100) | 部別 |
|
||
| section | VARCHAR(100) | 課別 |
|
||
| pm_name | VARCHAR(100) | PM 姓名 |
|
||
| product_code | VARCHAR(100) | 商品料號 |
|
||
| product_name | VARCHAR(500) | 商品名稱 |
|
||
| vendor_code | VARCHAR(100) | 廠商代碼 |
|
||
| vendor_name | VARCHAR(200) | 廠商名稱 |
|
||
| monthly_sales_qty | INTEGER | 全月銷量 |
|
||
| current_stock | INTEGER | 現有庫存 |
|
||
| stockout_days | INTEGER | 缺貨天數 |
|
||
| status | VARCHAR(20) | 狀態 (pending/sent/failed) |
|
||
| is_duplicate | BOOLEAN | 是否重複 |
|
||
|
||
---
|
||
|
||
### AI 模組 (3 表)
|
||
|
||
| 資料表 | 說明 |
|
||
|--------|------|
|
||
| `ai_generation_history` | AI 生成歷史 |
|
||
| `ai_usage_tracking` | AI 用量追蹤 |
|
||
| `ai_prompt_templates` | AI 提示模板 |
|
||
|
||
#### ai_generation_history 欄位詳情
|
||
|
||
| 欄位 | 類型 | 說明 |
|
||
|------|------|------|
|
||
| id | INTEGER PK | 記錄 ID |
|
||
| generation_type | VARCHAR(50) | 類型 (copy/recommend/weather_analysis) |
|
||
| product_name | VARCHAR(255) | 商品名稱 |
|
||
| input_keywords | TEXT (JSON) | 輸入關鍵字 |
|
||
| input_style | VARCHAR(50) | 文案風格 |
|
||
| output_content | TEXT | 生成內容 |
|
||
| model_name | VARCHAR(100) | 模型名稱 |
|
||
| generation_duration | FLOAT | 生成耗時 (秒) |
|
||
| ai_provider | VARCHAR(20) | AI 提供者 (ollama/gemini) |
|
||
| input_tokens | INTEGER | 輸入 Token 數 |
|
||
| output_tokens | INTEGER | 輸出 Token 數 |
|
||
| rating | INTEGER | 用戶評分 (1-5) |
|
||
| is_favorite | BOOLEAN | 是否收藏 |
|
||
| is_used | BOOLEAN | 是否已使用 |
|
||
| created_by | INTEGER FK | 建立者 |
|
||
| created_at | DATETIME | 建立時間 |
|
||
|
||
---
|
||
|
||
### 匯入模組 (2 表)
|
||
|
||
| 資料表 | 說明 |
|
||
|--------|------|
|
||
| `import_jobs` | 匯入任務 |
|
||
| `import_config` | 匯入配置 |
|
||
|
||
---
|
||
|
||
### 權限模組 (3 表)
|
||
|
||
| 資料表 | 說明 |
|
||
|--------|------|
|
||
| `permissions` | 權限定義 |
|
||
| `role_permissions` | 角色權限關聯 |
|
||
| `user_permissions` | 用戶權限關聯 |
|
||
|
||
---
|
||
|
||
### 通知模組 (2 表)
|
||
|
||
| 資料表 | 說明 |
|
||
|--------|------|
|
||
| `notification_templates` | 通知模板 |
|
||
| `notification_logs` | 通知發送記錄 |
|
||
|
||
---
|
||
|
||
### 趨勢模組 (3 表)
|
||
|
||
| 資料表 | 說明 |
|
||
|--------|------|
|
||
| `trend_products` | 趨勢商品 |
|
||
| `trend_categories` | 趨勢分類 |
|
||
| `trend_keywords` | 趨勢關鍵字 |
|
||
|
||
---
|
||
|
||
## 🔑 索引設計
|
||
|
||
### 高頻查詢索引
|
||
|
||
```sql
|
||
-- 商品查詢
|
||
CREATE INDEX idx_products_i_code ON products(i_code);
|
||
CREATE INDEX idx_products_category ON products(category);
|
||
CREATE INDEX idx_products_status ON products(status);
|
||
|
||
-- 價格記錄查詢
|
||
CREATE INDEX idx_price_records_product_id ON price_records(product_id);
|
||
CREATE INDEX idx_price_records_timestamp ON price_records(timestamp);
|
||
|
||
-- 用戶查詢
|
||
CREATE INDEX idx_users_username ON users(username);
|
||
CREATE INDEX idx_users_role ON users(role);
|
||
|
||
-- 廠商缺貨查詢
|
||
CREATE INDEX idx_vendor_stockout_batch_id ON vendor_stockout(batch_id);
|
||
CREATE INDEX idx_vendor_stockout_vendor_code ON vendor_stockout(vendor_code);
|
||
CREATE INDEX idx_vendor_stockout_status ON vendor_stockout(status);
|
||
|
||
-- AI 歷史查詢
|
||
CREATE INDEX idx_ai_history_type_created ON ai_generation_history(generation_type, created_at);
|
||
CREATE INDEX idx_ai_history_favorite ON ai_generation_history(is_favorite, created_at);
|
||
```
|
||
|
||
---
|
||
|
||
## 📈 資料成長預估
|
||
|
||
| 資料表 | 日增長量 | 月增長量 | 保留策略 |
|
||
|--------|----------|----------|----------|
|
||
| price_records | ~6,000 | ~180,000 | 保留 1 年 |
|
||
| login_history | ~100 | ~3,000 | 保留 90 天 |
|
||
| email_send_log | ~50 | ~1,500 | 永久保留 |
|
||
| ai_generation_history | ~200 | ~6,000 | 永久保留 |
|
||
| ai_usage_tracking | ~200 | ~6,000 | 永久保留 |
|
||
|
||
---
|
||
|
||
## 🔄 資料庫遷移
|
||
|
||
### 使用 Alembic 管理
|
||
|
||
```bash
|
||
# 建立遷移
|
||
alembic revision --autogenerate -m "description"
|
||
|
||
# 執行遷移
|
||
alembic upgrade head
|
||
|
||
# 回滾遷移
|
||
alembic downgrade -1
|
||
```
|