# GoingsOn Database Schema

SQLite schema reference. 50 migrations. Migrations live in `migrations/sqlite/` and auto-run on first launch.

## Domain Map

| Domain | Tables | Purpose |
|--------|--------|---------|
| Core | 5 | Projects, tasks, subtasks, annotations, milestones |
| Email | 2 | Email accounts (IMAP/JMAP), email messages |
| Contacts | 5 | People, email addresses, phones, social handles, custom fields |
| Planning | 3 | Events (calendar/time blocks), weekly reviews, saved views |
| Search | 5 | FTS5 virtual tables for emails, tasks, projects, events, contacts |
| Sync | 2 | SyncKit changelog and state (cloud sync via MNW) |
| Config | 1 | Users, backup settings |

---

## Core

### projects
Top-level organizational unit (work projects, personal areas, etc.).

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users (CASCADE). Added migration 002 |
| `name` | TEXT | NOT NULL |
| `description` | TEXT | Default '' |
| `project_type` | TEXT | Default 'Other' |
| `status` | TEXT | Default 'Active' |
| `created_at` | TEXT | `datetime('now')` |

Indexes: `status`, `project_type`, `user_id`.

### tasks
TaskWarrior-inspired task model with urgency scoring, recurrence, scheduling, snooze, and follow-up tracking.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users (CASCADE). Migration 002 |
| `project_id` | TEXT FK | -> projects (SET NULL) |
| `description` | TEXT | NOT NULL |
| `status` | TEXT | Default 'Pending' |
| `priority` | TEXT | Default 'Medium' |
| `due` | TEXT | ISO datetime, nullable |
| `tags` | TEXT | JSON array string, default '[]' |
| `urgency` | REAL | Computed score, default 0.0 |
| `recurrence` | TEXT | Default 'None' |
| `recurrence_parent_id` | TEXT FK | -> tasks (SET NULL). Migration 003 |
| `source_email_id` | TEXT FK | -> emails (SET NULL). Migration 009 |
| `snoozed_until` | TEXT | Migration 010 |
| `waiting_for_response` | INTEGER | Boolean, default 0. Migration 012 |
| `waiting_since` | TEXT | Migration 012 |
| `expected_response_date` | TEXT | Migration 012 |
| `scheduled_start` | TEXT | Migration 013 |
| `scheduled_duration` | INTEGER | Minutes. Migration 013 |
| `is_focus` | INTEGER | Boolean, default 0. Migration 018 |
| `focus_set_at` | TEXT | Migration 018 |
| `contact_id` | TEXT FK | -> contacts (SET NULL). Migration 025 |
| `milestone_id` | TEXT FK | -> milestones (SET NULL). Migration 028 |
| `completed_at` | TEXT | Backfilled from created_at. Migration 031 |
| `created_at` | TEXT | `datetime('now')` |

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`.

### subtasks
Checkbox items within a task. Can optionally link to another full task (multi-phase work).

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `task_id` | TEXT FK | -> tasks (CASCADE) |
| `text` | TEXT | NOT NULL |
| `is_completed` | INTEGER | Boolean, default 0 |
| `position` | INTEGER | Sort order, default 0 |
| `linked_task_id` | TEXT FK | -> tasks (SET NULL). Migration 022 |
| `created_at` | TEXT | `datetime('now')` |

Indexes: `task_id`, `linked_task_id`.

### annotations
Timestamped notes attached to tasks.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `task_id` | TEXT FK | -> tasks (CASCADE) |
| `timestamp` | TEXT | `datetime('now')` |
| `note` | TEXT | NOT NULL |

Index: `task_id`.

### milestones
Scope boundaries within a project. "When these tasks are done, ship it." Migration 028.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users (CASCADE) |
| `project_id` | TEXT FK | -> projects (CASCADE) |
| `name` | TEXT | NOT NULL |
| `description` | TEXT | Default '' |
| `position` | INTEGER | Sort order, default 0 |
| `target_date` | TEXT | Nullable |
| `status` | TEXT | Default 'open' |
| `created_at` | TEXT | `datetime('now')` |

Index: `project_id`. Tasks link to milestones via `tasks.milestone_id`.

---

## Email

### email_accounts
IMAP/SMTP and OAuth2/JMAP account configuration. Credentials excluded from sync (migration 033).

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users (CASCADE) |
| `account_name` | TEXT | Display name |
| `email_address` | TEXT | Unique per user |
| `imap_server` | TEXT | |
| `imap_port` | INTEGER | Default 993 |
| `smtp_server` | TEXT | |
| `smtp_port` | INTEGER | Default 587 |
| `username` | TEXT | |
| `password` | TEXT | IMAP/SMTP credential |
| `use_tls` | INTEGER | Boolean, default 1 |
| `auth_type` | TEXT | 'password' or 'oauth2_fastmail'. Migration 017 |
| `oauth2_access_token` | TEXT | Migration 017 |
| `oauth2_refresh_token` | TEXT | Migration 017 |
| `oauth2_token_expires_at` | TEXT | Migration 017 |
| `jmap_session_url` | TEXT | Fastmail JMAP. Migration 017 |
| `jmap_account_id` | TEXT | Migration 017 |
| `archive_folder_name` | TEXT | Default 'Archive'. Migration 006 |
| `sync_interval_minutes` | INTEGER | Default 15, NULL = disabled. Migration 021 |
| `last_sync_at` | TEXT | Device-local, excluded from sync |
| `created_at` | TEXT | `datetime('now')` |

Unique: `(user_id, email_address)`. Index: `user_id`.

### emails
Individual email messages synced from IMAP/JMAP.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users (CASCADE). Migration 002 |
| `project_id` | TEXT FK | -> projects (SET NULL) |
| `email_account_id` | TEXT FK | -> email_accounts (SET NULL). Migration 004 |
| `from_address` | TEXT | |
| `to_address` | TEXT | |
| `subject` | TEXT | |
| `body` | TEXT | Plain text, default '' |
| `html_body` | TEXT | Original HTML for browser view. Migration 020 |
| `message_id` | TEXT | RFC 2822 Message-ID. Migration 004 |
| `in_reply_to` | TEXT | Threading header. Migration 016 |
| `thread_id` | TEXT | Conversation grouping. Migration 016 |
| `is_read` | INTEGER | Boolean, default 0 |
| `is_archived` | INTEGER | Boolean, default 0. Migration 005 |
| `is_outgoing` | INTEGER | Boolean, default 0. Migration 004 |
| `imap_uid` | INTEGER | IMAP UID for sync. Migration 006 |
| `source_folder` | TEXT | IMAP folder. Migration 006 |
| `snoozed_until` | TEXT | Migration 010 |
| `waiting_for_response` | INTEGER | Boolean, default 0. Migration 012 |
| `waiting_since` | TEXT | Migration 012 |
| `expected_response_date` | TEXT | Migration 012 |
| `received_at` | TEXT | `datetime('now')` |

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`.

---

## Contacts

### contacts
People with structured metadata. Migration 023.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users |
| `display_name` | TEXT | NOT NULL |
| `nickname` | TEXT | |
| `company` | TEXT | |
| `title` | TEXT | Job title |
| `notes` | TEXT | Default '' |
| `tags` | TEXT | JSON array string, default '[]' |
| `birthday` | TEXT | |
| `timezone` | TEXT | |
| `created_at` | TEXT | |
| `updated_at` | TEXT | |

Indexes: `user_id`, `(user_id, display_name)`.

### contact_emails
Multiple email addresses per contact. Migration 023.

- FK `contact_id` -> contacts (CASCADE)
- `address`, `label` (default ''), `is_primary` (boolean)
- Indexes: `contact_id`, `address`

### contact_phones
Multiple phone numbers per contact. Migration 023.

- FK `contact_id` -> contacts (CASCADE)
- `number`, `label` (default ''), `is_primary` (boolean)
- Index: `contact_id`

### contact_social_handles
Social platform handles per contact. Migration 023.

- FK `contact_id` -> contacts (CASCADE)
- `platform`, `handle`, `url` (optional)
- Index: `contact_id`

### contact_custom_fields
Arbitrary label/value pairs with optional URL. Migration 024.

- FK `contact_id` -> contacts (CASCADE)
- `label`, `value`, `url` (optional)
- Index: `contact_id`

---

## Planning

### events
Calendar events and time blocks. Supports recurrence and task/contact linking.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users (CASCADE). Migration 002 |
| `project_id` | TEXT FK | -> projects (SET NULL) |
| `title` | TEXT | NOT NULL |
| `description` | TEXT | Default '' |
| `start_time` | TEXT | ISO datetime |
| `end_time` | TEXT | Nullable |
| `location` | TEXT | |
| `linked_task_id` | TEXT FK | -> tasks (SET NULL). Migration 008 |
| `recurrence` | TEXT | Default 'None'. Migration 008 |
| `recurrence_parent_id` | TEXT FK | -> events (SET NULL). Migration 008 |
| `contact_id` | TEXT FK | -> contacts (SET NULL). Migration 025 |
| `block_type` | TEXT | Time block category. Migration 026 |

Indexes: `project_id`, `start_time`, `user_id`, `linked_task_id`, `recurrence_parent_id`.

### weekly_reviews
Weekly review completion tracking. Migration 018.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users |
| `week_start_date` | TEXT | ISO week Monday (YYYY-MM-DD) |
| `completed_at` | TEXT | `datetime('now')` |
| `notes` | TEXT | Default '' |
| `vacation_days` | TEXT | Comma-separated day indices. Migration 027 |

Unique: `(user_id, week_start_date)`. Index: `(user_id, week_start_date)`.

### saved_views
Persisted filter/sort configurations for quick access. Migration 015.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users |
| `name` | TEXT | |
| `view_type` | TEXT | 'tasks', 'emails', 'events' |
| `filters` | TEXT | JSON object with filter criteria |
| `sort_by` | TEXT | Sort field |
| `sort_order` | TEXT | Default 'asc' |
| `is_pinned` | INTEGER | Boolean, default 0 |
| `position` | INTEGER | Sidebar order, default 0 |
| `created_at` | TEXT | |
| `updated_at` | TEXT | |

Indexes: `user_id`. Partial index: `(user_id, is_pinned) WHERE is_pinned = 1`.

---

## Search

FTS5 virtual tables with automatic sync triggers (INSERT/UPDATE/DELETE). Created in migration 011, contacts FTS in migration 023.

| FTS Table | Source Table | Indexed Columns |
|-----------|-------------|-----------------|
| `emails_fts` | emails | subject, body, from_address, to_address |
| `tasks_fts` | tasks | description, tags |
| `projects_fts` | projects | name, description |
| `events_fts` | events | title, description, location |
| `contacts_fts` | contacts | display_name, nickname, company, notes, tags |

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.

---

## Sync

### sync_state
Key-value store for SyncKit configuration and state. Migration 030.

Seeded keys: `device_id`, `pull_cursor`, `auto_sync_enabled`, `sync_interval_minutes`, `applying_remote`, `last_sync_at`, `initial_snapshot_done`.

### sync_changelog
Local change log captured by triggers, pushed to MNW server. Migration 030.

| Column | Type | Notes |
|--------|------|-------|
| `id` | INTEGER PK | AUTOINCREMENT |
| `table_name` | TEXT | Source table name |
| `op` | TEXT | 'INSERT', 'UPDATE', 'DELETE' |
| `row_id` | TEXT | PK of changed row |
| `timestamp` | TEXT | Millisecond-precision UTC |
| `data` | TEXT | JSON snapshot of row (NULL for DELETE) |
| `pushed` | INTEGER | Boolean, default 0 |

Partial index: `pushed WHERE pushed = 0`.

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).

---

## Config

### users
Local user identity.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `email` | TEXT | Unique |
| `password_hash` | TEXT | |
| `display_name` | TEXT | |
| `created_at` | TEXT | `datetime('now')` |
| `last_login_at` | TEXT | |

Index: `email`.

### llm_settings (unused - tables retained for migration compatibility)
Per-user LLM provider configuration. Migration 014.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users (CASCADE). Unique |
| `provider_type` | TEXT | 'ollama' or 'openai_compatible', default 'ollama' |
| `base_url` | TEXT | Default 'http://localhost:11434' |
| `api_key` | TEXT | Nullable (for OpenAI-compatible) |
| `model_name` | TEXT | Default 'llama3.2' |
| `timeout_ms` | INTEGER | Default 30000 |
| `max_tokens` | INTEGER | Default 256 |
| `temperature` | REAL | Default 0.7 |
| `is_enabled` | INTEGER | Boolean, default 1 |
| `created_at` | TEXT | |
| `updated_at` | TEXT | |

### llm_cache (unused - tables retained for migration compatibility)
Prompt-response cache with date-aware invalidation. Migration 014.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users (CASCADE) |
| `prompt_hash` | TEXT | |
| `context_date` | TEXT | For date-dependent invalidation |
| `response` | TEXT | |
| `created_at` | TEXT | |
| `expires_at` | TEXT | |

Unique: `(user_id, prompt_hash, context_date)`. Index: `(user_id, prompt_hash, context_date)`.

### backup_settings
Automated local backup configuration. Migration 019, revised migration 029.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `user_id` | TEXT FK | -> users. Unique |
| `auto_backup_enabled` | INTEGER | Boolean, default 1 |
| `backup_frequency_minutes` | INTEGER | Default 15 (was hours, converted in 029) |
| `max_backups_to_keep` | INTEGER | Default 1 (was 7, changed in 029) |
| `last_backup_at` | TEXT | |
| `created_at` | TEXT | |
| `updated_at` | TEXT | |

---

## Design Patterns

- **Text UUIDs for all PKs:** All `id` columns are TEXT containing UUIDs, not INTEGER AUTOINCREMENT (except `sync_changelog.id`)
- **Datetimes as TEXT:** All timestamps stored as ISO 8601 strings via `datetime('now')` or `strftime('%Y-%m-%dT%H:%M:%fZ', 'now')`
- **JSON in TEXT columns:** `tasks.tags`, `saved_views.filters`, and `sync_changelog.data` store JSON as TEXT strings
- **Boolean as INTEGER:** SQLite has no native boolean; 0/1 INTEGER used throughout
- **FTS5 content tables:** Virtual tables with `content=` pointing to source table; triggers keep indexes in sync
- **Sync echo prevention:** All sync triggers check `sync_state.applying_remote` to avoid logging changes pulled from the server
- **Credential isolation:** Email account sync triggers exclude passwords and OAuth tokens (migration 033)
- **Recurrence via parent links:** Both tasks and events use `recurrence_parent_id` self-referential FK for recurring series
- **Multi-entity linking:** Tasks can link to emails (`source_email_id`), contacts (`contact_id`), milestones (`milestone_id`), and events (`linked_task_id` on events)
- **Subtask-to-task links:** Subtasks can reference another full task via `linked_task_id` for multi-phase work

## Key Indexes

- **FTS5** on emails (subject, body, from, to), tasks (description, tags), projects (name, description), events (title, description, location), contacts (display_name, nickname, company, notes, tags)
- **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`)
- **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)`
- **Sort** on tasks `(urgency DESC)`, emails `(received_at DESC)`

## Schema Evolution

Major milestones across 50 migrations:

| Migration | Change |
|-----------|--------|
| 001 | Initial schema (projects, tasks, annotations, events, emails) |
| 002 | Users table, user_id FK on all data tables |
| 003 | Task recurrence parent links |
| 004 | Email accounts (IMAP/SMTP config), email message tracking |
| 007 | Subtasks (checkbox items on tasks) |
| 008 | Task-event linking, event recurrence |
| 009 | Email-to-task linking |
| 010 | Snooze/defer for tasks and emails |
| 011 | FTS5 full-text search (emails, tasks, projects, events) |
| 012 | Follow-up / waiting-for tracking |
| 013 | Time blocking (scheduled_start, scheduled_duration) |
| 014 | LLM settings and prompt cache |
| 015 | Saved views / filters |
| 016 | Email threading (in_reply_to, thread_id) |
| 017 | OAuth2/JMAP support for email accounts (Fastmail) |
| 018 | Weekly reviews, task focus mode |
| 019 | Backup settings |
| 022 | Subtask-to-task links (multi-phase work) |
| 023 | Contacts (people, emails, phones, social handles, FTS) |
| 024 | Contact custom fields |
| 025 | Contact linking to tasks and events |
| 026 | Time block types on events |
| 027 | Vacation days on weekly reviews |
| 028 | Milestones (scope boundaries within projects) |
| 029 | Backup frequency: hours to minutes |
| 030 | SyncKit changelog + state + 33 sync triggers (11 tables) |
| 031 | completed_at on tasks, updated sync triggers |
| 032 | Email account sync triggers |
| 033 | Credential isolation (exclude passwords/tokens from sync) |