Skip to main content

max / makenotwork

7.0 KB · 205 lines History Blame Raw
1 //! Report queries: create, list, resolve, and count user-submitted reports.
2
3 use sqlx::PgPool;
4 use uuid::Uuid;
5
6 use super::enums::{ReportStatus, ReportTargetType, ReportType};
7 use super::id_types::{ReportId, UserId};
8 use super::models::{DbAdminReportRow, DbReport, DbReportStats};
9 use crate::error::{AppError, Result};
10
11 /// Get reports for the admin queue, joined with reporter, target, and owner info.
12 ///
13 /// Uses two sub-queries (one for projects, one for items) to resolve target details,
14 /// then UNIONs and filters. This avoids N+1 queries.
15 #[tracing::instrument(skip_all)]
16 pub(crate) async fn get_admin_reports(
17 pool: &PgPool,
18 status_filter: Option<&str>,
19 limit: i64,
20 offset: i64,
21 ) -> Result<Vec<DbAdminReportRow>> {
22 let rows = sqlx::query_as::<_, DbAdminReportRow>(
23 r"
24 SELECT
25 r.id,
26 u.username AS reporter_username,
27 r.target_type,
28 COALESCE(
29 CASE WHEN r.target_type = 'project' THEN p.title END,
30 CASE WHEN r.target_type = 'item' THEN i.title END,
31 '(deleted)'
32 ) AS target_title,
33 COALESCE(
34 CASE WHEN r.target_type = 'project' THEN p.slug::TEXT END,
35 CASE WHEN r.target_type = 'item' THEN r.target_id::TEXT END,
36 ''
37 ) AS target_slug_or_id,
38 COALESCE(
39 CASE WHEN r.target_type = 'project' THEN pu.username END,
40 CASE WHEN r.target_type = 'item' THEN iu.username END,
41 '(unknown)'
42 ) AS target_owner,
43 -- Custom-page source that rendered for this target, so moderators
44 -- see what was published. Items inherit their parent project's CSS.
45 CASE WHEN r.target_type = 'project' THEN p.custom_html
46 WHEN r.target_type = 'item' THEN ip.custom_html END AS target_custom_html,
47 CASE WHEN r.target_type = 'project' THEN p.custom_css
48 WHEN r.target_type = 'item' THEN ip.custom_css END AS target_custom_css,
49 r.report_type,
50 r.reason,
51 r.status,
52 r.admin_notes,
53 r.created_at,
54 r.resolved_at
55 FROM reports r
56 JOIN users u ON u.id = r.reporter_user_id
57 LEFT JOIN projects p ON r.target_type = 'project' AND p.id = r.target_id
58 LEFT JOIN users pu ON p.user_id = pu.id
59 LEFT JOIN items i ON r.target_type = 'item' AND i.id = r.target_id
60 LEFT JOIN projects ip ON i.project_id = ip.id
61 LEFT JOIN users iu ON ip.user_id = iu.id
62 WHERE ($1::TEXT IS NULL OR r.status = $1)
63 ORDER BY r.created_at DESC
64 LIMIT $2 OFFSET $3
65 ",
66 )
67 .bind(status_filter)
68 .bind(limit)
69 .bind(offset)
70 .fetch_all(pool)
71 .await?;
72
73 Ok(rows)
74 }
75
76 /// Get aggregate report stats.
77 #[tracing::instrument(skip_all)]
78 pub(crate) async fn get_report_stats(pool: &PgPool) -> Result<DbReportStats> {
79 let stats = sqlx::query_as::<_, DbReportStats>(
80 r"
81 SELECT
82 COUNT(*) FILTER (WHERE status = 'open') AS open,
83 COUNT(*) FILTER (WHERE status = 'resolved') AS resolved,
84 COUNT(*) FILTER (WHERE status = 'dismissed') AS dismissed
85 FROM reports
86 ",
87 )
88 .fetch_one(pool)
89 .await?;
90
91 Ok(stats)
92 }
93
94 /// Resolve or dismiss a report.
95 #[tracing::instrument(skip_all)]
96 pub(crate) async fn resolve_report(
97 pool: &PgPool,
98 id: ReportId,
99 status: ReportStatus,
100 admin_notes: &str,
101 resolved_by: crate::auth::AdminId,
102 ) -> Result<()> {
103 let result = sqlx::query(
104 r"
105 UPDATE reports
106 SET status = $2, admin_notes = $3, resolved_by = $4, resolved_at = NOW()
107 WHERE id = $1
108 ",
109 )
110 .bind(id)
111 .bind(status)
112 .bind(admin_notes)
113 .bind(resolved_by.get())
114 .execute(pool)
115 .await?;
116
117 // No matching row means the report id was stale (already resolved away, or
118 // never existed). Surface it as NotFound so the admin sees a real failure
119 // instead of a false "resolved", the UPDATE silently affecting zero rows
120 // otherwise reports success.
121 if result.rows_affected() == 0 {
122 return Err(AppError::NotFound);
123 }
124
125 Ok(())
126 }
127
128 /// Create a report only if the reporter is under the daily cap, atomically.
129 ///
130 /// Returns `Ok(None)` when the reporter already has `>= max_per_day` reports in
131 /// the trailing 24h. The count and the insert run in one transaction under a
132 /// per-reporter `pg_advisory_xact_lock`, so a single user's concurrent submits
133 /// can't both observe `count = max-1` and both insert (the count-then-insert
134 /// TOCTOU the previous two-call sequence had). The lock is keyed on the
135 /// reporter id, so contention is scoped to one user's own concurrent requests,
136 /// it never blocks unrelated traffic, and it auto-releases at commit/rollback.
137 #[tracing::instrument(skip_all)]
138 pub(crate) async fn create_report_within_daily_limit(
139 pool: &PgPool,
140 reporter_id: UserId,
141 target_type: ReportTargetType,
142 target_id: Uuid,
143 report_type: ReportType,
144 reason: &str,
145 max_per_day: i64,
146 ) -> Result<Option<DbReport>> {
147 let mut tx = pool.begin().await?;
148
149 sqlx::query("SELECT pg_advisory_xact_lock(hashtextextended($1::text, 0))")
150 .bind(reporter_id)
151 .execute(&mut *tx)
152 .await?;
153
154 let recent: i64 = sqlx::query_scalar(
155 "SELECT COUNT(*) FROM reports WHERE reporter_user_id = $1 AND created_at > NOW() - INTERVAL '24 hours'",
156 )
157 .bind(reporter_id)
158 .fetch_one(&mut *tx)
159 .await?;
160
161 if recent >= max_per_day {
162 // Drop rolls the (empty) tx back and releases the advisory lock.
163 return Ok(None);
164 }
165
166 // Per-(reporter, target) dedup: a reporter cannot stack a second still-open
167 // report on the same target. Without this, one user could spend their whole
168 // daily quota re-reporting one item and flood the moderation queue with
169 // duplicates of a single complaint. Re-reporting is allowed once the prior
170 // report is resolved (`resolved_at` set). Runs under the same per-reporter
171 // advisory lock as the cap, so concurrent submits can't both slip past.
172 let already_open: bool = sqlx::query_scalar(
173 "SELECT EXISTS(SELECT 1 FROM reports \
174 WHERE reporter_user_id = $1 AND target_type = $2 AND target_id = $3 \
175 AND resolved_at IS NULL)",
176 )
177 .bind(reporter_id)
178 .bind(target_type)
179 .bind(target_id)
180 .fetch_one(&mut *tx)
181 .await?;
182 if already_open {
183 return Ok(None);
184 }
185
186 let report = sqlx::query_as::<_, DbReport>(
187 r"
188 INSERT INTO reports (reporter_user_id, target_type, target_id, report_type, reason)
189 VALUES ($1, $2, $3, $4, $5)
190 RETURNING id, reporter_user_id, target_type, target_id, report_type, reason,
191 status, admin_notes, resolved_by, created_at, resolved_at
192 ",
193 )
194 .bind(reporter_id)
195 .bind(target_type)
196 .bind(target_id)
197 .bind(report_type)
198 .bind(reason)
199 .fetch_one(&mut *tx)
200 .await?;
201
202 tx.commit().await?;
203 Ok(Some(report))
204 }
205