1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
/** @jsxImportSource https://esm.sh/preact */
import { generateNewsletterJsx } from "https://esm.town/v/petermillspaugh/generateNewsletterJsx";
import { newsletters } from "https://esm.town/v/petermillspaugh/newsletters";
import { email as sendEmail } from "https://esm.town/v/std/email?v=11";
import { sqlite } from "https://esm.town/v/std/sqlite?v=4";
import { render } from "npm:preact-render-to-string";
type SubscriberRow = [
subscriberId: number,
emailAddress: string,
];
export async function sendEmailNewsletter(interval: Interval) {
const { jsx: newsletterContent, subject, webUrl, targetSendDate } = newsletters[newsletters.length - 1];
// no-op and alert if the current timestamp isn't within five minutes of the targetSendDate
const fiveMinutes = 5 * 60 * 1000;
if (Math.abs(new Date(targetSendDate).getTime() - Date.now()) > fiveMinutes) {
return await sendEmail({
subject: "Alert! Unexpected error in sendEmailNewsletter",
html: `Send attempt for newsletter_id=${newsletters.length} is not within 5 minutes of target send date`,
});
}
// no-op and alert if interval was run <28 days ago (enforce max one newsletter per month)
const twentyEightDaysAgo = Date.now() - (28 * 24 * 60 * 60 * 1000);
if (!interval.lastRunAt || interval.lastRunAt.getTime() > twentyEightDaysAgo) {
return await sendEmail({
subject: "Alert! Unexpected error in sendEmailNewsletter",
html: "Val fired twice in <28 days",
});
}
const { rows: newsletterEmailLogs } = await sqlite.execute({
sql: `SELECT * FROM email_logs WHERE newsletter_id = ?;`,
args: [newsletters.length],
});
// no-op and alert if there's already a log of the latest newsletter
if (newsletterEmailLogs.length > 0) {
return await sendEmail({
subject: "Alert! Unexpected error in sendEmailNewsletter",
html: `Duplicate send attempt for newsletter_id=${newsletters.length}`,
});
}
const { rows: subscribers } = await sqlite.execute(
`
SELECT id, email
FROM subscribers
WHERE verified = 1
AND subscribed_at IS NOT NULL;
`,
);
for (const [subscriberId, emailAddress] of subscribers as unknown as SubscriberRow[]) {
const { rows: subscriberEmailLogs } = await sqlite.execute({
sql: `
SELECT *
FROM email_logs
WHERE newsletter_id = ?
AND subscriber_id = ?;
`,
args: [newsletters.length, subscriberId],
});
// skip subscriber and alert if log exists for newsletter + subscriber
if (subscriberEmailLogs.length > 0) {
await sendEmail({
subject: "Alert! Unexpected error in sendEmailNewsletter",
html: `Duplicate send attempt for subscriber_id=${subscriberId} and newsletter_id=${newsletters.length}`,
});
continue;
}
const jsx = generateNewsletterJsx({ webUrl, newsletterContent, emailAddress });
await sendEmail({
subject,
html: render(jsx),
to: emailAddress,
from: {
name: "Pete Millspaugh",
email: "petermillspaugh.sendEmailNewsletter@valtown.email",
},
replyTo: "pete@petemillspaugh.com",
});
// log sent email
await sqlite.execute({
sql: `
INSERT INTO email_logs (newsletter_id, subscriber_id)
VALUES (?, ?);
`,
args: [newsletters.length, subscriberId],
});
}
}