SaaS Email Sequences: Transactional System, Template Engine, Queuing, and Analytics

Email is the highest-ROI channel in SaaS. Onboarding sequences, trial conversion nudges, and renewal reminders drive activation and retention more reliably than any other touchpoint. But the implementation — rendering templates, queuing delivery, tracking opens, respecting unsubscribes — is more complex than it looks. A dropped email during onboarding loses activations. A missing unsubscribe link loses CAN-SPAM compliance.

This post covers the full email infrastructure stack: React Email for type-safe templates, BullMQ for reliable queued delivery, drip sequence scheduling, event tracking, and proper unsubscribe handling.

Architecture

Trigger (signup, trial start, payment)
    │
    ▼
Email Queue (BullMQ/Redis)
    │
    ├── Immediate: transactional (password reset, receipt)
    └── Scheduled: sequences (onboarding day 1, 3, 7, 14)
            │
            ▼
        Email Worker
            ├── Render template (React Email → HTML)
            ├── Check suppression list (unsubscribed, bounced)
            ├── Send via provider (Resend / SES / SendGrid)
            └── Record event (sent, delivered, opened, clicked)

---

1. Database Schema

-- Email contacts with suppression state
CREATE TABLE email_contacts (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID        NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  user_id         UUID        REFERENCES users(id) ON DELETE SET NULL,
  email           TEXT        NOT NULL,
  name            TEXT,
  
  -- Suppression
  status          TEXT        NOT NULL DEFAULT 'subscribed'
                              CHECK (status IN ('subscribed', 'unsubscribed', 'bounced', 'complained')),
  unsubscribed_at TIMESTAMPTZ,
  bounced_at      TIMESTAMPTZ,
  complained_at   TIMESTAMPTZ,
  
  -- Preferences
  categories      TEXT[]      NOT NULL DEFAULT ARRAY['transactional', 'marketing'],
  
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  
  UNIQUE (tenant_id, email)
);

-- Email send log (append-only)
CREATE TABLE email_sends (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID        NOT NULL,
  contact_id      UUID        NOT NULL REFERENCES email_contacts(id),
  
  template_id     TEXT        NOT NULL,   -- 'onboarding-day-1', 'trial-expiring', etc.
  sequence_id     TEXT,                   -- NULL for transactional
  subject         TEXT        NOT NULL,
  
  provider_id     TEXT,                   -- Provider message ID (for tracking)
  status          TEXT        NOT NULL DEFAULT 'queued'
                              CHECK (status IN ('queued', 'sent', 'delivered', 'bounced', 'failed')),
  
  metadata        JSONB       NOT NULL DEFAULT '{}',
  
  queued_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  sent_at         TIMESTAMPTZ,
  delivered_at    TIMESTAMPTZ,
  failed_at       TIMESTAMPTZ,
  failure_reason  TEXT,
  
  INDEX idx_email_sends_contact (contact_id, queued_at DESC),
  INDEX idx_email_sends_sequence (sequence_id, template_id, contact_id)
);

-- Email events (opens, clicks, unsubscribes)
CREATE TABLE email_events (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  send_id     UUID        NOT NULL REFERENCES email_sends(id),
  event_type  TEXT        NOT NULL CHECK (event_type IN ('opened', 'clicked', 'unsubscribed', 'bounced', 'complained')),
  url         TEXT,       -- For click events
  user_agent  TEXT,
  ip          INET,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  
  INDEX idx_email_events_send (send_id, occurred_at DESC),
  INDEX idx_email_events_type (event_type, occurred_at DESC)
);

---

2. React Email Templates

npm install @react-email/components react-email

// src/emails/templates/OnboardingDay1.tsx
import {
  Body,
  Button,
  Container,
  Head,
  Heading,
  Html,
  Img,
  Link,
  Preview,
  Section,
  Text,
  Tailwind,
} from '@react-email/components';

interface OnboardingDay1Props {
  userName: string;
  accountName: string;
  dashboardUrl: string;
  unsubscribeUrl: string;
}

export function OnboardingDay1({
  userName,
  accountName,
  dashboardUrl,
  unsubscribeUrl,
}: OnboardingDay1Props) {
  return (
    <Html>
      <Head />
      <Preview>Welcome to Viprasol, {userName} — here's how to get started</Preview>
      <Tailwind>
        <Body className="bg-gray-50 font-sans">
          <Container className="mx-auto max-w-xl p-6">
            <Img
              src="https://viprasol.com/images/logo.png"
              width={120}
              height={40}
              alt="Viprasol"
              className="mb-8"
            />

            <Heading className="text-2xl font-bold text-gray-900 mb-4">
              Welcome to Viprasol, {userName}!
            </Heading>

            <Text className="text-gray-600 mb-4">
              Your {accountName} workspace is ready. Here's what to do first:
            </Text>

            <Section className="bg-white rounded-lg p-6 mb-6 border border-gray-200">
              <Text className="font-semibold text-gray-900 mb-2">✅ Step 1: Connect your data</Text>
              <Text className="text-gray-600 text-sm mb-4">
                Import your existing data or connect via our API to get started immediately.
              </Text>
              <Text className="font-semibold text-gray-900 mb-2">✅ Step 2: Invite your team</Text>
              <Text className="text-gray-600 text-sm">
                Collaboration is free — add teammates from Settings → Team.
              </Text>
            </Section>

            <Button
              href={dashboardUrl}
              className="bg-blue-600 text-white rounded-lg px-6 py-3 font-semibold text-base no-underline block text-center"
            >
              Go to your dashboard →
            </Button>

            <Text className="text-gray-400 text-xs mt-8 text-center">
              You're receiving this because you created a Viprasol account.{' '}
              <Link href={unsubscribeUrl} className="text-gray-400 underline">
                Unsubscribe
              </Link>
            </Text>
          </Container>
        </Body>
      </Tailwind>
    </Html>
  );
}

// Default export for React Email preview server
export default OnboardingDay1;

Template Registry

// src/emails/registry.ts
import { render } from '@react-email/components';
import { OnboardingDay1 } from './templates/OnboardingDay1';
import { TrialExpiring } from './templates/TrialExpiring';
import { PaymentFailed } from './templates/PaymentFailed';
import { PasswordReset } from './templates/PasswordReset';

export type TemplateId =
  | 'onboarding-day-1'
  | 'onboarding-day-3'
  | 'onboarding-day-7'
  | 'trial-expiring-7d'
  | 'trial-expiring-1d'
  | 'payment-failed-first'
  | 'payment-failed-final'
  | 'password-reset';

interface TemplateConfig<T extends object> {
  subject: (props: T) => string;
  component: (props: T) => JSX.Element;
  category: 'transactional' | 'marketing';
}

// Templates with their subject line generators
const TEMPLATES: Record<TemplateId, TemplateConfig<any>> = {
  'onboarding-day-1': {
    subject: ({ userName }: { userName: string }) => `Welcome to Viprasol, ${userName}!`,
    component: OnboardingDay1,
    category: 'marketing',
  },
  'trial-expiring-7d': {
    subject: () => 'Your trial ends in 7 days',
    component: TrialExpiring,
    category: 'transactional',
  },
  'payment-failed-first': {
    subject: () => 'Action required: payment failed',
    component: PaymentFailed,
    category: 'transactional',
  },
  'password-reset': {
    subject: () => 'Reset your Viprasol password',
    component: PasswordReset,
    category: 'transactional',
  },
  // ... more templates
} as any;

export async function renderTemplate(
  templateId: TemplateId,
  props: object
): Promise<{ html: string; text: string; subject: string }> {
  const template = TEMPLATES[templateId];
  if (!template) throw new Error(`Unknown template: ${templateId}`);

  const element = template.component(props);
  const html = await render(element);
  const text = await render(element, { plainText: true });
  const subject = template.subject(props);

  return { html, text, subject };
}

export function getTemplateCategory(templateId: TemplateId): 'transactional' | 'marketing' {
  return TEMPLATES[templateId]?.category ?? 'marketing';
}

---

3. Email Queue with BullMQ

// src/lib/email/queue.ts
import { Queue, Worker, Job } from 'bullmq';
import { redis } from '../redis';
import { renderTemplate, TemplateId, getTemplateCategory } from '../../emails/registry';
import { db } from '../db';
import { resend } from './providers/resend';

export interface EmailJobData {
  sendId: string;          // DB record ID
  contactId: string;
  tenantId: string;
  templateId: TemplateId;
  props: Record<string, unknown>;
  scheduledFor?: string;   // ISO string for delayed sends
}

export const emailQueue = new Queue<EmailJobData>('email', {
  connection: redis,
  defaultJobOptions: {
    attempts: 3,
    backoff: { type: 'exponential', delay: 60_000 }, // 1min, 2min, 4min
    removeOnComplete: { count: 1000 },
    removeOnFail: { count: 5000 },
  },
});

// Add a single email to the queue
export async function queueEmail(
  input: Omit<EmailJobData, 'sendId'> & { delay?: number }
): Promise<string> {
  const send = await db.emailSend.create({
    data: {
      tenantId: input.tenantId,
      contactId: input.contactId,
      templateId: input.templateId,
      sequenceId: (input.props.sequenceId as string) ?? null,
      subject: '',  // Will be populated by worker
      status: 'queued',
    },
  });

  await emailQueue.add(
    `email:${send.id}`,
    { ...input, sendId: send.id },
    {
      delay: input.delay,
      jobId: send.id,  // Idempotency: same sendId = same job
    }
  );

  return send.id;
}

Email Worker

// src/workers/email.worker.ts
import { Worker, Job } from 'bullmq';
import { redis } from '../lib/redis';
import { EmailJobData } from '../lib/email/queue';
import { renderTemplate, getTemplateCategory } from '../emails/registry';
import { db } from '../lib/db';
import { resend } from '../lib/email/providers/resend';

const worker = new Worker<EmailJobData>(
  'email',
  async (job: Job<EmailJobData>) => {
    const { sendId, contactId, tenantId, templateId, props } = job.data;

    // 1. Get contact and check suppression
    const contact = await db.emailContact.findUnique({
      where: { id: contactId },
    });

    if (!contact) {
      throw new Error(`Contact ${contactId} not found`);
    }

    const category = getTemplateCategory(templateId);

    if (contact.status !== 'subscribed') {
      console.info(`Skipping ${templateId} for ${contact.email}: status=${contact.status}`);
      await db.emailSend.update({
        where: { id: sendId },
        data: { status: 'failed', failureReason: `Suppressed: ${contact.status}` },
      });
      return; // Not an error — don't retry
    }

    if (!contact.categories.includes(category)) {
      console.info(`Skipping ${templateId} for ${contact.email}: category=${category} not in preferences`);
      await db.emailSend.update({
        where: { id: sendId },
        data: { status: 'failed', failureReason: 'Category unsubscribed' },
      });
      return;
    }

    // 2. Render template with unsubscribe URL injected
    const unsubscribeToken = generateUnsubscribeToken(contactId);
    const { html, text, subject } = await renderTemplate(templateId, {
      ...props,
      unsubscribeUrl: `${process.env.APP_URL}/unsubscribe?token=${unsubscribeToken}`,
    });

    // 3. Update subject in DB
    await db.emailSend.update({
      where: { id: sendId },
      data: { subject },
    });

    // 4. Send via provider
    const { id: providerId } = await resend.emails.send({
      from: 'Viprasol <hello@viprasol.com>',
      to: contact.email,
      subject,
      html,
      text,
      headers: {
        'List-Unsubscribe': `<${process.env.APP_URL}/unsubscribe?token=${unsubscribeToken}>`,
        'X-Send-Id': sendId,
      },
    });

    // 5. Mark as sent
    await db.emailSend.update({
      where: { id: sendId },
      data: { status: 'sent', sentAt: new Date(), providerId },
    });

    console.info(`Sent ${templateId} to ${contact.email} (${providerId})`);
  },
  {
    connection: redis,
    concurrency: 10,  // 10 concurrent sends
  }
);

worker.on('failed', async (job, err) => {
  if (!job) return;
  await db.emailSend.update({
    where: { id: job.data.sendId },
    data: {
      status: 'failed',
      failedAt: new Date(),
      failureReason: err.message,
    },
  });
});

---

4. Drip Sequence Scheduling

// src/lib/email/sequences.ts
import { queueEmail } from './queue';

interface SequenceStep {
  templateId: string;
  delayDays: number;
  condition?: (contact: EmailContact) => boolean;
}

const SEQUENCES: Record<string, SequenceStep[]> = {
  'onboarding': [
    { templateId: 'onboarding-day-1', delayDays: 0 },
    { templateId: 'onboarding-day-3', delayDays: 3 },
    { templateId: 'onboarding-day-7', delayDays: 7,
      condition: (c) => !c.hasActivated }, // Skip if user already activated
    { templateId: 'onboarding-day-14', delayDays: 14,
      condition: (c) => !c.hasActivated },
  ],
  'trial': [
    { templateId: 'trial-expiring-7d', delayDays: -7 },  // 7 days before trial ends
    { templateId: 'trial-expiring-1d', delayDays: -1 },
  ],
};

export async function enrollInSequence(
  contactId: string,
  tenantId: string,
  sequenceId: keyof typeof SEQUENCES,
  baseDate: Date = new Date(),
  props: Record<string, unknown> = {}
): Promise<void> {
  const steps = SEQUENCES[sequenceId];
  if (!steps) throw new Error(`Unknown sequence: ${sequenceId}`);

  for (const step of steps) {
    const scheduledFor = new Date(baseDate);
    scheduledFor.setDate(scheduledFor.getDate() + step.delayDays);

    const delayMs = scheduledFor.getTime() - Date.now();

    // Skip if scheduled in the past
    if (delayMs < -60_000) continue;

    await queueEmail({
      contactId,
      tenantId,
      templateId: step.templateId as any,
      props: { ...props, sequenceId },
      delay: Math.max(0, delayMs),
    });
  }

  console.info(
    `Enrolled contact ${contactId} in sequence ${sequenceId} (${steps.length} emails)`
  );
}

// Cancel remaining sequence emails
export async function cancelSequence(
  contactId: string,
  sequenceId: string
): Promise<void> {
  const pendingSends = await db.emailSend.findMany({
    where: {
      contactId,
      sequenceId,
      status: 'queued',
    },
  });

  for (const send of pendingSends) {
    await emailQueue.remove(send.id);
    await db.emailSend.update({
      where: { id: send.id },
      data: { status: 'failed', failureReason: 'Sequence cancelled' },
    });
  }
}

// Usage: enroll after signup
// await enrollInSequence(contactId, tenantId, 'onboarding', new Date(), {
//   userName: user.name,
//   accountName: account.name,
//   dashboardUrl: `${APP_URL}/dashboard`,
// });

---

5. Unsubscribe Handling

// src/app/api/unsubscribe/route.ts
import crypto from 'crypto';
import { NextRequest, NextResponse } from 'next/server';
import { db } from '../../../lib/db';

const UNSUBSCRIBE_SECRET = process.env.UNSUBSCRIBE_HMAC_SECRET!;

export function generateUnsubscribeToken(contactId: string): string {
  return crypto
    .createHmac('sha256', UNSUBSCRIBE_SECRET)
    .update(contactId)
    .digest('hex');
}

export async function GET(req: NextRequest) {
  const token = req.nextUrl.searchParams.get('token');
  if (!token) {
    return NextResponse.json({ error: 'Missing token' }, { status: 400 });
  }

  // Find contact by verifying token against all contacts (or store token in DB)
  // Efficient: store token hash in DB at enrollment time
  const contact = await db.emailContact.findFirst({
    where: {
      unsubscribeToken: token,
      status: 'subscribed',
    },
  });

  if (!contact) {
    return NextResponse.redirect(`${process.env.APP_URL}/unsubscribe/invalid`);
  }

  await db.emailContact.update({
    where: { id: contact.id },
    data: {
      status: 'unsubscribed',
      unsubscribedAt: new Date(),
    },
  });

  // Cancel any pending sequence emails
  await db.emailSend.updateMany({
    where: { contactId: contact.id, status: 'queued' },
    data: { status: 'failed', failureReason: 'Unsubscribed' },
  });

  return NextResponse.redirect(`${process.env.APP_URL}/unsubscribe/confirmed`);
}

---

Cost Reference

---

See Also

• SaaS Customer Success Engineering: Health Scores and Churn Signals
• SaaS Trial Conversion: Onboarding Sequences and Feature Gates
• SaaS Dunning Management: Recovering Failed Payments
• Background Jobs Architecture with BullMQ
• SaaS Audit Logging: Immutable Trails and Compliance

---

Working With Viprasol

Building a SaaS product that needs onboarding sequences, trial conversion emails, and dunning flows that actually work — with proper unsubscribe handling and CAN-SPAM compliance? We design and implement the full email infrastructure stack: React Email templates, BullMQ queues, drip sequence scheduling, and delivery analytics.

Talk to our team → | Explore our SaaS engineering services →