Where Amazon connectors stop: FBA inventory, FBM listings, and settlement reconciliation

The parts that are supposed to be boring

Order ingestion and returns are table stakes. They should work, they should be idempotent, and they should not be interesting. Ours are not, and that is deliberate.

Orders are pulled on a schedule with pagination, filtered to shipped orders in a rolling window, and deduplicated before any record is written by checking whether a sales order already carries that Amazon order ID. FBM orders create a sales order and stop, waiting for a real warehouse fulfillment. FBA orders create the sales order, then transform it straight into an item fulfillment and an invoice, because on FBA the goods left an Amazon fulfillment center before you ever heard about the order. Returns come from a report, deduplicate on order plus SKU because partial returns of multi-line orders are legitimate, and land as a return authorization plus an item receipt back into FBA stock.

That is the standard shape of an Amazon integration, and a connector will do it. Everything below is where connectors run out of road.

Amazon does not tell you how much stock you have. It tells you how much stock is in each of a dozen states, nested several levels deep, and the states matter: inventory sitting in an inbound shipment is not the same asset as inventory reserved against a customer order, which is not the same as inventory a fulfillment center has marked damaged.

Collapsing that into a single on-hand number throws away the distinction your finance team needs. So the integration maps Amazon's buckets one-to-one onto NetSuite inventory statuses.

That last row is doing more work than it looks. Amazon splits damaged stock across several fields: customer damaged, warehouse damaged, distributor damaged, carrier damaged. Rather than enumerate them, the integration sums every key whose name contains "damaged." When Amazon adds a fifth damage category next year, and it will, the sync picks it up without a code change.

One item, two conditions

The client sells refurbished fixtures alongside new ones. Amazon models that as separate listings with SKUs in a distinctive shape, and the integration extracts the underlying SKU with a regular expression while flagging the listing as used.

That single flag then drives everything downstream. A used listing's fulfillable stock collapses into a Used status rather than Good, its reserved stock collapses into a separate Used unavailable status, and the standard buckets are zeroed out for that row. The result is one physical NetSuite item carrying two Amazon identities, separated only by inventory status, with no possibility of new and refurbished stock double-counting against the same item record.

A diff, not an overwrite

The naive version of an inventory sync overwrites NetSuite with whatever Amazon just said. That is destructive, it fights with every other process touching the same item, and running it twice in quick succession produces different results depending on timing.

Instead, the map stage resolves each Amazon SKU to a NetSuite item and emits everything under a single reduce key, so all items arrive in one reduce call. That stage merges rows per item, which is what handles a new SKU and a used SKU resolving to the same physical item. It then batch-queries current balances for every item at the FBA location in one search, computes the difference per status, and drops every line where the difference is zero.

What survives becomes a single inventory adjustment record containing every line for every item. One save instead of hundreds, which on a governance-metered platform is the difference between a job that completes and a job that does not.

The adjustment sets both the from and to status to the same value on each assignment line, which makes it an adjustment within a status rather than a transfer between them. And because the whole thing is expressed as a difference, running it twice is a no-op. Idempotency here is not a guard bolted on top; it falls out of the design.

The job runs every thirty minutes with a concurrency limit of one, on an incremental window by default, with a parameter to force a full sync when a reconciliation demands it.

The FBM side runs the opposite direction, and it has a harder failure mode. Get FBA inventory wrong and your reports are off. Get FBM availability wrong and you sell stock you do not have.

The buffer. The quantity pushed to Amazon is available quantity minus a configurable buffer, floored at zero. Between syncs, NetSuite and Amazon drift; orders land in the gap. The buffer is what absorbs that drift, and because it lives in configuration rather than code, the client can tune it against their own oversell tolerance without a deployment.

Many locations, many channels. FBM items can live in several NetSuite locations, and Amazon models fulfillment channels separately from your warehouses. The sync searches across every configured FBM location and keys by SKU, so the reduce stage receives all locations for one SKU together. A configured map from location to Amazon fulfillment channel code turns them into the availability array Amazon expects.

Locations without a channel mapping are logged and skipped rather than pushed as zero. That distinction matters: silently zeroing an unmapped location would delist real, sellable stock, and it would look exactly like a successful sync.

Supersession roll-up

Lighting catalogs supersede constantly. A fixture gets a revised driver or a new efficacy rating and becomes a different SKU, but the old listing still carries the reviews, the rank and the buyers who search for it by name.

Items can point at a replacement item, and the sync folds the successor's stock into the predecessor's listing quantity. The replacement quantities are batch-queried under the same eligibility rules as everything else, active, FBM-flagged, at an FBM location and in good status, and rolled up on a composite key of item and location so the roll-up never crosses a location boundary.

The practical effect is that a discontinued SKU keeps selling against its replacement's stock instead of going out of stock and losing its ranking.

Why changing one number is not one API call

Amazon's Listings API offers two ways to write availability, and the integration uses both, because they are not interchangeable.

Multi-channel updates go out as a partial patch against the fulfillment availability attribute. That path only needs the listing summary, and it always appends a default channel at quantity zero, because omitting a channel is not the same as setting it to zero and Amazon treats the difference as meaningful.

Single-quantity updates go out as a full replace. That path rewrites the entire listing, so the existing attributes have to be fetched and merged into the payload first, or the write succeeds and quietly strips everything it did not carry.

Both paths need the marketplace-specific product type resolved from the listing summary before Amazon will accept them at all. Which is why changing one number is a read, a resolve, and then a write.

Amazon does not send you a payment with an explanation. It sends a settlement report covering a period, containing every transaction that contributed to the deposit, and leaves the accounting to you.

The pipeline is: list available settlement reports, take the newest completed one, request its document, download from the pre-signed URL Amazon returns, and parse the tab-separated file.

Five amount columns, not one

The thing that makes this file hard is that it does not have an amount column. It has five, each paired with its own type column, and a single row may populate several of them at once.

Each pair runs through its own lookup table and accumulates into per-account totals. Those totals become journal entry lines, a positive net crediting the account and a negative net debiting it.

The clearing line is the whole trick. On top of the mapped accounts, the entry carries one line posted to an Amazon holding account, taken from the summary row of the report, the row with a blank transaction type. That line is what makes the entry balance, and more importantly it is what makes the holding account reconcile to zero when the deposit actually lands in the bank. Without it you have a journal entry full of correct numbers that does not tie to anything.

The scar tissue

Three details in this parser exist because reality is untidy.

The mapping tables carry two spellings of Amazon's marketplace facilitator tax types, one with a hyphen and one with an en dash, because Amazon is not consistent about which Unicode character it emits. That is a one-line fix that takes a day to find.

Certain transaction types are skipped outright, including internal balance transfers that would otherwise double-count against the deposit. And at least one transaction type has to be matched on the transaction type column rather than any type column, with a fallback across two possible amount columns, because it does not follow the file's own conventions.

Unmapped values are collected and logged rather than silently dropped. That log is the mechanism by which you discover Amazon has introduced a new fee type, usually before your accountant does.

The whole job is idempotent through a memo check: before creating anything, it searches for an existing journal entry carrying that settlement ID.

The architecture underneath all three

None of the above works without a few decisions made early.

Configuration as data

Every credential and behavior flag lives on a configuration record: refresh token, client ID, client secret, endpoint host, plus a settings payload holding marketplace and seller identifiers, region, order defaults, and the inventory settings each flow reads. Which config record a deployment uses is itself a script parameter, so the same codebase drives a different Amazon account or marketplace by pointing a deployment at a different record.

One token, not two hundred

Authentication is a refresh-token exchange returning a roughly one-hour access token. Naively, a Map/Reduce with two hundred map stages would fetch two hundred tokens.

Instead, configuration and token both live in a protected-scope cache shared across every script in the account, populated through a loader pattern so a cache miss fills itself atomically. A two hundred item sync makes one token call.

The part worth stealing is the negative caching. When a token request fails, the failure is cached with a short expiry. Without it, an Amazon outage turns every map stage into a retry against a service that is already down. With it, you get one retry every few minutes instead of one per stage.

Map/Reduce as governance architecture

On a platform that meters units rather than time, stage boundaries are the tool. Deduplication checks live in the map stage, which is cheap and parallel, so the reduce stage never touches work that is already done. The FBA sync deliberately funnels everything through a single reduce key to batch one record save. And because there is no cross-script scheduler, jobs chain each other in their summarize stage, so one scheduled run cascades through orders, returns and inventory in dependency order.

Alerting as a first-class concern

Failures that need a human go to the client's operations channel through their alerting tool's API: unknown SKUs holding real stock, missing order item identifiers, invalid item references during order creation, settlement failures, return processing failures. Everything else is logged.

Two details make it usable. Alert-worthy is deliberately distinguished from log-worthy, so the channel stays credible. And alerting no-ops outside production, so sandbox testing never pages anybody.

What we would change

Anything running in production long enough accumulates things you would do differently. Ours has four worth naming.

• A bad SKU is detected by string-matching NetSuite's error message. It works, and it will keep working until NetSuite rewords that error. It should be a validity check before the search, not a rescue after it.
• The same quantity computation is duplicated in a try branch and its catch. Ten lines, verbatim, in two places. It should be one function.
• Account lookup pulls a thousand accounts and filters client-side. The filter belongs in the search.
• Our pacing mechanism between paginated calls costs more than it should. It is the least elegant thing in the codebase, it exists because the platform offers no proper alternative, and it is first in line to be replaced when one appears.

There is also one inconsistency we have not resolved: the ignore list for known SKU mismatches is a hardcoded array in the FBA script, while everything comparable is driven from the configuration record. It should move.

What changed for the client

The brief was not a feature list. It was that the existing setup did not work correctly and the client wanted the process to be smooth. That is a legitimate specification, and it translates into something measurable: the integration should stop generating work.

It largely does. FBA inventory reconciles on a thirty minute cycle without anyone watching it, and because it is expressed as a difference rather than an overwrite, a cycle where nothing changed writes nothing at all. Amazon's quantity distinctions survive the trip, so stock in an inbound shipment, stock reserved against an order and stock a fulfillment center has damaged are three different things in NetSuite, as they are in reality.

FBM availability goes out across every mapped channel with a buffer in front of it, and locations without a channel mapping are surfaced rather than silently zeroed. Settlements arrive as a journal entry that ties to the deposit, with the holding account reconciling to zero once the money lands, and unmapped values logged so a new Amazon fee type shows up as a line in a log instead of an unexplained variance.

The errors that remain are the ones that should exist. An unknown SKU holding real stock is a genuine problem and it raises an alert. An unknown SKU holding no stock is a dead listing and it does not. Distinguishing between those two is most of what people mean when they say they want something to run smoothly.

What this adds up to

An Amazon integration is not one integration. It is six flows with different directions, different failure modes and different owners inside the business. Orders belong to operations, inventory to planning, settlements to finance, and none of them forgive a system that treats Amazon's data as simpler than it is.

Connectors handle the flow everyone demos. What is left is condition-graded stock that has to keep its identity, availability that has to be pushed conservatively across channels that do not match your warehouses, and a settlement file that has to become an entry your controller can tie to a bank deposit. That is the work, and it is where a client who has already bought a connector usually discovers the gap.

None of which makes connectors the wrong choice universally. They are the right answer for plenty of Amazon sellers, and we say so in our connector comparison. They were the wrong answer for this catalog, with refurbished stock, multi-location FBM, constant supersession and settlements that had to reconcile rather than import.