Skip to main content

max / goingson

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