Skip to main content

max / makenotwork

10.1 KB · 368 lines History Blame Raw
1 //! Project CRUD and lookup queries.
2
3 use sqlx::PgPool;
4
5 use super::models::*;
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 let project = sqlx::query_as::<_, DbProject>(
24 r#"
25 INSERT INTO projects (user_id, slug, title, description, project_type, features)
26 VALUES ($1, $2, $3, $4, $5, $6)
27 RETURNING *
28 "#,
29 )
30 .bind(user_id)
31 .bind(slug)
32 .bind(title)
33 .bind(description)
34 .bind(project_type)
35 .bind(features)
36 .fetch_one(pool)
37 .await?;
38
39 Ok(project)
40 }
41
42 /// Fetch a project by primary key. Returns `None` if not found.
43 #[tracing::instrument(skip_all)]
44 pub async fn get_project_by_id(pool: &PgPool, id: ProjectId) -> Result<Option<DbProject>> {
45 let project = sqlx::query_as::<_, DbProject>("SELECT * FROM projects WHERE id = $1")
46 .bind(id)
47 .fetch_optional(pool)
48 .await?;
49
50 Ok(project)
51 }
52
53 /// Fetch a project by its owning user and URL slug. Returns `None` if not found.
54 #[tracing::instrument(skip_all)]
55 pub async fn get_project_by_user_and_slug(
56 pool: &PgPool,
57 user_id: UserId,
58 slug: &Slug,
59 ) -> Result<Option<DbProject>> {
60 let project = sqlx::query_as::<_, DbProject>(
61 "SELECT * FROM projects WHERE user_id = $1 AND slug = $2",
62 )
63 .bind(user_id)
64 .bind(slug)
65 .fetch_optional(pool)
66 .await?;
67
68 Ok(project)
69 }
70
71 /// Fetch a public project by user ID and slug (for custom domain routing).
72 #[tracing::instrument(skip_all)]
73 pub async fn get_public_project_by_user_and_slug(
74 pool: &PgPool,
75 user_id: UserId,
76 slug: &Slug,
77 ) -> Result<Option<DbProject>> {
78 let project = sqlx::query_as::<_, DbProject>(
79 "SELECT * FROM projects WHERE user_id = $1 AND slug = $2 AND is_public = true",
80 )
81 .bind(user_id)
82 .bind(slug)
83 .fetch_optional(pool)
84 .await?;
85
86 Ok(project)
87 }
88
89 /// Return just the IDs of all projects owned by a user (lightweight, for cleanup).
90 #[tracing::instrument(skip_all)]
91 pub async fn get_project_ids_for_user(pool: &PgPool, user_id: UserId) -> Result<Vec<ProjectId>> {
92 let ids = sqlx::query_scalar::<_, ProjectId>(
93 "SELECT id FROM projects WHERE user_id = $1",
94 )
95 .bind(user_id)
96 .fetch_all(pool)
97 .await?;
98
99 Ok(ids)
100 }
101
102 /// List all projects owned by a user, newest first.
103 ///
104 /// Capped at 500 as a safety limit.
105 #[tracing::instrument(skip_all)]
106 pub async fn get_projects_by_user(pool: &PgPool, user_id: UserId) -> Result<Vec<DbProject>> {
107 let projects = sqlx::query_as::<_, DbProject>(
108 "SELECT * FROM projects WHERE user_id = $1 ORDER BY created_at DESC LIMIT 500",
109 )
110 .bind(user_id)
111 .fetch_all(pool)
112 .await?;
113
114 Ok(projects)
115 }
116
117 /// Partially update a project's fields (COALESCE keeps existing values when `None`).
118 ///
119 /// When `features` is `Some`, the project_type is auto-derived from the new features.
120 #[tracing::instrument(skip_all)]
121 pub async fn update_project(
122 pool: &PgPool,
123 id: ProjectId,
124 user_id: UserId,
125 title: Option<&str>,
126 description: Option<&str>,
127 features: Option<&[String]>,
128 is_public: Option<bool>,
129 ) -> Result<DbProject> {
130 let project_type = features.map(super::ProjectFeature::derive_project_type);
131 let project = sqlx::query_as::<_, DbProject>(
132 r#"
133 UPDATE projects
134 SET title = COALESCE($3, title),
135 description = COALESCE($4, description),
136 project_type = COALESCE($5, project_type),
137 is_public = COALESCE($6, is_public),
138 features = COALESCE($7, features)
139 WHERE id = $1 AND user_id = $2
140 RETURNING *
141 "#,
142 )
143 .bind(id)
144 .bind(user_id)
145 .bind(title)
146 .bind(description)
147 .bind(project_type)
148 .bind(is_public)
149 .bind(features)
150 .fetch_one(pool)
151 .await?;
152
153 Ok(project)
154 }
155
156 /// Set or clear a project's category.
157 #[tracing::instrument(skip_all)]
158 pub async fn set_project_category(
159 pool: &PgPool,
160 id: ProjectId,
161 user_id: UserId,
162 category_id: Option<super::CategoryId>,
163 ) -> Result<()> {
164 sqlx::query("UPDATE projects SET category_id = $3 WHERE id = $1 AND user_id = $2")
165 .bind(id)
166 .bind(user_id)
167 .bind(category_id)
168 .execute(pool)
169 .await?;
170
171 Ok(())
172 }
173
174 /// Permanently delete a project by ID (cascades to items).
175 #[tracing::instrument(skip_all)]
176 pub async fn delete_project(pool: &PgPool, id: ProjectId, user_id: UserId) -> Result<()> {
177 sqlx::query("DELETE FROM projects WHERE id = $1 AND user_id = $2")
178 .bind(id)
179 .bind(user_id)
180 .execute(pool)
181 .await?;
182
183 Ok(())
184 }
185
186 /// Get public projects with item counts in a single query (avoids N+1)
187 #[tracing::instrument(skip_all)]
188 pub async fn get_public_projects_with_item_counts(
189 pool: &PgPool,
190 user_id: UserId,
191 ) -> Result<Vec<DbProjectWithItemCount>> {
192 let projects = sqlx::query_as::<_, DbProjectWithItemCount>(
193 r#"
194 SELECT
195 p.id,
196 p.user_id,
197 p.slug,
198 p.title,
199 p.description,
200 p.project_type,
201 p.cover_image_url,
202 p.is_public,
203 p.created_at,
204 p.updated_at,
205 COUNT(i.id) as item_count
206 FROM projects p
207 LEFT JOIN items i ON i.project_id = p.id AND i.is_public = true
208 WHERE p.user_id = $1 AND p.is_public = true
209 GROUP BY p.id
210 ORDER BY p.created_at DESC
211 "#,
212 )
213 .bind(user_id)
214 .fetch_all(pool)
215 .await?;
216
217 Ok(projects)
218 }
219
220 /// Fetch a public project by its URL slug. Returns `None` if not found or not public.
221 #[tracing::instrument(skip_all)]
222 pub async fn get_public_project_by_slug(
223 pool: &PgPool,
224 slug: &Slug,
225 ) -> Result<Option<DbProject>> {
226 let project = sqlx::query_as::<_, DbProject>(
227 "SELECT * FROM projects WHERE slug = $1 AND is_public = true ORDER BY created_at ASC LIMIT 1",
228 )
229 .bind(slug)
230 .fetch_optional(pool)
231 .await?;
232
233 Ok(project)
234 }
235
236 /// Fetch a public project by slug string (bypasses Slug validation).
237 /// Used by the inbound patch handler where the slug comes from an email address.
238 #[tracing::instrument(skip_all)]
239 pub async fn get_public_project_by_slug_str(
240 pool: &PgPool,
241 slug: &str,
242 ) -> Result<Option<DbProject>> {
243 let project = sqlx::query_as::<_, DbProject>(
244 "SELECT * FROM projects WHERE slug = $1 AND is_public = true ORDER BY created_at ASC LIMIT 1",
245 )
246 .bind(slug)
247 .fetch_optional(pool)
248 .await?;
249
250 Ok(project)
251 }
252
253 /// Set the linked MT community ID for a project.
254 #[tracing::instrument(skip_all)]
255 pub async fn set_mt_community_id(
256 pool: &PgPool,
257 project_id: ProjectId,
258 community_id: uuid::Uuid,
259 ) -> Result<()> {
260 sqlx::query("UPDATE projects SET mt_community_id = $2 WHERE id = $1")
261 .bind(project_id)
262 .bind(community_id)
263 .execute(pool)
264 .await?;
265 Ok(())
266 }
267
268 /// Fetch all projects that don't have an MT community linked.
269 #[tracing::instrument(skip_all)]
270 pub async fn get_projects_without_mt_community(pool: &PgPool) -> Result<Vec<DbProject>> {
271 let projects = sqlx::query_as::<_, DbProject>(
272 "SELECT * FROM projects WHERE mt_community_id IS NULL ORDER BY created_at LIMIT 500",
273 )
274 .fetch_all(pool)
275 .await?;
276 Ok(projects)
277 }
278
279 /// Set or clear a project's image URL (stored in cover_image_url column).
280 ///
281 /// Returns `true` when the row was actually updated, `false` when the
282 /// ownership filter matched zero rows (project deleted or transferred to a
283 /// different user between the caller's authorization check and this UPDATE).
284 /// Callers that fire side-effects after the write — storage credit, scan
285 /// enqueue, S3 orphan queueing — must check the bool and roll back on false.
286 #[tracing::instrument(skip_all)]
287 pub async fn update_project_image_url<'e>(
288 executor: impl sqlx::PgExecutor<'e>,
289 id: ProjectId,
290 user_id: UserId,
291 url: &str,
292 ) -> Result<bool> {
293 let result = sqlx::query("UPDATE projects SET cover_image_url = $1, updated_at = NOW() WHERE id = $2 AND user_id = $3")
294 .bind(url)
295 .bind(id)
296 .bind(user_id)
297 .execute(executor)
298 .await?;
299
300 Ok(result.rows_affected() > 0)
301 }
302
303 /// Update a project's AI content tier and disclosure.
304 #[tracing::instrument(skip_all)]
305 pub async fn update_project_ai_tier(
306 pool: &PgPool,
307 id: ProjectId,
308 user_id: UserId,
309 ai_tier: super::AiTier,
310 ai_disclosure: Option<&str>,
311 ) -> Result<()> {
312 sqlx::query(
313 r#"
314 UPDATE projects
315 SET ai_tier = $3, ai_disclosure = $4, updated_at = NOW()
316 WHERE id = $1 AND user_id = $2
317 "#,
318 )
319 .bind(id)
320 .bind(user_id)
321 .bind(ai_tier)
322 .bind(ai_disclosure)
323 .execute(pool)
324 .await?;
325
326 Ok(())
327 }
328
329 /// Update a project's pricing model, price, and PWYW minimum.
330 #[tracing::instrument(skip_all)]
331 pub async fn update_project_pricing(
332 pool: &PgPool,
333 id: ProjectId,
334 user_id: UserId,
335 pricing_model: super::PricingKind,
336 price_cents: i32,
337 pwyw_min_cents: Option<i32>,
338 ) -> Result<()> {
339 sqlx::query(
340 r#"
341 UPDATE projects
342 SET pricing_model = $3, price_cents = $4, pwyw_min_cents = $5, updated_at = NOW()
343 WHERE id = $1 AND user_id = $2
344 "#,
345 )
346 .bind(id)
347 .bind(user_id)
348 .bind(pricing_model)
349 .bind(price_cents)
350 .bind(pwyw_min_cents)
351 .execute(pool)
352 .await?;
353
354 Ok(())
355 }
356
357 /// Atomically increment the project's cache generation counter.
358 /// Call after any write that changes project-visible dashboard data.
359 #[tracing::instrument(skip_all)]
360 pub async fn bump_cache_generation(pool: &PgPool, project_id: ProjectId) -> Result<()> {
361 sqlx::query("UPDATE projects SET cache_generation = cache_generation + 1 WHERE id = $1")
362 .bind(project_id)
363 .execute(pool)
364 .await?;
365
366 Ok(())
367 }
368