Skip to main content

max / makenotwork

5.6 KB · 205 lines History Blame Raw
1 //! Item media metadata: post-upload file-size writebacks and per-content-type
2 //! S3 key/URL/metadata updates (audio, cover, video).
3
4 use sqlx::PgPool;
5
6 use crate::db::models::*;
7 use crate::db::{ItemId, UserId};
8 use crate::error::Result;
9
10 /// Get the audio, cover, and video file sizes for an item (for storage decrement on delete).
11 #[tracing::instrument(skip_all)]
12 pub async fn get_item_file_sizes(
13 pool: &PgPool,
14 id: ItemId,
15 ) -> Result<crate::db::models::ItemFileSizes> {
16 let row = sqlx::query_as::<_, (Option<i64>, Option<i64>, Option<i64>)>(
17 "SELECT audio_file_size_bytes, cover_file_size_bytes, video_file_size_bytes FROM items WHERE id = $1",
18 )
19 .bind(id)
20 .fetch_optional(pool)
21 .await?;
22
23 match row {
24 Some((audio, cover, video)) => Ok(crate::db::models::ItemFileSizes {
25 audio_file_size_bytes: audio,
26 cover_file_size_bytes: cover,
27 video_file_size_bytes: video,
28 }),
29 None => Ok(crate::db::models::ItemFileSizes {
30 audio_file_size_bytes: None,
31 cover_file_size_bytes: None,
32 video_file_size_bytes: None,
33 }),
34 }
35 }
36
37 /// Update the audio file size on an item (defense-in-depth: verifies ownership).
38 #[tracing::instrument(skip_all)]
39 pub async fn update_item_audio_file_size(
40 pool: &PgPool,
41 item_id: ItemId,
42 user_id: UserId,
43 bytes: i64,
44 ) -> Result<()> {
45 sqlx::query(
46 "UPDATE items SET audio_file_size_bytes = $2 WHERE id = $1 AND project_id IN (SELECT id FROM projects WHERE user_id = $3)",
47 )
48 .bind(item_id)
49 .bind(bytes)
50 .bind(user_id)
51 .execute(pool)
52 .await?;
53
54 Ok(())
55 }
56
57 /// Update the cover image URL for an item (defense-in-depth: verifies ownership).
58 #[tracing::instrument(skip_all)]
59 pub async fn update_item_cover_image_url(
60 pool: &PgPool,
61 item_id: ItemId,
62 user_id: UserId,
63 url: &str,
64 ) -> Result<()> {
65 sqlx::query(
66 "UPDATE items SET cover_image_url = $2, updated_at = NOW() WHERE id = $1 AND project_id IN (SELECT id FROM projects WHERE user_id = $3)",
67 )
68 .bind(item_id)
69 .bind(url)
70 .bind(user_id)
71 .execute(pool)
72 .await?;
73
74 Ok(())
75 }
76
77 /// Atomically update cover image URL, S3 key, and file size in a single UPDATE
78 /// (defense-in-depth: verifies ownership).
79 ///
80 /// Returns `true` when the row was actually updated, `false` when the
81 /// ownership filter matched zero rows (item deleted or moved between
82 /// projects mid-flight). Callers that fire side-effects after the write —
83 /// storage credit, scan enqueue, S3 orphan queueing — must check the bool
84 /// and roll back on false.
85 #[tracing::instrument(skip_all)]
86 pub async fn update_item_cover<'e>(
87 executor: impl sqlx::PgExecutor<'e>,
88 item_id: ItemId,
89 user_id: UserId,
90 url: &str,
91 s3_key: &str,
92 file_size_bytes: i64,
93 ) -> Result<bool> {
94 let result = sqlx::query(
95 r#"UPDATE items
96 SET cover_image_url = $2, cover_s3_key = $3, cover_file_size_bytes = $4, updated_at = NOW()
97 WHERE id = $1
98 AND project_id IN (SELECT id FROM projects WHERE user_id = $5)"#,
99 )
100 .bind(item_id)
101 .bind(url)
102 .bind(s3_key)
103 .bind(file_size_bytes)
104 .bind(user_id)
105 .execute(executor)
106 .await?;
107
108 Ok(result.rows_affected() > 0)
109 }
110
111 /// Update the cover file size on an item (defense-in-depth: verifies ownership).
112 #[tracing::instrument(skip_all)]
113 pub async fn update_item_cover_file_size(
114 pool: &PgPool,
115 item_id: ItemId,
116 user_id: UserId,
117 bytes: i64,
118 ) -> Result<()> {
119 sqlx::query(
120 "UPDATE items SET cover_file_size_bytes = $2 WHERE id = $1 AND project_id IN (SELECT id FROM projects WHERE user_id = $3)",
121 )
122 .bind(item_id)
123 .bind(bytes)
124 .bind(user_id)
125 .execute(pool)
126 .await?;
127
128 Ok(())
129 }
130
131 /// Update the video S3 key for an item (defense-in-depth: verifies ownership).
132 #[tracing::instrument(skip_all)]
133 pub async fn update_item_video_s3_key(
134 pool: &PgPool,
135 item_id: ItemId,
136 user_id: UserId,
137 s3_key: &str,
138 ) -> Result<DbItem> {
139 let item = sqlx::query_as::<_, DbItem>(
140 r#"
141 UPDATE items
142 SET video_s3_key = $2, updated_at = NOW()
143 WHERE id = $1
144 AND project_id IN (SELECT id FROM projects WHERE user_id = $3)
145 RETURNING *
146 "#,
147 )
148 .bind(item_id)
149 .bind(s3_key)
150 .bind(user_id)
151 .fetch_one(pool)
152 .await?;
153
154 Ok(item)
155 }
156
157 /// Update the video file size on an item (defense-in-depth: verifies ownership).
158 #[tracing::instrument(skip_all)]
159 pub async fn update_item_video_file_size(
160 pool: &PgPool,
161 item_id: ItemId,
162 user_id: UserId,
163 bytes: i64,
164 ) -> Result<()> {
165 sqlx::query(
166 "UPDATE items SET video_file_size_bytes = $2 WHERE id = $1 AND project_id IN (SELECT id FROM projects WHERE user_id = $3)",
167 )
168 .bind(item_id)
169 .bind(bytes)
170 .bind(user_id)
171 .execute(pool)
172 .await?;
173
174 Ok(())
175 }
176
177 /// Update video metadata (duration, resolution) on an item (defense-in-depth: verifies ownership).
178 #[tracing::instrument(skip_all)]
179 pub async fn update_item_video_metadata(
180 pool: &PgPool,
181 item_id: ItemId,
182 user_id: UserId,
183 duration_seconds: Option<i32>,
184 width: Option<i32>,
185 height: Option<i32>,
186 ) -> Result<()> {
187 sqlx::query(
188 r#"
189 UPDATE items
190 SET video_duration_seconds = $2, video_width = $3, video_height = $4, updated_at = NOW()
191 WHERE id = $1
192 AND project_id IN (SELECT id FROM projects WHERE user_id = $5)
193 "#,
194 )
195 .bind(item_id)
196 .bind(duration_seconds)
197 .bind(width)
198 .bind(height)
199 .bind(user_id)
200 .execute(pool)
201 .await?;
202
203 Ok(())
204 }
205