Skip to main content

max / makenotwork

10.9 KB · 325 lines History Blame Raw
1 //! Ordered image galleries for items and projects (launchplan S.1).
2 //!
3 //! Two near-identical tables (`item_images` / `project_images`) keyed by parent,
4 //! each row an image with an alt string and an explicit `position`. The single
5 //! `cover_image_url` on items/projects is unaffected, it stays the OG/card
6 //! image; these rows are the additive carousel gallery.
7 //!
8 //! `s3_key` + `file_size_bytes` are stored so a delete decrements storage from
9 //! the recorded size (no S3 HEAD) and the cleanup garbage-collector recognizes
10 //! live gallery objects via the S3_KEY_REFS registry.
11
12 use sqlx::{PgExecutor, PgPool};
13
14 use super::{ImageId, ItemId, ProjectId};
15 use crate::error::Result;
16
17 /// One gallery image row (shared shape for item and project galleries).
18 #[derive(Debug, Clone, sqlx::FromRow)]
19 pub struct GalleryImage {
20 pub id: ImageId,
21 pub s3_key: String,
22 pub image_url: String,
23 pub alt: String,
24 pub position: i32,
25 pub file_size_bytes: i64,
26 }
27
28 /// Maximum gallery images per entity. Keeps a single creator from ballooning
29 /// storage with one listing and bounds the carousel length.
30 pub const MAX_GALLERY_IMAGES: i64 = 8;
31
32 // Item galleries
33
34 /// List an item's gallery images in display order.
35 #[tracing::instrument(skip_all)]
36 pub async fn list_for_item<'e>(
37 executor: impl PgExecutor<'e>,
38 item_id: ItemId,
39 ) -> Result<Vec<GalleryImage>> {
40 // Fail-closed render gate: only images whose scan cleared are carouselled.
41 // Pending/held rows stay hidden. The per-entity cap (`count_for_item`) still
42 // counts every row, so a held image cannot be re-uploaded around the limit.
43 let rows = sqlx::query_as::<_, GalleryImage>(
44 "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
45 FROM item_images WHERE item_id = $1 AND scan_status = 'clean' \
46 ORDER BY position, created_at",
47 )
48 .bind(item_id)
49 .fetch_all(executor)
50 .await?;
51 Ok(rows)
52 }
53
54 /// Count an item's gallery images (for the per-entity cap check).
55 #[tracing::instrument(skip_all)]
56 pub async fn count_for_item<'e>(executor: impl PgExecutor<'e>, item_id: ItemId) -> Result<i64> {
57 let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM item_images WHERE item_id = $1")
58 .bind(item_id)
59 .fetch_one(executor)
60 .await?;
61 Ok(count)
62 }
63
64 /// Insert a gallery image at the end (position = current max + 1). Returns the
65 /// new row id. Takes an executor so it can run inside the confirm transaction.
66 #[tracing::instrument(skip_all)]
67 pub async fn insert_for_item<'e>(
68 executor: impl PgExecutor<'e>,
69 item_id: ItemId,
70 s3_key: &str,
71 image_url: &str,
72 alt: &str,
73 file_size_bytes: i64,
74 ) -> Result<ImageId> {
75 let id: ImageId = sqlx::query_scalar(
76 "INSERT INTO item_images (item_id, s3_key, image_url, alt, position, file_size_bytes) \
77 VALUES ($1, $2, $3, $4, \
78 COALESCE((SELECT MAX(position) + 1 FROM item_images WHERE item_id = $1), 0), \
79 $5) \
80 RETURNING id",
81 )
82 .bind(item_id)
83 .bind(s3_key)
84 .bind(image_url)
85 .bind(alt)
86 .bind(file_size_bytes)
87 .fetch_one(executor)
88 .await?;
89 Ok(id)
90 }
91
92 /// Look up an item gallery row by its `s3_key` (idempotency guard for confirm:
93 /// a replayed upload-confirm finds the already-inserted row and short-circuits
94 /// instead of double-inserting + double-charging storage). Takes an executor so
95 /// it runs inside the confirm transaction under the gallery advisory lock.
96 #[tracing::instrument(skip_all)]
97 pub async fn find_for_item_by_key<'e>(
98 executor: impl PgExecutor<'e>,
99 item_id: ItemId,
100 s3_key: &str,
101 ) -> Result<Option<GalleryImage>> {
102 let row = sqlx::query_as::<_, GalleryImage>(
103 "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
104 FROM item_images WHERE item_id = $1 AND s3_key = $2",
105 )
106 .bind(item_id)
107 .bind(s3_key)
108 .fetch_optional(executor)
109 .await?;
110 Ok(row)
111 }
112
113 /// Delete one item gallery image IF it belongs to an item owned by `user_id`.
114 /// Returns the deleted row (for storage decrement + S3 cleanup), or None if it
115 /// did not exist or the caller does not own it.
116 #[tracing::instrument(skip_all)]
117 pub async fn delete_for_item<'e>(
118 executor: impl PgExecutor<'e>,
119 image_id: ImageId,
120 user_id: super::UserId,
121 ) -> Result<Option<GalleryImage>> {
122 let row = sqlx::query_as::<_, GalleryImage>(
123 "DELETE FROM item_images WHERE id = $1 AND item_id IN ( \
124 SELECT i.id FROM items i JOIN projects p ON p.id = i.project_id WHERE p.user_id = $2 \
125 ) RETURNING id, s3_key, image_url, alt, position, file_size_bytes",
126 )
127 .bind(image_id)
128 .bind(user_id)
129 .fetch_optional(executor)
130 .await?;
131 Ok(row)
132 }
133
134 /// Reorder an item's gallery to match `ordered_ids` (ids not belonging to the
135 /// item are ignored). Positions are assigned by list order.
136 ///
137 /// One set-based `UPDATE ... FROM UNNEST(...) WITH ORDINALITY` rather than a
138 /// per-id UPDATE loop, `WITH ORDINALITY` is 1-based, so `position = ord - 1`
139 /// keeps the prior 0-based ordering (audit Run 17 Performance N+1).
140 #[tracing::instrument(skip_all)]
141 pub async fn reorder_item(pool: &PgPool, item_id: ItemId, ordered_ids: &[ImageId]) -> Result<()> {
142 sqlx::query(
143 "UPDATE item_images AS t SET position = o.ord - 1 \
144 FROM UNNEST($1::uuid[]) WITH ORDINALITY AS o(id, ord) \
145 WHERE t.id = o.id AND t.item_id = $2",
146 )
147 .bind(ordered_ids)
148 .bind(item_id)
149 .execute(pool)
150 .await?;
151 Ok(())
152 }
153
154 // Project galleries
155
156 /// List a project's gallery images in display order.
157 #[tracing::instrument(skip_all)]
158 pub async fn list_for_project<'e>(
159 executor: impl PgExecutor<'e>,
160 project_id: ProjectId,
161 ) -> Result<Vec<GalleryImage>> {
162 // Fail-closed render gate: only cleared images are carouselled (see
163 // `list_for_item`). The cap count (`count_for_project`) still counts all rows.
164 let rows = sqlx::query_as::<_, GalleryImage>(
165 "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
166 FROM project_images WHERE project_id = $1 AND scan_status = 'clean' \
167 ORDER BY position, created_at",
168 )
169 .bind(project_id)
170 .fetch_all(executor)
171 .await?;
172 Ok(rows)
173 }
174
175 /// Count a project's gallery images (for the per-entity cap check).
176 #[tracing::instrument(skip_all)]
177 pub async fn count_for_project<'e>(
178 executor: impl PgExecutor<'e>,
179 project_id: ProjectId,
180 ) -> Result<i64> {
181 let count: i64 =
182 sqlx::query_scalar("SELECT COUNT(*) FROM project_images WHERE project_id = $1")
183 .bind(project_id)
184 .fetch_one(executor)
185 .await?;
186 Ok(count)
187 }
188
189 /// Insert a project gallery image at the end. Returns the new row id.
190 #[tracing::instrument(skip_all)]
191 pub async fn insert_for_project<'e>(
192 executor: impl PgExecutor<'e>,
193 project_id: ProjectId,
194 s3_key: &str,
195 image_url: &str,
196 alt: &str,
197 file_size_bytes: i64,
198 ) -> Result<ImageId> {
199 let id: ImageId = sqlx::query_scalar(
200 "INSERT INTO project_images (project_id, s3_key, image_url, alt, position, file_size_bytes) \
201 VALUES ($1, $2, $3, $4, \
202 COALESCE((SELECT MAX(position) + 1 FROM project_images WHERE project_id = $1), 0), \
203 $5) \
204 RETURNING id",
205 )
206 .bind(project_id)
207 .bind(s3_key)
208 .bind(image_url)
209 .bind(alt)
210 .bind(file_size_bytes)
211 .fetch_one(executor)
212 .await?;
213 Ok(id)
214 }
215
216 /// Look up a project gallery row by its `s3_key` (confirm idempotency guard,
217 /// see [`find_for_item_by_key`]).
218 #[tracing::instrument(skip_all)]
219 pub async fn find_for_project_by_key<'e>(
220 executor: impl PgExecutor<'e>,
221 project_id: ProjectId,
222 s3_key: &str,
223 ) -> Result<Option<GalleryImage>> {
224 let row = sqlx::query_as::<_, GalleryImage>(
225 "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
226 FROM project_images WHERE project_id = $1 AND s3_key = $2",
227 )
228 .bind(project_id)
229 .bind(s3_key)
230 .fetch_optional(executor)
231 .await?;
232 Ok(row)
233 }
234
235 /// Delete one project gallery image IF the project is owned by `user_id`.
236 /// Returns the deleted row, or None if missing / not owned.
237 #[tracing::instrument(skip_all)]
238 pub async fn delete_for_project<'e>(
239 executor: impl PgExecutor<'e>,
240 image_id: ImageId,
241 user_id: super::UserId,
242 ) -> Result<Option<GalleryImage>> {
243 let row = sqlx::query_as::<_, GalleryImage>(
244 "DELETE FROM project_images WHERE id = $1 AND project_id IN ( \
245 SELECT id FROM projects WHERE user_id = $2 \
246 ) RETURNING id, s3_key, image_url, alt, position, file_size_bytes",
247 )
248 .bind(image_id)
249 .bind(user_id)
250 .fetch_optional(executor)
251 .await?;
252 Ok(row)
253 }
254
255 /// Reorder a project's gallery to match `ordered_ids`. Set-based, like
256 /// [`reorder_item`].
257 #[tracing::instrument(skip_all)]
258 pub async fn reorder_project(
259 pool: &PgPool,
260 project_id: ProjectId,
261 ordered_ids: &[ImageId],
262 ) -> Result<()> {
263 sqlx::query(
264 "UPDATE project_images AS t SET position = o.ord - 1 \
265 FROM UNNEST($1::uuid[]) WITH ORDINALITY AS o(id, ord) \
266 WHERE t.id = o.id AND t.project_id = $2",
267 )
268 .bind(ordered_ids)
269 .bind(project_id)
270 .execute(pool)
271 .await?;
272 Ok(())
273 }
274
275 // Lifecycle key collection (delete / purge)
276 //
277 // Gallery rows CASCADE away when their parent item/project is deleted, so any
278 // destructive path must collect their `s3_key`s BEFORE the cascade or the S3
279 // objects orphan with no durable record (Run #18 Storage B2). These collectors
280 // are the gallery half of the per-entity key sweep the item/version collectors
281 // already do.
282
283 /// S3 keys of every gallery image (item carousel + project carousel) belonging
284 /// to a project, both `item_images` (via the project's items) and
285 /// `project_images`. Call before deleting the project.
286 #[tracing::instrument(skip_all)]
287 pub async fn s3_keys_for_project<'e>(
288 executor: impl PgExecutor<'e>,
289 project_id: ProjectId,
290 ) -> Result<Vec<String>> {
291 let keys: Vec<String> = sqlx::query_scalar(
292 r"
293 SELECT ii.s3_key
294 FROM item_images ii JOIN items i ON ii.item_id = i.id
295 WHERE i.project_id = $1
296 UNION ALL
297 SELECT pi.s3_key
298 FROM project_images pi
299 WHERE pi.project_id = $1
300 ",
301 )
302 .bind(project_id)
303 .fetch_all(executor)
304 .await?;
305 Ok(keys)
306 }
307
308 /// S3 keys of item-gallery images belonging to items soft-deleted more than 7
309 /// days ago (the purge horizon). Call before the purge CASCADE destroys the
310 /// `item_images` rows. (Project galleries are not soft-deleted, projects are
311 /// hard-deleted via [`s3_keys_for_project`].)
312 #[tracing::instrument(skip_all)]
313 pub async fn s3_keys_for_expired_purged_items(pool: &PgPool) -> Result<Vec<String>> {
314 let keys: Vec<String> = sqlx::query_scalar(
315 r"
316 SELECT ii.s3_key
317 FROM item_images ii JOIN items i ON ii.item_id = i.id
318 WHERE i.deleted_at IS NOT NULL AND i.deleted_at < NOW() - INTERVAL '7 days'
319 ",
320 )
321 .fetch_all(pool)
322 .await?;
323 Ok(keys)
324 }
325