◀ 上一章：資料流與同步策略 | 下一章：API Contract ▶

本文件定義 SolidFocus App 的本地資料庫 Schema，使用 WatermelonDB 作為 React Native 的離線資料庫方案。

涵蓋內容:

• 技術選型理由 (為何選擇 WatermelonDB)
• 5 個 Tables 的 Schema 設計
• TypeScript Models 定義
• CRUD 操作與 React 整合範例
• 與 Server 的同步策略

目錄¶

1. 技術選型：為何選 WatermelonDB
2. Schema 設計
3. Models 定義
4. 使用範例
5. 同步策略

技術選型：為何選 WatermelonDB¶

WatermelonDB vs 其他方案¶

決策：WatermelonDB ⭐¶

理由： - ✅ 完美符合 Offline-First 架構 - ✅ 為 React Native 優化（observables + hooks） - ✅ 效能優秀（lazy loading） - ✅ 套件體積小 - ✅ TypeScript 支援完整

Schema 設計¶

整體架構¶

Local Database (WatermelonDB)
├─ users (使用者資料)
├─ sessions (運動記錄)
├─ trainings (課程列表)
├─ training_videos (影片下載資訊)
└─ sync_queue (同步佇列)

Schema 定義¶

// src/database/schema.ts

import { appSchema, tableSchema } from '@nozbe/watermelondb';

export const schema = appSchema({
  version: 1,
  tables: [
    // ==========================================
    // 1. Users Table (使用者資料)
    // ==========================================
    tableSchema({
      name: 'users',
      columns: [
        { name: 'user_id', type: 'string' },              // Server 的 User ID
        { name: 'email', type: 'string', isOptional: true },
        { name: 'display_name', type: 'string', isOptional: true },
        { name: 'avatar_url', type: 'string', isOptional: true },
        { name: 'bound_device_id', type: 'string', isOptional: true }, // 綁定的裝置 ID

        // Stats (嵌入在 user 表，避免額外 join)
        { name: 'workout_streak', type: 'number' },       // 連續運動天數
        { name: 'workout_total_count', type: 'number' },  // 總運動次數
        { name: 'workout_total_duration', type: 'number' }, // 總運動時長 (秒)
        { name: 'workout_total_energy', type: 'number' }, // 總消耗卡路里
        { name: 'workout_total_distance', type: 'number' }, // 總距離 (公尺)

        // Tokens
        { name: 'access_token', type: 'string', isOptional: true },
        { name: 'refresh_token', type: 'string', isOptional: true },
        { name: 'token_expires_at', type: 'number', isOptional: true }, // Unix timestamp

        // Metadata
        { name: 'synced_at', type: 'number', isOptional: true }, // 最後同步時間
        { name: 'created_at', type: 'number' },
        { name: 'updated_at', type: 'number' },
      ]
    }),

    // ==========================================
    // 2. Sessions Table (運動記錄)
    // ==========================================
    tableSchema({
      name: 'sessions',
      columns: [
        { name: 'session_id', type: 'string', isIndexed: true }, // UUID (由 App 產生)
        { name: 'user_id', type: 'string', isIndexed: true },
        { name: 'training_id', type: 'number', isOptional: true, isIndexed: true }, // 關聯到 trainings

        // Session Info
        { name: 'device_type', type: 'string' },          // "bike" | "rower"
        { name: 'started_at', type: 'number', isIndexed: true }, // Unix timestamp (用於排序)
        { name: 'ended_at', type: 'number', isOptional: true },
        { name: 'duration', type: 'number' },             // 秒數

        // Metrics
        { name: 'total_energy', type: 'number' },         // 卡路里
        { name: 'total_distance', type: 'number' },       // 公尺

        // Detail (JSON string)
        { name: 'detail', type: 'string' },               // JSONB: { avg_hr, avg_power, max_hr, ... }

        // Raw Data (optional, Base64 encoded)
        { name: 'raw', type: 'string', isOptional: true }, // Base64 BLE data

        // Upload Status
        { name: 'upload_status', type: 'string' },        // "pending" | "uploaded" | "failed"
        { name: 'upload_retry_count', type: 'number' },   // 重試次數
        { name: 'uploaded_at', type: 'number', isOptional: true }, // 上傳成功時間

        // Metadata
        { name: 'created_at', type: 'number' },
        { name: 'updated_at', type: 'number' },
      ]
    }),

    // ==========================================
    // 3. Trainings Table (課程列表)
    // ==========================================
    tableSchema({
      name: 'trainings',
      columns: [
        { name: 'training_id', type: 'number', isIndexed: true }, // Server 的 Training ID
        { name: 'name', type: 'string' },
        { name: 'description', type: 'string', isOptional: true },
        { name: 'device_type', type: 'string' },          // "bike" | "rower"
        { name: 'difficulty', type: 'string', isOptional: true }, // "beginner" | "intermediate" | "advanced"
        { name: 'duration', type: 'number' },             // 預計時長 (秒)
        { name: 'target_energy', type: 'number', isOptional: true }, // 目標卡路里

        // Video Info
        { name: 'has_video', type: 'boolean' },           // 是否有影片
        { name: 'video_url', type: 'string', isOptional: true }, // Server 影片 URL
        { name: 'video_size', type: 'number', isOptional: true }, // 檔案大小 (bytes)
        { name: 'video_duration', type: 'number', isOptional: true }, // 影片長度 (秒)

        // Cover Image
        { name: 'cover_url', type: 'string', isOptional: true },

        // Stage Settings (JSON string)
        { name: 'stages', type: 'string', isOptional: true }, // JSONB: [{ duration, resistance, ... }]

        // Metadata
        { name: 'cache_timestamp', type: 'number' },      // 快取時間
        { name: 'created_at', type: 'number' },
        { name: 'updated_at', type: 'number' },
      ]
    }),

    // ==========================================
    // 4. Training Videos Table (影片下載資訊)
    // ==========================================
    tableSchema({
      name: 'training_videos',
      columns: [
        { name: 'training_id', type: 'number', isIndexed: true }, // 關聯到 trainings

        // Download Status
        { name: 'download_status', type: 'string' },      // "not_downloaded" | "downloading" | "downloaded" | "update_available"
        { name: 'download_progress', type: 'number' },    // 0-100

        // Local File Info
        { name: 'local_video_path', type: 'string', isOptional: true }, // 本地檔案路徑
        { name: 'local_file_size', type: 'number', isOptional: true }, // 實際檔案大小

        // Version Control (用於檢測更新)
        { name: 'server_video_url', type: 'string' },     // Server 的影片 URL (用於比對)
        { name: 'downloaded_at', type: 'number', isOptional: true }, // 下載時間

        // Metadata
        { name: 'created_at', type: 'number' },
        { name: 'updated_at', type: 'number' },
      ]
    }),

    // ==========================================
    // 5. Sync Queue Table（初期版本已移除）
    // ==========================================
    // ❌ 初期版本簡化設計：不使用獨立的 sync queue table
    //
    // **理由**：
    // - 降低實作複雜度
    // - `sessions.upload_status` 欄位足以管理上傳狀態
    // - 開發效率與成本考量
    //
    // **實作方式**：
    // - 使用 `sessions` table 的 `upload_status` 欄位（'pending' | 'uploaded'）
    // - 使用 `upload_retry_count` 欄位追蹤失敗次數
    // - 查詢 pending sessions：`Q.where('upload_status', 'pending')`
    //
    // **未來升級**（如有需要）：
    // - Iteration 2 可考慮加入獨立的 sync queue table
    // - 支援更複雜的 retry 策略與錯誤追蹤
    //
    // **詳細設計**請參考：`docs/future-plans/background-sync-retry-queue.md`
  ]
});

Models 定義¶

1. User Model¶

// src/database/models/User.ts

import { Model } from '@nozbe/watermelondb';
import { field, readonly, date, children } from '@nozbe/watermelondb/decorators';
import { Associations } from '@nozbe/watermelondb/Model';

export default class User extends Model {
  static table = 'users';
  static associations: Associations = {
    sessions: { type: 'has_many', foreignKey: 'user_id' },
  };

  @field('user_id') userId!: string;
  @field('email') email?: string;
  @field('display_name') displayName?: string;
  @field('avatar_url') avatarUrl?: string;
  @field('bound_device_id') boundDeviceId?: string;

  // Stats
  @field('workout_streak') workoutStreak!: number;
  @field('workout_total_count') workoutTotalCount!: number;
  @field('workout_total_duration') workoutTotalDuration!: number;
  @field('workout_total_energy') workoutTotalEnergy!: number;
  @field('workout_total_distance') workoutTotalDistance!: number;

  // Tokens
  @field('access_token') accessToken?: string;
  @field('refresh_token') refreshToken?: string;
  @field('token_expires_at') tokenExpiresAt?: number;

  // Metadata
  @field('synced_at') syncedAt?: number;
  @readonly @date('created_at') createdAt!: Date;
  @readonly @date('updated_at') updatedAt!: Date;

  // Relations
  @children('sessions') sessions: any;

  // Helpers
  get isTokenExpired(): boolean {
    if (!this.tokenExpiresAt) return true;
    return Date.now() > this.tokenExpiresAt;
  }

  get statsObject() {
    return {
      workout_streak: this.workoutStreak,
      workout_total_count: this.workoutTotalCount,
      workout_total_duration: this.workoutTotalDuration,
      workout_total_energy: this.workoutTotalEnergy,
      workout_total_distance: this.workoutTotalDistance,
    };
  }
}

2. Session Model¶

// src/database/models/Session.ts

import { Model, Q } from '@nozbe/watermelondb';
import { field, readonly, date, relation, json } from '@nozbe/watermelondb/decorators';
import { Associations } from '@nozbe/watermelondb/Model';
import Training from './Training';

interface SessionDetail {
  avg_hr?: number;
  max_hr?: number;
  avg_power?: number;
  max_power?: number;
  avg_cadence?: number;
  max_cadence?: number;
  avg_speed?: number;
  max_speed?: number;
  // ... 其他 metrics
}

export default class Session extends Model {
  static table = 'sessions';
  static associations: Associations = {
    trainings: { type: 'belongs_to', key: 'training_id' },
  };

  @field('session_id') sessionId!: string;
  @field('user_id') userId!: string;
  @field('training_id') trainingId?: number;

  @field('device_type') deviceType!: 'bike' | 'rower';
  @field('started_at') startedAt!: number;
  @field('ended_at') endedAt?: number;
  @field('duration') duration!: number;

  @field('total_energy') totalEnergy!: number;
  @field('total_distance') totalDistance!: number;

  @json('detail', (json) => json) detail!: SessionDetail;
  @field('raw') raw?: string;

  @field('upload_status') uploadStatus!: 'pending' | 'uploaded' | 'failed';
  @field('upload_retry_count') uploadRetryCount!: number;
  @field('uploaded_at') uploadedAt?: number;

  @readonly @date('created_at') createdAt!: Date;
  @readonly @date('updated_at') updatedAt!: Date;

  // Relations
  @relation('trainings', 'training_id') training: any;

  // Helpers
  get isPending(): boolean {
    return this.uploadStatus === 'pending' || this.uploadStatus === 'failed';
  }

  get formattedDuration(): string {
    const hours = Math.floor(this.duration / 3600);
    const minutes = Math.floor((this.duration % 3600) / 60);
    const seconds = this.duration % 60;

    if (hours > 0) {
      return `${hours}:${minutes.toString().padStart(2, '0')}:${seconds.toString().padStart(2, '0')}`;
    }
    return `${minutes}:${seconds.toString().padStart(2, '0')}`;
  }

  get startedAtDate(): Date {
    return new Date(this.startedAt);
  }
}

3. Training Model¶

// src/database/models/Training.ts

import { Model } from '@nozbe/watermelondb';
import { field, readonly, date, json, children } from '@nozbe/watermelondb/decorators';
import { Associations } from '@nozbe/watermelondb/Model';

interface TrainingStage {
  duration: number;
  resistance: number;
  target_power?: number;
  target_cadence?: number;
}

export default class Training extends Model {
  static table = 'trainings';
  static associations: Associations = {
    sessions: { type: 'has_many', foreignKey: 'training_id' },
    training_videos: { type: 'has_many', foreignKey: 'training_id' },
  };

  @field('training_id') trainingId!: number;
  @field('name') name!: string;
  @field('description') description?: string;
  @field('device_type') deviceType!: 'bike' | 'rower';
  @field('difficulty') difficulty?: 'beginner' | 'intermediate' | 'advanced';
  @field('duration') duration!: number;
  @field('target_energy') targetEnergy?: number;

  @field('has_video') hasVideo!: boolean;
  @field('video_url') videoUrl?: string;
  @field('video_size') videoSize?: number;
  @field('video_duration') videoDuration?: number;

  @field('cover_url') coverUrl?: string;

  @json('stages', (json) => json) stages?: TrainingStage[];

  @field('cache_timestamp') cacheTimestamp!: number;
  @readonly @date('created_at') createdAt!: Date;
  @readonly @date('updated_at') updatedAt!: Date;

  // Relations
  @children('sessions') sessions: any;
  @children('training_videos') trainingVideos: any;

  // Helpers
  get formattedDuration(): string {
    const minutes = Math.floor(this.duration / 60);
    return `${minutes} min`;
  }

  get difficultyColor(): string {
    switch (this.difficulty) {
      case 'beginner': return '#4CAF50';
      case 'intermediate': return '#FF9800';
      case 'advanced': return '#F44336';
      default: return '#9E9E9E';
    }
  }
}

4. TrainingVideo Model¶

// src/database/models/TrainingVideo.ts

import { Model } from '@nozbe/watermelondb';
import { field, readonly, date, relation } from '@nozbe/watermelondb/decorators';
import { Associations } from '@nozbe/watermelondb/Model';
import Training from './Training';

export type DownloadStatus = 'not_downloaded' | 'downloading' | 'downloaded' | 'update_available';

export default class TrainingVideo extends Model {
  static table = 'training_videos';
  static associations: Associations = {
    trainings: { type: 'belongs_to', key: 'training_id' },
  };

  @field('training_id') trainingId!: number;

  @field('download_status') downloadStatus!: DownloadStatus;
  @field('download_progress') downloadProgress!: number;

  @field('local_video_path') localVideoPath?: string;
  @field('local_file_size') localFileSize?: number;

  @field('server_video_url') serverVideoUrl!: string;
  @field('downloaded_at') downloadedAt?: number;

  @readonly @date('created_at') createdAt!: Date;
  @readonly @date('updated_at') updatedAt!: Date;

  // Relations
  @relation('trainings', 'training_id') training: any;

  // Helpers
  get isDownloaded(): boolean {
    return this.downloadStatus === 'downloaded';
  }

  get needsUpdate(): boolean {
    return this.downloadStatus === 'update_available';
  }

  get formattedFileSize(): string {
    if (!this.localFileSize) return '';
    const mb = this.localFileSize / (1024 * 1024);
    return `${mb.toFixed(1)} MB`;
  }
}

5. SyncQueue Model¶

// src/database/models/SyncQueue.ts

import { Model } from '@nozbe/watermelondb';
import { field, readonly, date } from '@nozbe/watermelondb/decorators';

export default class SyncQueue extends Model {
  static table = 'sync_queue';

  @field('entity_type') entityType!: 'session' | 'user_stats';
  @field('entity_id') entityId!: string;
  @field('action') action!: 'create' | 'update' | 'delete';
  @field('payload') payload!: string;
  @field('status') status!: 'pending' | 'syncing' | 'completed' | 'failed';
  @field('retry_count') retryCount!: number;
  @field('last_error') lastError?: string;

  @readonly @date('created_at') createdAt!: Date;
  @readonly @date('updated_at') updatedAt!: Date;

  // Helpers
  get payloadObject(): any {
    return JSON.parse(this.payload);
  }

  get shouldRetry(): boolean {
    return this.status === 'failed' && this.retryCount < 5;
  }
}

使用範例¶

初始化 Database¶

// src/database/index.ts

import { Database } from '@nozbe/watermelondb';
import SQLiteAdapter from '@nozbe/watermelondb/adapters/sqlite';
import { schema } from './schema';
import User from './models/User';
import Session from './models/Session';
import Training from './models/Training';
import TrainingVideo from './models/TrainingVideo';
import SyncQueue from './models/SyncQueue';

const adapter = new SQLiteAdapter({
  schema,
  jsi: true, // 啟用 JSI (更快)
  onSetUpError: (error) => {
    console.error('[WatermelonDB] Setup error:', error);
  }
});

export const database = new Database({
  adapter,
  modelClasses: [
    User,
    Session,
    Training,
    TrainingVideo,
    SyncQueue,
  ],
});

範例 1: 儲存運動記錄¶

// services/SessionService.ts

import { database } from '../database';
import Session from '../database/models/Session';
import { v4 as uuidv4 } from 'uuid';

export class SessionService {
  static async createSession(data: {
    userId: string;
    trainingId?: number;
    deviceType: 'bike' | 'rower';
    startedAt: number;
    duration: number;
    totalEnergy: number;
    totalDistance: number;
    detail: any;
    raw?: string;
  }): Promise<Session> {
    const session = await database.write(async () => {
      return await database.collections.get<Session>('sessions').create((record) => {
        record.sessionId = uuidv4();
        record.userId = data.userId;
        record.trainingId = data.trainingId;
        record.deviceType = data.deviceType;
        record.startedAt = data.startedAt;
        record.endedAt = Date.now();
        record.duration = data.duration;
        record.totalEnergy = data.totalEnergy;
        record.totalDistance = data.totalDistance;
        record.detail = data.detail;
        record.raw = data.raw;
        record.uploadStatus = 'pending';
        record.uploadRetryCount = 0;
      });
    });

    console.log('[SessionService] Session created:', session.sessionId);
    return session;
  }

  static async getPendingSessions(): Promise<Session[]> {
    const sessions = await database.collections
      .get<Session>('sessions')
      .query(
        Q.where('upload_status', Q.oneOf(['pending', 'failed'])),
        Q.sortBy('created_at', Q.asc)
      )
      .fetch();

    return sessions;
  }

  static async markAsUploaded(sessionId: string): Promise<void> {
    const session = await database.collections
      .get<Session>('sessions')
      .query(Q.where('session_id', sessionId))
      .fetch();

    if (session.length > 0) {
      await database.write(async () => {
        await session[0].update((record) => {
          record.uploadStatus = 'uploaded';
          record.uploadedAt = Date.now();
        });
      });
    }
  }

  static async getRecentSessions(limit: number = 20): Promise<Session[]> {
    return await database.collections
      .get<Session>('sessions')
      .query(
        Q.sortBy('started_at', Q.desc),
        Q.take(limit)
      )
      .fetch();
  }

  // 新增：取得 pending sessions 數量（用於 Login sync）
  static async getPendingSessionsCount(): Promise<number> {
    return await database.collections
      .get<Session>('sessions')
      .query(Q.where('upload_status', 'pending'))
      .fetchCount();
  }
}

範例 2: 查詢課程列表（含影片狀態）¶

// services/TrainingService.ts

import { database } from '../database';
import Training from '../database/models/Training';
import TrainingVideo from '../database/models/TrainingVideo';
import { Q } from '@nozbe/watermelondb';

export class TrainingService {
  static async getTrainingsWithVideoStatus() {
    const trainings = await database.collections
      .get<Training>('trainings')
      .query(Q.sortBy('created_at', Q.desc))
      .fetch();

    // 使用 observeWithColumns 實現自動更新 UI
    return trainings.map(training => {
      const videoObservable = training.trainingVideos.observe();
      return { training, videoObservable };
    });
  }

  static async getTrainingById(trainingId: number): Promise<Training | null> {
    const results = await database.collections
      .get<Training>('trainings')
      .query(Q.where('training_id', trainingId))
      .fetch();

    return results.length > 0 ? results[0] : null;
  }
}

範例 3: 在 React Component 中使用（自動更新）¶

// screens/HistoryScreen.tsx

import React from 'react';
import { View, FlatList, Text } from 'react-native';
import { database } from '../database';
import Session from '../database/models/Session';
import { Q } from '@nozbe/watermelondb';
import { withObservables } from '@nozbe/watermelondb/react';

// 定義 Component
const HistoryScreen = ({ sessions }: { sessions: Session[] }) => {
  return (
    <FlatList
      data={sessions}
      keyExtractor={(item) => item.id}
      renderItem={({ item }) => (
        <View>
          <Text>{item.startedAtDate.toLocaleDateString()}</Text>
          <Text>{item.formattedDuration}</Text>
          <Text>{item.totalEnergy} kcal</Text>
          {item.isPending && <Text>🔄 上傳中...</Text>}
        </View>
      )}
    />
  );
};

// 自動觀察資料變更（WatermelonDB 的魔法！）
const enhance = withObservables([], () => ({
  sessions: database.collections
    .get<Session>('sessions')
    .query(Q.sortBy('started_at', Q.desc))
    .observe(), // ⭐ observe() 會自動更新 UI
}));

export default enhance(HistoryScreen);

同步策略¶

Batch Upload Service 概述¶

Batch Upload Service 負責批次上傳所有 pending sessions。以下為服務介面定義：

核心方法：

1. batchUploadProcedure
2. 功能：批次上傳所有 pending sessions
3. 參數：可選的進度回調函式

4. 返回：上傳結果統計（成功與失敗數量）

5. getPendingCount

6. 功能：取得 pending sessions 數量
7. 返回：pending sessions 的數量

設計要點：

// Batch Upload Procedure 介面定義

interface BatchUploadOptions {
  onProgress?: (current: number, total: number) => void;
}

interface BatchUploadResult {
  success: number;
  failed: number;
}

// 處理邏輯流程：
// 1. 查詢所有 upload_status = 'pending' 的 sessions（按 started_at 升序）
// 2. 如果沒有 pending sessions，返回 {success: 0, failed: 0}
// 3. 逐一上傳每個 session：
//    - 呼叫 POST /sessions API
//    - 201 Created → 標記為 'uploaded'，successCount++
//    - 400 duplicate → 標記為 'uploaded'（server 已有），successCount++
//    - 其他錯誤 → 保留為 'pending'，upload_retry_count++，failedCount++
//    - 呼叫 onProgress callback（如果提供）
// 4. 返回 {success: successCount, failed: failedCount}

實作要點：

• 使用 WatermelonDB 的 Query API 查詢 pending sessions
• 錯誤處理區分 duplicate 與其他錯誤
• 進度回調支援 UI 更新
• 不中斷執行：某個 session 失敗不影響後續 sessions

2. 下載 Server Sessions¶

export class SyncService {
  static async downloadServerSessions(userId: string): Promise<void> {
    try {
      const response = await APIClient.get('/sessions');
      const serverSessions = response.data.sessions;

      await database.write(async () => {
        const sessionsCollection = database.collections.get<Session>('sessions');

        for (const serverSession of serverSessions) {
          // 檢查是否已存在
          const existing = await sessionsCollection
            .query(Q.where('session_id', serverSession.session_id))
            .fetch();

          if (existing.length === 0) {
            // 不存在，建立新記錄
            await sessionsCollection.create((record) => {
              record.sessionId = serverSession.session_id;
              record.userId = userId;
              record.trainingId = serverSession.training_id;
              record.deviceType = serverSession.device_type;
              record.startedAt = new Date(serverSession.started_at).getTime();
              record.endedAt = new Date(serverSession.ended_at).getTime();
              record.duration = serverSession.duration;
              record.totalEnergy = serverSession.total_energy;
              record.totalDistance = serverSession.total_distance;
              record.detail = serverSession.detail;
              record.uploadStatus = 'uploaded';
              record.uploadRetryCount = 0;
              record.uploadedAt = Date.now();
            });
          }
        }
      });

      console.log('[SyncService] Downloaded', serverSessions.length, 'sessions');
    } catch (error) {
      console.error('[SyncService] Download failed:', error);
    }
  }
}

📊 總結¶

WatermelonDB 優點¶

✅ 自動更新 UI：使用 observe() + withObservables ✅ 效能優秀：Lazy loading，處理大量資料無壓力 ✅ Offline-First：完美符合架構設計 ✅ TypeScript 友善：完整型別支援

🔗 相關章節¶

• 03.1 App 架構 - React Native 技術堆疊
• 03.3 資料流與同步策略 ⭐ - 同步邏輯設計
• 第 4 章：API Contract - Backend API 規格

◀ 上一章：資料流與同步策略 | 下一章：API Contract ▶