Email Order Processing Integration (Provider-Agnostic)

May contain stale references

Audited 2026-09-17. The provider-agnostic apps/email-processor/ Hono service described below was never built: inbound email is a Cloudflare Email Worker at apps/email-worker/, and there are no SendGrid/Mailgun adapters. The upload contract in the diagrams is also out of date. The worker posts source: 'external', not 'email', plus an organizationId, and signs {filename, connectionId, organizationId} with X-Inbound-Email-Signature (source is not covered by the HMAC). It then calls POST /api/webhook/inbound-email to link the document. See Inbound email: shared triage + intake contract for the live contracts. The alias, verification and inbox sections are still broadly accurate.

Feature Overview

Email Order Processor is an automated order intake system that allows ERP-Unlocked customers to submit purchase orders by simply forwarding emails to a dedicated address. Using email subaddressing (e.g., orders+genfit@erpunlocked.com), the system automatically routes orders to the correct organization and user, processing both attachments (PDF, CSV, Excel, XLSX) and email body content through the existing order processing pipeline.

Key Benefits

• Frictionless Order Submission: Customers forward orders directly from their inbox
• Zero UI Required: No need to log into webapp to submit orders
• Automatic Routing: Subaddressing ensures orders reach the correct organization and user
• Flexible Input: Supports attachments AND email body text (AI-powered extraction)
• Security: Only verified aliases are processed, preventing spam/abuse
• Multi-Format Support: Handles PDF, CSV, Excel, XLSX attachments + structured/unstructured email body text
• Provider-Agnostic: Extensible architecture supporting Gmail, SendGrid, Mailgun, Outlook, etc.
• Auto-Generated Aliases: Automatic alias creation for organizations and users
• Email Verification: Secure magic link verification before aliases become active
• Dual Inbox Views: Organization-wide inbox + personal user inbox

Technical Architecture

The system consists of six main components:

1. Email Processor App (apps/email-processor/) - Hono-based service with provider adapters
2. Database Schema - Enhanced email_order_aliases table with verification and auto-generation
3. Admin Panel - Webapp dashboard for alias management, verification, and testing (org admins only)
4. Organization Inbox - View all orders received via email for the entire organization
5. User Inbox - Personal inbox showing only orders assigned to the authenticated user
6. Webhook Handlers - Provider-specific adapters (Gmail, SendGrid, Mailgun, etc.)

---

High-Level Flow Diagram

flowchart TD
    A[Customer Forwards Email] -->|To: orders+genfit'@'erpunlocked.com| B[Email Provider<br/>Gmail/SendGrid/Mailgun]
    B -->|Webhook/Push| C[Email Processor App<br/>Provider Adapters]
    C -->|Parse Subaddress| D{Lookup Alias<br/>+ Check Verified}
    D -->|Not Found/Unverified| E[Ignore Email]
    D -->|Found & Verified| F{Has Attachment?}
    F -->|Yes| G[Process Attachment<br/>PDF/CSV/Excel/XLSX]
    F -->|No| H[Parse Email Body<br/>AI Extraction]
    G -->|Success| I[Call POST /api/pdf-documents]
    G -->|Fail| H
    H -->|Success| I
    H -->|Fail| E
    I -->|source='external'<br/>userId from alias| J[Order Created in Database]
    J -->|Update Inboxes| K[Organization Inbox<br/>+ User Inbox]

    L[Org Admin] -->|Manages Aliases| M[Webapp Admin Panel]
    M -->|CRUD + Verify| N[(email_order_aliases)]

    O[New Org/User Created] -->|Auto-generate| N
    N -->|Send Verification| P[Magic Link Email]
    P -->|Click Link| Q[Alias Verified]

    style C fill:#f9f,stroke:#333,stroke-width:2px
    style M fill:#bbf,stroke:#333,stroke-width:2px
    style I fill:#bfb,stroke:#333,stroke-width:2px
    style K fill:#ffd,stroke:#333,stroke-width:2px

---

Detailed Technical Flow Diagram

sequenceDiagram
    participant Customer
    participant EmailProvider as Email Provider<br/>(Gmail/SendGrid/Mailgun)
    participant EmailApp as Email Processor<br/>(Hono App)
    participant DB as PostgreSQL<br/>(email_order_aliases)
    participant UploadAPI as POST /api/pdf-documents<br/>(Upload Endpoint)
    participant OrderPipeline as Order Processing<br/>Pipeline
    participant Webapp as Webapp Dashboard<br/>(Admin Panel)
    participant OrgAdmin as Organization Admin
    participant User as User (Email Recipient)

    %% Email Processing Flow
    rect rgb(240, 248, 255)
        Note over Customer,OrderPipeline: Email Order Submission Flow
        Customer->>EmailProvider: Forward email to<br/>orders+genfit@erpunlocked.com
        EmailProvider->>EmailApp: POST /webhook/{provider}<br/>(message notification)

        EmailApp->>EmailProvider: GET message details<br/>(Provider API)
        EmailProvider-->>EmailApp: Email metadata + attachments + body

        EmailApp->>EmailApp: Parse "To" field<br/>Extract subaddress: "genfit"

        EmailApp->>DB: SELECT * FROM email_order_aliases<br/>WHERE subaddress = 'genfit' AND verified = true

        alt Alias Not Found/Unverified
            DB-->>EmailApp: No records or verified = false
            EmailApp->>EmailApp: Log ignored email
            EmailApp-->>EmailProvider: 200 OK (processed)
        else Alias Found & Verified
            DB-->>EmailApp: {subaddress, user_id, org_id,<br/>erp_connection_id, alias_type}

            EmailApp->>EmailApp: Check for valid attachments<br/>(PDF/CSV/Excel/XLSX)

            alt Has Valid Attachment
                EmailApp->>EmailProvider: Download attachment
                EmailProvider-->>EmailApp: File binary data
                EmailApp->>EmailApp: Process attachment
            else No Valid Attachment
                EmailApp->>EmailApp: Parse email body<br/>AI extraction from text/HTML
            end

            EmailApp->>UploadAPI: POST /api/pdf-documents<br/>FormData: {file/body, connectionId,<br/>source: 'email', userId}

            UploadAPI->>OrderPipeline: Process order document
            OrderPipeline-->>UploadAPI: {orderId, status}
            UploadAPI-->>EmailApp: 201 Created

            EmailApp->>DB: Log successful processing<br/>(audit log)
            EmailApp-->>EmailProvider: 200 OK (processed)
        end
    end

    %% Admin Panel Flow
    rect rgb(255, 250, 240)
        Note over OrgAdmin,DB: Email Alias Management Flow
        OrgAdmin->>Webapp: Navigate to /dashboard/email-aliases
        Webapp->>Webapp: Check auth (Clerk)<br/>Verify org admin role

        Webapp->>DB: SELECT * FROM email_order_aliases<br/>WHERE clerk_organization_id = $orgId
        DB-->>Webapp: List of aliases with verification status
        Webapp-->>OrgAdmin: Display aliases table

        OrgAdmin->>Webapp: Send verification email<br/>for alias "genfit"
        Webapp->>Webapp: Generate magic link token<br/>expires in 24h
        Webapp->>User: Send verification email<br/>to user's email address
        User-->>Webapp: Click magic link
        Webapp->>DB: UPDATE email_order_aliases<br/>SET verified = true, verified_at = NOW()
        DB-->>Webapp: Success
        Webapp-->>User: Alias verified successfully<br/>orders+genfit@erpunlocked.com is now active
    end

    %% Auto-generation Flow
    rect rgb(240, 255, 240)
        Note over DB,User: Auto-Generation Flow
        Webapp->>Webapp: New org created<br/>"GENFIT" with users John Doe, William Banks
        Webapp->>DB: INSERT 3 aliases:<br/>1. genfit (org alias, userId=null)<br/>2. genfit_johndoe (user alias, userId=john)<br/>3. genfit_williambanks (user alias, userId=william)
        DB-->>Webapp: Aliases created (verified=false)
        Webapp->>Webapp: Send verification emails<br/>to respective users
    end

---

Implementation Plan

1. Database Schema Migration

File: packages/db/src/schema/email-order-aliases.ts

Create enhanced table for email alias → organization mapping with verification:

export const emailOrderAliases = pgTable(
  'email_order_aliases',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    clerkOrganizationId: text('clerk_organization_id').notNull(),
    userId: text('user_id'), // NULLABLE for org aliases (wildcard)
    erpConnectionId: uuid('erp_connection_id')
      .references(() => erpConnections.id)
      .notNull(),
    subaddress: varchar('subaddress', { length: 100 }).notNull(), // e.g., "genfit", "genfit_johndoe"
    aliasType: text('alias_type', { enum: ['organization', 'user'] }).notNull(),
    description: text('description'), // Optional notes
    active: boolean('active').default(true),

    // Verification fields
    verified: boolean('verified').default(false),
    verificationToken: text('verification_token'), // Magic link token
    verificationTokenExpires: timestamp('verification_token_expires'),
    verifiedAt: timestamp('verified_at'),
    verificationEmailSent: timestamp('verification_email_sent'),

    // Auto-generation tracking
    autoGenerated: boolean('auto_generated').default(false),
    generatedBy: text('generated_by'), // 'org_creation', 'user_creation', 'manual'

    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  table => [
    uniqueIndex('email_aliases_subaddress_unique').on(table.subaddress),
    index('email_aliases_org_idx').on(table.clerkOrganizationId),
    index('email_aliases_verification_token_idx').on(table.verificationToken),
  ]
);

Key Constraints:

• subaddress must be globally unique (one client can’t be used by multiple orgs)
• userId is nullable for organization aliases (wildcard aliases)
• Foreign key to erpConnections ensures valid connection
• Verification token expires in 24 hours
• Auto-generated aliases are marked for tracking

Updated PDF Documents Schema:

// Update pdfDocuments to allow nullable userId for org aliases
export const pdfDocuments = pgTable('pdf_documents', {
  // ... existing fields ...
  userId: text('user_id'), // NULLABLE when created from org alias
  // ... rest of fields ...
});

---

2. Email Processor App (Provider-Agnostic)

Location: apps/email-processor/

Structure:

apps/email-processor/
├── src/
│   ├── index.ts                    # Hono app entry point
│   ├── routes/
│   │   ├── webhook.ts             # Unified webhook handler
│   │   ├── health.ts              # Health check endpoint
│   │   └── verify.ts              # Magic link verification endpoint
│   ├── adapters/
│   │   ├── base-adapter.ts        # Abstract base adapter
│   │   ├── gmail-adapter.ts       # Gmail API implementation
│   │   ├── sendgrid-adapter.ts    # SendGrid implementation
│   │   └── mailgun-adapter.ts     # Mailgun implementation
│   ├── services/
│   │   ├── alias-lookup.ts         # Database alias resolution
│   │   ├── order-submit.ts         # Upload file to webapp API
│   │   ├── body-parser.ts          # AI-powered email body parsing
│   │   └── verification.ts         # Magic link verification service
│   ├── utils/
│   │   ├── logger.ts               # Logging utility
│   │   └── env.ts                  # Environment validation
│   └── tasks/
│       ├── renew-watch.ts          # Temporal scheduled workflow hook (Gmail)
│       └── cleanup-tokens.ts       # Cleanup expired verification tokens
├── package.json
├── tsconfig.json
├── Dockerfile
└── README.md

Key Endpoints:

1. POST /webhook/{provider} - Unified webhook handler

   • Routes to appropriate adapter based on provider
   • Validates webhook authenticity per provider
   • Fetches full message using provider-specific API
   • Parses subaddress from “To” field
   • Looks up alias in database (verified only)
   • Processes attachment OR body content
   • Calls POST /api/pdf-documents

2. GET /verify/{token} - Magic link verification

   • Validates verification token
   • Checks expiration (24 hours)
   • Updates alias to verified status
   • Redirects to success page

3. GET /health - Health check for monitoring

---

3. Auto-Generation System

Clerk Webhook Integration:

Create webhook handlers in webapp to auto-generate aliases:

---

4. Webapp Admin Panel

Location: apps/webapp/src/pages/dashboard/email-aliases/

Pages:

1. index.astro - List all aliases for organization

   • Table with columns: Subaddress, Full Email, Type, Status, Verified, Actions
   • Filter/search functionality
   • “Create New Alias” button
   • “Send Verification” button for unverified aliases
   • “Generate Default Aliases” button

2. new.astro - Create new alias form

   • Input: Subaddress (alphanumeric + hyphens + underscores only)
   • Dropdown: Select ERP Connection
   • Radio: Alias Type (Organization/User)
   • Textarea: Description (optional)
   • Preview: orders+[subaddress]@erpunlocked.com
   • Validation: Check uniqueness before submit

3. [id].astro - Edit/view alias details

   • Update description
   • Toggle active/inactive status
   • Send verification email
   • View verification status and history
   • Delete alias (with confirmation)
   • View usage stats (future enhancement)

4. verify/[token].astro - Magic link verification page

   • Validate token and show success/error
   • Redirect to admin panel after verification
   • Show instructions for using the alias

API Routes:

• apps/webapp/src/pages/api/email-aliases/index.ts

  • GET: List aliases for org
  • POST: Create new alias
  • POST: Generate default aliases for org

• apps/webapp/src/pages/api/email-aliases/[id].ts

  • GET: Get alias details
  • PATCH: Update alias
  • DELETE: Soft delete alias
  • POST: Send verification email

• apps/webapp/src/pages/api/email-aliases/verify/[token].ts

  • GET: Verify magic link token
  • Update alias verification status

---

5. Dual Inbox System

Organization Inbox (/dashboard/email-orders):

// Show all orders from email for the organization
const orgOrders = await db.query.pdfDocuments.findMany({
  where: and(eq(pdfDocuments.clerkOrganizationId, orgId), eq(pdfDocuments.source, 'external')),
  with: {
    extractedOrders: true,
  },
  orderBy: desc(pdfDocuments.createdAt),
});

User Inbox (/dashboard/my-email-orders):

// Show only orders assigned to the authenticated user
const userOrders = await db.query.pdfDocuments.findMany({
  where: and(
    eq(pdfDocuments.clerkOrganizationId, orgId),
    eq(pdfDocuments.userId, userId),
    eq(pdfDocuments.source, 'external')
  ),
  with: {
    extractedOrders: true,
  },
  orderBy: desc(pdfDocuments.createdAt),
});

Inbox Features:

• Filter by date range, status, alias used
• Search by customer name, order number

---

6. Email Body Parsing

Fallback Strategy:

1. Try attachment processing first
2. If attachment fails or doesn’t exist, parse email body
3. If body parsing fails, log error and ignore email
4. Always log the processing path taken for debugging

---

7. Magic Link Verification System

Token Generation:

// Generate secure verification token
const verificationToken = crypto.randomBytes(32).toString('hex');
const expiresAt = new Date(Date.now() + 24 * 60 * 60 * 1000); // 24 hours

await db
  .update(emailOrderAliases)
  .set({
    verificationToken,
    verificationTokenExpires: expiresAt,
    verificationEmailSent: new Date(),
  })
  .where(eq(emailOrderAliases.id, aliasId));

Verification Endpoint:

apps/webapp/src/pages/api/email-aliases/verify/[token].ts

export const GET: APIRoute = async ({ params }) => {
  const { token } = params;

  const alias = await db.query.emailOrderAliases.findFirst({
    where: and(
      eq(emailOrderAliases.verificationToken, token),
      gt(emailOrderAliases.verificationTokenExpires, new Date())
    ),
  });

  if (!alias) {
    return new Response('Invalid or expired verification link', { status: 400 });
  }

  // Update alias to verified
  await db
    .update(emailOrderAliases)
    .set({
      verified: true,
      verifiedAt: new Date(),
      verificationToken: null, // Clear token after use
      verificationTokenExpires: null,
    })
    .where(eq(emailOrderAliases.id, alias.id));

  // Redirect to success page
  return Response.redirect('/dashboard/email-aliases?verified=true');
};

---

8. Security Considerations

Enhanced Threat Model:

1. Magic Link Security:

   • Tokens are cryptographically secure (32 bytes)
   • 24-hour expiration prevents long-term abuse
   • One-time use (token cleared after verification)
   • Rate limiting on verification attempts

2. Email Body Parsing Security:

   • Sanitize HTML content before AI processing
   • Limit body size to prevent DoS
   • Validate extracted data before processing
   • Log all parsing attempts for audit

3. Provider Webhook Security:

   • Validate webhook signatures per provider
   • Rate limiting on webhook endpoints
   • IP allowlisting for known provider IPs
   • Monitor for unusual webhook patterns

---

Future Enhancements

1. Reply-to Automation: Send confirmation email when order is processed
2. Error Notifications: Email sender if processing fails
3. Usage Analytics: Dashboard showing email volume per alias
4. Multi-Attachment Support: Process multiple files in one email
5. Email Templates: Support specific email formats/templates per client
6. CC/BCC Handling: Extract additional recipients for notifications

---

Risks & Mitigations

Risk	Impact	Mitigation
Email provider API limits	High	Implement caching, batch operations, multiple providers
Magic link abuse	Medium	Rate limiting, token expiration, one-time use
AI parsing errors	Medium	Fallback to manual review, confidence scoring
Watch expiration during downtime	Medium	Alert on expiration, manual renewal procedure
Email spam/abuse	Medium	Rate limiting, monitoring for unusual patterns
Attachment size limits	Low	Document max file size, reject oversized files
Subaddress collision	Low	Enforce uniqueness constraint in database
Provider webhook failures	Medium	Retry logic, dead letter queue, manual processing

---