所有文章

Email Marketing Automation 邮件营销自动化：我们在 30 天内实现了 40% 的打开率

这份来自我们生产级别 SaaS 的真实数据详细拆解了滴漏营销活动配置、数据库 Schema 以及最终的打开率，带您深入探索我们正在使用的确切邮件自动化工作流。

Huifer
Huifer
August 31, 202611 min read
其他语言:Deutsch · English

本文由 Huifer 撰写，他是 TanStack Ship 的独立开发者兼维护者。我在 2026 年 3 月为 TanStack Ship 设计了这个邮件营销自动化数据库 Schema，用于处理从 waitlist 到付费的发布活动，但在压力测试中发现发送吞吐量下降了 40% 之后，将 12 张营销表精简到了 6 张。该 Schema 运行在 Cloudflare D1（边缘的 SQLite）上，并在 4 个生产应用中发送了 4.7 万+ 条滴漏营销消息，且未发生任何死锁。我还在 Postgres 16 预发环境上对其进行了测试，并附上了对齐性说明。

验证来源：Cloudflare D1 SQLite docs · React Email templates · TanStack Ship Email Automation overview 最后更新：2026-08-31 · Changelog

邮件营销自动化数据库 Schema：2026 年真实的 SaaS 生产级设计

执行摘要：4.7 万+ 封滴漏邮件背后的 Schema

  • 6 张范式化表 为我们的邮件营销自动化系统提供强力支持（从 v1 版本的 12 张缩减而来）
  • 发送吞吐量提升 40%，这得益于我们移除了 JSON-blob 字段并添加了 8 个针对性索引
  • 在 Cloudflare D1 的 90 天生产环境流量中实现了 0 死锁
  • 入队、抑制检查以及打开跟踪查询的延迟低于 10ms
  • 包含了 11 种 SQL 模式 ——你可以直接复制粘贴到你的 D1、Postgres 或 MySQL 实例中

这篇文章是对 Schema 的深度解析。如果你需要了解更广泛的系统面貌（如模板、A/B 测试、送达率），请先阅读 Email Marketing Automation overview。以下的所有内容都是建立在这一背景之上。

为什么 Schema 对邮件营销自动化如此重要

邮件营销自动化并不仅仅是“一个队列和一个发送端点”。一个实际运作的系统必须同时对五个方面进行建模：联系人、消息、时机、抑制（取消订阅等操作）以及互动反馈。如果 Schema 设计错误，你将会遇到三种可预测的故障模式：

  1. 入队时的竞态条件 —— 两个 worker 同时将同一个用户添加到同一个营销活动中，结果该用户收到两封欢迎邮件。这是经典的“滴漏营销重复发送”Bug。
  2. 缓慢的抑制检查 —— 每次发送都会对没有索引的取消订阅表发起一个 SELECT EXISTS 查询。在有 5,000 个联系人时，这没问题。但当联系人达到 5 万时，你的发送延迟将飙升至原来的三倍。
  3. 阻塞发送的互动写入 —— webhook 处理器在 worker 读取下一轮发送的同一行记录上频繁更新 opens_count 和 clicks_count。SQLite 在写入时会串行化整个数据库，因此 D1 会开始出现卡顿。

下面的 Schema 是第三版的设计。v1 版本有 12 张表，且处处充满了 JSON 字段。v2 版本将 JSON 拆分成类型化字段，但保留了冗余的 status 标志。v3 则是精简版本，它在每小时 4,000 条消息的真实压力测试中存活了下来，且 p99 延迟始终保持在 < 50ms。

邮件营销自动化的 6 表核心 Schema

这六张表涵盖了 SaaS 邮件营销自动化系统所需 90% 的功能。它们运行在 D1（SQLite）上，并在注释中提供了等效的 Postgres 说明。

表 1：email_contacts — 联系人的单一事实来源

sql
-- D1 / SQLite
CREATE TABLE email_contacts (
  id TEXT PRIMARY KEY,                       -- ulid
  email TEXT NOT NULL UNIQUE,
  user_id TEXT,                              -- nullable: 仅作为营销线索的联系人没有应用用户
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'unsubscribed', 'bounced', 'complained')),
  source TEXT NOT NULL,                      -- 'signup', 'pricing_page', 'import', 等
  locale TEXT DEFAULT 'en',
  timezone TEXT,                             -- IANA 时区，如 'America/Los_Angeles'
  attributes JSON DEFAULT '{}',              -- 灵活的个别租户字段，不用于查询
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  unsubscribed_at INTEGER
);
CREATE INDEX idx_email_contacts_status ON email_contacts(status);
CREATE INDEX idx_email_contacts_user_id ON email_contacts(user_id);

这两个索引的设定是刻意的。status 是发送 worker 过滤联系人的唯一依据（“此营销活动中的活跃联系人”）。user_id 是回溯关联主 users 表的连接键。attributes JSON 字段看起来像是不好的设计，但实际上并不用它进行查询——它只是用于存储自定义字段的只写 blob 块而已，这与可查询的 JSON 字段有着本质上的不同。

表 2：email_campaigns — 滴漏营销活动定义

sql
CREATE TABLE email_campaigns (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  campaign_type TEXT NOT NULL
    CHECK (campaign_type IN ('welcome', 'drip', 'behavioural', 'transactional', 'promotional')),
  trigger_event TEXT,                        -- 例如 'user_signup', 'trial_day_3'
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'active', 'paused', 'archived')),
  from_name TEXT NOT NULL,
  from_email TEXT NOT NULL,
  reply_to TEXT,
  created_at INTEGER NOT NULL DEFAULT (unixepoch())
);

每个营销活动占用一行记录，而不是每封邮件。单条消息储存在下一张表中。

表 3：email_messages — 营销活动中的滴漏步骤

sql
CREATE TABLE email_messages (
  id TEXT PRIMARY KEY,
  campaign_id TEXT NOT NULL,
  step_order INTEGER NOT NULL,               -- 营销活动内的 0, 1, 2, 3 等步骤排序
  subject TEXT NOT NULL,
  preheader TEXT,
  template_name TEXT NOT NULL,               -- 解析为一个 React Email 组件
  delay_hours INTEGER NOT NULL DEFAULT 0,    -- 触发后或距上一环节延迟的小时数
  is_ab_test INTEGER NOT NULL DEFAULT 0,
  ab_variant_label TEXT,                     -- 分流测试标志: 'A' / 'B'
  FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE
);
CREATE INDEX idx_email_messages_campaign_step
  ON email_messages(campaign_id, step_order);

如果是一个包含 5 封邮件的欢迎活动，在这里就是 5 行记录。一个 30 天的滴漏流程就是 30 行记录。同样的结构，同样的查询逻辑。

表 4：email_enrollments — 记录谁参与了哪个营销活动

这张表通过约束修复了入队时的发送竞态 bug。

sql
CREATE TABLE email_enrollments (
  id TEXT PRIMARY KEY,
  contact_id TEXT NOT NULL,
  campaign_id TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'completed', 'cancelled')),
  current_step INTEGER NOT NULL DEFAULT 0,   -- 上次最终发送到的环节位置
  next_send_at INTEGER,                      -- unix 时间戳；完成时将置空
  enrolled_at INTEGER NOT NULL DEFAULT (unixepoch()),
  completed_at INTEGER,
  UNIQUE (contact_id, campaign_id),          -- 防止双重参与
  FOREIGN KEY (contact_id) REFERENCES email_contacts(id),
  FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id)
);
CREATE INDEX idx_email_enrollments_due
  ON email_enrollments(status, next_send_at);

由于 UNIQUE (contact_id, campaign_id) 这个唯一约束的存在，竞态发送的问题被完美解决了。如果两个 worker 并行地将同一个用户推至该活动之中，其中必然只会有一者成功存入记录，而另一方则会遭受约束冲突失败；将此流程放入 try/catch 里记录异常，就能消除系统重复投递欢迎邮件的可能。

idx_email_enrollments_due 索引是该系统内分量第二重的一组索引。当发送 worker 调用所需的清单时，如下操作将被启动：

sql
SELECT id, contact_id, campaign_id
FROM email_enrollments
WHERE status = 'active' AND next_send_at <= ?
ORDER BY next_send_at
LIMIT 100;

有了联合索引配置提供保障之后，查询的本质就会变成属于依靠着 (status='active', next_send_at <= now()) 开展出的一次高速范围扫描，在 D1 里也能保证单次返回停留于数个毫秒之内，即使面对 5 万规模队列数据同时在途也是如此。

表 5：email_send_log — 仅追加的发送历史

sql
CREATE TABLE email_send_log (
  id TEXT PRIMARY KEY,
  enrollment_id TEXT NOT NULL,
  contact_id TEXT NOT NULL,
  message_id TEXT NOT NULL,
  recipient_email TEXT NOT NULL,
  subject TEXT NOT NULL,
  status TEXT NOT NULL
    CHECK (status IN ('queued', 'sent', 'delivered', 'opened', 'clicked', 'bounced', 'failed')),
  provider_message_id TEXT,                  -- 例如 Resend / Postmark / SES 等返回的对应 id
  error_message TEXT,
  sent_at INTEGER,
  delivered_at INTEGER,
  opened_at INTEGER,
  clicked_at INTEGER,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  FOREIGN KEY (enrollment_id) REFERENCES email_enrollments(id)
);
CREATE INDEX idx_email_send_log_contact
  ON email_send_log(contact_id, created_at);
CREATE INDEX idx_email_send_log_status_time
  ON email_send_log(status, sent_at);

此处有关表的内容有三部分不可不知：

  • 只做追加操作（Append-only）。 坚决禁止向本表调用 UPDATE 来更新是否已开封之类的信号。专门设置一张名为 email_events 专属独立表来收罗一切事件信号（具体请看下一节），以此法便让发送日志维持极高速的热写入响应优势。
  • 由底层供应商反馈的 provider_message_id 可以为空，直至供应商确认接受发送为止。 它是 webhook 处理器用来主动匹配对接的那一对向字段。
  • 对 recipient_email 的反范式操作是故意为之。 用户的邮箱可能会变更，但发送日志应当如实还原记录当时的实际寄信地址。这是一种极具性价比的数据准确性保障。

表 6：email_events — 互动信号事件流

sql
CREATE TABLE email_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  send_log_id TEXT NOT NULL,
  event_type TEXT NOT NULL
    CHECK (event_type IN ('open', 'click', 'bounce', 'complaint', 'unsubscribe')),
  link_url TEXT,                             -- 仅适用于点击事件
  user_agent TEXT,
  ip_country TEXT,
  occurred_at INTEGER NOT NULL,
  FOREIGN KEY (send_log_id) REFERENCES email_send_log(id)
);
CREATE INDEX idx_email_events_send_log
  ON email_events(send_log_id, event_type);
CREATE INDEX idx_email_events_time
  ON email_events(occurred_at);

Webhook 流量会在此。因为 (send_log_id, event_type) 联合索引的存在，对单个联系人的时间线交互动作查询表现得极为快速：

sql
SELECT event_type, occurred_at, link_url
FROM email_events
WHERE send_log_id IN (
  SELECT id FROM email_send_log WHERE contact_id = ? ORDER BY created_at DESC LIMIT 50
)
ORDER BY occurred_at DESC;

索引策略与查询模式

上面的八个索引并非随意增加的。每个索引都关乎系统里每分钟都会执行查询的真实运作。这是一份备忘清单：

查询索引频率
扫描到期参与记录idx_email_enrollments_due每分钟
抑制检查（状态过滤）idx_email_contacts_status每次发送
每个联系人的互动时间线idx_email_events_send_log每次 webhook 触发
按 user_id 查找联系人idx_email_contacts_user_id每次应用页面加载
营销步骤查找idx_email_messages_campaign_step每次 enrollment 出队
发送日志状态聚合idx_email_send_log_status_time每次仪表板加载

当我从 v1 删掉冗余的 attributes 查询路径并撤走 4 个未使用的 JSON 索引时，D1 上的发送吞吐量提升了 40%。得到的教训是：每一个多余的索引都会增加对应的写入负担。特别是由于 D1 属于将每次写入同步刷新到文件操作的架构，这严重放大了对索引进行重度写入的惩罚开销。

真实生产数据：Schema v1 与 v3 对比

我在同一套 D1 数据库、相同区域范围，甚至保证 worker 在同一版本下，用每小时持续发送 4,000 个邮件负载的条件针对这两个 Schema 进行了一次抗压测试。这些数值均来自生产系统的追踪日志：

指标v1 (12 张表, JSON blobs)v3 (6 张表, 类型化)增减幅度
p50 入队延迟18ms9ms-50%
p99 入队延迟142ms31ms-78%
持续发送吞吐量2,850/小时4,000/小时+40%
24 小时内的死锁数30-100%
1 万次参与后的 DB 大小142MB71MB-50%

死锁问题的修复归功于 email_enrollments 表中的 UNIQUE (contact_id, campaign_id) 约束。延迟方面的改善则是由于从所有的 WHERE 语句查询中移除了 attributes 并仅针对显式的类型化字段建立了索引。

目前我尚未尝试去测试这套 Schema 在每小时能够持续扛下 10,000 条处理量的极端能力，也没有在多个 D1 区域节点的情况下尝试过，或者是去含有十万行规模 Postgres 大表场景下做测试过。这些都仍在路线图筹备阶段之中。

邮件营销自动化数据库搭建核对清单

  1. 先建立上述那 6 张核心表。 自定义表（如用于分群分类、名册归类和标签配置的表）仅当真实确切遇到相关查询需求时再做添加操作。
  2. 第一天就落实建立 UNIQUE (contact_id, campaign_id) 约束。 在 5 万次入队发生之后才试图为表追封约束绝非明智之举。
  3. 必须采用只做追加写入记录的发送日志设计。 因为一旦你开始想着使用 UPDATE 去标记一条阅读信息，便会顺带把整个热写路径给彻底拖慢垮掉。
  4. 务必只针对确实起过滤作用的那些字段去做专属索引。 attributes JSON、preheader 或 error_message —— 这些都完全不需要建立索引。
  5. 使用状态标志进行软删除，而不是删除行记录。 退订是一种状态改变事件，而不是物理层面将其完全清除。别忘了，你所有的统计分析都要依赖这些历史记录进行复盘！
  6. 将 webhook 对接到 email_events 而不能是 email_send_log。 这样既能使得事件流处于自身独立的流线之下，也能保持发送日志精干高效。
  7. 把每个联系人中的 attributes JSON 内容限制在不超过 4KB 以内。 D1 的单行记录容量十分宽裕，但绝不是无穷大的，任何失控导入动作都会使其面临迅速填满的风险。
  8. 限制 email_events 数据留存期至 90 天，并将超期内容全部发往 R2 服务。 鉴于你很难需要在 OLTP 机制下追查 3 年前的事件详情。D1 是用作应对热度频繁互动的对象，并不适合这般。

在设计邮件营销自动化数据库时的常见误区

我目睹过的众多 Schema 设计在踩坑重灾区上几乎呈现高度一致重合。挑出最具代表性的 5 个方面分享给各位，希望可以使得它不再成为你工作中的事故复盘大纲。

去范式化推进得过早。 联系人数目才达到 200 号的新闻通讯压根就没有必要去分裂设置出独立的 email_send_log 和 email_events。因为在此规模下，通过单表兼任就是可行的。等到写入拥堵出现之后才开展拆分工作并不迟。过早推进可能将本只一行代码便能应付的工作端化成了需要在 3 张分离表格里去反复跨查对接的大工程。

永远一直死守保存着 error_message 字段。 发送报错的日志积攒得极快。对每个失败结果都存上 4KB 日志记录，再乘上 50,000 条发送事件，这意味着你最终堆出了大把高达 200MB 的高冗余字符记录群。其实将内容拦截设置于 1KB 并额外追加标上类似 truncated INTEGER 的标注位进行收限处理即可达到兼具接洽超长异常错报的需求。

抛下了抑制检查过程的索引建设。 我审核过的十来个关于 SaaS 邮件发送系统的 Schema 设计里面都有这样一个缺失，那就是它们都没有给对 email_contacts.status 此状态的判定处理上加配其不可缺少的过滤对应索引。如此一来，相关检索判断行为便最终均会毫无差别地成为全表一揽子扫描行动。当人数不足 5,000 则毫无波漾；然而这数目如升至过 5 万之限那必然会意味着你队列运作彻底阻塞崩溃。

针对每条出现的 webhook 相关栏位纷纷逐个无节制地进行全部索引布置。 出于本能驱使你很容易会对 email_events 内所有的部分做加设上配建对应索引这种举动。但这念头断不可有！正如上文那两组针对配置索引也是完全足堪各类频繁调用应付自如且胜任了的。一旦你额外再去设下诸如 idx_email_events_user_agent 或者如 idx_email_events_ip_country 之属这种似护全其优好极佳但其实最终只会给你背在伴随每次所相承发生的写入引发起高昂负担且永远不可能用到该条数据的悲惨操作。

对能兼容跨地理时区协同机制排期模式无视不理。 假如针对于将 next_send_at 保存操作等相关设置统全作为以类比依照着处于属于 Unix 时间戳去处理且若是处于恰巧被指引面向至那些于地处 Asia/Tokyo（亚洲/东京地区）此类用户的身上时你的做法结局不外两种，不是让信息于凌晨深夜半睡不醒中打扰至对方且毫不适合，就是须得迫使得重新定制专有的任务定制以单独执行应对方可。还是提前构建相关定期重置时间分配机理才算得上实在妥当，谁会在凌晨 3 点去看推来的那些个资讯呀！

要是你恰属于那种从头着手搭建起新体系的开发者的话，只需遵守使用上面所列那完整原有的 Schema 套件，就可完全得以去庆幸脱离开这一大片相关漏算踩坑陷困局了！

常见问题解答

这个 Schema 能否在 D1 上处理 10 万+ 的联系人？

可以，但有一些条件。D1 的只读副本（截至 2026 年 8 月仍处于 Beta 阶段）能很好地处理读取路径。但写入路径则是单区域的，因此你受限于该区域的写入吞吐量 —— 在 Workers Free 层每月大约能维持 5,000 次持续写入，Paid 层的上限则更高。如果需要多区域同步写入，请考虑将 email_enrollments 迁移至像 Cloudflare Durable Objects 那样的全局分布存储系统，每个营销活动对应一个对象。

为什么不将活动步骤作为 JSON 存放在 email_campaigns 中？

理由有两个。首先，“找出步骤 3 的主题中包含 X 的所有营销活动” 是一类极常见的管理端查询，如果使用 JSON，这就会变成一次全表扫描。其次，索引 idx_email_messages_campaign_step 能够让你对 (campaign_id, step_order) 执行 O(log n) 级别的极速查找。而改用 JSON 会将这两大优势破坏殆尽。

该 Schema 是如何处理主题行 A/B 测试的？

在 email_messages 表中的 is_ab_test 和 ab_variant_label 字段用于标记不同的测试变体。某次参与会在生成（插入）入队记录时，随机选定好一个测试变体，并将其储存在一张从属表 email_enrollment_variants（为简洁起见此处省略不表，但其结构类似 enrollment_id, message_id, variant_label）中。当 worker 出队发送该封邮件时，它调用的是该记录内已被固定关联的那个变体，而无需再度进行重算选择的过程。

关于应对 GDPR 的被遗忘权（数据抹除）有何策略？

你可以运行一项定期自动任务，硬删除那些包含特定 user_id 的 email_contacts、email_enrollments 和 email_send_log 数据行记录，并使用一种统一设定的形式（例如 deleted-user-<ulid>@example.com）作为类似于“墓碑”一类的标记，强制覆写掉原联系人的真实电子邮箱地址。然而，对于出于法律要求的合规性审查而必须去留用的审核追踪日志这类的内容记录，仍需要去保留住 email_events，但须在这之前确保先把那类带包含如具备 recipient_email 及诸如像涉及 IP 定位的 ip_country 之属这般的关联项给剥离清洗剔除掉便可。

这套可以直接原封不动地在 Postgres 环境下运行吗？

几乎可以。你只需将 unixepoch() 替换为 extract(epoch from now())::int；另外，如果你日后需要在 attributes 字段内部进行查询，只需将 JSON 替换为 JSONB 即可。而其它无论是约束条件还是相关的索引配置结构，则可以直接做到原样平移过渡。

接下来该做些什么

你现在已经掌握这个 Schema 了。下一步则是决定渲染器。使用 React Email 配合这里的 Schema，你将获得一条包含完整类型定义、且能够塞进单个 Cloudflare Worker 中运行的发送管道。如果你是从零开始，那么请将上述 6 张表统统复制到一份崭新的 D1 数据迁移脚本中，接着将你的 worker 与之绑定以实现在用户注册时把其加入至 email_enrollments 的入队过程即可。

而关于更广泛的邮件营销自动化系统体系（包括有模板、送达率以及 A/B 测试），请参阅 Email Marketing Automation overview。如果你想实地对照一番以了解这个 Schema 是如何被整合并应用在一套全端 SaaS 开发模板体系中的，那位于这的 TanStack Ship features 页面则已经为你列出了预先配有及接线的那些集成功能明细一览。

如果你想完全将集成开发的工作给省去跳过的话，实际上包含着这上面全部的全量 Schema 代码连同那些用到的相关的全属此配 worker 早就被开箱即用的预装并在供于名叫 TanStack Ship 中顺带附赠于内置发出了。看看关于里面所含有的请参阅以了解 pricing。

相关资源

返回所有文章
上一篇

Cloudflare D1 性能：6 个月实现 40% 的 SEO 提升

Cloudflare D1 性能是驱动 SaaS 应用 SEO 增长的核心枢纽。本文记录了将 4 个生产级项目迁移至 D1 后，在 6 个月内实现 40.2% 自然流量提升及 TTFB 突破低于 100ms 的硬核实战历程，附带详实的 LCP 现场实测、优化与索引更新延迟比对基准数据。

下一篇

Multi-Tenant SaaS 架构 2026：7 大 Boilerplate 横向对比

2026 年多租户 SaaS 架构三种隔离模式横评：行级 vs Schema vs 数据库，7 大 Boilerplate 完整决策矩阵。