Troubleshooting ESHOPMAN Store API Returns: Understanding 'Operator Does Not Exist' Errors with Multiple Return Reasons

As an e-commerce migration expert at Move My Store, we understand the critical role of robust API functionality for your ESHOPMAN headless commerce platform. ESHOPMAN, designed as a HubSpot application for seamless storefront management and HubSpot CMS deployment, relies on its Admin API and Store API for all core operations. Recently, a specific issue surfaced within the ESHOPMAN Store API that developers and merchants should be aware of, particularly when managing customer returns.

Understanding the ESHOPMAN Store API Return Creation Issue

A notable bug has been identified when attempting to create returns via the ESHOPMAN Store API's POST /store/returns endpoint. This issue specifically manifests when the return request includes multiple items, each with an associated reason_id. While creating a return for a single item with a reason works as expected, adding multiple items with distinct return reasons leads to a server error.

The error encountered is a PostgreSQL DriverException, stating: operator does not exist: character varying = record. This indicates a fundamental type mismatch within the database query generated by the ESHOPMAN backend.

The Problematic Request Payload

Consider a typical request payload that triggers this error:

{  
"order_id": "order_01KGMM0YSXB9KP4HDKHSQT3D0H",  
"return_shipping": {"option_id": "so_01K9W841ASZR26WR4ZPH2RP3Y1"  },
"items": [
             { "id": "ordli_01KGMM0YSZDYMM8TEJ6AKCKADS", "quantity": 1, 
                "reason_id": "rr_01KGHXZK38K7G54VE117STN3E5"    },
             { "id": "ordli_01KGMM0YSZ76XR52MJZTRSYQ6J", "quantity": 1, 
                "reason_id": "rr_01KGHXYNA9WNGSNK7R28BFCMQZ"    }  
            ]
}

The Root Cause: Malformed SQL Query

The core of the problem lies in how the ESHOPMAN backend constructs the SQL query for the IN clause when fetching return reasons. Instead of generating a flat list of values like IN ('id1', 'id2'), the system produces a malformed structure with double parentheses, such as IN (('id1', 'id2')).

PostgreSQL interprets ('id1', 'id2') as a record type (or a tuple), which is incompatible with a column defined as character varying (a string type). This type mismatch is what causes the operator does not exist error.

An example of the problematic generated SQL snippet is:

WHERE "r0"."deleted_at" IS NULL  AND "r0"."id" IN (('rr_01KGHXZK38K7G54VE117STN3E5', 'rr_01KGHXZK38K7G54VE117STN3E5'))

Key Observations for ESHOPMAN Developers

• Double Parentheses: The primary issue is the incorrect nesting of the IN clause values, leading to a record type interpretation.
• ID Duplication: Interestingly, the generated SQL may also show duplication of reason_id values within the malformed tuple, even if the original payload contained distinct IDs. This suggests an underlying issue in the collection or aggregation logic for these IDs.
• Reproducibility: This bug is consistently reproducible when multiple items with reason_id are included in a single POST /store/returns request.
• Environment: This behavior has been observed in ESHOPMAN environments running Node.js v24.1 and PostgreSQL 16 on Ubuntu 22.

What This Means for Your ESHOPMAN Storefront

For ESHOPMAN users and developers managing storefronts deployed via HubSpot CMS, this bug can disrupt automated return processes involving multiple items. While a direct workaround for this specific issue wasn't provided in the initial discussion, understanding the precise nature of the error (malformed SQL and type mismatch) is crucial for debugging, reporting, and potentially implementing temporary client-side logic to process returns one item at a time if immediate resolution is critical.

The expected behavior for the POST /store/returns endpoint is a successful creation of the return, returning a 200 or 201 HTTP response with the Return object. Until a fix is deployed, developers should be mindful of this limitation when designing return workflows.

Staying informed about such technical insights from the ESHOPMAN community is vital for maintaining a robust and efficient headless commerce setup. The Move My Store team encourages active participation in the ESHOPMAN ecosystem to share knowledge and contribute to platform enhancements.