Skip to main content

max / goingson

4.7 KB · 132 lines History Blame Raw
1 -- Migration 038: Fix sync triggers for events and contacts to include columns
2 -- added in migration 037, and add composite index for common query pattern.
3
4 -- ── events: add external_source, external_id, is_read_only (16 cols total) ──
5
6 DROP TRIGGER IF EXISTS sync_trg_events_insert;
7 CREATE TRIGGER IF NOT EXISTS sync_trg_events_insert
8 AFTER INSERT ON events
9 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
10 BEGIN
11 INSERT INTO sync_changelog (table_name, op, row_id, data)
12 VALUES ('events', 'INSERT', NEW.id, json_object(
13 'id', NEW.id,
14 'project_id', NEW.project_id,
15 'title', NEW.title,
16 'description', NEW.description,
17 'start_time', NEW.start_time,
18 'end_time', NEW.end_time,
19 'location', NEW.location,
20 'user_id', NEW.user_id,
21 'linked_task_id', NEW.linked_task_id,
22 'recurrence', NEW.recurrence,
23 'recurrence_parent_id', NEW.recurrence_parent_id,
24 'contact_id', NEW.contact_id,
25 'block_type', NEW.block_type,
26 'external_source', NEW.external_source,
27 'external_id', NEW.external_id,
28 'is_read_only', NEW.is_read_only
29 ));
30 END;
31
32 DROP TRIGGER IF EXISTS sync_trg_events_update;
33 CREATE TRIGGER IF NOT EXISTS sync_trg_events_update
34 AFTER UPDATE ON events
35 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
36 BEGIN
37 INSERT INTO sync_changelog (table_name, op, row_id, data)
38 VALUES ('events', 'UPDATE', NEW.id, json_object(
39 'id', NEW.id,
40 'project_id', NEW.project_id,
41 'title', NEW.title,
42 'description', NEW.description,
43 'start_time', NEW.start_time,
44 'end_time', NEW.end_time,
45 'location', NEW.location,
46 'user_id', NEW.user_id,
47 'linked_task_id', NEW.linked_task_id,
48 'recurrence', NEW.recurrence,
49 'recurrence_parent_id', NEW.recurrence_parent_id,
50 'contact_id', NEW.contact_id,
51 'block_type', NEW.block_type,
52 'external_source', NEW.external_source,
53 'external_id', NEW.external_id,
54 'is_read_only', NEW.is_read_only
55 ));
56 END;
57
58 -- DELETE trigger unchanged (no data payload), but recreate for consistency
59 DROP TRIGGER IF EXISTS sync_trg_events_delete;
60 CREATE TRIGGER IF NOT EXISTS sync_trg_events_delete
61 AFTER DELETE ON events
62 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
63 BEGIN
64 INSERT INTO sync_changelog (table_name, op, row_id, data)
65 VALUES ('events', 'DELETE', OLD.id, NULL);
66 END;
67
68 -- ── contacts: add external_source, external_id (14 cols total) ──
69
70 DROP TRIGGER IF EXISTS sync_trg_contacts_insert;
71 CREATE TRIGGER IF NOT EXISTS sync_trg_contacts_insert
72 AFTER INSERT ON contacts
73 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
74 BEGIN
75 INSERT INTO sync_changelog (table_name, op, row_id, data)
76 VALUES ('contacts', 'INSERT', NEW.id, json_object(
77 'id', NEW.id,
78 'user_id', NEW.user_id,
79 'display_name', NEW.display_name,
80 'nickname', NEW.nickname,
81 'company', NEW.company,
82 'title', NEW.title,
83 'notes', NEW.notes,
84 'tags', NEW.tags,
85 'birthday', NEW.birthday,
86 'timezone', NEW.timezone,
87 'external_source', NEW.external_source,
88 'external_id', NEW.external_id,
89 'created_at', NEW.created_at,
90 'updated_at', NEW.updated_at
91 ));
92 END;
93
94 DROP TRIGGER IF EXISTS sync_trg_contacts_update;
95 CREATE TRIGGER IF NOT EXISTS sync_trg_contacts_update
96 AFTER UPDATE ON contacts
97 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
98 BEGIN
99 INSERT INTO sync_changelog (table_name, op, row_id, data)
100 VALUES ('contacts', 'UPDATE', NEW.id, json_object(
101 'id', NEW.id,
102 'user_id', NEW.user_id,
103 'display_name', NEW.display_name,
104 'nickname', NEW.nickname,
105 'company', NEW.company,
106 'title', NEW.title,
107 'notes', NEW.notes,
108 'tags', NEW.tags,
109 'birthday', NEW.birthday,
110 'timezone', NEW.timezone,
111 'external_source', NEW.external_source,
112 'external_id', NEW.external_id,
113 'created_at', NEW.created_at,
114 'updated_at', NEW.updated_at
115 ));
116 END;
117
118 -- DELETE trigger unchanged, but recreate for consistency
119 DROP TRIGGER IF EXISTS sync_trg_contacts_delete;
120 CREATE TRIGGER IF NOT EXISTS sync_trg_contacts_delete
121 AFTER DELETE ON contacts
122 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
123 BEGIN
124 INSERT INTO sync_changelog (table_name, op, row_id, data)
125 VALUES ('contacts', 'DELETE', OLD.id, NULL);
126 END;
127
128 -- ── Composite indexes for common query patterns ──
129
130 CREATE INDEX IF NOT EXISTS idx_tasks_user_status ON tasks(user_id, status);
131 CREATE INDEX IF NOT EXISTS idx_tasks_user_status_due ON tasks(user_id, status, due);
132