Skip to main content

max / makenotwork

17.6 KB · 553 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 /// Active item count for one project, read from the denormalized
295 /// `projects.item_count` column (migration 156, trigger-maintained).
296 ///
297 /// "Active" is exactly the storefront and discover filter (`is_public AND listed
298 /// AND deleted_at IS NULL`), so this number agrees with both the item list on the
299 /// project page and the count on a discover card. Use it wherever a page needs
300 /// the count without the rows, notably the paywall, which shows how much is
301 /// behind it but not what.
302 #[tracing::instrument(skip_all, fields(%project_id))]
303 pub async fn get_active_item_count(pool: &PgPool, project_id: ProjectId) -> Result<i32> {
304 let count = sqlx::query_scalar::<_, i32>("SELECT item_count FROM projects WHERE id = $1")
305 .bind(project_id)
306 .fetch_one(pool)
307 .await?;
308
309 Ok(count)
310 }
311
312 /// Get public projects with item counts in a single query (avoids N+1)
313 #[tracing::instrument(skip_all)]
314 pub async fn get_public_projects_with_item_counts(
315 pool: &PgPool,
316 user_id: UserId,
317 ) -> Result<Vec<DbProjectWithItemCount>> {
318 let projects = sqlx::query_as::<_, DbProjectWithItemCount>(
319 r"
320 SELECT
321 p.id,
322 p.user_id,
323 p.slug,
324 p.title,
325 p.description,
326 p.project_type,
327 p.cover_image_url,
328 p.cover_scan_status,
329 p.is_public,
330 p.created_at,
331 p.updated_at,
332 COUNT(i.id) as item_count
333 FROM projects p
334 LEFT JOIN items i ON i.project_id = p.id AND i.is_public = true
335 WHERE p.user_id = $1 AND p.is_public = true
336 GROUP BY p.id
337 ORDER BY p.created_at DESC
338 ",
339 )
340 .bind(user_id)
341 .fetch_all(pool)
342 .await?;
343
344 Ok(projects)
345 }
346
347 /// Fetch a public project by its URL slug. Returns `None` if not found, not
348 /// public, or owned by a sandbox account. Sandbox accounts are hidden from all
349 /// public surfaces (discover, item pages, user pages); this join closes the gap
350 /// where their `/p/{slug}`, RSS feeds, and blog pages still rendered publicly.
351 #[tracing::instrument(skip_all)]
352 pub async fn get_public_project_by_slug(pool: &PgPool, slug: &Slug) -> Result<Option<DbProject>> {
353 let project = sqlx::query_as::<_, DbProject>(
354 "SELECT p.* FROM projects p \
355 JOIN users u ON u.id = p.user_id \
356 WHERE p.slug = $1 AND p.is_public = true AND u.is_sandbox = false \
357 ORDER BY p.created_at ASC LIMIT 1",
358 )
359 .bind(slug)
360 .fetch_optional(pool)
361 .await?;
362
363 Ok(project)
364 }
365
366 /// Fetch a public project by slug string (bypasses Slug validation).
367 /// Used by the inbound patch handler where the slug comes from an email address.
368 #[tracing::instrument(skip_all)]
369 pub async fn get_public_project_by_slug_str(
370 pool: &PgPool,
371 slug: &str,
372 ) -> Result<Option<DbProject>> {
373 let project = sqlx::query_as::<_, DbProject>(
374 "SELECT * FROM projects WHERE slug = $1 AND is_public = true ORDER BY created_at ASC LIMIT 1",
375 )
376 .bind(slug)
377 .fetch_optional(pool)
378 .await?;
379
380 Ok(project)
381 }
382
383 /// Set the linked MT community ID for a project.
384 #[tracing::instrument(skip_all)]
385 pub async fn set_mt_community_id(
386 pool: &PgPool,
387 project_id: ProjectId,
388 community_id: uuid::Uuid,
389 ) -> Result<()> {
390 sqlx::query("UPDATE projects SET mt_community_id = $2 WHERE id = $1")
391 .bind(project_id)
392 .bind(community_id)
393 .execute(pool)
394 .await?;
395 Ok(())
396 }
397
398 /// Fetch all projects that don't have an MT community linked.
399 #[tracing::instrument(skip_all)]
400 pub async fn get_projects_without_mt_community(pool: &PgPool) -> Result<Vec<DbProject>> {
401 let projects = sqlx::query_as::<_, DbProject>(
402 "SELECT * FROM projects WHERE mt_community_id IS NULL ORDER BY created_at",
403 )
404 .fetch_all(pool)
405 .await?;
406 Ok(projects)
407 }
408
409 /// Set or clear a project's image URL (stored in cover_image_url column).
410 ///
411 /// Returns `true` when the row was actually updated, `false` when the
412 /// ownership filter matched zero rows (project deleted or transferred to a
413 /// different user between the caller's authorization check and this UPDATE).
414 /// Callers that fire side-effects after the write, storage credit, scan
415 /// enqueue, S3 orphan queueing, must check the bool and roll back on false.
416 #[tracing::instrument(skip_all)]
417 pub async fn update_project_image_url<'e>(
418 executor: impl sqlx::PgExecutor<'e>,
419 id: ProjectId,
420 user_id: UserId,
421 url: &str,
422 cover_s3_key: Option<&str>,
423 ) -> Result<bool> {
424 // Store the bare key alongside the URL so the deletion worker's liveness
425 // check matches it exactly (no URL-suffix parsing). The wizard derives the
426 // key from the CDN URL it just validated; `None` only when clearing.
427 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")
428 .bind(url)
429 .bind(id)
430 .bind(user_id)
431 .bind(cover_s3_key)
432 .execute(executor)
433 .await?;
434
435 Ok(result.rows_affected() > 0)
436 }
437
438 /// Confirm an uploaded project cover: set the URL **and** record its byte size,
439 /// guarded by a compare-and-swap on the existing `cover_image_url`.
440 ///
441 /// This is the only path that writes `cover_image_size_bytes` (migration 126),
442 /// which the storage recalc/breakdown read to reconcile project-cover charges,
443 /// the plain [`update_project_image_url`] setter (used by the wizard) leaves the
444 /// size untouched and is not a storage-charging operation.
445 ///
446 /// Returns `false` when the UPDATE matched zero rows: either the ownership
447 /// filter no-matched (project deleted/transferred mid-flight) OR a concurrent
448 /// confirm already swapped the cover URL out from under `expected_old_url`. The
449 /// CAS stops two concurrent confirms from each deducting the old size and
450 /// orphaning the loser's object (Run #18 Storage B4). Callers fire storage
451 /// credit + S3 cleanup after this and must roll back on `false`.
452 #[tracing::instrument(skip_all)]
453 pub async fn update_project_cover_cas<'e>(
454 executor: impl sqlx::PgExecutor<'e>,
455 id: ProjectId,
456 user_id: UserId,
457 expected_old_url: Option<&str>,
458 url: &str,
459 cover_s3_key: &str,
460 file_size_bytes: i64,
461 ) -> Result<bool> {
462 // Record the bare key (the confirm handler's `req.s3_key`) so deletion-worker
463 // liveness is an exact key match, not a URL-suffix match.
464 let result = sqlx::query(
465 r"UPDATE projects
466 SET cover_image_url = $1, cover_s3_key = $6, cover_image_size_bytes = $4, updated_at = NOW()
467 WHERE id = $2 AND user_id = $3
468 AND cover_image_url IS NOT DISTINCT FROM $5",
469 )
470 .bind(url)
471 .bind(id)
472 .bind(user_id)
473 .bind(file_size_bytes)
474 .bind(expected_old_url)
475 .bind(cover_s3_key)
476 .execute(executor)
477 .await?;
478
479 Ok(result.rows_affected() > 0)
480 }
481
482 /// Update a project's AI content tier and disclosure.
483 #[tracing::instrument(skip_all)]
484 pub async fn update_project_ai_tier(
485 pool: &PgPool,
486 id: ProjectId,
487 user_id: UserId,
488 ai_tier: super::AiTier,
489 ai_disclosure: Option<&str>,
490 ) -> Result<()> {
491 sqlx::query(
492 r"
493 UPDATE projects
494 SET ai_tier = $3, ai_disclosure = $4, updated_at = NOW()
495 WHERE id = $1 AND user_id = $2
496 ",
497 )
498 .bind(id)
499 .bind(user_id)
500 .bind(ai_tier)
501 .bind(ai_disclosure)
502 .execute(pool)
503 .await?;
504
505 Ok(())
506 }
507
508 /// Update a project's pricing model, price, and PWYW minimum.
509 ///
510 /// Takes [`PriceCents`](super::PriceCents) (not raw `i32`) so the `$10k` cap and
511 /// non-negative floor are enforced by the type at every call site, the only way
512 /// to obtain a `PriceCents` is the cap-checking `new`/`buy_once` constructors.
513 /// This is the structural fix for the price-cap-per-writer chronic: a writer
514 /// cannot pass an uncapped value (ultra-fuzz Run 11 UX F1).
515 #[tracing::instrument(skip_all)]
516 pub async fn update_project_pricing(
517 pool: &PgPool,
518 id: ProjectId,
519 user_id: UserId,
520 pricing_model: super::PricingKind,
521 price_cents: super::PriceCents,
522 pwyw_min_cents: Option<super::PriceCents>,
523 ) -> Result<()> {
524 sqlx::query(
525 r"
526 UPDATE projects
527 SET pricing_model = $3, price_cents = $4, pwyw_min_cents = $5, updated_at = NOW()
528 WHERE id = $1 AND user_id = $2
529 ",
530 )
531 .bind(id)
532 .bind(user_id)
533 .bind(pricing_model)
534 .bind(price_cents.as_i32())
535 .bind(pwyw_min_cents.map(super::validated_types::PriceCents::as_i32))
536 .execute(pool)
537 .await?;
538
539 Ok(())
540 }
541
542 /// Atomically increment the project's cache generation counter.
543 /// Call after any write that changes project-visible dashboard data.
544 #[tracing::instrument(skip_all)]
545 pub async fn bump_cache_generation(pool: &PgPool, project_id: ProjectId) -> Result<()> {
546 sqlx::query("UPDATE projects SET cache_generation = cache_generation + 1 WHERE id = $1")
547 .bind(project_id)
548 .execute(pool)
549 .await?;
550
551 Ok(())
552 }
553