Skip to main content

max / makenotwork

16.8 KB · 535 lines History Blame Raw
1 //! Project CRUD and lookup queries.
2
3 use sqlx::PgPool;
4
5 use super::models::{DbProject, DbProjectWithItemCount};
6 use super::validated_types::Slug;
7 use super::{ProjectId, UserId};
8 use crate::error::Result;
9
10 /// Insert a new project and return the created row.
11 ///
12 /// `project_type` is auto-derived from `features` using [`ProjectFeature::derive_project_type`].
13 #[tracing::instrument(skip_all)]
14 pub async fn create_project(
15 pool: &PgPool,
16 user_id: UserId,
17 slug: &Slug,
18 title: &str,
19 description: Option<&str>,
20 features: &[String],
21 ) -> Result<DbProject> {
22 let project_type = super::ProjectFeature::derive_project_type(features);
23 // Slug uniqueness is enforced by the per-table unique indexes, including the
24 // cross-creator `idx_projects_public_slug` (migration 062). Route the bare
25 // INSERT through `insert_with_unique_slug` so a collision auto-suffixes
26 // (`slug`, `slug-2`, ...) and retries instead of surfacing a raw 500 (the
27 // CHRONIC slug-dedup drift, ultra-fuzz Run 2 UX). This is the seal: there is
28 // no public bare-insert constructor for projects.
29 crate::helpers::insert_with_unique_slug(slug.as_str(), |candidate| async move {
30 let candidate = Slug::from_trusted(candidate);
31 sqlx::query_as::<_, DbProject>(
32 r"
33 INSERT INTO projects (user_id, slug, title, description, project_type, features)
34 VALUES ($1, $2, $3, $4, $5, $6)
35 RETURNING *
36 ",
37 )
38 .bind(user_id)
39 .bind(&candidate)
40 .bind(title)
41 .bind(description)
42 .bind(project_type)
43 .bind(features)
44 .fetch_one(pool)
45 .await
46 .map_err(Into::into)
47 })
48 .await
49 }
50
51 /// Fetch a project by primary key. Returns `None` if not found.
52 #[tracing::instrument(skip_all)]
53 pub async fn get_project_by_id(pool: &PgPool, id: ProjectId) -> Result<Option<DbProject>> {
54 let project = sqlx::query_as::<_, DbProject>("SELECT * FROM projects WHERE id = $1")
55 .bind(id)
56 .fetch_optional(pool)
57 .await?;
58
59 Ok(project)
60 }
61
62 /// Fetch a project by its owning user and URL slug. Returns `None` if not found.
63 #[tracing::instrument(skip_all)]
64 pub async fn get_project_by_user_and_slug(
65 pool: &PgPool,
66 user_id: UserId,
67 slug: &Slug,
68 ) -> Result<Option<DbProject>> {
69 let project =
70 sqlx::query_as::<_, DbProject>("SELECT * FROM projects WHERE user_id = $1 AND slug = $2")
71 .bind(user_id)
72 .bind(slug)
73 .fetch_optional(pool)
74 .await?;
75
76 Ok(project)
77 }
78
79 /// Fetch a public project by user ID and slug (for custom domain routing).
80 #[tracing::instrument(skip_all)]
81 pub async fn get_public_project_by_user_and_slug(
82 pool: &PgPool,
83 user_id: UserId,
84 slug: &Slug,
85 ) -> Result<Option<DbProject>> {
86 let project = sqlx::query_as::<_, DbProject>(
87 "SELECT * FROM projects WHERE user_id = $1 AND slug = $2 AND is_public = true",
88 )
89 .bind(user_id)
90 .bind(slug)
91 .fetch_optional(pool)
92 .await?;
93
94 Ok(project)
95 }
96
97 /// Return just the IDs of all projects owned by a user (lightweight, for cleanup).
98 #[tracing::instrument(skip_all)]
99 pub async fn get_project_ids_for_user(pool: &PgPool, user_id: UserId) -> Result<Vec<ProjectId>> {
100 let ids = sqlx::query_scalar::<_, ProjectId>("SELECT id FROM projects WHERE user_id = $1")
101 .bind(user_id)
102 .fetch_all(pool)
103 .await?;
104
105 Ok(ids)
106 }
107
108 /// List all projects owned by a user, newest first.
109 ///
110 /// Capped at 500 as a safety limit.
111 #[tracing::instrument(skip_all)]
112 pub async fn get_projects_by_user(pool: &PgPool, user_id: UserId) -> Result<Vec<DbProject>> {
113 let projects = sqlx::query_as::<_, DbProject>(
114 // No LIMIT: one creator's project set is naturally bounded; an arbitrary
115 // cap silently truncated exports/feeds/dashboard (audit Run 17 Perf).
116 "SELECT * FROM projects WHERE user_id = $1 ORDER BY created_at DESC",
117 )
118 .bind(user_id)
119 .fetch_all(pool)
120 .await?;
121
122 Ok(projects)
123 }
124
125 /// Of the given candidate slugs, return those already taken by a project owned
126 /// by `user_id`. One indexed `slug = ANY($2)` query replaces a per-candidate
127 /// point-query loop (ultra-fuzz Run 6 R6-Perf-M5).
128 #[tracing::instrument(skip_all)]
129 pub async fn filter_taken_slugs(
130 pool: &PgPool,
131 user_id: UserId,
132 slugs: &[String],
133 ) -> Result<Vec<String>> {
134 let taken: Vec<String> =
135 sqlx::query_scalar("SELECT slug FROM projects WHERE user_id = $1 AND slug = ANY($2)")
136 .bind(user_id)
137 .bind(slugs)
138 .fetch_all(pool)
139 .await?;
140 Ok(taken)
141 }
142
143 /// Count a user's projects without materializing the rows. For callers that only
144 /// need the total (e.g. stats), this avoids fetching up to 500 full rows just to
145 /// `.len()` them (ultra-fuzz Run 6 R6-Perf-M4).
146 #[tracing::instrument(skip_all)]
147 pub async fn count_projects_by_user(pool: &PgPool, user_id: UserId) -> Result<i64> {
148 let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM projects WHERE user_id = $1")
149 .bind(user_id)
150 .fetch_one(pool)
151 .await?;
152 Ok(count)
153 }
154
155 /// Partially update a project's fields (COALESCE keeps existing values when `None`).
156 ///
157 /// When `features` is `Some`, the project_type is auto-derived from the new features.
158 #[tracing::instrument(skip_all)]
159 pub async fn update_project(
160 pool: &PgPool,
161 id: ProjectId,
162 user_id: UserId,
163 title: Option<&str>,
164 description: Option<&str>,
165 features: Option<&[String]>,
166 is_public: Option<bool>,
167 ) -> Result<DbProject> {
168 let project_type = features.map(super::ProjectFeature::derive_project_type);
169 let project = sqlx::query_as::<_, DbProject>(
170 r"
171 UPDATE projects
172 SET title = COALESCE($3, title),
173 description = COALESCE($4, description),
174 project_type = COALESCE($5, project_type),
175 is_public = COALESCE($6, is_public),
176 features = COALESCE($7, features)
177 WHERE id = $1 AND user_id = $2
178 RETURNING *
179 ",
180 )
181 .bind(id)
182 .bind(user_id)
183 .bind(title)
184 .bind(description)
185 .bind(project_type)
186 .bind(is_public)
187 .bind(features)
188 .fetch_one(pool)
189 .await?;
190
191 Ok(project)
192 }
193
194 /// Store a project's custom-page source (original, pre-sanitization), stamp
195 /// `custom_pages_updated_at` (which also invalidates the edge caches of every
196 /// item page that inherits this project's CSS), and bump the cache generation.
197 /// Scoped to the owner so a non-owner can't write through this path.
198 pub async fn update_project_custom_page<'e>(
199 executor: impl sqlx::PgExecutor<'e>,
200 id: ProjectId,
201 user_id: UserId,
202 custom_html: &str,
203 custom_css: &str,
204 ) -> Result<DbProject> {
205 let project = sqlx::query_as::<_, DbProject>(
206 r"
207 UPDATE projects
208 SET custom_html = $3,
209 custom_css = $4,
210 custom_pages_updated_at = now(),
211 cache_generation = cache_generation + 1
212 WHERE id = $1 AND user_id = $2
213 RETURNING *
214 ",
215 )
216 .bind(id)
217 .bind(user_id)
218 .bind(custom_html)
219 .bind(custom_css)
220 .fetch_one(executor)
221 .await?;
222 Ok(project)
223 }
224
225 /// Clear a project's custom page back to the platform default.
226 pub async fn reset_project_custom_page(
227 pool: &PgPool,
228 id: ProjectId,
229 user_id: UserId,
230 ) -> Result<()> {
231 sqlx::query(
232 "UPDATE projects SET custom_html = '', custom_css = '', \
233 custom_pages_updated_at = NULL, cache_generation = cache_generation + 1 \
234 WHERE id = $1 AND user_id = $2",
235 )
236 .bind(id)
237 .bind(user_id)
238 .execute(pool)
239 .await?;
240 Ok(())
241 }
242
243 /// Set or clear a project's category.
244 #[tracing::instrument(skip_all)]
245 pub async fn set_project_category(
246 pool: &PgPool,
247 id: ProjectId,
248 user_id: UserId,
249 category_id: Option<super::CategoryId>,
250 ) -> Result<()> {
251 sqlx::query("UPDATE projects SET category_id = $3 WHERE id = $1 AND user_id = $2")
252 .bind(id)
253 .bind(user_id)
254 .bind(category_id)
255 .execute(pool)
256 .await?;
257
258 Ok(())
259 }
260
261 /// Set or clear a project's creator theme. `None` clears to the platform
262 /// default. The id is validated against the embedded registry before this call.
263 #[tracing::instrument(skip_all)]
264 pub async fn set_project_theme(
265 pool: &PgPool,
266 id: ProjectId,
267 user_id: UserId,
268 theme_id: Option<&str>,
269 ) -> Result<()> {
270 sqlx::query(
271 "UPDATE projects SET theme_id = $3, updated_at = NOW() WHERE id = $1 AND user_id = $2",
272 )
273 .bind(id)
274 .bind(user_id)
275 .bind(theme_id)
276 .execute(pool)
277 .await?;
278
279 Ok(())
280 }
281
282 /// Permanently delete a project by ID (cascades to items).
283 #[tracing::instrument(skip_all)]
284 pub async fn delete_project(pool: &PgPool, id: ProjectId, user_id: UserId) -> Result<()> {
285 sqlx::query("DELETE FROM projects WHERE id = $1 AND user_id = $2")
286 .bind(id)
287 .bind(user_id)
288 .execute(pool)
289 .await?;
290
291 Ok(())
292 }
293
294 /// Get public projects with item counts in a single query (avoids N+1)
295 #[tracing::instrument(skip_all)]
296 pub async fn get_public_projects_with_item_counts(
297 pool: &PgPool,
298 user_id: UserId,
299 ) -> Result<Vec<DbProjectWithItemCount>> {
300 let projects = sqlx::query_as::<_, DbProjectWithItemCount>(
301 r"
302 SELECT
303 p.id,
304 p.user_id,
305 p.slug,
306 p.title,
307 p.description,
308 p.project_type,
309 p.cover_image_url,
310 p.cover_scan_status,
311 p.is_public,
312 p.created_at,
313 p.updated_at,
314 COUNT(i.id) as item_count
315 FROM projects p
316 LEFT JOIN items i ON i.project_id = p.id AND i.is_public = true
317 WHERE p.user_id = $1 AND p.is_public = true
318 GROUP BY p.id
319 ORDER BY p.created_at DESC
320 ",
321 )
322 .bind(user_id)
323 .fetch_all(pool)
324 .await?;
325
326 Ok(projects)
327 }
328
329 /// Fetch a public project by its URL slug. Returns `None` if not found, not
330 /// public, or owned by a sandbox account. Sandbox accounts are hidden from all
331 /// public surfaces (discover, item pages, user pages); this join closes the gap
332 /// where their `/p/{slug}`, RSS feeds, and blog pages still rendered publicly.
333 #[tracing::instrument(skip_all)]
334 pub async fn get_public_project_by_slug(pool: &PgPool, slug: &Slug) -> Result<Option<DbProject>> {
335 let project = sqlx::query_as::<_, DbProject>(
336 "SELECT p.* FROM projects p \
337 JOIN users u ON u.id = p.user_id \
338 WHERE p.slug = $1 AND p.is_public = true AND u.is_sandbox = false \
339 ORDER BY p.created_at ASC LIMIT 1",
340 )
341 .bind(slug)
342 .fetch_optional(pool)
343 .await?;
344
345 Ok(project)
346 }
347
348 /// Fetch a public project by slug string (bypasses Slug validation).
349 /// Used by the inbound patch handler where the slug comes from an email address.
350 #[tracing::instrument(skip_all)]
351 pub async fn get_public_project_by_slug_str(
352 pool: &PgPool,
353 slug: &str,
354 ) -> Result<Option<DbProject>> {
355 let project = sqlx::query_as::<_, DbProject>(
356 "SELECT * FROM projects WHERE slug = $1 AND is_public = true ORDER BY created_at ASC LIMIT 1",
357 )
358 .bind(slug)
359 .fetch_optional(pool)
360 .await?;
361
362 Ok(project)
363 }
364
365 /// Set the linked MT community ID for a project.
366 #[tracing::instrument(skip_all)]
367 pub async fn set_mt_community_id(
368 pool: &PgPool,
369 project_id: ProjectId,
370 community_id: uuid::Uuid,
371 ) -> Result<()> {
372 sqlx::query("UPDATE projects SET mt_community_id = $2 WHERE id = $1")
373 .bind(project_id)
374 .bind(community_id)
375 .execute(pool)
376 .await?;
377 Ok(())
378 }
379
380 /// Fetch all projects that don't have an MT community linked.
381 #[tracing::instrument(skip_all)]
382 pub async fn get_projects_without_mt_community(pool: &PgPool) -> Result<Vec<DbProject>> {
383 let projects = sqlx::query_as::<_, DbProject>(
384 "SELECT * FROM projects WHERE mt_community_id IS NULL ORDER BY created_at",
385 )
386 .fetch_all(pool)
387 .await?;
388 Ok(projects)
389 }
390
391 /// Set or clear a project's image URL (stored in cover_image_url column).
392 ///
393 /// Returns `true` when the row was actually updated, `false` when the
394 /// ownership filter matched zero rows (project deleted or transferred to a
395 /// different user between the caller's authorization check and this UPDATE).
396 /// Callers that fire side-effects after the write, storage credit, scan
397 /// enqueue, S3 orphan queueing, must check the bool and roll back on false.
398 #[tracing::instrument(skip_all)]
399 pub async fn update_project_image_url<'e>(
400 executor: impl sqlx::PgExecutor<'e>,
401 id: ProjectId,
402 user_id: UserId,
403 url: &str,
404 cover_s3_key: Option<&str>,
405 ) -> Result<bool> {
406 // Store the bare key alongside the URL so the deletion worker's liveness
407 // check matches it exactly (no URL-suffix parsing). The wizard derives the
408 // key from the CDN URL it just validated; `None` only when clearing.
409 let result = sqlx::query("UPDATE projects SET cover_image_url = $1, cover_s3_key = $4, updated_at = NOW() WHERE id = $2 AND user_id = $3")
410 .bind(url)
411 .bind(id)
412 .bind(user_id)
413 .bind(cover_s3_key)
414 .execute(executor)
415 .await?;
416
417 Ok(result.rows_affected() > 0)
418 }
419
420 /// Confirm an uploaded project cover: set the URL **and** record its byte size,
421 /// guarded by a compare-and-swap on the existing `cover_image_url`.
422 ///
423 /// This is the only path that writes `cover_image_size_bytes` (migration 126),
424 /// which the storage recalc/breakdown read to reconcile project-cover charges,
425 /// the plain [`update_project_image_url`] setter (used by the wizard) leaves the
426 /// size untouched and is not a storage-charging operation.
427 ///
428 /// Returns `false` when the UPDATE matched zero rows: either the ownership
429 /// filter no-matched (project deleted/transferred mid-flight) OR a concurrent
430 /// confirm already swapped the cover URL out from under `expected_old_url`. The
431 /// CAS stops two concurrent confirms from each deducting the old size and
432 /// orphaning the loser's object (Run #18 Storage B4). Callers fire storage
433 /// credit + S3 cleanup after this and must roll back on `false`.
434 #[tracing::instrument(skip_all)]
435 pub async fn update_project_cover_cas<'e>(
436 executor: impl sqlx::PgExecutor<'e>,
437 id: ProjectId,
438 user_id: UserId,
439 expected_old_url: Option<&str>,
440 url: &str,
441 cover_s3_key: &str,
442 file_size_bytes: i64,
443 ) -> Result<bool> {
444 // Record the bare key (the confirm handler's `req.s3_key`) so deletion-worker
445 // liveness is an exact key match, not a URL-suffix match.
446 let result = sqlx::query(
447 r"UPDATE projects
448 SET cover_image_url = $1, cover_s3_key = $6, cover_image_size_bytes = $4, updated_at = NOW()
449 WHERE id = $2 AND user_id = $3
450 AND cover_image_url IS NOT DISTINCT FROM $5",
451 )
452 .bind(url)
453 .bind(id)
454 .bind(user_id)
455 .bind(file_size_bytes)
456 .bind(expected_old_url)
457 .bind(cover_s3_key)
458 .execute(executor)
459 .await?;
460
461 Ok(result.rows_affected() > 0)
462 }
463
464 /// Update a project's AI content tier and disclosure.
465 #[tracing::instrument(skip_all)]
466 pub async fn update_project_ai_tier(
467 pool: &PgPool,
468 id: ProjectId,
469 user_id: UserId,
470 ai_tier: super::AiTier,
471 ai_disclosure: Option<&str>,
472 ) -> Result<()> {
473 sqlx::query(
474 r"
475 UPDATE projects
476 SET ai_tier = $3, ai_disclosure = $4, updated_at = NOW()
477 WHERE id = $1 AND user_id = $2
478 ",
479 )
480 .bind(id)
481 .bind(user_id)
482 .bind(ai_tier)
483 .bind(ai_disclosure)
484 .execute(pool)
485 .await?;
486
487 Ok(())
488 }
489
490 /// Update a project's pricing model, price, and PWYW minimum.
491 ///
492 /// Takes [`PriceCents`](super::PriceCents) (not raw `i32`) so the `$10k` cap and
493 /// non-negative floor are enforced by the type at every call site, the only way
494 /// to obtain a `PriceCents` is the cap-checking `new`/`buy_once` constructors.
495 /// This is the structural fix for the price-cap-per-writer chronic: a writer
496 /// cannot pass an uncapped value (ultra-fuzz Run 11 UX F1).
497 #[tracing::instrument(skip_all)]
498 pub async fn update_project_pricing(
499 pool: &PgPool,
500 id: ProjectId,
501 user_id: UserId,
502 pricing_model: super::PricingKind,
503 price_cents: super::PriceCents,
504 pwyw_min_cents: Option<super::PriceCents>,
505 ) -> Result<()> {
506 sqlx::query(
507 r"
508 UPDATE projects
509 SET pricing_model = $3, price_cents = $4, pwyw_min_cents = $5, updated_at = NOW()
510 WHERE id = $1 AND user_id = $2
511 ",
512 )
513 .bind(id)
514 .bind(user_id)
515 .bind(pricing_model)
516 .bind(price_cents.as_i32())
517 .bind(pwyw_min_cents.map(super::validated_types::PriceCents::as_i32))
518 .execute(pool)
519 .await?;
520
521 Ok(())
522 }
523
524 /// Atomically increment the project's cache generation counter.
525 /// Call after any write that changes project-visible dashboard data.
526 #[tracing::instrument(skip_all)]
527 pub async fn bump_cache_generation(pool: &PgPool, project_id: ProjectId) -> Result<()> {
528 sqlx::query("UPDATE projects SET cache_generation = cache_generation + 1 WHERE id = $1")
529 .bind(project_id)
530 .execute(pool)
531 .await?;
532
533 Ok(())
534 }
535