Skip to main content

max / goingson

21.7 KB · 419 lines History Blame Raw
1 //! Backup round-trip fidelity: export then restore must lose nothing.
2 //!
3 //! `backup_roundtrip_preserves_every_column` is the structural guard for the
4 //! "restore silently omitted column X" finding class. The sibling table-level
5 //! guard (`schema_tables_are_all_classified` in `backup_completeness_tests.rs`)
6 //! fixed the *table* case and has held since, but it was then treated as closing
7 //! the whole class, and it does not look at columns. Migrations 059 (`group_id`),
8 //! 062 (`tasks.title`) and 063 (the event timezone model) each shipped without
9 //! touching `restore.rs` and nothing failed, producing four live data losses
10 //! through the one path a user reaches when they have already lost data.
11 //!
12 //! The guard here is deliberately not a second registry. A `RESTORE_COLUMNS`
13 //! list naming what each INSERT writes would be a second copy of the truth, free
14 //! to drift from the statements it claims to describe. Instead this seeds one
15 //! fully-populated row per table in `BACKUP_TABLES` (every column set to a
16 //! distinctive non-default value), runs the real export path, restores into a
17 //! fresh database, and compares every column of every row. A migration that adds
18 //! a column to a backed-up table fails here until `restore_all` carries it,
19 //! because the seeded value has nowhere to survive. That also catches the
20 //! neighbouring classes a registry cannot see: datetime formatting, JSON
21 //! encoding, and type coercion drift.
22
23 mod common;
24
25 use goingson_core::ContextRepository as _;
26 use goingson_core::backup_restore::RestoreInput;
27 use goingson_core::{
28 AttachmentRepository, ContactRepository, DailyNoteRepository, EmailRepository, EventRepository,
29 MilestoneRepository, MonthlyReviewRepository, ProblemFilter, ProblemRepository,
30 ProjectRepository, SavedViewRepository, SyncAccountRepository, TaskCrud, TaskDependencies,
31 TaskTimeTracking, UserId, WeeklyReviewRepository,
32 };
33 use goingson_db_sqlite::Db;
34 use goingson_db_sqlite::{
35 BACKUP_TABLES, EXCLUDED_BACKUP_COLUMNS, SqliteAttachmentRepository, SqliteContactRepository,
36 SqliteContextRepository, SqliteDailyNoteRepository, SqliteEmailRepository,
37 SqliteEventRepository, SqliteMilestoneRepository, SqliteMonthlyReviewRepository,
38 SqliteProblemRepository, SqliteProjectRepository, SqliteSavedViewRepository,
39 SqliteSyncAccountRepository, SqliteTaskRepository, SqliteWeeklyReviewRepository, restore_all,
40 };
41 use rusqlite::params;
42
43 /// Fixed ids so the seed can wire real foreign keys without a lookup pass.
44 const PROJECT_ID: &str = "11111111-1111-4111-8111-111111111111";
45 const TASK_ID: &str = "22222222-2222-4222-8222-222222222222";
46 const EVENT_ID: &str = "33333333-3333-4333-8333-333333333333";
47 const EMAIL_ID: &str = "44444444-4444-4444-8444-444444444444";
48 const CONTACT_ID: &str = "55555555-5555-4555-8555-555555555555";
49 const MILESTONE_ID: &str = "66666666-6666-4666-8666-666666666666";
50 const GROUP_ID: &str = "77777777-7777-4777-8777-777777777777";
51 /// The second endpoint of the seeded dependency edge. `task_dependencies` has a
52 /// CHECK forbidding a self edge, so this table is the one that cannot be seeded
53 /// from the single `TASK_ID` every other task child hangs off.
54 const BLOCKER_TASK_ID: &str = "22222222-2222-4222-8222-222222222223";
55
56 /// One row per backed-up table, every column carrying a distinctive non-default
57 /// value. Raw SQL rather than the repository builders on purpose: the builders
58 /// only reach the columns they happen to expose, and a column no API sets is
59 /// exactly the kind this guard exists to catch.
60 fn seed_every_column(db: &Db, user_id: UserId) {
61 let u = user_id.to_string();
62
63 // Parents first — the seed runs with foreign keys on.
64 db.conn().unwrap().execute(// status_changed_at is deliberately later than created_at: it is the
65 // shelving instant a dormant project's problems anchor to, so a restore
66 // that collapsed it back onto created_at would silently rescore them.
67 "INSERT INTO projects (id, name, description, project_type, status, created_at, status_changed_at, user_id, group_id)
68 VALUES (?, 'Seeded project', 'project description', 'SideProject', 'OnHold', '2026-01-02 03:04:05', '2026-03-04 05:06:07', ?, ?)", params![PROJECT_ID, &u, GROUP_ID]).expect("seed projects");
69
70 db.conn().unwrap().execute("INSERT INTO contacts (id, user_id, display_name, nickname, company, title, notes, tags,
71 birthday, timezone, created_at, updated_at, external_source, external_id, is_implicit)
72 VALUES (?, ?, 'Seeded Contact', 'Seed', 'Acme', 'Principal', 'contact notes', '[\"vip\"]',
73 '1990-04-05', 'Europe/Berlin', '2026-01-02 03:04:05', '2026-01-03 03:04:05',
74 'carddav', 'ext-contact-1', 1)", params![CONTACT_ID, &u])
75 .expect("seed contacts");
76
77 db.conn().unwrap().execute("INSERT INTO milestones (id, user_id, project_id, name, description, position, target_date,
78 status, created_at, group_id)
79 VALUES (?, ?, ?, 'Seeded milestone', 'milestone description', 7, '2026-06-01',
80 'open', '2026-01-02 03:04:05', ?)", params![MILESTONE_ID, &u, PROJECT_ID, GROUP_ID])
81 .expect("seed milestones");
82
83 db.conn()
84 .unwrap()
85 .execute(
86 "INSERT INTO emails (id, project_id, from_address, to_address, subject, body, is_read,
87 received_at, user_id, message_id, email_account_id, is_outgoing, is_archived, imap_uid,
88 source_folder, snoozed_until, waiting_for_response, waiting_since,
89 expected_response_date, in_reply_to, thread_id, html_body, attachment_meta, is_draft,
90 cc_address, bcc_address, draft_account_id, labels, jmap_id, body_truncated)
91 VALUES (?, ?, 'from@example.com', 'to@example.com', 'Seeded subject', 'plain body', 1,
92 '2026-01-02 03:04:05', ?, '<msg-1@example.com>', NULL, 1, 1, 4242,
93 'INBOX/Seeded', '2026-02-01 00:00:00', 1, '2026-01-04 00:00:00',
94 '2026-01-09 00:00:00', '<parent@example.com>', 'thread-1', '<p>html body</p>',
95 '[{\"filename\":\"a.pdf\",\"blob_hash\":\"abc123\"}]', 0,
96 'cc@example.com', 'bcc@example.com', NULL, '[\"important\"]', 'jmap-1', 1)",
97 params![EMAIL_ID, PROJECT_ID, &u],
98 )
99 .expect("seed emails");
100
101 db.conn().unwrap().execute("INSERT INTO tasks (id, project_id, description, status, priority, due, tags, urgency,
102 recurrence, created_at, user_id, recurrence_parent_id, source_email_id, snoozed_until,
103 waiting_for_response, waiting_since, expected_response_date, scheduled_start,
104 scheduled_duration, is_focus, focus_set_at, contact_id, milestone_id, completed_at,
105 estimated_minutes, actual_minutes, recurrence_rule, group_id, title)
106 VALUES (?, ?, 'task description', 'Pending', 'High', '2026-05-05 12:00:00', '[\"seed\"]',
107 3.5, 'Monthly', '2026-01-02 03:04:05', ?, NULL, ?, '2026-03-01 00:00:00',
108 1, '2026-01-05 00:00:00', '2026-01-10 00:00:00', '2026-04-01 09:00:00',
109 90, 1, '2026-01-06 00:00:00', ?, ?, NULL,
110 120, 45, '{\"pattern\":\"Monthly\",\"interval\":2,\"weekdays\":[],\"monthlySpec\":null}', ?, 'Seeded task title')", params![TASK_ID, PROJECT_ID, &u, EMAIL_ID, CONTACT_ID, MILESTONE_ID, GROUP_ID]).expect("seed tasks");
111
112 db.conn()
113 .unwrap()
114 .execute(
115 "INSERT INTO tasks (id, description, title, status, priority, tags, urgency, recurrence,
116 created_at, user_id, actual_minutes)
117 VALUES (?, 'blocker description', 'Seeded blocker task', 'Pending', 'Medium', '[]',
118 1.0, 'None', '2026-01-02 03:04:05', ?, 0)",
119 params![BLOCKER_TASK_ID, &u],
120 )
121 .expect("seed blocker task");
122
123 db.conn()
124 .unwrap()
125 .execute(
126 "INSERT INTO task_dependencies (id, blocked_id, blocker_id, created_at, group_id)
127 VALUES ('88888888-8888-4888-8888-888888888884', ?, ?, '2026-01-02 03:04:05', ?)",
128 params![TASK_ID, BLOCKER_TASK_ID, GROUP_ID],
129 )
130 .expect("seed task_dependencies");
131
132 db.conn().unwrap().execute("INSERT INTO events (id, project_id, title, description, start_time, end_time, location,
133 user_id, linked_task_id, recurrence, recurrence_parent_id, contact_id, block_type,
134 external_source, external_id, is_read_only, recurrence_rule, snoozed_until,
135 reminder_offsets_seconds, group_id, tz_kind, timezone, start_local, end_local)
136 VALUES (?, ?, 'Seeded event', 'event description', '2026-07-01 06:00:00',
137 '2026-07-01 07:00:00', 'Studio', ?, ?, 'Weekly', NULL, ?, 'focus',
138 'caldav', 'ext-event-1', 1, '{\"pattern\":\"Weekly\",\"interval\":3,\"weekdays\":[0,2],\"monthlySpec\":null}',
139 '2026-06-01 00:00:00', '[600,1800]', ?, 'relative', 'Europe/Berlin',
140 '2026-07-01 08:00:00', '2026-07-01 09:00:00')", params![EVENT_ID, PROJECT_ID, &u, TASK_ID, CONTACT_ID, GROUP_ID]).expect("seed events");
141
142 // Task children.
143 db.conn().unwrap().execute("INSERT INTO subtasks (id, task_id, text, is_completed, position, created_at, linked_task_id, group_id)
144 VALUES ('88888888-8888-4888-8888-888888888881', ?, 'seeded subtask', 1, 3,
145 '2026-01-02 03:04:05', NULL, ?)", params![TASK_ID, GROUP_ID]).expect("seed subtasks");
146
147 db.conn().unwrap().execute("INSERT INTO annotations (id, task_id, timestamp, note, group_id)
148 VALUES ('88888888-8888-4888-8888-888888888882', ?, '2026-01-02 03:04:05', 'seeded note', ?)", params![TASK_ID, GROUP_ID]).expect("seed annotations");
149
150 db.conn().unwrap().execute("INSERT INTO task_status_tokens (id, task_id, kind, reference, state, is_primary, position, created_at, group_id)
151 VALUES ('88888888-8888-4888-8888-888888888883', ?, 'commit', 'go@abc1234', 'Pending', 1, 2,
152 '2026-01-02 03:04:05', ?)", params![TASK_ID, GROUP_ID]).expect("seed task_status_tokens");
153
154 db.conn().unwrap().execute("INSERT INTO time_sessions (id, task_id, user_id, started_at, ended_at, duration_minutes, created_at, group_id)
155 VALUES ('88888888-8888-4888-8888-888888888884', ?, ?, '2026-01-02 03:04:05',
156 '2026-01-02 04:04:05', 60, '2026-01-02 03:04:05', ?)", params![TASK_ID, &u, GROUP_ID]).expect("seed time_sessions");
157
158 db.conn().unwrap().execute("INSERT INTO attachments (id, user_id, task_id, project_id, filename, file_size, mime_type,
159 blob_hash, source_email_id, created_at, group_id)
160 VALUES ('88888888-8888-4888-8888-888888888885', ?, ?, ?, 'seeded.pdf', 12345,
161 'application/pdf', 'a1b2c3d4e5f60718293a4b5c6d7e8f90a1b2c3d4e5f60718293a4b5c6d7e8f90',
162 ?, '2026-01-02 03:04:05', ?)", params![&u, TASK_ID, PROJECT_ID, EMAIL_ID, GROUP_ID])
163 .expect("seed attachments");
164
165 // Contact children.
166 db.conn()
167 .unwrap()
168 .execute(
169 "INSERT INTO contact_emails (id, contact_id, address, label, is_primary)
170 VALUES ('99999999-9999-4999-8999-999999999991', ?, 'primary@example.com', 'work', 1)",
171 params![CONTACT_ID],
172 )
173 .expect("seed contact_emails");
174
175 db.conn()
176 .unwrap()
177 .execute(
178 "INSERT INTO contact_phones (id, contact_id, number, label, is_primary)
179 VALUES ('99999999-9999-4999-8999-999999999992', ?, '+15550001111', 'mobile', 1)",
180 params![CONTACT_ID],
181 )
182 .expect("seed contact_phones");
183
184 db.conn().unwrap().execute("INSERT INTO contact_social_handles (id, contact_id, platform, handle, url)
185 VALUES ('99999999-9999-4999-8999-999999999993', ?, 'mastodon', '@seed', 'https://example.com/@seed')", params![CONTACT_ID]).expect("seed contact_social_handles");
186
187 db.conn().unwrap().execute("INSERT INTO contact_custom_fields (id, contact_id, label, value, url)
188 VALUES ('99999999-9999-4999-8999-999999999994', ?, 'Website', 'example.com', 'https://example.com')", params![CONTACT_ID]).expect("seed contact_custom_fields");
189
190 // Standalone user-scoped tables.
191 db.conn()
192 .unwrap()
193 .execute(
194 "INSERT INTO contexts (id, user_id, label, kind, starts_on, ends_on,
195 created_at, updated_at)
196 VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa5', ?, 'Seeded leave', 'Vacation',
197 '2026-01-05', '2026-01-09', '2026-01-02 03:04:05', '2026-01-02 03:04:05')",
198 params![&u],
199 )
200 .expect("seed contexts");
201
202 db.conn().unwrap().execute("INSERT INTO daily_notes (id, user_id, note_date, went_well, could_improve, is_reviewed,
203 reviewed_at, created_at, updated_at)
204 VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa1', ?, '2026-01-02', 'went well text',
205 'could improve text', 1, '2026-01-03 00:00:00', '2026-01-02 03:04:05', '2026-01-03 03:04:05')", params![&u]).expect("seed daily_notes");
206
207 db.conn()
208 .unwrap()
209 .execute(
210 "INSERT INTO saved_views (id, user_id, name, view_type, filters, sort_by, sort_order,
211 is_pinned, position, created_at, updated_at)
212 VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa2', ?, 'Seeded view', 'tasks',
213 '{\"status\":[\"Pending\"]}', 'due', 'asc', 1, 4,
214 '2026-01-02 03:04:05', '2026-01-03 03:04:05')",
215 params![&u],
216 )
217 .expect("seed saved_views");
218
219 db.conn().unwrap().execute("INSERT INTO weekly_reviews (id, user_id, week_start_date, completed_at, notes, vacation_days)
220 VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa3', ?, '2026-01-05', '2026-01-11 18:00:00',
221 'weekly notes', 2)", params![&u]).expect("seed weekly_reviews");
222
223 db.conn().unwrap().execute("INSERT INTO monthly_goals (id, user_id, month, text, status, position, created_at, updated_at)
224 VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa4', ?, '2026-01', 'goal text', 'active', 5,
225 '2026-01-02 03:04:05', '2026-01-03 03:04:05')", params![&u]).expect("seed monthly_goals");
226
227 db.conn().unwrap().execute("INSERT INTO monthly_reflections (id, user_id, month, highlight_text, change_text, completed_at)
228 VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa5', ?, '2026-01', 'highlight text',
229 'change text', '2026-02-01 00:00:00')", params![&u]).expect("seed monthly_reflections");
230
231 db.conn().unwrap().execute("INSERT INTO problems (id, user_id, source, source_ref, title, body, pain, scale, status,
232 project_id, tags, promoted_task_id, created_at, updated_at, settled_at, last_seen_at)
233 VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa6', ?, 'audit', 'go:seed:ref', 'Seeded problem',
234 'problem body', 4, 3, 'Open', ?, '[\"seed\"]', NULL,
235 '2026-01-02 03:04:05', '2026-01-03 03:04:05', NULL, '2026-01-04 03:04:05')", params![&u, PROJECT_ID]).expect("seed problems");
236
237 db.conn()
238 .unwrap()
239 .execute(
240 "INSERT INTO sync_accounts (id, user_id, provider, account_name, email, sync_calendars,
241 sync_contacts, calendar_ids, last_calendar_sync, last_contact_sync,
242 sync_interval_minutes, enabled, created_at)
243 VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa7', ?, 'google', 'Seeded account',
244 'sync@example.com', 1, 1, '[\"cal-1\"]', '2026-01-03 00:00:00', '2026-01-04 00:00:00',
245 30, 1, '2026-01-02 03:04:05')",
246 params![&u],
247 )
248 .expect("seed sync_accounts");
249 }
250
251 /// The production export path, assembled exactly as `collect_full_export` does in
252 /// `src-tauri/src/backup_scheduler.rs`. Kept in the same shape so an export-side
253 /// filter (the kind that hid draft emails) shows up here as a missing row.
254 fn export_all(db: &Db, user_id: UserId) -> RestoreInput {
255 let tasks_repo = SqliteTaskRepository::new(db.clone());
256 let monthly = SqliteMonthlyReviewRepository::new(db.clone());
257 RestoreInput {
258 projects: SqliteProjectRepository::new(db.clone())
259 .list_all(user_id)
260 .unwrap(),
261 tasks: tasks_repo.list_all_for_backup(user_id).unwrap(),
262 events: SqliteEventRepository::new(db.clone())
263 .list_all(user_id)
264 .unwrap(),
265 emails: SqliteEmailRepository::new(db.clone())
266 .list_all_for_backup(user_id)
267 .unwrap(),
268 contacts: SqliteContactRepository::new(db.clone())
269 .list_all_for_backup(user_id)
270 .unwrap(),
271 time_sessions: tasks_repo.list_all_time_sessions(user_id).unwrap(),
272 milestones: SqliteMilestoneRepository::new(db.clone())
273 .list_all(user_id)
274 .unwrap(),
275 contexts: SqliteContextRepository::new(db.clone())
276 .list_all(user_id)
277 .unwrap(),
278 daily_notes: SqliteDailyNoteRepository::new(db.clone())
279 .list_all(user_id)
280 .unwrap(),
281 attachments: SqliteAttachmentRepository::new(db.clone())
282 .list_all(user_id)
283 .unwrap(),
284 sync_accounts: SqliteSyncAccountRepository::new(db.clone())
285 .list_all(user_id)
286 .unwrap(),
287 saved_views: SqliteSavedViewRepository::new(db.clone())
288 .list_all(user_id)
289 .unwrap(),
290 weekly_reviews: SqliteWeeklyReviewRepository::new(db.clone())
291 .list_all(user_id)
292 .unwrap(),
293 monthly_goals: monthly.list_all_goals(user_id).unwrap(),
294 monthly_reflections: monthly.list_all_reflections(user_id).unwrap(),
295 problems: SqliteProblemRepository::new(db.clone())
296 .list(user_id, &ProblemFilter::default())
297 .unwrap(),
298 dependencies: tasks_repo.list_all_dependencies(user_id).unwrap(),
299 }
300 }
301
302 /// Every column of `table`, read as TEXT so one comparator covers every affinity.
303 fn columns_of(db: &Db, table: &str) -> Vec<String> {
304 let conn = db.conn().unwrap();
305 let mut stmt = conn
306 .prepare(&format!("SELECT name FROM pragma_table_info('{table}')"))
307 .expect("pragma table_info");
308 let names = stmt
309 .query_map([], |row| row.get::<_, String>("name"))
310 .expect("pragma table_info");
311 names.map(Result::unwrap).collect()
312 }
313
314 /// All rows of `table` as (id, column, value) triples, ordered by id.
315 fn rows_of(db: &Db, table: &str, cols: &[String]) -> Vec<(String, Vec<(String, Option<String>)>)> {
316 let select = cols
317 .iter()
318 .map(|c| format!("CAST(\"{c}\" AS TEXT) AS \"{c}\""))
319 .collect::<Vec<_>>()
320 .join(", ");
321 let conn = db.conn().unwrap();
322 let mut stmt = conn
323 .prepare(&format!("SELECT {select} FROM \"{table}\" ORDER BY id"))
324 .unwrap_or_else(|e| panic!("select from {table}: {e}"));
325 let rows = stmt
326 .query_map([], |r| {
327 let id: Option<String> = r.get("id")?;
328 let values = cols
329 .iter()
330 .map(|c| Ok((c.clone(), r.get::<_, Option<String>>(c.as_str())?)))
331 .collect::<rusqlite::Result<Vec<_>>>()?;
332 Ok((id.unwrap_or_default(), values))
333 })
334 .unwrap_or_else(|e| panic!("select from {table}: {e}"));
335 rows.map(Result::unwrap).collect()
336 }
337
338 #[test]
339 fn backup_roundtrip_preserves_every_column() {
340 let src = common::setup_test_db();
341 let user_id = common::create_test_user(&src);
342 seed_every_column(&src, user_id);
343
344 let input = export_all(&src, user_id);
345
346 // Restore into a fresh database carrying only the same user row, so every
347 // other value present afterwards arrived through the backup.
348 let dst = common::setup_test_db();
349 dst.conn()
350 .unwrap()
351 .execute(
352 "INSERT INTO users (id, email, password_hash, display_name, created_at)
353 VALUES (?, ?, 'x', 'Restored User', '2026-01-01 00:00:00')",
354 params![user_id.to_string(), format!("u-{user_id}@example.com")],
355 )
356 .expect("recreate user in destination");
357
358 restore_all(&mut dst.conn().unwrap(), user_id, &input).expect("restore must succeed");
359
360 let mut failures: Vec<String> = Vec::new();
361 for table in BACKUP_TABLES {
362 let cols = columns_of(&src, table);
363 let before = rows_of(&src, table, &cols);
364 let after = rows_of(&dst, table, &cols);
365
366 if before.len() != after.len() {
367 failures.push(format!(
368 "{table}: {} row(s) exported, {} restored — the export query drops rows the \
369 backup is supposed to carry",
370 before.len(),
371 after.len()
372 ));
373 continue;
374 }
375
376 for ((id, src_row), (_, dst_row)) in before.iter().zip(after.iter()) {
377 for ((col, src_val), (_, dst_val)) in src_row.iter().zip(dst_row.iter()) {
378 let excluded = EXCLUDED_BACKUP_COLUMNS
379 .iter()
380 .any(|(t, c, _)| t == table && c == col);
381 if !excluded && src_val != dst_val {
382 failures.push(format!(
383 "{table}.{col} (row {id}): {src_val:?} before restore, {dst_val:?} after"
384 ));
385 }
386 }
387 }
388 }
389
390 assert!(
391 failures.is_empty(),
392 "backup round-trip lost data. Each line is a column the export carries and \
393 `restore_all` discards, or a row the export never collected:\n {}",
394 failures.join("\n ")
395 );
396 }
397
398 /// A typo in the exclusion register would silently excuse a column that does not
399 /// exist while the real one goes unguarded, so pin the entries to the schema.
400 #[test]
401 fn excluded_backup_columns_are_real_columns() {
402 let db = common::setup_test_db();
403 for (table, column, reason) in EXCLUDED_BACKUP_COLUMNS {
404 assert!(
405 BACKUP_TABLES.contains(table),
406 "EXCLUDED_BACKUP_COLUMNS names table `{table}`, which is not in BACKUP_TABLES"
407 );
408 let cols = columns_of(&db, table);
409 assert!(
410 cols.iter().any(|c| c == column),
411 "EXCLUDED_BACKUP_COLUMNS excuses `{table}.{column}`, which is not a real column"
412 );
413 assert!(
414 !reason.trim().is_empty(),
415 "`{table}.{column}` is excluded from backups with no stated reason"
416 );
417 }
418 }
419