max / goingson

3.2 KB · 72 lines History Blame Raw

1	-- Migration 037: External sync fields + sync_accounts table
2	-- Adds provenance tracking for imported/synced records and provider connection management.
3
4	-- Track provenance of imported/synced events
5	ALTER TABLE events ADD COLUMN external_source TEXT;
6	ALTER TABLE events ADD COLUMN external_id TEXT;
7	ALTER TABLE events ADD COLUMN is_read_only INTEGER NOT NULL DEFAULT 0;
8
9	-- Track provenance of imported/synced contacts
10	ALTER TABLE contacts ADD COLUMN external_source TEXT;
11	ALTER TABLE contacts ADD COLUMN external_id TEXT;
12
13	-- Unique index: one record per external source+id pair
14	CREATE UNIQUE INDEX IF NOT EXISTS idx_events_external
15	ON events(external_source, external_id) WHERE external_source IS NOT NULL;
16	CREATE UNIQUE INDEX IF NOT EXISTS idx_contacts_external
17	ON contacts(external_source, external_id) WHERE external_source IS NOT NULL;
18
19	-- Sync accounts table for provider connections (Google, Apple, Microsoft)
20	CREATE TABLE IF NOT EXISTS sync_accounts (
21	id TEXT PRIMARY KEY NOT NULL,
22	user_id TEXT NOT NULL REFERENCES users(id),
23	provider TEXT NOT NULL,
24	account_name TEXT NOT NULL,
25	email TEXT,
26	sync_calendars INTEGER NOT NULL DEFAULT 1,
27	sync_contacts INTEGER NOT NULL DEFAULT 1,
28	calendar_ids TEXT NOT NULL DEFAULT '[]',
29	last_calendar_sync TEXT,
30	last_contact_sync TEXT,
31	sync_interval_minutes INTEGER NOT NULL DEFAULT 15,
32	enabled INTEGER NOT NULL DEFAULT 1,
33	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
34	);
35
36	-- Sync changelog triggers for sync_accounts
37	CREATE TRIGGER IF NOT EXISTS sync_accounts_insert_changelog
38	AFTER INSERT ON sync_accounts
39	WHEN (SELECT COALESCE(value, '0') FROM sync_state WHERE key = 'applying_remote') = '0'
40	BEGIN
41	INSERT INTO sync_changelog (table_name, row_id, op, data)
42	VALUES ('sync_accounts', NEW.id, 'INSERT', json_object(
43	'id', NEW.id, 'user_id', NEW.user_id, 'provider', NEW.provider,
44	'account_name', NEW.account_name, 'email', NEW.email,
45	'sync_calendars', NEW.sync_calendars, 'sync_contacts', NEW.sync_contacts,
46	'calendar_ids', NEW.calendar_ids, 'sync_interval_minutes', NEW.sync_interval_minutes,
47	'enabled', NEW.enabled, 'created_at', NEW.created_at
48	));
49	END;
50
51	CREATE TRIGGER IF NOT EXISTS sync_accounts_update_changelog
52	AFTER UPDATE ON sync_accounts
53	WHEN (SELECT COALESCE(value, '0') FROM sync_state WHERE key = 'applying_remote') = '0'
54	BEGIN
55	INSERT INTO sync_changelog (table_name, row_id, op, data)
56	VALUES ('sync_accounts', NEW.id, 'UPDATE', json_object(
57	'id', NEW.id, 'user_id', NEW.user_id, 'provider', NEW.provider,
58	'account_name', NEW.account_name, 'email', NEW.email,
59	'sync_calendars', NEW.sync_calendars, 'sync_contacts', NEW.sync_contacts,
60	'calendar_ids', NEW.calendar_ids, 'sync_interval_minutes', NEW.sync_interval_minutes,
61	'enabled', NEW.enabled, 'created_at', NEW.created_at
62	));
63	END;
64
65	CREATE TRIGGER IF NOT EXISTS sync_accounts_delete_changelog
66	AFTER DELETE ON sync_accounts
67	WHEN (SELECT COALESCE(value, '0') FROM sync_state WHERE key = 'applying_remote') = '0'
68	BEGIN
69	INSERT INTO sync_changelog (table_name, row_id, op, data)
70	VALUES ('sync_accounts', OLD.id, 'DELETE', json_object('id', OLD.id));
71	END;
72