How to Build a Email Automation with Lovable

1Create a complete email automation schema in Supabase.
2
3Tables:
4
51. contacts:
6   id uuid primary key default gen_random_uuid()
7   email text not null unique
8   first_name text
9   last_name text
10   company text
11   tags text[] default '{}'
12   status text default 'subscribed' — 'subscribed', 'unsubscribed', 'bounced', 'complained'
13   source text
14   metadata jsonb default '{}'
15   subscribed_at timestamptz default now()
16   unsubscribed_at timestamptz
17   created_at timestamptz default now()
18
192. email_templates:
20   id uuid primary key default gen_random_uuid()
21   name text not null
22   subject text not null
23   html_body text not null — supports {{variable}} substitution
24   text_body text
25   preview_text text
26   from_name text default 'Your Company'
27   from_email text default 'hello@yourcompany.com'
28   created_at timestamptz default now()
29   updated_at timestamptz default now()
30
313. campaigns:
32   id uuid primary key default gen_random_uuid()
33   name text not null
34   description text
35   status text default 'draft' — 'draft', 'active', 'paused', 'archived'
36   trigger_type text default 'manual' — 'manual', 'signup', 'tag'
37   trigger_config jsonb default '{}' — for tag triggers: { tag: 'lead' }
38   steps jsonb not null default '[]' — array of { day: int, template_id: uuid, subject_override?: string }
39   created_at timestamptz default now()
40   updated_at timestamptz default now()
41
424. contact_enrollments:
43   id uuid primary key default gen_random_uuid()
44   contact_id uuid references contacts(id) on delete cascade
45   campaign_id uuid references campaigns(id) on delete cascade
46   unique(contact_id, campaign_id)
47   enrolled_at timestamptz default now()
48   current_step int default 0
49   completed_at timestamptz
50   unenrolled_at timestamptz
51   status text default 'active' — 'active', 'completed', 'unenrolled'
52
535. send_logs:
54   id uuid primary key default gen_random_uuid()
55   contact_id uuid references contacts(id)
56   campaign_id uuid references campaigns(id)
57   template_id uuid references email_templates(id)
58   enrollment_id uuid references contact_enrollments(id)
59   step_index int
60   to_email text not null
61   subject text not null
62   status text default 'pending' — 'pending', 'sent', 'failed', 'bounced', 'complained'
63   resend_message_id text
64   error_message text
65   opened_at timestamptz
66   clicked_at timestamptz
67   sent_at timestamptz default now()
68
69RLS:
70- All tables: authenticated users can read all rows
71- contacts: authenticated INSERT/UPDATE/DELETE
72- email_templates: authenticated INSERT/UPDATE/DELETE
73- campaigns: authenticated INSERT/UPDATE/DELETE
74- contact_enrollments: authenticated INSERT/UPDATE/DELETE
75- send_logs: authenticated INSERT; no DELETE (audit trail)
76
77Create indexes:
78- contacts(email), contacts(status), contacts(tags using GIN)
79- contact_enrollments(contact_id, campaign_id), contact_enrollments(status)
80- send_logs(contact_id), send_logs(campaign_id), send_logs(sent_at)

1Create a Supabase Edge Function at supabase/functions/send-email/index.ts.
2
3The function accepts POST requests with body:
4{ enrollment_id: string, step_index: number }
5
6Logic:
71. Fetch the enrollment row with contact and campaign JOINed
82. Get the step from campaign.steps[step_index]
93. Fetch the email template by template_id
104. Render the template: replace all {{variable}} occurrences with contact fields
11   - Available variables: {{first_name}}, {{last_name}}, {{email}}, {{company}}, plus any contact.metadata keys
12   - Use a simple regex replace: template.html_body.replace(/\{\{(\w+)\}\}/g, (_, key) => contactVars[key] ?? '')
135. Create a send_logs row with status = 'pending'
146. Call the Resend API:
15   POST https://api.resend.com/emails
16   Headers: Authorization: Bearer RESEND_API_KEY
17   Body: { from: template.from_email, to: contact.email, subject, html: renderedHtml, text: renderedText }
187. On success: update send_logs.status = 'sent', resend_message_id = response.id, sent_at = now()
198. Update contact_enrollments.current_step = step_index + 1
209. Check if this was the last step: if step_index + 1 >= campaign.steps.length, set enrollment.status = 'completed'
2110. On failure: update send_logs.status = 'failed', error_message = error.message
2211. Return { success: true, message_id } or { success: false, error }
23
24Always return 200 even on send failures — the pg_cron job should not retry individual sends.

1// SQL to run in Supabase Dashboard → SQL Editor
2// Step 1: Enable pg_cron extension (if not already enabled)
3-- Do this in Dashboard → Database → Extensions → enable pg_cron
4
5-- Step 2: Create a function that finds due steps and calls the Edge Function
6CREATE OR REPLACE FUNCTION process_due_email_steps()
7RETURNS void
8LANGUAGE plpgsql
9SECURITY DEFINER
10AS $$
11DECLARE
12  enrollment RECORD;
13  step_day integer;
14  step_template_id uuid;
15  send_at timestamptz;
16BEGIN
17  FOR enrollment IN
18    SELECT
19      ce.id AS enrollment_id,
20      ce.contact_id,
21      ce.campaign_id,
22      ce.enrolled_at,
23      ce.current_step,
24      c.steps
25    FROM contact_enrollments ce
26    JOIN campaigns c ON c.id = ce.campaign_id
27    WHERE ce.status = 'active'
28      AND c.status = 'active'
29      AND ce.current_step < jsonb_array_length(c.steps)
30  LOOP
31    step_day := (enrollment.steps -> enrollment.current_step ->> 'day')::integer;
32    send_at := enrollment.enrolled_at + (step_day || ' days')::interval;
33
34    IF send_at <= now() THEN
35      -- Check not already sent (unique constraint on enrollment_id, step_index prevents duplicates)
36      IF NOT EXISTS (
37        SELECT 1 FROM send_logs
38        WHERE enrollment_id = enrollment.enrollment_id
39          AND step_index = enrollment.current_step
40          AND status IN ('sent', 'pending')
41      ) THEN
42        PERFORM net.http_post(
43          url := current_setting('app.edge_function_url') || '/send-email',
44          headers := jsonb_build_object(
45            'Content-Type', 'application/json',
46            'Authorization', 'Bearer ' || current_setting('app.service_role_key')
47          ),
48          body := jsonb_build_object(
49            'enrollment_id', enrollment.enrollment_id,
50            'step_index', enrollment.current_step
51          )
52        );
53      END IF;
54    END IF;
55  END LOOP;
56END;
57$$;
58
59-- Step 3: Register the cron job (runs every hour)
60SELECT cron.schedule(
61  'process-email-sequences',
62  '0 * * * *',
63  'SELECT process_due_email_steps()'
64);

1Build a campaign management section with three pages.
2
31. src/pages/Campaigns.tsx — Campaign list:
4   - DataTable showing all campaigns with columns: Name, Status Badge, Steps count, Active Enrollments count, Created
5   - 'New Campaign' Button opens a Dialog
6   - Dialog: Campaign name, description, status Select
7   - Clicking a campaign row navigates to /campaigns/:id
8
92. src/pages/CampaignDetail.tsx — Campaign editor:
10   - Tabs: 'Sequence', 'Enrollments', 'Analytics'
11   - 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
12   - 'Add Step' button appends a new step object to the steps array
13   - Steps are sortable (drag-to-reorder using a simple up/down button approach)
14   - Save button persists steps as JSONB to campaigns.steps
15   - Enrollments tab: DataTable of contacts enrolled in this campaign with their current_step and status
16   - 'Enroll Contacts' button opens a Dialog with a searchable contact list and multi-select Checkboxes
17
183. src/pages/Templates.tsx — Template editor:
19   - List of templates on the left
20   - Selecting a template shows an editor on the right
21   - Fields: name, subject, from_name, from_email, preview_text
22   - html_body: a Textarea with monospace font and a 'Preview' Button
23   - Preview Dialog renders the HTML with sample variable values substituted
24   - Available variables shown as Badges below the editor: {{first_name}}, {{last_name}}, {{email}}, {{company}}

1// src/components/CampaignAnalytics.tsx
2import { useEffect, useState } from 'react'
3import { supabase } from '@/integrations/supabase/client'
4import { BarChart, Bar, LineChart, Line, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer } from 'recharts'
5import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card'
6
7type StepStats = {
8  step_index: number
9  sent: number
10  opened: number
11  clicked: number
12  failed: number
13}
14
15type DayStats = {
16  date: string
17  sent: number
18  opened: number
19}
20
21export function CampaignAnalytics({ campaignId }: { campaignId: string }) {
22  const [stepStats, setStepStats] = useState<StepStats[]>([])
23  const [dayStats, setDayStats] = useState<DayStats[]>([])
24  const [totals, setTotals] = useState({ sent: 0, opened: 0, clicked: 0, failed: 0 })
25
26  useEffect(() => {
27    async function load() {
28      const { data } = await supabase
29        .from('send_logs')
30        .select('step_index, status, opened_at, clicked_at, sent_at')
31        .eq('campaign_id', campaignId)
32
33      if (!data) return
34
35      const byStep = data.reduce<Record<number, StepStats>>((acc, row) => {
36        const s = acc[row.step_index] ?? { step_index: row.step_index, sent: 0, opened: 0, clicked: 0, failed: 0 }
37        if (row.status === 'sent') s.sent++
38        if (row.opened_at) s.opened++
39        if (row.clicked_at) s.clicked++
40        if (row.status === 'failed') s.failed++
41        acc[row.step_index] = s
42        return acc
43      }, {})
44
45      setStepStats(Object.values(byStep).sort((a, b) => a.step_index - b.step_index))
46      setTotals(data.reduce((acc, row) => ({
47        sent: acc.sent + (row.status === 'sent' ? 1 : 0),
48        opened: acc.opened + (row.opened_at ? 1 : 0),
49        clicked: acc.clicked + (row.clicked_at ? 1 : 0),
50        failed: acc.failed + (row.status === 'failed' ? 1 : 0),
51      }), { sent: 0, opened: 0, clicked: 0, failed: 0 }))
52    }
53    load()
54  }, [campaignId])
55
56  const openRate = totals.sent > 0 ? ((totals.opened / totals.sent) * 100).toFixed(1) : '0'
57
58  return (
59    <div className='grid grid-cols-2 gap-4'>
60      <Card><CardHeader><CardTitle>Open Rate</CardTitle></CardHeader>
61        <CardContent><p className='text-3xl font-bold'>{openRate}%</p></CardContent>
62      </Card>
63      <Card><CardHeader><CardTitle>Total Sent</CardTitle></CardHeader>
64        <CardContent><p className='text-3xl font-bold'>{totals.sent.toLocaleString()}</p></CardContent>
65      </Card>
66      <Card className='col-span-2'>
67        <CardHeader><CardTitle>Sends by Step</CardTitle></CardHeader>
68        <CardContent>
69          <ResponsiveContainer width='100%' height={200}>
70            <BarChart data={stepStats}>
71              <CartesianGrid strokeDasharray='3 3' />
72              <XAxis dataKey='step_index' tickFormatter={(v) => `Step ${v + 1}`} />
73              <YAxis />
74              <Tooltip />
75              <Bar dataKey='sent' fill='#6366f1' name='Sent' />
76              <Bar dataKey='opened' fill='#22c55e' name='Opened' />
77            </BarChart>
78          </ResponsiveContainer>
79        </CardContent>
80      </Card>
81    </div>
82  )
83}