Skip to main content

max / goingson

31.6 KB · 842 lines History Blame Raw
1 //! Transactional backup restore.
2 //!
3 //! A single all-or-nothing restore over one SQLite transaction: either the entire
4 //! backup is merged into the database, or nothing is. This replaces the previous
5 //! per-repository orchestration in `goingson_core::backup_restore`, which ran each
6 //! entity through a separate pooled connection (so a mid-restore failure left a
7 //! permanent half-state) and rebuilt task sub-collections through the normal create
8 //! helpers -- losing subtask completion state and ordering, and annotation
9 //! timestamps (ultra-fuzz Run #26 SERIOUS finding).
10 //!
11 //! Restore is a *merge*: every row is inserted verbatim with `INSERT OR IGNORE`,
12 //! preserving its original id and every field, and existing rows are left untouched.
13 //! Because ids are preserved, restoring the same backup twice is idempotent. The sync
14 //! changelog triggers fire as normal, so restored rows propagate to other devices.
15
16 use goingson_core::backup_restore::{RestoreInput, RestoreResult};
17 use goingson_core::models::SortDirection;
18 use goingson_core::{CoreError, DbValue, Result, UserId};
19 use sqlx::{Sqlite, SqlitePool, Transaction};
20
21 use crate::utils::{format_datetime, format_datetime_opt};
22
23 /// Every table a full backup must capture and restore -- the single source of
24 /// truth for backup completeness. `collect_full_export` gathers each of these and
25 /// `restore_all` writes each back; the `backup_tables_match_schema` test asserts
26 /// this list equals the live schema minus [`EXCLUDED_TABLES`], so adding a table to
27 /// the schema without registering it here (or excluding it) is a test failure. This
28 /// is the structural fix for the recurring "backup silently omitted table X" finding
29 /// (ultra-fuzz CHRONIC-D), mirroring the trigger/synced-column round-trip test.
30 pub const BACKUP_TABLES: &[&str] = &[
31 "projects",
32 "tasks",
33 "annotations",
34 "subtasks",
35 "task_status_tokens",
36 "events",
37 "emails",
38 "contacts",
39 "contact_emails",
40 "contact_phones",
41 "contact_social_handles",
42 "contact_custom_fields",
43 "milestones",
44 "time_sessions",
45 "daily_notes",
46 "attachments",
47 "sync_accounts",
48 "saved_views",
49 "weekly_reviews",
50 "monthly_goals",
51 "monthly_reflections",
52 ];
53
54 /// Tables deliberately excluded from backups, each for a documented reason. A table
55 /// must appear in either [`BACKUP_TABLES`] or here; the coverage test fails otherwise.
56 pub const EXCLUDED_TABLES: &[&str] = &[
57 "users", // single fixed desktop user, recreated on init
58 "email_accounts", // credentials/config; secrets live in the OS keychain, re-auth on restore
59 "backup_settings", // local backup cadence/retention config
60 "backup_settings_new", // transient table-rename scaffold (absent post-migration)
61 "imap_folder_sync_state", // sync-transient IMAP state, re-derived on next sync
62 "sync_changelog", // sync internals, regenerated
63 "sync_state", // sync cursors/flags, internal
64 "hlc_state", // hybrid logical clock, device-local sync internal
65 "sync_committed_hlc", // per-row committed HLC clock store, device-local sync internal
66 ];
67
68 /// Restore a backup into the database as a single transaction.
69 ///
70 /// On any error the transaction is dropped without committing, so the database is
71 /// left exactly as it was before the call.
72 pub async fn restore_all(
73 pool: &SqlitePool,
74 user_id: UserId,
75 input: &RestoreInput,
76 ) -> Result<RestoreResult> {
77 let mut tx = pool.begin().await.map_err(CoreError::database)?;
78 let mut result = RestoreResult::default();
79
80 // Defer foreign-key enforcement to COMMIT. Without this, a self-referential FK
81 // (tasks/events `recurrence_parent_id`) raises immediately when a child instance
82 // is inserted before its parent root -- and because the backup is emitted in
83 // urgency/created order, the child routinely precedes the parent. An immediate
84 // FK violation (code 787) aborts the whole transaction, losing the entire
85 // restore (ultra-fuzz Run #27 C1). Deferring checks them once, at commit, by
86 // which point every referent in the backup has been inserted; only a genuinely
87 // dangling reference still fails. `INSERT OR IGNORE` does NOT suppress FK
88 // violations -- it only suppresses UNIQUE/NOT NULL/CHECK conflicts.
89 sqlx::query("PRAGMA defer_foreign_keys = ON")
90 .execute(&mut *tx)
91 .await
92 .map_err(CoreError::database)?;
93
94 // The insert order below is no longer load-bearing for FK correctness (deferred
95 // checks make any order valid as long as every referent is present), but is kept
96 // roots-first for readability. Every table in `BACKUP_TABLES` must be restored
97 // here; the round-trip test enforces it.
98 restore_projects(&mut tx, user_id, input, &mut result).await?;
99 restore_contacts_with_children(&mut tx, user_id, input, &mut result).await?;
100 restore_milestones(&mut tx, user_id, input, &mut result).await?;
101 restore_emails(&mut tx, user_id, input, &mut result).await?;
102 restore_tasks_with_children(&mut tx, user_id, input, &mut result).await?;
103 restore_time_sessions(&mut tx, user_id, input, &mut result).await?;
104 restore_events(&mut tx, user_id, input, &mut result).await?;
105 restore_attachments(&mut tx, user_id, input, &mut result).await?;
106 restore_daily_notes(&mut tx, user_id, input, &mut result).await?;
107 restore_sync_accounts(&mut tx, user_id, input, &mut result).await?;
108 restore_saved_views(&mut tx, user_id, input, &mut result).await?;
109 restore_weekly_reviews(&mut tx, user_id, input, &mut result).await?;
110 restore_monthly_goals(&mut tx, user_id, input, &mut result).await?;
111 restore_monthly_reflections(&mut tx, user_id, input, &mut result).await?;
112
113 tx.commit().await.map_err(CoreError::database)?;
114 Ok(result)
115 }
116
117 async fn restore_projects(
118 tx: &mut Transaction<'_, Sqlite>,
119 user_id: UserId,
120 input: &RestoreInput,
121 result: &mut RestoreResult,
122 ) -> Result<()> {
123 for project in &input.projects {
124 let affected = sqlx::query(
125 "INSERT OR IGNORE INTO projects (id, user_id, name, description, project_type, status, created_at) \
126 VALUES (?, ?, ?, ?, ?, ?, ?)",
127 )
128 .bind(project.id.to_string())
129 .bind(user_id.to_string())
130 .bind(&project.name)
131 .bind(&project.description)
132 .bind(project.project_type.db_value())
133 .bind(project.status.db_value())
134 .bind(format_datetime(&project.created_at))
135 .execute(&mut **tx)
136 .await
137 .map_err(CoreError::database)?
138 .rows_affected();
139 if affected > 0 {
140 result.projects_restored += 1;
141 }
142 }
143 Ok(())
144 }
145
146 async fn restore_tasks_with_children(
147 tx: &mut Transaction<'_, Sqlite>,
148 user_id: UserId,
149 input: &RestoreInput,
150 result: &mut RestoreResult,
151 ) -> Result<()> {
152 // First pass: task row + annotations + text-only subtasks. Children are restored
153 // only when the parent task is newly inserted (a re-restore skips them).
154 for task in &input.tasks {
155 let tags_json = serde_json::to_string(&task.tags).unwrap_or_else(|_| "[]".to_string());
156 let recurrence_rule_json = task
157 .recurrence_rule
158 .as_ref()
159 .map(|r| serde_json::to_string(r).unwrap_or_default());
160
161 let affected = sqlx::query(
162 "INSERT OR IGNORE INTO tasks (\
163 id, user_id, project_id, contact_id, milestone_id, description, status, \
164 priority, due, tags, urgency, recurrence, recurrence_rule, recurrence_parent_id, \
165 source_email_id, snoozed_until, waiting_for_response, waiting_since, expected_response_date, \
166 scheduled_start, scheduled_duration, estimated_minutes, actual_minutes, \
167 created_at, completed_at, is_focus, focus_set_at\
168 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
169 )
170 .bind(task.id.to_string())
171 .bind(user_id.to_string())
172 .bind(task.project_id.map(|p| p.to_string()))
173 .bind(task.contact_id.map(|c| c.to_string()))
174 .bind(task.milestone_id.map(|m| m.to_string()))
175 .bind(&task.description)
176 .bind(task.status.db_value())
177 .bind(task.priority.db_value())
178 .bind(format_datetime_opt(task.due))
179 .bind(&tags_json)
180 .bind(task.urgency)
181 .bind(task.recurrence.db_value())
182 .bind(&recurrence_rule_json)
183 .bind(task.recurrence_parent_id.map(|p| p.to_string()))
184 .bind(task.source_email_id.map(|e| e.to_string()))
185 .bind(format_datetime_opt(task.snoozed_until))
186 .bind(if task.waiting_for_response { 1 } else { 0 })
187 .bind(format_datetime_opt(task.waiting_since))
188 .bind(format_datetime_opt(task.expected_response_date))
189 .bind(format_datetime_opt(task.scheduled_start))
190 .bind(task.scheduled_duration)
191 .bind(task.estimated_minutes)
192 .bind(task.actual_minutes)
193 .bind(format_datetime(&task.created_at))
194 .bind(task.completed_at.map(|d| format_datetime(&d)))
195 .bind(if task.is_focus { 1 } else { 0 })
196 .bind(task.focus_set_at.map(|d| format_datetime(&d)))
197 .execute(&mut **tx)
198 .await
199 .map_err(CoreError::database)?
200 .rows_affected();
201
202 if affected == 0 {
203 continue;
204 }
205 result.tasks_restored += 1;
206
207 // Annotations -- preserve the original timestamp verbatim.
208 for annotation in &task.annotations {
209 let a = sqlx::query(
210 "INSERT OR IGNORE INTO annotations (id, task_id, timestamp, note) VALUES (?, ?, ?, ?)",
211 )
212 .bind(annotation.id.to_string())
213 .bind(task.id.to_string())
214 .bind(format_datetime(&annotation.timestamp))
215 .bind(&annotation.note)
216 .execute(&mut **tx)
217 .await
218 .map_err(CoreError::database)?
219 .rows_affected();
220 if a > 0 {
221 result.annotations_restored += 1;
222 }
223 }
224
225 // Text-only subtasks -- preserve is_completed and position verbatim.
226 // Linked subtasks are deferred to the second pass (their linked_task_id FK
227 // requires every task row to exist first).
228 for subtask in &task.subtasks {
229 if subtask.linked_task_id.is_some() {
230 continue;
231 }
232 let s = sqlx::query(
233 "INSERT OR IGNORE INTO subtasks (id, task_id, text, is_completed, position) \
234 VALUES (?, ?, ?, ?, ?)",
235 )
236 .bind(subtask.id.to_string())
237 .bind(task.id.to_string())
238 .bind(&subtask.text)
239 .bind(if subtask.is_completed { 1 } else { 0 })
240 .bind(subtask.position)
241 .execute(&mut **tx)
242 .await
243 .map_err(CoreError::database)?
244 .rows_affected();
245 if s > 0 {
246 result.subtasks_restored += 1;
247 }
248 }
249
250 // Status tokens -- preserve kind, state, is_primary, position, and the
251 // deterministic id verbatim.
252 for token in &task.status_tokens {
253 let c = sqlx::query(
254 "INSERT OR IGNORE INTO task_status_tokens (id, task_id, kind, reference, state, is_primary, position) \
255 VALUES (?, ?, ?, ?, ?, ?, ?)",
256 )
257 .bind(token.id.to_string())
258 .bind(task.id.to_string())
259 .bind(&token.kind)
260 .bind(&token.reference)
261 .bind(token.state.db_value())
262 .bind(if token.is_primary { 1 } else { 0 })
263 .bind(token.position)
264 .execute(&mut **tx)
265 .await
266 .map_err(CoreError::database)?
267 .rows_affected();
268 if c > 0 {
269 result.status_tokens_restored += 1;
270 }
271 }
272 }
273
274 // Second pass: linked subtasks, now that all task rows exist. Verbatim and
275 // idempotent (INSERT OR IGNORE on the preserved id), so it is safe to run for
276 // every task regardless of whether the parent was newly inserted.
277 for task in &input.tasks {
278 for subtask in &task.subtasks {
279 let Some(linked_id) = subtask.linked_task_id else {
280 continue;
281 };
282 let s = sqlx::query(
283 "INSERT OR IGNORE INTO subtasks (id, task_id, text, is_completed, position, linked_task_id) \
284 VALUES (?, ?, ?, ?, ?, ?)",
285 )
286 .bind(subtask.id.to_string())
287 .bind(task.id.to_string())
288 .bind(&subtask.text)
289 .bind(if subtask.is_completed { 1 } else { 0 })
290 .bind(subtask.position)
291 .bind(linked_id.to_string())
292 .execute(&mut **tx)
293 .await
294 .map_err(CoreError::database)?
295 .rows_affected();
296 if s > 0 {
297 result.subtasks_restored += 1;
298 }
299 }
300 }
301
302 Ok(())
303 }
304
305 async fn restore_events(
306 tx: &mut Transaction<'_, Sqlite>,
307 user_id: UserId,
308 input: &RestoreInput,
309 result: &mut RestoreResult,
310 ) -> Result<()> {
311 for event in &input.events {
312 let recurrence_rule_json = event
313 .recurrence_rule
314 .as_ref()
315 .map(|r| serde_json::to_string(r).unwrap_or_default());
316 let reminder_offsets_json = if event.reminder_offsets_seconds.is_empty() {
317 None
318 } else {
319 Some(serde_json::to_string(&event.reminder_offsets_seconds).unwrap_or_default())
320 };
321
322 let affected = sqlx::query(
323 "INSERT OR IGNORE INTO events (\
324 id, user_id, project_id, title, description, start_time, end_time, location, \
325 linked_task_id, recurrence, recurrence_rule, recurrence_parent_id, contact_id, \
326 block_type, external_source, external_id, is_read_only, snoozed_until, reminder_offsets_seconds\
327 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
328 )
329 .bind(event.id.to_string())
330 .bind(user_id.to_string())
331 .bind(event.project_id.map(|p| p.to_string()))
332 .bind(&event.title)
333 .bind(&event.description)
334 .bind(format_datetime(&event.start_time))
335 .bind(format_datetime_opt(event.end_time))
336 .bind(&event.location)
337 .bind(event.linked_task_id.map(|t| t.to_string()))
338 .bind(event.recurrence.db_value())
339 .bind(&recurrence_rule_json)
340 .bind(event.recurrence_parent_id.map(|p| p.to_string()))
341 .bind(event.contact_id.map(|c| c.to_string()))
342 .bind(event.block_type.as_ref().map(|b| b.db_value()))
343 .bind(&event.external_source)
344 .bind(&event.external_id)
345 .bind(if event.is_read_only { 1 } else { 0 })
346 .bind(format_datetime_opt(event.snoozed_until))
347 .bind(&reminder_offsets_json)
348 .execute(&mut **tx)
349 .await
350 .map_err(CoreError::database)?
351 .rows_affected();
352 if affected > 0 {
353 result.events_restored += 1;
354 }
355 }
356 Ok(())
357 }
358
359 async fn restore_emails(
360 tx: &mut Transaction<'_, Sqlite>,
361 user_id: UserId,
362 input: &RestoreInput,
363 result: &mut RestoreResult,
364 ) -> Result<()> {
365 for email in &input.emails {
366 // Dedupe by message_id when present (the same message can have a different
367 // row id across devices); otherwise the preserved id + INSERT OR IGNORE
368 // makes the restore idempotent.
369 if let Some(msg_id) = &email.message_id {
370 let existing: (i64,) = sqlx::query_as(
371 "SELECT COUNT(*) FROM emails WHERE user_id = ? AND message_id = ?",
372 )
373 .bind(user_id.to_string())
374 .bind(msg_id)
375 .fetch_one(&mut **tx)
376 .await
377 .map_err(CoreError::database)?;
378 if existing.0 > 0 {
379 continue;
380 }
381 }
382
383 let labels_json =
384 serde_json::to_string(&email.labels).unwrap_or_else(|_| "[]".to_string());
385 let affected = sqlx::query(
386 "INSERT OR IGNORE INTO emails (\
387 id, user_id, project_id, from_address, to_address, subject, body, html_body, \
388 is_read, is_archived, received_at, message_id, in_reply_to, thread_id, is_outgoing, \
389 labels, is_draft, cc_address, bcc_address, snoozed_until, waiting_for_response, \
390 waiting_since, expected_response_date\
391 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
392 )
393 .bind(email.id.to_string())
394 .bind(user_id.to_string())
395 .bind(email.project_id.map(|p| p.to_string()))
396 .bind(&email.from)
397 .bind(&email.to)
398 .bind(&email.subject)
399 .bind(&email.body)
400 .bind(&email.html_body)
401 .bind(if email.is_read { 1 } else { 0 })
402 .bind(if email.is_archived { 1 } else { 0 })
403 .bind(format_datetime(&email.received_at))
404 .bind(&email.message_id)
405 .bind(&email.in_reply_to)
406 .bind(&email.thread_id)
407 .bind(if email.is_outgoing { 1 } else { 0 })
408 .bind(&labels_json)
409 .bind(if email.is_draft { 1 } else { 0 })
410 .bind(&email.cc_address)
411 .bind(&email.bcc_address)
412 .bind(format_datetime_opt(email.snoozed_until))
413 .bind(if email.waiting_for_response { 1 } else { 0 })
414 .bind(format_datetime_opt(email.waiting_since))
415 .bind(format_datetime_opt(email.expected_response_date))
416 .execute(&mut **tx)
417 .await
418 .map_err(CoreError::database)?
419 .rows_affected();
420 if affected > 0 {
421 result.emails_restored += 1;
422 }
423 }
424 Ok(())
425 }
426
427 async fn restore_contacts_with_children(
428 tx: &mut Transaction<'_, Sqlite>,
429 user_id: UserId,
430 input: &RestoreInput,
431 result: &mut RestoreResult,
432 ) -> Result<()> {
433 for contact in &input.contacts {
434 let tags_json = serde_json::to_string(&contact.tags).unwrap_or_else(|_| "[]".to_string());
435 let birthday_str = contact.birthday.map(|d| d.format("%Y-%m-%d").to_string());
436
437 let affected = sqlx::query(
438 "INSERT OR IGNORE INTO contacts (\
439 id, user_id, display_name, nickname, company, title, notes, tags, birthday, \
440 timezone, external_source, external_id, is_implicit, created_at, updated_at\
441 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
442 )
443 .bind(contact.id.to_string())
444 .bind(user_id.to_string())
445 .bind(&contact.display_name)
446 .bind(&contact.nickname)
447 .bind(&contact.company)
448 .bind(&contact.title)
449 .bind(&contact.notes)
450 .bind(&tags_json)
451 .bind(&birthday_str)
452 .bind(&contact.timezone)
453 .bind(&contact.external_source)
454 .bind(&contact.external_id)
455 .bind(if contact.is_implicit { 1 } else { 0 })
456 .bind(format_datetime(&contact.created_at))
457 .bind(format_datetime(&contact.updated_at))
458 .execute(&mut **tx)
459 .await
460 .map_err(CoreError::database)?
461 .rows_affected();
462
463 if affected == 0 {
464 continue;
465 }
466 result.contacts_restored += 1;
467
468 // Sub-collections, verbatim with their preserved ids.
469 for email in &contact.emails {
470 sqlx::query(
471 "INSERT OR IGNORE INTO contact_emails (id, contact_id, address, label, is_primary) \
472 VALUES (?, ?, ?, ?, ?)",
473 )
474 .bind(email.id.to_string())
475 .bind(contact.id.to_string())
476 .bind(&email.address)
477 .bind(&email.label)
478 .bind(if email.is_primary { 1 } else { 0 })
479 .execute(&mut **tx)
480 .await
481 .map_err(CoreError::database)?;
482 }
483 for phone in &contact.phones {
484 sqlx::query(
485 "INSERT OR IGNORE INTO contact_phones (id, contact_id, number, label, is_primary) \
486 VALUES (?, ?, ?, ?, ?)",
487 )
488 .bind(phone.id.to_string())
489 .bind(contact.id.to_string())
490 .bind(&phone.number)
491 .bind(&phone.label)
492 .bind(if phone.is_primary { 1 } else { 0 })
493 .execute(&mut **tx)
494 .await
495 .map_err(CoreError::database)?;
496 }
497 for handle in &contact.social_handles {
498 sqlx::query(
499 "INSERT OR IGNORE INTO contact_social_handles (id, contact_id, platform, handle, url) \
500 VALUES (?, ?, ?, ?, ?)",
501 )
502 .bind(handle.id.to_string())
503 .bind(contact.id.to_string())
504 .bind(&handle.platform)
505 .bind(&handle.handle)
506 .bind(&handle.url)
507 .execute(&mut **tx)
508 .await
509 .map_err(CoreError::database)?;
510 }
511 for field in &contact.custom_fields {
512 sqlx::query(
513 "INSERT OR IGNORE INTO contact_custom_fields (id, contact_id, label, value, url) \
514 VALUES (?, ?, ?, ?, ?)",
515 )
516 .bind(field.id.to_string())
517 .bind(contact.id.to_string())
518 .bind(&field.label)
519 .bind(&field.value)
520 .bind(&field.url)
521 .execute(&mut **tx)
522 .await
523 .map_err(CoreError::database)?;
524 }
525 }
526 Ok(())
527 }
528
529 async fn restore_milestones(
530 tx: &mut Transaction<'_, Sqlite>,
531 user_id: UserId,
532 input: &RestoreInput,
533 result: &mut RestoreResult,
534 ) -> Result<()> {
535 for milestone in &input.milestones {
536 let target_date = milestone.target_date.map(|d| d.format("%Y-%m-%d").to_string());
537 let affected = sqlx::query(
538 "INSERT OR IGNORE INTO milestones (\
539 id, user_id, project_id, name, description, position, target_date, status, created_at\
540 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
541 )
542 .bind(milestone.id.to_string())
543 .bind(user_id.to_string())
544 .bind(milestone.project_id.to_string())
545 .bind(&milestone.name)
546 .bind(&milestone.description)
547 .bind(milestone.position)
548 .bind(&target_date)
549 .bind(milestone.status.db_value())
550 .bind(format_datetime(&milestone.created_at))
551 .execute(&mut **tx)
552 .await
553 .map_err(CoreError::database)?
554 .rows_affected();
555 if affected > 0 {
556 result.milestones_restored += 1;
557 }
558 }
559 Ok(())
560 }
561
562 async fn restore_time_sessions(
563 tx: &mut Transaction<'_, Sqlite>,
564 user_id: UserId,
565 input: &RestoreInput,
566 result: &mut RestoreResult,
567 ) -> Result<()> {
568 for session in &input.time_sessions {
569 let affected = sqlx::query(
570 "INSERT OR IGNORE INTO time_sessions (\
571 id, task_id, user_id, started_at, ended_at, duration_minutes, created_at\
572 ) VALUES (?, ?, ?, ?, ?, ?, ?)",
573 )
574 .bind(session.id.to_string())
575 .bind(session.task_id.to_string())
576 .bind(user_id.to_string())
577 .bind(format_datetime(&session.started_at))
578 .bind(format_datetime_opt(session.ended_at))
579 .bind(session.duration_minutes)
580 .bind(format_datetime(&session.created_at))
581 .execute(&mut **tx)
582 .await
583 .map_err(CoreError::database)?
584 .rows_affected();
585 if affected > 0 {
586 result.time_sessions_restored += 1;
587 }
588 }
589 Ok(())
590 }
591
592 async fn restore_attachments(
593 tx: &mut Transaction<'_, Sqlite>,
594 user_id: UserId,
595 input: &RestoreInput,
596 result: &mut RestoreResult,
597 ) -> Result<()> {
598 // Row metadata only; the content-addressed blob files are a separate store and
599 // are re-fetched by blob sync, not embedded in the backup.
600 for attachment in &input.attachments {
601 let affected = sqlx::query(
602 "INSERT OR IGNORE INTO attachments (\
603 id, user_id, task_id, project_id, filename, file_size, mime_type, blob_hash, \
604 source_email_id, created_at\
605 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
606 )
607 .bind(attachment.id.to_string())
608 .bind(user_id.to_string())
609 .bind(attachment.task_id.map(|t| t.to_string()))
610 .bind(attachment.project_id.map(|p| p.to_string()))
611 .bind(&attachment.filename)
612 .bind(attachment.file_size)
613 .bind(&attachment.mime_type)
614 .bind(&attachment.blob_hash)
615 .bind(attachment.source_email_id.map(|e| e.to_string()))
616 .bind(format_datetime(&attachment.created_at))
617 .execute(&mut **tx)
618 .await
619 .map_err(CoreError::database)?
620 .rows_affected();
621 if affected > 0 {
622 result.attachments_restored += 1;
623 }
624 }
625 Ok(())
626 }
627
628 async fn restore_daily_notes(
629 tx: &mut Transaction<'_, Sqlite>,
630 user_id: UserId,
631 input: &RestoreInput,
632 result: &mut RestoreResult,
633 ) -> Result<()> {
634 for note in &input.daily_notes {
635 let affected = sqlx::query(
636 "INSERT OR IGNORE INTO daily_notes (\
637 id, user_id, note_date, went_well, could_improve, is_reviewed, reviewed_at, \
638 created_at, updated_at\
639 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
640 )
641 .bind(note.id.to_string())
642 .bind(user_id.to_string())
643 .bind(note.note_date.format("%Y-%m-%d").to_string())
644 .bind(&note.went_well)
645 .bind(&note.could_improve)
646 .bind(if note.is_reviewed { 1 } else { 0 })
647 .bind(format_datetime_opt(note.reviewed_at))
648 .bind(format_datetime(&note.created_at))
649 .bind(format_datetime(&note.updated_at))
650 .execute(&mut **tx)
651 .await
652 .map_err(CoreError::database)?
653 .rows_affected();
654 if affected > 0 {
655 result.daily_notes_restored += 1;
656 }
657 }
658 Ok(())
659 }
660
661 async fn restore_sync_accounts(
662 tx: &mut Transaction<'_, Sqlite>,
663 user_id: UserId,
664 input: &RestoreInput,
665 result: &mut RestoreResult,
666 ) -> Result<()> {
667 // The sync-transient last_*_sync timestamps are intentionally omitted (they are
668 // re-derived on the next sync), matching the synced-column set.
669 for account in &input.sync_accounts {
670 let calendar_ids = serde_json::to_string(&account.calendar_ids).unwrap_or_else(|_| "[]".to_string());
671 let affected = sqlx::query(
672 "INSERT OR IGNORE INTO sync_accounts (\
673 id, user_id, provider, account_name, email, sync_calendars, sync_contacts, \
674 calendar_ids, sync_interval_minutes, enabled, created_at\
675 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
676 )
677 .bind(account.id.to_string())
678 .bind(user_id.to_string())
679 .bind(&account.provider)
680 .bind(&account.account_name)
681 .bind(&account.email)
682 .bind(if account.sync_calendars { 1 } else { 0 })
683 .bind(if account.sync_contacts { 1 } else { 0 })
684 .bind(&calendar_ids)
685 .bind(account.sync_interval_minutes)
686 .bind(if account.enabled { 1 } else { 0 })
687 .bind(format_datetime(&account.created_at))
688 .execute(&mut **tx)
689 .await
690 .map_err(CoreError::database)?
691 .rows_affected();
692 if affected > 0 {
693 result.sync_accounts_restored += 1;
694 }
695 }
696 Ok(())
697 }
698
699 async fn restore_saved_views(
700 tx: &mut Transaction<'_, Sqlite>,
701 user_id: UserId,
702 input: &RestoreInput,
703 result: &mut RestoreResult,
704 ) -> Result<()> {
705 for view in &input.saved_views {
706 let filters_json = serde_json::to_string(&view.filters).unwrap_or_else(|_| "{}".to_string());
707 let sort_by = view.sort_by.and_then(|sf| {
708 serde_json::to_value(sf)
709 .ok()
710 .and_then(|v| v.as_str().map(String::from))
711 });
712 let sort_order = match view.sort_order {
713 SortDirection::Desc => "desc",
714 SortDirection::Asc => "asc",
715 };
716 let affected = sqlx::query(
717 "INSERT OR IGNORE INTO saved_views (\
718 id, user_id, name, view_type, filters, sort_by, sort_order, is_pinned, position, \
719 created_at, updated_at\
720 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
721 )
722 .bind(view.id.to_string())
723 .bind(user_id.to_string())
724 .bind(&view.name)
725 .bind(view.view_type.db_value())
726 .bind(&filters_json)
727 .bind(&sort_by)
728 .bind(sort_order)
729 .bind(if view.is_pinned { 1 } else { 0 })
730 .bind(view.position)
731 .bind(format_datetime(&view.created_at))
732 .bind(format_datetime(&view.updated_at))
733 .execute(&mut **tx)
734 .await
735 .map_err(CoreError::database)?
736 .rows_affected();
737 if affected > 0 {
738 result.saved_views_restored += 1;
739 }
740 }
741 Ok(())
742 }
743
744 async fn restore_weekly_reviews(
745 tx: &mut Transaction<'_, Sqlite>,
746 user_id: UserId,
747 input: &RestoreInput,
748 result: &mut RestoreResult,
749 ) -> Result<()> {
750 for review in &input.weekly_reviews {
751 // Restore vacation_days verbatim. Earlier code silently dropped any value > 6,
752 // which violates this module's "every row inserted exactly as backed up" contract
753 // (ultra-fuzz Run #28 MINOR): a backup is a faithful snapshot, not a place to
754 // re-validate. The 0-6 weekday domain is enforced on the write path, not here.
755 let vacation_days = review
756 .vacation_days
757 .iter()
758 .map(|d| d.to_string())
759 .collect::<Vec<_>>()
760 .join(",");
761 let affected = sqlx::query(
762 "INSERT OR IGNORE INTO weekly_reviews (\
763 id, user_id, week_start_date, completed_at, notes, vacation_days\
764 ) VALUES (?, ?, ?, ?, ?, ?)",
765 )
766 .bind(review.id.to_string())
767 .bind(user_id.to_string())
768 .bind(review.week_start_date.format("%Y-%m-%d").to_string())
769 .bind(format_datetime(&review.completed_at))
770 .bind(&review.notes)
771 .bind(&vacation_days)
772 .execute(&mut **tx)
773 .await
774 .map_err(CoreError::database)?
775 .rows_affected();
776 if affected > 0 {
777 result.weekly_reviews_restored += 1;
778 }
779 }
780 Ok(())
781 }
782
783 async fn restore_monthly_goals(
784 tx: &mut Transaction<'_, Sqlite>,
785 user_id: UserId,
786 input: &RestoreInput,
787 result: &mut RestoreResult,
788 ) -> Result<()> {
789 for goal in &input.monthly_goals {
790 let affected = sqlx::query(
791 "INSERT OR IGNORE INTO monthly_goals (\
792 id, user_id, month, text, status, position, created_at, updated_at\
793 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
794 )
795 .bind(goal.id.to_string())
796 .bind(user_id.to_string())
797 .bind(&goal.month)
798 .bind(&goal.text)
799 .bind(goal.status.as_str())
800 .bind(goal.position)
801 .bind(format_datetime(&goal.created_at))
802 .bind(format_datetime(&goal.updated_at))
803 .execute(&mut **tx)
804 .await
805 .map_err(CoreError::database)?
806 .rows_affected();
807 if affected > 0 {
808 result.monthly_goals_restored += 1;
809 }
810 }
811 Ok(())
812 }
813
814 async fn restore_monthly_reflections(
815 tx: &mut Transaction<'_, Sqlite>,
816 user_id: UserId,
817 input: &RestoreInput,
818 result: &mut RestoreResult,
819 ) -> Result<()> {
820 for reflection in &input.monthly_reflections {
821 let affected = sqlx::query(
822 "INSERT OR IGNORE INTO monthly_reflections (\
823 id, user_id, month, highlight_text, change_text, completed_at\
824 ) VALUES (?, ?, ?, ?, ?, ?)",
825 )
826 .bind(reflection.id.to_string())
827 .bind(user_id.to_string())
828 .bind(&reflection.month)
829 .bind(&reflection.highlight_text)
830 .bind(&reflection.change_text)
831 .bind(format_datetime(&reflection.completed_at))
832 .execute(&mut **tx)
833 .await
834 .map_err(CoreError::database)?
835 .rows_affected();
836 if affected > 0 {
837 result.monthly_reflections_restored += 1;
838 }
839 }
840 Ok(())
841 }
842