Amazon Order IDs Are Not Unique (And It Will Corrupt Your Database)

You're syncing orders from SP-API into your own database. The order ID looks like a primary key, reads like a primary key, and is formatted like one:

111-1111111-1111111

So you do the obvious thing:

amazon_order_id = Column(String, unique=True, index=True)

That works perfectly for one seller account. It works perfectly for ten seller accounts, for months. Then a specific and rare thing happens, and you either start throwing integrity errors on insert, or — far worse — you silently overwrite one account's order with another's.

An Amazon order ID is unique per buyer order, not per seller. If a single buyer's cart contains items sold by two different merchants, both merchants see that same order ID for their own portion. Normally the two merchants are unrelated companies and nobody notices. But if you operate a portfolio of seller accounts — an agency, an aggregator, a brand running multiple storefronts — then both merchants can be you, and both rows land in the same table.

Two of your accounts, one buyer, one order ID, two genuinely different sets of line items:

Which outcome you get depends on how your sync writes:

• Plain insert → an integrity error. Loud, annoying, and by far the best case, because you find out immediately.
• Upsert on order ID → the second account's sync overwrites the first's row. Line items, totals, shipping address and account ownership all get replaced. No error, no log line, nothing to alert on.
• Lookup-then-update → your sync finds the *other* account's order, decides it already has it, and updates that row with this account's data. Same corruption, different route.

The upsert case is the dangerous one, and it's also the most common pattern in marketplace syncs.

Three things conspire to keep this invisible:

It's rare. It needs one buyer to buy from two of your specific accounts in one cart. Across a small portfolio that might be a handful of orders a year. Your test data will never contain one.

The damage looks like something else. A merged row shows up as an order with the wrong total, or line items the seller doesn't recognise, or an order attributed to the wrong storefront. That reads as a mapping bug, a currency bug, or "the marketplace data is weird" — not as a primary-key problem. People re-check their totals logic for days.

The corruption survives a re-sync. Because the constraint is still there, re-running the sync just reproduces the same overwrite. "Have you tried re-syncing?" doesn't fix it, which pushes suspicion even further from the real cause.

The correct grain is (platform, owning account, external order ID):

# Wrong — assumes the marketplace's id is globally unique
UniqueConstraint("external_order_id")

# Right — the same buyer order can legitimately appear
# once per seller account you operate
UniqueConstraint("platform", "seller_account_id", "external_order_id")

And then the part people forget: every lookup in the sync path must be scoped by the owning account too. Fixing the constraint but leaving a bare filter_by(external_order_id=...) in your upsert just converts silent corruption into an integrity error — better, but still broken.

# Wrong
existing = session.query(Order).filter_by(
    external_order_id=payload["AmazonOrderId"]
).one_or_none()

# Right
existing = session.query(Order).filter_by(
    platform="amazon",
    seller_account_id=account.id,
    external_order_id=payload["AmazonOrderId"],
).one_or_none()

Grep your codebase for every query that touches the external ID. In a mature sync there are usually more than you expect: the upsert, the tracking write-back, the refund handler, the admin search, the reconciliation job, the CSV export.

The underlying rule: an identifier issued by an external system is only unique within that system's own scope, and that scope is rarely the one you assumed. Once you're multi-account or multi-marketplace, treat every external ID as needing a scope column beside it.

• Across marketplaces, ID formats can collide outright — a numeric Etsy receipt ID and a numeric eBay order ID have no reason to stay distinct. That's what the platform column is for.
• SKUs are per-seller-account, not global. The same SKU string in two accounts is two different products.
• Etsy receipt IDs are per shop.
• Refund and transaction IDs inherit the scope of whatever issued them.

The cost of the scope column is one index. The cost of omitting it is a class of corruption that surfaces months later as unexplainable numbers — which is also how it interacts with reconciliation: if your dashboard totals don't match Seller Central, collisions are one of the candidates worth ruling out early.

Are Amazon order IDs unique?

They are unique per buyer order, not per seller. If one buyer's cart contains items from two different merchants, both merchants receive the same Amazon order ID for their own portion. So the ID is only unique within a single seller account, and a globally unique constraint across multiple accounts is unsafe.

What is the correct unique key for a multi-account Amazon order table?

The tuple of platform, owning seller account, and external order ID. Every lookup in the sync path must be scoped by the owning account as well, not just the constraint.

How often does an order ID collision actually happen?

Rarely — it needs one buyer to purchase from two of your own seller accounts in a single cart. That rarity is the problem: it never appears in test data and surfaces long after the schema is set, usually disguised as a totals or mapping bug.

Does re-syncing fix a collided order?

No. While the globally unique constraint is in place, re-running the sync reproduces the same overwrite. You need the schema change first, then a repair pass that re-pulls the affected orders per account.

Does this affect Etsy, eBay and Walmart too?

The same principle applies: every external identifier is scoped to the system that issued it. Etsy receipt IDs are per shop, and IDs from different marketplaces can collide in format. A platform column plus an owning-account column handles all of them.