How to Build Email automation with V0

1// Paste this prompt into V0's AI chat:
2// Build an email automation system. Create a Supabase schema with:
3// 1. subscribers: id (uuid PK), email (text unique), first_name (text), tags (text[]), status (text default 'active' check in 'active','unsubscribed','bounced'), subscribed_at (timestamptz default now()), unsubscribed_at (timestamptz)
4// 2. campaigns: id (uuid PK), name (text), subject (text), html_body (text), plain_body (text), status (text default 'draft'), scheduled_at (timestamptz), sent_at (timestamptz), created_by (uuid FK to auth.users), created_at (timestamptz)
5// 3. sequences: id (uuid PK), name (text), trigger_event (text), created_by (uuid FK to auth.users), is_active (boolean default true)
6// 4. sequence_steps: id (uuid PK), sequence_id (uuid FK to sequences), step_order (int), delay_hours (int), subject (text), html_body (text), created_at (timestamptz)
7// 5. email_logs: id (uuid PK), subscriber_id (uuid FK), campaign_id (uuid FK nullable), sequence_id (uuid FK nullable), step_order (int), status (text check in 'queued','sent','delivered','opened','clicked','bounced'), sent_at (timestamptz), opened_at (timestamptz)
8// 6. tracking_pixels: id (uuid PK), email_log_id (uuid FK), token (text unique default gen_random_uuid())
9// Add RLS policies for authenticated access.

1// Paste this prompt into V0's AI chat:
2// Build a subscriber management page at app/subscribers/page.tsx.
3// Requirements:
4// - Server Component that fetches subscribers from Supabase with pagination (20 per page)
5// - Display in a shadcn/ui Table with columns: Checkbox for bulk select, email, first_name, tags (as Badge components), status (Badge with color), subscribed_at date
6// - Add filter controls above the table: Select for status filter, Input for search by email, and a tag filter using Command/Popover
7// - Bulk action buttons above the table: "Delete Selected" and "Tag Selected" that appear when checkboxes are checked
8// - An "Import CSV" Button that opens a Dialog with a file upload Input accepting .csv files
9// - The import Server Action parses the CSV, validates emails with Zod, and bulk inserts into subscribers
10// - An "Add Subscriber" Button that opens a Dialog with email Input, first_name Input, and tags Input
11// - Show total subscriber count and active/unsubscribed breakdown as Card stats at the top

1import { NextRequest, NextResponse } from 'next/server'
2import { Resend } from 'resend'
3import { createClient } from '@supabase/supabase-js'
4
5const resend = new Resend(process.env.RESEND_API_KEY)
6const supabase = createClient(
7  process.env.SUPABASE_URL!,
8  process.env.SUPABASE_SERVICE_ROLE_KEY!
9)
10
11export async function POST(req: NextRequest) {
12  const { subscriberIds, subject, htmlBody, campaignId } = await req.json()
13
14  const { data: subscribers } = await supabase
15    .from('subscribers')
16    .select('id, email, first_name')
17    .in('id', subscriberIds)
18    .eq('status', 'active')
19
20  if (!subscribers?.length) {
21    return NextResponse.json({ error: 'No active subscribers' }, { status: 400 })
22  }
23
24  const results = []
25
26  for (const sub of subscribers) {
27    const { data: logEntry } = await supabase
28      .from('email_logs')
29      .insert({
30        subscriber_id: sub.id,
31        campaign_id: campaignId,
32        status: 'queued',
33      })
34      .select()
35      .single()
36
37    const { data: pixel } = await supabase
38      .from('tracking_pixels')
39      .insert({ email_log_id: logEntry!.id })
40      .select('token')
41      .single()
42
43    const personalizedHtml = htmlBody
44      .replace('{{first_name}}', sub.first_name || 'there')
45      + `<img src="${req.nextUrl.origin}/api/track/${pixel!.token}" width="1" height="1" alt="" />`
46
47    const { error } = await resend.emails.send({
48      from: 'newsletter@yourdomain.com',
49      to: sub.email,
50      subject,
51      html: personalizedHtml,
52    })
53
54    await supabase
55      .from('email_logs')
56      .update({ status: error ? 'bounced' : 'sent', sent_at: new Date().toISOString() })
57      .eq('id', logEntry!.id)
58
59    results.push({ email: sub.email, success: !error })
60  }
61
62  return NextResponse.json({ results })
63}

1import { NextRequest, NextResponse } from 'next/server'
2import { createClient } from '@supabase/supabase-js'
3
4const supabase = createClient(
5  process.env.SUPABASE_URL!,
6  process.env.SUPABASE_SERVICE_ROLE_KEY!
7)
8
9const TRANSPARENT_GIF = Buffer.from(
10  'R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7',
11  'base64'
12)
13
14export async function GET(
15  req: NextRequest,
16  { params }: { params: Promise<{ token: string }> }
17) {
18  const { token } = await params
19
20  const { data: pixel } = await supabase
21    .from('tracking_pixels')
22    .select('email_log_id')
23    .eq('token', token)
24    .single()
25
26  if (pixel) {
27    await supabase
28      .from('email_logs')
29      .update({ status: 'opened', opened_at: new Date().toISOString() })
30      .eq('id', pixel.email_log_id)
31      .is('opened_at', null)
32  }
33
34  return new NextResponse(TRANSPARENT_GIF, {
35    headers: {
36      'Content-Type': 'image/gif',
37      'Cache-Control': 'no-store, no-cache, must-revalidate',
38    },
39  })
40}

1import { NextRequest, NextResponse } from 'next/server'
2import { createClient } from '@supabase/supabase-js'
3import { Resend } from 'resend'
4
5const supabase = createClient(
6  process.env.SUPABASE_URL!,
7  process.env.SUPABASE_SERVICE_ROLE_KEY!
8)
9const resend = new Resend(process.env.RESEND_API_KEY)
10
11export async function GET(req: NextRequest) {
12  const authHeader = req.headers.get('authorization')
13  if (authHeader !== `Bearer ${process.env.CRON_SECRET}`) {
14    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
15  }
16
17  const { data: activeSequences } = await supabase
18    .from('sequences')
19    .select('id, sequence_steps(*)')
20    .eq('is_active', true)
21
22  let sent = 0
23
24  for (const seq of activeSequences ?? []) {
25    for (const step of seq.sequence_steps) {
26      const cutoff = new Date(
27        Date.now() - step.delay_hours * 60 * 60 * 1000
28      ).toISOString()
29
30      const { data: eligibleSubs } = await supabase
31        .from('subscribers')
32        .select('id, email, first_name')
33        .eq('status', 'active')
34        .lte('subscribed_at', cutoff)
35        .not('id', 'in', `(SELECT subscriber_id FROM email_logs WHERE sequence_id = '${seq.id}' AND step_order = ${step.step_order})`)
36
37      for (const sub of eligibleSubs ?? []) {
38        await resend.emails.send({
39          from: 'newsletter@yourdomain.com',
40          to: sub.email,
41          subject: step.subject.replace('{{first_name}}', sub.first_name || 'there'),
42          html: step.html_body,
43        })
44
45        await supabase.from('email_logs').insert({
46          subscriber_id: sub.id,
47          sequence_id: seq.id,
48          step_order: step.step_order,
49          status: 'sent',
50          sent_at: new Date().toISOString(),
51        })
52        sent++
53      }
54    }
55  }
56
57  return NextResponse.json({ processed: sent })
58}

1// Paste this prompt into V0's AI chat:
2// Build two pages for the email automation system:
3// 1. app/campaigns/new/page.tsx — a 'use client' campaign editor with:
4//    - Input for campaign name and subject line
5//    - Textarea for HTML email body with placeholder text
6//    - A preview panel (side by side) that renders the HTML in an iframe
7//    - DatePicker for scheduling send time (optional, immediate if not set)
8//    - Tabs to switch between editing subscribers (select by tags using Checkbox list) and the email content
9//    - "Send Now" Button and "Schedule" Button that call Server Actions
10//    - Card wrapper for each section with clear labels
11// 2. app/page.tsx — dashboard with:
12//    - Card stats at top: total subscribers, emails sent today, average open rate, average click rate
13//    - Recharts BarChart showing emails sent per day for the last 30 days
14//    - Table of recent campaigns with columns: name, subject, sent_at, total sent, open rate %, status Badge
15//    - Tabs for switching between Campaigns, Sequences, and Subscribers quick views
16// Use shadcn/ui components throughout.