Phantom Orders: Your Upsert-Only Sync Never Deletes Anything

This one is hard to spot because there is nothing to spot. Open any individual order and it's perfectly well-formed: real buyer, real line items, real total, sensible dates. Nothing is corrupt. Nothing errors. The sync reports success every single run.

The only symptom is that your totals sit slightly above the marketplace's, and the gap grows very slowly over months.

The cause is structural. Marketplace syncs are written as upserts — insert if new, update if seen — which means they have no mechanism for removal at all. An upsert can tell you an order exists. It can never tell you an order stopped existing. So anything that disappears on the marketplace side stays in your database permanently, and every count, sum and dashboard built on that table is quietly wrong.

I call these phantom orders: rows that were real when you fetched them and aren't real any more.

A typical incremental sync asks "what changed since my last run?" and writes what comes back. That question is answerable. The question it never asks is "what did I have that you no longer have?" — and there is usually no API that answers it either.

Which is why this survives so long. It isn't a bug in your sync — your sync is doing exactly what it was written to do. It's a missing capability that nobody specified, because "orders don't disappear" feels like a safe assumption right up until you check.

Orders vanish upstream more often than you'd expect, and for boring reasons:

• Removed or voided receipts. The most literal case: you fetch a receipt successfully, and weeks later fetching the same ID returns 404. It's gone on their side.
• Orders that never became real. Payment never cleared, or the order was abandoned in a pending state. Some marketplaces expose these briefly and then drop them.
• Test and internal orders that get purged.
• Anything you created that the marketplace didn't. Manual orders, imports, seeded fixtures that outlived their welcome. Not phantoms in the strict sense, but they distort the same totals and deserve the same audit.

There's also a close cousin worth checking at the same time: orders that still exist but whose totals are stale or zero. An order pulled at the moment it was created can carry no total, and if nothing ever re-reads it, that zero is permanent. It isn't a phantom, but it fails the same "does my data still match theirs?" test and is worth fixing in the same sweep.

Since the sync can't tell you, you have to ask directly: take orders you believe exist and fetch them by ID.

The critical discipline is not acting on a single failed fetch. A 404 can mean "this is gone" — or it can mean a transient upstream error, a partial outage, an expired token producing a misleading status, or a bug in your own ID handling. Retire on the first 404 and one bad afternoon will retire thousands of perfectly real orders. That's a far worse outcome than the phantoms you were trying to remove.

# Returns False only when we are confident the order is gone.
async def verify_still_exists(client, order) -> bool:
    try:
        await client.get_order(order.external_id)
        return True
    except NotFound:
        # A single 404 is a hypothesis, not a verdict. Confirm on a
        # later run before acting on it.
        order.missing_upstream_count += 1
        return order.missing_upstream_count < 2
    except (RateLimited, ServerError, Timeout):
        # Never counts as evidence of absence.
        return True

Three rules that make this safe:

• Only a definitive not-found counts. Throttling, timeouts and 5xx must never be treated as absence — that's the same reasoning error as concluding a service is down because your request failed.
• Require confirmation across separate runs, hours apart. A phantom stays a phantom; a blip doesn't.
• Scope the check. Re-fetching every order forever is expensive and pointless. Restrict it to a window that matters — recent enough to still be queryable, old enough to have settled.

When you're confident, do not delete the row. Mark it.

ALTER TABLE orders ADD COLUMN retired_at TIMESTAMP NULL;
ALTER TABLE orders ADD COLUMN retired_reason TEXT NULL;

Then exclude retired rows from every count, sum and dashboard, and keep them visible in an admin view. The reasons are practical:

• You will be wrong sometimes, and a soft retire is reversible where a delete is not.
• Orders have children — line items, shipments, labels, invoices, audit events. Hard-deleting the parent either cascades destruction or orphans records.
• Someone will ask. "Where did order #4883 go?" has a real answer if the row still exists with a timestamp and a reason on it.
• Retirements are themselves a signal. A sudden spike in retirements means something changed upstream — or that your detection logic broke. You can only see that if you're recording them.

While you're here, check which timestamp your incremental sync filters on, because there's a closely related class of drift.

If you pull on creation date, your window only ever contains new orders. An order created five weeks ago that gets cancelled, refunded or edited today never re-enters the window, so its old state is frozen in your database forever. Same visible symptom as phantoms: totals that don't match, no errors anywhere.

Pull on last-modified date instead. On Amazon's Orders API that means LastUpdatedAfter rather than CreatedAfter; on eBay's Fulfillment API it's filter=lastmodifieddate:[…] instead of creationdate. Late cancellations, refunds and buyer edits then re-enter the window automatically, which fixes an entire family of staleness bugs for one parameter change.

It's the cheapest correctness fix in a marketplace integration, and it pairs naturally with phantom retirement: one keeps existing orders current, the other removes ones that stopped existing.

The instinct after finding phantoms is to write a cleanup script, run it, and move on. That fixes today and guarantees a repeat, because the structural gap is still there.

Turn it into something that runs on a schedule and reports:

• A nightly check comparing your active order count per account against the marketplace's own count for the same window. That difference is the number you actually care about, and it should be near zero.
• An alert on change, not on state. Alerting whenever the gap is non-zero trains everyone to ignore it. Alert when it grows, or when retirements spike.
• Reconciliation as a first-class job, not a script someone remembers. If it isn't scheduled, it doesn't exist.

The general principle: check invariants, not known bugs. "My order count matches theirs" catches phantoms, and it also catches the next three bugs of this class that you haven't thought of yet — because it asserts what must be true rather than testing for what went wrong last time.

What is a phantom order?

A row in your database for an order that no longer exists on the marketplace. It was real when you fetched it, and was later removed, voided or purged upstream. Because marketplace syncs are upsert-only, nothing ever removes it locally.

Why doesn't my order sync delete removed orders?

Because incremental syncs ask "what changed since my last run" and apply creates and updates. Deletion is not reported by that question, and most marketplace APIs offer no "what did you remove" endpoint, so an upsert-only sync structurally cannot observe it.

How do I detect orders that were deleted on the marketplace?

Re-fetch known orders by ID and treat a definitive not-found as evidence. Never act on a single 404 — require confirmation on a later run, and never count throttling, timeouts or 5xx errors as absence.

Should I delete phantom orders from my database?

No. Mark them retired with a timestamp and reason, and exclude them from counts and dashboards. Soft retirement is reversible, preserves child records like line items and shipments, and lets you answer questions about the order later.

Should I sync on created date or last-modified date?

Last-modified. Filtering on creation date means orders that change after creation — cancellations, refunds, buyer edits — never re-enter your sync window and stay frozen at their original state.