Case Study: E-Commerce Catalog, Inventory, Cart, and Search

Three Failures in One Promotion

Moonridge Market began with one relational database for products, variants, prices, inventory, carts, and orders. For a small retailer, that was a strength. Transactions and constraints kept the consequential facts together, and the team could operate the system without a fleet of specialists.

Then a seasonal promotion produced three failures that seemed unrelated. Search pages slowed as shoppers combined brand, size, color, price, delivery region, and availability filters. A popular lamp appeared at its old price in a product-page cache after the promotion ended. Later that evening, an item withdrawn for a safety review disappeared from the catalog tables but remained in search and a marketplace feed.

The proposed fixes arrived as a shopping list: add a search engine, a larger cache, a stream, a warehouse, an inventory service, perhaps a separate cart store. Each could remove load. Together, adopted complaint by complaint, they could create six plausible answers to “what is true?”

The design review therefore starts with a boundary rather than a product. Catalog identity, price, inventory reservation, cart intent, and order commitment remain authoritative facts. Search results, cached pages, availability labels, analytics, and partner feeds are copies made for particular readers. A copy may be fast, flexible, or temporarily stale. It may never silently acquire the power to authorize a sale.

Give Each Path One Promise

The workload does not divide neatly by nouns such as “catalog” and “commerce.” It divides by promises.

This is the one comparison worth holding in view. Search can own document shape, tokenization, synonyms, facets, and ranking. The cache can own efficient retrieval and eviction. Analytics can own transformations for reporting. None owns canonical product identity, the accepted price rule, a reservation, or the order transition.

Moonridge keeps those source facts relational during this redesign because its strongest invariants still cross them. A product has a canonical variant and restriction state. A price has a version, region, currency, activation interval, and approval trail. Inventory distinguishes on-hand, reserved, committed, returned, and adjusted quantities. A cart records intent; an order records a commitment. Splitting stores before those boundaries are understood would move ambiguity across the network without removing it.

Separate the Copy Without Losing the Change

The team adds a transactional outbox to the source database. Change data capture could serve the same role if it preserves the required transaction and ordering information. The choice is less important than one property: the source fact and the record announcing its change cannot diverge because a process crashed between two writes.

The initial event vocabulary stays small: ProductChanged, PriceActivated, InventoryAvailabilityChanged, ProductRestricted, ProductDeleted, and the reservation transitions needed for operations and analytics. Each record carries the source transaction, aggregate key, aggregate version, event and publication times, schema version, and any restriction marker. Consumers persist their checkpoints and process repeated delivery idempotently.

That produces two deliberate paths:

merchant and warehouse writes
             |
             v
relational source of truth
  product | price | inventory | cart | order | tombstone
             |
             +--> checkout: validate -> reserve -> create order
             |
             +--> versioned change log
                     +--> search
                     +--> cache refresh or purge
                     +--> availability projection
                     +--> analytics
                     +--> partner feeds

The asynchronous branch is now an owned production system, not plumbing. The team measures publication lag and consumer lag by event type. It knows which partition is behind, which source version a document contains, and whether a dead-letter queue is growing. A backfill uses the same projection rules as ordinary consumption and cannot overwrite a newer document or resurrect a tombstone.

Two tempting alternatives are rejected. Search does not become the catalog master merely because it contains the richest read document. Cache invalidation does not become the price-correctness mechanism merely because it is usually quick. Both would place authority in systems designed to lose, reorder, expire, or rebuild data.

Follow One Price Through the System

At 14:00, a merchant schedules a lamp promotion to end at 18:00. Moonridge distinguishes three prices. The display price appears in search, product pages, messages, and feeds. A quote price is a short-lived promise carrying its price version, region, currency, and expiry. The order price is the version actually committed with the order.

When the new price activates, the source transaction writes the approved version and its outbox record together. Search updates the product document. The product cache refreshes or invalidates keys that include region and customer segment where relevant. Analytics retains both versions for promotion analysis. Each consumer rejects an older version arriving after a newer one.

Suppose the cache misses its invalidation. A shopper sees the promotional display price at 18:03 and begins checkout. The system still has to obey a stated customer promise. If Moonridge promises to honor an add-to-cart price for fifteen minutes, it must have issued a durable quote when the item entered the cart. If it promises only to confirm price at checkout, the interface must say so, and checkout reads the current source rule. Architecture cannot resolve an unstated business policy.

Support can reconstruct the outcome because the product view, quote, and order carry price versions. “The cache was stale” is then a diagnosable failure, not a reason the organization cannot explain what it charged.

A Cart Is Not a Reservation

The promotion also concentrates thousands of attempts on a few variants. Search may show “in stock” from a projection, and the cart may remember a quantity, but neither operation changes sellable inventory.

Moonridge names the forbidden state: live reservations plus committed quantities may not exceed the amount allowed by its written oversell policy. The checkout path validates the product restriction and price or quote, then creates an expiring reservation with a guarded write before creating the pending order. Payment success commits the reservation; failure or timeout eventually releases or expires it through an idempotent transition.

A conventional relational transaction and guarded update may be enough for most stock. The hottest variants may need compare-and-swap with bounded retries, partitioned reservation ownership, or an admission queue. A business that deliberately accepts some oversell can encode that allowance. These mechanisms differ, but every sound design answers the same questions: which quantity is protected, who may change it, how reservations expire, how duplicate attempts collapse, and how the balance is reconciled with warehouse adjustments.

During contention, the discovery path should become less precise rather than more misleading. “Available” or “selling quickly” is safer than a stale exact count. Checkout remains the place where availability becomes a commitment.

Search Must Be Rebuildable

Moonridge’s search document combines canonical identity and taxonomy, catalog text and media, a display-price projection, an availability hint, restriction markers, and slower-moving ranking signals. Those fields do not share one freshness policy. A late ranking feature affects relevance. A late price affects trust. A late restriction can expose a recalled or prohibited item.

The dashboard therefore separates lag for product edits, price activation, availability, and deletion or restriction. Cluster health alone is insufficient: a green search cluster that is forty minutes behind on ProductRestricted is unhealthy for this retailer.

The recovery proof is a full reindex. The team can take a source snapshot, remember its change-log position, build a new index from deterministic projections, apply later changes, compare counts and sampled versions, and switch the read alias. Tombstones participate in both the snapshot and catch-up phases. Live changes continue until the final switch, so an all-night rebuild cannot finish with an index that was already stale when it opened.

Deletion Is a Distributed Operation

The withdrawn lamp reveals a different failure. Removing its catalog row cannot remove copies that the source database does not control. Moonridge records a tombstone instead: product or variant key, reason class, effective time, initiating workflow, retention rule, and propagation obligation. Restrictions and tombstones take priority over ordinary merchandising work.

Search suppresses the item from results, autocomplete, and facets. The cache purges product, regional-price, and availability keys. Partner exporters stop future inclusion and request removal where the integration permits it. Support tools show a safe restricted state rather than a cached sellable page. Analytics applies its retention and privacy rules without becoming a path that republishes the item.

Acknowledgment is useful, but verification closes the operation. A worker queries the public search path, checks the product route, probes the relevant cache dimensions, inspects the next partner feed, and records any consumer that missed its deadline. The system retains enough tombstone evidence to prevent a later backfill from restoring the product.

This stricter path also clarifies ordinary deletion. Different obligations may apply to a safety withdrawal, an embargo, a merchant deletion, and a privacy request. The architecture carries the classification and deadline; it does not pretend one generic deleted=true flag expresses the policy.

Move Analytics Off the Customer Path

Merchandising reports had been running on a read replica used by product traffic. During the promotion, long scans increased replica lag until product pages served older facts. The redesign moves those workloads to an analytical store fed from the same registered change path.

Offload creates obligations of its own. Each dataset names its source, schema version, transformation owner, privacy and retention class, replay procedure, and expected lag. Reports display freshness. Backpressure alerts reach an owner before a “daily” report becomes a week-old decision surface. Analytics may recommend a category change, ranking boost, or replenishment action, but any write returns through the reviewed catalog or inventory workflow. The warehouse does not gain a side door into checkout truth.

Record the Decision, Including Its Cost

Moonridge’s ADR says that the relational system remains authoritative for product and variant identity, price versions, inventory reservations, cart intent, order transitions, and tombstones. Search, cache, availability, analytics, and partner feeds consume versioned source changes through an outbox or equivalent CDC boundary. Checkout validates restrictions and price, then reserves inventory against authoritative state before order commitment.

The rejected paths are recorded plainly: keep discovery on transactional replicas; make search the catalog master; depend on cache invalidation for price correctness; or split every domain before ownership and invariants are clear. The chosen design removes discovery and reporting load from the source, but it creates responsibility for event lag, idempotent replay, reindexing, cache purge, tombstone verification, and cross-system incident response.

The team will revisit the decision if reservation contention dominates checkout latency, derived-view lag repeatedly breaches its promises, a full search rebuild exceeds the recovery objective, or organizational ownership becomes stable enough to support a narrower source boundary. “We now have several data products” is not itself a reason to split authority.

Prove Readiness With Two Drills

Before launch, owners must be able to locate source schemas, event versions, consumer checkpoints, freshness dashboards, dead-letter handling, replay controls, search rebuild instructions, cache dimensions, reservation expiry and reconciliation, report lineage, and the runbooks for stale price, oversell, stuck publication, failed reindex, purge failure, and deletion leakage.

Documents alone do not prove the paths meet. Moonridge runs two drills.

In the flash-sale drill, one variant receives twenty times its ordinary checkout load while workers restart, caches miss, and search lags. The team traces displayed, quoted, and ordered price versions; accounts for every reservation as live, committed, released, or expired; and proves that committed plus reserved inventory stays within the declared policy.

In the restriction drill, an operator withdraws a product under a one-hour deadline. The team follows the source tombstone through publication, search suppression, cache purge, partner removal, support behavior, analytics handling, and an attempted reindex. Success means the public copies are absent, exceptions are visible, and the rebuild does not resurrect the item.

The result is polyglot persistence with a limit. Moonridge uses different systems because its paths make different promises, not because each symptom acquired a product. Search discovers. Cache accelerates. Analytics explains. The cart remembers intent. Checkout commits authoritative price and inventory decisions.

The next case applies the same discipline where uncertainty has a higher cost. Payment work can be queued and projected, but the ledger must still say which financial facts were committed and how every external side effect will be reconciled.