# Email Marketing Sequences **Date:** 2026-05-31 **Status:** Draft **Spec:** 99 **Tier:** Business+ **Depends on:** `marketing-catalogs-campaigns`, `system-communications-notifications`, `marketing-leads-pipeline`, `foundation-auth-rbac` **Referenced by:** `marketing-catalogs-campaigns` --- ## Overview Spec 23 (`marketing-catalogs-campaigns`) defines one-time blast `campaigns`. This spec adds **email sequences**: automated multi-step drip series that enroll contacts based on triggers (lead created, stage changed, etc.) and send emails with configurable delays between steps. --- ## Data Model ```sql CREATE TABLE email_sequences ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id), name TEXT NOT NULL, trigger TEXT NOT NULL CHECK (trigger IN ( 'lead_created', 'lead_stage_updated', 'lead_converted', 'customer_created', 'proposal_sent', 'manual' )), trigger_filter JSONB, -- e.g. { stage: 'Qualified' } for lead_stage_updated status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'paused', 'archived')), created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE sequence_steps ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), sequence_id UUID NOT NULL REFERENCES email_sequences(id) ON DELETE CASCADE, position INTEGER NOT NULL, -- step order (1, 2, 3...) delay_hours INTEGER NOT NULL DEFAULT 0, -- delay after previous step (0 = immediately after trigger) subject TEXT NOT NULL, body_html TEXT NOT NULL, -- Handlebars template with {{firstName}}, {{tenantName}}, etc. from_name TEXT, -- NULL = tenant default UNIQUE (sequence_id, position) ); CREATE TABLE sequence_enrollments ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), sequence_id UUID NOT NULL REFERENCES email_sequences(id), tenant_id UUID NOT NULL, contact_type TEXT NOT NULL CHECK (contact_type IN ('lead', 'customer')), contact_id UUID NOT NULL, -- lead.id or customer.id email TEXT NOT NULL, enrolled_at TIMESTAMPTZ DEFAULT now(), current_step INTEGER DEFAULT 1, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'completed', 'unsubscribed', 'bounced')), next_send_at TIMESTAMPTZ, UNIQUE (sequence_id, contact_id) -- one enrollment per contact per sequence ); CREATE INDEX idx_seq_enroll_next ON sequence_enrollments(tenant_id, next_send_at) WHERE status = 'active'; ``` --- ## Sequence Builder UI `/marketing/sequences`: ``` ┌──────────────────────────────────────────────────────────────┐ │ Sequences [+ New sequence]│ │ │ │ Welcome series (lead_created) ACTIVE 3 steps │ │ Proposal follow-up (proposal_sent) ACTIVE 2 steps │ │ Re-engagement (manual) DRAFT 4 steps │ └──────────────────────────────────────────────────────────────┘ ``` ### Sequence Editor: `/marketing/sequences/:id` ``` ┌──────────────────────────────────────────────────────────────┐ │ Welcome series [Pause] [Settings] │ │ │ │ Trigger: Lead created [Change] │ │ │ │ Step 1 Immediately │ │ ┌────────────────────────────────────┐ │ │ │ Subject: Welcome to {{tenantName}} │ [Edit] [✕] │ │ │ Preview: Hi {{firstName}}, ... │ │ │ └────────────────────────────────────┘ │ │ ↓ wait 24 hours │ │ Step 2 +1 day │ │ ┌────────────────────────────────────┐ │ │ │ Subject: Quick question about your │ [Edit] [✕] │ │ │ Preview: Hi {{firstName}}, ... │ │ │ └────────────────────────────────────┘ │ │ ↓ wait 72 hours │ │ Step 3 +3 days │ │ ┌────────────────────────────────────┐ │ │ │ Subject: Resources for you │ [Edit] [✕] │ │ └────────────────────────────────────┘ │ │ │ │ [+ Add step] │ └──────────────────────────────────────────────────────────────┘ ``` Step email editor: same Tiptap-based editor as spec 43 (`email-template-editor`). Template variables: `{{firstName}}`, `{{lastName}}`, `{{email}}`, `{{tenantName}}`, `{{tenantSlug}}`. --- ## Enrollment Triggers On each trigger event (handled in the same transaction/handler as the event): | Trigger | Condition | Enrolled | |---------|-----------|----------| | `lead_created` | Always | New lead | | `lead_stage_updated` | `trigger_filter.stage` matches `toStage` | Lead that changed stage | | `lead_converted` | Always | Lead that converted | | `customer_created` | Always | New customer | | `proposal_sent` | Always | Lead/customer proposal sent to | | `manual` | Staff action only | Any lead or customer | Enrollment skips if already enrolled and not completed/unsubscribed (UNIQUE constraint). On enrollment: `next_send_at = enrolled_at + step[1].delay_hours * interval '1 hour'`; `current_step = 1`. --- ## Sequence Send Cron Cron: `email-sequences-send` — every 15 minutes (`*/15 * * * *`). ```sql SELECT se.id, se.email, se.contact_type, se.contact_id, se.current_step, ss.subject, ss.body_html, ss.from_name, se.sequence_id FROM sequence_enrollments se JOIN sequence_steps ss ON ss.sequence_id = se.sequence_id AND ss.position = se.current_step WHERE se.status = 'active' AND se.next_send_at <= now() LIMIT 500 -- batch cap ``` Per enrollment: 1. Send email via Resend with rendered template (replace `{{var}}` with contact data) 2. Check if `current_step` = last step → set `status = 'completed'` 3. Else → `current_step += 1`, `next_send_at = now() + next_step.delay_hours * interval '1 hour'` Update in batch after send. --- ## Unsubscribe Every sequence email includes an unsubscribe link: `https://zync.is/unsubscribe/sequence?e={hmacToken}` On click: `sequence_enrollments.status → 'unsubscribed'`. Also sets the campaign subscriber `status = 'UNSUBSCRIBED'` (spec 23 `campaign_subscribers`) if the contact appears there. --- ## Analytics: Sequence Stats Sequence detail shows send/open stats per step using Cloudflare Analytics Engine (same `email_events` dataset as campaigns in spec 23): ``` │ Step │ Sent │ Opened │ Clicked │ Unsub │ │ 1 │ 892 │ 412 (46%) │ 83 (9%) │ 12 │ │ 2 │ 748 │ 280 (37%) │ 44 (6%) │ 9 │ │ 3 │ 651 │ 190 (29%) │ 28 (4%) │ 5 │ ``` --- ## Enrollments Management `sequence_enrollments` tracks per-contact state but the editor only edits steps. The sequence detail (`/marketing/sequences/:id`) gains an **[Enrollments]** tab alongside the step editor, giving staff a screen to view and manage who is enrolled. ``` ┌──────────────────────────────────────────────────────────────┐ │ Welcome series [Steps] [Enrollments] [Stats] │ │ │ │ [Search email/name…] [Status: All ▾] │ │ │ │ Contact Type Step Status Next send│ │ ─────────────────────────────────────────────────────────── │ │ dana@acme.com Lead 2 of 3 ● Active in 18h │ │ noa@beta.co.il Customer 1 of 3 ● Active in 2h │ │ amir@gama.com Lead 3 of 3 ✓ Completed — │ │ lior@x.co.il Lead 1 of 3 ✗ Unsubscribed — │ │ yael@delta.com Customer 2 of 3 ⚠ Bounced — │ │ │ │ 248 enrolled · 31 active · 12 unsubscribed │ └──────────────────────────────────────────────────────────────┘ ``` **Columns:** Contact (email + name, links to lead/customer detail), Type (`lead`/`customer`), Step (`current_step` of total), Status (`active`/`completed`/`unsubscribed`/`bounced`), Next send (`next_send_at` relative, or "—" when not active). **Filters:** free-text search on email/name; status filter (All · Active · Completed · Unsubscribed · Bounced). **Per-row action — Unenroll:** staff can manually remove an active enrollee. **[Unenroll]** sets `sequence_enrollments.status = 'unsubscribed'` and clears `next_send_at` (the cron skips non-`active` rows). Confirmation prompt: "Stop sending this sequence to {email}?". The `UNIQUE (sequence_id, contact_id)` constraint means re-enrollment (manual sequences only) is allowed once unenrolled, matching the existing enrollment-skip rule. Counts in the tab footer feed from the same query. --- ## Manual Enrollment Staff can enroll any lead or customer manually into a `manual`-trigger sequence: `POST /api/sequences/:id/enroll` — body: `{ contact_type, contact_id }`. Also available from lead detail and customer detail pages via "Enroll in sequence" action. --- ## API ``` GET /api/sequences → list sequences for tenant Requires: marketing:read (Business+) POST /api/sequences → create sequence body: { name, trigger, trigger_filter?, steps: [{delay_hours, subject, body_html}] } Requires: marketing:write (Business+) PATCH /api/sequences/:id → update sequence (name, trigger, steps, status) Requires: marketing:write POST /api/sequences/:id/enroll → manual enrollment body: { contact_type, contact_id } Requires: marketing:write GET /api/sequences/:id/enrollments → list enrollees for the Enrollments tab (paginated) query: { status?, search?, page? } Returns: [{ id, contact_type, contact_id, email, current_step, total_steps, status, next_send_at, enrolled_at }] Requires: marketing:read POST /api/sequences/:id/enrollments/:enrollmentId/unenroll → manual unenroll: status → 'unsubscribed', next_send_at → NULL Requires: marketing:write GET /api/sequences/:id/stats → step-level send/open/click stats Requires: marketing:read ``` --- ## Architecture Decisions | Decision | Choice | Reason | |----------|--------|--------| | Separate from campaigns | Not campaign type | Sequences are ongoing (new enrollees enter continuously); campaigns are one-time blasts; different data model and delivery pattern | | Handlebars in `body_html` | Not React email | Server-rendered at send time from stored HTML template; no build step needed; Resend accepts raw HTML | | 15-minute cron | Not per-enrollment timer | Durable Objects per enrollment would be overkill; 15-minute delivery window is acceptable for drip emails | | `next_send_at` index on active only | Not full table | Partial index on `WHERE status = 'active'` keeps the cron scan tight | | Business+ gate | Not all tiers | Email sequences require campaign credits and marketing infra; Freelancer tier uses campaigns for one-offs |