Skip to main content

max / goingson

7.9 KB · 207 lines History Blame Raw
1 //! Table column whitelists and row-level apply logic (upsert, delete).
2 //!
3 //! # SQL Safety: `format!()` for table and column names
4 //!
5 //! Several functions in this module use `format!()` to interpolate table and column
6 //! names into SQL strings (e.g., `apply_upsert`, `apply_delete`).
7 //! This is safe because:
8 //!
9 //! 1. **Table names come from hardcoded constants** (`UPSERT_ORDER`, `DELETE_ORDER`) -- never
10 //! from user input or remote data.
11 //! 2. **Column names come from `table_columns()`**, a compile-time whitelist of `&'static str`
12 //! literals -- also never from user input.
13 //! 3. **All user-supplied values** (row IDs, field data) are passed through `sqlx::query().bind()`,
14 //! which parameterizes them safely.
15 //! 4. **Unknown table names are rejected** before any SQL is constructed: both `apply_upsert` and
16 //! `apply_delete` return an error if `table_columns()` returns `None`.
17
18 use goingson_core::CoreError;
19 use sqlx::SqliteConnection;
20
21 use super::EMAIL_ACCOUNT_SYNC_COLS;
22
23 /// Return the syncable column whitelist for a given table, or `None` if unknown.
24 pub(crate) fn table_columns(table: &str) -> Option<&'static [&'static str]> {
25 match table {
26 "projects" => Some(&[
27 "id", "name", "description", "project_type", "status", "created_at", "user_id",
28 ]),
29 "tasks" => Some(&[
30 "id", "project_id", "description", "status", "priority", "due", "tags", "urgency",
31 "recurrence", "created_at", "user_id", "recurrence_parent_id", "source_email_id",
32 "snoozed_until", "waiting_for_response", "waiting_since", "expected_response_date",
33 "scheduled_start", "scheduled_duration", "is_focus", "focus_set_at", "contact_id",
34 "milestone_id", "completed_at", "estimated_minutes", "actual_minutes",
35 ]),
36 "events" => Some(&[
37 "id", "project_id", "title", "description", "start_time", "end_time", "location",
38 "user_id", "linked_task_id", "recurrence", "recurrence_parent_id", "contact_id",
39 "block_type", "external_source", "external_id", "is_read_only", "snoozed_until",
40 "reminder_offsets_seconds",
41 ]),
42 "contacts" => Some(&[
43 "id", "user_id", "display_name", "nickname", "company", "title", "notes", "tags",
44 "birthday", "timezone", "external_source", "external_id", "created_at", "updated_at",
45 ]),
46 "contact_emails" => Some(&["id", "contact_id", "address", "label", "is_primary"]),
47 "contact_phones" => Some(&["id", "contact_id", "number", "label", "is_primary"]),
48 "contact_social_handles" => Some(&["id", "contact_id", "platform", "handle", "url"]),
49 "contact_custom_fields" => Some(&["id", "contact_id", "label", "value", "url"]),
50 "annotations" => Some(&["id", "task_id", "timestamp", "note"]),
51 "subtasks" => Some(&[
52 "id", "task_id", "text", "is_completed", "position", "created_at", "linked_task_id",
53 ]),
54 "milestones" => Some(&[
55 "id", "user_id", "project_id", "name", "description", "position", "target_date",
56 "status", "created_at",
57 ]),
58 "time_sessions" => Some(&[
59 "id", "task_id", "user_id", "started_at", "ended_at", "duration_minutes",
60 "created_at",
61 ]),
62 "attachments" => Some(&[
63 "id", "user_id", "task_id", "project_id", "filename",
64 "file_size", "mime_type", "blob_hash", "source_email_id", "created_at",
65 ]),
66 "sync_accounts" => Some(&[
67 "id", "user_id", "provider", "account_name", "email",
68 "sync_calendars", "sync_contacts", "calendar_ids",
69 "sync_interval_minutes", "enabled", "created_at",
70 ]),
71 "email_accounts" => Some(EMAIL_ACCOUNT_SYNC_COLS),
72 "daily_notes" => Some(&[
73 "id", "user_id", "note_date", "went_well", "could_improve",
74 "is_reviewed", "reviewed_at", "created_at", "updated_at",
75 ]),
76 _ => None,
77 }
78 }
79
80 /// Apply an INSERT OR REPLACE for a remote change.
81 pub(crate) async fn apply_upsert(
82 conn: &mut SqliteConnection,
83 table: &str,
84 _row_id: &str,
85 data: &serde_json::Value,
86 ) -> Result<(), CoreError> {
87 // Email accounts use ON CONFLICT to preserve local credentials
88 if table == "email_accounts" {
89 return apply_email_account_upsert(conn, data).await;
90 }
91
92 let columns = table_columns(table)
93 .ok_or_else(|| CoreError::bad_request(format!("unknown syncable table: {}", table)))?;
94
95 let col_list = columns.join(", ");
96 let placeholders = columns.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
97 let sql = format!(
98 "INSERT OR REPLACE INTO {} ({}) VALUES ({})",
99 table, col_list, placeholders
100 );
101
102 let mut query = sqlx::query(&sql);
103
104 for col in columns {
105 query = bind_json_value(query, &data[*col]);
106 }
107
108 query
109 .execute(&mut *conn)
110 .await
111 .map_err(CoreError::database)?;
112
113 Ok(())
114 }
115
116 /// Apply an upsert for email_accounts that preserves local credentials.
117 ///
118 /// Uses INSERT ... ON CONFLICT(id) DO UPDATE to only touch the 16 config columns,
119 /// leaving `password`, `oauth2_access_token`, `oauth2_refresh_token`, and
120 /// `oauth2_token_expires_at` untouched on existing rows. New rows get `password = ''`
121 /// to satisfy the NOT NULL constraint.
122 async fn apply_email_account_upsert(
123 conn: &mut SqliteConnection,
124 data: &serde_json::Value,
125 ) -> Result<(), CoreError> {
126 let cols = EMAIL_ACCOUNT_SYNC_COLS;
127
128 // INSERT columns: 16 sync cols + password (hardcoded to '')
129 let mut insert_cols: Vec<&str> = cols.to_vec();
130 insert_cols.push("password");
131
132 let col_list = insert_cols.join(", ");
133 let placeholders = insert_cols.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
134
135 // ON CONFLICT: only update the 16 sync columns
136 let update_set = cols
137 .iter()
138 .filter(|c| **c != "id")
139 .map(|c| format!("{} = excluded.{}", c, c))
140 .collect::<Vec<_>>()
141 .join(", ");
142
143 let sql = format!(
144 "INSERT INTO email_accounts ({}) VALUES ({}) ON CONFLICT(id) DO UPDATE SET {}",
145 col_list, placeholders, update_set
146 );
147
148 let mut query = sqlx::query(&sql);
149
150 // Bind the 16 sync columns from data
151 for col in cols {
152 query = bind_json_value(query, &data[*col]);
153 }
154
155 // Bind password = '' for the INSERT
156 query = query.bind("");
157
158 query
159 .execute(&mut *conn)
160 .await
161 .map_err(CoreError::database)?;
162
163 Ok(())
164 }
165
166 /// Bind a JSON value to a sqlx query.
167 pub(crate) fn bind_json_value<'q>(
168 query: sqlx::query::Query<'q, sqlx::Sqlite, sqlx::sqlite::SqliteArguments<'q>>,
169 val: &'q serde_json::Value,
170 ) -> sqlx::query::Query<'q, sqlx::Sqlite, sqlx::sqlite::SqliteArguments<'q>> {
171 match val {
172 serde_json::Value::String(s) => query.bind(s.as_str()),
173 serde_json::Value::Number(n) => {
174 if let Some(i) = n.as_i64() {
175 query.bind(i)
176 } else if let Some(f) = n.as_f64() {
177 query.bind(f)
178 } else {
179 query.bind(None::<String>)
180 }
181 }
182 serde_json::Value::Bool(b) => query.bind(if *b { 1i32 } else { 0i32 }),
183 serde_json::Value::Null => query.bind(None::<String>),
184 _ => {
185 // Arrays/objects: serialize as JSON string -- need owned String
186 query.bind(val.to_string())
187 }
188 }
189 }
190
191 /// Apply a DELETE for a remote change.
192 pub(crate) async fn apply_delete(conn: &mut SqliteConnection, table: &str, row_id: &str) -> Result<(), CoreError> {
193 // Validate table name is in our whitelist
194 if table_columns(table).is_none() {
195 return Err(CoreError::bad_request(format!("unknown syncable table: {}", table)));
196 }
197
198 let sql = format!("DELETE FROM {} WHERE id = ?", table);
199 sqlx::query(&sql)
200 .bind(row_id)
201 .execute(&mut *conn)
202 .await
203 .map_err(CoreError::database)?;
204
205 Ok(())
206 }
207