Skip to main content

max / goingson

6.3 KB · 182 lines History Blame Raw
1 -- Sync saved_views, weekly_reviews, monthly_goals, and monthly_reflections across
2 -- devices (ultra-fuzz Run #28 S2). These user-content tables were backed up but
3 -- never synced — edits silently diverged between devices. They follow the same
4 -- changelog-trigger pattern as every other synced table (030_sync_changelog.sql),
5 -- gated on the `applying_remote` flag so pull-applied writes don't re-log. Matching
6 -- SYNCED_COLUMNS / UPSERT_ORDER / DELETE_ORDER entries land in the sync service; the
7 -- trigger-vs-whitelist round-trip test keeps the two in lockstep.
8
9 -- ── saved_views (11 cols) ──
10
11 CREATE TRIGGER IF NOT EXISTS sync_trg_saved_views_insert
12 AFTER INSERT ON saved_views
13 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
14 BEGIN
15 INSERT INTO sync_changelog (table_name, op, row_id, data)
16 VALUES ('saved_views', 'INSERT', NEW.id, json_object(
17 'id', NEW.id,
18 'user_id', NEW.user_id,
19 'name', NEW.name,
20 'view_type', NEW.view_type,
21 'filters', NEW.filters,
22 'sort_by', NEW.sort_by,
23 'sort_order', NEW.sort_order,
24 'is_pinned', NEW.is_pinned,
25 'position', NEW.position,
26 'created_at', NEW.created_at,
27 'updated_at', NEW.updated_at
28 ));
29 END;
30
31 CREATE TRIGGER IF NOT EXISTS sync_trg_saved_views_update
32 AFTER UPDATE ON saved_views
33 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
34 BEGIN
35 INSERT INTO sync_changelog (table_name, op, row_id, data)
36 VALUES ('saved_views', 'UPDATE', NEW.id, json_object(
37 'id', NEW.id,
38 'user_id', NEW.user_id,
39 'name', NEW.name,
40 'view_type', NEW.view_type,
41 'filters', NEW.filters,
42 'sort_by', NEW.sort_by,
43 'sort_order', NEW.sort_order,
44 'is_pinned', NEW.is_pinned,
45 'position', NEW.position,
46 'created_at', NEW.created_at,
47 'updated_at', NEW.updated_at
48 ));
49 END;
50
51 CREATE TRIGGER IF NOT EXISTS sync_trg_saved_views_delete
52 AFTER DELETE ON saved_views
53 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
54 BEGIN
55 INSERT INTO sync_changelog (table_name, op, row_id, data)
56 VALUES ('saved_views', 'DELETE', OLD.id, NULL);
57 END;
58
59 -- ── weekly_reviews (6 cols) ──
60
61 CREATE TRIGGER IF NOT EXISTS sync_trg_weekly_reviews_insert
62 AFTER INSERT ON weekly_reviews
63 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
64 BEGIN
65 INSERT INTO sync_changelog (table_name, op, row_id, data)
66 VALUES ('weekly_reviews', 'INSERT', NEW.id, json_object(
67 'id', NEW.id,
68 'user_id', NEW.user_id,
69 'week_start_date', NEW.week_start_date,
70 'completed_at', NEW.completed_at,
71 'notes', NEW.notes,
72 'vacation_days', NEW.vacation_days
73 ));
74 END;
75
76 CREATE TRIGGER IF NOT EXISTS sync_trg_weekly_reviews_update
77 AFTER UPDATE ON weekly_reviews
78 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
79 BEGIN
80 INSERT INTO sync_changelog (table_name, op, row_id, data)
81 VALUES ('weekly_reviews', 'UPDATE', NEW.id, json_object(
82 'id', NEW.id,
83 'user_id', NEW.user_id,
84 'week_start_date', NEW.week_start_date,
85 'completed_at', NEW.completed_at,
86 'notes', NEW.notes,
87 'vacation_days', NEW.vacation_days
88 ));
89 END;
90
91 CREATE TRIGGER IF NOT EXISTS sync_trg_weekly_reviews_delete
92 AFTER DELETE ON weekly_reviews
93 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
94 BEGIN
95 INSERT INTO sync_changelog (table_name, op, row_id, data)
96 VALUES ('weekly_reviews', 'DELETE', OLD.id, NULL);
97 END;
98
99 -- ── monthly_goals (8 cols) ──
100
101 CREATE TRIGGER IF NOT EXISTS sync_trg_monthly_goals_insert
102 AFTER INSERT ON monthly_goals
103 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
104 BEGIN
105 INSERT INTO sync_changelog (table_name, op, row_id, data)
106 VALUES ('monthly_goals', 'INSERT', NEW.id, json_object(
107 'id', NEW.id,
108 'user_id', NEW.user_id,
109 'month', NEW.month,
110 'text', NEW.text,
111 'status', NEW.status,
112 'position', NEW.position,
113 'created_at', NEW.created_at,
114 'updated_at', NEW.updated_at
115 ));
116 END;
117
118 CREATE TRIGGER IF NOT EXISTS sync_trg_monthly_goals_update
119 AFTER UPDATE ON monthly_goals
120 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
121 BEGIN
122 INSERT INTO sync_changelog (table_name, op, row_id, data)
123 VALUES ('monthly_goals', 'UPDATE', NEW.id, json_object(
124 'id', NEW.id,
125 'user_id', NEW.user_id,
126 'month', NEW.month,
127 'text', NEW.text,
128 'status', NEW.status,
129 'position', NEW.position,
130 'created_at', NEW.created_at,
131 'updated_at', NEW.updated_at
132 ));
133 END;
134
135 CREATE TRIGGER IF NOT EXISTS sync_trg_monthly_goals_delete
136 AFTER DELETE ON monthly_goals
137 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
138 BEGIN
139 INSERT INTO sync_changelog (table_name, op, row_id, data)
140 VALUES ('monthly_goals', 'DELETE', OLD.id, NULL);
141 END;
142
143 -- ── monthly_reflections (6 cols) ──
144
145 CREATE TRIGGER IF NOT EXISTS sync_trg_monthly_reflections_insert
146 AFTER INSERT ON monthly_reflections
147 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
148 BEGIN
149 INSERT INTO sync_changelog (table_name, op, row_id, data)
150 VALUES ('monthly_reflections', 'INSERT', NEW.id, json_object(
151 'id', NEW.id,
152 'user_id', NEW.user_id,
153 'month', NEW.month,
154 'highlight_text', NEW.highlight_text,
155 'change_text', NEW.change_text,
156 'completed_at', NEW.completed_at
157 ));
158 END;
159
160 CREATE TRIGGER IF NOT EXISTS sync_trg_monthly_reflections_update
161 AFTER UPDATE ON monthly_reflections
162 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
163 BEGIN
164 INSERT INTO sync_changelog (table_name, op, row_id, data)
165 VALUES ('monthly_reflections', 'UPDATE', NEW.id, json_object(
166 'id', NEW.id,
167 'user_id', NEW.user_id,
168 'month', NEW.month,
169 'highlight_text', NEW.highlight_text,
170 'change_text', NEW.change_text,
171 'completed_at', NEW.completed_at
172 ));
173 END;
174
175 CREATE TRIGGER IF NOT EXISTS sync_trg_monthly_reflections_delete
176 AFTER DELETE ON monthly_reflections
177 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
178 BEGIN
179 INSERT INTO sync_changelog (table_name, op, row_id, data)
180 VALUES ('monthly_reflections', 'DELETE', OLD.id, NULL);
181 END;
182