dunkirk.sh
rss email digests over ssh because you're a cool kid
herald.dunkirk.sh
go
rss
rss-reader
ssh
charm
1package store
2
3import (
4 "crypto/rand"
5 "database/sql"
6 "encoding/base64"
7 "fmt"
8 "time"
9)
10
11type EmailSend struct {
12 ID int64
13 ConfigID int64
14 Recipient string
15 Subject string
16 TrackingToken string
17 SentAt time.Time
18 Bounced bool
19 BounceReason sql.NullString
20 Opened bool
21 OpenedAt sql.NullTime
22}
23
24// RecordEmailSend records an email send with optional tracking token
25func (db *DB) RecordEmailSend(configID int64, recipient, subject string, includeTracking bool) (string, error) {
26 var trackingToken string
27 if includeTracking {
28 token, err := generateTrackingToken()
29 if err != nil {
30 return "", fmt.Errorf("generate tracking token: %w", err)
31 }
32 trackingToken = token
33 }
34
35 query := `INSERT INTO email_sends (config_id, recipient, subject, tracking_token)
36 VALUES (?, ?, ?, ?)`
37 _, err := db.Exec(query, configID, recipient, subject, sql.NullString{String: trackingToken, Valid: trackingToken != ""})
38 if err != nil {
39 return "", fmt.Errorf("insert email send: %w", err)
40 }
41
42 return trackingToken, nil
43}
44
45// RecordEmailSendTx records an email send within an existing transaction
46func (db *DB) RecordEmailSendTx(tx *sql.Tx, configID int64, recipient, subject, trackingToken string) error {
47 query := `INSERT INTO email_sends (config_id, recipient, subject, tracking_token)
48 VALUES (?, ?, ?, ?)`
49 _, err := tx.Exec(query, configID, recipient, subject, sql.NullString{String: trackingToken, Valid: trackingToken != ""})
50 if err != nil {
51 return fmt.Errorf("insert email send: %w", err)
52 }
53 return nil
54}
55
56// MarkEmailBounced marks an email as bounced
57func (db *DB) MarkEmailBounced(configID int64, recipient, reason string) error {
58 query := `UPDATE email_sends
59 SET bounced = TRUE, bounce_reason = ?
60 WHERE config_id = ? AND recipient = ?
61 AND sent_at > datetime('now', '-7 days')
62 ORDER BY sent_at DESC
63 LIMIT 1`
64 _, err := db.Exec(query, reason, configID, recipient)
65 return err
66}
67
68// MarkEmailOpened marks an email as opened via tracking token
69func (db *DB) MarkEmailOpened(trackingToken string) error {
70 query := `UPDATE email_sends
71 SET opened = TRUE, opened_at = CURRENT_TIMESTAMP
72 WHERE tracking_token = ? AND opened = FALSE`
73 result, err := db.Exec(query, trackingToken)
74 if err != nil {
75 return fmt.Errorf("update email opened: %w", err)
76 }
77
78 rows, err := result.RowsAffected()
79 if err != nil {
80 return fmt.Errorf("rows affected: %w", err)
81 }
82
83 if rows == 0 {
84 return fmt.Errorf("tracking token not found or already opened")
85 }
86
87 return nil
88}
89
90// GetInactiveConfigs returns config IDs that haven't had keep-alive activity in the specified days
91func (db *DB) GetInactiveConfigs(daysWithoutActivity int, minSends int) ([]int64, error) {
92 query := `
93 SELECT DISTINCT c.id
94 FROM configs c
95 INNER JOIN email_sends es ON es.config_id = c.id
96 WHERE c.id IN (
97 SELECT config_id
98 FROM email_sends
99 GROUP BY config_id
100 HAVING COUNT(*) >= ?
101 )
102 AND (
103 c.last_active_at IS NULL
104 OR c.last_active_at < datetime('now', '-' || ? || ' days')
105 )
106 AND c.created_at < datetime('now', '-' || ? || ' days')
107 GROUP BY c.id
108 `
109
110 rows, err := db.Query(query, minSends, daysWithoutActivity, daysWithoutActivity)
111 if err != nil {
112 return nil, fmt.Errorf("query inactive configs: %w", err)
113 }
114 defer func() { _ = rows.Close() }()
115
116 var configIDs []int64
117 for rows.Next() {
118 var id int64
119 if err := rows.Scan(&id); err != nil {
120 return nil, fmt.Errorf("scan config id: %w", err)
121 }
122 configIDs = append(configIDs, id)
123 }
124
125 return configIDs, rows.Err()
126}
127
128// GetConfigEngagement returns engagement stats for a config
129func (db *DB) GetConfigEngagement(configID int64, days int) (totalSends, opens, bounces int, lastOpen *time.Time, err error) {
130 // First get counts
131 countQuery := `
132 SELECT
133 COUNT(*) as total_sends,
134 COALESCE(SUM(CASE WHEN opened = TRUE THEN 1 ELSE 0 END), 0) as opens,
135 COALESCE(SUM(CASE WHEN bounced = TRUE THEN 1 ELSE 0 END), 0) as bounces
136 FROM email_sends
137 WHERE config_id = ?
138 AND sent_at > datetime('now', '-' || ? || ' days')
139 `
140
141 err = db.QueryRow(countQuery, configID, days).Scan(&totalSends, &opens, &bounces)
142 if err != nil {
143 return 0, 0, 0, nil, fmt.Errorf("query engagement counts: %w", err)
144 }
145
146 // Get most recent open
147 openQuery := `
148 SELECT opened_at
149 FROM email_sends
150 WHERE config_id = ?
151 AND opened = TRUE
152 AND sent_at > datetime('now', '-' || ? || ' days')
153 ORDER BY opened_at DESC
154 LIMIT 1
155 `
156
157 var lastOpenStr sql.NullString
158 err = db.QueryRow(openQuery, configID, days).Scan(&lastOpenStr)
159 if err != nil && err != sql.ErrNoRows {
160 return 0, 0, 0, nil, fmt.Errorf("query last open: %w", err)
161 }
162
163 if lastOpenStr.Valid && lastOpenStr.String != "" {
164 t, err := time.Parse("2006-01-02 15:04:05", lastOpenStr.String)
165 if err == nil {
166 lastOpen = &t
167 }
168 }
169
170 return totalSends, opens, bounces, lastOpen, nil
171}
172
173// CleanupOldSends removes email send records older than specified days
174func (db *DB) CleanupOldSends(daysToKeep int) (int64, error) {
175 query := `DELETE FROM email_sends WHERE sent_at < datetime('now', '-' || ? || ' days')`
176 result, err := db.Exec(query, daysToKeep)
177 if err != nil {
178 return 0, fmt.Errorf("cleanup old sends: %w", err)
179 }
180 return result.RowsAffected()
181}
182
183// GenerateTrackingToken generates a secure random tracking token
184func (db *DB) GenerateTrackingToken() (string, error) {
185 b := make([]byte, 24)
186 if _, err := rand.Read(b); err != nil {
187 return "", err
188 }
189 return base64.URLEncoding.EncodeToString(b), nil
190}
191
192func generateTrackingToken() (string, error) {
193 b := make([]byte, 24)
194 if _, err := rand.Read(b); err != nil {
195 return "", err
196 }
197 return base64.URLEncoding.EncodeToString(b), nil
198}
199
200// UpdateLastActive updates the last_active_at timestamp for a config by tracking token
201func (db *DB) UpdateLastActive(trackingToken string) error {
202 query := `UPDATE configs
203 SET last_active_at = CURRENT_TIMESTAMP
204 WHERE id = (
205 SELECT config_id FROM email_sends WHERE tracking_token = ? LIMIT 1
206 )`
207 result, err := db.Exec(query, trackingToken)
208 if err != nil {
209 return fmt.Errorf("update last active: %w", err)
210 }
211
212 rows, err := result.RowsAffected()
213 if err != nil {
214 return fmt.Errorf("rows affected: %w", err)
215 }
216
217 if rows == 0 {
218 return fmt.Errorf("tracking token not found")
219 }
220
221 return nil
222}