Skip to main content

max / goingson

9.9 KB · 312 lines History Blame Raw
1 //! SQLite implementation of the MonthlyReviewRepository.
2 //!
3 //! Provides monthly goal and reflection persistence.
4
5 use async_trait::async_trait;
6 use chrono::Utc;
7 use sqlx::SqlitePool;
8 use goingson_core::{
9 CoreError, MonthlyGoal, MonthlyGoalId, MonthlyGoalStatus, MonthlyReflection,
10 MonthlyReflectionId, MonthlyReviewRepository, Result, UserId,
11 };
12
13 use crate::utils::{format_datetime, parse_datetime, parse_uuid};
14
15 /// SQLite-backed implementation of [`MonthlyReviewRepository`].
16 pub struct SqliteMonthlyReviewRepository {
17 pool: SqlitePool,
18 }
19
20 impl SqliteMonthlyReviewRepository {
21 #[tracing::instrument(skip_all)]
22 pub fn new(pool: SqlitePool) -> Self {
23 Self { pool }
24 }
25 }
26
27 // ============ Row Types ============
28
29 #[derive(sqlx::FromRow)]
30 struct MonthlyGoalRow {
31 id: String,
32 user_id: String,
33 month: String,
34 text: String,
35 status: String,
36 position: i32,
37 created_at: String,
38 updated_at: String,
39 }
40
41 #[derive(sqlx::FromRow)]
42 struct MonthlyReflectionRow {
43 id: String,
44 user_id: String,
45 month: String,
46 highlight_text: String,
47 change_text: String,
48 completed_at: String,
49 }
50
51 // ============ Conversions ============
52
53 impl TryFrom<MonthlyGoalRow> for MonthlyGoal {
54 type Error = CoreError;
55
56 fn try_from(row: MonthlyGoalRow) -> Result<Self> {
57 Ok(MonthlyGoal {
58 id: parse_uuid(&row.id)?.into(),
59 user_id: parse_uuid(&row.user_id)?.into(),
60 month: row.month,
61 text: row.text,
62 status: row.status.parse()?,
63 position: row.position,
64 created_at: parse_datetime(&row.created_at)?,
65 updated_at: parse_datetime(&row.updated_at)?,
66 })
67 }
68 }
69
70 impl TryFrom<MonthlyReflectionRow> for MonthlyReflection {
71 type Error = CoreError;
72
73 fn try_from(row: MonthlyReflectionRow) -> Result<Self> {
74 Ok(MonthlyReflection {
75 id: parse_uuid(&row.id)?.into(),
76 user_id: parse_uuid(&row.user_id)?.into(),
77 month: row.month,
78 highlight_text: row.highlight_text,
79 change_text: row.change_text,
80 completed_at: parse_datetime(&row.completed_at)?,
81 })
82 }
83 }
84
85 // ============ Repository Implementation ============
86
87 #[async_trait]
88 impl MonthlyReviewRepository for SqliteMonthlyReviewRepository {
89 #[tracing::instrument(skip_all)]
90 async fn list_goals(&self, user_id: UserId, month: &str) -> Result<Vec<MonthlyGoal>> {
91 let user_id_str = user_id.to_string();
92
93 let rows: Vec<MonthlyGoalRow> = sqlx::query_as(
94 "SELECT id, user_id, month, text, status, position, created_at, updated_at
95 FROM monthly_goals
96 WHERE user_id = ? AND month = ?
97 ORDER BY position"
98 )
99 .bind(&user_id_str)
100 .bind(month)
101 .fetch_all(&self.pool)
102 .await
103 .map_err(CoreError::database)?;
104
105 rows.into_iter().map(MonthlyGoal::try_from).collect()
106 }
107
108 #[tracing::instrument(skip_all)]
109 async fn list_all_goals(&self, user_id: UserId) -> Result<Vec<MonthlyGoal>> {
110 let user_id_str = user_id.to_string();
111
112 let rows: Vec<MonthlyGoalRow> = sqlx::query_as(
113 "SELECT id, user_id, month, text, status, position, created_at, updated_at
114 FROM monthly_goals
115 WHERE user_id = ?
116 ORDER BY month, position",
117 )
118 .bind(&user_id_str)
119 .fetch_all(&self.pool)
120 .await
121 .map_err(CoreError::database)?;
122
123 rows.into_iter().map(MonthlyGoal::try_from).collect()
124 }
125
126 #[tracing::instrument(skip_all)]
127 async fn list_all_reflections(&self, user_id: UserId) -> Result<Vec<MonthlyReflection>> {
128 let user_id_str = user_id.to_string();
129
130 let rows: Vec<MonthlyReflectionRow> = sqlx::query_as(
131 "SELECT id, user_id, month, highlight_text, change_text, completed_at
132 FROM monthly_reflections
133 WHERE user_id = ?
134 ORDER BY month",
135 )
136 .bind(&user_id_str)
137 .fetch_all(&self.pool)
138 .await
139 .map_err(CoreError::database)?;
140
141 rows.into_iter().map(MonthlyReflection::try_from).collect()
142 }
143
144 #[tracing::instrument(skip_all)]
145 async fn upsert_goal(&self, user_id: UserId, month: &str, text: &str, position: i32) -> Result<MonthlyGoal> {
146 let user_id_str = user_id.to_string();
147 let now = format_datetime(&Utc::now());
148
149 // monthly_goals has no unique key on (user_id, month, position) and one
150 // can't be added safely (it would make two devices' position-1 goals
151 // collide on sync apply). So make the get-then-write atomic with a
152 // transaction instead of an ON CONFLICT upsert — closes the race that
153 // could double-insert a goal at the same position.
154 let mut tx = self.pool.begin().await.map_err(CoreError::database)?;
155
156 let existing: Option<MonthlyGoalRow> = sqlx::query_as(
157 "SELECT id, user_id, month, text, status, position, created_at, updated_at
158 FROM monthly_goals
159 WHERE user_id = ? AND month = ? AND position = ?"
160 )
161 .bind(&user_id_str)
162 .bind(month)
163 .bind(position)
164 .fetch_optional(&mut *tx)
165 .await
166 .map_err(CoreError::database)?;
167
168 let goal = if let Some(existing) = existing {
169 let id = existing.id.clone();
170 sqlx::query(
171 "UPDATE monthly_goals SET text = ?, updated_at = ? WHERE id = ?"
172 )
173 .bind(text)
174 .bind(&now)
175 .bind(&id)
176 .execute(&mut *tx)
177 .await
178 .map_err(CoreError::database)?;
179
180 let mut goal = MonthlyGoal::try_from(existing)?;
181 goal.text = text.to_string();
182 goal.updated_at = Utc::now();
183 goal
184 } else {
185 let id = MonthlyGoalId::new();
186 sqlx::query(
187 "INSERT INTO monthly_goals (id, user_id, month, text, status, position, created_at, updated_at)
188 VALUES (?, ?, ?, ?, 'active', ?, ?, ?)"
189 )
190 .bind(id.to_string())
191 .bind(&user_id_str)
192 .bind(month)
193 .bind(text)
194 .bind(position)
195 .bind(&now)
196 .bind(&now)
197 .execute(&mut *tx)
198 .await
199 .map_err(CoreError::database)?;
200
201 let now_dt = Utc::now();
202 MonthlyGoal {
203 id,
204 user_id,
205 month: month.to_string(),
206 text: text.to_string(),
207 status: MonthlyGoalStatus::Active,
208 position,
209 created_at: now_dt,
210 updated_at: now_dt,
211 }
212 };
213
214 tx.commit().await.map_err(CoreError::database)?;
215 Ok(goal)
216 }
217
218 #[tracing::instrument(skip_all)]
219 async fn update_goal_status(&self, id: MonthlyGoalId, user_id: UserId, status: &MonthlyGoalStatus) -> Result<Option<MonthlyGoal>> {
220 let user_id_str = user_id.to_string();
221 let id_str = id.to_string();
222 let now = format_datetime(&Utc::now());
223
224 let result = sqlx::query(
225 "UPDATE monthly_goals SET status = ?, updated_at = ? WHERE id = ? AND user_id = ?"
226 )
227 .bind(status.as_str())
228 .bind(&now)
229 .bind(&id_str)
230 .bind(&user_id_str)
231 .execute(&self.pool)
232 .await
233 .map_err(CoreError::database)?;
234
235 if result.rows_affected() == 0 {
236 return Ok(None);
237 }
238
239 let row: MonthlyGoalRow = sqlx::query_as(
240 "SELECT id, user_id, month, text, status, position, created_at, updated_at
241 FROM monthly_goals WHERE id = ?"
242 )
243 .bind(&id_str)
244 .fetch_one(&self.pool)
245 .await
246 .map_err(CoreError::database)?;
247
248 Ok(Some(MonthlyGoal::try_from(row)?))
249 }
250
251 #[tracing::instrument(skip_all)]
252 async fn delete_goal(&self, id: MonthlyGoalId, user_id: UserId) -> Result<bool> {
253 let result = sqlx::query(
254 "DELETE FROM monthly_goals WHERE id = ? AND user_id = ?"
255 )
256 .bind(id.to_string())
257 .bind(user_id.to_string())
258 .execute(&self.pool)
259 .await
260 .map_err(CoreError::database)?;
261
262 Ok(result.rows_affected() > 0)
263 }
264
265 #[tracing::instrument(skip_all)]
266 async fn get_reflection(&self, user_id: UserId, month: &str) -> Result<Option<MonthlyReflection>> {
267 let row: Option<MonthlyReflectionRow> = sqlx::query_as(
268 "SELECT id, user_id, month, highlight_text, change_text, completed_at
269 FROM monthly_reflections
270 WHERE user_id = ? AND month = ?"
271 )
272 .bind(user_id.to_string())
273 .bind(month)
274 .fetch_optional(&self.pool)
275 .await
276 .map_err(CoreError::database)?;
277
278 row.map(MonthlyReflection::try_from).transpose()
279 }
280
281 #[tracing::instrument(skip_all)]
282 async fn upsert_reflection(&self, user_id: UserId, month: &str, highlight: &str, change: &str) -> Result<MonthlyReflection> {
283 let user_id_str = user_id.to_string();
284 let now = Utc::now();
285 let now_str = format_datetime(&now);
286
287 // Atomic upsert on the (user_id, month) unique key — no get-then-write
288 // race.
289 sqlx::query(
290 "INSERT INTO monthly_reflections (id, user_id, month, highlight_text, change_text, completed_at)
291 VALUES (?, ?, ?, ?, ?, ?)
292 ON CONFLICT(user_id, month) DO UPDATE SET
293 highlight_text = excluded.highlight_text,
294 change_text = excluded.change_text,
295 completed_at = excluded.completed_at",
296 )
297 .bind(MonthlyReflectionId::new().to_string())
298 .bind(&user_id_str)
299 .bind(month)
300 .bind(highlight)
301 .bind(change)
302 .bind(&now_str)
303 .execute(&self.pool)
304 .await
305 .map_err(CoreError::database)?;
306
307 self.get_reflection(user_id, month)
308 .await?
309 .ok_or_else(|| CoreError::database_msg("monthly reflection vanished after upsert"))
310 }
311 }
312