# How to Build an Email Automation with Lovable
- Tool: How to Build with Lovable
- Difficulty: Advanced
- Compatibility: Lovable Pro or higher
- Last updated: April 2026
## TL;DR
Build a drip email automation system in Lovable with contacts, campaigns, and message templates stored in Supabase. An Edge Function sends emails via Resend on demand. A pg_cron job runs every hour to find contacts due for the next sequence step and triggers the send function — fully automated without any external queue service.
## Before you start
- Resend account with a verified sending domain and API key (free tier sends 3,000 emails/month)
- RESEND_API_KEY saved in Cloud tab → Secrets
- pg_cron enabled in your Supabase project (Dashboard → Database → Extensions → pg_cron)
- Supabase Auth for the admin dashboard login
- Basic understanding of email deliverability concepts (SPF, DKIM) for production use
## Step-by-step guide
### 1. Create the email automation schema
Build the complete database schema for contacts, campaigns, templates, enrollments, and send logs in one prompt. Getting the schema right before building the UI saves significant rework.
```
Create a complete email automation schema in Supabase.
Tables:
1. contacts:
id uuid primary key default gen_random_uuid()
email text not null unique
first_name text
last_name text
company text
tags text[] default '{}'
status text default 'subscribed' — 'subscribed', 'unsubscribed', 'bounced', 'complained'
source text
metadata jsonb default '{}'
subscribed_at timestamptz default now()
unsubscribed_at timestamptz
created_at timestamptz default now()
2. email_templates:
id uuid primary key default gen_random_uuid()
name text not null
subject text not null
html_body text not null — supports {{variable}} substitution
text_body text
preview_text text
from_name text default 'Your Company'
from_email text default 'hello@yourcompany.com'
created_at timestamptz default now()
updated_at timestamptz default now()
3. campaigns:
id uuid primary key default gen_random_uuid()
name text not null
description text
status text default 'draft' — 'draft', 'active', 'paused', 'archived'
trigger_type text default 'manual' — 'manual', 'signup', 'tag'
trigger_config jsonb default '{}' — for tag triggers: { tag: 'lead' }
steps jsonb not null default '[]' — array of { day: int, template_id: uuid, subject_override?: string }
created_at timestamptz default now()
updated_at timestamptz default now()
4. contact_enrollments:
id uuid primary key default gen_random_uuid()
contact_id uuid references contacts(id) on delete cascade
campaign_id uuid references campaigns(id) on delete cascade
unique(contact_id, campaign_id)
enrolled_at timestamptz default now()
current_step int default 0
completed_at timestamptz
unenrolled_at timestamptz
status text default 'active' — 'active', 'completed', 'unenrolled'
5. send_logs:
id uuid primary key default gen_random_uuid()
contact_id uuid references contacts(id)
campaign_id uuid references campaigns(id)
template_id uuid references email_templates(id)
enrollment_id uuid references contact_enrollments(id)
step_index int
to_email text not null
subject text not null
status text default 'pending' — 'pending', 'sent', 'failed', 'bounced', 'complained'
resend_message_id text
error_message text
opened_at timestamptz
clicked_at timestamptz
sent_at timestamptz default now()
RLS:
- All tables: authenticated users can read all rows
- contacts: authenticated INSERT/UPDATE/DELETE
- email_templates: authenticated INSERT/UPDATE/DELETE
- campaigns: authenticated INSERT/UPDATE/DELETE
- contact_enrollments: authenticated INSERT/UPDATE/DELETE
- send_logs: authenticated INSERT; no DELETE (audit trail)
Create indexes:
- contacts(email), contacts(status), contacts(tags using GIN)
- contact_enrollments(contact_id, campaign_id), contact_enrollments(status)
- send_logs(contact_id), send_logs(campaign_id), send_logs(sent_at)
```
> Pro tip: Add a unique constraint on send_logs(enrollment_id, step_index) to prevent duplicate sends if the pg_cron job runs while a previous send is still in progress. The INSERT will fail gracefully instead of sending the same email twice.
**Expected result:** All five tables created with indexes and RLS. TypeScript types generated. The app shell renders in the preview.
### 2. Build the email send Edge Function
Create the core Edge Function that renders a template with contact variables and sends it via Resend. This function is called both manually and by the pg_cron scheduler.
```
Create a Supabase Edge Function at supabase/functions/send-email/index.ts.
The function accepts POST requests with body:
{ enrollment_id: string, step_index: number }
Logic:
1. Fetch the enrollment row with contact and campaign JOINed
2. Get the step from campaign.steps[step_index]
3. Fetch the email template by template_id
4. Render the template: replace all {{variable}} occurrences with contact fields
- Available variables: {{first_name}}, {{last_name}}, {{email}}, {{company}}, plus any contact.metadata keys
- Use a simple regex replace: template.html_body.replace(/\{\{(\w+)\}\}/g, (_, key) => contactVars[key] ?? '')
5. Create a send_logs row with status = 'pending'
6. Call the Resend API:
POST https://api.resend.com/emails
Headers: Authorization: Bearer RESEND_API_KEY
Body: { from: template.from_email, to: contact.email, subject, html: renderedHtml, text: renderedText }
7. On success: update send_logs.status = 'sent', resend_message_id = response.id, sent_at = now()
8. Update contact_enrollments.current_step = step_index + 1
9. Check if this was the last step: if step_index + 1 >= campaign.steps.length, set enrollment.status = 'completed'
10. On failure: update send_logs.status = 'failed', error_message = error.message
11. Return { success: true, message_id } or { success: false, error }
Always return 200 even on send failures — the pg_cron job should not retry individual sends.
```
> Pro tip: Add an unsubscribe link to every email automatically in this function. Append a line to the HTML body: '
'. Create an /unsubscribe route in your app that sets contacts.status = 'unsubscribed'.
**Expected result:** The Edge Function deploys. Calling it with a valid enrollment_id and step_index sends an email via Resend and creates a send_logs row with status='sent'.
### 3. Set up pg_cron for automated sequence processing
Configure a PostgreSQL cron job that runs hourly, finds all due enrollment steps, and triggers the send Edge Function for each one. This is the automation engine.
```
// SQL to run in Supabase Dashboard → SQL Editor
// Step 1: Enable pg_cron extension (if not already enabled)
-- Do this in Dashboard → Database → Extensions → enable pg_cron
-- Step 2: Create a function that finds due steps and calls the Edge Function
CREATE OR REPLACE FUNCTION process_due_email_steps()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
enrollment RECORD;
step_day integer;
step_template_id uuid;
send_at timestamptz;
BEGIN
FOR enrollment IN
SELECT
ce.id AS enrollment_id,
ce.contact_id,
ce.campaign_id,
ce.enrolled_at,
ce.current_step,
c.steps
FROM contact_enrollments ce
JOIN campaigns c ON c.id = ce.campaign_id
WHERE ce.status = 'active'
AND c.status = 'active'
AND ce.current_step < jsonb_array_length(c.steps)
LOOP
step_day := (enrollment.steps -> enrollment.current_step ->> 'day')::integer;
send_at := enrollment.enrolled_at + (step_day || ' days')::interval;
IF send_at <= now() THEN
-- Check not already sent (unique constraint on enrollment_id, step_index prevents duplicates)
IF NOT EXISTS (
SELECT 1 FROM send_logs
WHERE enrollment_id = enrollment.enrollment_id
AND step_index = enrollment.current_step
AND status IN ('sent', 'pending')
) THEN
PERFORM net.http_post(
url := current_setting('app.edge_function_url') || '/send-email',
headers := jsonb_build_object(
'Content-Type', 'application/json',
'Authorization', 'Bearer ' || current_setting('app.service_role_key')
),
body := jsonb_build_object(
'enrollment_id', enrollment.enrollment_id,
'step_index', enrollment.current_step
)
);
END IF;
END IF;
END LOOP;
END;
$$;
-- Step 3: Register the cron job (runs every hour)
SELECT cron.schedule(
'process-email-sequences',
'0 * * * *',
'SELECT process_due_email_steps()'
);
```
> Pro tip: Store the Edge Function base URL and service role key as PostgreSQL settings: SELECT set_config('app.edge_function_url', 'https://YOUR_PROJECT.supabase.co/functions/v1', true). This avoids hardcoding them in the function body.
**Expected result:** The pg_cron job is registered and visible in Supabase Dashboard → Database → Cron Jobs. The process_due_email_steps() function exists. Running it manually triggers sends for any due enrollment steps.
### 4. Build the campaign builder and template editor
Create the campaign management UI where admins build drip sequences, write email templates with variable preview, and enroll contacts in campaigns.
```
Build a campaign management section with three pages.
1. src/pages/Campaigns.tsx — Campaign list:
- DataTable showing all campaigns with columns: Name, Status Badge, Steps count, Active Enrollments count, Created
- 'New Campaign' Button opens a Dialog
- Dialog: Campaign name, description, status Select
- Clicking a campaign row navigates to /campaigns/:id
2. src/pages/CampaignDetail.tsx — Campaign editor:
- Tabs: 'Sequence', 'Enrollments', 'Analytics'
- Sequence tab: shows the steps in order. Each step is a Card with: Day number Input, Template selector (Select with all templates), a remove button
- 'Add Step' button appends a new step object to the steps array
- Steps are sortable (drag-to-reorder using a simple up/down button approach)
- Save button persists steps as JSONB to campaigns.steps
- Enrollments tab: DataTable of contacts enrolled in this campaign with their current_step and status
- 'Enroll Contacts' button opens a Dialog with a searchable contact list and multi-select Checkboxes
3. src/pages/Templates.tsx — Template editor:
- List of templates on the left
- Selecting a template shows an editor on the right
- Fields: name, subject, from_name, from_email, preview_text
- html_body: a Textarea with monospace font and a 'Preview' Button
- Preview Dialog renders the HTML with sample variable values substituted
- Available variables shown as Badges below the editor: {{first_name}}, {{last_name}}, {{email}}, {{company}}
```
> Pro tip: In the template preview, substitute real values from the first contact in your database. This gives a more realistic preview than using 'John Doe' placeholders, and helps catch missing data issues before sending.
**Expected result:** The campaigns list shows all campaigns. The campaign editor lets you build multi-step sequences. The template editor shows a live HTML preview. Enrolling contacts creates contact_enrollment rows.
### 5. Build the campaign analytics dashboard
Add an analytics tab to each campaign showing delivery volume, open rate, and click rate using Recharts charts fed by queries on send_logs.
```
// src/components/CampaignAnalytics.tsx
import { useEffect, useState } from 'react'
import { supabase } from '@/integrations/supabase/client'
import { BarChart, Bar, LineChart, Line, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer } from 'recharts'
import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card'
type StepStats = {
step_index: number
sent: number
opened: number
clicked: number
failed: number
}
type DayStats = {
date: string
sent: number
opened: number
}
export function CampaignAnalytics({ campaignId }: { campaignId: string }) {
const [stepStats, setStepStats] = useState([])
const [dayStats, setDayStats] = useState([])
const [totals, setTotals] = useState({ sent: 0, opened: 0, clicked: 0, failed: 0 })
useEffect(() => {
async function load() {
const { data } = await supabase
.from('send_logs')
.select('step_index, status, opened_at, clicked_at, sent_at')
.eq('campaign_id', campaignId)
if (!data) return
const byStep = data.reduce>((acc, row) => {
const s = acc[row.step_index] ?? { step_index: row.step_index, sent: 0, opened: 0, clicked: 0, failed: 0 }
if (row.status === 'sent') s.sent++
if (row.opened_at) s.opened++
if (row.clicked_at) s.clicked++
if (row.status === 'failed') s.failed++
acc[row.step_index] = s
return acc
}, {})
setStepStats(Object.values(byStep).sort((a, b) => a.step_index - b.step_index))
setTotals(data.reduce((acc, row) => ({
sent: acc.sent + (row.status === 'sent' ? 1 : 0),
opened: acc.opened + (row.opened_at ? 1 : 0),
clicked: acc.clicked + (row.clicked_at ? 1 : 0),
failed: acc.failed + (row.status === 'failed' ? 1 : 0),
}), { sent: 0, opened: 0, clicked: 0, failed: 0 }))
}
load()
}, [campaignId])
const openRate = totals.sent > 0 ? ((totals.opened / totals.sent) * 100).toFixed(1) : '0'
return (
Open Rate
{openRate}%
Total Sent
{totals.sent.toLocaleString()}
Sends by Step `Step ${v + 1}`} />
)
}
```
> Pro tip: Add a Resend webhook endpoint to track open and click events. Resend sends email.opened and email.clicked webhook events with the message ID. Match the message_id to send_logs.resend_message_id and update opened_at and clicked_at.
**Expected result:** The Analytics tab shows open rate, total sent, and a bar chart of sends and opens per step. Stats update when new sends complete.
## Complete code example
File: `src/components/CampaignAnalytics.tsx`
```typescript
import { useEffect, useState } from 'react'
import { supabase } from '@/integrations/supabase/client'
import { BarChart, Bar, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer } from 'recharts'
import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card'
import { Badge } from '@/components/ui/badge'
type StepStats = {
step_index: number
sent: number
opened: number
clicked: number
failed: number
}
type Totals = { sent: number; opened: number; clicked: number; failed: number }
export function CampaignAnalytics({ campaignId }: { campaignId: string }) {
const [stepStats, setStepStats] = useState([])
const [totals, setTotals] = useState({ sent: 0, opened: 0, clicked: 0, failed: 0 })
const [loading, setLoading] = useState(true)
useEffect(() => {
async function load() {
const { data } = await supabase
.from('send_logs')
.select('step_index, status, opened_at, clicked_at')
.eq('campaign_id', campaignId)
.eq('status', 'sent')
if (!data) { setLoading(false); return }
const byStep = data.reduce>((acc, row) => {
const i = row.step_index ?? 0
const s = acc[i] ?? { step_index: i, sent: 0, opened: 0, clicked: 0, failed: 0 }
s.sent++
if (row.opened_at) s.opened++
if (row.clicked_at) s.clicked++
acc[i] = s
return acc
}, {})
const sorted = Object.values(byStep).sort((a, b) => a.step_index - b.step_index)
setStepStats(sorted)
setTotals(sorted.reduce((acc, s) => ({
sent: acc.sent + s.sent,
opened: acc.opened + s.opened,
clicked: acc.clicked + s.clicked,
failed: acc.failed + s.failed,
}), { sent: 0, opened: 0, clicked: 0, failed: 0 }))
setLoading(false)
}
load()
}, [campaignId])
const openRate = totals.sent > 0 ? ((totals.opened / totals.sent) * 100).toFixed(1) : '—'
const clickRate = totals.opened > 0 ? ((totals.clicked / totals.opened) * 100).toFixed(1) : '—'
if (loading) return
return (