POSTGRESQL_MIGRATION_GUIDE.md

# 🔄 PostgreSQL 直連遷移指南

本文件說明如何從 Supabase SDK 遷移到 PostgreSQL 直連的詳細步驟和注意事項。

## 📋 遷移概述

### 變更內容
- ✅ 移除 Supabase Python SDK 依賴
- ✅ 新增 SQLAlchemy + psycopg 直連
- ✅ 重構資料庫服務層
- ✅ 新增 ORM 模型定義
- ✅ 支援 Alembic 資料庫遷移

### 優勢
- 🚀 **更好的效能**: 直連減少中間層開銷
- 🔧 **更多控制**: 完整的 SQL 查詢控制
- 📊 **ORM 支援**: SQLAlchemy 提供強大的 ORM 功能
- 🔄 **遷移管理**: Alembic 提供版本化的資料庫遷移
- 🛡️ **連線池**: 自動連線池管理

## 🔑 環境變數更新

### 舊的 Supabase 設定
```bash
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_KEY=your-anon-key
```

### 新的 PostgreSQL 設定
```bash
DB_HOST=db.your-project.supabase.co
DB_PORT=6543
DB_NAME=postgres
DB_USER=postgres
DB_PASSWORD=your-database-password
```

## 🔍 如何取得 PostgreSQL 連線資訊

### 從 Supabase Dashboard 取得

1. **登入 Supabase Dashboard**
   - 前往 [Supabase Dashboard](https://supabase.com/dashboard)
   - 選擇您的專案

2. **取得連線資訊**
   - 前往 "Settings" → "Database"
   - 在 "Connection info" 區域找到：
     - **Host**: `db.your-project-ref.supabase.co`
     - **Port**: `6543` (PostgreSQL 直連埠)
     - **Database**: `postgres`
     - **User**: `postgres`
     - **Password**: 您設定的資料庫密碼

3. **連線字串範例**
   ```
   postgresql://postgres:your-password@db.your-project-ref.supabase.co:6543/postgres
   ```

### 從其他 PostgreSQL 提供商取得

如果您使用其他 PostgreSQL 服務：
- **AWS RDS**: 在 RDS Console 中查看端點資訊
- **Google Cloud SQL**: 在 Cloud Console 中查看連線詳情
- **Azure Database**: 在 Azure Portal 中查看連線字串
- **Railway/Render**: 在專案設定中查看資料庫資訊

## 🛠️ 部署步驟

### 1. 更新環境變數

在 Hugging Face Spaces 的 Settings 中更新：

```bash
# 移除舊的 Supabase 設定
# SUPABASE_URL=...
# SUPABASE_KEY=...

# 新增 PostgreSQL 設定
DB_HOST=db.your-project-ref.supabase.co
DB_PORT=6543
DB_NAME=postgres
DB_USER=postgres
DB_PASSWORD=your-database-password

# 其他設定保持不變
LINE_CHANNEL_ACCESS_TOKEN=your-line-token
LINE_CHANNEL_SECRET=your-line-secret
OPENROUTER_API_KEY=your-openrouter-key
OPENROUTER_MODEL=anthropic/claude-3-haiku
```

### 2. 初始化資料庫

部署後，執行資料庫初始化：

```bash
# 在容器中執行
python -m backend.database.init_db
```

或者透過 API 端點觸發初始化（如果實作了相關端點）。

### 3. 驗證連線

檢查健康檢查端點：
```bash
curl https://your-space-url.hf.space/health
```

應該會看到：
```json
{
  "status": "healthy",
  "message": "LINE Bot API is operational", 
  "database": "connected",
  "database_url": "postgresql://your-host:6543/postgres"
}
```

## 📊 資料庫模型對應

### 新的 SQLAlchemy 模型

```python
# backend/database/models.py

class User(Base):
    __tablename__ = "users"
    user_id = Column(String(255), primary_key=True)
    name = Column(String(255))
    email = Column(String(255))
    # ... 其他欄位

class Product(Base):
    __tablename__ = "products"
    product_id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    price = Column(DECIMAL(10, 2), nullable=False)
    # ... 其他欄位
```

### 資料表關聯

```python
# 一對多關聯
class User(Base):
    orders = relationship("Order", back_populates="user")

class Order(Base):
    user = relationship("User", back_populates="orders")
    order_items = relationship("OrderItem", back_populates="order")
```

## 🔄 API 變更

### 查詢方式變更

#### 舊的 Supabase 方式
```python
result = supabase.table("users").select("*").eq("name", "張三").execute()
```

#### 新的 SQLAlchemy 方式
```python
db = get_database_session()
users = db.query(User).filter(User.name == "張三").all()
```

### 錯誤處理改進

```python
def database_operation():
    db = None
    try:
        db = get_database_session()
        # 資料庫操作
        result = db.query(User).all()
        db.commit()
        return result
    except Exception as e:
        if db:
            db.rollback()
        logger.error(f"資料庫操作失敗: {str(e)}")
        raise
    finally:
        if db:
            close_database_session(db)
```

## 🚀 效能優化

### 連線池設定

```python
# backend/database/connection.py
engine = create_engine(
    settings.DATABASE_URL,
    pool_size=10,          # 連線池大小
    max_overflow=20,       # 最大溢出連線
    pool_pre_ping=True,    # 連線前檢查
    pool_recycle=3600,     # 1小時後回收連線
)
```

### 查詢優化

```python
# 使用 eager loading 避免 N+1 查詢
from sqlalchemy.orm import joinedload

users_with_orders = db.query(User).options(
    joinedload(User.orders)
).all()

# 使用索引優化查詢
# 在模型中定義索引
class User(Base):
    __tablename__ = "users"
    name = Column(String(255), index=True)  # 建立索引
```

## 🔧 開發工具

### Alembic 遷移

```bash
# 初始化 Alembic
alembic init alembic

# 建立遷移檔案
alembic revision --autogenerate -m "Initial migration"

# 執行遷移
alembic upgrade head

# 回滾遷移
alembic downgrade -1
```

### 本地開發

```bash
# 安裝依賴
pip install -r requirements.txt

# 設定環境變數
export DB_HOST=localhost
export DB_PORT=5432
export DB_NAME=linebot_dev
export DB_USER=postgres
export DB_PASSWORD=password

# 初始化資料庫
python -m backend.database.init_db

# 執行應用程式
uvicorn backend.main:app --reload --port 7860
```

## 🛡️ 安全性考量

### 連線安全

1. **使用 SSL 連線**
   ```python
   DATABASE_URL = f"postgresql+psycopg://{user}:{password}@{host}:{port}/{db}?sslmode=require"
   ```

2. **限制資料庫權限**
   - 建立專用的應用程式使用者
   - 只授予必要的資料表權限
   - 定期輪換密碼

3. **連線字串保護**
   - 使用環境變數儲存敏感資訊
   - 避免在日誌中記錄連線字串
   - 使用密碼管理工具

## 📈 監控與除錯

### 日誌設定

```python
# 啟用 SQLAlchemy 日誌
engine = create_engine(
    settings.DATABASE_URL,
    echo=settings.DEBUG,  # 在 DEBUG 模式下顯示 SQL
)
```

### 效能監控

```python
import time
from functools import wraps

def monitor_db_performance(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        execution_time = time.time() - start_time
        logger.info(f"{func.__name__} 執行時間: {execution_time:.2f}秒")
        return result
    return wrapper
```

## 🚨 常見問題

### Q: 連線失敗怎麼辦？
A: 檢查以下項目：
1. 主機名稱和埠號是否正確
2. 使用者名稱和密碼是否正確
3. 資料庫是否允許外部連線
4. 防火牆設定是否正確

### Q: 效能比 Supabase SDK 慢？
A: 可能原因：
1. 連線池設定不當
2. 查詢未優化
3. 缺少適當的索引
4. 網路延遲問題

### Q: 如何處理資料庫遷移？
A: 使用 Alembic：
1. 建立遷移腳本
2. 在部署前測試遷移
3. 備份資料庫
4. 執行遷移

## 📞 技術支援

如果遇到問題：
1. 檢查應用程式日誌
2. 驗證環境變數設定
3. 測試資料庫連線
4. 參考 SQLAlchemy 官方文件

---

**注意**: 遷移前請務必備份您的資料庫，並在測試環境中驗證所有功能正常運作。