max / goingson

2.8 KB · 72 lines History Blame Raw

1	-- Sync triggers for email_accounts.
2	-- All columns synced except last_sync_at (device-local IMAP poll state).
3	-- Credentials (password, OAuth tokens) are protected by E2E encryption in transit.
4
5	-- ── email_accounts (20 cols, excluding last_sync_at) ──
6
7	CREATE TRIGGER IF NOT EXISTS sync_trg_email_accounts_insert
8	AFTER INSERT ON email_accounts
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 ('email_accounts', 'INSERT', NEW.id, json_object(
13	'id', NEW.id,
14	'user_id', NEW.user_id,
15	'account_name', NEW.account_name,
16	'email_address', NEW.email_address,
17	'imap_server', NEW.imap_server,
18	'imap_port', NEW.imap_port,
19	'smtp_server', NEW.smtp_server,
20	'smtp_port', NEW.smtp_port,
21	'username', NEW.username,
22	'password', NEW.password,
23	'use_tls', NEW.use_tls,
24	'created_at', NEW.created_at,
25	'archive_folder_name', NEW.archive_folder_name,
26	'auth_type', NEW.auth_type,
27	'oauth2_access_token', NEW.oauth2_access_token,
28	'oauth2_refresh_token', NEW.oauth2_refresh_token,
29	'oauth2_token_expires_at', NEW.oauth2_token_expires_at,
30	'jmap_session_url', NEW.jmap_session_url,
31	'jmap_account_id', NEW.jmap_account_id,
32	'sync_interval_minutes', NEW.sync_interval_minutes
33	));
34	END;
35
36	CREATE TRIGGER IF NOT EXISTS sync_trg_email_accounts_update
37	AFTER UPDATE ON email_accounts
38	WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
39	BEGIN
40	INSERT INTO sync_changelog (table_name, op, row_id, data)
41	VALUES ('email_accounts', 'UPDATE', NEW.id, json_object(
42	'id', NEW.id,
43	'user_id', NEW.user_id,
44	'account_name', NEW.account_name,
45	'email_address', NEW.email_address,
46	'imap_server', NEW.imap_server,
47	'imap_port', NEW.imap_port,
48	'smtp_server', NEW.smtp_server,
49	'smtp_port', NEW.smtp_port,
50	'username', NEW.username,
51	'password', NEW.password,
52	'use_tls', NEW.use_tls,
53	'created_at', NEW.created_at,
54	'archive_folder_name', NEW.archive_folder_name,
55	'auth_type', NEW.auth_type,
56	'oauth2_access_token', NEW.oauth2_access_token,
57	'oauth2_refresh_token', NEW.oauth2_refresh_token,
58	'oauth2_token_expires_at', NEW.oauth2_token_expires_at,
59	'jmap_session_url', NEW.jmap_session_url,
60	'jmap_account_id', NEW.jmap_account_id,
61	'sync_interval_minutes', NEW.sync_interval_minutes
62	));
63	END;
64
65	CREATE TRIGGER IF NOT EXISTS sync_trg_email_accounts_delete
66	AFTER DELETE ON email_accounts
67	WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
68	BEGIN
69	INSERT INTO sync_changelog (table_name, op, row_id, data)
70	VALUES ('email_accounts', 'DELETE', OLD.id, NULL);
71	END;
72