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

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.