從零打造 EDM 系統（一）：整體架構設計與技術選型

為什麼要自建 EDM 系統？

在開始之前，先釐清自建的合理時機：

✅ 適合自建的情況

訂閱者數量適中（500～10,000 人）但需要高度客製化
整合自有系統（CRM、會員資料庫、內容管理系統等）
掌控數據隱私，完全自有不依賴第三方
學習需求，想深入理解 Email 發送的技術細節
成本最優，免費額度 + 按量計費相比訂閱制更划算

❌ 不適合自建的情況

需要複雜的自動化行銷流程（Drip Campaign、條件分支等）
缺乏工程資源維護和迭代系統
訂閱者數量龐大（>50,000），需要企業級支援和 SLA 保障
需要高度複雜的 A/B 測試和分析功能

技術選型

核心元件一覽

元件	技術選擇	選擇理由
前端	Next.js 14 (App Router)	全棧開發、內建 API Routes、SSR/SSG 優化、部署簡單
資料庫	Supabase (PostgreSQL)	免費額度充足、實時訂閱、Edge Functions、完整開發者工具
Email 發送	Resend	API 簡潔直觀、React Email 整合、完整 Webhook 追蹤、價格透明
部署	Vercel	與 Next.js 完美整合、零配置部署、自動化 CI/CD

為什麼選 Resend？

雖然市面上有多個 Email API 服務，但 Resend 對於開發者來說最具優勢：

服務	免費額度	核心優勢
Resend	100 封/天、3,000 封/月	API 簡潔、React Email 支援、完整 Webhook、無隱藏費用
SendGrid	100 封/天	功能完整但設定複雜
Mailgun	5,000 封/月（3個月試用）	以驗證和基礎設施為主
Amazon SES	$0.10 / 1,000 封	低廉但設定繁複、冷啟動困難

Resend 的三大優勢：

API 極簡 — 一個 POST 請求即可發信，無需複雜的設定
完整 Webhook — sent、delivered、opened、clicked、bounced、complained 全追蹤
React Email — 用熟悉的 React 元件寫 Email 模板，維護性高

系統架構設計

整體流程圖

┌──────────────────────────────────────────────┐
│           管理員介面 (Dashboard)              │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐   │
│  │訂閱者管理 │  │活動管理  │  │分析報表  │   │
│  └──────────┘  └──────────┘  └──────────┘   │
└──────────────────────────────────────────────┘
                     ▼
┌──────────────────────────────────────────────┐
│         Next.js 後端 API Routes              │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐   │
│  │/api/send │  │ /api/    │  │/api/     │   │
│  │          │  │ webhook  │  │subscribe │   │
│  └──────────┘  └──────────┘  └──────────┘   │
└──────────────────────────────────────────────┘
     ▼              ▼              ▼
┌──────────┐  ┌──────────┐  ┌──────────┐
│ Resend   │  │Supabase  │  │Supabase  │
│ API      │  │Database  │  │Edge Func │
│(發送郵件)│  │(資料儲存)│  │(排程任務)│
└──────────┘  └──────────┘  └──────────┘

資料庫核心設計

四張關鍵資料表

整個系統以四張精心設計的資料表為核心：

1️⃣ subscribers（訂閱者表）

CREATE TABLE subscribers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  first_name VARCHAR(100),
  name VARCHAR(255),
  company VARCHAR(255),
  position VARCHAR(100),

  -- 分群與來源追蹤
  tier VARCHAR(50),           -- Tier1/Tier2/Tier3 等級
  source VARCHAR(50),         -- 來源：website/manual/import

  -- 狀態管理
  status VARCHAR(20) DEFAULT 'active',      -- active/unsubscribed/paused
  email_status VARCHAR(20) DEFAULT 'active', -- active/bounced/complained

  -- 時間戳
  created_at TIMESTAMPTZ DEFAULT NOW(),
  unsubscribed_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

設計思路： - tier 欄位支援分層策略 — 不同等級訂閱者接收不同內容或頻率 - email_status 自動追蹤 Email 健康度 — Webhook 退信自動標記為 bounced - source 記錄訂閱來源 — 便於分析各渠道轉換效率

2️⃣ edm_campaigns（發送活動表）

CREATE TABLE edm_campaigns (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  subject TEXT NOT NULL,
  preview_text TEXT,
  content TEXT NOT NULL,

  -- 目標設定
  target_tier TEXT,          -- 指定分群投放
  target_count INTEGER,      -- 實際投放人數

  -- 發送控制
  emails_per_day INTEGER DEFAULT 100,
  status TEXT DEFAULT 'draft',  -- draft/scheduled/sending/completed/cancelled
  scheduled_at TIMESTAMPTZ,

  -- 統計數據
  sent_count INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  failed_count INTEGER DEFAULT 0,
  open_count INTEGER DEFAULT 0,
  click_count INTEGER DEFAULT 0,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

設計思路： - emails_per_day 控制每日發送量 — 避免超過服務商配額 - status 狀態機嚴格控制發送流程 — 防止誤操作 - 即時統計欄位 — Webhook 更新時同步更新，提供實時儀表板

3️⃣ edmsendqueue（發送佇列表）

CREATE TABLE edm_send_queue (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id UUID REFERENCES edm_campaigns(id),
  subscriber_id UUID REFERENCES subscribers(id),

  -- 收件人快照（防止後續修改影響記錄）
  email TEXT NOT NULL,
  first_name TEXT,
  name TEXT,

  -- 發送狀態
  status TEXT DEFAULT 'pending',  -- pending/sent/failed/bounced
  batch_number INTEGER,
  scheduled_date DATE,

  -- Resend 追蹤 ID
  resend_email_id TEXT UNIQUE,
  
  -- Email 生命週期追蹤
  sent_at TIMESTAMPTZ,
  delivered_at TIMESTAMPTZ,
  opened_at TIMESTAMPTZ,
  clicked_at TIMESTAMPTZ,
  bounced_at TIMESTAMPTZ,
  complained_at TIMESTAMPTZ,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 創建索引提升查詢效能
CREATE INDEX idx_edm_queue_campaign ON edm_send_queue(campaign_id);
CREATE INDEX idx_edm_queue_status ON edm_send_queue(status);
CREATE INDEX idx_edm_queue_scheduled ON edm_send_queue(scheduled_date);
CREATE INDEX idx_edm_queue_resend_id ON edm_send_queue(resend_email_id);

設計思路： - 收件人快照 — 儲存發送時的收件人資訊，確保歷史記錄的準確性（即使日後修改訂閱者資訊也不影響） - Resend Email ID— 對應 Resend 返回的追蹤 ID，用於匹配 Webhook 事件 - 完整生命週期追蹤 — 從 sent 到 opened/clicked，記錄每一步時間戳

4️⃣ email_events（事件記錄表）

CREATE TABLE email_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  queue_id UUID REFERENCES edm_send_queue(id),
  resend_email_id TEXT NOT NULL,
  event_type TEXT NOT NULL,  -- sent/delivered/opened/clicked/bounced/complained
  event_data JSONB,          -- 完整 Webhook 原始資料
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_email_events_queue ON email_events(queue_id);
CREATE INDEX idx_email_events_type ON email_events(event_type);
CREATE INDEX idx_email_events_resend_id ON email_events(resend_email_id);

設計思路： - 完整事件記錄 — 儲存所有 Webhook 事件，便於除錯和分析異常 - JSONB 原始資料 — 保留最大彈性，日後可增加新的分析維度

資料關聯圖

┌─────────────────┐
│   subscribers   │
│   (訂閱者)       │
└────────┬────────┘
         │
         │ (多對多)
         │
         ▼
┌──────────────────────┐
│  edm_send_queue      │
│  (發送佇列)          │
└────────┬─────────────┘
         │
         │ (一對多)
         │
         ▼
┌──────────────────────┐
│   email_events       │
│   (事件記錄)         │
└──────────────────────┘

┌──────────────────────┐
│  edm_campaigns       │  (一對多)
│  (活動)              │──────→ edm_send_queue
└──────────────────────┘

智慧分批發送機制

為了避免超過 Resend 的免費配額限制（3,000 封/月），我們設計了智慧分批發送機制：

分批邏輯

假設活動目標 5,000 人，每日配額 100 封：

Day 1: 發送 Queue #1 ~ #100      (100 封)
Day 2: 發送 Queue #101 ~ #200    (100 封)
Day 3: 發送 Queue #201 ~ #300    (100 封)
...
Day 50: 發送剩餘項目             (完成)

實作步驟

步驟 1：建立佇列時分配批次號

WITH numbered_subscribers AS (
  SELECT
    sub.id,
    sub.email,
    sub.first_name,
    ROW_NUMBER() OVER (ORDER BY sub.created_at) as row_num
  FROM subscribers sub
  WHERE sub.status = 'active'
    AND sub.email_status = 'active'
    AND (target_tier IS NULL OR sub.tier = target_tier)
)
INSERT INTO edm_send_queue (
  campaign_id, subscriber_id, email, first_name,
  batch_number, scheduled_date
)
SELECT
  p_campaign_id,
  id,
  email,
  first_name,
  CEIL(row_num::NUMERIC / p_daily_limit) as batch_number,
  CURRENT_DATE + (CEIL(row_num::NUMERIC / p_daily_limit) - 1)
FROM numbered_subscribers;

步驟 2：排程任務每日執行

// Supabase Edge Function 或 Next.js 後端定時任務
export async function sendDailyBatch() {
  const today = new Date().toISOString().split('T')[0];
  
  const { data: queueItems, error } = await supabase
    .from('edm_send_queue')
    .select('*')
    .eq('status', 'pending')
    .lte('scheduled_date', today)
    .limit(100);  // 每日限制 100 封

  for (const item of queueItems) {
    await sendViaResend(item);
  }
}

配額管理機制

建立簡單但有效的配額監控：

CREATE TABLE email_quota (
  date DATE PRIMARY KEY,
  sent_count INTEGER DEFAULT 0,
  monthly_sent INTEGER DEFAULT 0
);

CREATE FUNCTION get_quota_status()
RETURNS TABLE (
  today_sent INTEGER,
  today_remaining INTEGER,
  monthly_sent INTEGER,
  monthly_remaining INTEGER,
  can_send_today BOOLEAN
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    COALESCE(eq.sent_count, 0) as today_sent,
    GREATEST(0, 100 - COALESCE(eq.sent_count, 0)) as today_remaining,
    COALESCE(eq.monthly_sent, 0) as monthly_sent,
    GREATEST(0, 3000 - COALESCE(eq.monthly_sent, 0)) as monthly_remaining,
    COALESCE(eq.sent_count, 0) < 100 as can_send_today
  FROM email_quota eq
  WHERE eq.date = CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

下一篇預告

這篇介紹了完整的系統架構和資料庫設計，下一篇將深入實現細節，涵蓋：

Resend API 基本使用 — SDK 初始化、基本發送
HTML Email 模板處理 — React Email 元件編寫
變數替換機制 — 個性化郵件內容的實作
錯誤處理與重試策略 — 保證可靠性

系列文章導航

✅ 整體架構設計與技術選型（本篇）
Resend API 串接實戰（連載中）
Webhook 追蹤系統實作（規劃中）
訂閱者管理與分群策略（規劃中）
發送最佳化與異常處理（規劃中）

對這套系統有想法嗎？ 歡迎在下方評論或聯繫我分享你的經驗！