Skip to main content

max / goingson

17.5 KB · 535 lines History Blame Raw
1 -- Sync changelog: change tracking for cloud sync via SyncKit.
2 --
3 -- sync_state: key-value store for sync configuration/state.
4 -- sync_changelog: local change log captured by triggers, pushed to server.
5 -- Triggers: 3 per table (INSERT/UPDATE/DELETE) x 11 syncable tables = 33 triggers.
6
7 -- ============ Tables ============
8
9 CREATE TABLE IF NOT EXISTS sync_state (
10 key TEXT PRIMARY KEY NOT NULL,
11 value TEXT NOT NULL
12 );
13
14 INSERT OR IGNORE INTO sync_state (key, value) VALUES
15 ('device_id', ''),
16 ('pull_cursor', '0'),
17 ('auto_sync_enabled', '1'),
18 ('sync_interval_minutes', '5'),
19 ('applying_remote', '0'),
20 ('last_sync_at', ''),
21 ('initial_snapshot_done', '0');
22
23 CREATE TABLE IF NOT EXISTS sync_changelog (
24 id INTEGER PRIMARY KEY AUTOINCREMENT,
25 table_name TEXT NOT NULL,
26 op TEXT NOT NULL,
27 row_id TEXT NOT NULL,
28 timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
29 data TEXT,
30 pushed INTEGER NOT NULL DEFAULT 0
31 );
32
33 CREATE INDEX IF NOT EXISTS idx_sync_changelog_unpushed ON sync_changelog(pushed) WHERE pushed = 0;
34
35 -- ============ Triggers ============
36 -- Each trigger checks applying_remote flag to prevent echo during pull.
37
38 -- ── projects (7 cols) ──
39
40 CREATE TRIGGER IF NOT EXISTS sync_trg_projects_insert
41 AFTER INSERT ON projects
42 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
43 BEGIN
44 INSERT INTO sync_changelog (table_name, op, row_id, data)
45 VALUES ('projects', 'INSERT', NEW.id, json_object(
46 'id', NEW.id,
47 'name', NEW.name,
48 'description', NEW.description,
49 'project_type', NEW.project_type,
50 'status', NEW.status,
51 'created_at', NEW.created_at,
52 'user_id', NEW.user_id
53 ));
54 END;
55
56 CREATE TRIGGER IF NOT EXISTS sync_trg_projects_update
57 AFTER UPDATE ON projects
58 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
59 BEGIN
60 INSERT INTO sync_changelog (table_name, op, row_id, data)
61 VALUES ('projects', 'UPDATE', NEW.id, json_object(
62 'id', NEW.id,
63 'name', NEW.name,
64 'description', NEW.description,
65 'project_type', NEW.project_type,
66 'status', NEW.status,
67 'created_at', NEW.created_at,
68 'user_id', NEW.user_id
69 ));
70 END;
71
72 CREATE TRIGGER IF NOT EXISTS sync_trg_projects_delete
73 AFTER DELETE ON projects
74 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
75 BEGIN
76 INSERT INTO sync_changelog (table_name, op, row_id, data)
77 VALUES ('projects', 'DELETE', OLD.id, NULL);
78 END;
79
80 -- ── tasks (23 cols) ──
81
82 CREATE TRIGGER IF NOT EXISTS sync_trg_tasks_insert
83 AFTER INSERT ON tasks
84 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
85 BEGIN
86 INSERT INTO sync_changelog (table_name, op, row_id, data)
87 VALUES ('tasks', 'INSERT', NEW.id, json_object(
88 'id', NEW.id,
89 'project_id', NEW.project_id,
90 'description', NEW.description,
91 'status', NEW.status,
92 'priority', NEW.priority,
93 'due', NEW.due,
94 'tags', NEW.tags,
95 'urgency', NEW.urgency,
96 'recurrence', NEW.recurrence,
97 'created_at', NEW.created_at,
98 'user_id', NEW.user_id,
99 'recurrence_parent_id', NEW.recurrence_parent_id,
100 'source_email_id', NEW.source_email_id,
101 'snoozed_until', NEW.snoozed_until,
102 'waiting_for_response', NEW.waiting_for_response,
103 'waiting_since', NEW.waiting_since,
104 'expected_response_date', NEW.expected_response_date,
105 'scheduled_start', NEW.scheduled_start,
106 'scheduled_duration', NEW.scheduled_duration,
107 'is_focus', NEW.is_focus,
108 'focus_set_at', NEW.focus_set_at,
109 'contact_id', NEW.contact_id,
110 'milestone_id', NEW.milestone_id
111 ));
112 END;
113
114 CREATE TRIGGER IF NOT EXISTS sync_trg_tasks_update
115 AFTER UPDATE ON tasks
116 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
117 BEGIN
118 INSERT INTO sync_changelog (table_name, op, row_id, data)
119 VALUES ('tasks', 'UPDATE', NEW.id, json_object(
120 'id', NEW.id,
121 'project_id', NEW.project_id,
122 'description', NEW.description,
123 'status', NEW.status,
124 'priority', NEW.priority,
125 'due', NEW.due,
126 'tags', NEW.tags,
127 'urgency', NEW.urgency,
128 'recurrence', NEW.recurrence,
129 'created_at', NEW.created_at,
130 'user_id', NEW.user_id,
131 'recurrence_parent_id', NEW.recurrence_parent_id,
132 'source_email_id', NEW.source_email_id,
133 'snoozed_until', NEW.snoozed_until,
134 'waiting_for_response', NEW.waiting_for_response,
135 'waiting_since', NEW.waiting_since,
136 'expected_response_date', NEW.expected_response_date,
137 'scheduled_start', NEW.scheduled_start,
138 'scheduled_duration', NEW.scheduled_duration,
139 'is_focus', NEW.is_focus,
140 'focus_set_at', NEW.focus_set_at,
141 'contact_id', NEW.contact_id,
142 'milestone_id', NEW.milestone_id
143 ));
144 END;
145
146 CREATE TRIGGER IF NOT EXISTS sync_trg_tasks_delete
147 AFTER DELETE ON tasks
148 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
149 BEGIN
150 INSERT INTO sync_changelog (table_name, op, row_id, data)
151 VALUES ('tasks', 'DELETE', OLD.id, NULL);
152 END;
153
154 -- ── events (13 cols) ──
155
156 CREATE TRIGGER IF NOT EXISTS sync_trg_events_insert
157 AFTER INSERT ON events
158 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
159 BEGIN
160 INSERT INTO sync_changelog (table_name, op, row_id, data)
161 VALUES ('events', 'INSERT', NEW.id, json_object(
162 'id', NEW.id,
163 'project_id', NEW.project_id,
164 'title', NEW.title,
165 'description', NEW.description,
166 'start_time', NEW.start_time,
167 'end_time', NEW.end_time,
168 'location', NEW.location,
169 'user_id', NEW.user_id,
170 'linked_task_id', NEW.linked_task_id,
171 'recurrence', NEW.recurrence,
172 'recurrence_parent_id', NEW.recurrence_parent_id,
173 'contact_id', NEW.contact_id,
174 'block_type', NEW.block_type
175 ));
176 END;
177
178 CREATE TRIGGER IF NOT EXISTS sync_trg_events_update
179 AFTER UPDATE ON events
180 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
181 BEGIN
182 INSERT INTO sync_changelog (table_name, op, row_id, data)
183 VALUES ('events', 'UPDATE', NEW.id, json_object(
184 'id', NEW.id,
185 'project_id', NEW.project_id,
186 'title', NEW.title,
187 'description', NEW.description,
188 'start_time', NEW.start_time,
189 'end_time', NEW.end_time,
190 'location', NEW.location,
191 'user_id', NEW.user_id,
192 'linked_task_id', NEW.linked_task_id,
193 'recurrence', NEW.recurrence,
194 'recurrence_parent_id', NEW.recurrence_parent_id,
195 'contact_id', NEW.contact_id,
196 'block_type', NEW.block_type
197 ));
198 END;
199
200 CREATE TRIGGER IF NOT EXISTS sync_trg_events_delete
201 AFTER DELETE ON events
202 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
203 BEGIN
204 INSERT INTO sync_changelog (table_name, op, row_id, data)
205 VALUES ('events', 'DELETE', OLD.id, NULL);
206 END;
207
208 -- ── contacts (12 cols) ──
209
210 CREATE TRIGGER IF NOT EXISTS sync_trg_contacts_insert
211 AFTER INSERT ON contacts
212 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
213 BEGIN
214 INSERT INTO sync_changelog (table_name, op, row_id, data)
215 VALUES ('contacts', 'INSERT', NEW.id, json_object(
216 'id', NEW.id,
217 'user_id', NEW.user_id,
218 'display_name', NEW.display_name,
219 'nickname', NEW.nickname,
220 'company', NEW.company,
221 'title', NEW.title,
222 'notes', NEW.notes,
223 'tags', NEW.tags,
224 'birthday', NEW.birthday,
225 'timezone', NEW.timezone,
226 'created_at', NEW.created_at,
227 'updated_at', NEW.updated_at
228 ));
229 END;
230
231 CREATE TRIGGER IF NOT EXISTS sync_trg_contacts_update
232 AFTER UPDATE ON contacts
233 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
234 BEGIN
235 INSERT INTO sync_changelog (table_name, op, row_id, data)
236 VALUES ('contacts', 'UPDATE', NEW.id, json_object(
237 'id', NEW.id,
238 'user_id', NEW.user_id,
239 'display_name', NEW.display_name,
240 'nickname', NEW.nickname,
241 'company', NEW.company,
242 'title', NEW.title,
243 'notes', NEW.notes,
244 'tags', NEW.tags,
245 'birthday', NEW.birthday,
246 'timezone', NEW.timezone,
247 'created_at', NEW.created_at,
248 'updated_at', NEW.updated_at
249 ));
250 END;
251
252 CREATE TRIGGER IF NOT EXISTS sync_trg_contacts_delete
253 AFTER DELETE ON contacts
254 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
255 BEGIN
256 INSERT INTO sync_changelog (table_name, op, row_id, data)
257 VALUES ('contacts', 'DELETE', OLD.id, NULL);
258 END;
259
260 -- ── contact_emails (5 cols) ──
261
262 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_emails_insert
263 AFTER INSERT ON contact_emails
264 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
265 BEGIN
266 INSERT INTO sync_changelog (table_name, op, row_id, data)
267 VALUES ('contact_emails', 'INSERT', NEW.id, json_object(
268 'id', NEW.id,
269 'contact_id', NEW.contact_id,
270 'address', NEW.address,
271 'label', NEW.label,
272 'is_primary', NEW.is_primary
273 ));
274 END;
275
276 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_emails_update
277 AFTER UPDATE ON contact_emails
278 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
279 BEGIN
280 INSERT INTO sync_changelog (table_name, op, row_id, data)
281 VALUES ('contact_emails', 'UPDATE', NEW.id, json_object(
282 'id', NEW.id,
283 'contact_id', NEW.contact_id,
284 'address', NEW.address,
285 'label', NEW.label,
286 'is_primary', NEW.is_primary
287 ));
288 END;
289
290 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_emails_delete
291 AFTER DELETE ON contact_emails
292 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
293 BEGIN
294 INSERT INTO sync_changelog (table_name, op, row_id, data)
295 VALUES ('contact_emails', 'DELETE', OLD.id, NULL);
296 END;
297
298 -- ── contact_phones (5 cols) ──
299
300 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_phones_insert
301 AFTER INSERT ON contact_phones
302 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
303 BEGIN
304 INSERT INTO sync_changelog (table_name, op, row_id, data)
305 VALUES ('contact_phones', 'INSERT', NEW.id, json_object(
306 'id', NEW.id,
307 'contact_id', NEW.contact_id,
308 'number', NEW.number,
309 'label', NEW.label,
310 'is_primary', NEW.is_primary
311 ));
312 END;
313
314 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_phones_update
315 AFTER UPDATE ON contact_phones
316 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
317 BEGIN
318 INSERT INTO sync_changelog (table_name, op, row_id, data)
319 VALUES ('contact_phones', 'UPDATE', NEW.id, json_object(
320 'id', NEW.id,
321 'contact_id', NEW.contact_id,
322 'number', NEW.number,
323 'label', NEW.label,
324 'is_primary', NEW.is_primary
325 ));
326 END;
327
328 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_phones_delete
329 AFTER DELETE ON contact_phones
330 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
331 BEGIN
332 INSERT INTO sync_changelog (table_name, op, row_id, data)
333 VALUES ('contact_phones', 'DELETE', OLD.id, NULL);
334 END;
335
336 -- ── contact_social_handles (5 cols) ──
337
338 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_social_handles_insert
339 AFTER INSERT ON contact_social_handles
340 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
341 BEGIN
342 INSERT INTO sync_changelog (table_name, op, row_id, data)
343 VALUES ('contact_social_handles', 'INSERT', NEW.id, json_object(
344 'id', NEW.id,
345 'contact_id', NEW.contact_id,
346 'platform', NEW.platform,
347 'handle', NEW.handle,
348 'url', NEW.url
349 ));
350 END;
351
352 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_social_handles_update
353 AFTER UPDATE ON contact_social_handles
354 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
355 BEGIN
356 INSERT INTO sync_changelog (table_name, op, row_id, data)
357 VALUES ('contact_social_handles', 'UPDATE', NEW.id, json_object(
358 'id', NEW.id,
359 'contact_id', NEW.contact_id,
360 'platform', NEW.platform,
361 'handle', NEW.handle,
362 'url', NEW.url
363 ));
364 END;
365
366 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_social_handles_delete
367 AFTER DELETE ON contact_social_handles
368 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
369 BEGIN
370 INSERT INTO sync_changelog (table_name, op, row_id, data)
371 VALUES ('contact_social_handles', 'DELETE', OLD.id, NULL);
372 END;
373
374 -- ── contact_custom_fields (5 cols) ──
375
376 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_custom_fields_insert
377 AFTER INSERT ON contact_custom_fields
378 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
379 BEGIN
380 INSERT INTO sync_changelog (table_name, op, row_id, data)
381 VALUES ('contact_custom_fields', 'INSERT', NEW.id, json_object(
382 'id', NEW.id,
383 'contact_id', NEW.contact_id,
384 'label', NEW.label,
385 'value', NEW.value,
386 'url', NEW.url
387 ));
388 END;
389
390 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_custom_fields_update
391 AFTER UPDATE ON contact_custom_fields
392 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
393 BEGIN
394 INSERT INTO sync_changelog (table_name, op, row_id, data)
395 VALUES ('contact_custom_fields', 'UPDATE', NEW.id, json_object(
396 'id', NEW.id,
397 'contact_id', NEW.contact_id,
398 'label', NEW.label,
399 'value', NEW.value,
400 'url', NEW.url
401 ));
402 END;
403
404 CREATE TRIGGER IF NOT EXISTS sync_trg_contact_custom_fields_delete
405 AFTER DELETE ON contact_custom_fields
406 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
407 BEGIN
408 INSERT INTO sync_changelog (table_name, op, row_id, data)
409 VALUES ('contact_custom_fields', 'DELETE', OLD.id, NULL);
410 END;
411
412 -- ── annotations (4 cols) ──
413
414 CREATE TRIGGER IF NOT EXISTS sync_trg_annotations_insert
415 AFTER INSERT ON annotations
416 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
417 BEGIN
418 INSERT INTO sync_changelog (table_name, op, row_id, data)
419 VALUES ('annotations', 'INSERT', NEW.id, json_object(
420 'id', NEW.id,
421 'task_id', NEW.task_id,
422 'timestamp', NEW.timestamp,
423 'note', NEW.note
424 ));
425 END;
426
427 CREATE TRIGGER IF NOT EXISTS sync_trg_annotations_update
428 AFTER UPDATE ON annotations
429 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
430 BEGIN
431 INSERT INTO sync_changelog (table_name, op, row_id, data)
432 VALUES ('annotations', 'UPDATE', NEW.id, json_object(
433 'id', NEW.id,
434 'task_id', NEW.task_id,
435 'timestamp', NEW.timestamp,
436 'note', NEW.note
437 ));
438 END;
439
440 CREATE TRIGGER IF NOT EXISTS sync_trg_annotations_delete
441 AFTER DELETE ON annotations
442 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
443 BEGIN
444 INSERT INTO sync_changelog (table_name, op, row_id, data)
445 VALUES ('annotations', 'DELETE', OLD.id, NULL);
446 END;
447
448 -- ── subtasks (7 cols) ──
449
450 CREATE TRIGGER IF NOT EXISTS sync_trg_subtasks_insert
451 AFTER INSERT ON subtasks
452 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
453 BEGIN
454 INSERT INTO sync_changelog (table_name, op, row_id, data)
455 VALUES ('subtasks', 'INSERT', NEW.id, json_object(
456 'id', NEW.id,
457 'task_id', NEW.task_id,
458 'text', NEW.text,
459 'is_completed', NEW.is_completed,
460 'position', NEW.position,
461 'created_at', NEW.created_at,
462 'linked_task_id', NEW.linked_task_id
463 ));
464 END;
465
466 CREATE TRIGGER IF NOT EXISTS sync_trg_subtasks_update
467 AFTER UPDATE ON subtasks
468 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
469 BEGIN
470 INSERT INTO sync_changelog (table_name, op, row_id, data)
471 VALUES ('subtasks', 'UPDATE', NEW.id, json_object(
472 'id', NEW.id,
473 'task_id', NEW.task_id,
474 'text', NEW.text,
475 'is_completed', NEW.is_completed,
476 'position', NEW.position,
477 'created_at', NEW.created_at,
478 'linked_task_id', NEW.linked_task_id
479 ));
480 END;
481
482 CREATE TRIGGER IF NOT EXISTS sync_trg_subtasks_delete
483 AFTER DELETE ON subtasks
484 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
485 BEGIN
486 INSERT INTO sync_changelog (table_name, op, row_id, data)
487 VALUES ('subtasks', 'DELETE', OLD.id, NULL);
488 END;
489
490 -- ── milestones (9 cols) ──
491
492 CREATE TRIGGER IF NOT EXISTS sync_trg_milestones_insert
493 AFTER INSERT ON milestones
494 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
495 BEGIN
496 INSERT INTO sync_changelog (table_name, op, row_id, data)
497 VALUES ('milestones', 'INSERT', NEW.id, json_object(
498 'id', NEW.id,
499 'user_id', NEW.user_id,
500 'project_id', NEW.project_id,
501 'name', NEW.name,
502 'description', NEW.description,
503 'position', NEW.position,
504 'target_date', NEW.target_date,
505 'status', NEW.status,
506 'created_at', NEW.created_at
507 ));
508 END;
509
510 CREATE TRIGGER IF NOT EXISTS sync_trg_milestones_update
511 AFTER UPDATE ON milestones
512 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
513 BEGIN
514 INSERT INTO sync_changelog (table_name, op, row_id, data)
515 VALUES ('milestones', 'UPDATE', NEW.id, json_object(
516 'id', NEW.id,
517 'user_id', NEW.user_id,
518 'project_id', NEW.project_id,
519 'name', NEW.name,
520 'description', NEW.description,
521 'position', NEW.position,
522 'target_date', NEW.target_date,
523 'status', NEW.status,
524 'created_at', NEW.created_at
525 ));
526 END;
527
528 CREATE TRIGGER IF NOT EXISTS sync_trg_milestones_delete
529 AFTER DELETE ON milestones
530 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
531 BEGIN
532 INSERT INTO sync_changelog (table_name, op, row_id, data)
533 VALUES ('milestones', 'DELETE', OLD.id, NULL);
534 END;
535