Developer Handover Pack

Developer Handover Pack

Returns and Reverse Logistics Automation

Captures every new return request from the Shopify returns portal, evaluates it against the configured eligibility rules (return window, product type, sale/bundle exclusions), logs the approved return as a new row in Google Sheets, and creates a prepaid return label in ShipStation. Handles the first four manual steps in full for all qualifying returns without any staff involvement. Non-qualifying requests are written to the sheet with status 'Exception' and routed for manual review.

// Input
shopify.return.id          // Shopify return ID
shopify.order.id           // Parent order ID
shopify.order.created_at   // Original purchase date (for window check)
shopify.order.email        // Customer email address
shopify.return.reason      // Reason code from portal
shopify.line_items[]       // Returned line items with SKU and quantity
shopify.customer.address   // Shipping address for label creation

// Eligibility check logic
return_window_days: 30     // Configurable policy rule
excluded_types: ['sale', 'bundle', 'digital']  // Configurable exclusion list

// Output (qualifying return)
sheets.row.order_id        // Written to Google Sheet column A
sheets.row.customer_email  // Written to Google Sheet column B
sheets.row.reason_code     // Written to Google Sheet column C
sheets.row.status          // 'Label Sent' or 'Exception'
sheets.row.timestamp       // ISO 8601 timestamp
shipstation.return_shipment_id  // ShipStation shipment record ID
shipstation.label_url      // Prepaid return label download URL

// Output (non-qualifying return)
sheets.row.status          // 'Exception — Manual Review'
sheets.row.exception_reason // e.g. 'Outside return window' or 'Excluded item type'

Sends the return label to the customer via a templated Gmail message as soon as the Returns Intake Agent produces a label URL. Then monitors the ShipStation tracking feed for the inbound shipment. When ShipStation marks the shipment as delivered, this agent posts a structured Slack alert to the designated warehouse channel, including the order number, return ID, and a link to the inspection outcome form. This agent eliminates the manual label email and the periodic ShipStation polling that currently consumes 12 minutes per return.

// Input (label dispatch)
shipstation.label_url       // From Returns Intake Agent output
shopify.order.id            // For reference in email body
shopify.customer.email      // Recipient address
shopify.return.reason       // Included in email body for context

// Output (label dispatch)
gmail.message_id            // Sent message ID for audit log
sheets.row.label_sent_at    // Timestamp written back to tracking sheet

// Input (delivery monitoring)
shipstation.tracking_event.status   // 'Delivered' triggers warehouse alert
shipstation.tracking_event.timestamp // Delivery confirmed datetime
shipstation.return_shipment_id       // Matched to open return record

// Output (warehouse alert)
slack.message.channel       // #warehouse-returns (or configured channel name)
slack.message.order_id      // Shopify order ID in message text
slack.message.return_id     // ShipStation return shipment ID
slack.message.inspection_form_url   // Link to Google Form or Typeform for inspection outcome
sheets.row.status           // Updated to 'Awaiting Inspection'

Triggered when a warehouse operative submits the inspection outcome form linked from the Slack alert. Reads the inspection result and applies the correct refund type in Shopify, adjusts the inventory level for the returned SKU based on the outcome (resalable restocks, damaged or supplier-return does not), creates a matching credit note in Xero, and sends the customer a confirmation email via Gmail. All status fields in the Google Sheet are updated to reflect completion. This agent replaces five manual steps and eliminates the primary source of month-end reconciliation errors between Shopify and Xero.

// Input
form.order_id               // Shopify order ID from inspection form
form.return_id              // ShipStation return shipment ID
form.inspection_result      // 'Resalable' | 'Damaged' | 'SupplierReturn'
form.refund_recommendation  // 'Full' | 'Partial' | 'None'
form.partial_amount_usd     // Only present when recommendation is 'Partial'
form.operative_name         // For audit log

// Shopify refund logic
// Full: refund 100% of line item total + shipping if applicable
// Partial: refund form.partial_amount_usd against the order
// None: no refund issued; sheet updated to 'Write-Off'

// Inventory adjustment logic
// Resalable: increment inventory_quantity for SKU at default location
// Damaged or SupplierReturn: do not adjust inventory; flag for warehouse review

// Output (Shopify)
shopify.refund.id           // Created refund record ID
shopify.refund.amount_usd   // Actual refund amount processed
shopify.inventory.updated   // Boolean — true if stock adjusted

// Output (Xero)
xero.credit_note.id         // Created credit note ID
xero.credit_note.amount_usd // Must match shopify.refund.amount_usd exactly
xero.credit_note.reference  // Shopify order ID as Xero reference field
xero.credit_note.contact    // Matched by customer email to existing Xero contact

// Output (Gmail)
gmail.confirmation.message_id  // Sent confirmation email message ID

// Output (Google Sheets)
sheets.row.status           // 'Complete' | 'Write-Off' | 'Refund Error'
sheets.row.refund_amount    // Final refund amount
sheets.row.xero_credit_note_id // For reconciliation audit trail
sheets.row.completed_at     // ISO 8601 timestamp

// On approval (partial or exception path)
// If form.refund_recommendation is 'None', no Shopify refund is created.
// Sheet status is set to 'Write-Off' and an internal Slack message
// is posted to notify the customer service rep to close the case.

// =========================================================
// TRIGGER: Shopify returns portal submission
// =========================================================
SHOPIFY_WEBHOOK :: orders/returns/create
  -> return.id              // e.g. 'ret_9182736450'
  -> order.id               // e.g. 'ord_1234567890'
  -> order.created_at       // ISO 8601 original purchase date
  -> order.email            // 'customer@example.com'
  -> return.reason          // e.g. 'defective' | 'wrong_item' | 'no_longer_needed'
  -> line_items[].sku       // e.g. 'SKU-ABC-001'
  -> line_items[].quantity  // e.g. 1
  -> customer.address       // Full shipping address object

// =========================================================
// AGENT 1: Returns Intake Agent
// =========================================================

// Step 1: Eligibility check against policy rules
SHOPIFY_READ :: GET /admin/api/2024-01/orders/{order.id}.json
  <- order.created_at       // Compare to NOW() minus return_window_days (30)
  <- line_items[].product_type  // Check against excluded_types[]

IF eligibility == false:
  SHEETS_APPEND :: status = 'Exception — Manual Review'
                   exception_reason = '<reason string>'
  HALT — exception path, human review required

// Step 2: Log qualifying return in Google Sheets
SHEETS_APPEND :: {
  col_A: order.id,
  col_B: order.email,
  col_C: return.reason,
  col_D: status = 'Processing',
  col_E: timestamp = NOW()
}
  -> sheets.row_index       // Row number for later updates

// Step 3: Create return shipment in ShipStation
SHIPSTATION_POST :: POST /shipments/createreturn
  payload: {
    orderNumber: order.id,
    customerEmail: order.email,
    shipTo: customer.address,
    carrierCode: 'usps',       // Configurable default carrier
    serviceCode: 'usps_first_class_mail'  // Configurable default service
  }
  <- shipstation.return_shipment_id
  <- shipstation.label_url

SHEETS_UPDATE :: row_index -> col_F: shipstation.return_shipment_id
                              col_G: shipstation.label_url
                              col_D: status = 'Label Created'

// =========================================================
// HANDOFF: Returns Intake Agent -> Dispatch and Monitoring Agent
// Context passed: order.id, order.email, return.reason,
//   shipstation.return_shipment_id, shipstation.label_url,
//   sheets.row_index
// =========================================================

// AGENT 2: Dispatch and Monitoring Agent

// Step 4: Send label email to customer via Gmail
GMAIL_SEND :: {
  to: order.email,
  subject: 'Your return label for order {order.id}',
  body: template('label_email', {label_url, order_id, reason})
}
  <- gmail.message_id

SHEETS_UPDATE :: row_index -> col_H: gmail.message_id
                              col_I: label_sent_at = NOW()
                              col_D: status = 'Label Sent'

// Step 5: Monitor ShipStation for delivery event
SHIPSTATION_WEBHOOK :: shipment_delivered
  filter: shipment_id == shipstation.return_shipment_id
  <- tracking_event.status      // 'Delivered'
  <- tracking_event.timestamp   // Delivery confirmed datetime

// Step 6: Post warehouse alert in Slack
SLACK_POST :: channel = '#warehouse-returns'
  payload: {
    text: 'Return delivered for order {order.id}. Please inspect and submit outcome.',
    inspection_form_url: 'https://forms.example.com/inspection?order_id={order.id}',
    return_id: shipstation.return_shipment_id
  }

SHEETS_UPDATE :: row_index -> col_D: status = 'Awaiting Inspection'
                              col_J: delivered_at = tracking_event.timestamp

// =========================================================
// HUMAN STEP: Warehouse operative inspects goods and submits form
// Form fields: order_id, return_id, inspection_result,
//   refund_recommendation, partial_amount_usd (conditional),
//   operative_name
// =========================================================

// HANDOFF: Dispatch and Monitoring Agent -> Refund and Reconciliation Agent
// Trigger: inspection form submission webhook fires
// Context passed: form.order_id, form.return_id,
//   form.inspection_result, form.refund_recommendation,
//   form.partial_amount_usd, sheets.row_index
// =========================================================

// AGENT 3: Refund and Reconciliation Agent

// Step 7: Issue refund in Shopify
IF refund_recommendation == 'Full':
  SHOPIFY_POST :: POST /admin/api/2024-01/orders/{order.id}/refunds.json
    payload: { refund_line_items: line_items[], shipping: true }
    <- shopify.refund.id
    <- shopify.refund.amount_usd

IF refund_recommendation == 'Partial':
  SHOPIFY_POST :: POST /admin/api/2024-01/orders/{order.id}/refunds.json
    payload: { amount: form.partial_amount_usd }
    <- shopify.refund.id
    <- shopify.refund.amount_usd

IF refund_recommendation == 'None':
  SHEETS_UPDATE :: col_D: status = 'Write-Off'
  SLACK_POST :: #cs-team alert: 'No refund issued for order {order.id}. Close case.'
  HALT

// Step 8: Adjust inventory in Shopify
IF inspection_result == 'Resalable':
  SHOPIFY_POST :: POST /admin/api/2024-01/inventory_levels/adjust.json
    payload: { inventory_item_id, location_id, available_adjustment: +quantity }
    <- shopify.inventory.updated = true

IF inspection_result IN ['Damaged', 'SupplierReturn']:
  shopify.inventory.updated = false  // No adjustment; flagged in sheet

// Step 9: Create credit note in Xero
XERO_LOOKUP :: GET /api.xro/2.0/Contacts?where=EmailAddress='{order.email}'
  <- xero.contact.ContactID   // Use existing or create new contact

XERO_POST :: POST /api.xro/2.0/CreditNotes
  payload: {
    Type: 'ACCREC',
    Contact: { ContactID: xero.contact.ContactID },
    Date: TODAY(),
    Reference: order.id,
    LineItems: [{ Description: 'Return refund — {order.id}',
                  Quantity: 1,
                  UnitAmount: shopify.refund.amount_usd,
                  AccountCode: '200' }]
  }
  <- xero.credit_note.id
  <- xero.credit_note.amount_usd   // Must == shopify.refund.amount_usd

// Step 10: Send refund confirmation email to customer
GMAIL_SEND :: {
  to: order.email,
  subject: 'Your refund for order {order.id} has been processed',
  body: template('refund_confirmation', {refund_amount, order_id})
}
  <- gmail.confirmation.message_id

// Step 11: Final Google Sheets update
SHEETS_UPDATE :: row_index -> {
  col_D: status = 'Complete',
  col_K: shopify.refund.id,
  col_L: shopify.refund.amount_usd,
  col_M: xero.credit_note.id,
  col_N: gmail.confirmation.message_id,
  col_O: completed_at = NOW()
}

// =========================================================
// END OF AUTOMATED FLOW
// Remaining human steps: physical inspection (Step 7 in process)
// and manual exception review for non-qualifying returns
// =========================================================