Skip to main content

max / makenotwork

34.2 KB · 1170 lines History Blame Raw
1 //! Direct tests for mt_db::mutations functions.
2 //!
3 //! These tests exercise database mutation functions that lack integration
4 //! test coverage through route-level tests.
5
6 use crate::harness::TestHarness;
7 use mt_core::types::BanType;
8 use uuid::Uuid;
9
10 // Ban cleanup
11
12 #[tokio::test]
13 async fn cleanup_expired_bans_removes_expired() {
14 let h = TestHarness::new().await;
15 let comm_id = h.create_community("Test", "test").await;
16
17 let user1 = Uuid::new_v4();
18 let user2 = Uuid::new_v4();
19 let admin = Uuid::new_v4();
20
21 for (id, name) in [(user1, "user1"), (user2, "user2"), (admin, "admin")] {
22 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
23 .bind(id)
24 .bind(name)
25 .execute(&h.db)
26 .await
27 .unwrap();
28 }
29
30 // Create expired ban
31 sqlx::query(
32 "INSERT INTO community_bans (community_id, user_id, banned_by, ban_type, expires_at)
33 VALUES ($1, $2, $3, 'ban', now() - interval '1 day')",
34 )
35 .bind(comm_id)
36 .bind(user1)
37 .bind(admin)
38 .execute(&h.db)
39 .await
40 .unwrap();
41
42 // Create active ban
43 sqlx::query(
44 "INSERT INTO community_bans (community_id, user_id, banned_by, ban_type, expires_at)
45 VALUES ($1, $2, $3, 'ban', now() + interval '1 day')",
46 )
47 .bind(comm_id)
48 .bind(user2)
49 .bind(admin)
50 .execute(&h.db)
51 .await
52 .unwrap();
53
54 let cleaned = mt_db::mutations::cleanup_expired_bans(&h.db, comm_id)
55 .await
56 .unwrap();
57 assert_eq!(cleaned, 1, "Should remove 1 expired ban");
58
59 let remaining: i64 =
60 sqlx::query_scalar("SELECT COUNT(*) FROM community_bans WHERE community_id = $1")
61 .bind(comm_id)
62 .fetch_one(&h.db)
63 .await
64 .unwrap();
65 assert_eq!(remaining, 1, "Should keep 1 active ban");
66 }
67
68 #[tokio::test]
69 async fn cleanup_expired_bans_keeps_permanent() {
70 let h = TestHarness::new().await;
71 let comm_id = h.create_community("Test", "test").await;
72
73 let user = Uuid::new_v4();
74 let admin = Uuid::new_v4();
75
76 for (id, name) in [(user, "user"), (admin, "admin")] {
77 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
78 .bind(id)
79 .bind(name)
80 .execute(&h.db)
81 .await
82 .unwrap();
83 }
84
85 // Create permanent ban (no expires_at)
86 mt_db::mutations::create_community_ban(
87 &h.db,
88 comm_id,
89 user,
90 admin,
91 BanType::Ban,
92 Some("permanent"),
93 None,
94 )
95 .await
96 .unwrap();
97
98 let cleaned = mt_db::mutations::cleanup_expired_bans(&h.db, comm_id)
99 .await
100 .unwrap();
101 assert_eq!(cleaned, 0, "Should not remove permanent bans");
102 }
103
104 #[tokio::test]
105 async fn create_community_ban_upserts_on_conflict() {
106 let h = TestHarness::new().await;
107 let comm_id = h.create_community("Test", "test").await;
108
109 let user = Uuid::new_v4();
110 let admin = Uuid::new_v4();
111
112 for (id, name) in [(user, "user"), (admin, "admin")] {
113 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
114 .bind(id)
115 .bind(name)
116 .execute(&h.db)
117 .await
118 .unwrap();
119 }
120
121 // First ban
122 let id1 = mt_db::mutations::create_community_ban(
123 &h.db,
124 comm_id,
125 user,
126 admin,
127 BanType::Ban,
128 Some("first reason"),
129 None,
130 )
131 .await
132 .unwrap();
133
134 // Second ban (same user+type) should upsert, not duplicate
135 let id2 = mt_db::mutations::create_community_ban(
136 &h.db,
137 comm_id,
138 user,
139 admin,
140 BanType::Ban,
141 Some("updated reason"),
142 None,
143 )
144 .await
145 .unwrap();
146
147 assert_eq!(id1, id2, "Upsert should return same ban ID");
148
149 // Verify reason updated
150 let reason: Option<String> =
151 sqlx::query_scalar("SELECT reason FROM community_bans WHERE id = $1")
152 .bind(id1)
153 .fetch_one(&h.db)
154 .await
155 .unwrap();
156 assert_eq!(reason.as_deref(), Some("updated reason"));
157 }
158
159 // Category mutations
160
161 #[tokio::test]
162 async fn swap_category_order_atomic() {
163 let h = TestHarness::new().await;
164 let comm_id = h.create_community("Test", "test").await;
165
166 let cat_a = mt_db::mutations::create_category(&h.db, comm_id, "Alpha", "alpha", None, 0)
167 .await
168 .unwrap();
169 let cat_b = mt_db::mutations::create_category(&h.db, comm_id, "Beta", "beta", None, 1)
170 .await
171 .unwrap();
172
173 mt_db::mutations::swap_category_order(&h.db, cat_a, 0, cat_b, 1)
174 .await
175 .unwrap();
176
177 let order_a: i32 = sqlx::query_scalar("SELECT sort_order FROM categories WHERE id = $1")
178 .bind(cat_a)
179 .fetch_one(&h.db)
180 .await
181 .unwrap();
182 let order_b: i32 = sqlx::query_scalar("SELECT sort_order FROM categories WHERE id = $1")
183 .bind(cat_b)
184 .fetch_one(&h.db)
185 .await
186 .unwrap();
187
188 assert_eq!(order_a, 1, "Alpha should now have order 1");
189 assert_eq!(order_b, 0, "Beta should now have order 0");
190 }
191
192 #[tokio::test]
193 async fn get_category_id_by_slugs_found() {
194 let h = TestHarness::new().await;
195 let comm_id = h.create_community("Test", "test").await;
196 let cat_id = h.create_category(comm_id, "General", "general").await;
197
198 let found = mt_db::mutations::get_category_id_by_slugs(&h.db, "test", "general")
199 .await
200 .unwrap();
201 assert_eq!(found, Some(cat_id));
202 }
203
204 #[tokio::test]
205 async fn get_category_id_by_slugs_not_found() {
206 let h = TestHarness::new().await;
207 let _comm_id = h.create_community("Test", "test").await;
208
209 let found = mt_db::mutations::get_category_id_by_slugs(&h.db, "test", "nonexistent")
210 .await
211 .unwrap();
212 assert_eq!(found, None);
213
214 let found = mt_db::mutations::get_category_id_by_slugs(&h.db, "nosuchcommunity", "general")
215 .await
216 .unwrap();
217 assert_eq!(found, None);
218 }
219
220 #[tokio::test]
221 async fn update_category_updates_fields() {
222 let h = TestHarness::new().await;
223 let comm_id = h.create_community("Test", "test").await;
224 let cat_id = mt_db::mutations::create_category(
225 &h.db,
226 comm_id,
227 "Old Name",
228 "oldslug",
229 Some("Old desc"),
230 0,
231 )
232 .await
233 .unwrap();
234
235 let updated =
236 mt_db::mutations::update_category(&h.db, cat_id, comm_id, "New Name", Some("New desc"))
237 .await
238 .unwrap();
239 assert!(updated);
240
241 let (name, desc): (String, Option<String>) =
242 sqlx::query_as("SELECT name, description FROM categories WHERE id = $1")
243 .bind(cat_id)
244 .fetch_one(&h.db)
245 .await
246 .unwrap();
247
248 assert_eq!(name, "New Name");
249 assert_eq!(desc.as_deref(), Some("New desc"));
250 }
251
252 // Membership mutations
253
254 #[tokio::test]
255 async fn ensure_membership_idempotent() {
256 let h = TestHarness::new().await;
257 let comm_id = h.create_community("Test", "test").await;
258
259 let user = Uuid::new_v4();
260 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
261 .bind(user)
262 .bind("testuser")
263 .execute(&h.db)
264 .await
265 .unwrap();
266
267 // First call creates membership
268 mt_db::mutations::ensure_membership(&h.db, user, comm_id)
269 .await
270 .unwrap();
271
272 // Second call should succeed (ON CONFLICT DO NOTHING)
273 mt_db::mutations::ensure_membership(&h.db, user, comm_id)
274 .await
275 .unwrap();
276
277 let count: i64 = sqlx::query_scalar(
278 "SELECT COUNT(*) FROM memberships WHERE user_id = $1 AND community_id = $2",
279 )
280 .bind(user)
281 .bind(comm_id)
282 .fetch_one(&h.db)
283 .await
284 .unwrap();
285 assert_eq!(count, 1, "Should have exactly one membership row");
286 }
287
288 #[tokio::test]
289 async fn ensure_membership_with_role_idempotent() {
290 let h = TestHarness::new().await;
291 let comm_id = h.create_community("Test", "test").await;
292
293 let user = Uuid::new_v4();
294 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
295 .bind(user)
296 .bind("roleuser")
297 .execute(&h.db)
298 .await
299 .unwrap();
300
301 mt_db::mutations::ensure_membership_with_role(
302 &h.db,
303 user,
304 comm_id,
305 mt_core::types::CommunityRole::Moderator,
306 )
307 .await
308 .unwrap();
309
310 // Second call with same role should succeed
311 mt_db::mutations::ensure_membership_with_role(
312 &h.db,
313 user,
314 comm_id,
315 mt_core::types::CommunityRole::Moderator,
316 )
317 .await
318 .unwrap();
319
320 // Verify role is preserved (DO NOTHING means first write wins)
321 let role: String =
322 sqlx::query_scalar("SELECT role FROM memberships WHERE user_id = $1 AND community_id = $2")
323 .bind(user)
324 .bind(comm_id)
325 .fetch_one(&h.db)
326 .await
327 .unwrap();
328 assert_eq!(role, "moderator");
329 }
330
331 // Thread mutations
332
333 #[tokio::test]
334 async fn soft_delete_sets_deleted_at() {
335 let h = TestHarness::new().await;
336 let comm_id = h.create_community("Test", "test").await;
337 let cat_id = h.create_category(comm_id, "General", "general").await;
338
339 let author = Uuid::new_v4();
340 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
341 .bind(author)
342 .bind("author")
343 .execute(&h.db)
344 .await
345 .unwrap();
346
347 let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Delete Me")
348 .await
349 .unwrap();
350
351 // Verify not deleted
352 let deleted: bool =
353 sqlx::query_scalar("SELECT deleted_at IS NOT NULL FROM threads WHERE id = $1")
354 .bind(thread_id)
355 .fetch_one(&h.db)
356 .await
357 .unwrap();
358 assert!(!deleted, "Should not be deleted initially");
359
360 mt_db::mutations::soft_delete_thread(&h.db, thread_id)
361 .await
362 .unwrap();
363
364 let deleted: bool =
365 sqlx::query_scalar("SELECT deleted_at IS NOT NULL FROM threads WHERE id = $1")
366 .bind(thread_id)
367 .fetch_one(&h.db)
368 .await
369 .unwrap();
370 assert!(deleted, "Should be soft-deleted");
371 }
372
373 #[tokio::test]
374 async fn create_post_bumps_thread_activity() {
375 let h = TestHarness::new().await;
376 let comm_id = h.create_community("Test", "test").await;
377 let cat_id = h.create_category(comm_id, "General", "general").await;
378
379 let author = Uuid::new_v4();
380 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
381 .bind(author)
382 .bind("author")
383 .execute(&h.db)
384 .await
385 .unwrap();
386
387 let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Activity Test")
388 .await
389 .unwrap();
390
391 // Set activity to a known old time
392 sqlx::query("UPDATE threads SET last_activity_at = '2000-01-01'::timestamptz WHERE id = $1")
393 .bind(thread_id)
394 .execute(&h.db)
395 .await
396 .unwrap();
397
398 // Create post should bump last_activity_at
399 mt_db::mutations::create_post(&h.db, thread_id, author, "test", "<p>test</p>")
400 .await
401 .unwrap();
402
403 let recent: bool = sqlx::query_scalar(
404 "SELECT last_activity_at > '2020-01-01'::timestamptz FROM threads WHERE id = $1",
405 )
406 .bind(thread_id)
407 .fetch_one(&h.db)
408 .await
409 .unwrap();
410 assert!(recent, "last_activity_at should be updated to recent time");
411 }
412
413 /// Regression (fuzz-2026-07-06 TOCTOU): a reply insert is conditional on the
414 /// thread still being live and unlocked, in the same statement, so a lock or
415 /// soft-delete that races the handler's snapshot check can't let the reply
416 /// commit (nor let the post_count trigger increment a dead thread). Exercised at
417 /// the DB layer, where the guard lives.
418 #[tokio::test]
419 async fn reply_insert_blocked_on_locked_or_deleted_thread() {
420 let h = TestHarness::new().await;
421 let comm_id = h.create_community("Test", "test").await;
422 let cat_id = h.create_category(comm_id, "General", "general").await;
423
424 let author = Uuid::new_v4();
425 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, 'toctouauthor')")
426 .bind(author)
427 .execute(&h.db)
428 .await
429 .unwrap();
430 let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "TOCTOU")
431 .await
432 .unwrap();
433
434 // Locked → no insert.
435 sqlx::query("UPDATE threads SET locked = true WHERE id = $1")
436 .bind(thread_id)
437 .execute(&h.db)
438 .await
439 .unwrap();
440 assert!(
441 matches!(
442 mt_db::mutations::create_post(&h.db, thread_id, author, "r", "<p>r</p>").await,
443 Err(sqlx::Error::RowNotFound)
444 ),
445 "a reply must not insert on a locked thread"
446 );
447
448 // Unlocked but soft-deleted → still no insert.
449 sqlx::query("UPDATE threads SET locked = false, deleted_at = now() WHERE id = $1")
450 .bind(thread_id)
451 .execute(&h.db)
452 .await
453 .unwrap();
454 assert!(
455 matches!(
456 mt_db::mutations::create_post(&h.db, thread_id, author, "r", "<p>r</p>").await,
457 Err(sqlx::Error::RowNotFound)
458 ),
459 "a reply must not insert on a soft-deleted thread"
460 );
461
462 // Restored to live → the reply lands, proving the guard only blocks the bad states.
463 sqlx::query("UPDATE threads SET deleted_at = NULL WHERE id = $1")
464 .bind(thread_id)
465 .execute(&h.db)
466 .await
467 .unwrap();
468 assert!(
469 mt_db::mutations::create_post(&h.db, thread_id, author, "r", "<p>r</p>")
470 .await
471 .is_ok(),
472 "a live, unlocked thread must still accept the reply"
473 );
474
475 // The post_count trigger only counted the one successful insert.
476 let post_count: i32 = sqlx::query_scalar("SELECT post_count FROM threads WHERE id = $1")
477 .bind(thread_id)
478 .fetch_one(&h.db)
479 .await
480 .unwrap();
481 assert_eq!(
482 post_count, 1,
483 "only the successful reply may bump post_count"
484 );
485 }
486
487 // Endorsement mutations
488
489 #[tokio::test]
490 async fn toggle_endorsement_db_roundtrip() {
491 let h = TestHarness::new().await;
492 let comm_id = h.create_community("Test", "test").await;
493 let cat_id = h.create_category(comm_id, "General", "general").await;
494
495 let author = Uuid::new_v4();
496 let endorser = Uuid::new_v4();
497 for (id, name) in [(author, "author"), (endorser, "endorser")] {
498 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
499 .bind(id)
500 .bind(name)
501 .execute(&h.db)
502 .await
503 .unwrap();
504 }
505
506 let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Test")
507 .await
508 .unwrap();
509 let post_id =
510 mt_db::mutations::create_post(&h.db, thread_id, author, "content", "<p>content</p>")
511 .await
512 .unwrap();
513
514 // First toggle: endorse
515 let result = mt_db::mutations::toggle_endorsement(&h.db, post_id, endorser)
516 .await
517 .unwrap();
518 assert!(result, "First toggle should endorse (return true)");
519
520 // Second toggle: un-endorse
521 let result = mt_db::mutations::toggle_endorsement(&h.db, post_id, endorser)
522 .await
523 .unwrap();
524 assert!(!result, "Second toggle should un-endorse (return false)");
525
526 // Third toggle: endorse again
527 let result = mt_db::mutations::toggle_endorsement(&h.db, post_id, endorser)
528 .await
529 .unwrap();
530 assert!(result, "Third toggle should endorse again (return true)");
531 }
532
533 // Flag mutations
534
535 #[tokio::test]
536 async fn create_post_external_ref_is_idempotent() {
537 // The internal reply path dedups on external_ref: a retried/replayed call
538 // returns the existing post and never inserts a duplicate (audit E1).
539 let h = TestHarness::new().await;
540 let comm_id = h.create_community("Test", "test").await;
541 let cat_id = h.create_category(comm_id, "General", "general").await;
542
543 let author = Uuid::new_v4();
544 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
545 .bind(author)
546 .bind("author")
547 .execute(&h.db)
548 .await
549 .unwrap();
550
551 let (thread_id, _op) =
552 mt_db::mutations::create_thread_with_op(&h.db, cat_id, author, "T", "op", "<p>op</p>")
553 .await
554 .unwrap();
555
556 let count = || async {
557 let n: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM posts WHERE thread_id = $1")
558 .bind(thread_id)
559 .fetch_one(&h.db)
560 .await
561 .unwrap();
562 n.0
563 };
564 let activity = || async {
565 let a: (chrono::DateTime<chrono::Utc>,) =
566 sqlx::query_as("SELECT last_activity_at FROM threads WHERE id = $1")
567 .bind(thread_id)
568 .fetch_one(&h.db)
569 .await
570 .unwrap();
571 a.0
572 };
573
574 // First call: fresh reply, created = true.
575 let (id1, created1) = mt_db::mutations::create_post_external_ref(
576 &h.db,
577 thread_id,
578 author,
579 "mnw:post:msg-1",
580 "reply",
581 "<p>reply</p>",
582 )
583 .await
584 .unwrap();
585 assert!(created1, "first call creates the reply");
586 assert_eq!(count().await, 2, "OP + one reply");
587 let count_for_trigger: (i32,) = sqlx::query_as("SELECT post_count FROM threads WHERE id = $1")
588 .bind(thread_id)
589 .fetch_one(&h.db)
590 .await
591 .unwrap();
592 assert_eq!(
593 count_for_trigger.0, 2,
594 "m029 trigger counted the reply once"
595 );
596 let activity_after_first = activity().await;
597
598 // Replay with the same external_ref: same post id, created = false, no dup,
599 // and last_activity_at is NOT re-bumped.
600 let (id2, created2) = mt_db::mutations::create_post_external_ref(
601 &h.db,
602 thread_id,
603 author,
604 "mnw:post:msg-1",
605 "reply",
606 "<p>reply</p>",
607 )
608 .await
609 .unwrap();
610 assert_eq!(id1, id2, "replay returns the original post id");
611 assert!(!created2, "replay does not create");
612 assert_eq!(count().await, 2, "no duplicate reply inserted");
613 assert_eq!(
614 activity().await,
615 activity_after_first,
616 "replay does not re-bump activity"
617 );
618
619 // A distinct external_ref does insert a new reply.
620 let (id3, created3) = mt_db::mutations::create_post_external_ref(
621 &h.db,
622 thread_id,
623 author,
624 "mnw:post:msg-2",
625 "reply2",
626 "<p>reply2</p>",
627 )
628 .await
629 .unwrap();
630 assert_ne!(id3, id1);
631 assert!(created3);
632 assert_eq!(count().await, 3, "distinct ref adds a reply");
633 }
634
635 #[tokio::test]
636 async fn insert_flag_idempotent_per_user() {
637 let h = TestHarness::new().await;
638 let comm_id = h.create_community("Test", "test").await;
639 let cat_id = h.create_category(comm_id, "General", "general").await;
640
641 let author = Uuid::new_v4();
642 let flagger = Uuid::new_v4();
643 for (id, name) in [(author, "author"), (flagger, "flagger")] {
644 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
645 .bind(id)
646 .bind(name)
647 .execute(&h.db)
648 .await
649 .unwrap();
650 }
651
652 let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Test")
653 .await
654 .unwrap();
655 let post_id =
656 mt_db::mutations::create_post(&h.db, thread_id, author, "content", "<p>content</p>")
657 .await
658 .unwrap();
659
660 // First flag
661 mt_db::mutations::insert_flag(&h.db, post_id, flagger, "spam", None)
662 .await
663 .unwrap();
664
665 // Second flag from same user, ON CONFLICT DO NOTHING
666 mt_db::mutations::insert_flag(&h.db, post_id, flagger, "off_topic", Some("detail"))
667 .await
668 .unwrap();
669
670 let count: i64 = sqlx::query_scalar(
671 "SELECT COUNT(*) FROM post_flags WHERE post_id = $1 AND flagger_id = $2",
672 )
673 .bind(post_id)
674 .bind(flagger)
675 .fetch_one(&h.db)
676 .await
677 .unwrap();
678 assert_eq!(count, 1, "Should have exactly 1 flag per user per post");
679 }
680
681 // Image mutations
682
683 #[tokio::test]
684 async fn remove_image_marks_removed() {
685 let h = TestHarness::new().await;
686 let comm_id = h.create_community("Test", "test").await;
687
688 let uploader = Uuid::new_v4();
689 let moderator = Uuid::new_v4();
690 for (id, name) in [(uploader, "uploader"), (moderator, "moderator")] {
691 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
692 .bind(id)
693 .bind(name)
694 .execute(&h.db)
695 .await
696 .unwrap();
697 }
698
699 let image_id = mt_db::mutations::insert_image(
700 &h.db,
701 uploader,
702 comm_id,
703 "s3/key.jpg",
704 "photo.jpg",
705 "image/jpeg",
706 12345,
707 )
708 .await
709 .unwrap();
710
711 // Verify not removed
712 let removed: bool =
713 sqlx::query_scalar("SELECT removed_at IS NOT NULL FROM images WHERE id = $1")
714 .bind(image_id)
715 .fetch_one(&h.db)
716 .await
717 .unwrap();
718 assert!(!removed, "Should not be removed initially");
719
720 mt_db::mutations::remove_image(&h.db, image_id, moderator)
721 .await
722 .unwrap();
723
724 let (removed, removed_by): (bool, Option<Uuid>) =
725 sqlx::query_as("SELECT removed_at IS NOT NULL, removed_by FROM images WHERE id = $1")
726 .bind(image_id)
727 .fetch_one(&h.db)
728 .await
729 .unwrap();
730 assert!(removed, "Should be marked as removed");
731 assert_eq!(
732 removed_by,
733 Some(moderator),
734 "removed_by should match moderator"
735 );
736 }
737
738 #[tokio::test]
739 async fn remove_image_idempotent() {
740 let h = TestHarness::new().await;
741 let comm_id = h.create_community("Test", "test").await;
742
743 let uploader = Uuid::new_v4();
744 let mod1 = Uuid::new_v4();
745 let mod2 = Uuid::new_v4();
746 for (id, name) in [(uploader, "uploader"), (mod1, "mod1"), (mod2, "mod2")] {
747 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
748 .bind(id)
749 .bind(name)
750 .execute(&h.db)
751 .await
752 .unwrap();
753 }
754
755 let image_id = mt_db::mutations::insert_image(
756 &h.db,
757 uploader,
758 comm_id,
759 "s3/key.jpg",
760 "photo.jpg",
761 "image/jpeg",
762 100,
763 )
764 .await
765 .unwrap();
766
767 // First remove
768 mt_db::mutations::remove_image(&h.db, image_id, mod1)
769 .await
770 .unwrap();
771
772 // Second remove (different mod), should be no-op (WHERE removed_at IS NULL)
773 mt_db::mutations::remove_image(&h.db, image_id, mod2)
774 .await
775 .unwrap();
776
777 // Verify original remover preserved
778 let removed_by: Option<Uuid> =
779 sqlx::query_scalar("SELECT removed_by FROM images WHERE id = $1")
780 .bind(image_id)
781 .fetch_one(&h.db)
782 .await
783 .unwrap();
784 assert_eq!(removed_by, Some(mod1), "First remover should be preserved");
785 }
786
787 // Link preview mutations
788
789 #[tokio::test]
790 async fn insert_link_preview_dedup() {
791 let h = TestHarness::new().await;
792 let comm_id = h.create_community("Test", "test").await;
793 let cat_id = h.create_category(comm_id, "General", "general").await;
794
795 let author = Uuid::new_v4();
796 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
797 .bind(author)
798 .bind("author")
799 .execute(&h.db)
800 .await
801 .unwrap();
802
803 let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Test")
804 .await
805 .unwrap();
806 let post_id =
807 mt_db::mutations::create_post(&h.db, thread_id, author, "content", "<p>content</p>")
808 .await
809 .unwrap();
810
811 mt_db::mutations::insert_link_preview(
812 &h.db,
813 post_id,
814 "https://example.com",
815 Some("Example"),
816 Some("A site"),
817 )
818 .await
819 .unwrap();
820
821 // Duplicate, ON CONFLICT DO NOTHING
822 mt_db::mutations::insert_link_preview(
823 &h.db,
824 post_id,
825 "https://example.com",
826 Some("Different Title"),
827 None,
828 )
829 .await
830 .unwrap();
831
832 let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM link_previews WHERE post_id = $1")
833 .bind(post_id)
834 .fetch_one(&h.db)
835 .await
836 .unwrap();
837 assert_eq!(
838 count, 1,
839 "Should have exactly 1 link preview per URL per post"
840 );
841 }
842
843 // Mention mutations
844
845 #[tokio::test]
846 async fn insert_mentions_dedup() {
847 let h = TestHarness::new().await;
848 let comm_id = h.create_community("Test", "test").await;
849 let cat_id = h.create_category(comm_id, "General", "general").await;
850
851 let author = Uuid::new_v4();
852 let mentioned = Uuid::new_v4();
853 for (id, name) in [(author, "author"), (mentioned, "mentioned")] {
854 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
855 .bind(id)
856 .bind(name)
857 .execute(&h.db)
858 .await
859 .unwrap();
860 }
861
862 let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Test")
863 .await
864 .unwrap();
865 let post_id =
866 mt_db::mutations::create_post(&h.db, thread_id, author, "content", "<p>content</p>")
867 .await
868 .unwrap();
869
870 mt_db::mutations::insert_mentions(&h.db, post_id, &[mentioned])
871 .await
872 .unwrap();
873
874 // Insert same mention again, ON CONFLICT DO NOTHING
875 mt_db::mutations::insert_mentions(&h.db, post_id, &[mentioned])
876 .await
877 .unwrap();
878
879 let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM post_mentions WHERE post_id = $1")
880 .bind(post_id)
881 .fetch_one(&h.db)
882 .await
883 .unwrap();
884 assert_eq!(
885 count, 1,
886 "Should have exactly 1 mention row per user per post"
887 );
888 }
889
890 // Upsert user
891
892 #[tokio::test]
893 async fn upsert_user_updates_on_conflict() {
894 let h = TestHarness::new().await;
895 let user_id = Uuid::new_v4();
896
897 mt_db::mutations::upsert_user(&h.db, user_id, "oldname", Some("Old Display"))
898 .await
899 .unwrap();
900
901 // Upsert with new values
902 mt_db::mutations::upsert_user(&h.db, user_id, "newname", Some("New Display"))
903 .await
904 .unwrap();
905
906 let (username, display_name): (String, Option<String>) =
907 sqlx::query_as("SELECT username, display_name FROM users WHERE mnw_account_id = $1")
908 .bind(user_id)
909 .fetch_one(&h.db)
910 .await
911 .unwrap();
912
913 assert_eq!(username, "newname");
914 assert_eq!(display_name.as_deref(), Some("New Display"));
915 }
916
917 #[tokio::test]
918 async fn s3_purge_sweep_targets_only_removed_unpurged_images() {
919 let h = TestHarness::new().await;
920 let comm_id = h.create_community("Test", "test").await;
921
922 let uploader = Uuid::new_v4();
923 let moderator = Uuid::new_v4();
924 for (id, name) in [(uploader, "purgeuploader"), (moderator, "purgemod")] {
925 sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
926 .bind(id)
927 .bind(name)
928 .execute(&h.db)
929 .await
930 .unwrap();
931 }
932
933 // A live image and a removed image.
934 let live = mt_db::mutations::insert_image(
935 &h.db,
936 uploader,
937 comm_id,
938 "s3/live.jpg",
939 "live.jpg",
940 "image/jpeg",
941 100,
942 )
943 .await
944 .unwrap();
945 let removed = mt_db::mutations::insert_image(
946 &h.db,
947 uploader,
948 comm_id,
949 "s3/removed.jpg",
950 "removed.jpg",
951 "image/jpeg",
952 100,
953 )
954 .await
955 .unwrap();
956 mt_db::mutations::remove_image(&h.db, removed, moderator)
957 .await
958 .unwrap();
959
960 // Only the removed-and-unpurged image is pending purge.
961 let pending = mt_db::queries::list_images_pending_s3_purge(&h.db, 100)
962 .await
963 .unwrap();
964 let ids: Vec<Uuid> = pending.iter().map(|p| p.id).collect();
965 assert!(
966 ids.contains(&removed),
967 "removed image must be pending purge"
968 );
969 assert!(!ids.contains(&live), "live image must not be pending purge");
970 assert_eq!(
971 pending
972 .iter()
973 .find(|p| p.id == removed)
974 .map(|p| p.s3_key.as_str()),
975 Some("s3/removed.jpg"),
976 "pending row must carry the S3 key for deletion"
977 );
978
979 // After marking purged, it drops out of the sweep, convergent.
980 mt_db::mutations::mark_images_s3_purged(&h.db, &[removed])
981 .await
982 .unwrap();
983 let pending = mt_db::queries::list_images_pending_s3_purge(&h.db, 100)
984 .await
985 .unwrap();
986 assert!(
987 !pending.iter().any(|p| p.id == removed),
988 "purged image must not be revisited by the sweep"
989 );
990
991 // Marking an empty batch is a no-op (sweep's failed-batch path).
992 mt_db::mutations::mark_images_s3_purged(&h.db, &[])
993 .await
994 .unwrap();
995 }
996
997 // Username-collision reconciliation (ultra-fuzz S2)
998
999 /// A login that renames into a username a *stale* mirror row still holds must
1000 /// succeed, not 23505-lockout. The stale row yields the name (MNW is the source
1001 /// of truth; usernames are reusable there) and keeps its data under a
1002 /// collision-proof `mnw_<account-id>` placeholder.
1003 #[tokio::test]
1004 async fn upsert_user_reclaims_username_from_stale_row() {
1005 let h = TestHarness::new().await;
1006 let old = Uuid::new_v4();
1007 let new = Uuid::new_v4();
1008
1009 // `old` currently owns "shared".
1010 mt_db::mutations::upsert_user(&h.db, old, "shared", None)
1011 .await
1012 .unwrap();
1013
1014 // `new` logs in having taken the name "shared" upstream. This must not error.
1015 mt_db::mutations::upsert_user(&h.db, new, "shared", None)
1016 .await
1017 .expect("rename into a name a stale row holds must not lock the user out");
1018
1019 // `new` now owns "shared"; `old` was vacated to its placeholder.
1020 let owner: Uuid =
1021 sqlx::query_scalar("SELECT mnw_account_id FROM users WHERE username = 'shared'")
1022 .fetch_one(&h.db)
1023 .await
1024 .unwrap();
1025 assert_eq!(owner, new, "the current login must own the reused username");
1026
1027 let old_name: String =
1028 sqlx::query_scalar("SELECT username FROM users WHERE mnw_account_id = $1")
1029 .bind(old)
1030 .fetch_one(&h.db)
1031 .await
1032 .unwrap();
1033 assert_eq!(
1034 old_name,
1035 format!("mnw_{old}"),
1036 "stale row keeps its data under a placeholder name"
1037 );
1038 }
1039
1040 /// Re-upserting the same account with its own unchanged username is a no-op rename
1041 /// (the `mnw_account_id <> $2` guard skips self), not a self-collision.
1042 #[tokio::test]
1043 async fn upsert_user_same_account_same_name_is_stable() {
1044 let h = TestHarness::new().await;
1045 let acct = Uuid::new_v4();
1046 mt_db::mutations::upsert_user(&h.db, acct, "stable", Some("Display"))
1047 .await
1048 .unwrap();
1049 mt_db::mutations::upsert_user(&h.db, acct, "stable", Some("Display Two"))
1050 .await
1051 .unwrap();
1052
1053 let name: String = sqlx::query_scalar("SELECT username FROM users WHERE mnw_account_id = $1")
1054 .bind(acct)
1055 .fetch_one(&h.db)
1056 .await
1057 .unwrap();
1058 assert_eq!(name, "stable");
1059 }
1060
1061 /// `posts.is_active` (migration 034) is the canonical live-post predicate. These
1062 /// pin the two properties that make it worth having over a hand-copied
1063 /// `removed_at IS NULL AND deleted_at IS NULL` pair: it tracks BOTH columns, and
1064 /// it cannot be set independently of them.
1065 #[tokio::test]
1066 async fn posts_is_active_tracks_both_soft_delete_columns() {
1067 let mut h = TestHarness::new().await;
1068 let author_id = h.login_as("isactiveauthor").await;
1069 let comm_id = h.create_community("Test", "test").await;
1070 let cat_id = h.create_category(comm_id, "General", "general").await;
1071 h.add_membership(author_id, comm_id, "member").await;
1072
1073 let thread_id = h
1074 .create_thread_with_post(cat_id, author_id, "Active", "Content")
1075 .await;
1076 let post_id = mt_db::queries::list_posts_in_thread(&h.db, thread_id)
1077 .await
1078 .unwrap()[0]
1079 .id;
1080
1081 let active = |id: Uuid, db: sqlx::PgPool| async move {
1082 sqlx::query_scalar::<_, bool>("SELECT is_active FROM posts WHERE id = $1")
1083 .bind(id)
1084 .fetch_one(&db)
1085 .await
1086 .unwrap()
1087 };
1088
1089 assert!(
1090 active(post_id, h.db.clone()).await,
1091 "a fresh post is active"
1092 );
1093
1094 // The mod column alone flips it.
1095 sqlx::query("UPDATE posts SET removed_at = now() WHERE id = $1")
1096 .bind(post_id)
1097 .execute(&h.db)
1098 .await
1099 .unwrap();
1100 assert!(
1101 !active(post_id, h.db.clone()).await,
1102 "removed_at deactivates"
1103 );
1104
1105 sqlx::query("UPDATE posts SET removed_at = NULL WHERE id = $1")
1106 .bind(post_id)
1107 .execute(&h.db)
1108 .await
1109 .unwrap();
1110 assert!(
1111 active(post_id, h.db.clone()).await,
1112 "clearing it reactivates"
1113 );
1114
1115 // The author column alone flips it too. This is the half that drifted at
1116 // five call sites, because it is dormant and nothing catches a missing check.
1117 sqlx::query("UPDATE posts SET deleted_at = now() WHERE id = $1")
1118 .bind(post_id)
1119 .execute(&h.db)
1120 .await
1121 .unwrap();
1122 assert!(
1123 !active(post_id, h.db.clone()).await,
1124 "deleted_at must deactivate too, not just removed_at"
1125 );
1126 }
1127
1128 #[tokio::test]
1129 async fn posts_is_active_cannot_be_written_directly() {
1130 let mut h = TestHarness::new().await;
1131 let author_id = h.login_as("nowriteauthor").await;
1132 let comm_id = h.create_community("Test", "test").await;
1133 let cat_id = h.create_category(comm_id, "General", "general").await;
1134 h.add_membership(author_id, comm_id, "member").await;
1135
1136 let thread_id = h
1137 .create_thread_with_post(cat_id, author_id, "NoWrite", "Content")
1138 .await;
1139 let post_id = mt_db::queries::list_posts_in_thread(&h.db, thread_id)
1140 .await
1141 .unwrap()[0]
1142 .id;
1143
1144 sqlx::query("UPDATE posts SET removed_at = now() WHERE id = $1")
1145 .bind(post_id)
1146 .execute(&h.db)
1147 .await
1148 .unwrap();
1149
1150 // Postgres refuses the write, so the column can never disagree with the two
1151 // it is derived from. This is the property a shared SQL fragment could not
1152 // have given us.
1153 let err = sqlx::query("UPDATE posts SET is_active = true WHERE id = $1")
1154 .bind(post_id)
1155 .execute(&h.db)
1156 .await
1157 .expect_err("a generated column must reject a direct write");
1158 assert!(
1159 err.to_string().contains("can only be updated to DEFAULT"),
1160 "expected Postgres to reject the write to a generated column, got: {err}"
1161 );
1162
1163 let still_inactive: bool = sqlx::query_scalar("SELECT is_active FROM posts WHERE id = $1")
1164 .bind(post_id)
1165 .fetch_one(&h.db)
1166 .await
1167 .unwrap();
1168 assert!(!still_inactive, "the removed post must stay inactive");
1169 }
1170