E-Commerce Databases Are Not One Database: Catalog, Cart, Orders, Inventory, Payments

E-commerce systems fail when teams treat checkout as one database transaction instead of five different consistency problems moving at different speeds.

Situation

A storefront looks simple from the outside: browse a product, add it to a cart, pay, receive an order. That shape encourages a dangerous internal model: one application, one relational schema, one transaction boundary.

That model works while traffic is low, SKU count is small, inventory is forgiving, and payment retries are rare. It breaks when the business adds marketplace sellers, regional fulfillment, promotions, backorders, fraud review, partial shipments, returns, and mobile clients that retry aggressively on weak networks.

The operational truth is that “purchase” is not one write. It is a chain of state transitions across catalog, cart, order, inventory, and payment systems. Each subsystem has a different read pattern, write pattern, failure mode, and recovery requirement.

Catalog wants broad, cached, searchable reads. Cart wants cheap ephemeral writes. Orders want durable append-only state. Inventory wants contention control. Payments want idempotent external side effects.

Trying to force all of that into one database does not simplify the system. It hides the boundaries until the first incident.

The Problem

The single-database version usually fails in one of five ways.

First, catalog reads overload transactional tables. Search pages, recommendation widgets, product detail pages, and merchandising tools all want denormalized product data. If they read from the same schema used by checkout, a catalog launch or search crawler can degrade order creation.

Second, cart state becomes falsely important. Most carts are abandoned. Treating every cart mutation like an order mutation wastes durable write capacity and turns transient user behavior into transactional load.

Third, orders become mutable documents instead of ledgers. If order rows are repeatedly overwritten as payment, fulfillment, cancellation, and refund events arrive, it becomes hard to reconstruct what happened during disputes or retries.

Fourth, inventory becomes a race condition. The system must decide whether it is selling available stock, reserving stock, promising future stock, or reconciling stock later. These are different contracts. A generic quantity column is not an inventory system.

Fifth, payments introduce side effects outside the database. A database rollback cannot undo a card authorization already sent to a processor. A client timeout does not mean the charge failed. Retrying without an idempotency boundary can create duplicate financial operations.

The core question is: how should an e-commerce platform split data ownership so checkout remains reliable without making every subsystem strongly consistent with every other subsystem?

Five Stores, One Checkout Contract

The answer is not “microservices” as a slogan. The answer is separating consistency domains and then making the handoffs explicit.

flowchart TD
  Browser[buyer session — browse and checkout] --> Catalog[catalog store — searchable product facts]
  Browser --> Cart[cart store — ephemeral buyer intent]
  Cart --> Checkout[checkout coordinator — validation and command boundary]
  Checkout --> Inventory[inventory store — reservations and stock movements]
  Checkout --> Orders[order ledger — durable commercial record]
  Checkout --> Payments[payment ledger — idempotent external effects]
  Inventory --> Orders
  Payments --> Orders
  Orders --> Events[event stream — fulfillment and notifications]
  Catalog --> Events

Catalog should be optimized for product discovery, not purchase finality. It can be document-oriented, search-indexed, cached, and rebuilt from authoritative product sources. Catalog availability shown to the user is often a hint, not a promise. The promise happens later, at reservation.

Cart should represent intent, not revenue. It can expire aggressively, tolerate last-write-wins semantics, and store product snapshots only when needed for user experience. Cart storage should be horizontally cheap because cart write volume can exceed order volume by orders of magnitude.

Orders should be the commercial ledger. Once an order is placed, the system should prefer append-only events or tightly controlled state transitions over arbitrary mutation. OrderCreated, PaymentAuthorized, InventoryReserved, FulfillmentReleased, and RefundIssued are operational facts. They are not merely fields on a row.

Inventory should own stock truth. The important decision is whether checkout reserves inventory before payment, after authorization, or asynchronously. Each choice has a business cost. Reserve too early and carts lock scarce goods. Reserve too late and paid orders can oversell. Reserve asynchronously and the customer experience must handle apology, substitution, or backorder flows.

Payments should own idempotency and reconciliation. The payment system should record every attempted external operation with an idempotency key, request hash, provider reference, response, and final reconciliation state. Order creation may request payment, but it should not pretend the local order transaction and the remote payment operation are one atomic commit.

The checkout coordinator is therefore not a giant transaction. It is a command boundary. It validates the cart, requests inventory reservation, creates an order record, requests payment authorization, and emits durable events. When one step fails, the coordinator executes compensating transitions rather than pretending it can roll back the world.

In Practice

Context: Public cloud documentation describes shopping carts as a canonical high-scale key-value workload. AWS documents DynamoDB as suitable for a shopping cart use case with single-digit millisecond performance across very large user counts: Amazon DynamoDB introduction.

Action: The documented pattern is to keep cart access keyed by buyer or session, avoid cross-cart joins, and let cart entries expire. This makes cart storage independent from order durability.

Result: Cart traffic can scale without forcing checkout, inventory, or payment tables to absorb every add, remove, and quantity-change event.

Learning: Cart data is intent. Treating intent like revenue creates unnecessary coupling.

Context: PostgreSQL documents row-level locking behavior for statements such as SELECT FOR UPDATE, and also notes that deadlocks can occur with row-level locks: PostgreSQL explicit locking.

Action: The documented database behavior supports an inventory pattern where reservations update a constrained set of stock rows under transaction control. The reservation write is small, explicit, and separated from catalog browsing.

Result: The contention surface is reduced to the SKU, location, or stock bucket being reserved. Search, cart editing, and order history do not participate in the lock path.

Learning: Inventory correctness is a concurrency problem. It should not be mixed with high-fanout read models.

Context: Stripe publicly documents idempotency for mutating API requests and explains that retry safety matters because clients and APIs form a distributed system: Stripe idempotent requests and Stripe engineering on idempotency.

Action: The documented payment pattern is to attach an idempotency key to a logical operation and persist the first result for that key.

Result: A timeout between checkout and payment provider does not require guessing whether to retry. The retry can reuse the same operation identity.

Learning: Payments are not just writes. They are external side effects requiring replay-safe command design.

Context: Shopify also documents idempotency as a way to retry failed API requests without duplication or conflict: Shopify idempotent requests.

Action: The acknowledged pattern is to make client and server retries safe by assigning stable operation identity.

Result: Network failure becomes a recoverable condition instead of a duplicate-order or duplicate-charge incident.

Learning: Retry behavior is part of the data model.

Where It Breaks

Boundary	Failure mode	Mitigation	Tradeoff
Catalog to cart	Product price or availability changes after add-to-cart	Reprice and revalidate at checkout	Users may see cart changes late
Cart to order	Duplicate checkout submission	Checkout idempotency key	Requires persisted command records
Order to inventory	Paid order cannot reserve stock	Reserve before capture or support backorder compensation	Either lower conversion or more exception handling
Inventory to fulfillment	Reservation never converts to shipment	Reservation expiry and reconciliation jobs	Requires operational cleanup paths
Order to payment	Payment succeeds but order write fails	Payment ledger and reconciliation by provider reference	Adds recovery workflow
Payment to order	Payment retry creates duplicate charge	Idempotency key and request hash	Requires stable operation identity
Events to downstream systems	Email or fulfillment receives duplicate events	Consumer idempotency and event identifiers	Every consumer owns dedupe logic

The important architectural smell is not eventual consistency. Eventual consistency is often the right answer. The smell is hidden inconsistency: no ledger, no operation identity, no reconciliation path, and no clear owner for the disputed fact.

What to Do Next

• Problem: One database makes checkout look atomic while catalog, cart, orders, inventory, and payments have different correctness requirements.
• Solution: Split the model by consistency domain: searchable catalog, ephemeral cart, durable order ledger, transactional inventory reservation, and idempotent payment ledger.
• Proof: Known systems and documented behaviors support the split: key-value carts scale independently, row locks constrain inventory contention, and idempotency keys make payment retries safe.
• Action: Draw the checkout state machine before drawing tables. For every transition, define the owner, idempotency key, retry behavior, timeout behavior, reconciliation query, and customer-visible fallback.