系統要記住使用者是誰、學習目標是什麼、目前進度在哪裡，以及每次和教練聊了什麼。這些資料不能全部塞在同一張表，不然後面查詢和修改都會變得困難。

今天會設計 8 張表，並用 SQLAlchemy 定義它們的關係。Day 5 再把這些 Model 建成立即可查詢的資料庫。

先理解資料庫的基本結構

可以把資料庫想成一組彼此有關聯的表：

• Table：資料表
• Row：一筆資料
• Column：欄位
• Primary Key：每筆資料的唯一識別
• Foreign Key：連到另一張表的欄位

例如 tasks 會用 plan_id 指向 plans。查詢任務時，系統就知道它是哪個學習計畫。

8 張表的用途

1. Users

保存登入相關資料：

users
- id：唯一編號
- email：登入用電子郵件
- password_hash：密碼雜湊，不能保存明文
- created_at：建立時間

2. Profiles

保存學習檔案。一個使用者目前先對應一份 profile：

profiles
- id：檔案編號
- user_id：使用者外鍵
- level：初級、中級或高級
- available_hours：每週可用時間
- learning_topic：想學的主題
- created_at：建立時間

3. Goals

保存長期目標。一個使用者可以有多個目標：

goals
- id：目標編號
- user_id：使用者外鍵
- title：目標標題
- deadline：截止日期
- description：詳細說明
- status：進行中、完成或放棄

4. Plans

保存 AI 產生的學習計畫：

plans
- id：計畫編號
- user_id：使用者外鍵
- goal_id：目標外鍵
- title：計畫名稱
- duration_weeks：持續週數
- status：草稿、進行中或完成
- content：每週任務大綱，使用 JSON
- created_at：生成時間

5. Tasks

保存每天要執行的任務：

tasks
- id：任務編號
- plan_id：計畫外鍵
- day：第幾天
- title：任務標題
- description：詳細說明
- estimated_hours：預計時間
- status：待做、進行中或完成
- deadline：截止時間

6. Progress_Logs

保存使用者每天回報的學習進度：

progress_logs
- id：記錄編號
- task_id：任務外鍵
- actual_hours：實際花費時間
- notes：使用者回饋
- completed_at：完成時間
- difficulty_rating：難度 1 到 5

7. Assessments

保存測驗內容與分數：

assessments
- id：測驗編號
- plan_id：計畫外鍵
- title：測驗名稱
- questions：題目 JSON
- created_at：建立時間
- score：測驗分數

8. Conversations

保存使用者和 AI 教練的對話：

conversations
- id：對話編號
- user_id：使用者外鍵
- message：使用者訊息
- response：AI 回應
- created_at：對話時間
- intent：對話意圖

用 SQLAlchemy 定義 Model

檔案位置：backend/models.py

先建立 Base：

from datetime import datetime
from sqlalchemy import Column, Integer, String, Float, DateTime, Text, ForeignKey, JSON
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

接著定義 8 個 Model：

class User(Base):
    """用戶基本資料"""
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    password_hash = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)

    profiles = relationship("Profile", back_populates="user")
    goals = relationship("Goal", back_populates="user")
    plans = relationship("Plan", back_populates="user")


class Profile(Base):
    """學習檔案"""
    __tablename__ = "profiles"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    level = Column(String)
    available_hours = Column(Float)
    learning_topic = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship("User", back_populates="profiles")


class Goal(Base):
    """學習目標"""
    __tablename__ = "goals"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    title = Column(String)
    deadline = Column(DateTime)
    description = Column(Text)
    status = Column(String, default="進行中")

    user = relationship("User", back_populates="goals")
    plans = relationship("Plan", back_populates="goal")


class Plan(Base):
    """AI生成的學習計畫"""
    __tablename__ = "plans"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    goal_id = Column(Integer, ForeignKey("goals.id"))
    title = Column(String)
    duration_weeks = Column(Integer)
    status = Column(String, default="草稿")
    content = Column(JSON)
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship("User", back_populates="plans")
    goal = relationship("Goal", back_populates="plans")
    tasks = relationship("Task", back_populates="plan")


class Task(Base):
    """具體的每日任務"""
    __tablename__ = "tasks"

    id = Column(Integer, primary_key=True, index=True)
    plan_id = Column(Integer, ForeignKey("plans.id"))
    day = Column(Integer)
    title = Column(String)
    description = Column(Text)
    estimated_hours = Column(Float)
    status = Column(String, default="待做")
    deadline = Column(DateTime)

    plan = relationship("Plan", back_populates="tasks")
    progress_logs = relationship("ProgressLog", back_populates="task")


class ProgressLog(Base):
    """進度記錄"""
    __tablename__ = "progress_logs"

    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(Integer, ForeignKey("tasks.id"))
    actual_hours = Column(Float)
    notes = Column(Text)
    completed_at = Column(DateTime)
    difficulty_rating = Column(Integer)

    task = relationship("Task", back_populates="progress_logs")


class Assessment(Base):
    """測驗"""
    __tablename__ = "assessments"

    id = Column(Integer, primary_key=True, index=True)
    plan_id = Column(Integer, ForeignKey("plans.id"))
    title = Column(String)
    questions = Column(JSON)
    score = Column(Float)
    created_at = Column(DateTime, default=datetime.utcnow)


class Conversation(Base):
    """對話記錄"""
    __tablename__ = "conversations"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    message = Column(Text)
    response = Column(Text)
    intent = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)

ForeignKey 會把資料表連起來，relationship 則讓 SQLAlchemy 可以用物件方式讀取關聯資料。例如一個 Plan 可以取得自己的 tasks，一個 Task 可以取得 progress_logs。

資料表關係

erDiagram
    USERS ||--o{ PROFILES : has
    USERS ||--o{ GOALS : has
    USERS ||--o{ PLANS : has
    USERS ||--o{ CONVERSATIONS : has
    GOALS ||--o{ PLANS : has
    PLANS ||--o{ TASKS : has
    PLANS ||--o{ ASSESSMENTS : has
    TASKS ||--o{ PROGRESS_LOGS : has

關係可以整理成：

• 一個 User 有多個 Goal、Plan 和 Conversation
• 一個 Goal 可以產生多個 Plan
• 一個 Plan 有多個 Task 和 Assessment
• 一個 Task 可以有多筆 ProgressLog

常見問題

為什麼不直接用 Excel？

Excel 適合手動整理，但 API 需要同時查詢、寫入與關聯多筆資料。資料庫能用主鍵、外鍵和索引維持結構。

為什麼要分成多張表？

如果全部資料放在同一張表，使用者資訊會在每個任務重複出現。分表後，每份資料只保存一次，彼此用外鍵連接。

password_hash 是什麼？

密碼不能直接存進資料庫。系統應該先用密碼雜湊函式處理，再保存結果。Day 7 會再處理登入與密碼驗證。

JSON 欄位適合保存什麼？

像計畫內容這種巢狀資料，可以先用 JSON 保存。若日後需要頻繁篩選某個欄位，再評估是否拆成獨立資料表。

ForeignKey 會拖慢查詢嗎？

ForeignKey 主要用來保證關聯資料的完整性。查詢常用的欄位另外建立索引，就能維持合理的查詢速度。

Day 4 完成檢查

今天完成：

• 定義 8 個 SQLAlchemy Models
• 為主要資料建立主鍵
• 用 ForeignKey 連結資料表
• 用 relationship 讀取關聯資料
• 畫出資料庫關係圖

目前進度：

Day 1 ✓ 產品定義完成
Day 2 ✓ 開發環境準備
Day 3 ✓ 專案架構設計
Day 4 ✓ 資料庫設計
Day 5 ⬜ SQLite 資料庫建立
Day 6+ ⬜ API 實作

明天會把這些 Model 建立成 SQLite 資料庫，插入測試資料，再用查詢腳本驗證資料是否完整。