Skip to main content

max / goingson

5.6 KB · 149 lines History Blame Raw
1 -- Rich recurrence rules: JSON configuration for recurring events and tasks.
2 -- The existing 'recurrence' column (Daily/Weekly/Monthly/None) is preserved
3 -- for backward compat. When recurrence_rule is non-null, it takes precedence.
4
5 ALTER TABLE events ADD COLUMN recurrence_rule TEXT;
6 ALTER TABLE tasks ADD COLUMN recurrence_rule TEXT;
7
8 -- ── Update event sync triggers to include recurrence_rule (17 cols total) ──
9
10 DROP TRIGGER IF EXISTS sync_trg_events_insert;
11 CREATE TRIGGER IF NOT EXISTS sync_trg_events_insert
12 AFTER INSERT ON events
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 ('events', 'INSERT', NEW.id, json_object(
17 'id', NEW.id,
18 'project_id', NEW.project_id,
19 'title', NEW.title,
20 'description', NEW.description,
21 'start_time', NEW.start_time,
22 'end_time', NEW.end_time,
23 'location', NEW.location,
24 'user_id', NEW.user_id,
25 'linked_task_id', NEW.linked_task_id,
26 'recurrence', NEW.recurrence,
27 'recurrence_parent_id', NEW.recurrence_parent_id,
28 'recurrence_rule', NEW.recurrence_rule,
29 'contact_id', NEW.contact_id,
30 'block_type', NEW.block_type,
31 'external_source', NEW.external_source,
32 'external_id', NEW.external_id,
33 'is_read_only', NEW.is_read_only
34 ));
35 END;
36
37 DROP TRIGGER IF EXISTS sync_trg_events_update;
38 CREATE TRIGGER IF NOT EXISTS sync_trg_events_update
39 AFTER UPDATE ON events
40 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
41 BEGIN
42 INSERT INTO sync_changelog (table_name, op, row_id, data)
43 VALUES ('events', 'UPDATE', NEW.id, json_object(
44 'id', NEW.id,
45 'project_id', NEW.project_id,
46 'title', NEW.title,
47 'description', NEW.description,
48 'start_time', NEW.start_time,
49 'end_time', NEW.end_time,
50 'location', NEW.location,
51 'user_id', NEW.user_id,
52 'linked_task_id', NEW.linked_task_id,
53 'recurrence', NEW.recurrence,
54 'recurrence_parent_id', NEW.recurrence_parent_id,
55 'recurrence_rule', NEW.recurrence_rule,
56 'contact_id', NEW.contact_id,
57 'block_type', NEW.block_type,
58 'external_source', NEW.external_source,
59 'external_id', NEW.external_id,
60 'is_read_only', NEW.is_read_only
61 ));
62 END;
63
64 -- DELETE trigger unchanged (no data payload), but recreate for consistency
65 DROP TRIGGER IF EXISTS sync_trg_events_delete;
66 CREATE TRIGGER IF NOT EXISTS sync_trg_events_delete
67 AFTER DELETE ON events
68 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
69 BEGIN
70 INSERT INTO sync_changelog (table_name, op, row_id, data)
71 VALUES ('events', 'DELETE', OLD.id, NULL);
72 END;
73
74 -- ── Update task sync triggers to include recurrence_rule (28 cols total) ──
75
76 DROP TRIGGER IF EXISTS sync_trg_tasks_insert;
77 CREATE TRIGGER IF NOT EXISTS sync_trg_tasks_insert
78 AFTER INSERT ON tasks
79 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
80 BEGIN
81 INSERT INTO sync_changelog (table_name, op, row_id, data)
82 VALUES ('tasks', 'INSERT', NEW.id, json_object(
83 'id', NEW.id,
84 'project_id', NEW.project_id,
85 'description', NEW.description,
86 'status', NEW.status,
87 'priority', NEW.priority,
88 'due', NEW.due,
89 'tags', NEW.tags,
90 'urgency', NEW.urgency,
91 'recurrence', NEW.recurrence,
92 'recurrence_rule', NEW.recurrence_rule,
93 'created_at', NEW.created_at,
94 'user_id', NEW.user_id,
95 'recurrence_parent_id', NEW.recurrence_parent_id,
96 'source_email_id', NEW.source_email_id,
97 'snoozed_until', NEW.snoozed_until,
98 'waiting_for_response', NEW.waiting_for_response,
99 'waiting_since', NEW.waiting_since,
100 'expected_response_date', NEW.expected_response_date,
101 'scheduled_start', NEW.scheduled_start,
102 'scheduled_duration', NEW.scheduled_duration,
103 'is_focus', NEW.is_focus,
104 'focus_set_at', NEW.focus_set_at,
105 'contact_id', NEW.contact_id,
106 'milestone_id', NEW.milestone_id,
107 'completed_at', NEW.completed_at,
108 'estimated_minutes', NEW.estimated_minutes,
109 'actual_minutes', NEW.actual_minutes
110 ));
111 END;
112
113 DROP TRIGGER IF EXISTS sync_trg_tasks_update;
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 'recurrence_rule', NEW.recurrence_rule,
130 'created_at', NEW.created_at,
131 'user_id', NEW.user_id,
132 'recurrence_parent_id', NEW.recurrence_parent_id,
133 'source_email_id', NEW.source_email_id,
134 'snoozed_until', NEW.snoozed_until,
135 'waiting_for_response', NEW.waiting_for_response,
136 'waiting_since', NEW.waiting_since,
137 'expected_response_date', NEW.expected_response_date,
138 'scheduled_start', NEW.scheduled_start,
139 'scheduled_duration', NEW.scheduled_duration,
140 'is_focus', NEW.is_focus,
141 'focus_set_at', NEW.focus_set_at,
142 'contact_id', NEW.contact_id,
143 'milestone_id', NEW.milestone_id,
144 'completed_at', NEW.completed_at,
145 'estimated_minutes', NEW.estimated_minutes,
146 'actual_minutes', NEW.actual_minutes
147 ));
148 END;
149