Skip to main content

External API Setup

Mock external systems using Cloudflare Workers and D1 database for testing NetSuite integrations.

Architecture Overview​

INTEGRATION TEST ENVIRONMENT
===============================================================================

  NetSuite                          Cloudflare Worker                D1 Database
+-------------+                    +------------------+              +----------+
|             |   PUSH OUT         |                  |              |          |
| Sales Order |  --------------->  | POST /api/orders |  --------->  | orders   |
| (approved)  |   JSON payload     |                  |   INSERT     |          |
|             |                    +------------------+              +----------+
|             |                                                               
|             |   PULL IN          +------------------+              +----------+
| Exchange    |  <---------------  | GET /api/rates   |  <---------  | rates    |
| Rates       |   JSON response    |                  |   SELECT     |          |
+-------------+                    +------------------+              +----------+

Auth: X-API-Key header with static token

Cloudflare Worker Setup​

Prerequisites​

# Install Wrangler CLI
npm install -g wrangler

# Login to Cloudflare
wrangler login

# Create new project
wrangler init quickmart-warehouse-api
cd quickmart-warehouse-api

wrangler.toml Configuration​

name = "quickmart-warehouse-api"
main = "src/index.js"
compatibility_date = "2024-03-01"

# D1 Database binding
[[d1_databases]]
binding = "DB"
database_name = "quickmart-warehouse"
database_id = "your-database-id-here"

# Environment variables (secrets set via wrangler secret)
[vars]
API_KEY = "qm-warehouse-test-key-2024"  # For dev only, use secrets in prod

Create D1 Database​

# Create the database
wrangler d1 create quickmart-warehouse

# Copy the database_id to wrangler.toml

Database Schema​

schema.sql​

-- ============================================================================
-- QuickMart Warehouse Database Schema
-- ============================================================================

-- Orders received from NetSuite
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    netsuite_id INTEGER NOT NULL UNIQUE,
    order_number TEXT NOT NULL,
    customer_name TEXT,
    ship_address TEXT,
    priority INTEGER DEFAULT 0,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'picking', 'packed', 'shipped', 'cancelled')),
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    completed_at TEXT
);

-- Order line items
CREATE TABLE IF NOT EXISTS order_lines (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    item_sku TEXT,
    item_name TEXT,
    quantity INTEGER NOT NULL,
    location TEXT,
    picked_qty INTEGER DEFAULT 0,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'picked', 'packed'))
);

-- Exchange rates (for Pull In testing)
CREATE TABLE IF NOT EXISTS exchange_rates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    base_currency TEXT DEFAULT 'USD',
    target_currency TEXT NOT NULL,
    rate REAL NOT NULL,
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Integration log (audit trail)
CREATE TABLE IF NOT EXISTS integration_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    direction TEXT CHECK(direction IN ('inbound', 'outbound')),
    endpoint TEXT,
    method TEXT,
    payload TEXT,
    response TEXT,
    status_code INTEGER,
    created_at TEXT DEFAULT (datetime('now'))
);

-- Seed exchange rates
INSERT INTO exchange_rates (target_currency, rate) VALUES 
    ('EUR', 0.92),
    ('GBP', 0.79),
    ('SGD', 1.35),
    ('IDR', 15650);

-- Create indexes
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_netsuite_id ON orders(netsuite_id);
CREATE INDEX idx_order_lines_order_id ON order_lines(order_id);

Apply Schema​

wrangler d1 execute quickmart-warehouse --file=./schema.sql

Worker Source Code​

src/index.js​

/**
 * QuickMart Warehouse API
 * Cloudflare Worker + D1
 * 
 * Endpoints:
 *   POST /api/orders          - Receive order from NetSuite (Push Out)
 *   GET  /api/orders          - List orders
 *   GET  /api/orders/:id      - Get order details
 *   POST /api/orders/:id/pick - Mark order as picked
 *   POST /api/orders/:id/ship - Mark order as shipped
 *   GET  /api/rates           - Get exchange rates (Pull In)
 *   GET  /api/stock/:sku      - Get stock level
 */

export default {
    async fetch(request, env, ctx) {
        const url = new URL(request.url);
        const path = url.pathname;
        const method = request.method;

        // CORS headers
        const corsHeaders = {
            'Access-Control-Allow-Origin': '*',
            'Access-Control-Allow-Methods': 'GET, POST, PUT, DELETE, OPTIONS',
            'Access-Control-Allow-Headers': 'Content-Type, X-API-Key',
        };

        // Handle preflight
        if (method === 'OPTIONS') {
            return new Response(null, { headers: corsHeaders });
        }

        // Authentication
        const apiKey = request.headers.get('X-API-Key');
        if (apiKey !== env.API_KEY) {
            return jsonResponse({ 
                error: 'Unauthorized', 
                message: 'Invalid or missing X-API-Key header' 
            }, 401, corsHeaders);
        }

        try {
            // Route handling
            // POST /api/orders - Receive order from NetSuite
            if (path === '/api/orders' && method === 'POST') {
                return await handleReceiveOrder(request, env, corsHeaders);
            }

            // GET /api/orders - List all orders
            if (path === '/api/orders' && method === 'GET') {
                return await handleListOrders(url, env, corsHeaders);
            }

            // GET /api/orders/:id - Get specific order
            const orderMatch = path.match(/^\/api\/orders\/(\d+)$/);
            if (orderMatch && method === 'GET') {
                return await handleGetOrder(orderMatch[1], env, corsHeaders);
            }

            // POST /api/orders/:id/pick - Mark as picked
            const pickMatch = path.match(/^\/api\/orders\/(\d+)\/pick$/);
            if (pickMatch && method === 'POST') {
                return await handlePickOrder(pickMatch[1], env, corsHeaders);
            }

            // POST /api/orders/:id/ship - Mark as shipped
            const shipMatch = path.match(/^\/api\/orders\/(\d+)\/ship$/);
            if (shipMatch && method === 'POST') {
                return await handleShipOrder(shipMatch[1], request, env, corsHeaders);
            }

            // GET /api/rates - Get exchange rates
            if (path === '/api/rates' && method === 'GET') {
                return await handleGetRates(env, corsHeaders);
            }

            // GET /api/stock/:sku - Get stock (mock)
            const stockMatch = path.match(/^\/api\/stock\/(.+)$/);
            if (stockMatch && method === 'GET') {
                return await handleGetStock(stockMatch[1], corsHeaders);
            }

            // 404 for unmatched routes
            return jsonResponse({ error: 'Not Found' }, 404, corsHeaders);

        } catch (error) {
            console.error('Error:', error);
            return jsonResponse({ 
                error: 'Internal Server Error', 
                message: error.message 
            }, 500, corsHeaders);
        }
    }
};

// ============================================================================
// Handler Functions
// ============================================================================

/**
 * POST /api/orders
 * Receives order push from NetSuite
 */
async function handleReceiveOrder(request, env, headers) {
    const body = await request.json();

    // Validate required fields
    if (!body.nsInternalId || !body.orderId) {
        return jsonResponse({ 
            error: 'Validation Error',
            message: 'nsInternalId and orderId are required'
        }, 400, headers);
    }

    // Check for duplicate
    const existing = await env.DB.prepare(
        'SELECT id FROM orders WHERE netsuite_id = ?'
    ).bind(body.nsInternalId).first();

    if (existing) {
        return jsonResponse({
            success: false,
            error: 'Duplicate Order',
            message: `Order ${body.orderId} already exists`,
            warehouse_id: existing.id
        }, 409, headers);
    }

    // Insert order
    const result = await env.DB.prepare(`
        INSERT INTO orders (netsuite_id, order_number, customer_name, ship_address, priority)
        VALUES (?, ?, ?, ?, ?)
    `).bind(
        body.nsInternalId,
        body.orderId,
        body.customer || 'Unknown',
        body.shipAddress || '',
        body.priority ? 1 : 0
    ).run();

    const orderId = result.meta.last_row_id;

    // Insert lines
    if (body.lines && Array.isArray(body.lines)) {
        for (const line of body.lines) {
            await env.DB.prepare(`
                INSERT INTO order_lines (order_id, item_sku, item_name, quantity, location)
                VALUES (?, ?, ?, ?, ?)
            `).bind(
                orderId,
                line.sku || line.item,
                line.item,
                line.quantity,
                line.location || 'Main Warehouse'
            ).run();
        }
    }

    // Log the integration
    await logIntegration(env, 'inbound', '/api/orders', 'POST', body, { success: true, orderId }, 200);

    return jsonResponse({
        success: true,
        message: 'Order received and queued for fulfillment',
        warehouse_id: orderId,
        order_number: body.orderId,
        status: 'pending',
        estimated_ship_date: getEstimatedShipDate(body.priority)
    }, 201, headers);
}

/**
 * GET /api/orders
 * List orders with optional status filter
 */
async function handleListOrders(url, env, headers) {
    const status = url.searchParams.get('status');
    const limit = parseInt(url.searchParams.get('limit')) || 50;

    let query = 'SELECT * FROM orders';
    let params = [];

    if (status) {
        query += ' WHERE status = ?';
        params.push(status);
    }

    query += ' ORDER BY priority DESC, created_at DESC LIMIT ?';
    params.push(limit);

    const stmt = env.DB.prepare(query);
    const result = await stmt.bind(...params).all();

    return jsonResponse({
        success: true,
        count: result.results.length,
        orders: result.results
    }, 200, headers);
}

/**
 * GET /api/orders/:id
 * Get order with lines
 */
async function handleGetOrder(id, env, headers) {
    const order = await env.DB.prepare(
        'SELECT * FROM orders WHERE id = ?'
    ).bind(id).first();

    if (!order) {
        return jsonResponse({ error: 'Order not found' }, 404, headers);
    }

    const lines = await env.DB.prepare(
        'SELECT * FROM order_lines WHERE order_id = ?'
    ).bind(id).all();

    return jsonResponse({
        success: true,
        order: {
            ...order,
            lines: lines.results
        }
    }, 200, headers);
}

/**
 * POST /api/orders/:id/pick
 * Mark order as picked
 */
async function handlePickOrder(id, env, headers) {
    // Update all lines to picked
    await env.DB.prepare(`
        UPDATE order_lines 
        SET status = 'picked', picked_qty = quantity 
        WHERE order_id = ?
    `).bind(id).run();

    // Update order status
    await env.DB.prepare(`
        UPDATE orders 
        SET status = 'picking', updated_at = datetime('now') 
        WHERE id = ?
    `).bind(id).run();

    return jsonResponse({
        success: true,
        message: 'Order marked as picked',
        order_id: id,
        status: 'picking'
    }, 200, headers);
}

/**
 * POST /api/orders/:id/ship
 * Mark order as shipped, return tracking info to send back to NetSuite
 */
async function handleShipOrder(id, request, env, headers) {
    const body = await request.json().catch(() => ({}));
    const trackingNumber = body.trackingNumber || generateTrackingNumber();

    await env.DB.prepare(`
        UPDATE orders 
        SET status = 'shipped', 
            completed_at = datetime('now'),
            updated_at = datetime('now')
        WHERE id = ?
    `).bind(id).run();

    const order = await env.DB.prepare(
        'SELECT * FROM orders WHERE id = ?'
    ).bind(id).first();

    return jsonResponse({
        success: true,
        message: 'Order shipped',
        order_id: id,
        netsuite_id: order?.netsuite_id,
        tracking_number: trackingNumber,
        carrier: 'FedEx',
        shipped_at: new Date().toISOString()
    }, 200, headers);
}

/**
 * GET /api/rates
 * Return exchange rates (for NetSuite Pull In)
 */
async function handleGetRates(env, headers) {
    const rates = await env.DB.prepare(
        'SELECT target_currency, rate, updated_at FROM exchange_rates'
    ).all();

    const rateMap = {};
    rates.results.forEach(r => {
        rateMap[r.target_currency] = r.rate;
    });

    return jsonResponse({
        success: true,
        base: 'USD',
        date: new Date().toISOString().split('T')[0],
        rates: rateMap
    }, 200, headers);
}

/**
 * GET /api/stock/:sku
 * Mock stock levels
 */
async function handleGetStock(sku, headers) {
    // Mock data - in real app, would query inventory system
    const mockStock = {
        'PAPER-A4': { available: 150, onHand: 200, onOrder: 50 },
        'PAPER-LETTER': { available: 80, onHand: 100, onOrder: 0 },
        'INK-HP-BLACK': { available: 25, onHand: 30, onOrder: 100 },
        'INK-HP-COLOR': { available: 15, onHand: 20, onOrder: 50 },
    };

    const stock = mockStock[sku.toUpperCase()] || { available: 0, onHand: 0, onOrder: 0 };

    return jsonResponse({
        success: true,
        sku: sku.toUpperCase(),
        inventory: {
            available: stock.available,
            on_hand: stock.onHand,
            on_order: stock.onOrder,
            warehouse: 'Main Warehouse'
        },
        last_updated: new Date().toISOString()
    }, 200, headers);
}

// ============================================================================
// Helper Functions
// ============================================================================

function jsonResponse(data, status = 200, headers = {}) {
    return new Response(JSON.stringify(data, null, 2), {
        status,
        headers: {
            'Content-Type': 'application/json',
            ...headers
        }
    });
}

function getEstimatedShipDate(isPriority) {
    const date = new Date();
    date.setDate(date.getDate() + (isPriority ? 1 : 3));
    return date.toISOString().split('T')[0];
}

function generateTrackingNumber() {
    return 'QM' + Date.now().toString(36).toUpperCase() + 
           Math.random().toString(36).substring(2, 6).toUpperCase();
}

async function logIntegration(env, direction, endpoint, method, payload, response, statusCode) {
    await env.DB.prepare(`
        INSERT INTO integration_log (direction, endpoint, method, payload, response, status_code)
        VALUES (?, ?, ?, ?, ?, ?)
    `).bind(
        direction,
        endpoint,
        method,
        JSON.stringify(payload),
        JSON.stringify(response),
        statusCode
    ).run();
}

Deployment​

# Deploy to Cloudflare
wrangler deploy

# Set secret API key for production
wrangler secret put API_KEY
# Enter: your-secure-api-key-here

# Test the deployment
curl -X GET "https://quickmart-warehouse-api.your-subdomain.workers.dev/api/rates" \
  -H "X-API-Key: qm-warehouse-test-key-2024"

API Reference​

Authentication​

All requests require the X-API-Key header:

X-API-Key: qm-warehouse-test-key-2024

Endpoints​

POST /api/orders​

Receive order from NetSuite (Push Out pattern).

Request:

{
    "nsInternalId": 12345,
    "orderId": "SO-00567",
    "customer": "ABC Corporation",
    "shipAddress": "123 Main St, Springfield, IL 62701",
    "priority": true,
    "lines": [
        {
            "item": "A4 Printer Paper",
            "sku": "PAPER-A4",
            "quantity": 20,
            "location": "Main Warehouse"
        },
        {
            "item": "HP Ink Cartridge",
            "sku": "INK-HP-BLACK",
            "quantity": 5,
            "location": "Main Warehouse"
        }
    ]
}

Response (201):

{
    "success": true,
    "message": "Order received and queued for fulfillment",
    "warehouse_id": 42,
    "order_number": "SO-00567",
    "status": "pending",
    "estimated_ship_date": "2024-03-16"
}

GET /api/orders​

List orders with optional filtering.

Query Parameters:

  • status - Filter by status (pending, picking, packed, shipped)
  • limit - Max results (default: 50)

Response:

{
    "success": true,
    "count": 2,
    "orders": [
        {
            "id": 42,
            "netsuite_id": 12345,
            "order_number": "SO-00567",
            "customer_name": "ABC Corporation",
            "priority": 1,
            "status": "pending",
            "created_at": "2024-03-15T10:30:00Z"
        }
    ]
}

GET /api/orders/:id​

Get order details with lines.

Response:

{
    "success": true,
    "order": {
        "id": 42,
        "netsuite_id": 12345,
        "order_number": "SO-00567",
        "customer_name": "ABC Corporation",
        "ship_address": "123 Main St, Springfield, IL 62701",
        "priority": 1,
        "status": "pending",
        "created_at": "2024-03-15T10:30:00Z",
        "lines": [
            {
                "id": 1,
                "item_sku": "PAPER-A4",
                "item_name": "A4 Printer Paper",
                "quantity": 20,
                "location": "Main Warehouse",
                "picked_qty": 0,
                "status": "pending"
            }
        ]
    }
}

POST /api/orders/:id/ship​

Mark order as shipped.

Request:

{
    "trackingNumber": "FEDEX123456789"
}

Response:

{
    "success": true,
    "message": "Order shipped",
    "order_id": 42,
    "netsuite_id": 12345,
    "tracking_number": "FEDEX123456789",
    "carrier": "FedEx",
    "shipped_at": "2024-03-16T14:30:00Z"
}

GET /api/rates​

Get exchange rates (for Pull In pattern).

Response:

{
    "success": true,
    "base": "USD",
    "date": "2024-03-15",
    "rates": {
        "EUR": 0.92,
        "GBP": 0.79,
        "SGD": 1.35,
        "IDR": 15650
    }
}

GET /api/stock/:sku​

Get stock availability.

Response:

{
    "success": true,
    "sku": "PAPER-A4",
    "inventory": {
        "available": 150,
        "on_hand": 200,
        "on_order": 50,
        "warehouse": "Main Warehouse"
    },
    "last_updated": "2024-03-15T10:00:00Z"
}

Testing with cURL​

# Set your worker URL
API_URL="https://quickmart-warehouse-api.your-subdomain.workers.dev"
API_KEY="qm-warehouse-test-key-2024"

# Test: Push order from "NetSuite"
curl -X POST "$API_URL/api/orders" \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $API_KEY" \
  -d '{
    "nsInternalId": 12345,
    "orderId": "SO-00567",
    "customer": "ABC Corporation",
    "priority": true,
    "lines": [
      {"item": "Paper A4", "sku": "PAPER-A4", "quantity": 20}
    ]
  }'

# Test: List orders
curl "$API_URL/api/orders" -H "X-API-Key: $API_KEY"

# Test: Get exchange rates
curl "$API_URL/api/rates" -H "X-API-Key: $API_KEY"

# Test: Get stock
curl "$API_URL/api/stock/PAPER-A4" -H "X-API-Key: $API_KEY"

Next Steps​