Skip to main content

max / makenotwork

5.2 KB · 171 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::id_types::*;
7 use super::models::*;
8 use super::enums::*;
9 use crate::error::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 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 r.report_type,
44 r.reason,
45 r.status,
46 r.admin_notes,
47 r.created_at,
48 r.resolved_at
49 FROM reports r
50 JOIN users u ON u.id = r.reporter_user_id
51 LEFT JOIN projects p ON r.target_type = 'project' AND p.id = r.target_id
52 LEFT JOIN users pu ON p.user_id = pu.id
53 LEFT JOIN items i ON r.target_type = 'item' AND i.id = r.target_id
54 LEFT JOIN projects ip ON i.project_id = ip.id
55 LEFT JOIN users iu ON ip.user_id = iu.id
56 WHERE ($1::TEXT IS NULL OR r.status = $1)
57 ORDER BY r.created_at DESC
58 LIMIT $2 OFFSET $3
59 "#,
60 )
61 .bind(status_filter)
62 .bind(limit)
63 .bind(offset)
64 .fetch_all(pool)
65 .await?;
66
67 Ok(rows)
68 }
69
70 /// Get aggregate report stats.
71 #[tracing::instrument(skip_all)]
72 pub async fn get_report_stats(pool: &PgPool) -> Result<DbReportStats> {
73 let stats = sqlx::query_as::<_, DbReportStats>(
74 r#"
75 SELECT
76 COUNT(*) FILTER (WHERE status = 'open') AS open,
77 COUNT(*) FILTER (WHERE status = 'resolved') AS resolved,
78 COUNT(*) FILTER (WHERE status = 'dismissed') AS dismissed
79 FROM reports
80 "#,
81 )
82 .fetch_one(pool)
83 .await?;
84
85 Ok(stats)
86 }
87
88 /// Resolve or dismiss a report.
89 #[tracing::instrument(skip_all)]
90 pub async fn resolve_report(
91 pool: &PgPool,
92 id: ReportId,
93 status: ReportStatus,
94 admin_notes: &str,
95 resolved_by: UserId,
96 ) -> Result<()> {
97 sqlx::query(
98 r#"
99 UPDATE reports
100 SET status = $2, admin_notes = $3, resolved_by = $4, resolved_at = NOW()
101 WHERE id = $1
102 "#,
103 )
104 .bind(id)
105 .bind(status)
106 .bind(admin_notes)
107 .bind(resolved_by)
108 .execute(pool)
109 .await?;
110
111 Ok(())
112 }
113
114 /// Create a report only if the reporter is under the daily cap — atomically.
115 ///
116 /// Returns `Ok(None)` when the reporter already has `>= max_per_day` reports in
117 /// the trailing 24h. The count and the insert run in one transaction under a
118 /// per-reporter `pg_advisory_xact_lock`, so a single user's concurrent submits
119 /// can't both observe `count = max-1` and both insert (the count-then-insert
120 /// TOCTOU the previous two-call sequence had). The lock is keyed on the
121 /// reporter id, so contention is scoped to one user's own concurrent requests —
122 /// it never blocks unrelated traffic — and it auto-releases at commit/rollback.
123 #[tracing::instrument(skip_all)]
124 pub async fn create_report_within_daily_limit(
125 pool: &PgPool,
126 reporter_id: UserId,
127 target_type: ReportTargetType,
128 target_id: Uuid,
129 report_type: ReportType,
130 reason: &str,
131 max_per_day: i64,
132 ) -> Result<Option<DbReport>> {
133 let mut tx = pool.begin().await?;
134
135 sqlx::query("SELECT pg_advisory_xact_lock(hashtextextended($1::text, 0))")
136 .bind(reporter_id)
137 .execute(&mut *tx)
138 .await?;
139
140 let recent: i64 = sqlx::query_scalar(
141 "SELECT COUNT(*) FROM reports WHERE reporter_user_id = $1 AND created_at > NOW() - INTERVAL '24 hours'",
142 )
143 .bind(reporter_id)
144 .fetch_one(&mut *tx)
145 .await?;
146
147 if recent >= max_per_day {
148 // Drop rolls the (empty) tx back and releases the advisory lock.
149 return Ok(None);
150 }
151
152 let report = sqlx::query_as::<_, DbReport>(
153 r#"
154 INSERT INTO reports (reporter_user_id, target_type, target_id, report_type, reason)
155 VALUES ($1, $2, $3, $4, $5)
156 RETURNING id, reporter_user_id, target_type, target_id, report_type, reason,
157 status, admin_notes, resolved_by, created_at, resolved_at
158 "#,
159 )
160 .bind(reporter_id)
161 .bind(target_type)
162 .bind(target_id)
163 .bind(report_type)
164 .bind(reason)
165 .fetch_one(&mut *tx)
166 .await?;
167
168 tx.commit().await?;
169 Ok(Some(report))
170 }
171