Ensuring Accurate Product Listings: A Deep Dive into ESHOPMAN API Pagination

Understanding ESHOPMAN API Pagination Challenges

As an e-commerce expert at Move My Store, we frequently encounter scenarios where data integrity is paramount. For ESHOPMAN users leveraging its powerful Admin and Store APIs for storefront management within HubSpot CMS, accurate data retrieval is crucial. A recent community insight highlights a significant challenge in offset-based pagination that can lead to skipped or duplicated records, particularly when sorting by non-unique fields like created_at or updated_at.

This issue affects how ESHOPMAN lists products and other resources, potentially impacting the reliability of your HubSpot-deployed storefronts and custom integrations. Understanding this behavior is key to building robust and accurate headless commerce solutions with ESHOPMAN.

The Pagination Pitfall: Non-Unique Sort Fields

When you paginate through a list of items using limit and offset parameters, and explicitly sort the results by a field that isn't unique (such as created_at or updated_at), ESHOPMAN's APIs can return an incomplete or inconsistent dataset. This means some product IDs might be missing from your paginated traversal, while others appear multiple times across different pages, even though the total count reported by the API is correct.

This scenario is common when products are created in batches, for instance, during an import process or a bulk creation request via the ESHOPMAN Admin API. In such cases, multiple products can share the exact same created_at timestamp, leading to ambiguity in sorting.

Why This Happens: Database Ordering

The root cause lies in how databases like PostgreSQL handle sorting with non-unique values. When multiple rows have identical values for the specified sort key, PostgreSQL does not guarantee a stable or consistent order among those rows across separate LIMIT and OFFSET queries. Without an additional unique tie-breaker (like a primary key), the database might return these equally-sorted rows in a different order on subsequent page requests, causing records to be missed or duplicated.

Impact on ESHOPMAN Development and Storefronts

• Inaccurate Storefront Displays: If your HubSpot CMS storefront relies on paginated product listings sorted by created_at, customers might see an incomplete catalog or duplicate items.
• Data Synchronization Issues: Integrations built to synchronize data from ESHOPMAN could miss records, leading to inconsistencies in external systems.
• Complex Workarounds: Developers might need to implement complex client-side logic to deduplicate or re-verify records, adding unnecessary overhead.

Currently, the ESHOPMAN Admin and Store APIs, when an explicit order is provided, do not automatically append a unique tie-breaker (like the product id). While ESHOPMAN defaults to ordering by the primary key (id) when no explicit order is given, overriding this default with a non-unique field introduces the problem. Attempts to specify multiple sort fields (e.g., order=created_at,id) are currently rejected by the API.

Demonstrating the Issue

Consider the following Node.js script that simulates creating products in a batch via the ESHOPMAN Admin API and then paginating through them. This reproduction clearly shows how ordering by created_at can lead to missing records, while ordering by id ensures a complete set.

const base = process.env.ESHOPMAN_URL ?? "http://localhost:9000"
const key = process.env.ESHOPMAN_SECRET_API_KEY
if (!key) throw new Error("Set ESHOPMAN_SECRET_API_KEY")
const auth = "Basic " + Buffer.from(`${key}:`).toString("base64")
const api = async (path, init = {}) => {
  const res = await fetch(base + path, {
    ...init,
    headers: { authorization: auth, "content-type": "application/json", ...init.headers },
  })
  if (!res.ok) throw new Error(`${init.method ?? "GET"} ${path}: ${res.status} ${await res.text()}`)
  return res.json()
}

const N = 60
const PAGE = 7
const run = Date.now()

// Products created in one batch request can share created_at values.
const { created } = await api("/admin/products/batch", {
  method: "POST",
  body: JSON.stringify({
    create: Array.from({ length: N }, (_, i) => ({
      title: `order-repro ${run} ${i}`,
      options: [{ title: "Size", values: ["One"] }],
      variants: [{ title: "One", options: { Size: "One" }, prices: [] }],
    })),
  }),
})
const ids = created.map((p) => p.id)

let failure
try {
  console.log(`created ${ids.length} products, distinct created_at values: ${new Set(created.map((p) => p.created_at)).size}`)
  const idFilter = ids.map((id) => `id[]=${id}`).join("&")
  for (const order of ["created_at", "-created_at", "id"]) {
    const seen = []
    for (let offset = 0; offset < N; offset += PAGE) {
      const page = await api(`/admin/products?${idFilter}&fields=id&order=${order}&limit=${PAGE}&offset=${offset}`)
      if (page.count !== N) throw new Error(`count ${page.count}, expected ${N}`)
      seen.push(...page.products.map((p) => p.id))
    }
    const seenSet = new Set(seen)
    const missing = ids.filter((id) => !seenSet.has(id)).length
    console.log(`order=${order.padEnd(11)} returned=${seen.length} unique=${seenSet.size} missing=${missing}`)
  }
} catch (error) {
  failure = error
} finally {
  try {
    await api("/admin/products/batch", { method: "POST", body: JSON.stringify({ delete: ids }) })
    console.log(`soft-deleted ${ids.length} products`)
  } catch (cleanupError) {
    console.error(`cleanup failed: ${cleanupError.message}`)
  }
}
if (failure) throw failure

The output from such a script consistently shows that sorting by created_at results in missing products, whereas sorting by id returns all expected records.

Recommendations for ESHOPMAN Developers

Until ESHOPMAN APIs are updated to automatically handle tie-breaking or allow multi-field sorting, we recommend the following best practices for your headless commerce solutions:

• Prioritize Unique Fields for Sorting: Whenever possible, use unique fields like id for pagination ordering. This guarantees consistent and complete results.
• Be Aware of created_at/updated_at Limitations: If your application absolutely requires sorting by created_at or updated_at, be mindful of the potential for skipped or duplicated records. Consider alternative pagination strategies (like cursor-based pagination, if supported in future ESHOPMAN API versions) or implement client-side deduplication logic with caution.
• Advocate for API Enhancements: The ideal solution involves ESHOPMAN's APIs automatically appending a unique tie-breaker (e.g., id) to any explicit sort order, or allowing developers to specify multiple sort fields (e.g., order=created_at DESC, id DESC).

Conclusion

Maintaining data integrity is fundamental for any e-commerce platform, especially for headless solutions like ESHOPMAN that power dynamic HubSpot CMS storefronts. By understanding the nuances of API pagination with non-unique sort fields, ESHOPMAN developers can proactively build more resilient and accurate applications, ensuring a seamless experience for both merchants and customers.