Skip to main content

max / balanced_breakfast

4.8 KB · 145 lines History Blame Raw
1 -- Feature: Cloud sync
2 -- SyncKit cloud sync tables and triggers.
3
4 -- Key-value preferences store (Phase 6A).
5 CREATE TABLE IF NOT EXISTS user_config (
6 key TEXT PRIMARY KEY,
7 value TEXT
8 );
9
10 -- Sync metadata (single row, key-value).
11 CREATE TABLE IF NOT EXISTS sync_state (
12 key TEXT PRIMARY KEY,
13 value TEXT NOT NULL
14 );
15
16 INSERT INTO sync_state (key, value) VALUES ('device_id', '');
17 INSERT INTO sync_state (key, value) VALUES ('pull_cursor', '0');
18 INSERT INTO sync_state (key, value) VALUES ('auto_sync_enabled', '0');
19 INSERT INTO sync_state (key, value) VALUES ('sync_interval_minutes', '15');
20 INSERT INTO sync_state (key, value) VALUES ('applying_remote', '0');
21 INSERT INTO sync_state (key, value) VALUES ('last_sync_at', '');
22 INSERT INTO sync_state (key, value) VALUES ('initial_snapshot_done', '0');
23
24 -- Changelog of local changes to push.
25 CREATE TABLE IF NOT EXISTS sync_changelog (
26 id INTEGER PRIMARY KEY AUTOINCREMENT,
27 table_name TEXT NOT NULL,
28 op TEXT NOT NULL,
29 row_id TEXT NOT NULL,
30 timestamp TEXT NOT NULL DEFAULT (datetime('now')),
31 data TEXT,
32 pushed INTEGER NOT NULL DEFAULT 0
33 );
34
35 CREATE INDEX idx_sync_changelog_pushed ON sync_changelog(pushed);
36
37 -- ── Triggers: feeds ──
38
39 CREATE TRIGGER sync_feeds_insert AFTER INSERT ON feeds
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 ('feeds', 'INSERT', NEW.id, json_object(
44 'id', NEW.id,
45 'busser_id', NEW.busser_id,
46 'name', NEW.name,
47 'config', NEW.config,
48 'enabled', NEW.enabled,
49 'last_fetch', NEW.last_fetch,
50 'created_at', NEW.created_at,
51 'updated_at', NEW.updated_at,
52 'consecutive_failures', NEW.consecutive_failures,
53 'last_error', NEW.last_error,
54 'last_success_at', NEW.last_success_at
55 ));
56 END;
57
58 CREATE TRIGGER sync_feeds_update AFTER UPDATE ON feeds
59 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
60 BEGIN
61 INSERT INTO sync_changelog (table_name, op, row_id, data)
62 VALUES ('feeds', 'UPDATE', NEW.id, json_object(
63 'id', NEW.id,
64 'busser_id', NEW.busser_id,
65 'name', NEW.name,
66 'config', NEW.config,
67 'enabled', NEW.enabled,
68 'last_fetch', NEW.last_fetch,
69 'created_at', NEW.created_at,
70 'updated_at', NEW.updated_at,
71 'consecutive_failures', NEW.consecutive_failures,
72 'last_error', NEW.last_error,
73 'last_success_at', NEW.last_success_at
74 ));
75 END;
76
77 CREATE TRIGGER sync_feeds_delete AFTER DELETE ON feeds
78 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
79 BEGIN
80 INSERT INTO sync_changelog (table_name, op, row_id)
81 VALUES ('feeds', 'DELETE', OLD.id);
82 END;
83
84 -- ── Triggers: feed_tags ──
85
86 CREATE TRIGGER sync_feed_tags_insert AFTER INSERT ON feed_tags
87 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
88 BEGIN
89 INSERT INTO sync_changelog (table_name, op, row_id, data)
90 VALUES ('feed_tags', 'INSERT', NEW.feed_id || ':' || NEW.tag, json_object(
91 'feed_id', NEW.feed_id,
92 'tag', NEW.tag
93 ));
94 END;
95
96 CREATE TRIGGER sync_feed_tags_delete AFTER DELETE ON feed_tags
97 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
98 BEGIN
99 INSERT INTO sync_changelog (table_name, op, row_id)
100 VALUES ('feed_tags', 'DELETE', OLD.feed_id || ':' || OLD.tag);
101 END;
102
103 -- ── Triggers: user_config ──
104
105 CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
106 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
107 BEGIN
108 INSERT INTO sync_changelog (table_name, op, row_id, data)
109 VALUES ('user_config', 'INSERT', NEW.key, json_object(
110 'key', NEW.key,
111 'value', NEW.value
112 ));
113 END;
114
115 CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
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 ('user_config', 'UPDATE', NEW.key, json_object(
120 'key', NEW.key,
121 'value', NEW.value
122 ));
123 END;
124
125 CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
126 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
127 BEGIN
128 INSERT INTO sync_changelog (table_name, op, row_id)
129 VALUES ('user_config', 'DELETE', OLD.key);
130 END;
131
132 -- ── Triggers: feed_items (user state only) ──
133
134 CREATE TRIGGER sync_feed_items_update AFTER UPDATE ON feed_items
135 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
136 AND (OLD.is_read != NEW.is_read OR OLD.is_starred != NEW.is_starred)
137 BEGIN
138 INSERT INTO sync_changelog (table_name, op, row_id, data)
139 VALUES ('feed_items', 'UPDATE', NEW.id, json_object(
140 'id', NEW.id,
141 'is_read', NEW.is_read,
142 'is_starred', NEW.is_starred
143 ));
144 END;
145