Admin Email Integration

Overview

This document describes the complete plan for wiring real IMAP email functionality into the Admin Front-End template.
It defines architecture, backend APIs, database schema, caching policy, and the background automation system (“Reggie”).

The goals:

Use IMAP/SMTP only (no Gmail/Graph APIs).
Support multiple work accounts per user.
Provide manual and scheduled fetching of mail headers.
Avoid long-lived IMAP IDLE connections.
Keep data lightweight (headers only, on-demand bodies).
Enable Reggie — the background AI process that analyses incoming mail and drafts replies.

1. Architecture Summary

Frontend (Admin Template)
   |
   |-- REST API (JSON)
   |
Backend (Node + Express)
   |-- IMAP Client (ImapFlow)
   |-- SMTP Client (Nodemailer)
   |-- Job Scheduler ("Reggie" worker)
   |-- Database (PostgreSQL or similar)

Connection Model

1 IMAP connection per (user, account) while performing operations.
Connections are pooled in-memory and auto-closed after ~2-5 min idle.
No persistent IDLE; operations are fetch-and-close.
Safe auto-reconnect before every operation.

2. Front-End Behaviour

Manual “Get Mail” Button

Triggers authoritative resync of the top N headers (e.g. 100).
Adds new messages, updates flags, removes deleted ones.
Keeps header cache in sync with server state.

“Show More” Button

Fetches older pages by UID < oldestCachedUid.
Appends to list without trimming top.

Scheduled Fetch (Reggie)

Periodic (30–120 min) light fetch:
- Only messages UID > last_seen_uid.
- Updates header cache; doesn’t handle deletions.
Used for background analysis and notifications.

Cached Folders

Persistent header caches:

Inbox
Sent
Drafts
Junk
Trash
Archive

Ad-hoc Folders

Fetched on demand; purged (or expire quickly) after view closed.

3. Data Model (Database)

3.1. IMAP Accounts

CREATE TABLE email_imap_accounts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  display_name TEXT,
  email_address TEXT,
  imap_host TEXT, imap_port INTEGER, imap_secure BOOLEAN,
  smtp_host TEXT, smtp_port INTEGER, smtp_secure BOOLEAN,
  auth_type TEXT CHECK (auth_type IN ('basic','oauth2')),
  auth_json JSONB,              -- encrypted credentials
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

3.2. Mailbox State

Tracks watermarks per mailbox.

CREATE TABLE email_mailbox_state (
  id SERIAL PRIMARY KEY,
  account_id INTEGER REFERENCES email_imap_accounts(id),
  mailbox_path TEXT,
  last_seen_uid BIGINT DEFAULT 0,
  oldest_cached_uid BIGINT,
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(account_id, mailbox_path)
);

3.3. Cached Message Headers

Lightweight index of messages (no bodies).

CREATE TABLE email_message_headers (
  id SERIAL PRIMARY KEY,
  account_id INTEGER REFERENCES email_imap_accounts(id),
  mailbox_path TEXT,
  uid BIGINT,
  msgid TEXT,
  subject TEXT,
  from_text TEXT,
  internal_date TIMESTAMPTZ,
  flags TEXT[],
  has_attachments BOOLEAN,
  size INTEGER,
  seen BOOLEAN GENERATED ALWAYS AS (
    array_position(flags, '\Seen') IS NOT NULL
  ) STORED,
  UNIQUE(account_id, mailbox_path, uid)
);

3.4. Rules for Reggie

CREATE TABLE email_rules (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  account_id INTEGER REFERENCES email_imap_accounts(id),
  mailbox_path TEXT,
  rule_name TEXT,
  include_senders TEXT[] DEFAULT '{}',
  include_keywords TEXT[] DEFAULT '{}',
  lookback_days INTEGER DEFAULT 7,
  action TEXT CHECK (action IN ('draft_suggestion','label','flag'))
          DEFAULT 'draft_suggestion',
  is_enabled BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

3.5. Reggie Job Ledger

CREATE TABLE email_jobs (
  id SERIAL PRIMARY KEY,
  user_id INTEGER,
  account_id INTEGER,
  mailbox_path TEXT,
  uid BIGINT,
  job_type TEXT CHECK (job_type IN ('analyze_and_draft')),
  status TEXT CHECK (status IN ('queued','running','done','error')) DEFAULT 'queued',
  error_text TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(account_id, mailbox_path, uid, job_type)
);

4. API Specification

All routes prefixed with /v1/email/imap.

Accounts

GET  /accounts
GET  /accounts/:id/mailboxes

Message Listing

GET  /accounts/:id/messages?mailbox=INBOX&sinceUid=12345&limit=50
GET  /accounts/:id/messages?mailbox=INBOX&olderThanUid=12000&limit=50

Response:

{
  "items": [
    {
      "uid": 13001,
      "subject": "Example subject",
      "from": "Alice <alice@work.com>",
      "date": "2025-10-05T01:23:45Z",
      "hasAttachments": true,
      "seen": false
    }
  ],
  "nextOlderThanUid": 12001,
  "newestUid": 13050
}

Manual Resync (authoritative top-N)

POST /accounts/:id/mailbox/:path/resync?limit=100

Incremental Fetch (Reggie)

POST /accounts/:id/mailbox/:path/fetch-latest
Body: { "sinceUid": 12345, "limit": 200 }

Message Detail

GET /accounts/:id/messages/:uid
GET /accounts/:id/messages/:uid/attachments/:n

Flags

PATCH /accounts/:id/messages/:uid/flags
Body: { "seen": true, "flagged": false }

Send

POST /accounts/:id/send
Body: {
  "to": ["person@work.com"],
  "cc": [],
  "bcc": [],
  "subject": "Hi",
  "html": "<p>Hello</p>",
  "text": "Hello",
  "attachments": []
}

Manual Reggie Run

POST /reggie/run
Body: { "accountId": 1, "mailbox": "INBOX", "uid": 12345, "force": true }

5. Mail Fetching Logic

Incremental (Scheduled)

Read last_seen_uid.
UID SEARCH UID > last_seen_uid.
Fetch headers for those UIDs.
Upsert into header cache.
Update watermark.

Manual Resync (Authoritative)

Get UIDNEXT.
Determine window (UIDNEXT - 5000 : *).
Fetch lightweight headers.
Sort newest first, keep top N.
Compare with cache: remove missing, update changed, insert new.
Update watermark.

Periodic Compaction

Once per day, run resync to drop deleted messages.

Paging

Fetch olderThanUid windows for "Show More".

6. Reggie: Background AI Process

Schedule

Runs every 30–120 min per user/account; staggered.

Workflow

Fetch new headers.
Apply rules (senders/keywords).
Fetch bodies for matches.
Generate summaries & drafts.
Save drafts via IMAP APPEND or DB.
Record jobs and notify users.

Manual Reggie

User clicks button → analyze single message.

Safety

Rate limit, dedupe, retry, log errors.

7. Connection Lifecycle

State	Behaviour
Active fetch	1 connection per (user, account)
Idle 2–5 min	Keep alive; NOOP every 5–10 min
Inactive	Auto-close
On next request	Auto-reconnect

8. Background Cleanup Tasks

Task	Interval	Purpose
Reggie fetch	30–120 min	New mail + analysis
Light compaction	24 h	Remove deleted headers
Cache expiry (adhoc)	30–60 min	Keep DB small
Connection sweep	1 min	Close idle sessions

9. Security Notes

Encrypt all creds.
TLS for IMAP/SMTP.
Sanitize HTML.
Never log sensitive content.
Enforce per-user ownership.

10. Front-End Hooks

Page	Function
Inbox	Fetch + render header list
Detail	Fetch + render body
Compose	Send via API

Account dropdown, folder dropdown, Reggie badge.

Buttons

Button	Endpoint
Get Mail	POST /resync
Show More	GET /messages
Reggie	POST /reggie/run

11. Caching Policy

Folder	Persistence	Description
Inbox/Sent/Drafts etc	Persistent	Compacted daily
Ad-hoc folders	Temp (≤60m)	Purged after view
Bodies	None	On-demand
Attachments	None	Stream live

12. Advantages

Predictable resource use.
Works with any IMAP server.
Integrates with Reggie AI pipeline.
Manual resync keeps state accurate.
Simple DB footprint.

13. Next Steps

Create DB migrations.
Build Express router.
Implement manualResyncTopN.
Wire front-end buttons.
Add Reggie scheduler.
Add daily compaction job.
Add settings UI.
Test multi-account & folders.

Document version: 2025-10-05
Prepared by ChatGPT (GPT-5) with Brett’s design notes.

Overview​

1. Architecture Summary​

Connection Model​

2. Front-End Behaviour​

Manual “Get Mail” Button​

“Show More” Button​

Scheduled Fetch (Reggie)​

Cached Folders​

Ad-hoc Folders​

3. Data Model (Database)​

3.1. IMAP Accounts​

3.2. Mailbox State​

3.3. Cached Message Headers​

3.4. Rules for Reggie​

3.5. Reggie Job Ledger​

4. API Specification​

Accounts​

Message Listing​

Manual Resync (authoritative top-N)​

Incremental Fetch (Reggie)​

Message Detail​

Flags​

Send​

Manual Reggie Run​

5. Mail Fetching Logic​

Incremental (Scheduled)​

Manual Resync (Authoritative)​

Periodic Compaction​

Paging​

6. Reggie: Background AI Process​

Schedule​

Workflow​

Manual Reggie​

Safety​

7. Connection Lifecycle​

8. Background Cleanup Tasks​

9. Security Notes​

10. Front-End Hooks​

Toolbar​

Buttons​

11. Caching Policy​

12. Advantages​

13. Next Steps​