Skip to main content

max / makenotwork

9.9 KB · 335 lines History Blame Raw
1 //! Mailing list CRUD: per-project lists and subscriber management.
2
3 use sqlx::PgPool;
4
5 use super::enums::MailingListType;
6 use super::id_types::{MailingListId, ProjectId, UserId};
7 use super::models::DbMailingList;
8 use crate::error::Result;
9
10 /// Create a mailing list for a project. Idempotent via ON CONFLICT.
11 #[tracing::instrument(skip_all)]
12 pub async fn create_list(
13 pool: &PgPool,
14 project_id: ProjectId,
15 list_type: MailingListType,
16 name: &str,
17 description: Option<&str>,
18 ) -> Result<DbMailingList> {
19 let row = sqlx::query_as::<_, DbMailingList>(
20 r"
21 INSERT INTO mailing_lists (project_id, list_type, name, description)
22 VALUES ($1, $2, $3, $4)
23 ON CONFLICT (project_id, list_type) DO UPDATE SET name = EXCLUDED.name
24 RETURNING *
25 ",
26 )
27 .bind(project_id)
28 .bind(list_type)
29 .bind(name)
30 .bind(description)
31 .fetch_one(pool)
32 .await?;
33
34 Ok(row)
35 }
36
37 /// Look up a list by project and type.
38 #[tracing::instrument(skip_all)]
39 pub async fn get_list_by_project_and_type(
40 pool: &PgPool,
41 project_id: ProjectId,
42 list_type: MailingListType,
43 ) -> Result<Option<DbMailingList>> {
44 let row = sqlx::query_as::<_, DbMailingList>(
45 "SELECT * FROM mailing_lists WHERE project_id = $1 AND list_type = $2",
46 )
47 .bind(project_id)
48 .bind(list_type)
49 .fetch_optional(pool)
50 .await?;
51
52 Ok(row)
53 }
54
55 /// Subscribe a user to a list. Idempotent via ON CONFLICT DO NOTHING.
56 #[tracing::instrument(skip_all)]
57 pub async fn subscribe(pool: &PgPool, list_id: MailingListId, user_id: UserId) -> Result<()> {
58 sqlx::query(
59 r"
60 INSERT INTO mailing_list_subscribers (list_id, user_id)
61 VALUES ($1, $2)
62 ON CONFLICT (list_id, user_id) DO NOTHING
63 ",
64 )
65 .bind(list_id)
66 .bind(user_id)
67 .execute(pool)
68 .await?;
69
70 Ok(())
71 }
72
73 /// Unsubscribe a user from a specific list. Returns true if a row was deleted.
74 #[tracing::instrument(skip_all)]
75 pub async fn unsubscribe(pool: &PgPool, list_id: MailingListId, user_id: UserId) -> Result<bool> {
76 let result =
77 sqlx::query("DELETE FROM mailing_list_subscribers WHERE list_id = $1 AND user_id = $2")
78 .bind(list_id)
79 .bind(user_id)
80 .execute(pool)
81 .await?;
82
83 Ok(result.rows_affected() > 0)
84 }
85
86 /// Unsubscribe a user from ALL mailing lists on a project (used on unfollow).
87 #[tracing::instrument(skip_all)]
88 pub async fn unsubscribe_from_project(
89 pool: &PgPool,
90 project_id: ProjectId,
91 user_id: UserId,
92 ) -> Result<u64> {
93 let result = sqlx::query(
94 r"
95 DELETE FROM mailing_list_subscribers
96 WHERE user_id = $1
97 AND list_id IN (SELECT id FROM mailing_lists WHERE project_id = $2)
98 ",
99 )
100 .bind(user_id)
101 .bind(project_id)
102 .execute(pool)
103 .await?;
104
105 Ok(result.rows_affected())
106 }
107
108 /// A mailing-list recipient: either an MNW user (has `user_id`) or an
109 /// email-only subscriber imported from another platform (`user_id` is `None`).
110 /// The two need different unsubscribe links, user-keyed vs email-keyed.
111 #[derive(Debug, Clone, sqlx::FromRow)]
112 pub struct MailingSubscriber {
113 pub user_id: Option<UserId>,
114 pub email: String,
115 pub display_name: Option<String>,
116 }
117
118 /// Get all deliverable subscribers on a list, both MNW users AND email-only
119 /// imported subscribers. Capped at 10,000.
120 ///
121 /// User rows require a verified, non-suspended account; email-only rows
122 /// (`user_id IS NULL`) come straight from the import. Both branches exclude
123 /// suppressed addresses. Previously this INNER JOINed `users`, so imported
124 /// email-only subscribers were silently never emailed (Run 21 data).
125 #[tracing::instrument(skip_all)]
126 pub async fn get_subscribers(
127 pool: &PgPool,
128 list_id: MailingListId,
129 ) -> Result<Vec<MailingSubscriber>> {
130 let rows = sqlx::query_as::<_, MailingSubscriber>(
131 r"
132 SELECT u.id AS user_id, u.email, u.display_name
133 FROM mailing_list_subscribers s
134 JOIN users u ON u.id = s.user_id
135 WHERE s.list_id = $1
136 AND u.email_verified = true
137 AND u.suspended_at IS NULL
138 AND LOWER(u.email) NOT IN (SELECT LOWER(email) FROM email_suppressions)
139 UNION ALL
140 SELECT NULL::uuid AS user_id, s.email, NULL AS display_name
141 FROM mailing_list_subscribers s
142 WHERE s.list_id = $1
143 AND s.user_id IS NULL
144 AND s.email IS NOT NULL
145 AND LOWER(s.email) NOT IN (SELECT LOWER(email) FROM email_suppressions)
146 LIMIT 10000
147 ",
148 )
149 .bind(list_id)
150 .fetch_all(pool)
151 .await?;
152
153 Ok(rows)
154 }
155
156 /// Unsubscribe an email-only subscriber (no MNW account) from a list. Removes
157 /// the `(list_id, email)` row. Idempotent. Backs the email-keyed unsubscribe
158 /// link carried in emails to imported subscribers.
159 #[tracing::instrument(skip_all)]
160 pub async fn unsubscribe_by_email(
161 pool: &PgPool,
162 list_id: MailingListId,
163 email: &str,
164 ) -> Result<bool> {
165 let result = sqlx::query(
166 "DELETE FROM mailing_list_subscribers WHERE list_id = $1 AND LOWER(email) = LOWER($2) AND user_id IS NULL",
167 )
168 .bind(list_id)
169 .bind(email)
170 .execute(pool)
171 .await?;
172
173 Ok(result.rows_affected() > 0)
174 }
175
176 /// Auto-create the default content + devlog lists for a new project.
177 #[tracing::instrument(skip_all)]
178 pub async fn create_default_lists(
179 pool: &PgPool,
180 project_id: ProjectId,
181 project_title: &str,
182 ) -> Result<()> {
183 create_list(
184 pool,
185 project_id,
186 MailingListType::Content,
187 &format!("{project_title}: Content"),
188 Some("New releases and content updates"),
189 )
190 .await?;
191
192 create_list(
193 pool,
194 project_id,
195 MailingListType::Devlog,
196 &format!("{project_title}: Devlog"),
197 Some("Development updates and behind-the-scenes"),
198 )
199 .await?;
200
201 Ok(())
202 }
203
204 /// Batch-subscribe many emails to a list in a single statement.
205 ///
206 /// Used by the import pipeline, where subscribing one email per query is an
207 /// N+1 storm (a 100k-subscriber import would issue 100k sequential INSERTs on
208 /// the shared pool). Emails are lowercased and deduplicated within the batch;
209 /// `ON CONFLICT DO NOTHING` skips ones already subscribed. Returns the number of
210 /// rows actually inserted (new subscribers).
211 #[tracing::instrument(skip_all, fields(count = emails.len()))]
212 pub async fn subscribe_many_by_email(
213 pool: &PgPool,
214 list_id: MailingListId,
215 emails: &[String],
216 ) -> Result<u64> {
217 if emails.is_empty() {
218 return Ok(0);
219 }
220 let lowered: Vec<String> = emails.iter().map(|e| e.to_lowercase()).collect();
221 let result = sqlx::query(
222 r"
223 INSERT INTO mailing_list_subscribers (list_id, email)
224 SELECT $1, sub_email FROM UNNEST($2::text[]) AS sub_email
225 ON CONFLICT DO NOTHING
226 ",
227 )
228 .bind(list_id)
229 .bind(&lowered)
230 .execute(pool)
231 .await?;
232
233 Ok(result.rows_affected())
234 }
235
236 /// Convenience: find the content list for a project and subscribe a user.
237 /// No-op if the content list doesn't exist yet.
238 #[tracing::instrument(skip_all)]
239 pub async fn subscribe_to_content_list(
240 pool: &PgPool,
241 project_id: ProjectId,
242 user_id: UserId,
243 ) -> Result<()> {
244 if let Some(list) =
245 get_list_by_project_and_type(pool, project_id, MailingListType::Content).await?
246 {
247 subscribe(pool, list.id, user_id).await?;
248 }
249
250 Ok(())
251 }
252
253 #[cfg(test)]
254 mod tests {
255 use super::*;
256
257 #[test]
258 fn mailing_list_type_content_roundtrip() {
259 assert_eq!(MailingListType::Content.to_string(), "content");
260 assert_eq!(
261 "content".parse::<MailingListType>().unwrap(),
262 MailingListType::Content
263 );
264 }
265
266 #[test]
267 fn mailing_list_type_devlog_roundtrip() {
268 assert_eq!(MailingListType::Devlog.to_string(), "devlog");
269 assert_eq!(
270 "devlog".parse::<MailingListType>().unwrap(),
271 MailingListType::Devlog
272 );
273 }
274
275 #[test]
276 fn mailing_list_type_patches_roundtrip() {
277 assert_eq!(MailingListType::Patches.to_string(), "patches");
278 assert_eq!(
279 "patches".parse::<MailingListType>().unwrap(),
280 MailingListType::Patches
281 );
282 }
283
284 #[test]
285 fn mailing_list_type_invalid_parse_fails() {
286 assert!("newsletter".parse::<MailingListType>().is_err());
287 assert!("".parse::<MailingListType>().is_err());
288 assert!("CONTENT".parse::<MailingListType>().is_err());
289 }
290
291 #[test]
292 fn mailing_list_type_serde_json_roundtrip() {
293 let val = MailingListType::Content;
294 let json = serde_json::to_string(&val).unwrap();
295 assert_eq!(json, "\"content\"");
296 let parsed: MailingListType = serde_json::from_str(&json).unwrap();
297 assert_eq!(parsed, val);
298 }
299
300 #[test]
301 fn default_list_name_format_content() {
302 let name = format!("{}: Content", "My Project");
303 assert_eq!(name, "My Project: Content");
304 }
305
306 #[test]
307 fn default_list_name_format_devlog() {
308 let name = format!("{}: Devlog", "My Project");
309 assert_eq!(name, "My Project: Devlog");
310 }
311
312 #[test]
313 fn default_list_names_with_special_chars() {
314 let title = "Héllo & World <3>";
315 assert_eq!(format!("{title}: Content"), "Héllo & World <3>: Content");
316 assert_eq!(format!("{title}: Devlog"), "Héllo & World <3>: Devlog");
317 }
318
319 #[test]
320 fn subscribe_by_email_lowercases() {
321 // The function lowercases via email.to_lowercase() before binding.
322 // Verify the stdlib behaviour our code relies on.
323 assert_eq!("FOO@BAR.COM".to_lowercase(), "foo@bar.com");
324 assert_eq!("MiXeD@CaSe.Org".to_lowercase(), "mixed@case.org");
325 }
326
327 #[test]
328 fn id_types_are_distinct() {
329 let ml_id = MailingListId::new();
330 let proj_id = ProjectId::new();
331 // They wrap different UUIDs and are different types, this is a compile-time check.
332 assert_ne!(ml_id.as_uuid(), proj_id.as_uuid());
333 }
334 }
335