Skip to main content

max / makenotwork

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