Integration Architecture Pattern: Temporal-Based Durable Transactions

Date: March 6, 2026
Status: ESTABLISHED PATTERN
Safety Level: CRITICAL

---

Executive Summary

All third-party ERP integrations should follow the Temporal-based durable transaction pattern:

• Read Operations (GET) → Temporal activities (cacheable reference data) + Dagster batch jobs
• Write Operations (POST/PATCH/DELETE) → Temporal workflows with retry logic, audit trails, and rollback capability
• Rationale: Ensures reliability, traceability, and atomicity across network boundaries

This document establishes the pattern once and prevents reinventing the wheel for P21, NetSuite, SAP, etc.

---

Pattern Overview

┌─────────────────────────────────────────────────────────┐
│                    WEBAPP USER ACTION                    │
│              (e.g., "Submit Order to P21")              │
└────────────────────┬────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────┐
│         TEMPORAL WORKFLOW (Durable Transaction)          │
│                                                          │
│  1. Fetch live data from ERP API (GET)                 │
│  2. Validate against local rules                       │
│  3. Calculate adjustments (pricing, discounts, etc)    │
│  4. Show user confirmation                            │
│  5. On approval:                                      │
│     ├─ Begin transaction                              │
│     ├─ Send POST/PATCH to ERP                        │
│     ├─ Retry on failure (exponential backoff)        │
│     ├─ Rollback if failed                            │
│     └─ Log audit trail                               │
│  6. Return result (success/failure)                   │
└─────────────────────────────────────────────────────────┘
                     │
         ┌───────────┼───────────┐
         ▼           ▼           ▼
      SUCCESS    FAILURE     AUDIT LOG
      (return)   (retry)     (store)

---

Why Temporal?

Aspect	Temporal Advantage
Durability	Survives network failures, worker crashes
Retry Logic	Exponential backoff with configurable limits
Audit Trail	Every step logged for compliance
Rollback	Can reverse failed writes (compensating transactions)
Timeout Handling	Won’t hang indefinitely on bad connections
Ordering	Guarantees steps execute in order
Idempotency	Safe to replay without side effects

---

CK POC: WhereFour Integration Plan (APPROVED)

Status: ✅ READY FOR IMPLEMENTATION

Architecture

WEBAPP
  └─ User submits order
       │
       ▼
   TEMPORAL WORKFLOW: submit_order_to_wherefour
       │
       ├─ Activity 1: fetch_order_from_api(order_id)
       │    └─ GET /orders/{id}
       │    └─ Returns: order_items[], custom_line_items[], pricing
       │
       ├─ Activity 2: get_pricing_tiers_from_cache()
       │    └─ Read Redis (24h TTL, synced by Dagster)
       │
       ├─ Activity 3: validate_order(order, tiers)
       │    └─ Pure Python: validate products + custom items
       │
       ├─ Activity 4: calculate_discount(order, validation)
       │    └─ Apply tier discounts + allowances
       │    └─ Return discount chip data
       │
       ├─ (User confirms in UI)
       │
       ├─ Activity 5: submit_to_wherefour(order)
       │    └─ ⚠️ ONLY on Sandbox account (when available)
       │    └─ BLOCKED for production until sandbox exists
       │
       └─ Activity 6: audit_log(workflow_id, status)
            └─ Store in PostgreSQL audit_trail table

Data Sources

Data	Operation	Source	Where
Orders	GET	API	Temporal (on-submit)
Order Items	GET	API	Temporal (on-submit)
Custom Line Items	GET	API	Temporal (on-submit)
Pricing Tiers	GET	Scraper	Redis (Dagster daily)
Customers	GET	API	Redis (Dagster daily)
Inventory	GET	API	PostgreSQL (Dagster daily)

⚠️ CRITICAL SAFETY BOUNDARY: NO WRITES TO PRODUCTION

# ALLOWED (GET only)
✅ fetch_order_from_api(order_id)
✅ get_customers_list()
✅ get_inventory_catalog()

# BLOCKED (POST/PATCH/DELETE)
❌ submit_to_wherefour()      # ← Wait for sandbox account
❌ update_order_status()      # ← Wait for sandbox account
❌ create_invoice()           # ← Wait for sandbox account

RULE: If operation requires POST, PATCH, or DELETE, it MUST target sandbox account first.
STATUS: Sandbox account NOT YET AVAILABLE.
IMPLICATION: CK POC cannot test write operations until sandbox confirmed.

---

Project: P21 (Epicor Prophet21) Integration Refactor

Status: PLANNED (Q2/Q3 2026)
Scope: Refactor existing P21 integration to use Temporal pattern

Current State Analysis (To Be Completed)

• Identify all P21 API calls in codebase
• Classify: GET (read) vs POST (write)
• Document current error handling
• Identify silent failure points

Desired State (Temporal-based)

Temporal Workflow: submit_order_to_p21
  ├─ Activity: fetch_p21_customer(customer_id)
  │    └─ GET /customers/{id} from P21 API
  │
  ├─ Activity: validate_p21_sku_mapping(order_items)
  │    └─ Check SKU translations
  │
  ├─ Activity: submit_p21_purchase_order(order_data)
  │    └─ POST /purchase-orders to P21 (with retry)
  │
  ├─ Activity: confirm_p21_receipt(po_id)
  │    └─ PATCH /purchase-orders/{id}/confirm
  │
  └─ Activity: log_audit_trail(workflow_id, p21_po_id)
       └─ Store in PostgreSQL

Implementation Plan (Rough)

1. Phase 1: Extract P21 API client library (if doesn’t exist)
2. Phase 2: Create Temporal activities for core operations
3. Phase 3: Create Temporal workflows (submit order, confirm, cancel, etc)
4. Phase 4: Integrate with webapp (replace direct P21 calls)
5. Phase 5: Add retry logic + exponential backoff
6. Phase 6: Add audit logging + dashboard

---

Other Integrations: Project Plans to Update

Apply same pattern to:

NetSuite Integration

Temporal Workflows:
  ├─ submit_order_to_netsuite
  ├─ sync_inventory_from_netsuite
  └─ pull_customer_data

Read operations (Dagster):
  ├─ Fetch customers daily
  ├─ Fetch tax codes daily
  └─ Fetch item catalog daily

SAP Integration

Temporal Workflows:
  ├─ submit_sto_to_sap
  ├─ confirm_receipt
  └─ sync_shipment_status

Read operations (Dagster):
  ├─ Fetch material master data
  ├─ Fetch cost centers
  └─ Fetch plant data

QuickBooks Integration

Temporal Workflows:
  ├─ create_invoice_in_qb
  ├─ record_payment
  └─ sync_estimates

Read operations (Dagster):
  ├─ Fetch customers
  ├─ Fetch items
  └─ Fetch payment methods

---

Dagster Role: Reference Data Sync (Not Transactions)

Dagster jobs handle read-only, batch operations ONLY:

@job
def wherefour_sync_daily():
    """Sync reference data (NOT transactional)."""

    # Fetch reference data
    tiers = scrape_pricing_tiers()          # Playwright scraper
    customers = fetch_customers_from_api()  # GET /customers
    inventory = fetch_inventory_from_api()  # GET /inventory
    vendors = fetch_vendors_from_api()      # GET /vendors

    # Cache in Redis + PostgreSQL
    cache_pricing_tiers(tiers)      # Redis 24h TTL
    cache_customers(customers)      # Redis 24h TTL
    store_inventory(inventory)      # PostgreSQL 7d retention
    store_vendors(vendors)          # PostgreSQL 7d retention

NEVER use Dagster for writes (POST/PATCH/DELETE). Use Temporal instead.

---

Safety Rules (CRITICAL)

Rule 1: GET Only in Dagster

✅ OK:  GET /customers, GET /inventory, GET /orders
❌ NO:  POST /invoices, PATCH /status, DELETE /orders

Rule 2: Writes Only in Temporal Workflows

✅ OK:  Temporal activity submits POST to sandbox
❌ NO:  Direct httpx.post() in main code
❌ NO:  Direct requests.post() in webapp

Rule 3: Sandbox First for New Write Operations

Before testing ANY POST/PATCH/DELETE:
  1. Request sandbox account from vendor
  2. Verify credentials work
  3. Run test against sandbox
  4. Document test results
  5. ONLY then: enable production writes

Rule 4: All Writes Must Be Audited

Every POST/PATCH/DELETE must:
  ├─ Log to PostgreSQL audit_trail
  ├─ Include workflow_id + timestamp
  ├─ Include full request payload
  ├─ Include full response payload
  └─ Be queryable for compliance

---

Audit Trail Schema (PostgreSQL)

CREATE TABLE integration_audit_trail (
    id BIGSERIAL PRIMARY KEY,
    workflow_id UUID NOT NULL,           -- Temporal workflow ID
    integration_name VARCHAR(50),        -- "wherefour", "p21", "netsuite"
    operation VARCHAR(20),               -- "GET", "POST", "PATCH", "DELETE"
    endpoint VARCHAR(255),               -- "/orders", "/customers/123"
    status VARCHAR(20),                  -- "SUCCESS", "FAILURE", "RETRY"
    request_payload JSONB,               -- What we sent
    response_payload JSONB,              -- What we got back
    error_message TEXT,                  -- If failed
    attempted_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_audit_workflow_id ON integration_audit_trail(workflow_id);
CREATE INDEX idx_audit_integration ON integration_audit_trail(integration_name);
CREATE INDEX idx_audit_timestamp ON integration_audit_trail(created_at DESC);

---

WhereFour Integration: Documentation (COMPLETE)

Endpoints Used

Endpoint	Method	Purpose	Where Used	Cache TTL
/customers?limit=100	GET	Customer list	Dagster job	24h
/customers/{id}	GET	Customer details	Temporal (on-demand)	24h
/orders?limit=100	GET	Orders list	Dagster snapshot	-
/orders/{id}	GET	Order details + items	Temporal (on-submit)	5min
/inventory?limit=100	GET	Inventory catalog	Dagster job	7d
/inventory/search?q=X	GET	Search products	Webapp route	-
/vendors?limit=100	GET	Vendor list	Dagster job	7d
/settings/price_tiers	WEB SCRAPE	Pricing tiers	Dagster + Playwright	24h

No Write Operations

• ❌ POST to /orders
• ❌ PATCH to /order/{id}
• ❌ DELETE to /order/{id}

Reason: No sandbox account available. Production data protection.

Testing Status

• ✅ All GET endpoints verified (11/11 passing)
• ✅ Order structure validated (order_items + custom_line_items)
• ✅ Pricing data verified (subtotal, discount, total)
• ✅ Search endpoints confirmed working
• ❌ Write operations: NOT TESTED (blocked by safety rule)

---

Summary: Temporal Pattern Benefits

Scenario	Without Temporal	With Temporal
Network fails mid-submit	❌ Data lost	✅ Retry automatically
Worker crashes	❌ Workflow lost	✅ Resumed on recovery
Invalid data submitted	❌ Silent failure	✅ Logged + auditable
Need to replay transaction	❌ Manual intervention	✅ Replay from workflow ID
Compliance audit	❌ No trail	✅ Full audit log
Production issue	❌ Hard to debug	✅ Queryable workflow history

---

Next Steps (Priority Order)

1. ✅ WhereFour CK POC: Implement Temporal workflow for order submission (GET only)
2. 🔲 P21 Refactor: Create detailed project plan using this pattern (Q2/Q3)
3. 🔲 NetSuite: Update project plan to use Temporal (when started)
4. 🔲 SAP: Update project plan to use Temporal (when started)
5. 🔲 QuickBooks: Update project plan to use Temporal (when started)

Do not start P21/NetSuite/SAP refactors yet. This establishes the pattern for when you do.

---

Compliance & Safety Checklist

Before implementing any integration write operations:

• Sandbox account requested and confirmed
• Test data loaded into sandbox
• Temporal workflow created with retry logic
• Audit trail table created
• Rollback strategy documented
• Error handling tested
• Production credentials secured (1Password / Infisical)
• Approval from product owner
• Gradual rollout plan created (5% → 25% → 100%)

---

FINAL WARNING: 🚨 NO WRITES TO WHEREFOUR PRODUCTION 🚨
Sandbox account only. GET operations only. Await explicit approval before enabling POST/PATCH/DELETE.