/*
the br project — Private CRM & Email Campaigns
ONE-FILE VERSION: paste this whole file into the Cloudflare Worker editor.
Setup (all in the Cloudflare dashboard, no terminal):
1. Storage & Databases → D1 → Create database → name it br-crm
2. Workers & Pages → Create → Worker → "Hello World" → Deploy
3. Edit code → delete everything → paste this file → Deploy
4. Worker → Settings → Bindings → Add → D1 database
Variable name: DB Database: br-crm → Save
5. Worker → Settings → Trigger Events → Add → Cron Triggers → every hour ( 0 * * * * )
6. Open your worker's URL, sign in, go to Settings, paste your Resend API key + mailing address.
Login: brentle@brentleroberts.com (the password is stored only as a salted hash below)
*/
// "the br project — Email Campaign" (Nov 10, 2026 · Brentle Roberts)
// Six short emails over three weeks. Each email asks for one thing only.
//
// Placeholders filled in automatically for each contact / from Settings:
// {First Name} {Personal Line} {Site Link} {Feedback Link}
// {County Link} {How To Help Link} {Donate Link} {Impact}
// Links use [button text](url). Lines starting with • become bullet lists.
// day_offset 0 = Day 1 (the day the contact is added).
const CAMPAIGN = {
name: 'the br project — Launch Campaign',
description:
"Six short emails over three weeks. Together they get people to visit the br project, try it, tell you what's missing, and share it. Each email asks for one thing only. Every ask is something a reader can do in under two minutes.",
steps: [
{
day_offset: 0,
goal: 'Tell your story and launch',
ask: 'Visit the site',
subject: "I built something for Alabama, and I need your eyes on it",
alt_subject: "The project I've been quietly working on",
preview: 'Free help for every county, in English and Spanish.',
body: `Hi {First Name},
{Personal Line}
I want to share something I've been pouring my nights and weekends into: the br project.
When someone is in crisis, or when you're trying to help someone who is, the hardest part is often knowing where to turn. Phone numbers are buried, websites are confusing, and there's no time to search. I wanted one place people could go the moment they need it.
So I built it. the br project is a free resource hub for people in crisis and for everyday people who want to know how to show up for someone else. Right now it has:
• A page for all 67 Alabama counties with local resources
• 200+ pages in English and Spanish
• 911, 988 and 211 on every single page
• A Quick Exit button that leaves the site instantly if someone needs to hide what they're looking at
It's live, and it's still growing. That's where you come in.
[Take a look at the br project →]({Site Link})
I'd love for you to click around for two minutes. Over the next few weeks I'll send a few short notes asking for your honest thoughts. Your feedback will shape what this becomes.
Thank you for being someone I could send this to.
Brentle Roberts
Founder, the br project
P.S. If you know someone who works at a nonprofit, church, school or local business, forward this to them. They're exactly who I want to reach.`,
},
{
day_offset: 2,
goal: 'Make it personal',
ask: 'Look up your county',
subject: "What's in your county? (30-second challenge)",
alt_subject: "Is your county's page right?",
preview: 'Type in your county and tell me what you see.',
body: `Hi {First Name},
{Personal Line}
Here's a quick challenge. It takes about 30 seconds.
1. Go to {Site Link}
2. Type your county into the search bar
3. Look at what comes up
[Find your county →]({County Link})
You know your community better than any website does. So I want to know: is anything missing? A food pantry, a shelter, a support group, a church that helps people? If it's not on your county's page, I want it there.
Just hit reply and tell me. Even one name helps.
Every resource you send me could be the one someone finds at 2 a.m. when they don't know who else to call.
Thank you,
Brentle
P.S. Try a county where family lives too. Fixing one page helps everyone who lives there.`,
},
{
day_offset: 6,
goal: 'Get feedback',
ask: 'Answer 3 questions',
subject: 'Be honest with me (3 questions)',
alt_subject: 'I need your feedback, the real kind',
preview: 'Two minutes. No wrong answers.',
body: `Hi {First Name},
{Personal Line}
the br project has been live for a week. I'm going to make it better, but I can't do that without honest feedback, and that includes the stuff that isn't working.
Could you answer three questions? Reply to this email or use the short form. It takes about two minutes.
1. When you opened the site, was it clear what it's for?
2. Was anything confusing, slow, or hard to find?
3. What's one thing you wish it had?
[Answer the 3 questions →]({Feedback Link})
I read every single response. If you say something's broken, I'll fix it. If you suggest something, there's a good chance you'll see it on the site soon.
Thank you for helping me build this the right way,
Brentle
P.S. Did you look at it on your phone? Tell me how that went. Most people in a hard moment will find it on a phone.`,
},
{
day_offset: 9,
goal: 'Show value for everyday people',
ask: 'Read one "how to help" page',
subject: 'What would you say if a friend called you in crisis?',
alt_subject: "Most of us freeze. Here's what to do instead.",
preview: 'Five minutes now could help someone you love later.',
body: `Hi {First Name},
{Personal Line}
Most people who reach out in a hard moment don't call a hotline first. They call a friend, a sibling or a coworker. They call someone like you.
And most of us freeze. We don't know what to say, we're scared of saying the wrong thing, or we don't know where to send them.
That's why the br project isn't only for people in crisis. It's also for everyday people who want to be ready. The "how to help" pages walk you through:
• What to say, and what not to say
• How to tell when it's time to call 911 or 988
• How to find local help in your county fast
[Read one "how to help" page →]({How To Help Link})
It takes five minutes. You may never need it. But if someone you love ever calls, you'll be glad you read it.
Brentle
P.S. You can download the free quick guide from the site. Save it to your phone or print it for your fridge, your break room or your church bulletin board.`,
},
{
day_offset: 13,
goal: 'Get people involved',
ask: 'Share it with one person',
subject: 'One forward could matter more than you think',
alt_subject: 'Will you send this to one person?',
preview: 'The site only works if people know it exists.',
body: `Hi {First Name},
{Personal Line}
Thank you. Because of the people on this list, the br project has {Impact} in two weeks.
Here's the hard truth: a crisis resource only works if people know about it before they need it. Nobody searches for help calmly in the middle of the worst night of their life. They use what they already know.
So here's my ask this week. Send the br project to one person. You can copy and paste this:
"My friend Brentle built a free site with crisis help for every Alabama county, in English and Spanish. Worth saving: {Site Link}"
Or share it to your Facebook, your group chat, your church group or your team at work.
Want to do more? Reply with "I'm in" and I'll add you to a small group of early supporters. You'll get first looks at new features and a say in what gets built next.
Grateful for you,
Brentle
P.S. Teachers, pastors, HR managers and coaches: you reach a lot of people at once. If that's you, I'd love to talk.`,
},
{
day_offset: 20,
goal: 'Turn support into partners',
ask: 'Sponsor, book a class, or donate',
subject: 'Where the br project goes from here',
alt_subject: "Three ways to be part of what's next",
preview: 'Sponsor it, bring it to your team, or chip in.',
body: `Hi {First Name},
{Personal Line}
Three weeks ago I asked you to look at something I built. Your replies, fixes and shares have already made the br project better. Thank you.
My goal is for this to be the first place people in Alabama think of in a crisis. I want to give it my full time, and to do that it has to be able to sustain itself. Here are three ways you can help, depending on where you are:
1. Bring it to your workplace or organization. I teach a class for businesses and nonprofits on how to recognize when someone is struggling and connect them to help. Your team leaves with a co-branded quick guide to keep. Reply "class" and I'll send details.
2. Become a founding sponsor. I'm looking for the first businesses and organizations to put their name behind this work. Founding sponsors are recognized on the site and on printed materials handed out at events. Reply "sponsor" and let's talk.
3. Chip in any amount. Every dollar goes toward keeping the site running, adding resources and getting it in front of more people.
[Support the br project →]({Donate Link})
And if none of those fit right now, that's completely okay. Keep sharing it. That matters just as much.
With gratitude,
Brentle Roberts
Founder, the br project
{Site Link}
P.S. Know a business owner who cares about the community? Forward them this email. One introduction could become a sponsorship.`,
},
],
};
const CHECKLIST = [
'Build your list (aim for 100+ people to start) and import it',
'Make the 3-question feedback form and paste its link into Settings → Feedback link',
'Add a personal line for your closest 20–30 people (those are the emails that get forwarded)',
'Fill in your mailing address in Settings (required by law for the email footer)',
'Send Email 1 to yourself and a friend first; check links on a phone',
'Keep the send window around 10 a.m. Central (Settings), Tuesday through Thursday works best',
'Answer every reply within a day. Replies are the engagement you are after.',
'After two weeks, re-send Email 1 with the alt subject line to people who did not open it',
'Track open rate (about 40%+ is good), clicks to the site, and replies after each send',
"Don't call donations \"tax-deductible\" until the br project is a registered nonprofit",
];
const DEFAULT_SETTINGS = {
brand_name: 'the br project',
from_name: 'Brentle Roberts',
from_email: 'brentle@brentleroberts.com',
reply_to: 'brentle@brentleroberts.com',
site_url: 'https://thebrproject.com',
feedback_url: '',
county_url: 'https://thebrproject.com',
help_url: 'https://thebrproject.com',
donate_url: 'https://thebrproject.com',
impact_line: 'new resources added and pages fixed',
mailing_address: '',
send_hour_start: '10',
send_hour_end: '19',
send_days: '1,2,3,4,5',
max_per_run: '50',
checklist: '[]',
};
const CONFIG = {
ADMIN_EMAIL: 'brentle@brentleroberts.com',
PASSWORD_SALT: '02b8898bdf361cec0cf0aac8b6a6f583',
PASSWORD_HASH: 'b371a3b83b6c105223e33d1535ce909945a8e2a717f725b9c21bc573d5860bd5',
PASSWORD_ITERATIONS: '100000',
};
const SCHEMA = [
"CREATE TABLE IF NOT EXISTS contacts (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n first_name TEXT DEFAULT '',\n last_name TEXT DEFAULT '',\n email TEXT NOT NULL UNIQUE COLLATE NOCASE,\n phone TEXT DEFAULT '',\n company TEXT DEFAULT '',\n source TEXT DEFAULT '',\n stage TEXT DEFAULT 'New Lead',\n tags TEXT DEFAULT '',\n personal_line TEXT DEFAULT '',\n notes TEXT DEFAULT '',\n unsubscribed INTEGER DEFAULT 0,\n unsub_token TEXT NOT NULL,\n created_at INTEGER NOT NULL,\n updated_at INTEGER NOT NULL,\n last_contacted_at INTEGER\n)",
"CREATE TABLE IF NOT EXISTS activities (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,\n type TEXT NOT NULL,\n text TEXT NOT NULL,\n created_at INTEGER NOT NULL\n)",
"CREATE TABLE IF NOT EXISTS campaigns (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n description TEXT DEFAULT '',\n active INTEGER DEFAULT 1,\n auto_enroll INTEGER DEFAULT 1,\n created_at INTEGER NOT NULL\n)",
"CREATE TABLE IF NOT EXISTS steps (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n campaign_id INTEGER NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,\n position INTEGER NOT NULL,\n day_offset INTEGER NOT NULL,\n goal TEXT DEFAULT '',\n ask TEXT DEFAULT '',\n subject TEXT NOT NULL,\n alt_subject TEXT DEFAULT '',\n preview TEXT DEFAULT '',\n body TEXT NOT NULL\n)",
"CREATE TABLE IF NOT EXISTS enrollments (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,\n campaign_id INTEGER NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,\n enrolled_at INTEGER NOT NULL,\n next_position INTEGER DEFAULT 1,\n status TEXT DEFAULT 'active',\n UNIQUE (contact_id, campaign_id)\n)",
"CREATE TABLE IF NOT EXISTS sends (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n enrollment_id INTEGER,\n contact_id INTEGER,\n step_id INTEGER,\n to_email TEXT,\n subject TEXT,\n status TEXT,\n error TEXT,\n sent_at INTEGER NOT NULL\n)",
"CREATE TABLE IF NOT EXISTS settings (\n key TEXT PRIMARY KEY,\n value TEXT\n)",
"CREATE TABLE IF NOT EXISTS login_attempts (\n ip TEXT PRIMARY KEY,\n count INTEGER DEFAULT 0,\n window_start INTEGER\n)",
"CREATE INDEX IF NOT EXISTS idx_enroll_status ON enrollments(status)",
"CREATE INDEX IF NOT EXISTS idx_activity_contact ON activities(contact_id)",
"CREATE INDEX IF NOT EXISTS idx_sends_time ON sends(sent_at)"
];
const APP_HTML = "\n\n\n\n\n\nthe br project \u2014 CRM\n\n\n\n\n\n \n
the br projectPrivate CRM & email campaigns\n
\n
Almost there!
Connect the database: Worker → Settings → Bindings → Add → D1 database, variable name DB, database br-crm.
', { status: 500, headers: { 'content-type': 'text/html' } });
}
try {
return await route(request, env, ctx);
} catch (err) {
if (!err.status || err.status >= 500) console.error(err);
return json({ error: err.message || 'Server error' }, err.status || 500);
}
},
async scheduled(event, env, ctx) {
env = Object.assign({}, CONFIG, env);
ctx.waitUntil(runScheduler(env).then((r) => console.log('scheduler', JSON.stringify(r))));
},
};
/* ---------------- helpers ---------------- */
function json(data, status = 200, headers = {}) {
return new Response(JSON.stringify(data), {
status,
headers: { 'content-type': 'application/json', 'cache-control': 'no-store', 'x-robots-tag': 'noindex', ...headers },
});
}
function httpError(status, message) {
const e = new Error(message);
e.status = status;
return e;
}
const hex = (bytes) => [...bytes].map((b) => b.toString(16).padStart(2, '0')).join('');
const unhex = (s) => new Uint8Array(s.match(/.{2}/g).map((h) => parseInt(h, 16)));
function safeEqual(a, b) {
if (a.length !== b.length) return false;
let diff = 0;
for (let i = 0; i < a.length; i++) diff |= a.charCodeAt(i) ^ b.charCodeAt(i);
return diff === 0;
}
function randomToken(n = 18) {
const b = new Uint8Array(n);
crypto.getRandomValues(b);
return hex(b);
}
const clean = (v, max = 2000) => String(v ?? '').trim().slice(0, max);
const isEmail = (e) => /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(e);
const escapeHtml = (s) =>
String(s ?? '').replace(/[&<>"']/g, (c) => ({ '&': '&', '<': '<', '>': '>', '"': '"', "'": ''' })[c]);
function chicagoParts(date = new Date()) {
const parts = new Intl.DateTimeFormat('en-US', {
timeZone: 'America/Chicago', hour: 'numeric', hourCycle: 'h23', weekday: 'short',
}).formatToParts(date);
const hour = Number(parts.find((p) => p.type === 'hour').value);
const wd = parts.find((p) => p.type === 'weekday').value;
const day = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'].indexOf(wd);
return { hour, day };
}
/* ---------------- auth ---------------- */
async function pbkdf2(password, saltHex, iterations) {
const key = await crypto.subtle.importKey('raw', enc.encode(password), 'PBKDF2', false, ['deriveBits']);
const bits = await crypto.subtle.deriveBits({ name: 'PBKDF2', hash: 'SHA-256', salt: unhex(saltHex), iterations }, key, 256);
return hex(new Uint8Array(bits));
}
async function hmac(secret, data) {
const key = await crypto.subtle.importKey('raw', enc.encode(secret), { name: 'HMAC', hash: 'SHA-256' }, false, ['sign']);
return hex(new Uint8Array(await crypto.subtle.sign('HMAC', key, enc.encode(data))));
}
let SECRET_CACHE = null;
async function sessionSecret(env) {
if (env.SESSION_SECRET) return env.SESSION_SECRET;
if (SECRET_CACHE) return SECRET_CACHE;
await ensureSeed(env);
const q = "SELECT value FROM settings WHERE key = 'session_secret'";
let row = await env.DB.prepare(q).first();
if (!row) {
await env.DB.prepare("INSERT OR IGNORE INTO settings (key, value) VALUES ('session_secret', ?)").bind(randomToken(32)).run();
row = await env.DB.prepare(q).first();
}
return (SECRET_CACHE = row.value);
}
async function makeSession(env) {
const exp = Date.now() + SESSION_DAYS * DAY;
return `${exp}.${await hmac(await sessionSecret(env), `session:${exp}`)}`;
}
async function isAuthed(request, env) {
const cookie = request.headers.get('cookie') || '';
const m = cookie.match(/(?:^|;\s*)br_session=([^;]+)/);
if (!m) return false;
const [exp, sig] = m[1].split('.');
if (!exp || !sig || Number(exp) < Date.now()) return false;
return safeEqual(sig, await hmac(await sessionSecret(env), `session:${exp}`));
}
function cookieHeader(value, maxAge) {
return `br_session=${value}; Path=/; HttpOnly; Secure; SameSite=Strict; Max-Age=${maxAge}`;
}
async function login(request, env) {
const ip = request.headers.get('cf-connecting-ip') || 'unknown';
const now = Date.now();
const row = await env.DB.prepare('SELECT count, window_start FROM login_attempts WHERE ip = ?').bind(ip).first();
if (row && now - row.window_start < 15 * 60000 && row.count >= 8) {
throw httpError(429, 'Too many attempts. Try again in 15 minutes.');
}
const body = await request.json().catch(() => ({}));
const emailOk = clean(body.email).toLowerCase() === String(env.ADMIN_EMAIL).toLowerCase();
const hash = await pbkdf2(String(body.password ?? ''), env.PASSWORD_SALT, Number(env.PASSWORD_ITERATIONS || 100000));
if (!emailOk || !safeEqual(hash, env.PASSWORD_HASH)) {
if (!row || now - row.window_start >= 15 * 60000) {
await env.DB.prepare('INSERT OR REPLACE INTO login_attempts (ip, count, window_start) VALUES (?, 1, ?)').bind(ip, now).run();
} else {
await env.DB.prepare('UPDATE login_attempts SET count = count + 1 WHERE ip = ?').bind(ip).run();
}
throw httpError(401, 'Wrong email or password.');
}
await env.DB.prepare('DELETE FROM login_attempts WHERE ip = ?').bind(ip).run();
return json({ ok: true, email: env.ADMIN_EMAIL }, 200, { 'set-cookie': cookieHeader(await makeSession(env), SESSION_DAYS * 86400) });
}
/* ---------------- settings & seed ---------------- */
let seeded = false;
let ORIGIN_SAVED = false;
async function ensureSeed(env) {
if (seeded) return;
const db = env.DB;
await db.batch(SCHEMA.map((q) => db.prepare(q)));
const defaults = Object.entries(DEFAULT_SETTINGS).map(([k, v]) =>
db.prepare('INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)').bind(k, v));
await db.batch(defaults);
const c = await db.prepare('SELECT COUNT(*) n FROM campaigns').first();
if (!c.n) {
const res = await db.prepare('INSERT INTO campaigns (name, description, active, auto_enroll, created_at) VALUES (?, ?, 1, 1, ?)')
.bind(CAMPAIGN.name, CAMPAIGN.description, Date.now()).run();
const id = res.meta.last_row_id;
await db.batch(CAMPAIGN.steps.map((s, i) =>
db.prepare('INSERT INTO steps (campaign_id, position, day_offset, goal, ask, subject, alt_subject, preview, body) VALUES (?,?,?,?,?,?,?,?,?)')
.bind(id, i + 1, s.day_offset, s.goal, s.ask, s.subject, s.alt_subject, s.preview, s.body)));
}
seeded = true;
}
async function getSettings(env) {
const { results } = await env.DB.prepare('SELECT key, value FROM settings').all();
const s = { ...DEFAULT_SETTINGS };
for (const r of results) s[r.key] = r.value;
return s;
}
/* ---------------- email rendering ---------------- */
function fillTokens(text, contact, s) {
const map = {
'First Name': contact.first_name || 'there',
'Last Name': contact.last_name || '',
'Personal Line': contact.personal_line || '',
'Site Link': s.site_url,
'Feedback Link': s.feedback_url || `mailto:${s.reply_to}?subject=Feedback%20on%20the%20br%20project`,
'County Link': s.county_url || s.site_url,
'How To Help Link': s.help_url || s.site_url,
'Donate Link': s.donate_url || s.site_url,
'Impact': s.impact_line,
};
return String(text).replace(/\{([A-Za-z ]+)\}/g, (m, k) => (k in map ? map[k] : m));
}
const LINK_RE = /\[([^\]]+)\]\(([^)\s]+)\)/g;
function inlineHtml(line) {
return escapeHtml(line)
.replace(LINK_RE, (m, t, u) => `${t}`)
.replace(/(^|[\s(])(https?:\/\/[^\s<]+)/g, (m, pre, u) => `${pre}${u}`);
}
function bodyToHtml(text) {
const blocks = text.replace(/\r/g, '').split(/\n\s*\n/).map((b) => b.trim()).filter(Boolean);
return blocks.map((block) => {
const lines = block.split('\n').map((l) => l.trim());
const btn = block.match(/^\[([^\]]+)\]\(([^)\s]+)\)$/);
if (btn) {
return `${escapeHtml(btn[1])}
`;
}
if (lines.every((l) => /^[•\-*]\s+/.test(l))) {
return `${lines.map((l) => `- ${inlineHtml(l.replace(/^[•\-*]\s+/, ''))}
`).join('')}
`;
}
if (lines.every((l) => /^\d+\.\s+/.test(l))) {
return `${lines.map((l) => `- ${inlineHtml(l.replace(/^\d+\.\s+/, ''))}
`).join('')}
`;
}
return `${lines.map(inlineHtml).join('
')}
`;
}).join('\n');
}
function bodyToText(text) {
return text.replace(LINK_RE, '$1 $2').replace(/\n{3,}/g, '\n\n').trim();
}
function renderEmail(step, contact, s, unsubUrl) {
const subject = fillTokens(step.subject, contact, s);
const preview = fillTokens(step.preview || '', contact, s);
const filled = fillTokens(step.body, contact, s);
const address = s.mailing_address ? escapeHtml(s.mailing_address) : '';
const html = `${escapeHtml(subject)}
${escapeHtml(preview)}͏ ͏ ͏
`;
const text = `${bodyToText(filled)}\n\n---\nIn crisis right now? Call or text 988, call 911 for emergencies, or dial 211 for local help.\n${s.from_name} · ${s.reply_to} · ${s.site_url}\n${s.mailing_address ? s.mailing_address + '\n' : ''}Unsubscribe: ${unsubUrl}`;
return { subject, html, text };
}
async function sendEmail(env, s, { to, subject, html, text, unsubUrl }) {
const apiKey = env.RESEND_API_KEY || s.resend_api_key;
if (!apiKey) throw new Error('No Resend API key yet. Add it in Settings.');
const headers = {};
if (unsubUrl) {
headers['List-Unsubscribe'] = `<${unsubUrl}>`;
headers['List-Unsubscribe-Post'] = 'List-Unsubscribe=One-Click';
}
const res = await fetch('https://api.resend.com/emails', {
method: 'POST',
headers: { Authorization: `Bearer ${apiKey}`, 'content-type': 'application/json' },
body: JSON.stringify({ from: `${s.from_name} <${s.from_email}>`, to: [to], reply_to: s.reply_to, subject, html, text, headers }),
});
if (!res.ok) throw new Error(`Email service said ${res.status}: ${(await res.text()).slice(0, 300)}`);
return res.json();
}
function baseUrl(env, request) {
if (env.PUBLIC_URL) return env.PUBLIC_URL.replace(/\/$/, '');
if (request) return new URL(request.url).origin;
return '';
}
/* ---------------- scheduler ---------------- */
async function runScheduler(env, { force = false, origin = '' } = {}) {
await ensureSeed(env);
const s = await getSettings(env);
const { hour, day } = chicagoParts();
const days = String(s.send_days || '0,1,2,3,4,5,6').split(',').map(Number);
if (!force && (hour < Number(s.send_hour_start) || hour >= Number(s.send_hour_end) || !days.includes(day))) {
return { sent: 0, failed: 0, note: 'Outside the send window, nothing sent.' };
}
const origin_ = origin || baseUrl(env) || s.app_url || '';
const max = Math.max(1, Number(s.max_per_run) || 50);
const now = Date.now();
const { results } = await env.DB.prepare(`
SELECT e.id AS eid, e.enrolled_at, e.next_position, c.id AS cid, c.email, c.first_name, c.last_name, c.personal_line, c.stage, c.unsub_token,
st.id AS step_id, st.day_offset, st.subject, st.preview, st.body, st.position
FROM enrollments e
JOIN contacts c ON c.id = e.contact_id
JOIN campaigns k ON k.id = e.campaign_id
LEFT JOIN steps st ON st.campaign_id = e.campaign_id AND st.position = e.next_position
WHERE e.status = 'active' AND k.active = 1 AND c.unsubscribed = 0
ORDER BY e.enrolled_at LIMIT 500`).all();
let sent = 0, failed = 0;
const errors = [];
for (const r of results) {
if (sent + failed >= max) break;
if (r.step_id == null) {
await env.DB.prepare("UPDATE enrollments SET status = 'completed' WHERE id = ?").bind(r.eid).run();
continue;
}
if (now < r.enrolled_at + r.day_offset * DAY) continue;
// Claim this step so overlapping runs can never double-send.
const claim = await env.DB.prepare('UPDATE enrollments SET next_position = next_position + 1 WHERE id = ? AND next_position = ?')
.bind(r.eid, r.next_position).run();
if (!claim.meta.changes) continue;
const unsubUrl = `${origin_}/u/${r.unsub_token}`;
const contact = { first_name: r.first_name, last_name: r.last_name, personal_line: r.personal_line };
const email = renderEmail(r, contact, s, unsubUrl);
try {
await sendEmail(env, s, { to: r.email, ...email, unsubUrl });
sent++;
await env.DB.batch([
env.DB.prepare('INSERT INTO sends (enrollment_id, contact_id, step_id, to_email, subject, status, sent_at) VALUES (?,?,?,?,?,?,?)')
.bind(r.eid, r.cid, r.step_id, r.email, email.subject, 'sent', Date.now()),
env.DB.prepare('INSERT INTO activities (contact_id, type, text, created_at) VALUES (?,?,?,?)')
.bind(r.cid, 'email', `Campaign email ${r.position} sent: "${email.subject}"`, Date.now()),
env.DB.prepare("UPDATE contacts SET last_contacted_at = ?, stage = CASE WHEN stage = 'New Lead' THEN 'Contacted' ELSE stage END WHERE id = ?")
.bind(Date.now(), r.cid),
]);
} catch (err) {
failed++;
errors.push(`${r.email}: ${err.message}`);
// Give the step back so it retries next hour.
await env.DB.batch([
env.DB.prepare('UPDATE enrollments SET next_position = ? WHERE id = ?').bind(r.next_position, r.eid),
env.DB.prepare('INSERT INTO sends (enrollment_id, contact_id, step_id, to_email, subject, status, error, sent_at) VALUES (?,?,?,?,?,?,?,?)')
.bind(r.eid, r.cid, r.step_id, r.email, email.subject, 'failed', err.message.slice(0, 500), Date.now()),
]);
}
}
return { sent, failed, errors: errors.slice(0, 5) };
}
/* ---------------- contacts ---------------- */
const CONTACT_FIELDS = ['first_name', 'last_name', 'email', 'phone', 'company', 'source', 'stage', 'tags', 'personal_line', 'notes'];
function normalizeContact(input) {
const c = {};
for (const f of CONTACT_FIELDS) if (input[f] !== undefined) c[f] = clean(input[f], f === 'notes' ? 10000 : 1000);
if (c.email !== undefined) {
c.email = c.email.toLowerCase();
if (!isEmail(c.email)) throw httpError(400, `"${c.email || '(blank)'}" isn't a valid email address.`);
}
if (c.stage !== undefined && !STAGES.includes(c.stage)) c.stage = 'New Lead';
if (c.tags !== undefined) c.tags = c.tags.split(',').map((t) => t.trim()).filter(Boolean).join(', ');
return c;
}
async function autoEnroll(env, contactId) {
const { results } = await env.DB.prepare('SELECT id FROM campaigns WHERE active = 1 AND auto_enroll = 1').all();
if (!results.length) return 0;
await env.DB.batch(results.map((k) =>
env.DB.prepare('INSERT OR IGNORE INTO enrollments (contact_id, campaign_id, enrolled_at) VALUES (?,?,?)').bind(contactId, k.id, Date.now())));
return results.length;
}
async function createContact(env, input, { enroll = true } = {}) {
const c = normalizeContact(input);
if (!c.email) throw httpError(400, 'Email is required.');
const exists = await env.DB.prepare('SELECT id FROM contacts WHERE email = ?').bind(c.email).first();
if (exists) throw httpError(409, `${c.email} is already in your contacts.`);
const now = Date.now();
const row = { first_name: '', last_name: '', phone: '', company: '', source: '', stage: 'New Lead', tags: '', personal_line: '', notes: '', ...c };
const res = await env.DB.prepare(`INSERT INTO contacts (first_name,last_name,email,phone,company,source,stage,tags,personal_line,notes,unsub_token,created_at,updated_at)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)`).bind(row.first_name, row.last_name, row.email, row.phone, row.company, row.source, row.stage, row.tags,
row.personal_line, row.notes, randomToken(), now, now).run();
const id = res.meta.last_row_id;
await env.DB.prepare('INSERT INTO activities (contact_id, type, text, created_at) VALUES (?,?,?,?)').bind(id, 'created', 'Contact added', now).run();
if (enroll) await autoEnroll(env, id);
return id;
}
async function getContact(env, id) {
const contact = await env.DB.prepare('SELECT * FROM contacts WHERE id = ?').bind(id).first();
if (!contact) throw httpError(404, 'Contact not found.');
const [acts, enrolls, sends] = await env.DB.batch([
env.DB.prepare('SELECT * FROM activities WHERE contact_id = ? ORDER BY created_at DESC LIMIT 100').bind(id),
env.DB.prepare(`SELECT e.*, k.name AS campaign_name, (SELECT COUNT(*) FROM steps WHERE campaign_id = e.campaign_id) AS total_steps,
(SELECT day_offset FROM steps WHERE campaign_id = e.campaign_id AND position = e.next_position) AS next_offset
FROM enrollments e JOIN campaigns k ON k.id = e.campaign_id WHERE e.contact_id = ?`).bind(id),
env.DB.prepare('SELECT * FROM sends WHERE contact_id = ? ORDER BY sent_at DESC LIMIT 50').bind(id),
]);
return { contact, activities: acts.results, enrollments: enrolls.results, sends: sends.results };
}
/* ---------------- router ---------------- */
async function route(request, env, ctx) {
const url = new URL(request.url);
const path = url.pathname;
const method = request.method;
// Public unsubscribe page
const u = path.match(/^\/u\/([a-f0-9]{16,64})$/);
if (u) return unsubscribe(request, env, u[1]);
if (!path.startsWith('/api/')) {
if (path === '/favicon.ico') return new Response(null, { status: 204 });
return new Response(APP_HTML, { headers: {
'content-type': 'text/html; charset=utf-8', 'cache-control': 'no-store',
'x-robots-tag': 'noindex, nofollow', 'x-frame-options': 'DENY' } });
}
await ensureSeed(env);
if (path === '/api/login' && method === 'POST') return login(request, env);
if (path === '/api/logout' && method === 'POST') return json({ ok: true }, 200, { 'set-cookie': cookieHeader('', 0) });
if (!(await isAuthed(request, env))) return json({ error: 'Please sign in.' }, 401);
if (!ORIGIN_SAVED) {
await env.DB.prepare("INSERT OR REPLACE INTO settings (key, value) VALUES ('app_url', ?)").bind(url.origin).run();
ORIGIN_SAVED = true;
}
if (!['GET', 'DELETE'].includes(method) && !(request.headers.get('content-type') || '').includes('application/json')) {
throw httpError(415, 'Expected JSON.');
}
const body = method === 'GET' || method === 'DELETE' ? {} : await request.json().catch(() => ({}));
const db = env.DB;
let m;
if (path === '/api/me') return json({ email: env.ADMIN_EMAIL, stages: STAGES, checklist: CHECKLIST });
if (path === '/api/stats') {
const weekAgo = Date.now() - 7 * DAY;
const [total, active, sent7, unsub, stages, recent, upcoming] = await db.batch([
db.prepare('SELECT COUNT(*) n FROM contacts'),
db.prepare("SELECT COUNT(*) n FROM enrollments WHERE status = 'active'"),
db.prepare("SELECT COUNT(*) n FROM sends WHERE status = 'sent' AND sent_at > ?").bind(weekAgo),
db.prepare('SELECT COUNT(*) n FROM contacts WHERE unsubscribed = 1'),
db.prepare('SELECT stage, COUNT(*) n FROM contacts GROUP BY stage'),
db.prepare(`SELECT a.*, c.first_name, c.last_name, c.email FROM activities a JOIN contacts c ON c.id = a.contact_id ORDER BY a.created_at DESC LIMIT 12`),
db.prepare(`SELECT c.id, c.first_name, c.last_name, c.email, st.position, st.subject, e.enrolled_at + st.day_offset * ${DAY} AS due
FROM enrollments e JOIN contacts c ON c.id = e.contact_id JOIN campaigns k ON k.id = e.campaign_id
JOIN steps st ON st.campaign_id = e.campaign_id AND st.position = e.next_position
WHERE e.status = 'active' AND k.active = 1 AND c.unsubscribed = 0 ORDER BY due LIMIT 10`),
]);
const failed = await db.prepare("SELECT COUNT(*) n FROM sends WHERE status = 'failed' AND sent_at > ?").bind(weekAgo).first();
return json({
contacts: total.results[0].n, active: active.results[0].n, sent7: sent7.results[0].n, unsubscribed: unsub.results[0].n,
failed7: failed.n, stages: stages.results, recent: recent.results, upcoming: upcoming.results,
});
}
if (path === '/api/contacts' && method === 'GET') {
const q = `%${clean(url.searchParams.get('q'), 100)}%`;
const stage = clean(url.searchParams.get('stage'), 100);
const tag = clean(url.searchParams.get('tag'), 100);
let sql = `SELECT c.*, (SELECT COUNT(*) FROM sends s WHERE s.contact_id = c.id AND s.status = 'sent') AS emails_sent,
(SELECT status FROM enrollments e WHERE e.contact_id = c.id ORDER BY e.id DESC LIMIT 1) AS campaign_status
FROM contacts c WHERE (first_name || ' ' || last_name || ' ' || email || ' ' || company || ' ' || tags || ' ' || phone) LIKE ?`;
const args = [q];
if (stage) { sql += ' AND stage = ?'; args.push(stage); }
if (tag) { sql += " AND (', ' || tags || ',') LIKE ?"; args.push(`%, ${tag},%`); }
sql += ' ORDER BY created_at DESC LIMIT 2000';
const { results } = await db.prepare(sql).bind(...args).all();
return json({ contacts: results });
}
if (path === '/api/contacts' && method === 'POST') {
const id = await createContact(env, body, { enroll: body.enroll !== false });
return json(await getContact(env, id), 201);
}
if (path === '/api/contacts/import' && method === 'POST') {
const rows = Array.isArray(body.rows) ? body.rows.slice(0, 5000) : [];
let added = 0, skipped = 0;
const problems = [];
for (const r of rows) {
try {
await createContact(env, { ...r, source: r.source || body.source || 'Import' }, { enroll: body.enroll !== false });
added++;
} catch (e) {
skipped++;
if (problems.length < 20) problems.push(e.message);
}
}
return json({ added, skipped, problems });
}
if (path === '/api/contacts/export.csv' && method === 'GET') {
const { results } = await db.prepare('SELECT * FROM contacts ORDER BY created_at').all();
const cols = ['first_name', 'last_name', 'email', 'phone', 'company', 'stage', 'tags', 'source', 'personal_line', 'notes', 'unsubscribed', 'created_at', 'last_contacted_at'];
const q = (v) => `"${String(v ?? '').replace(/"/g, '""')}"`;
const fmt = (c, k) => (k.endsWith('_at') && c[k] ? new Date(c[k]).toISOString() : c[k]);
const csv = [cols.join(','), ...results.map((c) => cols.map((k) => q(fmt(c, k))).join(','))].join('\n');
return new Response(csv, { headers: { 'content-type': 'text/csv', 'content-disposition': 'attachment; filename="br-contacts.csv"' } });
}
if ((m = path.match(/^\/api\/contacts\/(\d+)$/))) {
const id = Number(m[1]);
if (method === 'GET') return json(await getContact(env, id));
if (method === 'DELETE') {
await db.batch([
db.prepare('DELETE FROM activities WHERE contact_id = ?').bind(id),
db.prepare('DELETE FROM enrollments WHERE contact_id = ?').bind(id),
db.prepare('DELETE FROM contacts WHERE id = ?').bind(id),
]);
return json({ ok: true });
}
if (method === 'PUT') {
const before = await db.prepare('SELECT * FROM contacts WHERE id = ?').bind(id).first();
if (!before) throw httpError(404, 'Contact not found.');
const c = normalizeContact(body);
if (c.email && c.email !== before.email) {
const dupe = await db.prepare('SELECT id FROM contacts WHERE email = ? AND id != ?').bind(c.email, id).first();
if (dupe) throw httpError(409, `${c.email} belongs to another contact.`);
}
const keys = Object.keys(c);
const stmts = [];
if (keys.length) {
stmts.push(db.prepare(`UPDATE contacts SET ${keys.map((k) => `${k} = ?`).join(', ')}, updated_at = ? WHERE id = ?`).bind(...keys.map((k) => c[k]), Date.now(), id));
}
if (c.stage && c.stage !== before.stage) {
stmts.push(db.prepare('INSERT INTO activities (contact_id, type, text, created_at) VALUES (?,?,?,?)').bind(id, 'stage', `Stage: ${before.stage} → ${c.stage}`, Date.now()));
if (c.stage === 'Not Interested') stmts.push(db.prepare("UPDATE enrollments SET status = 'stopped' WHERE contact_id = ? AND status = 'active'").bind(id));
}
if (body.unsubscribed !== undefined) {
const val = body.unsubscribed ? 1 : 0;
stmts.push(db.prepare('UPDATE contacts SET unsubscribed = ? WHERE id = ?').bind(val, id));
if (val) stmts.push(db.prepare("UPDATE enrollments SET status = 'stopped' WHERE contact_id = ? AND status = 'active'").bind(id));
}
if (stmts.length) await db.batch(stmts);
return json(await getContact(env, id));
}
}
if ((m = path.match(/^\/api\/contacts\/(\d+)\/notes$/)) && method === 'POST') {
const text = clean(body.text, 5000);
if (!text) throw httpError(400, 'Note is empty.');
const type = ['note', 'call', 'meeting', 'reply', 'text'].includes(body.type) ? body.type : 'note';
await db.batch([
db.prepare('INSERT INTO activities (contact_id, type, text, created_at) VALUES (?,?,?,?)').bind(m[1], type, text, Date.now()),
...(type !== 'note' ? [db.prepare('UPDATE contacts SET last_contacted_at = ? WHERE id = ?').bind(Date.now(), m[1])] : []),
]);
return json(await getContact(env, Number(m[1])));
}
if ((m = path.match(/^\/api\/activities\/(\d+)$/)) && method === 'DELETE') {
await db.prepare("DELETE FROM activities WHERE id = ? AND type IN ('note','call','meeting','reply','text')").bind(m[1]).run();
return json({ ok: true });
}
if (path === '/api/campaigns' && method === 'GET') {
const { results: camps } = await db.prepare(`SELECT k.*,
(SELECT COUNT(*) FROM enrollments e WHERE e.campaign_id = k.id AND e.status = 'active') AS active_count,
(SELECT COUNT(*) FROM enrollments e WHERE e.campaign_id = k.id AND e.status = 'completed') AS completed_count
FROM campaigns k ORDER BY k.id`).all();
const { results: steps } = await db.prepare(`SELECT st.*, (SELECT COUNT(*) FROM sends s WHERE s.step_id = st.id AND s.status = 'sent') AS sent_count
FROM steps st ORDER BY campaign_id, position`).all();
return json({ campaigns: camps.map((k) => ({ ...k, steps: steps.filter((s) => s.campaign_id === k.id) })) });
}
if ((m = path.match(/^\/api\/campaigns\/(\d+)$/)) && method === 'PUT') {
const sets = [], args = [];
if (body.name !== undefined) { sets.push('name = ?'); args.push(clean(body.name, 200)); }
if (body.active !== undefined) { sets.push('active = ?'); args.push(body.active ? 1 : 0); }
if (body.auto_enroll !== undefined) { sets.push('auto_enroll = ?'); args.push(body.auto_enroll ? 1 : 0); }
if (sets.length) await db.prepare(`UPDATE campaigns SET ${sets.join(', ')} WHERE id = ?`).bind(...args, m[1]).run();
return json({ ok: true });
}
if ((m = path.match(/^\/api\/campaigns\/(\d+)\/enroll$/)) && method === 'POST') {
const ids = (Array.isArray(body.contact_ids) ? body.contact_ids : []).map(Number).filter(Boolean);
const restart = !!body.restart;
const stmts = ids.map((cid) => restart
? db.prepare("INSERT INTO enrollments (contact_id, campaign_id, enrolled_at) VALUES (?,?,?) ON CONFLICT(contact_id, campaign_id) DO UPDATE SET enrolled_at = excluded.enrolled_at, next_position = 1, status = 'active'").bind(cid, m[1], Date.now())
: db.prepare('INSERT OR IGNORE INTO enrollments (contact_id, campaign_id, enrolled_at) VALUES (?,?,?)').bind(cid, m[1], Date.now()));
if (stmts.length) await db.batch(stmts);
return json({ ok: true, count: ids.length });
}
if ((m = path.match(/^\/api\/enrollments\/(\d+)$/)) && method === 'PUT') {
const status = ['active', 'stopped'].includes(body.status) ? body.status : 'stopped';
await db.prepare('UPDATE enrollments SET status = ? WHERE id = ?').bind(status, m[1]).run();
return json({ ok: true });
}
if ((m = path.match(/^\/api\/steps\/(\d+)$/)) && method === 'PUT') {
const fields = { subject: 300, alt_subject: 300, preview: 300, body: 20000, goal: 200, ask: 200 };
const sets = [], args = [];
for (const [f, max] of Object.entries(fields)) if (body[f] !== undefined) { sets.push(`${f} = ?`); args.push(clean(body[f], max)); }
if (body.day_offset !== undefined) { sets.push('day_offset = ?'); args.push(Math.max(0, Math.min(365, Number(body.day_offset) || 0))); }
if (!sets.length) throw httpError(400, 'Nothing to save.');
await db.prepare(`UPDATE steps SET ${sets.join(', ')} WHERE id = ?`).bind(...args, m[1]).run();
return json({ ok: true });
}
if ((m = path.match(/^\/api\/steps\/(\d+)\/(preview|test)$/))) {
const step = await db.prepare('SELECT * FROM steps WHERE id = ?').bind(m[1]).first();
if (!step) throw httpError(404, 'Email not found.');
const s = await getSettings(env);
const sample = { first_name: 'Brentle', last_name: 'Roberts', personal_line: '' };
const cid = Number(url.searchParams.get('contact_id') || body.contact_id || 0);
const contact = cid ? (await db.prepare('SELECT * FROM contacts WHERE id = ?').bind(cid).first()) || sample : sample;
const unsubUrl = `${baseUrl(env, request)}/u/preview`;
const email = renderEmail(step, contact, s, unsubUrl);
if (m[2] === 'preview') return json(email);
const to = isEmail(clean(body.to)) ? clean(body.to) : env.ADMIN_EMAIL;
await sendEmail(env, s, { to, ...email, subject: `[TEST] ${email.subject}` });
return json({ ok: true, to });
}
if (path === '/api/sends' && method === 'GET') {
const { results } = await db.prepare(`SELECT s.*, c.first_name, c.last_name, st.position FROM sends s
LEFT JOIN contacts c ON c.id = s.contact_id LEFT JOIN steps st ON st.id = s.step_id ORDER BY s.sent_at DESC LIMIT 300`).all();
return json({ sends: results });
}
if (path === '/api/run-now' && method === 'POST') {
return json(await runScheduler(env, { force: !!body.ignoreWindow, origin: baseUrl(env, request) }));
}
if (path === '/api/settings') {
if (method === 'PUT') {
const stmts = Object.keys(DEFAULT_SETTINGS).filter((k) => body[k] !== undefined)
.map((k) => db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)').bind(k, clean(body[k], 5000)));
const key = clean(body.resend_api_key, 300);
if (key) stmts.push(db.prepare("INSERT OR REPLACE INTO settings (key, value) VALUES ('resend_api_key', ?)").bind(key));
if (stmts.length) await db.batch(stmts);
}
const all = await getSettings(env);
const out = {};
for (const k of Object.keys(DEFAULT_SETTINGS)) out[k] = all[k];
out.resend_api_key_set = !!(env.RESEND_API_KEY || all.resend_api_key);
return json({ settings: out });
}
throw httpError(404, 'Not found.');
}
/* ---------------- unsubscribe ---------------- */
async function unsubscribe(request, env, token) {
const page = (title, msg, form = '') => new Response(`${title}
`,
{ headers: { 'content-type': 'text/html; charset=utf-8', 'x-robots-tag': 'noindex' } });
const c = await env.DB.prepare('SELECT id, unsubscribed FROM contacts WHERE unsub_token = ?').bind(token).first();
if (!c) return page('Link not found', 'This unsubscribe link is no longer valid. Reply to any email and Brentle will remove you by hand.');
if (request.method === 'POST') {
await env.DB.batch([
env.DB.prepare('UPDATE contacts SET unsubscribed = 1, updated_at = ? WHERE id = ?').bind(Date.now(), c.id),
env.DB.prepare("UPDATE enrollments SET status = 'stopped' WHERE contact_id = ? AND status = 'active'").bind(c.id),
env.DB.prepare('INSERT INTO activities (contact_id, type, text, created_at) VALUES (?,?,?,?)').bind(c.id, 'unsubscribe', 'Unsubscribed from emails', Date.now()),
]);
return page("You're unsubscribed", "You won't get any more of these emails. Thank you for your time.");
}
if (c.unsubscribed) return page("You're already unsubscribed", "You won't get any more of these emails.");
return page('Unsubscribe?', 'Click below to stop getting emails about the br project.', ``);
}