ESA Order Processor

Native pricing-app module replacing the Pabbly-based ESA order workflows. Bundle-aware, multi-platform, with proper error handling.

Mirrored from superhitech/leif:docs/plans/esa-order-processor-plan.md. The repo copy is the source of truth — this page is a browsable mirror.

ESA Order Processor — Pricing App Module

Overview

Replace the Pabbly “Odyssey - Iowa” (and future state-specific) workflows with a native order processing module in the pricing app. The module handles the full lifecycle: email ingestion → parsing → bundle decomposition → ShipStation order creation → RepairShopr ticket creation → sale logging.

Why

• Pabbly’s ShipStation integration only supports single-item orders — can’t handle bundles
• Lookup tables in Pabbly don’t scale and are painful to maintain
• extractPattern returns arrays with footgun indexing (result[1] vs result[0])
• Character limits on email body processing
• No real error handling or retry logic — failures cascade silently
• Bundle decomposition requires multi-line-item support in both ShipStation and RS
• Pricing app already has product data, RS sync, and vendor pricing context

Architecture

New Components

pricing-app/
├── esa_orders/
│   ├── __init__.py
│   ├── models.py           # DB models: bundles, orders, order_items
│   ├── email_parser.py     # Parse ESA order emails (per-platform parsers)
│   ├── bundle_resolver.py  # Lookup bundle definitions, decompose into components
│   ├── shipstation.py      # ShipStation API client (multi-item orders)
│   ├── repairshopr.py      # RS ticket/invoice creation (multi-line-item)
│   ├── processor.py        # Main orchestrator: parse → resolve → create orders
│   ├── routes.py           # API endpoints for manual triggers, status, config
│   └── gmail_poller.py     # Poll Gmail for new ESA order emails (or webhook)

Database Tables

esa_bundles — Bundle definitions

esa_bundle_components — Component items within a bundle

esa_orders — Processed orders

esa_order_items — Line items (decomposed if bundle)

Email Parsing

Each ESA platform sends a different email format. The parser module has platform-specific parsers:

class BaseOrderParser:
    """Extract order data from ESA notification email"""
    def parse(self, subject: str, body_html: str) -> OrderData:
        raise NotImplementedError

class OdysseyIowaParser(BaseOrderParser):
    """Parse 'New Order TN FSA Marketplace: #XXXXX - Customer Name'"""
    # Extracts: order_number, customer_name, item_name, price,
    #           shipping address (street, city, state, zip)

class TNESAParser(BaseOrderParser):
    """Parse Tennessee ESA portal order emails"""

class TXESAParser(BaseOrderParser):
    """Parse Texas ESA portal order emails (future)"""

Platform detection from email subject line:

• New Order TN FSA Marketplace: → OdysseyIowaParser
• New Order TN ESA: → TNESAParser
• Subject patterns for other platforms as they come online

Bundle Resolution

class BundleResolver:
    def resolve(self, item_name: str, platform: str) -> ResolvedOrder:
        """
        Look up item_name in esa_bundles table.
        If found → return decomposed components from esa_bundle_components.
        If not found → look up in existing product/RS mapping (single item).
        """

The resolver replaces both the Pabbly lookup table AND the bundle JS logic in one clean step. Adding a new bundle or product mapping is an INSERT, not editing a Pabbly step.

ShipStation Integration

class ShipStationClient:
    BASE_URL = 'https://ssapi.shipstation.com'

    def create_order(self, order: ResolvedOrder) -> str:
        """
        POST /orders/createorder
        Supports multiple items[] in a single call.
        Returns ShipStation order ID.
        """

Key difference from Pabbly: the ShipStation REST API natively supports multiple line items per order. No workaround needed.

RepairShopr Integration

Uses existing RS API infrastructure already in the pricing app (rs_ticket_invoice_sheet_sync). Creates ticket with multiple line items, each referencing the RS product ID so inventory decrements correctly.

Processing Flow

1. Gmail poll (every 10 min, matching Pabbly's current interval)
   └─ Filter: from:orders@super-ht.com, subject matches ESA patterns
   └─ Dedup: check gmail_message_id against esa_orders table

2. Parse email
   └─ Detect platform from subject
   └─ Extract: order_number, customer, address, item_name, price

3. Resolve bundle
   └─ Query esa_bundles by item_name + platform
   └─ If bundle: fetch components from esa_bundle_components
   └─ If single: resolve to RS product ID via existing product table

4. Create ShipStation order
   └─ POST /orders/createorder with all line items
   └─ Store ss_order_id

5. Create RepairShopr ticket
   └─ POST /api/v1/invoices with line items
   └─ Each component decrements from shared inventory pool
   └─ Store rs_ticket_id

6. Log to esa_orders + esa_order_items
   └─ Status: 'completed'
   └─ On any error: status='error', error_message populated, alert via Slack

7. (Optional) Update pricing app sale tracking

API Endpoints

GET  /api/v1/esa/orders                  — List processed orders
GET  /api/v1/esa/orders/:id              — Order detail
POST /api/v1/esa/orders/reprocess/:id    — Retry a failed order
GET  /api/v1/esa/bundles                 — List bundle definitions
POST /api/v1/esa/bundles                 — Create bundle
PUT  /api/v1/esa/bundles/:id             — Update bundle
GET  /api/v1/esa/bundles/:id/components  — List bundle components
POST /api/v1/esa/bundles/:id/components  — Add component to bundle
GET  /api/v1/esa/status                  — Poller health, last run, queue depth

Admin UI (Phase 2)

Add bundle management to the pricing app web UI:

• Create/edit bundles with component drag-and-drop
• Price split calculator (auto-proportional or manual)
• Order log with status, errors, reprocess button
• Bundle inventory availability view (min component stock = bundle availability)

Migration Plan

Phase 1: Build & Test (parallel)

• Build the module, run it alongside Pabbly
• Process orders through both systems, compare results
• Pabbly remains the live system

Phase 2: Cutover

• Disable Pabbly workflow
• Pricing app module goes live
• Monitor for 1 week

Phase 3: Expand

• Add bundle definitions for all ESA platforms
• Add Texas ESA parser when that goes live
• Build admin UI for bundle management
• Add component-aware stock availability to the inventory view

Dependencies

• ShipStation API credentials (already have — used in Pabbly)
• Gmail API access (already have via Leif)
• RS API access (already have via pricing app)
• Postgres (already have — pricing app DB)

Risk

• Email format changes: ESA platforms may change their order notification format. Mitigation: parser is modular, each platform is isolated. Raw email stored for debugging.
• Race conditions: Two orders for the last Pencil in stock could both succeed. Mitigation: RS inventory is the source of truth. Check stock before creating order, or accept and handle oversell alerts.
• Gmail polling gap: 10-minute poll interval means up to 10 minutes delay. Could move to Gmail push notifications later if latency matters.

Estimated Effort

• Phase 1 (core module + Iowa parser): 3-4 Claude Code sessions
• Phase 2 (cutover + monitoring): 1 session + 1 week observation
• Phase 3 (admin UI + TX parser): 2-3 sessions