max / goingson

17.2 KB · 449 lines History Blame Raw

1	# GoingsOn Database Schema
2
3	SQLite schema reference. 50 migrations. Migrations live in `migrations/sqlite/` and auto-run on first launch.
4
5	## Domain Map
6
7	\| Domain \| Tables \| Purpose \|
8	\|--------\|--------\|---------\|
9	\| Core \| 5 \| Projects, tasks, subtasks, annotations, milestones \|
10	\| Email \| 2 \| Email accounts (IMAP/JMAP), email messages \|
11	\| Contacts \| 5 \| People, email addresses, phones, social handles, custom fields \|
12	\| Planning \| 3 \| Events (calendar/time blocks), weekly reviews, saved views \|
13	\| Search \| 5 \| FTS5 virtual tables for emails, tasks, projects, events, contacts \|
14	\| Sync \| 2 \| SyncKit changelog and state (cloud sync via MNW) \|
15	\| Config \| 1 \| Users, backup settings \|
16
17	---
18
19	## Core
20
21	### projects
22	Top-level organizational unit (work projects, personal areas, etc.).
23
24	\| Column \| Type \| Notes \|
25	\|--------\|------\|-------\|
26	\| `id` \| TEXT PK \| \|
27	\| `user_id` \| TEXT FK \| -> users (CASCADE). Added migration 002 \|
28	\| `name` \| TEXT \| NOT NULL \|
29	\| `description` \| TEXT \| Default '' \|
30	\| `project_type` \| TEXT \| Default 'Other' \|
31	\| `status` \| TEXT \| Default 'Active' \|
32	\| `created_at` \| TEXT \| `datetime('now')` \|
33
34	Indexes: `status`, `project_type`, `user_id`.
35
36	### tasks
37	TaskWarrior-inspired task model with urgency scoring, recurrence, scheduling, snooze, and follow-up tracking.
38
39	\| Column \| Type \| Notes \|
40	\|--------\|------\|-------\|
41	\| `id` \| TEXT PK \| \|
42	\| `user_id` \| TEXT FK \| -> users (CASCADE). Migration 002 \|
43	\| `project_id` \| TEXT FK \| -> projects (SET NULL) \|
44	\| `description` \| TEXT \| NOT NULL \|
45	\| `status` \| TEXT \| Default 'Pending' \|
46	\| `priority` \| TEXT \| Default 'Medium' \|
47	\| `due` \| TEXT \| ISO datetime, nullable \|
48	\| `tags` \| TEXT \| JSON array string, default '[]' \|
49	\| `urgency` \| REAL \| Computed score, default 0.0 \|
50	\| `recurrence` \| TEXT \| Default 'None' \|
51	\| `recurrence_parent_id` \| TEXT FK \| -> tasks (SET NULL). Migration 003 \|
52	\| `source_email_id` \| TEXT FK \| -> emails (SET NULL). Migration 009 \|
53	\| `snoozed_until` \| TEXT \| Migration 010 \|
54	\| `waiting_for_response` \| INTEGER \| Boolean, default 0. Migration 012 \|
55	\| `waiting_since` \| TEXT \| Migration 012 \|
56	\| `expected_response_date` \| TEXT \| Migration 012 \|
57	\| `scheduled_start` \| TEXT \| Migration 013 \|
58	\| `scheduled_duration` \| INTEGER \| Minutes. Migration 013 \|
59	\| `is_focus` \| INTEGER \| Boolean, default 0. Migration 018 \|
60	\| `focus_set_at` \| TEXT \| Migration 018 \|
61	\| `contact_id` \| TEXT FK \| -> contacts (SET NULL). Migration 025 \|
62	\| `milestone_id` \| TEXT FK \| -> milestones (SET NULL). Migration 028 \|
63	\| `completed_at` \| TEXT \| Backfilled from created_at. Migration 031 \|
64	\| `created_at` \| TEXT \| `datetime('now')` \|
65
66	Indexes: `project_id`, `status`, `priority`, `due`, `urgency DESC`, `user_id`, `recurrence_parent_id`, `source_email_id`, `snoozed_until`, `scheduled_start`, `contact_id`. Partial indexes: `waiting_for_response WHERE = 1`, `is_focus WHERE = 1`, `completed_at WHERE NOT NULL`.
67
68	### subtasks
69	Checkbox items within a task. Can optionally link to another full task (multi-phase work).
70
71	\| Column \| Type \| Notes \|
72	\|--------\|------\|-------\|
73	\| `id` \| TEXT PK \| \|
74	\| `task_id` \| TEXT FK \| -> tasks (CASCADE) \|
75	\| `text` \| TEXT \| NOT NULL \|
76	\| `is_completed` \| INTEGER \| Boolean, default 0 \|
77	\| `position` \| INTEGER \| Sort order, default 0 \|
78	\| `linked_task_id` \| TEXT FK \| -> tasks (SET NULL). Migration 022 \|
79	\| `created_at` \| TEXT \| `datetime('now')` \|
80
81	Indexes: `task_id`, `linked_task_id`.
82
83	### annotations
84	Timestamped notes attached to tasks.
85
86	\| Column \| Type \| Notes \|
87	\|--------\|------\|-------\|
88	\| `id` \| TEXT PK \| \|
89	\| `task_id` \| TEXT FK \| -> tasks (CASCADE) \|
90	\| `timestamp` \| TEXT \| `datetime('now')` \|
91	\| `note` \| TEXT \| NOT NULL \|
92
93	Index: `task_id`.
94
95	### milestones
96	Scope boundaries within a project. "When these tasks are done, ship it." Migration 028.
97
98	\| Column \| Type \| Notes \|
99	\|--------\|------\|-------\|
100	\| `id` \| TEXT PK \| \|
101	\| `user_id` \| TEXT FK \| -> users (CASCADE) \|
102	\| `project_id` \| TEXT FK \| -> projects (CASCADE) \|
103	\| `name` \| TEXT \| NOT NULL \|
104	\| `description` \| TEXT \| Default '' \|
105	\| `position` \| INTEGER \| Sort order, default 0 \|
106	\| `target_date` \| TEXT \| Nullable \|
107	\| `status` \| TEXT \| Default 'open' \|
108	\| `created_at` \| TEXT \| `datetime('now')` \|
109
110	Index: `project_id`. Tasks link to milestones via `tasks.milestone_id`.
111
112	---
113
114	## Email
115
116	### email_accounts
117	IMAP/SMTP and OAuth2/JMAP account configuration. Credentials excluded from sync (migration 033).
118
119	\| Column \| Type \| Notes \|
120	\|--------\|------\|-------\|
121	\| `id` \| TEXT PK \| \|
122	\| `user_id` \| TEXT FK \| -> users (CASCADE) \|
123	\| `account_name` \| TEXT \| Display name \|
124	\| `email_address` \| TEXT \| Unique per user \|
125	\| `imap_server` \| TEXT \| \|
126	\| `imap_port` \| INTEGER \| Default 993 \|
127	\| `smtp_server` \| TEXT \| \|
128	\| `smtp_port` \| INTEGER \| Default 587 \|
129	\| `username` \| TEXT \| \|
130	\| `password` \| TEXT \| IMAP/SMTP credential \|
131	\| `use_tls` \| INTEGER \| Boolean, default 1 \|
132	\| `auth_type` \| TEXT \| 'password' or 'oauth2_fastmail'. Migration 017 \|
133	\| `oauth2_access_token` \| TEXT \| Migration 017 \|
134	\| `oauth2_refresh_token` \| TEXT \| Migration 017 \|
135	\| `oauth2_token_expires_at` \| TEXT \| Migration 017 \|
136	\| `jmap_session_url` \| TEXT \| Fastmail JMAP. Migration 017 \|
137	\| `jmap_account_id` \| TEXT \| Migration 017 \|
138	\| `archive_folder_name` \| TEXT \| Default 'Archive'. Migration 006 \|
139	\| `sync_interval_minutes` \| INTEGER \| Default 15, NULL = disabled. Migration 021 \|
140	\| `last_sync_at` \| TEXT \| Device-local, excluded from sync \|
141	\| `created_at` \| TEXT \| `datetime('now')` \|
142
143	Unique: `(user_id, email_address)`. Index: `user_id`.
144
145	### emails
146	Individual email messages synced from IMAP/JMAP.
147
148	\| Column \| Type \| Notes \|
149	\|--------\|------\|-------\|
150	\| `id` \| TEXT PK \| \|
151	\| `user_id` \| TEXT FK \| -> users (CASCADE). Migration 002 \|
152	\| `project_id` \| TEXT FK \| -> projects (SET NULL) \|
153	\| `email_account_id` \| TEXT FK \| -> email_accounts (SET NULL). Migration 004 \|
154	\| `from_address` \| TEXT \| \|
155	\| `to_address` \| TEXT \| \|
156	\| `subject` \| TEXT \| \|
157	\| `body` \| TEXT \| Plain text, default '' \|
158	\| `html_body` \| TEXT \| Original HTML for browser view. Migration 020 \|
159	\| `message_id` \| TEXT \| RFC 2822 Message-ID. Migration 004 \|
160	\| `in_reply_to` \| TEXT \| Threading header. Migration 016 \|
161	\| `thread_id` \| TEXT \| Conversation grouping. Migration 016 \|
162	\| `is_read` \| INTEGER \| Boolean, default 0 \|
163	\| `is_archived` \| INTEGER \| Boolean, default 0. Migration 005 \|
164	\| `is_outgoing` \| INTEGER \| Boolean, default 0. Migration 004 \|
165	\| `imap_uid` \| INTEGER \| IMAP UID for sync. Migration 006 \|
166	\| `source_folder` \| TEXT \| IMAP folder. Migration 006 \|
167	\| `snoozed_until` \| TEXT \| Migration 010 \|
168	\| `waiting_for_response` \| INTEGER \| Boolean, default 0. Migration 012 \|
169	\| `waiting_since` \| TEXT \| Migration 012 \|
170	\| `expected_response_date` \| TEXT \| Migration 012 \|
171	\| `received_at` \| TEXT \| `datetime('now')` \|
172
173	Indexes: `project_id`, `is_read`, `received_at DESC`, `user_id`, `message_id`, `email_account_id`, `(email_account_id, imap_uid, source_folder)`, `is_archived`, `snoozed_until`. Partial indexes: `thread_id WHERE NOT NULL`, `in_reply_to WHERE NOT NULL`, `waiting_for_response WHERE = 1`.
174
175	---
176
177	## Contacts
178
179	### contacts
180	People with structured metadata. Migration 023.
181
182	\| Column \| Type \| Notes \|
183	\|--------\|------\|-------\|
184	\| `id` \| TEXT PK \| \|
185	\| `user_id` \| TEXT FK \| -> users \|
186	\| `display_name` \| TEXT \| NOT NULL \|
187	\| `nickname` \| TEXT \| \|
188	\| `company` \| TEXT \| \|
189	\| `title` \| TEXT \| Job title \|
190	\| `notes` \| TEXT \| Default '' \|
191	\| `tags` \| TEXT \| JSON array string, default '[]' \|
192	\| `birthday` \| TEXT \| \|
193	\| `timezone` \| TEXT \| \|
194	\| `created_at` \| TEXT \| \|
195	\| `updated_at` \| TEXT \| \|
196
197	Indexes: `user_id`, `(user_id, display_name)`.
198
199	### contact_emails
200	Multiple email addresses per contact. Migration 023.
201
202	- FK `contact_id` -> contacts (CASCADE)
203	- `address`, `label` (default ''), `is_primary` (boolean)
204	- Indexes: `contact_id`, `address`
205
206	### contact_phones
207	Multiple phone numbers per contact. Migration 023.
208
209	- FK `contact_id` -> contacts (CASCADE)
210	- `number`, `label` (default ''), `is_primary` (boolean)
211	- Index: `contact_id`
212
213	### contact_social_handles
214	Social platform handles per contact. Migration 023.
215
216	- FK `contact_id` -> contacts (CASCADE)
217	- `platform`, `handle`, `url` (optional)
218	- Index: `contact_id`
219
220	### contact_custom_fields
221	Arbitrary label/value pairs with optional URL. Migration 024.
222
223	- FK `contact_id` -> contacts (CASCADE)
224	- `label`, `value`, `url` (optional)
225	- Index: `contact_id`
226
227	---
228
229	## Planning
230
231	### events
232	Calendar events and time blocks. Supports recurrence and task/contact linking.
233
234	\| Column \| Type \| Notes \|
235	\|--------\|------\|-------\|
236	\| `id` \| TEXT PK \| \|
237	\| `user_id` \| TEXT FK \| -> users (CASCADE). Migration 002 \|
238	\| `project_id` \| TEXT FK \| -> projects (SET NULL) \|
239	\| `title` \| TEXT \| NOT NULL \|
240	\| `description` \| TEXT \| Default '' \|
241	\| `start_time` \| TEXT \| ISO datetime \|
242	\| `end_time` \| TEXT \| Nullable \|
243	\| `location` \| TEXT \| \|
244	\| `linked_task_id` \| TEXT FK \| -> tasks (SET NULL). Migration 008 \|
245	\| `recurrence` \| TEXT \| Default 'None'. Migration 008 \|
246	\| `recurrence_parent_id` \| TEXT FK \| -> events (SET NULL). Migration 008 \|
247	\| `contact_id` \| TEXT FK \| -> contacts (SET NULL). Migration 025 \|
248	\| `block_type` \| TEXT \| Time block category. Migration 026 \|
249
250	Indexes: `project_id`, `start_time`, `user_id`, `linked_task_id`, `recurrence_parent_id`.
251
252	### weekly_reviews
253	Weekly review completion tracking. Migration 018.
254
255	\| Column \| Type \| Notes \|
256	\|--------\|------\|-------\|
257	\| `id` \| TEXT PK \| \|
258	\| `user_id` \| TEXT FK \| -> users \|
259	\| `week_start_date` \| TEXT \| ISO week Monday (YYYY-MM-DD) \|
260	\| `completed_at` \| TEXT \| `datetime('now')` \|
261	\| `notes` \| TEXT \| Default '' \|
262	\| `vacation_days` \| TEXT \| Comma-separated day indices. Migration 027 \|
263
264	Unique: `(user_id, week_start_date)`. Index: `(user_id, week_start_date)`.
265
266	### saved_views
267	Persisted filter/sort configurations for quick access. Migration 015.
268
269	\| Column \| Type \| Notes \|
270	\|--------\|------\|-------\|
271	\| `id` \| TEXT PK \| \|
272	\| `user_id` \| TEXT FK \| -> users \|
273	\| `name` \| TEXT \| \|
274	\| `view_type` \| TEXT \| 'tasks', 'emails', 'events' \|
275	\| `filters` \| TEXT \| JSON object with filter criteria \|
276	\| `sort_by` \| TEXT \| Sort field \|
277	\| `sort_order` \| TEXT \| Default 'asc' \|
278	\| `is_pinned` \| INTEGER \| Boolean, default 0 \|
279	\| `position` \| INTEGER \| Sidebar order, default 0 \|
280	\| `created_at` \| TEXT \| \|
281	\| `updated_at` \| TEXT \| \|
282
283	Indexes: `user_id`. Partial index: `(user_id, is_pinned) WHERE is_pinned = 1`.
284
285	---
286
287	## Search
288
289	FTS5 virtual tables with automatic sync triggers (INSERT/UPDATE/DELETE). Created in migration 011, contacts FTS in migration 023.
290
291	\| FTS Table \| Source Table \| Indexed Columns \|
292	\|-----------\|-------------\|-----------------\|
293	\| `emails_fts` \| emails \| subject, body, from_address, to_address \|
294	\| `tasks_fts` \| tasks \| description, tags \|
295	\| `projects_fts` \| projects \| name, description \|
296	\| `events_fts` \| events \| title, description, location \|
297	\| `contacts_fts` \| contacts \| display_name, nickname, company, notes, tags \|
298
299	All FTS tables carry `id` and `user_id` as UNINDEXED columns for filtering. Each has three triggers (AFTER INSERT, AFTER DELETE, AFTER UPDATE) to keep the index in sync with the source table.
300
301	---
302
303	## Sync
304
305	### sync_state
306	Key-value store for SyncKit configuration and state. Migration 030.
307
308	Seeded keys: `device_id`, `pull_cursor`, `auto_sync_enabled`, `sync_interval_minutes`, `applying_remote`, `last_sync_at`, `initial_snapshot_done`.
309
310	### sync_changelog
311	Local change log captured by triggers, pushed to MNW server. Migration 030.
312
313	\| Column \| Type \| Notes \|
314	\|--------\|------\|-------\|
315	\| `id` \| INTEGER PK \| AUTOINCREMENT \|
316	\| `table_name` \| TEXT \| Source table name \|
317	\| `op` \| TEXT \| 'INSERT', 'UPDATE', 'DELETE' \|
318	\| `row_id` \| TEXT \| PK of changed row \|
319	\| `timestamp` \| TEXT \| Millisecond-precision UTC \|
320	\| `data` \| TEXT \| JSON snapshot of row (NULL for DELETE) \|
321	\| `pushed` \| INTEGER \| Boolean, default 0 \|
322
323	Partial index: `pushed WHERE pushed = 0`.
324
325	Sync triggers exist on 11 tables: projects, tasks, events, contacts, contact_emails, contact_phones, contact_social_handles, contact_custom_fields, annotations, subtasks, milestones. Each trigger checks the `applying_remote` flag to prevent echo during pull. Email accounts sync config only (credentials excluded per migration 033).
326
327	---
328
329	## Config
330
331	### users
332	Local user identity.
333
334	\| Column \| Type \| Notes \|
335	\|--------\|------\|-------\|
336	\| `id` \| TEXT PK \| \|
337	\| `email` \| TEXT \| Unique \|
338	\| `password_hash` \| TEXT \| \|
339	\| `display_name` \| TEXT \| \|
340	\| `created_at` \| TEXT \| `datetime('now')` \|
341	\| `last_login_at` \| TEXT \| \|
342
343	Index: `email`.
344
345	### llm_settings (unused - tables retained for migration compatibility)
346	Per-user LLM provider configuration. Migration 014.
347
348	\| Column \| Type \| Notes \|
349	\|--------\|------\|-------\|
350	\| `id` \| TEXT PK \| \|
351	\| `user_id` \| TEXT FK \| -> users (CASCADE). Unique \|
352	\| `provider_type` \| TEXT \| 'ollama' or 'openai_compatible', default 'ollama' \|
353	\| `base_url` \| TEXT \| Default 'http://localhost:11434' \|
354	\| `api_key` \| TEXT \| Nullable (for OpenAI-compatible) \|
355	\| `model_name` \| TEXT \| Default 'llama3.2' \|
356	\| `timeout_ms` \| INTEGER \| Default 30000 \|
357	\| `max_tokens` \| INTEGER \| Default 256 \|
358	\| `temperature` \| REAL \| Default 0.7 \|
359	\| `is_enabled` \| INTEGER \| Boolean, default 1 \|
360	\| `created_at` \| TEXT \| \|
361	\| `updated_at` \| TEXT \| \|
362
363	### llm_cache (unused - tables retained for migration compatibility)
364	Prompt-response cache with date-aware invalidation. Migration 014.
365
366	\| Column \| Type \| Notes \|
367	\|--------\|------\|-------\|
368	\| `id` \| TEXT PK \| \|
369	\| `user_id` \| TEXT FK \| -> users (CASCADE) \|
370	\| `prompt_hash` \| TEXT \| \|
371	\| `context_date` \| TEXT \| For date-dependent invalidation \|
372	\| `response` \| TEXT \| \|
373	\| `created_at` \| TEXT \| \|
374	\| `expires_at` \| TEXT \| \|
375
376	Unique: `(user_id, prompt_hash, context_date)`. Index: `(user_id, prompt_hash, context_date)`.
377
378	### backup_settings
379	Automated local backup configuration. Migration 019, revised migration 029.
380
381	\| Column \| Type \| Notes \|
382	\|--------\|------\|-------\|
383	\| `id` \| TEXT PK \| \|
384	\| `user_id` \| TEXT FK \| -> users. Unique \|
385	\| `auto_backup_enabled` \| INTEGER \| Boolean, default 1 \|
386	\| `backup_frequency_minutes` \| INTEGER \| Default 15 (was hours, converted in 029) \|
387	\| `max_backups_to_keep` \| INTEGER \| Default 1 (was 7, changed in 029) \|
388	\| `last_backup_at` \| TEXT \| \|
389	\| `created_at` \| TEXT \| \|
390	\| `updated_at` \| TEXT \| \|
391
392	---
393
394	## Design Patterns
395
396	- Text UUIDs for all PKs: All `id` columns are TEXT containing UUIDs, not INTEGER AUTOINCREMENT (except `sync_changelog.id`)
397	- Datetimes as TEXT: All timestamps stored as ISO 8601 strings via `datetime('now')` or `strftime('%Y-%m-%dT%H:%M:%fZ', 'now')`
398	- JSON in TEXT columns: `tasks.tags`, `saved_views.filters`, and `sync_changelog.data` store JSON as TEXT strings
399	- Boolean as INTEGER: SQLite has no native boolean; 0/1 INTEGER used throughout
400	- FTS5 content tables: Virtual tables with `content=` pointing to source table; triggers keep indexes in sync
401	- Sync echo prevention: All sync triggers check `sync_state.applying_remote` to avoid logging changes pulled from the server
402	- Credential isolation: Email account sync triggers exclude passwords and OAuth tokens (migration 033)
403	- Recurrence via parent links: Both tasks and events use `recurrence_parent_id` self-referential FK for recurring series
404	- Multi-entity linking: Tasks can link to emails (`source_email_id`), contacts (`contact_id`), milestones (`milestone_id`), and events (`linked_task_id` on events)
405	- Subtask-to-task links: Subtasks can reference another full task via `linked_task_id` for multi-phase work
406
407	## Key Indexes
408
409	- FTS5 on emails (subject, body, from, to), tasks (description, tags), projects (name, description), events (title, description, location), contacts (display_name, nickname, company, notes, tags)
410	- Partial on tasks (`waiting_for_response WHERE = 1`, `is_focus WHERE = 1`, `completed_at WHERE NOT NULL`), emails (`waiting_for_response WHERE = 1`, `thread_id WHERE NOT NULL`, `in_reply_to WHERE NOT NULL`), saved_views (`is_pinned WHERE = 1`)
411	- Composite on emails `(email_account_id, imap_uid, source_folder)` for IMAP sync, contacts `(user_id, display_name)`, weekly_reviews `(user_id, week_start_date)`
412	- Sort on tasks `(urgency DESC)`, emails `(received_at DESC)`
413
414	## Schema Evolution
415
416	Major milestones across 50 migrations:
417
418	\| Migration \| Change \|
419	\|-----------\|--------\|
420	\| 001 \| Initial schema (projects, tasks, annotations, events, emails) \|
421	\| 002 \| Users table, user_id FK on all data tables \|
422	\| 003 \| Task recurrence parent links \|
423	\| 004 \| Email accounts (IMAP/SMTP config), email message tracking \|
424	\| 007 \| Subtasks (checkbox items on tasks) \|
425	\| 008 \| Task-event linking, event recurrence \|
426	\| 009 \| Email-to-task linking \|
427	\| 010 \| Snooze/defer for tasks and emails \|
428	\| 011 \| FTS5 full-text search (emails, tasks, projects, events) \|
429	\| 012 \| Follow-up / waiting-for tracking \|
430	\| 013 \| Time blocking (scheduled_start, scheduled_duration) \|
431	\| 014 \| LLM settings and prompt cache \|
432	\| 015 \| Saved views / filters \|
433	\| 016 \| Email threading (in_reply_to, thread_id) \|
434	\| 017 \| OAuth2/JMAP support for email accounts (Fastmail) \|
435	\| 018 \| Weekly reviews, task focus mode \|
436	\| 019 \| Backup settings \|
437	\| 022 \| Subtask-to-task links (multi-phase work) \|
438	\| 023 \| Contacts (people, emails, phones, social handles, FTS) \|
439	\| 024 \| Contact custom fields \|
440	\| 025 \| Contact linking to tasks and events \|
441	\| 026 \| Time block types on events \|
442	\| 027 \| Vacation days on weekly reviews \|
443	\| 028 \| Milestones (scope boundaries within projects) \|
444	\| 029 \| Backup frequency: hours to minutes \|
445	\| 030 \| SyncKit changelog + state + 33 sync triggers (11 tables) \|
446	\| 031 \| completed_at on tasks, updated sync triggers \|
447	\| 032 \| Email account sync triggers \|
448	\| 033 \| Credential isolation (exclude passwords/tokens from sync) \|
449