概述¶

城紹提供的內容（2025-12-22 版本）：

• 完整的資料庫 Schema（包含欄位名稱與型別的 SQLModel 類別）
• 資料表關聯（Foreign Keys、OneToOne、OneToMany）
• JSONB 用於異質裝置資料儲存
• 架構原則（Offline-First、APP 端計算指標）

尚未提供的內容：

• 驗證規則（如密碼強度、email 格式）
• 索引策略細節
• Cascade delete 行為

資料庫技術¶

目前預計使用：PostgreSQL 16+ 搭配 SQLModel ORM

設計哲學¶

1. 離線優先：APP 是資料來源的真相，Server 是資料封存中心
2. 異質數據標準化：使用 JSONB 儲存異質裝置資料（腳踏車、划船機、踏步機）
3. 批次優於即時：減少 API 呼叫次數，支援批次上傳與冪等性
4. 客戶端計算：APP 計算所有指標（卡路里、距離、XP），Server 僅儲存結果
5. 彈性 JSON 欄位：使用 JSONB 支援 Schema 演進，無需資料庫遷移

需要再確認的部份：

• 使用 client_session_id + X-Idempotency-Key header 實現冪等上傳
• 具體的索引策略
• Cascade delete 行為
• 業務邏輯演算法（參見下方「業務規則」章節）

實體關聯圖 (Entity Relationship Diagram)¶

┌─────────────────────────────────────────────────────────────────────┐
│                 SolidFocus Data Model (v3.0 - Offline-First)        │
└─────────────────────────────────────────────────────────────────────┘

┌──────────────────────┐           ┌──────────────────────┐
│       User           │           │   SocialAccount      │
├──────────────────────┤           ├──────────────────────┤
│ id (PK)              │◄─────────┤ id (PK)              │
│ user_uuid (UQ, IDX)  │    1:N    │ user_id (FK)         │
│ email (UNIQUE)       │           │ provider             │
│ hashed_password      │           │ provider_uid         │
│ display_name         │           │ extra_data (JSONB)   │
│ avatar_url           │           │ created_at           │
│ gender (m/f/o)       │           └──────────────────────┘
│ height_cm            │
│ weight_kg            │
│ is_active            │
│ created_at           │
└──────────────────────┘
         │
         │ 1:1
         │
┌──────────────────────────────┐
│         UserStats            │
├──────────────────────────────┤
│ user_id (PK, FK)             │◄────── OneToOne relationship
│ total_distance_km            │
│ total_duration_seconds       │        (Can be force-synced from APP
│ total_calories               │         via PATCH /users/me/stats)
│ total_xp                     │
│ current_level                │
│ workout_streak_days          │
│ last_workout_date            │
└──────────────────────────────┘
         ▲
         │
         │ APP calculates, Server archives
         │
┌──────────────────────────────┐           ┌──────────────────────┐
│      WorkoutSession          │           │      Training        │
├──────────────────────────────┤           ├──────────────────────┤
│ id (UUID, PK)                │           │ id (PK)              │
│ client_session_id (UQ, IDX)  │           │ title                │
│ user_id (FK)                 │───┐       │ type (course/game/   │
│ training_id (FK, NULLABLE)   │───┼──────►│      free)           │
│ internal_course_name         │   │  N:1  │ cover_url            │
│ source_type (backend/local/  │   │       │ video_url            │
│              free)            │   │       │ settings (JSONB)     │
│ device_type (bike/rower/     │   │       │ supported_devices    │
│              treadmill)       │   │       │   (JSONB array)      │
│ start_time                   │   │       │ enabled              │
│ end_time                     │   │       │ created_at           │
│ duration_seconds             │   │       └──────────────────────┘
│ total_calories               │   │
│ total_distance_meters        │   │
│ xp_earned                    │   │       User owns multiple Sessions
│ metrics_summary (JSONB)      │   └──────────────┐
│ time_series_data (JSONB[])   │                   │
│ created_at                   │                   │
└──────────────────────────────┘                   ▼
                                            (Aggregated to UserStats)

資料模型 (Data Models)¶

1. User¶

用途：儲存使用者帳號資訊。

資料表名稱：users

欄位¶

限制條件¶

• email 必須在所有使用者中唯一
• user_uuid 必須唯一（API 回應使用，而非 id）
• gender 必須為以下其一：'m'、'f'、'o'
• height_cm 必須介於 0-300 公分
• weight_kg 必須介於 0-500 公斤

業務規則¶

使用者識別：

• id：內部資料庫 ID（不在 API 中暴露）
• user_uuid：公開識別碼（用於 API 回應）
• 使用者建立時自動產生

顯示名稱：

• 必填欄位（無預設值）
• 可透過 PATCH /users/me 更新
• 必須為 1-100 字元

Email：

• 無法透過 API 變更（需客服介入）
• 用於密碼重設與通知
• 必須唯一

密碼：

• SSO 專用使用者為 NULL（Apple/Google/Facebook）
• 使用 bcrypt 或 Argon2 雜湊
• 永不在 API 回應中暴露

個人檔案欄位：

• height_cm、weight_kg：選填，用於卡路里計算
• avatar_url：選填，透過 POST /users/me/avatar 更新
• gender：預設為 'o'（其他）

2. UserStats¶

用途：儲存使用者累計運動統計資料（可從 APP 強制同步）。

資料表名稱：user_stats

欄位¶

限制條件¶

• 與 User 為 OneToOne 關聯（透過 user_id 的 PRIMARY KEY 強制執行）
• 所有數值欄位必須為非負數
• current_level 必須至少為 1

業務規則¶

從 APP 強制同步（v3.0 變更）：

• APP 是使用者統計資料的真相來源
• PATCH /users/me/stats 允許 APP 覆寫 Server 數值
• Server 接受 APP 計算的數值
• 使用情境：當 APP 有更新的資料時，解決同步衝突

更新策略：

• APP 在每次運動後更新本地統計資料
• APP 定期透過 PATCH /users/me/stats 同步至 Server
• Server 不會自動計算（客戶確認：APP 計算，Server 僅儲存）

未定義項目（需要後續討論）：

• Workout streak 連續天數計算邏輯
• Level 等級計算門檻
• XP 經驗值計算公式

3. SocialAccount¶

用途：儲存使用者的 SSO（Social Sign-On）供應商資訊。

資料表名稱：social_accounts

欄位¶

限制條件¶

• 對 (provider, provider_uid) 的複合唯一限制 - 相同供應商帳號只能連結一次
• provider 必須為以下其一：'apple'、'google'、'facebook'
• extra_data 儲存為 JSONB（非 JSON）

業務規則¶

帳號連結：

• 當使用者首次使用 SSO 登入時：
  • 如果 email 已存在於 User 資料表 → 連結至現有使用者
  • 如果 email 不存在 → 建立新的 User + 連結 SocialAccount
• 一個使用者可以有多個 SocialAccount（例如 Apple + Google）
• 一個 SocialAccount（provider+provider_uid）只能連結至一個 User

額外資料範例：

{
  "email": "user@privaterelay.appleid.com",
  "email_verified": true,
  "name": {
    "firstName": "John",
    "lastName": "Doe"
  },
  "apple_id_token_expires_at": "2025-12-18T10:30:00Z"
}

支援的 SSO：

• 第一階段：Apple SSO（優先）
• 未來：Google SSO
• 未來：Facebook Login

API Endpoint：

• 單一統一端點：POST /auth/sso/{provider}
• Provider 參數：apple、google 或 facebook

4. WorkoutSession¶

用途：儲存個別運動 session 記錄（從 APP 透過批次或單筆上傳）。

資料表名稱：workout_sessions

欄位¶

限制條件¶

• id（UUID）必須全域唯一（primary key）
• client_session_id 必須全域唯一（冪等性金鑰，已建立索引）
• end_time 必須晚於 start_time
• duration_seconds 必須為正數（> 0）
• device_type 必須為以下其一：'bike'、'rower'、'treadmill'
• source_type 必須為以下其一：'backend'、'local'、'free'
• total_calories、total_distance_meters、xp_earned 必須為非負數（>= 0）
• 如果 source_type = 'backend'，則必須提供 training_id
• 如果 source_type = 'local'，則應提供 internal_course_name

業務規則¶

使用 client_session_id 實現冪等性：

格式：APP 產生的 UUIDv4
範例：550e8400-e29b-41d4-a716-446655440000

• APP 使用 UUIDv4 產生 client_session_id
• 在批次上傳時與 X-Idempotency-Key header 搭配使用
• Backend 檢查 client_session_id 以偵測重複項目
• 針對重複項目回傳 200 OK（冪等行為）
• 即使重試上傳也能確保不會有重複的 session

時間戳記處理：

• start_time 與 end_time：由 APP 設定（實際運動時間）
• created_at：由 Server 設定（上傳/同步時間）
• 所有時間戳記皆為時區感知（ISO 8601 格式，UTC）

時長計算：

elapsed_time = end_time - start_time    (包含暫停時間)
duration_seconds = actual workout time   (不含暫停時間)

• ✅ 儲存 duration_seconds（純運動時間，由 APP 計算）
• elapsed_time 可從 end_time - start_time 導出
• 範例：30 分鐘 session，其中有 5 分鐘暫停
  • start_time: 2025-12-19T10:00:00Z
  • end_time: 2025-12-19T10:30:00Z
  • duration_seconds: 1500（25 分鐘）

來源類型邏輯：

• 'backend'：Session 遵循 Backend training 課程（必須有 training_id）
• 'local'：Session 遵循 APP 內建課程（必須有 internal_course_name）
• 'free'：自由騎乘/划船，無課程（無 training_id 或 internal_course_name）

異質裝置儲存：

• 同一資料表支援 bike、rower、treadmill 資料
• 裝置專屬欄位儲存於 JSONB metrics_summary
• 不同裝置有不同的可用指標
• Backend 不驗證指標欄位（APP 的責任）

JSONB 結構範例¶

這個部份再麻煩城紹提供之前踏步機的資料，如

• 機器多久傳到 MQTT 一次
• 一次內含多少筆 raw data
• JSONB 的範例... 等

5. Training¶

用途：儲存 training 課程範本與設定。

資料表名稱：trainings

欄位¶

限制條件¶

• type 必須為以下其一：'course'、'game'、'free'
• supported_devices 必須包含有效的裝置類型：['bike', 'rower', 'treadmill']
• supported_devices 陣列至少要有一個裝置（不允許空陣列）

業務規則¶

Training 類型：

• course：結構化課程，包含影片與階段（例如「台灣風景之旅」）
• game：遊戲化運動，包含互動元素（例如「龍舟競賽」）
• free：自由騎乘/划船，無結構化課程（可能有影片背景）

裝置篩選：

• APP 可透過 device_type 參數查詢 training：GET /trainings?device_type=bike
• 僅回傳 device_type IN supported_devices 的 training
• 範例：supported_devices = ["bike", "rower"] 的 Training 會同時出現在 bike 與 rower 使用者的列表中

Enabled 旗標：

• enabled = true：Training 出現在列表/詳細資料端點
• enabled = false：對使用者隱藏 Training（用於草稿或已棄用內容）

JSONB 結構範例¶

再由雙方制定 JSONB 格式。

💡 設計備註：

• JSONB 欄位允許彈性的 Schema 演進，無需資料庫遷移
• 不同 training 類型可以有不同的 settings 結構
• supported_devices 支援多裝置 training 資料庫（一個 training，多種裝置類型）
• 建議在應用層實作 JSON Schema 驗證以確保型別安全

索引策略 (Indexes Strategy)¶

效能考量（v3.0）¶

索引原理（v3.0）¶

唯一索引（防止重複）：

• users.email：防止重複帳號
• users.user_uuid：API 使用 UUID 進行使用者識別
• workout_sessions.client_session_id：冪等性關鍵 - 防止重複上傳
• social_accounts.(provider, provider_uid)：防止重複的 SSO 連結

效能索引（查詢最佳化）：

• workout_sessions.device_type：依裝置篩選 session（bike/rower/treadmill）
• workout_sessions.source_type：依來源篩選（backend/local/free）
• workout_sessions.start_time DESC：快速排序最近的 session
• trainings.type：依類型篩選 training（course/game/free）
• trainings.enabled：僅顯示已啟用的 training

Foreign Key 索引（關聯查詢）：

• 所有 user_id foreign key 都已建立索引
• 所有 training_id foreign key 都已建立索引

💡 實作備註：索引將在 Backend 實作期間建立。需監控查詢效能，並視需要新增額外索引。

資料完整性規則 (Data Integrity Rules)¶

資料庫層級限制條件¶

✅ 客戶提供的資料庫架構：

• 已定義 Foreign Key 關聯
• 已定義 NOT NULL 限制條件
• 已在 email、user_uuid、client_session_id 上定義唯一限制條件

唯一限制條件：

• users.email UNIQUE ✅
• users.user_uuid UNIQUE ✅
• user_stats.user_id UNIQUE（OneToOne 關聯）✅
• workout_sessions.client_session_id UNIQUE（冪等性金鑰）✅
• (social_accounts.provider, social_accounts.provider_uid) UNIQUE composite ✅

⚠️ 未定義項目（需要後續討論）：

• Foreign Key Cascade 行為（ON DELETE CASCADE/SET NULL）
• Check Constraints 驗證規則（Enum 驗證、數值驗證）
• 應用層驗證（FastAPI）

設計討論 (Design Discussions)¶

關鍵設計哲學變更¶

🎯 Offline-First 架構：

• APP 是運動資料的真相來源
• Server 是資料封存與同步中心
• 無裝置綁定 - 使用者可使用任何裝置
• 批次上傳搭配冪等性以確保可靠性

🎯 異質裝置儲存：

• 使用 JSONB 儲存異質裝置資料（bike/rower/treadmill）
• 對指標不進行嚴格的 Schema 驗證
• Vue admin panel 依據 device_type 動態渲染

根據 v3.0 的設計決策整理¶

✅ 1. 使用 JSONB 儲存異質裝置指標

決策：對 metrics_summary 與 time_series_data 使用 JSONB

原理：

• 不同裝置有不同的可用指標（bike 有 cadence，rower 有 stroke rate）
• 新增裝置類型時無需進行 Schema 遷移
• Backend 不驗證指標欄位 - APP 的責任
• Vue admin panel 使用 GET /meta/schemas 取得動態欄位定義

範例：Bike 有 avg_cadence，rower 有 avg_stroke_rate - 完全不同的結構儲存於同一個 JSONB 欄位。

✅ 2. 批次上傳搭配冪等性

決策：使用 client_session_id（UUIDv4）+ X-Idempotency-Key header

實作方式：POST /sessions/batch_upload

原理：

• 支援離線運動記錄與批次同步
• 即使重試也能防止重複上傳
• 減少 API 呼叫次數（一次上傳多個 session）
• 簡單的 UUIDv4 格式（不需要 MAC 前綴）

流程：

APP 產生 client_session_id → 本地儲存 →
網路可用 → 使用 Idempotency-Key 批次上傳 →
Server 檢查 client_session_id → 針對重複項目回傳 200 OK

✅ 3. APP 計算所有內容

決策：Server 儲存 APP 傳送的內容，不進行伺服器端計算

APP 的責任：

• 計算 total_calories、total_distance_meters、xp_earned
• 產生 metrics_summary（平均值/最大值）
• 產生 time_series_data 用於圖表
• 計算 duration_seconds（不含暫停時間）

Server 的責任：

• 原樣儲存資料（不驗證指標數值）
• 提供資料封存與檢索
• 透過 PATCH /users/me/stats 支援 UserStats 強制同步（APP 優先）

原理：

• Offline-First - APP 必須在無 Server 的情況下運作
• 異質裝置 - Server 不知道裝置特定邏輯
• 更簡單的 Backend - 無複雜業務邏輯

✅ 4. UserStats 強制同步（APP 優先）

決策：允許 PATCH /users/me/stats，APP 可覆寫 Server 數值

v3.0 的新行為：

• APP 在離線運動期間在本地追蹤統計資料
• 當 APP 偵測到差異時，會強制同步至 Server
• Server 無條件接受 APP 數值（APP 優先）
• Server 上無自動計算觸發

使用情境：

APP 離線 → 5 次運動 → APP 統計：總計 10 小時
Server 上線 → 僅有 5 小時（錯過的同步）
APP 傳送 PATCH /users/me/stats → total_duration_seconds: 36000
Server 更新 → 現在與 APP 一致

✅ 5. 無裝置綁定

決策：從 User 模型移除 bound_device_id

需要討論

如果沒有這個欄位，則無法實作單一裝置綁定的限制，進而會導致同步問題。

其他待討論的問題¶

⚠️ 1. Meta Schemas 更新頻率

問題：GET /meta/schemas 端點應多久呼叫一次？

考量因素：

• Schema 變更不頻繁（僅在新增裝置類型時）
• 可快取 24 小時
• 或使用基於版本的快取失效

建議：在 v1 中實作快取策略

⚠️ 2. 強制更新機制

問題：如何透過 GET /meta/app_version 實作強制 App 更新？

考量因素：

• 如果使用者使用舊版本會發生什麼？
• API 應該封鎖舊版本嗎？
• 還是只在 APP 中顯示警告？

建議：在實作前定義政策