Skip to main content

max / makenotwork

8.4 KB · 258 lines History Blame Raw
1 use super::{BanType, DateTime, ModAction, PgPool, Utc, Uuid};
2
3 /// Check if a user is platform-suspended (by admin).
4 #[tracing::instrument(skip_all)]
5 pub async fn is_user_suspended(pool: &PgPool, user_id: Uuid) -> Result<bool, sqlx::Error> {
6 sqlx::query_scalar!(
7 r#"SELECT EXISTS(SELECT 1 FROM users WHERE mnw_account_id = $1 AND suspended_at IS NOT NULL) AS "exists!""#,
8 user_id,
9 )
10 .fetch_one(pool)
11 .await
12 }
13
14 /// Check if user has an active ban in a community.
15 #[tracing::instrument(skip_all)]
16 pub async fn is_user_banned(
17 pool: &PgPool,
18 community_id: Uuid,
19 user_id: Uuid,
20 ) -> Result<bool, sqlx::Error> {
21 sqlx::query_scalar!(
22 r#"SELECT EXISTS(SELECT 1 FROM community_bans
23 WHERE community_id = $1 AND user_id = $2 AND ban_type = 'ban'
24 AND (expires_at IS NULL OR expires_at > now())) AS "exists!""#,
25 community_id,
26 user_id,
27 )
28 .fetch_one(pool)
29 .await
30 }
31
32 /// Check if user has an active mute in a community.
33 #[tracing::instrument(skip_all)]
34 pub async fn is_user_muted(
35 pool: &PgPool,
36 community_id: Uuid,
37 user_id: Uuid,
38 ) -> Result<bool, sqlx::Error> {
39 sqlx::query_scalar!(
40 r#"SELECT EXISTS(SELECT 1 FROM community_bans
41 WHERE community_id = $1 AND user_id = $2 AND ban_type = 'mute'
42 AND (expires_at IS NULL OR expires_at > now())) AS "exists!""#,
43 community_id,
44 user_id,
45 )
46 .fetch_one(pool)
47 .await
48 }
49
50 #[derive(sqlx::FromRow)]
51 pub struct CommunityBanRow {
52 pub id: Uuid,
53 pub user_id: Uuid,
54 pub username: String,
55 pub display_name: Option<String>,
56 pub ban_type: BanType,
57 pub reason: Option<String>,
58 pub expires_at: Option<DateTime<Utc>>,
59 pub created_at: DateTime<Utc>,
60 pub banned_by_username: String,
61 }
62
63 /// List active bans and mutes in a community, newest first, bounded by `limit`.
64 /// The moderation page caps the read so a community with a large ban backlog
65 /// can't make a single page load materialize an unbounded result set.
66 #[tracing::instrument(skip_all)]
67 pub async fn list_community_bans(
68 pool: &PgPool,
69 community_id: Uuid,
70 limit: i64,
71 ) -> Result<Vec<CommunityBanRow>, sqlx::Error> {
72 sqlx::query_as!(
73 CommunityBanRow,
74 r#"SELECT cb.id, cb.user_id,
75 u.username, u.display_name,
76 cb.ban_type AS "ban_type: BanType", cb.reason,
77 cb.expires_at AS "expires_at: chrono::DateTime<chrono::Utc>",
78 cb.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
79 actor.username AS banned_by_username
80 FROM community_bans cb
81 JOIN users u ON u.mnw_account_id = cb.user_id
82 JOIN users actor ON actor.mnw_account_id = cb.banned_by
83 WHERE cb.community_id = $1
84 AND (cb.expires_at IS NULL OR cb.expires_at > now())
85 ORDER BY cb.created_at DESC
86 LIMIT $2"#,
87 community_id,
88 limit,
89 )
90 .fetch_all(pool)
91 .await
92 }
93
94 #[derive(sqlx::FromRow)]
95 pub struct ModLogEntry {
96 pub id: Uuid,
97 pub actor_username: String,
98 pub action: ModAction,
99 pub target_username: Option<String>,
100 pub reason: Option<String>,
101 pub created_at: DateTime<Utc>,
102 }
103
104 /// List mod log entries for a community, paginated, newest first.
105 #[tracing::instrument(skip_all)]
106 pub async fn list_mod_log(
107 pool: &PgPool,
108 community_id: Uuid,
109 limit: i64,
110 offset: i64,
111 ) -> Result<Vec<ModLogEntry>, sqlx::Error> {
112 sqlx::query_as!(
113 ModLogEntry,
114 // LEFT JOIN + a "System" label for a NULL actor_id: migration 032 made
115 // actor_id nullable precisely so auto-hide (flag-threshold) removals could
116 // be recorded with no human actor. An INNER JOIN silently dropped every
117 // such row, the auto-moderation audit trail the migration exists to keep,
118 // while count_mod_log counted them, so pagination overcounted. COALESCE
119 // is non-null, hence the `!` (see fuzz-2026-07-06 top DB fix).
120 r#"SELECT ml.id,
121 COALESCE(actor.username, 'System') AS "actor_username!",
122 ml.action AS "action: ModAction",
123 target.username AS "target_username?",
124 ml.reason,
125 ml.created_at AS "created_at: chrono::DateTime<chrono::Utc>"
126 FROM mod_log ml
127 LEFT JOIN users actor ON actor.mnw_account_id = ml.actor_id
128 LEFT JOIN users target ON target.mnw_account_id = ml.target_user
129 WHERE ml.community_id = $1
130 ORDER BY ml.created_at DESC
131 LIMIT $2 OFFSET $3"#,
132 community_id,
133 limit,
134 offset,
135 )
136 .fetch_all(pool)
137 .await
138 }
139
140 /// Count mod log entries for a community.
141 #[tracing::instrument(skip_all)]
142 pub async fn count_mod_log(pool: &PgPool, community_id: Uuid) -> Result<i64, sqlx::Error> {
143 sqlx::query_scalar!(
144 r#"SELECT COUNT(*) AS "count!" FROM mod_log WHERE community_id = $1"#,
145 community_id,
146 )
147 .fetch_one(pool)
148 .await
149 }
150
151 #[derive(sqlx::FromRow)]
152 pub struct PendingFlagRow {
153 pub flag_id: Uuid,
154 pub post_id: Uuid,
155 pub thread_id: Uuid,
156 pub thread_title: String,
157 pub category_slug: String,
158 pub flagger_username: String,
159 pub reason: String,
160 pub detail: Option<String>,
161 pub created_at: DateTime<Utc>,
162 }
163
164 /// List unresolved flags in a community, newest first, bounded by `limit`.
165 /// The read is capped so a coordinated flag-spam backlog can't make the
166 /// moderation page build an unbounded result set on every load; mods clear the
167 /// newest flags and the next batch surfaces as they're resolved.
168 #[tracing::instrument(skip_all)]
169 pub async fn list_pending_flags(
170 pool: &PgPool,
171 community_id: Uuid,
172 limit: i64,
173 ) -> Result<Vec<PendingFlagRow>, sqlx::Error> {
174 sqlx::query_as!(
175 PendingFlagRow,
176 r#"SELECT f.id AS flag_id, f.post_id, p.thread_id,
177 t.title AS thread_title,
178 cat.slug AS category_slug,
179 u.username AS flagger_username,
180 f.reason, f.detail,
181 f.created_at AS "created_at: chrono::DateTime<chrono::Utc>"
182 FROM post_flags f
183 JOIN posts p ON p.id = f.post_id
184 JOIN threads t ON t.id = p.thread_id
185 JOIN categories cat ON cat.id = t.category_id
186 JOIN users u ON u.mnw_account_id = f.flagger_id
187 WHERE cat.community_id = $1 AND f.resolved_at IS NULL
188 ORDER BY f.created_at DESC
189 LIMIT $2"#,
190 community_id,
191 limit,
192 )
193 .fetch_all(pool)
194 .await
195 }
196
197 /// Check if a user has already flagged a specific post.
198 #[tracing::instrument(skip_all)]
199 pub async fn has_user_flagged_post(
200 pool: &PgPool,
201 post_id: Uuid,
202 flagger_id: Uuid,
203 ) -> Result<bool, sqlx::Error> {
204 sqlx::query_scalar!(
205 r#"SELECT EXISTS(SELECT 1 FROM post_flags WHERE post_id = $1 AND flagger_id = $2) AS "exists!""#,
206 post_id,
207 flagger_id,
208 )
209 .fetch_one(pool)
210 .await
211 }
212
213 /// Check if a flag belongs to a given community (via post → thread → category chain).
214 #[tracing::instrument(skip_all)]
215 pub async fn flag_belongs_to_community(
216 pool: &PgPool,
217 flag_id: Uuid,
218 community_id: Uuid,
219 ) -> Result<bool, sqlx::Error> {
220 sqlx::query_scalar!(
221 r#"SELECT EXISTS(
222 SELECT 1 FROM post_flags pf
223 JOIN posts p ON p.id = pf.post_id
224 JOIN threads t ON t.id = p.thread_id
225 JOIN categories c ON c.id = t.category_id
226 WHERE pf.id = $1 AND c.community_id = $2
227 ) AS "exists!""#,
228 flag_id,
229 community_id,
230 )
231 .fetch_one(pool)
232 .await
233 }
234
235 /// Post-removal target for a flag, scoped to a community: `(post_id, author_id,
236 /// thread_id)`. Returns `None` if the flag doesn't exist or belongs to another
237 /// community, the community scoping is enforced here rather than in the handler.
238 #[tracing::instrument(skip_all)]
239 pub async fn get_flag_removal_target(
240 pool: &PgPool,
241 flag_id: Uuid,
242 community_id: Uuid,
243 ) -> Result<Option<(Uuid, Uuid, Uuid)>, sqlx::Error> {
244 let row = sqlx::query!(
245 r#"SELECT pf.post_id AS "post_id!", p.author_id AS "author_id!", t.id AS "thread_id!"
246 FROM post_flags pf
247 JOIN posts p ON p.id = pf.post_id
248 JOIN threads t ON t.id = p.thread_id
249 JOIN categories c ON c.id = t.category_id
250 WHERE pf.id = $1 AND c.community_id = $2"#,
251 flag_id,
252 community_id,
253 )
254 .fetch_optional(pool)
255 .await?;
256 Ok(row.map(|r| (r.post_id, r.author_id, r.thread_id)))
257 }
258