mirror of
https://github.com/memohai/Memoh.git
synced 2026-04-25 07:00:48 +09:00
BBQ
cc5f00355f
* feat: add email service with multi-adapter support Implement a full-stack email service with global provider management, per-bot bindings with granular read/write permissions, outbox audit storage, and MCP tool integration for direct mailbox access. Backend: - Email providers: CRUD with dynamic config schema (generic SMTP/IMAP, Mailgun) - Generic adapter: go-mail (SMTP) + go-imap/v2 (IMAP IDLE real-time push via UnilateralDataHandler + UID-based tracking + periodic check fallback) - Mailgun adapter: mailgun-go/v5 with dual inbound mode (webhook + poll) - Bot email bindings: per-bot provider binding with independent r/w permissions - Outbox: outbound email audit log with status tracking - Trigger: inbound emails push notification to bot_inbox (from/subject only, LLM reads full content on demand via MCP tools) - MailboxReader interface: on-demand IMAP queries for listing/reading emails - MCP tools: email_accounts, email_send, email_list (paginated mailbox), email_read (by UID) — all with multi-binding and provider_id selection - Webhook: /email/mailgun/webhook/:config_id (JWT-skipped, signature-verified) - DB migration: 0019_add_email (email_providers, bot_email_bindings, email_outbox) Frontend: - Email Providers page: /email-providers with MasterDetailSidebarLayout - Dynamic config form rendered from ordered provider meta schema with i18n keys - Bot detail: Email tab with bindings management + outbox audit table - Sidebar navigation entry - Full i18n support (en + zh) - Auto-generated SDK from Swagger Closes #17 * feat(email): trigger bot conversation immediately on inbound email Instead of only storing an inbox item and waiting for the next chat, the email trigger now proactively invokes the conversation resolver so the bot processes new emails right away — aligned with the schedule/heartbeat trigger pattern. * fix: lint --------- Co-authored-by: Acbox <acbox0328@gmail.com>
50 lines
2.1 KiB
SQL
50 lines
2.1 KiB
SQL
-- 0019_add_email
|
|
-- Add email providers, outbox, and bot email bindings tables.
|
|
|
|
CREATE TABLE IF NOT EXISTS email_providers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
provider TEXT NOT NULL,
|
|
config JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT email_providers_name_unique UNIQUE (name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS bot_email_bindings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
bot_id UUID NOT NULL REFERENCES bots(id) ON DELETE CASCADE,
|
|
email_provider_id UUID NOT NULL REFERENCES email_providers(id) ON DELETE CASCADE,
|
|
email_address TEXT NOT NULL,
|
|
can_read BOOLEAN NOT NULL DEFAULT TRUE,
|
|
can_write BOOLEAN NOT NULL DEFAULT TRUE,
|
|
can_delete BOOLEAN NOT NULL DEFAULT FALSE,
|
|
config JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT bot_email_bindings_unique UNIQUE (bot_id, email_provider_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_bot_email_bindings_bot_id ON bot_email_bindings(bot_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bot_email_bindings_provider_id ON bot_email_bindings(email_provider_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS email_outbox (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
provider_id UUID NOT NULL REFERENCES email_providers(id) ON DELETE CASCADE,
|
|
bot_id UUID NOT NULL REFERENCES bots(id) ON DELETE CASCADE,
|
|
message_id TEXT NOT NULL DEFAULT '',
|
|
from_address TEXT NOT NULL DEFAULT '',
|
|
to_addresses JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
subject TEXT NOT NULL DEFAULT '',
|
|
body_text TEXT NOT NULL DEFAULT '',
|
|
body_html TEXT NOT NULL DEFAULT '',
|
|
attachments JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed')),
|
|
error TEXT NOT NULL DEFAULT '',
|
|
sent_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_outbox_provider_id ON email_outbox(provider_id);
|
|
CREATE INDEX IF NOT EXISTS idx_email_outbox_bot_id ON email_outbox(bot_id, created_at DESC);
|