Inboxintel/docs/discovery/multi-provider/03-database-design.md

# 03 — Database Design (Part 6)

Schema for a **small-team, self-hosted, one-org** platform: multi-provider accounts per
user, a normalised email store scaling to **millions of messages**, settings, feature
flags, and audit. PostgreSQL + EF Core (+ pgvector for the blueprint's semantic tier).

## Entity map
```
users ─┬─< accounts ─┬─< provider_tokens (1:1, encrypted)
       │             ├─< email_threads ─< email_messages ─┬─< attachments
       │             │                                    └─< message_labels >─ labels
       │             └─ sync_state (cursor)
       ├─< user_settings (1:1)
       └─< audit_logs (actor)
system_settings (singleton)      feature_flags
```

## Tables

### Identity & access
- **`users`** — the app identity (from OAuth).
  `id (uuid pk) · primary_email (citext, unique) · display_name · avatar_url · role (enum: Admin|Member) · status (enum: Active|Suspended) · created_at · last_login_at`
  *First-ever user is bootstrapped as **Admin** (see [05](05-admin-system.md)).*
- **`accounts`** — a connected mailbox **and** a login identity (OAuth-is-login).
  `id (uuid pk) · user_id (fk) · provider (enum: Google|Microsoft|Imap) · provider_account_id (text, the OAuth 'sub' — immutable) · email (citext) · display_name · is_login_identity (bool) · status (enum: Active|ReauthNeeded|Disabled) · scopes (text[]) · added_at · last_sync_at`
  **Unique:** `(provider, provider_account_id)` → resolves an OAuth login to exactly one account→user; prevents the same mailbox linking twice.
- **`provider_tokens`** — 1:1 with `accounts`, **encrypted at rest** (Data Protection API).
  `account_id (pk/fk) · access_token_enc (bytea) · refresh_token_enc (bytea) · expires_at_utc · token_type · rotated_at`
  *Never logged; see [06](06-security-model.md).*
- **`sessions`** — server-side app sessions (opaque cookie).
  `id · user_id · created_at · expires_at · ip · user_agent · revoked_at` (supports "sign out everywhere" + admin revoke).

### Email (normalised, provider-agnostic)
- **`email_threads`** — per account.
  `id (uuid pk) · account_id (fk) · user_id (denorm) · provider_thread_id · subject · participants (jsonb) · message_count · last_message_at`
  **Unique:** `(account_id, provider_thread_id)`.
- **`email_messages`** — the big table.
  `id (uuid pk) · account_id (fk) · user_id (denorm) · thread_id (fk) · provider_message_id · sender_id (fk) · subject · snippet · body_text · sent_at_utc · received_at_utc · size_bytes · flags (unread/starred/important/in_inbox/trashed as bits or bools) · has_attachments · category (enum) · has_list_unsubscribe · supports_one_click_unsub · search_vector (tsvector, generated, weighted) · embedding (vector(768), nullable) · created_at`
  **Unique:** `(account_id, provider_message_id)` (idempotent upsert).
- **`labels`** (`id · account_id · provider_label_id · name · type`) + **`message_labels`** (`message_id · label_id`, pk both).
- **`attachments`** (`id · message_id · filename · mime · size · provider_attachment_id · content_text nullable` for future OCR/search).
- **`senders`** / **`domains`** (existing) — kept, scoped per user (or global with per-user stats materialised in `sender_importance`).

### Settings, flags, audit
- **`user_settings`** — 1:1 with `users`.
  `user_id (pk) · theme (enum: system|light|dark) · inbox_layout (jsonb) · notifications (jsonb) · ai_prefs (jsonb) · provider_prefs (jsonb) · updated_at`
- **`system_settings`** — singleton (org-wide, admin-managed).
  `id (const) · maintenance_mode (bool) · default_theme · registration_open (bool) · updated_by · updated_at` (+ arbitrary `values jsonb` for growth).
- **`feature_flags`** — the flag system (drives AI gating).
  `key (pk text) · enabled (bool) · scope (enum: SystemOnly|UserOverridable) · description · rollout (jsonb, e.g. per-role) · updated_by · updated_at`
  Seeded flags: `ai.enabled`, `ai.semantic_search`, `ai.ask_inbox`, `provider.google`, `provider.microsoft`, `provider.imap`, `maintenance.readonly`.
- **`audit_logs`** — admin + security events.
  `id (bigserial) · actor_user_id (fk, nullable for system) · action (text) · target_type · target_id · metadata (jsonb) · ip · created_at`
  Append-only; indexed on `(created_at)`, `(actor_user_id)`, `(action)`.

## Indexing & performance
| Index | Column(s) | For |
|-------|-----------|-----|
| GIN | `email_messages.search_vector` | FTS |
| GIN `pg_trgm` | sender/subject | fuzzy (fixes non-sargable `.Contains`) |
| HNSW | `email_messages.embedding` | semantic k-NN |
| btree | `(user_id, sent_at_utc desc)`, `(account_id, sent_at_utc desc)`, `(thread_id)` | keyset pagination, scoping, threading |
| unique | `(account_id, provider_message_id)`, `(provider, provider_account_id)` | idempotency, login resolution |

## Scalability (millions of emails, multi-account, incremental)
- **Multi-account** = first-class via `accounts`; every email carries `account_id` + denormalised `user_id` (so cross-account per-user search is a single indexed scan).
- **Millions of rows:** keyset (cursor) pagination, top-N-by-score ranking, `pg_trgm`/GIN/HNSW indexes. If a single user exceeds ~1–2M messages, **list-partition `email_messages` by `account_id`** (or hash by `user_id`).
- **Incremental sync:** per-account `sync_state` cursor (`historyId` / `deltaLink` / `uidvalidity+modseq`) → only deltas fetched; idempotent upserts on the unique key.
- **Idempotency & resumability:** all sync writes keyed on `(account_id, provider_message_id)`; cursor advanced atomically with the batch.

## RBAC in the schema
Small-team model = a `role` column on `users` (`Admin|Member`) — no separate roles/permissions tables yet. **Extensible** to `roles`/`permissions`/`org_id` if this ever grows to multi-tenant, without reshaping the email tables.

## Migration from today (see full [Migration Guide](12-migration-guide.md))
1. Create `users` from existing OAuth identity; set first user = **Admin**.
2. Create one `Google` **`account`** per existing user; move current Gmail tokens → `provider_tokens`.
3. Backfill `email_messages.account_id`/`user_id`, rename/extend from the current `Email` table; widen `search_vector`; add nullable `embedding`.
4. Add `user_settings`, `system_settings`, `feature_flags` (seed `ai.enabled` from the current `Ai:Mode`), `audit_logs`.
5. All additive + backfill; no destructive step — safe to run behind a maintenance window.