前情提要

今天我們要來實作後端的多租戶架構。在 SaaS 應用中，多租戶架構的設計直接影響到系統的擴展性、安全性和效能。我們將實作 Database-per-Tenant 策略，每個租戶擁有獨立的資料庫，確保資料隔離性和客製化需求。

多租戶資料庫策略比較

在實作之前，我們先了解三種主要的多租戶資料庫策略：

1. Shared Database, Shared Schema

• 優點：實作簡單、成本最低、維護容易
• 缺點：資料隔離性差、客製化困難、效能瓶頸
• 適用場景：簡單的 SaaS 應用，租戶需求相似

2. Shared Database, Separate Schema

• 優點：中等隔離性、成本適中、支援部分客製化
• 缺點：複雜度增加、備份恢復困難、效能影響
• 適用場景：中型 SaaS 應用，需要一定程度的客製化

3. Database-per-Tenant (我們的選擇)

• 優點：完全資料隔離、高度客製化、獨立備份恢復、效能隔離
• 缺點：成本最高、管理複雜、跨租戶查詢困難
• 適用場景：企業級 SaaS 應用，高安全性需求

核心架構設計

我們的 Database-per-Tenant 架構包含以下核心組件：

// packages/kyo-core/src/database/tenant-connection.ts
interface TenantDatabaseConnection {
  tenantId: string;
  connectionString: string;
  pool: Pool;
  isActive: boolean;
  lastUsed: Date;
  maxConnections: number;
}

動態連線管理器

連線管理器負責管理多個租戶資料庫的連線池，實現動態建立、重用和清理連線：

export class TenantConnectionManager {
  private connections = new Map<string, TenantDatabaseConnection>();
  private readonly maxIdleTime = 30 * 60 * 1000; // 30分鐘
  private cleanupInterval: NodeJS.Timeout;

  constructor() {
    // 每5分鐘清理一次閒置連線
    this.cleanupInterval = setInterval(() => {
      this.cleanupIdleConnections();
    }, 5 * 60 * 1000);
  }

  async getConnection(tenantId: string): Promise<Pool> {
    let connection = this.connections.get(tenantId);

    if (!connection || !connection.isActive) {
      connection = await this.createConnection(tenantId);
      this.connections.set(tenantId, connection);
    }

    connection.lastUsed = new Date();
    return connection.pool;
  }

  private async createConnection(tenantId: string): Promise<TenantDatabaseConnection> {
    const connectionString = this.buildConnectionString(tenantId);
    const pool = new Pool({
      connectionString,
      max: 10, // 每個租戶最多10個連線
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 2000,
    });

    // 測試連線
    try {
      const client = await pool.connect();
      client.release();
    } catch (error) {
      await pool.end();
      throw new Error(`Failed to connect to tenant database: ${tenantId}`);
    }

    return {
      tenantId,
      connectionString,
      pool,
      isActive: true,
      lastUsed: new Date(),
      maxConnections: 10,
    };
  }

  private buildConnectionString(tenantId: string): string {
    const baseUrl = process.env.DATABASE_URL || 'postgresql://localhost:5432';
    return `${baseUrl}/${this.getDatabaseName(tenantId)}`;
  }

  private getDatabaseName(tenantId: string): string {
    // 租戶資料庫命名規則：kyo_tenant_{tenantId}
    return `kyo_tenant_${tenantId.replace(/-/g, '_')}`;
  }
}

租戶資料庫服務

租戶資料庫服務負責資料庫的生命週期管理，包括建立、初始化、備份和刪除：

// packages/kyo-core/src/database/tenant-service.ts
export class TenantDatabaseService {
  constructor(private connectionManager: TenantConnectionManager) {}

  async createTenantDatabase(request: CreateTenantDatabaseRequest): Promise<TenantDatabaseInfo> {
    const { tenantId, plan, settings } = request;
    const databaseName = this.getDatabaseName(tenantId);

    try {
      // 1. 建立資料庫
      await this.createDatabase(databaseName);

      // 2. 初始化 Schema
      await this.initializeSchema(tenantId, plan);

      // 3. 建立預設資料
      await this.seedDefaultData(tenantId, settings);

      // 4. 設定備份排程
      await this.setupBackupSchedule(tenantId, plan);

      return {
        tenantId,
        databaseName,
        status: 'active',
        createdAt: new Date(),
        plan,
        connectionString: this.buildConnectionString(tenantId),
        backupEnabled: plan !== 'free',
      };
    } catch (error) {
      // 失敗時清理已建立的資源
      await this.rollbackDatabase(databaseName);
      throw error;
    }
  }

  private async initializeSchema(tenantId: string, plan: string): Promise<void> {
    const pool = await this.connectionManager.getConnection(tenantId);

    // 根據方案決定要建立的表格
    const baseTables = [
      'users', 'members', 'check_ins', 'member_ships',
      'payments', 'services', 'staff', 'roles'
    ];

    const premiumTables = plan === 'premium' || plan === 'enterprise'
      ? ['analytics', 'reports', 'integrations', 'custom_fields']
      : [];

    const tables = [...baseTables, ...premiumTables];

    for (const table of tables) {
      const schema = await this.getTableSchema(table, plan);
      await pool.query(schema);
    }

    // 建立索引和約束
    await this.createIndexes(pool, plan);
    await this.createConstraints(pool, plan);
  }

  private async seedDefaultData(tenantId: string, settings: TenantSettings): Promise<void> {
    const pool = await this.connectionManager.getConnection(tenantId);

    // 建立預設管理員使用者
    await pool.query(`
      INSERT INTO users (id, email, name, role, is_active, created_at)
      VALUES ($1, $2, $3, $4, $5, $6)
    `, [
      `admin-${tenantId}`,
      settings.adminEmail,
      settings.adminName,
      'admin',
      true,
      new Date()
    ]);

    // 建立預設服務項目
    const defaultServices = [
      { name: '健身房使用', price: 1500, duration: 30 },
      { name: '個人教練', price: 3000, duration: 60 },
      { name: '團體課程', price: 800, duration: 45 },
    ];

    for (const service of defaultServices) {
      await pool.query(`
        INSERT INTO services (id, name, price, duration_minutes, is_active, created_at)
        VALUES ($1, $2, $3, $4, $5, $6)
      `, [
        `service-${Date.now()}-${Math.random()}`,
        service.name,
        service.price,
        service.duration,
        true,
        new Date()
      ]);
    }
  }
}

認證與授權系統

我們實作了完整的 RBAC (Role-Based Access Control) 系統，支援七種使用者角色：

// packages/kyo-core/src/auth/auth-service.ts
export class AuthService {
  private readonly jwtSecret: string;
  private readonly tokenExpiry = '24h';
  private readonly refreshExpiry = '7d';

  constructor(jwtSecret?: string) {
    this.jwtSecret = jwtSecret || process.env.JWT_SECRET || 'dev-secret-key';
  }

  async authenticateUser(tenantId: string, credentials: LoginCredentials): Promise<AuthResult> {
    const pool = await tenantConnectionManager.getConnection(tenantId);

    // 驗證使用者憑證
    const user = await this.validateCredentials(pool, credentials);
    if (!user) {
      throw new KyoError('E_AUTH_INVALID_CREDENTIALS', 'Invalid credentials', 401);
    }

    // 檢查使用者狀態
    if (!user.isActive) {
      throw new KyoError('E_AUTH_USER_INACTIVE', 'User account is inactive', 403);
    }

    // 產生 Token
    const tokens = await this.generateTokens(user, tenantId);

    // 記錄登入
    await this.logUserActivity(pool, user.id, 'login', {
      ip: credentials.ip,
      userAgent: credentials.userAgent,
    });

    return {
      user: this.sanitizeUser(user),
      accessToken: tokens.accessToken,
      refreshToken: tokens.refreshToken,
      expiresIn: 24 * 60 * 60, // 24 hours in seconds
    };
  }

  async generateTokens(user: User, tenantId: string): Promise<TokenPair> {
    const payload: JWTPayload = {
      userId: user.id,
      tenantId,
      role: user.role,
      permissions: this.getRolePermissions(user.role),
      iat: Math.floor(Date.now() / 1000),
    };

    const accessToken = jwt.sign(payload, this.jwtSecret, {
      expiresIn: this.tokenExpiry,
      issuer: 'kyo-system',
      audience: tenantId,
    });

    const refreshToken = jwt.sign(
      { userId: user.id, tenantId, type: 'refresh' },
      this.jwtSecret,
      { expiresIn: this.refreshExpiry }
    );

    return { accessToken, refreshToken };
  }

  private getRolePermissions(role: UserRole): Permission[] {
    const rolePermissions: Record<UserRole, Permission[]> = {
      admin: ['*'], // 全部權限
      manager: [
        'members:read', 'members:write', 'members:delete',
        'staff:read', 'staff:write',
        'services:read', 'services:write',
        'reports:read', 'reports:export',
        'payments:read', 'payments:write',
      ],
      staff: [
        'members:read', 'members:write',
        'checkins:read', 'checkins:write',
        'services:read',
        'payments:read',
      ],
      trainer: [
        'members:read',
        'checkins:read', 'checkins:write',
        'services:read',
        'training:read', 'training:write',
      ],
      receptionist: [
        'members:read', 'members:write',
        'checkins:read', 'checkins:write',
        'services:read',
        'payments:read', 'payments:write',
      ],
      accountant: [
        'payments:read', 'payments:write', 'payments:export',
        'reports:read', 'reports:export',
        'members:read',
      ],
      member: [
        'profile:read', 'profile:write',
        'checkins:read',
        'services:read',
        'payments:read',
      ],
    };

    return rolePermissions[role] || [];
  }

  async verifyToken(token: string, tenantId?: string): Promise<JWTPayload> {
    try {
      const payload = jwt.verify(token, this.jwtSecret, {
        issuer: 'kyo-system',
        audience: tenantId,
      }) as JWTPayload;

      // 檢查 Token 是否被撤銷（可選的黑名單機制）
      await this.checkTokenBlacklist(payload);

      return payload;
    } catch (error) {
      if (error instanceof jwt.TokenExpiredError) {
        throw new KyoError('E_AUTH_TOKEN_EXPIRED', 'Token has expired', 401);
      } else if (error instanceof jwt.JsonWebTokenError) {
        throw new KyoError('E_AUTH_TOKEN_INVALID', 'Invalid token', 401);
      }
      throw error;
    }
  }
}

會員管理服務

會員管理是健身房 SaaS 的核心功能，我們實作了完整的 CRUD 操作和進階查詢：

// packages/kyo-core/src/services/member-service.ts
export class MemberService {
  constructor(private connectionManager: TenantConnectionManager) {}

  async searchMembers(tenantId: string, params: MemberSearchParams): Promise<MemberSearchResult> {
    const pool = await this.connectionManager.getConnection(tenantId);

    // 建構查詢條件
    const conditions: string[] = [];
    const values: any[] = [];
    let paramIndex = 1;

    if (params.keyword) {
      conditions.push(`(
        m.name ILIKE $${paramIndex} OR
        m.member_code ILIKE $${paramIndex} OR
        m.email ILIKE $${paramIndex} OR
        m.phone ILIKE $${paramIndex}
      )`);
      values.push(`%${params.keyword}%`);
      paramIndex++;
    }

    if (params.status) {
      conditions.push(`m.status = $${paramIndex}`);
      values.push(params.status);
      paramIndex++;
    }

    if (params.joinDateFrom) {
      conditions.push(`m.join_date >= $${paramIndex}`);
      values.push(params.joinDateFrom);
      paramIndex++;
    }

    if (params.joinDateTo) {
      conditions.push(`m.join_date <= $${paramIndex}`);
      values.push(params.joinDateTo);
      paramIndex++;
    }

    // 建構完整查詢
    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
    const orderBy = `ORDER BY ${params.sortBy || 'm.created_at'} ${params.sortOrder || 'DESC'}`;
    const limit = params.limit || 20;
    const offset = ((params.page || 1) - 1) * limit;

    // 執行查詢
    const query = `
      SELECT
        m.*,
        ms.plan,
        ms.status as membership_status,
        ms.start_date as membership_start,
        ms.end_date as membership_end,
        (
          SELECT COUNT(*)
          FROM check_ins ci
          WHERE ci.member_id = m.id
          AND ci.check_in_time >= NOW() - INTERVAL '30 days'
        ) as visits_last_30_days,
        (
          SELECT ci.check_in_time
          FROM check_ins ci
          WHERE ci.member_id = m.id
          ORDER BY ci.check_in_time DESC
          LIMIT 1
        ) as last_visit
      FROM members m
      LEFT JOIN memberships ms ON m.id = ms.member_id AND ms.is_active = true
      ${whereClause}
      ${orderBy}
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
    `;

    values.push(limit, offset);

    const result = await pool.query(query, values);

    // 取得總數
    const countQuery = `
      SELECT COUNT(*) as total
      FROM members m
      LEFT JOIN memberships ms ON m.id = ms.member_id AND ms.is_active = true
      ${whereClause}
    `;

    const countResult = await pool.query(countQuery, values.slice(0, -2));
    const totalCount = parseInt(countResult.rows[0].total);

    return {
      members: result.rows.map(row => this.mapRowToMember(row)),
      pagination: {
        currentPage: params.page || 1,
        totalPages: Math.ceil(totalCount / limit),
        totalCount,
        pageSize: limit,
      },
    };
  }

  async checkInMember(tenantId: string, identifier: string): Promise<CheckInResult> {
    const pool = await this.connectionManager.getConnection(tenantId);

    try {
      await pool.query('BEGIN');

      // 尋找會員
      const member = await this.findMemberByIdentifier(pool, identifier);
      if (!member) {
        throw new KyoError('E_MEMBER_NOT_FOUND', 'Member not found', 404);
      }

      // 檢查會員狀態
      if (member.membershipInfo.status !== 'active') {
        throw new KyoError('E_MEMBERSHIP_INACTIVE', 'Membership is not active', 400);
      }

      // 檢查是否已經報到
      const todayCheckin = await pool.query(`
        SELECT id FROM check_ins
        WHERE member_id = $1
        AND DATE(check_in_time) = CURRENT_DATE
        AND check_out_time IS NULL
      `, [member.id]);

      if (todayCheckin.rows.length > 0) {
        throw new KyoError('E_ALREADY_CHECKED_IN', 'Member already checked in today', 400);
      }

      // 記錄報到
      const checkInResult = await pool.query(`
        INSERT INTO check_ins (id, member_id, check_in_time, created_at)
        VALUES ($1, $2, $3, $4)
        RETURNING *
      `, [
        `checkin-${Date.now()}-${Math.random()}`,
        member.id,
        new Date(),
        new Date()
      ]);

      // 更新會員最後到訪時間
      await pool.query(`
        UPDATE members
        SET last_visit = $1, visit_count = visit_count + 1
        WHERE id = $2
      `, [new Date(), member.id]);

      await pool.query('COMMIT');

      return {
        success: true,
        member: member,
        checkIn: this.mapRowToCheckIn(checkInResult.rows[0]),
        message: '報到成功',
      };
    } catch (error) {
      await pool.query('ROLLBACK');
      throw error;
    }
  }
}

API 路由整合

我們使用 Fastify 建立 RESTful API，支援租戶上下文和認證授權：

// apps/kyo-otp-service/src/routes/tenant-routes.ts
export async function tenantRoutes(fastify: FastifyInstance) {
  // 租戶身份驗證中間件
  fastify.addHook('preHandler', async (request) => {
    const tenantId = request.headers['x-tenant-id'] as string;
    if (!tenantId) {
      throw new Error('Tenant ID is required');
    }
    request.tenantId = tenantId;
  });

  // 會員搜尋
  fastify.get('/members', {
    schema: {
      querystring: MemberSearchParamsSchema,
      response: { 200: MemberSearchResultSchema },
    },
  }, async (request) => {
    const params = request.query as MemberSearchParams;
    return await memberService.searchMembers(request.tenantId, params);
  });

  // 會員報到
  fastify.post('/members/check-in', {
    schema: {
      body: Type.Object({
        identifier: Type.String({ minLength: 1, description: '會員編號、手機或email' }),
      }),
      response: { 200: CheckInResultSchema },
    },
  }, async (request) => {
    const { identifier } = request.body as { identifier: string };
    return await memberService.checkInMember(request.tenantId, identifier);
  });

  // 更新會員狀態
  fastify.patch('/members/:id/status', {
    schema: {
      params: Type.Object({ id: Type.String() }),
      body: Type.Object({ status: Type.Enum(MemberStatus) }),
    },
  }, async (request) => {
    const { id } = request.params as { id: string };
    const { status } = request.body as { status: MemberStatus };
    return await memberService.updateMemberStatus(request.tenantId, id, status);
  });
}

效能優化策略

1. 連線池管理

• 每個租戶獨立的連線池
• 動態連線建立和清理
• 閒置連線自動回收

2. 查詢優化

• 資料庫索引策略
• 分頁查詢優化
• 預載入關聯資料

3. 快取策略

• Redis 快取熱點資料
• 租戶配置快取
• 查詢結果快取

監控與日誌

// 連線監控
async getConnectionStats(): Promise<ConnectionStats[]> {
  const stats: ConnectionStats[] = [];

  for (const [tenantId, connection] of this.connections) {
    stats.push({
      tenantId,
      activeConnections: connection.pool.totalCount,
      idleConnections: connection.pool.idleCount,
      waitingClients: connection.pool.waitingCount,
      lastUsed: connection.lastUsed,
      isHealthy: connection.isActive,
    });
  }

  return stats;
}

// 效能監控
private async logSlowQuery(query: string, duration: number, tenantId: string) {
  if (duration > 1000) { // 超過1秒的查詢
    console.warn('Slow query detected:', {
      tenantId,
      query: query.substring(0, 100),
      duration,
      timestamp: new Date(),
    });
  }
}

今日總結

我們今天實作了完整的後端多租戶架構：

核心成就

1. Database-per-Tenant 策略：每個租戶獨立資料庫，確保資料隔離
2. 動態連線管理：自動建立、重用和清理資料庫連線
3. 完整的 RBAC 系統：支援七種使用者角色和細粒度權限控制
4. 會員管理服務：包含搜尋、報到、狀態管理等核心功能
5. 效能優化：連線池、快取、查詢優化等策略

技術亮點

• 可擴展性：支援無限租戶數量
• 安全性：完全的資料隔離和權限控制
• 效能：連線池管理和查詢優化
• 可維護性：清晰的架構和錯誤處理