- A supplier can sell the same product at several valid prices.
- There is the standard catalogue price.
- The client problem begins when a supplier sends a large CSV containing those negotiated prices.
- The tempting implementation is to find every SKU and replace its price.
The client problem
A supplier can sell the same product at several valid prices.
There is the standard catalogue price. There may be a negotiated price for a customer group, a contract price for selected properties or a temporary commercial agreement. All of them can be correct at the same time.
The client problem begins when a supplier sends a large CSV containing those negotiated prices.
The tempting implementation is to find every SKU and replace its price. It is also the fastest way to destroy the distinction between a standard price and a contextual price. The next catalogue synchronisation may overwrite the agreement. Another customer may see the wrong amount. An operator cannot explain which file changed which record.
For a multi-vendor procurement platform built on Medusa, we designed a different path.
The file targets an existing, supplier-owned Medusa price list. It does not redefine the standard catalogue. Rows are streamed into durable staging, validated against the supplier’s own products, resolved through indexed SKU matching and applied in bounded chunks. The operator can follow progress, inspect skipped rows, cancel the run and retain the original source.
We treated negotiated pricing as its own commercial lifecycle—not as a mass edit of product data.
Begin with the commercial contract
Before discussing CSV parsing, we needed to answer a more important question: what does the file mean?
In this journey, the supplier is not importing products. The products and variants already exist. The supplier is not replacing standard prices either. The file adds or updates amounts inside one existing price list.
That price list can then be attached to the customer or property groups for which the commercial agreement applies.
This boundary protects the catalogue’s price basis. A standard price remains the fallback commercial truth. A negotiated list becomes a deliberate override with its own identity, ownership and lifecycle.
It also makes the operator’s intention explicit. They choose the target price list before launching the import. The backend verifies that the authenticated supplier owns that list. A valid file cannot be redirected into another seller’s pricing context by changing an identifier in the request.
The first quality gate is therefore not “does this CSV parse?” It is “is this the correct supplier, target list and commercial operation?”
Upload first, process asynchronously
Large pricing files do not belong inside a long browser request.
The platform first uploads the source through a dedicated file session. Starting the import creates a tracked record and returns an acceptance response with an import identifier and a status URL. The heavy work continues through a Medusa workflow.
For the supplier, this means the dashboard remains usable. For the platform, it means the import has a durable identity independent of the original HTTP connection.
The record follows a clear lifecycle: pending review, processing, completed, failed or cancelled. It stores the target price list, source filename, row counters, timestamps and bounded processing errors.
That operating model matters as much as the transformation code. A pricing operation can take time, encounter imperfect data or be interrupted. The user needs to know whether the platform accepted the job, how far it progressed and what requires attention.
A 202 Accepted response is not presented as completion. It is the start of visible, recoverable work.
Stream the file instead of loading it into memory
The import reads the CSV as a stream from object storage.
Rows are normalised and accumulated only in a small buffer. Each buffer is inserted into a staging table in bulk, then released before the next rows arrive. The import therefore does not need to hold the complete supplier file in the application process.
The supplier can map its own column names to the two concepts the pricing workflow needs: SKU and amount. Headers are normalised for case, blank lines are ignored and malformed values are classified as skipped rows rather than allowed to contaminate later processing.
The original row is preserved alongside the canonical SKU and amount.
That detail is useful when a supplier asks why a record did not match. The system can show what arrived instead of reconstructing an explanation from logs. The source file remains downloadable through a seller-scoped route, and the staged rows can be paginated for review.
Staging turns an ephemeral upload into an inspectable import ledger.
Validate the whole dataset with set-based operations
Once the rows are staged, the platform validates them as a dataset.
Duplicate SKUs inside the same file are identified with a database window function. Empty SKUs and invalid amounts are marked as skipped. Unknown SKUs are found by comparing staged values with product variants belonging to the authenticated supplier.
This is more predictable than performing a product lookup, writing a price and recording an error for every row in one application loop.
The database is good at answering questions over sets:
- which SKU appears more than once?
- which amounts are missing or negative?
- which staged rows have no matching supplier variant?
- how many rows remain ready to process?
Each row receives a status and an explanation. The operator gets useful categories—matched, unmatched, skipped, failed and pending—instead of one vague import result.
The business value is straightforward: imperfect supplier data becomes a reviewable outcome, not an all-or-nothing surprise.
SKU matching must reflect the supplier’s catalogue
SKU resolution looks simple until real catalogues meet real files.
Capitalisation can differ. Marketplace systems may add a short seller suffix to maintain global uniqueness. The supplier’s source file may still contain the original base SKU. A naïve exact, case-sensitive comparison turns valid rows into “product not found.”
We implemented two seller-scoped matching paths.
The platform first attempts an exact case-insensitive SKU match. If that fails, it attempts a normalised match against a known seller-suffix pattern. In both cases, the product must belong to the supplier running the import.
The corresponding PostgreSQL expressions have dedicated partial indexes. That means the database can look up the normalised value instead of repeatedly scanning the whole variant table for every staged row.
We do not need a dramatic complexity claim to explain the benefit. The practical difference is that matching work follows indexed catalogue lookups and set-based validation rather than repeated in-memory catalogue scans.
The matcher is tolerant where the business contract permits it and strict about supplier ownership.
Apply prices in bounded chunks
After validation, the workflow processes only pending rows in bounded chunks.
For each chunk, it resolves the variant identifiers and loads the prices that already exist for the target price list. Rows are divided into two groups:
- create a price where the list has no amount for that variant;
- update the existing list price where one already exists.
Both operations run as set-based SQL inside the chunk transaction. An update targets the existing price identifier. An insert joins the variant to its Medusa price set and creates a price attached to the selected price list.
This gives repeated imports useful upsert behaviour. Re-sending a list updates the intended negotiated prices instead of silently accumulating duplicate records.
Chunking also creates operational boundaries. Progress can advance after each unit of work. Cancellation can be observed between chunks. A failed chunk can be classified without requiring every other row to disappear from view.
The aim is not simply to make the import faster. It is to make a large commercial change observable and controllable.
Cancellation is part of the product
An operator may discover that the wrong file was selected or that a commercial agreement should not yet be applied.
The import exposes cancellation as a real state transition. Before processing the next chunk, the worker rereads the import state. If the user has cancelled the run, pending rows are marked accordingly and processing stops.
This is more honest than a button that merely hides a progress modal while the backend continues changing prices.
The same principle applies to stale work. An import that remains in an active state without progress is not allowed to block the supplier indefinitely. The start route can classify stale records as failed, making room for a new controlled attempt.
Commercial operators do not need to know how a workflow engine schedules steps. They need reliable answers to ordinary questions: Is it still running? Can I stop it? Can I try again? Which prices were applied?
Those answers belong in the product design.
Progress should count every kind of row
An import progress bar can be technically animated and still be misleading.
Imagine a file containing many unknown SKUs. Validation may classify most rows before the price-creation loop begins. If progress counts only successfully processed prices, the interface appears frozen and then jumps directly to completion.
The platform derives live counts from staging: matched, unmatched, skipped, failed and pending. The status endpoint verifies both seller and target price-list ownership before returning those details.
Now the dashboard can explain that work is moving even when the outcome is “this SKU does not exist in your catalogue.”
That is an important product lesson. Progress is not merely the number of successful writes. It is the number of source rows whose fate has been decided.
For business users, a transparent partial result is more useful than a perfect-looking percentage with no explanation.
Keep the entire import reviewable
The import record and staged rows provide an audit trail for the operation.
Operators can inspect the latest run, page through row outcomes and download the source file. Error details are bounded so the process does not recreate the memory problem it was designed to avoid, while row-level state remains available in the database.
This changes support conversations.
Instead of “the import failed,” the supplier can see that one column mapping was wrong, a set of SKUs did not belong to its catalogue, duplicate rows were skipped or a particular chunk encountered a database error.
The platform can preserve evidence of the input without turning application logs into the primary user interface.
For negotiated pricing, that traceability is commercially valuable. The team can explain which file targeted which price list and how the platform classified its rows.
Deleting a large list needs the same discipline
Import is only half the lifecycle.
A large price list may eventually expire or be replaced. Deleting it through a generic per-price path can materialise too many child records and emit a storm of per-row events. That is the wrong shape for a large collection.
We added a dedicated bulk-deletion workflow.
The route verifies supplier ownership, creates tracked background work and protects the same price list from concurrent deletion. The workflow soft-deletes child prices, the list and its supplier association as a set. After the database transaction, it emits one price-list deletion event so downstream catalogue projections can refresh without receiving one event per price.
The business operation remains simple—remove this negotiated list—but the implementation respects its scale.
This is why lifecycle thinking matters. A design is not complete when it can create data. It must also update, inspect, cancel and retire that data safely.
Why this belongs on top of Medusa
Medusa already provides product variants, price sets and price lists. Those primitives are the reason we could preserve the difference between standard and contextual pricing.
The client needed the operational layer around them: supplier ownership, large-file ingestion, tolerant SKU mapping, staging, progress, cancellation, review and bounded deletion.
We extended Medusa where the business process demanded more than the core primitive. We did not replace the pricing engine with a parallel spreadsheet database.
That architectural choice keeps negotiated amounts inside the same pricing model used by the commerce journey. The import targets real Medusa price lists and real variant price sets. Customer or property-group eligibility can continue to use the platform’s commercial relationships.
The custom work makes the primitive operable at the scale and level of accountability the client required.
A practical price-list import checklist
Before accepting a large negotiated-pricing file, define these contracts.
- Does the file target a contextual price list rather than overwrite standard catalogue prices?
- Has the backend verified that the supplier owns the target list and upload?
- Can the source be streamed and staged without loading it all into memory?
- Are column mappings explicit and reviewable?
- How are duplicates, empty SKUs, invalid amounts and unknown products classified?
- Is SKU matching restricted to the supplier’s catalogue?
- Are normalisation rules supported by matching database indexes?
- Does a repeated import update existing list prices without creating duplicates?
- Can the operator follow progress, inspect rows, cancel and retry?
- Can a large list later be deleted without per-price event amplification?
If these questions have clear answers, the import becomes a dependable commercial workflow instead of a risky administrative shortcut.
The broader lesson
The client did not ask for a faster CSV parser.
They needed suppliers to maintain negotiated pricing without damaging the standard catalogue or losing control of a large operation.
That is the problem we solved on Medusa.
We kept the commercial meanings separate. We admitted the file into durable staging. We matched only inside the supplier’s catalogue. We applied changes in bounded units, exposed progress and cancellation, preserved review data and designed the deletion path with the same care as creation.
The result is a price list that behaves like a managed business agreement—not a spreadsheet capable of rewriting the shop.