從 Redis 記憶體到 PostgreSQL 持久化

在前四天的建置中，我們專注於 OTP 的核心邏輯：生成、驗證、速率限制。這些功能主要依賴 Redis 做記憶體快取，但對於一個完整的 SaaS 產品，我們需要持久化層來儲存：

• 簡訊模板：讓用戶自訂 OTP 訊息格式
• 發送記錄：追蹤每次簡訊發送的狀態與成本
• 用戶統計：分析使用量與成功率

在過往的工作經驗中，資料設計是整個系統成敗的關鍵。好的資料結構不只影響效能，更決定了未來功能擴展的可能性。

為什麼選擇 Prisma ORM？

在選擇 ORM 時，我經歷過從手寫 SQL 到 ORM 的過程：

各種資料存取方案比較：

選擇 Prisma 的關鍵原因：

• 強大的型別生成：從 Schema 自動生成 TypeScript 型別
• 直觀的查詢語法：類似 GraphQL 的巢狀查詢
• 優秀的開發者工具：Prisma Studio、Migration 工具
• 與 Fastify 整合良好：官方 Plugin 支援

現有專案的資料需求分析

基於目前 kyo-core 的實作，我們已經有了基本的 OTP 服務邏輯。再來看看需要持久化的資料：

// packages/kyo-core/src/index.ts (現有的 OtpService)
export class OtpService {
  async send(req: OtpSendRequest): Promise<OtpSendResponse> {
    // 目前流程：速率限制 → 生成驗證碼 → 發送簡訊
    // 缺少：模板系統、發送記錄
  }

  async verify(req: OtpVerifyRequest): Promise<OtpVerifyResponse> {
    // 目前流程：檢查鎖定 → 驗證碼比對 → 失敗計數
    // 缺少：驗證記錄
  }
}

Prisma Schema 設計

基於業務需求，我們需要三個核心資料表：

// packages/kyo-core/prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
  output   = "../generated/client"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// 簡訊模板管理
model Template {
  id        Int      @id @default(autoincrement())
  name      String   @unique
  content   String   // 支援變數如 {code}, {phone}
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // 關聯到發送記錄
  otpLogs   OtpLog[]

  @@map("templates")
}

// OTP 發送與驗證記錄
model OtpLog {
  id          Int      @id @default(autoincrement())
  phone       String
  msgId       String?  // 三竹簡訊的訊息 ID
  templateId  Int?
  status      OtpStatus
  errorCode   String?  // 錯誤代碼
  cost        Decimal? @db.Decimal(5,2) // 簡訊成本
  createdAt   DateTime @default(now())

  // 關聯模板（可選，向後相容）
  template    Template? @relation(fields: [templateId], references: [id])

  @@index([phone, createdAt])
  @@index([status, createdAt])
  @@map("otp_logs")
}

// OTP 驗證記錄
model OtpVerification {
  id        Int      @id @default(autoincrement())
  phone     String
  success   Boolean
  attempts  Int      @default(1)
  createdAt DateTime @default(now())

  @@index([phone, createdAt])
  @@map("otp_verifications")
}

// 狀態枚舉
enum OtpStatus {
  SENT     // 成功發送
  FAILED   // 發送失敗
  VERIFIED // 驗證成功
  EXPIRED  // 驗證碼過期
}

設計考量點：

1. 模板系統：支援自訂簡訊內容，提升用戶體驗
2. 成本追蹤：每次發送的費用記錄，便於分析營運成本
3. 索引策略：針對常用查詢（手機號碼、時間範圍）建立索引
4. 向後相容：templateId 設為可選，不影響現有功能

整合 Prisma 到 kyo-core

1. 安裝與設定

cd packages/kyo-core
pnpm add prisma @prisma/client
pnpm add -D prisma

# 初始化 Prisma（已建立 schema.prisma）
npx prisma init

2. 建立資料庫層抽象

// packages/kyo-core/src/database.ts
import { PrismaClient } from '../generated/client';

export interface DatabaseConfig {
  url: string;
  maxConnections?: number;
  queryTimeout?: number;
}

export class Database {
  private prisma: PrismaClient;

  constructor(config: DatabaseConfig) {
    this.prisma = new PrismaClient({
      datasources: {
        db: { url: config.url }
      },
      log: ['query', 'error'], // 開發環境顯示查詢
    });
  }

  async connect(): Promise<void> {
    try {
      await this.prisma.$connect();
      console.log('✅ Database connected successfully');
    } catch (error) {
      console.error('❌ Database connection failed:', error);
      throw error;
    }
  }

  async disconnect(): Promise<void> {
    await this.prisma.$disconnect();
  }

  // 暴露 Prisma 客戶端供服務層使用
  get client() {
    return this.prisma;
  }

  async healthCheck(): Promise<boolean> {
    try {
      await this.prisma.$queryRaw`SELECT 1`;
      return true;
    } catch {
      return false;
    }
  }
}

3. 模板服務實作

// packages/kyo-core/src/templateService.ts
import type { Database } from './database';
import type { Template } from '../generated/client';

export interface CreateTemplateRequest {
  name: string;
  content: string;
  isActive?: boolean;
}

export interface UpdateTemplateRequest {
  id: number;
  name?: string;
  content?: string;
  isActive?: boolean;
}

export class TemplateService {
  constructor(private db: Database) {}

  async createTemplate(req: CreateTemplateRequest): Promise<Template> {
    try {
      return await this.db.client.template.create({
        data: {
          name: req.name,
          content: req.content,
          isActive: req.isActive ?? true
        }
      });
    } catch (error) {
      if (error.code === 'P2002') { // Unique constraint violation
        throw new Error(`Template name '${req.name}' already exists`);
      }
      throw error;
    }
  }

  async listTemplates(activeOnly = true): Promise<Template[]> {
    return await this.db.client.template.findMany({
      where: activeOnly ? { isActive: true } : undefined,
      orderBy: { createdAt: 'desc' }
    });
  }

  async getTemplate(id: number): Promise<Template | null> {
    return await this.db.client.template.findUnique({
      where: { id }
    });
  }

  async updateTemplate(req: UpdateTemplateRequest): Promise<Template> {
    const { id, ...data } = req;
    return await this.db.client.template.update({
      where: { id },
      data,
    });
  }

  async deleteTemplate(id: number): Promise<void> {
    // 軟刪除，避免影響現有記錄
    await this.db.client.template.update({
      where: { id },
      data: { isActive: false }
    });
  }

  // 模板變數替換
  renderTemplate(template: Template, variables: Record<string, string>): string {
    let content = template.content;

    // 替換變數如 {code}, {phone}
    Object.entries(variables).forEach(([key, value]) => {
      const pattern = new RegExp(`{${key}}`, 'g');
      content = content.replace(pattern, value);
    });

    return content;
  }
}

4. OTP 記錄服務

// packages/kyo-core/src/otpLogService.ts
import type { Database } from './database';
import type { OtpLog, OtpStatus, OtpVerification } from '../generated/client';

export interface LogOtpRequest {
  phone: string;
  msgId?: string;
  templateId?: number;
  status: OtpStatus;
  errorCode?: string;
  cost?: number;
}

export interface LogVerificationRequest {
  phone: string;
  success: boolean;
  attempts?: number;
}

export class OtpLogService {
  constructor(private db: Database) {}

  async logOtpSend(req: LogOtpRequest): Promise<OtpLog> {
    return await this.db.client.otpLog.create({
      data: {
        phone: req.phone,
        msgId: req.msgId,
        templateId: req.templateId,
        status: req.status,
        errorCode: req.errorCode,
        cost: req.cost
      },
      include: {
        template: true // 包含模板資訊
      }
    });
  }

  async logVerification(req: LogVerificationRequest): Promise<OtpVerification> {
    return await this.db.client.otpVerification.create({
      data: {
        phone: req.phone,
        success: req.success,
        attempts: req.attempts ?? 1
      }
    });
  }

  // 查詢特定手機號碼的發送記錄
  async getOtpHistory(phone: string, limit = 10): Promise<OtpLog[]> {
    return await this.db.client.otpLog.findMany({
      where: { phone },
      include: { template: true },
      orderBy: { createdAt: 'desc' },
      take: limit
    });
  }

  // 統計分析：成功率
  async getSuccessRate(startDate?: Date, endDate?: Date) {
    const where = {
      createdAt: {
        gte: startDate,
        lte: endDate
      }
    };

    const [total, successful] = await Promise.all([
      this.db.client.otpLog.count({ where }),
      this.db.client.otpLog.count({
        where: { ...where, status: 'SENT' }
      })
    ]);

    return {
      total,
      successful,
      successRate: total > 0 ? (successful / total) * 100 : 0
    };
  }

  // 成本分析
  async getCostAnalysis(startDate?: Date, endDate?: Date) {
    const result = await this.db.client.otpLog.aggregate({
      where: {
        createdAt: {
          gte: startDate,
          lte: endDate
        },
        cost: { not: null }
      },
      _sum: { cost: true },
      _avg: { cost: true },
      _count: { cost: true }
    });

    return {
      totalCost: result._sum.cost || 0,
      averageCost: result._avg.cost || 0,
      totalSent: result._count.cost || 0
    };
  }
}

整合到現有的 OtpService

5. 更新核心 OTP 服務

// packages/kyo-core/src/index.ts (更新現有實作)
import { TemplateService } from './templateService';
import { OtpLogService } from './otpLogService';
import { Database } from './database';

export class OtpService {
  private templateService: TemplateService;
  private logService: OtpLogService;

  constructor(
    private redis: RedisLike,
    private sms: SmsProvider,
    private database: Database
  ) {
    this.templateService = new TemplateService(database);
    this.logService = new OtpLogService(database);
  }

  async send(req: OtpSendRequest): Promise<OtpSendResponse> {
    // 1. 速率限制檢查（現有邏輯）
    const rl = await tokenBucket(this.redis, this.rateKey(req.phone));
    if (!rl.allowed) {
      // 記錄失敗原因
      await this.logService.logOtpSend({
        phone: req.phone,
        status: 'FAILED',
        errorCode: 'RATE_LIMIT'
      });

      throw new KyoError('E_RATE_LIMIT', 'Too many requests', 429, {
        resetInSec: rl.resetInSec
      });
    }

    // 2. 取得簡訊模板（新功能）
    let message = `OTP: {code}`; // 預設模板
    let templateId: number | undefined;

    if (req.templateId) {
      const template = await this.templateService.getTemplate(req.templateId);
      if (template && template.isActive) {
        message = template.content;
        templateId = template.id;
      }
    }

    // 3. 生成並儲存驗證碼（現有邏輯）
    const code = this.generateCode(6);
    await this.redis.set(this.codeKey(req.phone), code, 'EX', 300);

    // 4. 替換模板變數
    const finalMessage = this.templateService.renderTemplate(
      { content: message } as any,
      { code, phone: req.phone }
    );

    try {
      // 5. 發送簡訊（現有邏輯）
      const smsResult = await this.sms.send({
        phone: req.phone,
        message: finalMessage
      });

      // 6. 記錄成功發送
      await this.logService.logOtpSend({
        phone: req.phone,
        msgId: smsResult.msgId,
        templateId,
        status: 'SENT',
        cost: 1.0 // 假設每則簡訊 1 元
      });

      return smsResult;
    } catch (error) {
      // 7. 記錄發送失敗
      await this.logService.logOtpSend({
        phone: req.phone,
        templateId,
        status: 'FAILED',
        errorCode: error.message || 'SMS_SEND_ERROR'
      });

      throw error;
    }
  }

  async verify(req: OtpVerifyRequest): Promise<OtpVerifyResponse> {
    // 現有驗證邏輯...
    const result = await this.performVerification(req);

    // 記錄驗證結果
    await this.logService.logVerification({
      phone: req.phone,
      success: result.success,
      attempts: result.attempts || 1
    });

    return result;
  }

  // 新增：取得發送歷史
  async getHistory(phone: string) {
    return await this.logService.getOtpHistory(phone);
  }

  // 新增：統計分析
  async getAnalytics(startDate?: Date, endDate?: Date) {
    const [successRate, costAnalysis] = await Promise.all([
      this.logService.getSuccessRate(startDate, endDate),
      this.logService.getCostAnalysis(startDate, endDate)
    ]);

    return { successRate, costAnalysis };
  }

  // 其他現有方法保持不變...
}

資料庫 Migration 與初始資料

6. 建立初始 Migration

# 生成並執行 migration
cd packages/kyo-core
npx prisma migrate dev --name init
npx prisma generate

7. 建立種子資料

// packages/kyo-core/prisma/seed.ts
import { PrismaClient } from '../generated/client';

const prisma = new PrismaClient();

async function main() {
  // 建立預設模板
  const defaultTemplates = [
    {
      name: 'default',
      content: '您的驗證碼：{code}，請於 5 分鐘內輸入。'
    },
    {
      name: 'urgent',
      content: '【緊急】驗證碼：{code}，限時 5 分鐘有效！'
    },
    {
      name: 'friendly',
      content: '嗨！您的驗證碼是 {code}，請記得在 5 分鐘內使用喔 😊'
    }
  ];

  for (const template of defaultTemplates) {
    await prisma.template.upsert({
      where: { name: template.name },
      update: {},
      create: template
    });
  }

  console.log('✅ Seed data created successfully');
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

與 Fastify 整合

8. 建立 Prisma Plugin

// apps/kyo-otp-service/src/plugins/database.ts
import fp from 'fastify-plugin';
import { Database } from '@kyong/kyo-core/database';

declare module 'fastify' {
  interface FastifyInstance {
    db: Database;
  }
}

export default fp(async function (fastify) {
  const database = new Database({
    url: process.env.DATABASE_URL!
  });

  await database.connect();

  fastify.decorate('db', database);

  fastify.addHook('onClose', async () => {
    await database.disconnect();
  });
});

9. 更新應用程式啟動

// apps/kyo-otp-service/src/app.ts
import Fastify from 'fastify';
import databasePlugin from './plugins/database';
import { OtpService } from '@kyong/kyo-core';

const fastify = Fastify({ logger: true });

// 註冊資料庫 plugin
await fastify.register(databasePlugin);

// 建立 OTP 服務實例（包含資料庫）
const otpService = new OtpService(
  redis, // Redis 實例
  smsProvider, // SMS 提供者
  fastify.db // 資料庫實例
);

// 新增模板管理路由
fastify.post('/api/templates', async (request, reply) => {
  const template = await otpService.templateService.createTemplate(request.body);
  return template;
});

fastify.get('/api/templates', async (request, reply) => {
  const templates = await otpService.templateService.listTemplates();
  return templates;
});

// 新增分析路由
fastify.get('/api/analytics', async (request, reply) => {
  const analytics = await otpService.getAnalytics();
  return analytics;
});

效能優化與最佳實踐

10. Connection Pool 調整

// 生產環境的資料庫設定
const database = new Database({
  url: process.env.DATABASE_URL!,
  maxConnections: 10, // 根據負載調整
  queryTimeout: 5000  // 5 秒查詢超時
});

11. 批次操作優化

// packages/kyo-core/src/otpLogService.ts (新增批次方法)
export class OtpLogService {
  // 批次記錄（用於高併發場景）
  async batchLogOtp(logs: LogOtpRequest[]): Promise<void> {
    await this.db.client.otpLog.createMany({
      data: logs,
      skipDuplicates: true
    });
  }

  // 使用交易確保資料一致性
  async logOtpWithTransaction(req: LogOtpRequest): Promise<OtpLog> {
    return await this.db.client.$transaction(async (tx) => {
      // 檢查是否有重複記錄
      const existing = await tx.otpLog.findFirst({
        where: {
          phone: req.phone,
          msgId: req.msgId,
        }
      });

      if (existing) {
        throw new Error('Duplicate OTP log detected');
      }

      return await tx.otpLog.create({
        data: req,
        include: { template: true }
      });
    });
  }
}

今日成果

✅ Prisma Schema 設計：模板、記錄、驗證三大資料表
✅ 服務層架構：TemplateService、OtpLogService 封裝
✅ 資料庫整合：Database 類別與 Fastify plugin
✅ 模板系統：支援變數替換的簡訊模板
✅ 記錄追蹤：完整的發送與驗證記錄
✅ 分析功能：成功率與成本統計
✅ 效能優化：批次操作與交易支援

架構思考

資料層的設計哲學：

1. 分離關注點：業務邏輯與資料存取分離
2. 型別安全：利用 Prisma 生成強型別 API
3. 可測試性：依賴注入讓單元測試更容易
4. 擴展性：預留欄位支援未來功能

與現有架構的整合：

• Redis 繼續負責快速存取（驗證碼、速率限制）
• PostgreSQL 負責持久化資料（模板、記錄）

下一步規劃

明天（Day6）我們將：

1. 建立完整的 API 端點（CRUD + 分析）
2. 加入資料庫健康檢查與監控
3. 實作資料備份與恢復策略
4. 準備 Docker 環境的資料庫設定