- A supplier unpublishes a product.
- This is not merely a search-quality problem.
- On a procurement marketplace built with Medusa and Elasticsearch, catalogue changes came from many paths: individual edits, bulk imports, supplier operations, price-list updates and stock vi
- We built a database-first reconciliation workflow instead.
The client problem
A supplier unpublishes a product. A price changes. Stock policy removes an item from sale. Five minutes later, the buyer searches and still sees the old result.
This is not merely a search-quality problem. The search engine is now presenting a commercial promise that the commerce platform no longer makes.
On a procurement marketplace built with Medusa and Elasticsearch, catalogue changes came from many paths: individual edits, bulk imports, supplier operations, price-list updates and stock visibility. Trying to attach one perfect event listener to every low-level mutation would make correctness depend on never missing a path. Loading the complete catalogue for every repair run would create a different operational risk.
We built a database-first reconciliation workflow instead. It walks the current Medusa catalogue in bounded batches, compares each product's source version with the search document, rewrites only what is missing or stale, records progress and, in full mode, removes documents that no longer belong in search.
Search becomes fast because of Elasticsearch, but trustworthy because Medusa can make it converge again.
The client problem: search can fail commercially while working technically
Elasticsearch may return in milliseconds and still return the wrong product.
A document can exist after the source product was deleted. It can remain searchable after publication was withdrawn. It can carry an older supplier, category, price or delivery projection. Conversely, a valid new product can be absent because one asynchronous write failed.
For the buyer, all of these failures look similar: search disagrees with the marketplace. For the operator, they have different causes and require different repairs.
The platform therefore needed more than a “reindex all” button. It needed an operational answer to four questions: what exists in Medusa but not Elasticsearch, what is older in Elasticsearch, what is already current and should be left alone, and what exists only in Elasticsearch and should be removed?
That comparison is the heart of reconciliation. Individual update events can make the index fresher, but reconciliation is the safety net that proves the projection can recover from missed events and partial failures.
Do not turn every database mutation into an event contract
A rich product document combines facts from several Medusa modules: base product, variants, supplier, prices, taxonomy, attributes, stock policy and delivery topology.
One business operation can change many underlying rows. If every row emits its own search event, a bulk catalogue update becomes an event storm. More importantly, each handler must know whether its small mutation changes the final product document and when all related mutations are complete.
That creates ordering problems. The price event may arrive before the product workflow finishes. A stock update may rebuild the document while publication is changing. Retrying one handler can replay an intermediate state.
We still use targeted synchronisation paths where they make sense, but we do not make long-term correctness depend on observing every elementary mutation. The reconciliation workflow asks the source database for the product document as it exists now.
The unit of synchronisation is the commercial product projection, not every table write that contributed to it.
Walk the catalogue with a cursor, not one giant array
The earlier shape of catalogue reconciliation loaded too much state into memory. That may appear simple on a small catalogue, but memory use grows with the number and richness of products.
The replacement uses keyset pagination. Each database query asks for the next bounded batch of product documents after the last processed product ID. The final ID becomes the cursor for the following query.
Keyset pagination matters because it avoids repeatedly skipping an ever-growing offset. It also gives the job a precise resume point. After each successful batch, the workflow writes the cursor and counters to a durable reindex_job record.
If execution stops, the job does not need to forget all previous work. A retry can continue from the stored cursor. The operator can see how many products were processed, selected for indexing or skipped.
The batch size bounds memory and the amount of work at risk. Catalogue size changes the number of iterations, not the shape of one enormous in-memory operation.
Compare source versions before paying for a rewrite
Rebuilding every document on every run would eventually converge, but it would waste Elasticsearch capacity and any expensive enrichment attached to indexing.
The canonical product document carries a source_updated_at marker derived from the Medusa-side projection. For each database batch, the workflow fetches the corresponding markers from Elasticsearch and partitions the products into clear branches.
If the document is missing, it is indexed. If it exists without a source marker, it is indexed so the older shape can join the contract. If the Elasticsearch marker is older than Medusa, it is reindexed. If the marker is equal, the document is skipped. If Elasticsearch is unexpectedly newer, it is also skipped rather than overwriting newer search state with an older source snapshot.
Focused tests cover those decisions, including mixed batches and preservation of cursor order.
This turns reconciliation into a diff, not a ritual rebuild. Unchanged products cost a comparison rather than a complete search write.
Rebuild after locking, because the world changes during a scan
A reconciliation scan takes time. A product that was valid when a batch was selected can be unpublished before Elasticsearch is written.
Writing the earlier snapshot would resurrect a result the seller had just removed.
The workflow therefore acquires PostgreSQL advisory locks for the product IDs, in deterministic order, then rebuilds the current documents while those write boundaries are held. Products that are no longer published, available or indexable disappear from the rebuilt map and are counted as skipped instead of being written back into search.
The version comparison also happens on these current documents. The decision is made as close as possible to the actual bulk write.
This is a useful pattern beyond search: discovery finds candidate work, but execution revalidates truth under the appropriate lock. The scan remains efficient while the mutation boundary remains safe against a concurrent catalogue action.
Keep one reconciliation active at a time
A daily safety run and an operator pressing “reconcile” can happen together. Two full scans would compete for database and Elasticsearch capacity, produce confusing progress and increase race windows.
At job creation, the workflow opens a PostgreSQL transaction, sets a bounded lock wait and acquires a dedicated advisory lock. Inside that same transaction it checks for an existing pending or running reconciliation and inserts the new progress row.
Keeping lock, check and insert together is essential. A lock acquired in one workflow step and a job created through another database connection would not protect the decision.
When another reconciliation already exists, the second caller receives an explicit conflict. The scheduled job treats that as a normal skip: the active run is already doing the necessary work.
This is not a distributed promise that no catalogue writer can ever run. It is the narrower guarantee the operation needs: one catalogue reconciliation coordinator per deployment, combined with product-level locks around writes.
Retry the failed items, not the successful batch
Elasticsearch bulk responses can succeed for most documents and fail for a few. Replaying every successful item because one item hit a temporary queue limit adds unnecessary work and can obscure the real failure.
The bulk helper classifies transient conditions such as rejected execution, temporary service unavailability and selected network failures. Only the affected documents are retried, with bounded exponential backoff. Mapping errors and other non-transient failures are recorded rather than hidden behind endless retries.
Every batch uses refresh: false. The workflow issues one product-index refresh after the forward phase completes instead of forcing Elasticsearch to refresh after each chunk.
The job counters distinguish candidates, successful writes, skips and failures. Operators can see that a run completed without pretending every item succeeded when Elasticsearch returned partial errors.
Resilience here means making failure smaller, visible and retryable—not claiming that a bulk request is magically atomic.
Make progress and cancellation part of the product
Long-running maintenance should not be a black box behind an HTTP timeout.
The reconciliation begins as a background workflow and returns control to the admin surface. Its durable job row records mode, origin, phase, cursor, counts, timestamps and terminal status. The interface can poll that state instead of holding one request open.
After every batch, the workflow updates progress and checks whether the operator cancelled the job. Cancellation is cooperative: the current bounded unit finishes, then the scan stops gracefully. Finalisation preserves the cancelled status rather than overwriting it with completed.
If a workflow reverts, compensation marks the progress record as failed and keeps it as an operational trail. The system does not erase the evidence that maintenance was attempted.
Before and after document counts can also be captured when Elasticsearch is reachable. Those counts help an operator interpret a run without making index availability a precondition for creating or finalising the job record.
Repair search documents that no longer have a sellable source
Scanning from Medusa to Elasticsearch finds missing and stale documents, but it cannot discover an Elasticsearch-only document. The source database has no row to visit.
Full reconciliation therefore adds an orphan sweep in the opposite direction. It pages through Elasticsearch product IDs using search_after, then asks PostgreSQL which IDs still represent published, non-deleted and marketplace-indexable products.
Candidates absent from that current set are potential orphans. Before deletion, the workflow acquires per-product locks and rechecks database indexability. A product republished or returned to an indexable stock state during the sweep is preserved.
Only the IDs that remain invalid are deleted from Elasticsearch. Cursor and deletion progress are stored after each page, and cancellation is checked throughout.
This handles more than hard deletion. A product that still exists but is unpublished or excluded by the marketplace's durable stock policy should not remain a buyer-facing search result.
Incremental and full modes solve different operational needs
The scheduled safety run uses incremental mode. It performs the database-to-search forward scan and relies on normal deletion paths for routine removals. This keeps recurring maintenance focused on finding missing or outdated projections.
Full mode adds the reverse orphan sweep. It is appropriate when an operator needs deeper repair—for example after migration, a suspected missed deletion or a broader index incident.
Separating the modes avoids paying for a complete Elasticsearch ID scan on every routine run while preserving a deliberate way to prove the two systems agree in both directions.
Both modes use the same canonical product-document builder as targeted by-ID and bulk synchronisation paths. A repaired document therefore has the same shape as a live update; reconciliation does not maintain a second, simplified interpretation of the catalogue.
The operational choice is how broadly to compare, not which definition of a product to use.
Why Elasticsearch remains a projection of Medusa
The marketplace needs Elasticsearch because buyers expect fast lexical, filtered and semantic discovery across a commercially rich catalogue. It would be a mistake to let that speed turn the index into a second source of truth.
Products, publication state, supplier relationships, prices, taxonomy, attributes, stock policy and delivery topology are authored in Medusa and the project's domain modules. Elasticsearch denormalises them into a search-ready document.
The reconciliation workflow makes that ownership practical. A projection can be deleted and rebuilt because the authoritative state and canonical builder remain elsewhere. Operators can inspect and repair drift instead of manually deciding which system is correct.
Choosing Medusa as the extensible commerce foundation made it possible to create this governed projection around the client's catalogue. Search gained the detail it needed without taking ownership of the commercial transaction.
Fast discovery and authoritative commerce remain separate responsibilities connected by an observable convergence process.
The catalogue-reconciliation checklist
Before trusting a search projection, ask:
- Which system owns product truth?
- What canonical builder creates a search document?
- Do live and repair paths use that same builder?
- Can a run process the catalogue without loading every product into memory?
- Is the cursor persisted after each successful batch?
- How does the job decide missing, stale and unchanged documents?
- Are documents rebuilt after acquiring their write boundary?
- What prevents two reconciliation coordinators from running together?
- Are transient bulk failures retried per item?
- Are permanent item failures visible?
- Is index refresh performed once per phase rather than once per batch?
- Can an operator observe progress and cancel safely?
- How are Elasticsearch-only documents discovered?
- Is indexability rechecked before orphan deletion?
- Can routine and deep-repair modes be selected deliberately?
These answers determine whether search drift is an incident with a recovery path or a permanent source of doubt.
The broader lesson
Search synchronisation is not a collection of callbacks. It is an operational convergence system.
For this marketplace, Medusa remains authoritative. Reconciliation reads the catalogue in bounded cursor batches, compares source markers, skips current documents, retries transient item failures and records progress. Locks prevent competing coordinators and stale per-product writes. Full mode walks Elasticsearch in reverse to remove documents that no longer represent sellable products.
That architecture does not require every mutation to announce itself perfectly. It gives the platform a deterministic way to recover when a signal is missed.
The buyer sees one coherent catalogue. The operator gets a visible repair process. And Elasticsearch can do what it is best at—fast discovery—without becoming a second commerce engine that nobody knows how to reconcile.