- A supplier sends the marketplace its weekly catalogue file.
- Some SKUs are new. Some describe products that already exist.
- Treating every row as an upsert would turn those distinctions into accidental catalogue changes.
- For a multi-vendor Medusa marketplace, we built a reconciliation step before mutation.
The client problem
A supplier sends the marketplace its weekly catalogue file.
Some SKUs are new. Some describe products that already exist. Several rows are identical to the current catalogue except for spacing. One SKU belongs to a product that was deliberately removed. Another collides with a different seller's identity. A zero price may mean “unavailable”, not “give this away”.
Treating every row as an upsert would turn those distinctions into accidental catalogue changes.
For a multi-vendor Medusa marketplace, we built a reconciliation step before mutation. The platform stages the complete feed, resolves identity in the current seller's context and classifies each row as create, update, no-op, restore, park, skip or fail. A shared comparator drives both the automated decision and the operator's side-by-side diff. Confirmed updates obey a captured field policy and never overwrite inventory.
The key design choice was to ask what a row means before deciding how to write it.
The client problem: a feed is not a command
Supplier files describe an external state. They do not necessarily express the action the marketplace should take.
A row with an existing SKU might contain a real price change, a rewritten description or no meaningful difference. A missing SKU may be a new product in a full catalogue, but an unexpected exception in a narrow update feed. A soft-deleted product may have been removed for a reason. A blank field might mean “preserve the current value”, “clear it” or “the source does not manage this field”.
Those semantics depend on the route and supplier contract.
A full catalogue import can introduce new draft products. A recurring delta feed may be authorised to change only price and description. It should not create unknown SKUs, restore removed products or reset stock because columns happen to exist.
The platform therefore separates source ingestion from catalogue authority.
Preserve every row in a durable review surface
The feed first becomes staged rows tied to an import record.
Each row retains its source position, complete canonical data, derived handle, seller-scoped SKU, status and error. This is the durable vocabulary of reconciliation.
The import can stop after staging and still be understood. Operators can count outcomes, inspect a row and resume processing without parsing the original file again. Automated validation can compare sets of rows with the catalogue before any product is created or changed.
Staging also prevents orchestration state from becoming the database. The workflow passes the import identity between steps; the rows and their decisions live in tables designed for querying, pagination and recovery.
The resulting audit trail answers not only “did the file run?” but “what did we decide about row 417, and why?”
Classify duplicates inside the file first
Before checking the live catalogue, the platform looks for duplicate handles and variant SKUs inside the incoming file.
Windowed database queries partition rows by identity and retain the earliest source row. Later duplicates become skipped with an explicit reason.
This is safer than allowing last-write-wins behaviour. If two rows disagree about price or description, their order in a supplier export should not silently decide the marketplace result. The operator can see that the source file contains an ambiguity.
It is also efficient. The database evaluates the file as a set instead of comparing every row with every other row in application memory.
Once intra-file ambiguity is removed, the remaining rows can be compared with existing seller products.
Match identity within the seller boundary
The same raw SKU can appear in more than one supplier catalogue.
The reconciliation queries therefore require the current seller relationship when identifying an active or deleted product. A matching key owned by another seller is a conflict, not permission to update that record.
For a full catalogue, identity can use the seller-scoped SKU and the canonical handle. For a narrow update feed, SKU is the only match key because that is the supplier's declared contract. A coincidental handle collision must not turn a genuinely unknown SKU into an update of another item.
This route-specific identity rule is important. Flexible matching may help onboarding, but it becomes dangerous when a recurring feed has authority to change existing products.
The system chooses the narrowest identity contract appropriate to the source.
Separate create, update and restore
After identity resolution, rows fall into different lifecycles.
A new row in a full catalogue remains pending for product creation. A row matching an active seller product becomes pending_update. A row matching a soft-deleted seller product can become pending_restore on the controlled catalogue path.
Those states are not immediately equivalent to writes.
Creation builds a new draft product graph. Update waits for the review or authorised automation policy. Restore reactivates the existing product and its connected variant, options, images, sales-channel, shipping-profile, seller, inventory and price relationships rather than creating a second identity.
On the narrow updates route, a soft-deleted SKU is skipped rather than resurrected. The source feed is not allowed to reverse an earlier catalogue-removal decision.
The route defines authority; the row does not acquire it by matching an old record.
Detect no-ops with the same comparator the operator sees
An existing SKU is not automatically an update.
Supplier exports often change formatting without changing meaning: extra whitespace, reordered specification text or a price rendered with different decimal formatting. Treating those as updates creates noise, unnecessary indexing and an unreliable audit trail.
The project builds a normalised “current” side from the live product and an “incoming” side from the staged row. A shared comparator evaluates title, description, price, brand, manufacturer, unit of measure and structured specifications using the route's field policy.
If no authorised field would change, the row becomes skipped with a clear “already up to date” reason.
The side-by-side diff endpoint uses the same comparator. What the review dialog highlights therefore matches what the classifier and confirmation path consider meaningful.
This removes a subtle but costly failure mode: an interface claiming a change that the writer ignores, or a writer changing a field the interface showed as equal.
Make rejection a first-class outcome
Some rows should not reach a review button.
An explicit zero price is skipped by import policy. A duplicate inside the file is skipped. A cross-seller identity conflict is skipped. A malformed required value can fail with a row-level reason.
These are not generic “import errors”. They are different business outcomes.
A zero price may signal missing supplier data and could otherwise create a product that appears free. A cross-seller conflict protects ownership. A duplicate asks the supplier to correct ambiguity. A parsing failure identifies source quality.
The review statistics preserve those categories separately. Operators can distinguish a clean no-op-heavy feed from a feed rejected because its commercial data is unsafe.
Useful automation does not maximise the number of written rows. It maximises the number of correct decisions.
Park unknown SKUs from delta feeds
A delta feed is designed to modify known products, not necessarily create new ones.
When the updates route encounters a SKU that does not exist for the seller, it stores the complete row in a dedicated pending-new-product table. The row is grouped by import and seller and can be reviewed or exported by an administrator.
It is not silently discarded, and it is not automatically published as an incomplete product.
In the evidenced baseline, this holding area is a view-and-export capability; it does not yet turn approval into product creation. That boundary is deliberate in the internal evidence. Publicly, the important product behaviour is that the delta source cannot expand the catalogue without a separate onboarding decision.
Unknown data remains visible without receiving unintended authority.
Capture the update policy with the import
Different suppliers own different fields.
One feed may be authoritative for title, description and price. Another may only update price. A marketplace team may curate brand and manufacturer centrally. Images may follow a separate controlled pipeline.
The updates route captures a field-policy allow-list in the upload session. Confirmation reads that snapshot rather than whatever policy happens to be configured later.
Only allowed fields participate in no-op classification and writing. Price updates run only when price is authorised. Brand, manufacturer, unit of measure, specifications and thumbnail changes are gated independently. Empty inputs use the defined preserve or clear semantics instead of one global rule.
This makes the import reproducible. The platform can explain which authority applied when the file arrived.
If the required policy snapshot is missing, confirmation refuses to proceed rather than interpreting the feed as fully authorised.
Keep inventory outside catalogue reconciliation
Confirmed catalogue updates intentionally do not touch stock levels.
Inventory changes at a different cadence and often arrives from a different source. A weekly content feed should not reset on-hand quantity because it contains an old stock column or leaves the field blank.
The new-product path establishes an inventory model because a new variant needs one. After that, dedicated inventory feeds own quantity changes.
This is another example of source authority. The current catalogue is not one undifferentiated object that every file may overwrite. Product content, price, inventory, media and publication have separate contracts.
That separation lets the marketplace accept more automation with less risk.
Confirm selected changes, then synchronise discovery
The review API can confirm selected update or restore rows, or all rows in the chosen pending state.
Confirmation rechecks seller ownership, applies the captured field policy and updates row statuses inside controlled transactions. A restore reactivates the connected product graph. An update changes only the authorised catalogue surfaces.
After reconciliation, the platform finalises import counts and queues the product-search synchronisation associated with that import.
Search is downstream of the confirmed catalogue decision. It does not index speculative changes merely because a supplier uploaded them.
This ordering protects visibility: classify first, review where required, write the canonical catalogue, then refresh its projection.
Give operators an outcome they can act on
The review endpoint reports totals for added, updated, restored, skipped and failed rows. Skips are broken down into no-op, zero price, duplicate, soft-deleted delta, cross-seller conflict, not confirmed and other reasons.
Rows are pageable and filterable. Existing-product candidates can show current and incoming values side by side. The operator can confirm a subset rather than accepting the file as one indivisible command.
That interface supports the real operational conversation:
- these products are new drafts;
- these prices genuinely changed;
- these rows were already current;
- these identifiers belong elsewhere;
- these unknown delta SKUs need onboarding;
- these rejected values need supplier correction.
The import becomes a decision workspace, not a spinner followed by a count.
A practical reconciliation checklist
Before applying a recurring supplier feed, ask:
- Is the complete incoming row preserved?
- Which key identifies a product for this route?
- Is identity constrained to the current seller?
- Are duplicates inside the same file detected before mutation?
- Can the system distinguish new, active and soft-deleted products?
- Is restore authority different from update authority?
- Are unknown delta SKUs parked rather than created?
- Does a normalised comparator detect meaningful changes?
- Does the review UI use the same comparator?
- Which fields is this supplier allowed to overwrite?
- Is that policy captured with the import?
- What does an empty value mean for each field?
- Are zero prices and cross-seller conflicts explicit outcomes?
- Are catalogue updates prevented from modifying inventory?
- Does search synchronisation happen only after confirmation?
- Can an operator explain every skipped row?
If one generic upsert answers these questions, it is probably answering them accidentally.
The broader lesson
Medusa gives us a canonical commerce model. Supplier feeds give us observations about external catalogues.
The reconciliation layer decides how much authority each observation deserves.
For this marketplace, durable staging preserves the source, seller-scoped identity protects ownership, route-specific rules separate full catalogues from deltas, one comparator aligns no-op detection with review, and field-policy snapshots constrain confirmed writes. Inventory and search remain behind their proper boundaries.
That architecture turns recurring files from a catalogue risk into an operable supply channel.
The safest import is not the one that writes every row. It is the one that can explain why each row was created, changed, ignored, parked or rejected.