max / goingson

3.0 KB · 87 lines History Blame Raw

1	-- Contacts: People with multiple emails, phones, and social handles
2	-- Enables future integration with emails (auto-suggest sender as contact)
3	-- and tasks/events (link contacts)
4
5	CREATE TABLE contacts (
6	id TEXT PRIMARY KEY NOT NULL,
7	user_id TEXT NOT NULL REFERENCES users(id),
8	display_name TEXT NOT NULL,
9	nickname TEXT,
10	company TEXT,
11	title TEXT,
12	notes TEXT NOT NULL DEFAULT '',
13	tags TEXT NOT NULL DEFAULT '[]',
14	birthday TEXT,
15	timezone TEXT,
16	created_at TEXT NOT NULL,
17	updated_at TEXT NOT NULL
18	);
19
20	CREATE INDEX idx_contacts_user_id ON contacts(user_id);
21	CREATE INDEX idx_contacts_display_name ON contacts(user_id, display_name);
22
23	-- Contact email addresses (multiple per contact)
24	CREATE TABLE contact_emails (
25	id TEXT PRIMARY KEY NOT NULL,
26	contact_id TEXT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
27	address TEXT NOT NULL,
28	label TEXT NOT NULL DEFAULT '',
29	is_primary INTEGER NOT NULL DEFAULT 0
30	);
31
32	CREATE INDEX idx_contact_emails_contact_id ON contact_emails(contact_id);
33	CREATE INDEX idx_contact_emails_address ON contact_emails(address);
34
35	-- Contact phone numbers (multiple per contact)
36	CREATE TABLE contact_phones (
37	id TEXT PRIMARY KEY NOT NULL,
38	contact_id TEXT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
39	number TEXT NOT NULL,
40	label TEXT NOT NULL DEFAULT '',
41	is_primary INTEGER NOT NULL DEFAULT 0
42	);
43
44	CREATE INDEX idx_contact_phones_contact_id ON contact_phones(contact_id);
45
46	-- Contact social handles (multiple per contact)
47	CREATE TABLE contact_social_handles (
48	id TEXT PRIMARY KEY NOT NULL,
49	contact_id TEXT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
50	platform TEXT NOT NULL,
51	handle TEXT NOT NULL,
52	url TEXT
53	);
54
55	CREATE INDEX idx_contact_social_handles_contact_id ON contact_social_handles(contact_id);
56
57	-- Full-text search for contacts
58	CREATE VIRTUAL TABLE contacts_fts USING fts5(
59	id UNINDEXED,
60	user_id UNINDEXED,
61	display_name,
62	nickname,
63	company,
64	notes,
65	tags,
66	content='contacts',
67	content_rowid='rowid'
68	);
69
70	-- FTS sync triggers
71	CREATE TRIGGER contacts_ai AFTER INSERT ON contacts BEGIN
72	INSERT INTO contacts_fts(id, user_id, display_name, nickname, company, notes, tags)
73	VALUES (new.id, new.user_id, new.display_name, new.nickname, new.company, new.notes, new.tags);
74	END;
75
76	CREATE TRIGGER contacts_ad AFTER DELETE ON contacts BEGIN
77	INSERT INTO contacts_fts(contacts_fts, id, user_id, display_name, nickname, company, notes, tags)
78	VALUES ('delete', old.id, old.user_id, old.display_name, old.nickname, old.company, old.notes, old.tags);
79	END;
80
81	CREATE TRIGGER contacts_au AFTER UPDATE ON contacts BEGIN
82	INSERT INTO contacts_fts(contacts_fts, id, user_id, display_name, nickname, company, notes, tags)
83	VALUES ('delete', old.id, old.user_id, old.display_name, old.nickname, old.company, old.notes, old.tags);
84	INSERT INTO contacts_fts(id, user_id, display_name, nickname, company, notes, tags)
85	VALUES (new.id, new.user_id, new.display_name, new.nickname, new.company, new.notes, new.tags);
86	END;
87