Skip to main content

max / goingson

4.8 KB · 141 lines History Blame Raw
1 //! SQLite implementation of the DailyNoteRepository.
2
3 use async_trait::async_trait;
4 use chrono::{NaiveDate, Utc};
5 use sqlx::SqlitePool;
6 use goingson_core::{CoreError, DailyNote, DailyNoteId, DailyNoteRepository, Result, UserId};
7
8 use crate::utils::{format_datetime, parse_datetime, parse_uuid};
9
10 pub struct SqliteDailyNoteRepository {
11 pool: SqlitePool,
12 }
13
14 impl SqliteDailyNoteRepository {
15 #[tracing::instrument(skip_all)]
16 pub fn new(pool: SqlitePool) -> Self {
17 Self { pool }
18 }
19 }
20
21 #[derive(sqlx::FromRow)]
22 struct DailyNoteRow {
23 id: String,
24 user_id: String,
25 note_date: String,
26 went_well: String,
27 could_improve: String,
28 is_reviewed: i32,
29 reviewed_at: Option<String>,
30 created_at: String,
31 updated_at: String,
32 }
33
34 impl TryFrom<DailyNoteRow> for DailyNote {
35 type Error = CoreError;
36
37 fn try_from(row: DailyNoteRow) -> Result<Self> {
38 Ok(DailyNote {
39 id: parse_uuid(&row.id)?.into(),
40 user_id: parse_uuid(&row.user_id)?.into(),
41 note_date: NaiveDate::parse_from_str(&row.note_date, "%Y-%m-%d")
42 .map_err(|_| CoreError::parse("Invalid date"))?,
43 went_well: row.went_well,
44 could_improve: row.could_improve,
45 is_reviewed: row.is_reviewed != 0,
46 reviewed_at: row.reviewed_at.as_deref().map(parse_datetime).transpose()?,
47 created_at: parse_datetime(&row.created_at)?,
48 updated_at: parse_datetime(&row.updated_at)?,
49 })
50 }
51 }
52
53 #[async_trait]
54 impl DailyNoteRepository for SqliteDailyNoteRepository {
55 #[tracing::instrument(skip_all)]
56 async fn get_by_date(&self, user_id: UserId, date: NaiveDate) -> Result<Option<DailyNote>> {
57 let user_id_str = user_id.to_string();
58 let date_str = date.format("%Y-%m-%d").to_string();
59
60 let row: Option<DailyNoteRow> = sqlx::query_as(
61 "SELECT id, user_id, note_date, went_well, could_improve, is_reviewed, reviewed_at, created_at, updated_at
62 FROM daily_notes
63 WHERE user_id = ? AND note_date = ?"
64 )
65 .bind(&user_id_str)
66 .bind(&date_str)
67 .fetch_optional(&self.pool)
68 .await
69 .map_err(CoreError::database)?;
70
71 row.map(DailyNote::try_from).transpose()
72 }
73
74 #[tracing::instrument(skip_all)]
75 async fn list_all(&self, user_id: UserId) -> Result<Vec<DailyNote>> {
76 let rows: Vec<DailyNoteRow> = sqlx::query_as(
77 "SELECT id, user_id, note_date, went_well, could_improve, is_reviewed, reviewed_at, created_at, updated_at
78 FROM daily_notes
79 WHERE user_id = ?
80 ORDER BY note_date ASC"
81 )
82 .bind(user_id.to_string())
83 .fetch_all(&self.pool)
84 .await
85 .map_err(CoreError::database)?;
86
87 rows.into_iter().map(DailyNote::try_from).collect()
88 }
89
90 #[tracing::instrument(skip_all)]
91 async fn upsert(
92 &self,
93 user_id: UserId,
94 date: NaiveDate,
95 went_well: &str,
96 could_improve: &str,
97 is_reviewed: bool,
98 ) -> Result<DailyNote> {
99 let user_id_str = user_id.to_string();
100 let date_str = date.format("%Y-%m-%d").to_string();
101 let now = Utc::now();
102 let now_str = format_datetime(&now);
103 let reviewed_at_str = if is_reviewed { Some(now_str.clone()) } else { None };
104
105 let id = DailyNoteId::new();
106
107 // Atomic upsert. A non-transactional get-then-insert/update let two
108 // concurrent first-writes for the same (user, date) both see "none" and
109 // both INSERT, surfacing a raw UNIQUE violation (ultra-fuzz Run #28). The
110 // conflict now folds into an UPDATE; created_at is preserved on that path.
111 sqlx::query(
112 "INSERT INTO daily_notes (id, user_id, note_date, went_well, could_improve, is_reviewed, reviewed_at, created_at, updated_at)
113 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
114 ON CONFLICT(user_id, note_date) DO UPDATE SET
115 went_well = excluded.went_well,
116 could_improve = excluded.could_improve,
117 is_reviewed = excluded.is_reviewed,
118 reviewed_at = excluded.reviewed_at,
119 updated_at = excluded.updated_at"
120 )
121 .bind(id.to_string())
122 .bind(&user_id_str)
123 .bind(&date_str)
124 .bind(went_well)
125 .bind(could_improve)
126 .bind(is_reviewed as i32)
127 .bind(&reviewed_at_str)
128 .bind(&now_str)
129 .bind(&now_str)
130 .execute(&self.pool)
131 .await
132 .map_err(CoreError::database)?;
133
134 // Read back the canonical row — on the conflict path the stored id and
135 // created_at are the pre-existing ones, not the values we just generated.
136 self.get_by_date(user_id, date)
137 .await?
138 .ok_or_else(|| CoreError::internal("daily note missing immediately after upsert"))
139 }
140 }
141