◀ 回到 API 規格 | 返回目錄

目錄 (Table of Contents)¶

1. 概述
2. 實體關聯圖
3. 資料模型
4. User
5. UserStats
6. SocialAccount
7. WorkoutSession
8. Training
9. 索引策略
10. 資料完整性規則
11. 設計討論
12. 總結

概述 (Overview)¶

⚠️ IMPORTANT CLARIFICATION (Updated 2025-12-24):

What the Client (SolidFocus) Provided:

• ✅ Complete database schema (SQLModel classes with field names and types)
• ✅ Table relationships (Foreign Keys, OneToOne, OneToMany)
• ✅ JSONB usage for device-agnostic storage
• ✅ Architecture principles (Offline-First, APP calculates metrics)

What the Client Did NOT Provide:

• ❌ Business logic details (e.g., XP calculation formula, workout streak algorithm)
• ❌ Validation rules (e.g., password strength, email format)
• ❌ Index strategy details
• ❌ Cascade delete behavior

Current Status: This document includes:

1. Client's schema (field names, types, relationships) - ✅ Confirmed
2. Fugu's business logic proposals (calculations, validations) - ⚠️ Needs approval

Database Technology¶

Confirmed by Client: PostgreSQL 16+ with SQLModel ORM

Rationale (from client spec):

• Excellent JSON/JSONB support (for heterogeneous device data - client's key requirement)
• Strong data integrity and ACID compliance
• Good performance with proper indexing
• Native timezone-aware timestamp support
• SQLModel provides type-safe ORM with Pydantic validation

Design Philosophy (v3.0 - From Client Spec)¶

Client-Defined Principles:

1. Offline-First (離線優先): APP is source of truth, server is data archive center
2. Device-Agnostic Storage (異質數據標準化): Use JSONB for heterogeneous device data (bike, rower, treadmill)
3. Batch over Real-time: Minimize API calls, support batch upload with idempotency
4. Client-side Calculation: APP calculates ALL metrics (calories, distance, XP), server stores results
5. No Device Binding: Users can log in from multiple devices (v3.0 change)
6. Flexible JSON Fields: Use JSONB for evolving schemas without migrations

Fugu's Interpretations (need client confirmation):

• Idempotent upload using client_session_id + X-Idempotency-Key header
• Specific index strategy
• Cascade delete behavior
• Business logic algorithms (see "Business Rules" sections below)

實體關聯圖 (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¶

Purpose: Store user account information (no device binding in v3.0).

Table Name: users

Fields¶

Constraints¶

✅ Confirmed: - email must be unique across all users - user_uuid must be unique (used in API responses instead of id) - gender must be one of: 'm', 'f', 'o' - height_cm must be between 0-300 cm - weight_kg must be between 0-500 kg

Business Rules¶

1. User Identification:
2. id: Internal database ID (not exposed in API)
3. user_uuid: Public identifier (used in API responses)

4. Auto-generated on user creation

5. Display Name:

6. Required field (no default)
7. Can be updated via PATCH /users/me

8. Must be 1-100 characters

9. Email:

10. Cannot be changed via API (requires support intervention)
11. Used for password reset and notifications

12. Must be unique

13. Password:

14. NULL for SSO-only users (Apple/Google/Facebook)
15. Hashed using bcrypt or Argon2

16. Never exposed in API responses

17. Profile Fields:

18. height_cm, weight_kg: Optional, used for calorie calculations
19. avatar_url: Optional, updated via POST /users/me/avatar
20. gender: Defaults to 'o' (other)

2. UserStats¶

Purpose: Store aggregated user workout statistics (can be force-synced from APP).

Table Name: user_stats

Fields¶

Constraints¶

✅ Confirmed: - OneToOne relationship with User (enforced by PRIMARY KEY on user_id) - All numeric fields must be non-negative - current_level must be at least 1

Business Rules¶

1. Force-Sync from APP (v3.0 Change):

2. APP is source of truth for user stats

3. PATCH /users/me/stats allows APP to override server values
4. Server accepts APP's calculated values (mentioned in client's description: "強制以 APP 端的累計里程/XP/等級覆蓋伺服器")

5. Use case: Resolve sync conflicts when APP has more recent data

6. Update Strategy (from client architecture):

7. APP updates local stats after each workout

8. APP syncs to server via PATCH /users/me/stats periodically
9. Server does NOT auto-calculate (client confirmed: APP calculates, server stores)

⚠️ 未定義項目 (需要後續討論):

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

3. SocialAccount¶

Purpose: Store SSO (Social Sign-On) provider information for users.

Table Name: social_accounts

Fields¶

Constraints¶

✅ Confirmed: - Composite unique constraint on (provider, provider_uid) - same provider account can only link once - provider must be one of: 'apple', 'google', 'facebook' - extra_data stored as JSONB (not JSON)

Business Rules¶

1. Account Linking:
2. When user signs in with SSO for first time:
   • If email exists in User table → link to existing user
   • If email doesn't exist → create new User + link SocialAccount
3. One user can have multiple SocialAccounts (e.g., Apple + Google)

4. One SocialAccount (provider+provider_uid) can only link to one User

5. Extra Data Example:

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

6. Provider Support:

7. ✅ Phase 1: Apple SSO (priority)
8. ⚠️ Future: Google SSO

9. ⚠️ Future: Facebook Login

10. API Endpoint:

11. Single unified endpoint: POST /auth/sso/{provider}
12. Provider parameter: apple, google, or facebook

4. WorkoutSession¶

Purpose: Store individual workout session records (uploaded from APP via batch or single upload).

Table Name: workout_sessions

Fields¶

Constraints¶

✅ Confirmed (v3.0): - id (UUID) must be globally unique (primary key) - client_session_id must be globally unique (idempotency key, indexed) - end_time must be after start_time - duration_seconds must be positive (> 0) - device_type must be one of: 'bike', 'rower', 'treadmill' - source_type must be one of: 'backend', 'local', 'free' - total_calories, total_distance_meters, xp_earned must be non-negative (>= 0) - If source_type = 'backend', then training_id must be provided - If source_type = 'local', then internal_course_name should be provided

Business Rules¶

1. Idempotency with client_session_id:

   Format: UUIDv4 generated by APP
   Example: 550e8400-e29b-41d4-a716-446655440000

2. APP generates client_session_id using UUIDv4
3. Used in batch upload with X-Idempotency-Key header
4. Backend checks client_session_id for duplicate detection
5. Returns 200 OK for duplicates (idempotent behavior)

6. Ensures no duplicate sessions even when retrying uploads

7. Timestamp Handling:

8. start_time and end_time: Set by APP (actual workout time)
9. created_at: Set by server (upload/sync time)

10. All timestamps are timezone-aware (ISO 8601 format with UTC)

11. Duration Calculation:

    elapsed_time = end_time - start_time    (includes pauses)
    duration_seconds = actual workout time   (excludes pauses)

12. ✅ Store duration_seconds (pure workout time, calculated by APP)
13. ~~elapsed_time~~ can be derived from end_time - start_time

14. Example: 30-minute session with 5-minute pause

    • start_time: 2025-12-19T10:00:00Z
    • end_time: 2025-12-19T10:30:00Z
    • duration_seconds: 1500 (25 minutes)

15. Source Type Logic:

16. 'backend': Session follows a backend training course (must have training_id)
17. 'local': Session follows an APP built-in course (must have internal_course_name)

18. 'free': Free ride/row with no course (no training_id or internal_course_name)

19. Device-Agnostic Storage:

20. Same table supports bike, rower, treadmill data
21. Device-specific fields stored in JSONB metrics_summary
22. Different devices have different available metrics
23. Backend doesn't validate metric fields (APP responsibility)

JSONB Structure Examples¶

metrics_summary Example (Bike):

{
  "avg_heart_rate": 145,
  "max_heart_rate": 170,
  "min_heart_rate": 120,
  "avg_power": 180,
  "max_power": 250,
  "avg_cadence": 80,
  "max_cadence": 95,
  "avg_speed_kmh": 25.5,
  "max_speed_kmh": 32.0,
  "avg_resistance_level": 5,
  "max_resistance_level": 8,
  "calories_breakdown": {
    "active": 230.5,
    "basal": 20.0
  },
  "heart_rate_zones": {
    "zone1_seconds": 120,
    "zone2_seconds": 480,
    "zone3_seconds": 900,
    "zone4_seconds": 300,
    "zone5_seconds": 0
  }
}

metrics_summary Example (Rower):

{
  "avg_heart_rate": 155,
  "max_heart_rate": 180,
  "avg_stroke_rate": 24,
  "max_stroke_rate": 32,
  "avg_power": 220,
  "max_power": 310,
  "avg_pace_500m_seconds": 120,
  "best_pace_500m_seconds": 105,
  "total_strokes": 720
}

time_series_data Example (for charting):

[
  {
    "timestamp": 0,
    "power": 150,
    "heart_rate": 130,
    "cadence": 75,
    "speed_kmh": 22.0,
    "resistance_level": 4
  },
  {
    "timestamp": 5,
    "power": 160,
    "heart_rate": 135,
    "cadence": 78,
    "speed_kmh": 23.5,
    "resistance_level": 4
  },
  {
    "timestamp": 10,
    "power": 180,
    "heart_rate": 140,
    "cadence": 80,
    "speed_kmh": 25.0,
    "resistance_level": 5
  }
]

5. Training¶

Purpose: Store training course templates and configurations.

Table Name: trainings

Fields¶

Constraints¶

✅ Confirmed (v3.0): - type must be one of: 'course', 'game', 'free' - supported_devices must contain valid device types: ['bike', 'rower', 'treadmill'] - At least one device in supported_devices array (empty array not allowed)

Business Rules¶

1. Training Types:
2. course: Structured course with video and stages (e.g., "Taiwan Scenic Tour")
3. game: Gamified workout with interactive elements (e.g., "Dragon Boat Race")

4. free: Free ride/row with no structured course (may have video background)

5. Device Filtering:

6. APP can query trainings by device_type parameter: GET /trainings?device_type=bike
7. Returns only trainings where device_type IN supported_devices

8. Example: Training with supported_devices = ["bike", "rower"] shows up for both bike and rower users

9. Enabled Flag:

10. enabled = true: Training appears in list/detail endpoints
11. enabled = false: Training hidden from users (for drafts or deprecated content)

JSONB Structure Examples¶

settings Example (Course):

{
  "duration_minutes": 45,
  "difficulty": "medium",
  "has_video": true,
  "target_heart_rate_zone": "zone2",
  "tags": ["scenic", "taiwan", "climbing"],
  "calories_estimate": 350,
  "distance_estimate_km": 15.0,
  "stages": [
    {
      "order": 1,
      "duration_seconds": 300,
      "resistance_level": 3,
      "target_cadence_rpm": 70,
      "target_power_watts": 150,
      "description": "Warm-up: North Coast"
    },
    {
      "order": 2,
      "duration_seconds": 1800,
      "resistance_level": 7,
      "target_cadence_rpm": 80,
      "target_power_watts": 200,
      "description": "Climb: Yilan Mountains"
    },
    {
      "order": 3,
      "duration_seconds": 600,
      "resistance_level": 4,
      "target_cadence_rpm": 75,
      "target_power_watts": 170,
      "description": "Cool-down: Hualien Coast"
    }
  ]
}

settings Example (Game):

{
  "duration_minutes": 30,
  "difficulty": "hard",
  "game_type": "dragon_boat_race",
  "opponent_count": 3,
  "reward_xp": 500,
  "tags": ["competitive", "multiplayer"],
  "supported_devices": ["rower"]
}

supported_devices Example:

["bike", "rower"]

Video Delivery: - Format: HLS (HTTP Live Streaming) - .m3u8 playlist - Storage: Object storage (S3/GCS) + CDN - Access: Public URLs or signed URLs (for access control) - Adaptive bitrate for different network conditions

💡 Design Notes: - JSONB fields allow flexible schema evolution without database migrations - Different training types can have different settings structures - supported_devices enables multi-device training library (one training, multiple device types) - Consider JSON Schema validation in application layer for type safety

索引策略 (Indexes Strategy)¶

Performance Considerations (v3.0)¶

Index Rationale (v3.0)¶

1. Unique Indexes (Prevent Duplicates):
2. users.email: Prevent duplicate accounts
3. users.user_uuid: API uses UUID for user identification
4. workout_sessions.client_session_id: Critical for idempotency - prevents duplicate uploads
5. social_accounts.(provider, provider_uid): Prevent duplicate SSO links

6. ❌ REMOVED: bound_device_id (no device binding in v3.0)

7. Performance Indexes (Query Optimization):

8. workout_sessions.device_type: Filter sessions by device (bike/rower/treadmill)
9. workout_sessions.source_type: Filter by source (backend/local/free)
10. workout_sessions.start_time DESC: Fast sorting for recent sessions
11. trainings.type: Filter trainings by type (course/game/free)

12. trainings.enabled: Show only enabled trainings

13. Foreign Key Indexes (Relationship Queries):

14. All user_id foreign keys are indexed
15. All training_id foreign keys are indexed

💡 Implementation Note: Indexes will be created during backend implementation. Monitor query performance and add additional indexes as needed.

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

Database-Level Constraints¶

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

• Foreign Key relationships defined
• NOT NULL constraints defined
• Unique constraints on email, user_uuid, client_session_id

Unique Constraints:

• users.email UNIQUE ✅
• users.user_uuid UNIQUE ✅
• user_stats.user_id UNIQUE (OneToOne relationship) ✅
• workout_sessions.client_session_id UNIQUE (idempotency key) ✅
• (social_accounts.provider, social_accounts.provider_uid) UNIQUE composite ✅

⚠️ 未定義項目 (需要後續討論):

• Foreign Key Cascade 行為 (ON DELETE CASCADE/SET NULL)
• Check Constraints 驗證規則 (Enum validations, numeric validations)
• Application-level validations (FastAPI)

設計討論 (Design Discussions)¶

Key Design Philosophy Changes¶

🎯 Offline-First Architecture: - APP is source of truth for workout data - Server is data archive and sync center - No device binding - users can use any device - Batch upload with idempotency for reliability

🎯 Device-Agnostic Storage: - JSONB for heterogeneous device data (bike/rower/treadmill) - No strict schema validation on metrics - Vue admin panel dynamically renders based on device_type

Confirmed Design Decisions (v3.0)¶

✅ 1. JSONB for Device-Agnostic Metrics

Decision: Use JSONB for metrics_summary and time_series_data

Rationale: - Different devices have different available metrics (bike has cadence, rower has stroke rate) - No need to migrate schema when adding new device types - Backend doesn't validate metric fields - APP responsibility - Vue admin panel uses GET /meta/schemas for dynamic field definitions

Example: Bike has avg_cadence, rower has avg_stroke_rate - completely different structures stored in same JSONB field.

✅ 2. Batch Upload with Idempotency

Decision: Use client_session_id (UUIDv4) + X-Idempotency-Key header

Implementation: POST /sessions/batch_upload

Rationale: - Supports offline workout recording and batch sync - Prevents duplicate uploads even with retries - Reduces API calls (upload multiple sessions at once) - Simple UUIDv4 format (no MAC prefix needed)

Flow:

APP generates client_session_id → Stores locally →
Network available → Batch upload with Idempotency-Key →
Server checks client_session_id → Returns 200 OK for duplicates

✅ 3. APP Calculates Everything

Decision: Server stores what APP sends, no server-side computation

APP Responsibilities: - Calculate total_calories, total_distance_meters, xp_earned - Generate metrics_summary (avg/max values) - Generate time_series_data for charts - Calculate duration_seconds (excludes pauses)

Server Responsibilities: - Store data as-is (no validation of metric values) - Provide data archive and retrieval - Support force-sync of UserStats via PATCH /users/me/stats (APP wins)

Rationale: - Offline-First - APP must work without server - Device-agnostic - server doesn't know device-specific logic - Simpler backend - no complex business logic

✅ 4. UserStats Force Sync (APP Wins)

Decision: Allow PATCH /users/me/stats where APP can override server values

New Behavior in v3.0: - APP tracks stats locally during offline workouts - When APP detects divergence, it force-syncs to server - Server accepts APP values unconditionally (APP wins) - No automatic calculation triggers on server

Use Case:

APP offline → 5 workouts → APP stats: 10 hours total
Server online → Only has 5 hours (missed syncs)
APP sends PATCH /users/me/stats → total_duration_seconds: 36000
Server updates → Now matches APP

✅ 5. No Device Binding

Decision: Remove bound_device_id from User model

Rationale: - Users can use multiple devices (phone, tablet) - No need to unbind when switching devices - Simpler user experience - Session tracking doesn't depend on device binding

Open Questions for Future¶

⚠️ 1. Avatar Storage Location

Question: Where to store user avatar images?

Options: - A) Object storage (S3/GCS) with signed URLs - B) Base64 in database (simple but not scalable) - C) Local filesystem with CDN

Recommendation: Discuss with SolidFocus RD team

⚠️ 2. Meta Schemas Update Frequency

Question: How often should GET /meta/schemas endpoint be called?

Considerations: - Schemas change infrequently (only when adding new device types) - Could cache for 24 hours - Or version-based cache invalidation

Recommendation: Implement caching strategy in v1

⚠️ 3. Force Update Mechanism

Question: How to implement forced app updates via GET /meta/app_version?

Considerations: - What happens if user on old version? - Should API block old versions? - Or just show warning in APP?

Recommendation: Define policy before implementation

總結 (Summary)¶

Data Model Overview¶

Implementation Status¶

v3.0 (Current):

1. ✅ SQLModel data models defined
2. ✅ Offline-First architecture designed
3. ✅ Batch upload strategy specified
4. ✅ Device-agnostic storage (JSONB)
5. ✅ SSO support (Apple/Google/Facebook)
6. ✅ UserStats force-sync (APP wins)

Future Enhancements:

1. ⚠️ Avatar object storage implementation
2. ⚠️ Meta schemas caching strategy
3. ⚠️ Force update mechanism
4. ⚠️ Soft delete with audit trail
5. ⚠️ Email/password authentication

Next Steps¶

1. Backend Implementation (SolidFocus RD)
2. Set up PostgreSQL 16+ database
3. Implement SQLModel models in FastAPI
4. Create Alembic migrations
5. Implement batch upload endpoint with idempotency

6. Implement UserStats force-sync endpoint

7. APP Development (Fugu)

8. Implement local database matching server schema
9. Build batch upload sync logic
10. Implement UserStats local calculation

11. Test offline-first workflows

12. Integration Testing

13. Test batch upload with idempotency
14. Test offline workout and sync
15. Test UserStats force-sync behavior

16. Verify device-agnostic metrics storage

17. Documentation Updates

18. Update 04.1 API Specification with batch upload endpoint

◀ 回到 API 規格 | 返回目錄