How to Build a Email Automation with Replit

1// Prompt to type into Replit Agent:
2// Build an email automation system with Express and PostgreSQL using Drizzle ORM.
3// Create these tables in shared/schema.ts:
4// - contacts: id serial pk, email text unique, name text, tags text[],
5//   status text default 'active' (active/unsubscribed/bounced),
6//   source text (import/form/api), subscribed_at timestamp, unsubscribed_at timestamp
7// - email_lists: id serial pk, name text, description text, created_at timestamp
8// - list_contacts: id serial pk, list_id integer references email_lists,
9//   contact_id integer references contacts, added_at timestamp,
10//   UNIQUE on (list_id, contact_id)
11// - campaigns: id serial pk, name text, subject text, from_name text, from_email text,
12//   body_html text, list_id integer references email_lists,
13//   status text default 'draft' (draft/scheduled/sending/sent/cancelled),
14//   scheduled_at timestamp, sent_at timestamp, created_at timestamp
15// - campaign_sends: id serial pk, campaign_id integer references campaigns,
16//   contact_id integer references contacts,
17//   status text default 'pending' (pending/sent/delivered/bounced/failed),
18//   sent_at timestamp, UNIQUE on (campaign_id, contact_id)
19// - drip_sequences: id serial pk, name text, trigger_event text (signup/manual),
20//   list_id integer references email_lists, is_active boolean default true, created_at timestamp
21// - drip_steps: id serial pk, sequence_id integer references drip_sequences,
22//   position integer, delay_hours integer, subject text, body_html text
23// - drip_enrollments: id serial pk, sequence_id integer references drip_sequences,
24//   contact_id integer references contacts, current_step integer default 0,
25//   status text default 'active' (active/completed/paused/unsubscribed),
26//   enrolled_at timestamp, next_send_at timestamp, UNIQUE on (sequence_id, contact_id)
27// - email_events: id serial pk, campaign_send_id integer, event_type text
28//   (open/click/bounce/unsubscribe), metadata jsonb, created_at timestamp
29// Install @sendgrid/mail. Set up Replit Auth. Bind server to 0.0.0.0.

1// 1. Open Secrets panel (lock icon in sidebar)
2//    Add: SENDGRID_API_KEY = your-sendgrid-api-key-here
3//
4// 2. Prompt to type into Replit Agent:
5// Add campaign routes to server/routes/campaigns.js:
6//
7// POST /api/campaigns — create campaign: {name, subject, from_name, from_email, body_html, list_id}
8// GET /api/campaigns — list user's campaigns with send counts per status
9// PUT /api/campaigns/:id — update draft campaign
10//
11// POST /api/campaigns/:id/send — queue for sending:
12//   1. Validate campaign status is 'draft' or 'scheduled'
13//   2. Get all contacts in the campaign's list_id where contact status = 'active'
14//   3. Insert one campaign_sends row per contact with status='pending'
15//      Use INSERT ... ON CONFLICT (campaign_id, contact_id) DO NOTHING
16//      to safely handle re-queuing attempts
17//   4. Update campaign status to 'sending' if scheduled_at is null (send now)
18//      or 'scheduled' if scheduled_at is in the future
19//   5. Return {queued: contactCount}
20//
21// GET /api/campaigns/:id/stats — return:
22//   total_sends, sent_count, open_count, click_count, bounce_count
23//   calculated from campaign_sends joined with email_events
24//   Calculate open_rate = open_count / sent_count * 100

1const sgMail = require('@sendgrid/mail');
2const { db } = require('./server/db');
3const { campaigns, campaignSends, contacts, dripEnrollments, dripSteps, dripSequences } = require('./shared/schema');
4const { eq, and, lte, sql } = require('drizzle-orm');
5
6sgMail.setApiKey(process.env.SENDGRID_API_KEY);
7
8async function processCampaignSends() {
9  // Get campaigns with status='sending'
10  const activeCampaigns = await db.select().from(campaigns)
11    .where(eq(campaigns.status, 'sending'));
12
13  for (const campaign of activeCampaigns) {
14    // Get next 100 pending sends for this campaign
15    const pendingSends = await db.select({
16      sendId: campaignSends.id,
17      email: contacts.email,
18      name: contacts.name
19    })
20    .from(campaignSends)
21    .innerJoin(contacts, eq(campaignSends.contactId, contacts.id))
22    .where(and(eq(campaignSends.campaignId, campaign.id), eq(campaignSends.status, 'pending')))
23    .limit(100);
24
25    if (pendingSends.length === 0) {
26      // All sends complete — mark campaign as sent
27      await db.update(campaigns).set({ status: 'sent', sentAt: new Date() })
28        .where(eq(campaigns.id, campaign.id));
29      continue;
30    }
31
32    for (const send of pendingSends) {
33      try {
34        await sgMail.send({
35          to: send.email,
36          from: { name: campaign.fromName, email: campaign.fromEmail },
37          subject: campaign.subject,
38          html: campaign.bodyHtml.replace('{{name}}', send.name || 'there')
39        });
40        await db.update(campaignSends).set({ status: 'sent', sentAt: new Date() })
41          .where(eq(campaignSends.id, send.sendId));
42      } catch (err) {
43        await db.update(campaignSends).set({ status: 'failed' })
44          .where(eq(campaignSends.id, send.sendId));
45        console.error(`Send failed for ${send.email}:`, err.response?.body || err.message);
46      }
47    }
48
49    // Small delay between batches to respect SendGrid rate limits
50    await new Promise(r => setTimeout(r, 1000));
51  }
52}
53
54async function processDripEnrollments() {
55  const dueEnrollments = await db.select()
56    .from(dripEnrollments)
57    .where(and(
58      eq(dripEnrollments.status, 'active'),
59      lte(dripEnrollments.nextSendAt, new Date())
60    ));
61
62  for (const enrollment of dueEnrollments) {
63    const nextStep = await db.query.dripSteps.findFirst({
64      where: and(
65        eq(dripSteps.sequenceId, enrollment.sequenceId),
66        eq(dripSteps.position, enrollment.currentStep)
67      )
68    });
69
70    if (!nextStep) {
71      await db.update(dripEnrollments).set({ status: 'completed' })
72        .where(eq(dripEnrollments.id, enrollment.id));
73      continue;
74    }
75
76    const contact = await db.query.contacts.findFirst({
77      where: eq(contacts.id, enrollment.contactId)
78    });
79
80    if (contact?.status === 'active') {
81      await sgMail.send({
82        to: contact.email,
83        from: { name: 'Your Company', email: process.env.FROM_EMAIL },
84        subject: nextStep.subject,
85        html: nextStep.bodyHtml.replace('{{name}}', contact.name || 'there')
86      });
87    }
88
89    // Advance to next step
90    const followingStep = await db.query.dripSteps.findFirst({
91      where: and(
92        eq(dripSteps.sequenceId, enrollment.sequenceId),
93        eq(dripSteps.position, enrollment.currentStep + 1)
94      )
95    });
96
97    await db.update(dripEnrollments).set({
98      currentStep: enrollment.currentStep + 1,
99      nextSendAt: followingStep
100        ? new Date(Date.now() + followingStep.delayHours * 3600000)
101        : null,
102      status: followingStep ? 'active' : 'completed'
103    }).where(eq(dripEnrollments.id, enrollment.id));
104  }
105}
106
107(async () => {
108  await processCampaignSends();
109  await processDripEnrollments();
110  process.exit(0);
111})();

1// Prompt to type into Replit Agent:
2// Add the SendGrid webhook handler to server/routes/webhooks.js:
3//
4// POST /api/webhooks/sendgrid
5//   SendGrid sends an array of events in the request body
6//   Each event has: email, event (open/click/bounce/unsubscribe/delivered),
7//   sg_event_id, sg_message_id, timestamp, url (for click events)
8//
9//   For each event in the array:
10//   1. Find the matching campaign_sends row by joining contacts.email = event.email
11//      and filtering by recent sent_at (within 30 days)
12//   2. If found, insert into email_events: {campaign_send_id, event_type: event.event,
13//      metadata: {url: event.url}, created_at: new Date(event.timestamp * 1000)}
14//   3. If event.event === 'bounce' or 'unsubscribe':
15//      UPDATE contacts SET status = (event.event === 'bounce' ? 'bounced' : 'unsubscribed'),
16//      unsubscribed_at = now() WHERE email = event.email
17//   4. Always return 200 — SendGrid retries on non-2xx responses
18//
19// Register the webhook URL in SendGrid:
20// Settings → Mail Settings → Event Webhook
21// HTTP Post URL: https://your-deployment-url.repl.co/api/webhooks/sendgrid
22// Select: Delivered, Opened, Clicked, Bounced, Unsubscribed

1// Prompt to type into Replit Agent:
2// Add these routes to server/routes/contacts.js:
3//
4// POST /api/contacts/import — CSV import
5//   Accept multipart form with CSV file using multer
6//   Parse CSV with csv-parse: expect columns 'email' and optionally 'name', 'tags'
7//   For each row: INSERT INTO contacts (email, name, tags) ON CONFLICT (email) DO NOTHING
8//   Return {imported: count, skipped: duplicateCount}
9//
10// POST /api/lists/:id/contacts — add contact by email to a list
11//   Find or create contact by email, then insert list_contacts row
12//
13// Add these routes to server/routes/drip.js:
14//
15// POST /api/drip-sequences — create sequence: {name, trigger_event, list_id}
16// POST /api/drip-sequences/:id/steps — add step: {delay_hours, subject, body_html, position}
17// GET /api/drip-sequences/:id — sequence with all steps ordered by position
18// DELETE /api/drip-sequences/:id/steps/:stepId — remove a step
19//
20// POST /api/drip-sequences/:id/enroll — enroll a contact
21//   Body: {contactId} — contact must be in the sequence's list
22//   INSERT INTO drip_enrollments (sequence_id, contact_id, current_step=0,
23//     next_send_at = now() + first step's delay_hours)
24//   ON CONFLICT (sequence_id, contact_id) DO NOTHING
25//
26// GET /api/drip-sequences/:id/enrollments — list enrollments with contact info and status

1// Deployment plan:
2//
3// 1. Open Secrets panel (lock icon) and verify:
4//    SENDGRID_API_KEY, SESSION_SECRET, FROM_EMAIL (verified sender)
5//    DATABASE_URL (auto-set by Replit)
6//
7// 2. Ensure server/index.js binds to 0.0.0.0:
8//    app.listen(process.env.PORT || 3000, '0.0.0.0', () => {...})
9//
10// 3. Deploy main app on Reserved VM:
11//    Deploy → Reserved VM (ensures webhook receiver is always-on)
12//    Note your deployment URL: https://your-repl.your-username.repl.co
13//
14// 4. Create Scheduled Deployment:
15//    Deploy → Scheduled → every 15 minutes
16//    Run command: node scripts/processQueue.js
17//    This runs the send queue processor on a cron schedule
18//
19// 5. Register SendGrid webhook:
20//    SendGrid Dashboard → Settings → Mail Settings → Event Webhook
21//    HTTP Post URL: https://your-deployment-url.repl.co/api/webhooks/sendgrid
22//    Check: Delivered, Opened, Clicked, Bounced, Unsubscribed → Save
23//
24// 6. Test: create a contact, add to a list, create a campaign, queue sends
25//    Wait for next Scheduled Deployment run (or trigger manually from Scripts)
26//    Check Drizzle Studio: campaign_sends should show status='sent'