Shopping Cart & Order Design for E-commerce Platform

1. Overview

Shopping cart and ordering are the most frequently used features on an e-commerce platform. They are also the most prone to issues: overselling, double charging, orders getting stuck during flash sales.

This article walks through a complete design as a case study. The design covers requirements, data models, APIs, processing flows, and challenging points. Each challenge comes with a solution using Spring Boot 3, PostgreSQL, Redis, and Kafka. After reading, you’ll have a design framework to apply or use in system design interviews.

2. Problem Requirements

2.1. Functional Requirements

• Customers can add, update, remove products (SKUs) from cart. The cart persists across multiple login sessions.
• During checkout, the system recalculates prices and checks inventory, then creates an order in PENDING_PAYMENT status.
• Orders not paid within 15 minutes will be cancelled and inventory restored.
• After successful payment, the system emits events for warehouse, email, and rewards services.

2.2. Assumed Scale

• 10 million active users per month.
• Cart operations: average 2,000 req/s, peak 20,000 req/s.
• Orders: average 200 orders/s. During flash sales can reach 5,000 orders/s, concentrated on a few dozen SKUs.

2.3. Constraints

• No overselling. This is a hard constraint.
• No duplicate orders when client retries due to timeout or user double-clicks.
• Once an order is created, events must be emitted, even if Kafka is temporarily down.
• Shopping cart can have eventual consistency. Orders and inventory must have strong consistency.

The last constraint determines the architecture. Cart has high read/write but low criticality, so it goes on Redis. Orders and inventory need transactions, so they go on PostgreSQL.

3. Data Models

The shopping cart on Redis uses one Hash per customer:

key:   cart:{customerId}
field: sku
value: quantity
TTL:   30 days

The cart only stores SKU and quantity, not price. Price can change anytime, so the system always recalculates price at checkout.

Transaction-critical data sits on PostgreSQL:

CREATE TABLE inventory (
    sku        VARCHAR(64) PRIMARY KEY,
    available  INT NOT NULL CHECK (available >= 0),
    reserved   INT NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    id               BIGSERIAL PRIMARY KEY,
    customer_id      BIGINT       NOT NULL,
    idempotency_key  VARCHAR(64)  NOT NULL,
    status           VARCHAR(32)  NOT NULL,
    total_amount     BIGINT       NOT NULL,
    expires_at       TIMESTAMPTZ  NOT NULL,
    created_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),
    UNIQUE (customer_id, idempotency_key)
);

CREATE TABLE order_items (
    order_id    BIGINT REFERENCES orders(id),
    sku         VARCHAR(64) NOT NULL,
    quantity    INT         NOT NULL,
    unit_price  BIGINT      NOT NULL,
    PRIMARY KEY (order_id, sku)
);

CREATE TABLE outbox_events (
    id           BIGSERIAL PRIMARY KEY,
    aggregate_id BIGINT      NOT NULL,
    event_type   VARCHAR(64) NOT NULL,
    payload      JSONB       NOT NULL,
    published    BOOLEAN     NOT NULL DEFAULT false
);

A few points to note about this schema:

• CHECK (available >= 0) is the last line of defense. If code has a bug, the database still prevents negative inventory.
• UNIQUE (customer_id, idempotency_key) prevents duplicate order creation at the database layer.
• order_items.unit_price is a price snapshot at order time. Old orders are not affected when product prices change.
• Money is stored as BIGINT in the smallest unit (cents/dong). Do not use DOUBLE.

4. API Design

GET    /api/cart                       -> view cart (with current prices)
PUT    /api/cart/items/{sku}           -> body {"quantity": 2}, set quantity
DELETE /api/cart/items/{sku}           -> remove SKU from cart
POST   /api/orders                     -> header Idempotency-Key, create order from cart
GET    /api/orders/{id}                -> view order status

PUT with absolute quantity is idempotent. Calling it multiple times yields the same result. If you design it like POST /add with +1, each client retry would add another product.

POST /api/orders is not inherently idempotent, so the client must generate an Idempotency-Key (UUID) for each “Place Order” click. On retry, the client sends the same key.

5. Shopping Cart on Redis

Add dependency:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>

Cart service:

@Service
public class CartService {

    private static final Duration CART_TTL = Duration.ofDays(30);
    private static final int MAX_ITEMS = 100;

    private final StringRedisTemplate redis;

    public CartService(StringRedisTemplate redis) {
        this.redis = redis;
    }

    public void setQuantity(long customerId, String sku, int quantity) {
        String key = "cart:" + customerId;
        HashOperations<String, String, String> ops = redis.opsForHash();
        if (quantity <= 0) {
            ops.delete(key, sku);
            return;
        }
        if (!ops.hasKey(key, sku) && ops.size(key) >= MAX_ITEMS) {
            throw new IllegalStateException("Cart already has " + MAX_ITEMS + " products");
        }
        ops.put(key, sku, String.valueOf(quantity));
        redis.expire(key, CART_TTL);
    }

    public Map<String, Integer> getItems(long customerId) {
        Map<String, String> raw = redis.<String, String>opsForHash().entries("cart:" + customerId);
        return raw.entrySet().stream()
                .collect(Collectors.toMap(Map.Entry::getKey, e -> Integer.parseInt(e.getValue())));
    }
}

Explanation:

• ops.put(key, sku, ...) writes a field in the Hash with O(1) complexity. Two clients editing different SKUs won’t overwrite each other. If you stored the entire cart as a JSON string, you’d overwrite.
• expire is refreshed on each write. The cart only expires after 30 days of no activity.
• MAX_ITEMS limits cart size to prevent oversized keys due to bots.

The hasKey then size check is not atomic. Two concurrent requests might exceed the limit by 1-2 items. For a shopping cart, such small deviations are acceptable. You should not apply this “approximate” level to inventory.

Note: Redis is not absolutely durable. Enable AOF (appendfsync everysec) to limit data loss to ~one second. Losing a second of cart data is acceptable, but losing orders is not.

6. Order Workflow

The POST /api/orders flow goes through these steps:

1. Read cart from Redis
2. Fetch current prices from Catalog
3. BEGIN transaction (PostgreSQL)
   3a. Insert orders with idempotency_key  -> if duplicate, return existing order
   3b. Reserve inventory for each SKU (conditional update) -> insufficient stock rolls back
   3c. Insert order_items (price snapshot)
   3d. Insert outbox_events "OrderCreated"
4. COMMIT
5. Delete cart (best-effort)
6. Relay reads outbox -> publishes to Kafka

6.1. Preventing Overselling with Conditional Update

The common wrong approach is to read inventory, compare in Java, then write. Between read and write, another thread might have bought the item. The correct way is to combine the check and deduction in a single SQL statement:

@Repository
public class InventoryRepository {

    private final JdbcClient jdbc;

    public InventoryRepository(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    public boolean reserve(String sku, int quantity) {
        int updated = jdbc.sql("""
                UPDATE inventory
                   SET available = available - :qty,
                       reserved  = reserved  + :qty
                 WHERE sku = :sku AND available >= :qty
                """)
                .param("qty", quantity)
                .param("sku", sku)
                .update();
        return updated == 1;
    }
}

The database locks the row while UPDATE runs. The second request waits for the first to finish, then re-evaluates the condition available >= :qty against the new value. If insufficient stock, updated == 0 and the method returns false. You don’t need SELECT ... FOR UPDATE or version columns.

6.2. Idempotency and Transaction

@Service
public class OrderService {

    private final JdbcClient jdbc;
    private final InventoryRepository inventory;

    public OrderService(JdbcClient jdbc, InventoryRepository inventory) {
        this.jdbc = jdbc;
        this.inventory = inventory;
    }

    @Transactional
    public long placeOrder(long customerId, String idempotencyKey, List<OrderLine> lines) {
        Optional<Long> existing = jdbc.sql("""
                INSERT INTO orders (customer_id, idempotency_key, status, total_amount, expires_at)
                VALUES (:c, :k, 'PENDING_PAYMENT', :total, now() + interval '15 minutes')
                ON CONFLICT (customer_id, idempotency_key) DO NOTHING
                RETURNING id
                """)
                .param("c", customerId).param("k", idempotencyKey)
                .param("total", lines.stream().mapToLong(OrderLine::subtotal).sum())
                .query(Long.class).optional();

        if (existing.isEmpty()) {
            return jdbc.sql("SELECT id FROM orders WHERE customer_id = :c AND idempotency_key = :k")
                    .param("c", customerId).param("k", idempotencyKey)
                    .query(Long.class).single();
        }
        long orderId = existing.get();

        for (OrderLine line : lines.stream().sorted(Comparator.comparing(OrderLine::sku)).toList()) {
            if (!inventory.reserve(line.sku(), line.quantity())) {
                throw new OutOfStockException(line.sku());
            }
            jdbc.sql("INSERT INTO order_items VALUES (:o, :s, :q, :p)")
                    .param("o", orderId).param("s", line.sku())
                    .param("q", line.quantity()).param("p", line.unitPrice())
                    .update();
        }

        jdbc.sql("""
                INSERT INTO outbox_events (aggregate_id, event_type, payload)
                VALUES (:id, 'OrderCreated', CAST(:payload AS jsonb))
                """)
                .param("id", orderId)
                .param("payload", "{\"orderId\":" + orderId + ",\"customerId\":" + customerId + "}")
                .update();
        return orderId;
    }
}

public record OrderLine(String sku, int quantity, long unitPrice) {
    long subtotal() { return quantity * unitPrice; }
}

public class OutOfStockException extends RuntimeException {
    public OutOfStockException(String sku) { super("Out of stock: " + sku); }
}

Key points:

• ON CONFLICT ... DO NOTHING RETURNING id only returns id on successful insert. If the key already exists, the result is empty. The service then reads the old order and returns it, so client retry still gets exactly one order.
• OutOfStockException is a RuntimeException, so @Transactional rolls back everything: the order, all previously deducted SKUs, and outbox. No half-baked state.
• The SKU list is sorted before reserving. Two orders containing SKUs A and B will always lock A first, then B, avoiding deadlock.
• If the first request rolls back, the key does too. Client retry with the same key will attempt again from the start, which is the desired behavior.

6.3. Transactional Outbox

If you call kafkaTemplate.send() inside a transaction, you run into one of two errors. Kafka sends successfully but DB rolls back, creating a phantom event. DB commits but Kafka fails, losing the event. Outbox solves this by writing events in the same transaction as the order, then a relay pushes events to Kafka.

<dependency>
    <groupId>org.springframework.kafka</groupId>
    <artifactId>spring-kafka</artifactId>
</dependency>

@Component
public class OutboxRelay {

    private final JdbcClient jdbc;
    private final KafkaTemplate<String, String> kafka;

    public OutboxRelay(JdbcClient jdbc, KafkaTemplate<String, String> kafka) {
        this.jdbc = jdbc;
        this.kafka = kafka;
    }

    @Scheduled(fixedDelay = 500)
    @Transactional
    public void publish() throws Exception {
        List<OutboxEvent> events = jdbc.sql("""
                SELECT id, aggregate_id, event_type, payload::text AS payload
                  FROM outbox_events WHERE published = false
                 ORDER BY id LIMIT 100
                 FOR UPDATE SKIP LOCKED
                """).query(OutboxEvent.class).list();

        for (OutboxEvent e : events) {
            kafka.send("orders", String.valueOf(e.aggregateId()), e.payload()).get();
            jdbc.sql("UPDATE outbox_events SET published = true WHERE id = :id")
                    .param("id", e.id()).update();
        }
    }
}

record OutboxEvent(long id, long aggregateId, String eventType, String payload) {}

@Scheduled only runs when the application has @EnableScheduling enabled.

• FOR UPDATE SKIP LOCKED allows multiple relay instances to run in parallel without processing the same row.
• The message key is aggregateId. All events for the same order fall into the same partition and maintain order.
• .get() waits for Kafka acknowledgment before marking published. If the relay dies midway, the event might be resent. Outbox guarantees at-least-once, so consumers must be idempotent, e.g., track processed orderIds.

6.4. Payment Expiration

A scheduled job cancels expired orders and restores inventory:

WITH expired AS (
    UPDATE orders SET status = 'CANCELLED'
     WHERE status = 'PENDING_PAYMENT' AND expires_at < now()
    RETURNING id
)
UPDATE inventory i
   SET available = i.available + oi.quantity,
       reserved  = i.reserved  - oi.quantity
  FROM order_items oi
 WHERE oi.order_id IN (SELECT id FROM expired) AND oi.sku = i.sku;

The status = 'PENDING_PAYMENT' condition arbitrates with the payment callback. The callback must also transition status with UPDATE ... WHERE status = 'PENDING_PAYMENT'. Whoever updates first wins; the other updates 0 rows. If payment arrives after cancellation, the system refunds instead of “resurrecting” the order.

The statement assumes each SKU appears at most once per order, guaranteed by the primary key (order_id, sku). If a batch cancellation involves many orders with the same SKU, PostgreSQL applies one join per inventory row. In that case, pre-aggregate using GROUP BY sku and SUM(quantity).

7. Spike Load: Flash Sales

When 5,000 req/s all contend for one SKU, they all queue waiting to lock one row in inventory. The database becomes congested, latency spikes, dragging down other SKUs. The solution is to throttle traffic before it hits the DB using a Redis-based inventory counter with Lua script:

@Component
public class FlashSaleGate {

    private static final RedisScript<Long> TRY_ACQUIRE = RedisScript.of("""
            local stock = tonumber(redis.call('GET', KEYS[1]) or '0')
            local qty = tonumber(ARGV[1])
            if stock >= qty then
                redis.call('DECRBY', KEYS[1], qty)
                return 1
            end
            return 0
            """, Long.class);

    private final StringRedisTemplate redis;

    public FlashSaleGate(StringRedisTemplate redis) {
        this.redis = redis;
    }

    public boolean tryAcquire(String sku, int quantity) {
        Long ok = redis.execute(TRY_ACQUIRE, List.of("flash:stock:" + sku), String.valueOf(quantity));
        return ok != null && ok == 1L;
    }

    public void release(String sku, int quantity) {
        redis.opsForValue().increment("flash:stock:" + sku, quantity);
    }
}

Redis runs Lua scripts atomically. No command can interleave between GET and DECRBY. With 1,000 slots and 50,000 requests, only ~1,000 pass the gate into OrderService. The other 49,000 get HTTP 409 “Sold out” immediately, without any DB query.

The workflow integrates like this:

tryAcquire (Redis)  --false-->  409 Sold out
       |true
placeOrder (PostgreSQL, conditional update vẫn giữ nguyên)
       |exception
release (Redis)

Redis is just a filter. PostgreSQL’s conditional update remains the source of truth. If Redis drifts, e.g., from a restart or missed release, the worst outcome is underselling, never overselling.

Other supporting measures:

• Rate limit per customer to block bot spam.
• Queuing: push winning requests to Kafka, consumers create orders at DB’s sustainable rate, client polls GET /api/orders/{id}. This trades latency for stability.
• Isolate hot SKUs to a separate table or instance to avoid impacting regular orders.

8. Testing Inventory Contention

The most important test is proving the system never oversells when many threads buy simultaneously.

@JdbcTest
@Import(InventoryRepository.class)
@Transactional(propagation = Propagation.NOT_SUPPORTED)
class InventoryRepositoryTest {

    @Autowired JdbcClient jdbc;
    @Autowired InventoryRepository inventory;

    @BeforeEach
    void setUp() {
        jdbc.sql("""
                CREATE TABLE IF NOT EXISTS inventory (
                  sku VARCHAR(64) PRIMARY KEY,
                  available INT NOT NULL CHECK (available >= 0),
                  reserved INT NOT NULL DEFAULT 0)
                """).update();
        jdbc.sql("DELETE FROM inventory").update();
        jdbc.sql("INSERT INTO inventory (sku, available) VALUES ('IPHONE-16', 5)").update();
    }

    @Test
    void givenStockOf5_when20ConcurrentBuyers_thenExactly5Succeed() throws Exception {
        try (var executor = Executors.newVirtualThreadPerTaskExecutor()) {
            List<Future<Boolean>> results = IntStream.range(0, 20)
                    .mapToObj(i -> executor.submit(() -> inventory.reserve("IPHONE-16", 1)))
                    .toList();

            long success = 0;
            for (Future<Boolean> f : results) {
                if (f.get()) success++;
            }
            assertThat(success).isEqualTo(5);
        }

        Integer left = jdbc.sql("SELECT available FROM inventory WHERE sku = 'IPHONE-16'")
                .query(Integer.class).single();
        assertThat(left).isZero();
    }
}

• @Transactional(propagation = NOT_SUPPORTED) disables the default transaction of @JdbcTest. Without it, data inserted in setUp isn’t committed and other threads can’t see it.
• The test uses Java 21 virtual threads to create 20 concurrent requests.
• The embedded database of @JdbcTest (H2) is sufficient for this check. To test ON CONFLICT and SKIP LOCKED, use real PostgreSQL via Testcontainers.

Expected result:

success = 5, available = 0

If you replace the UPDATE with “SELECT then compare in Java”, this test will randomly fail, sometimes with success reaching 7 or 8.

9. Common Mistakes and When Not to Use

• Storing price in cart and reusing it at checkout. An attacker could hold an old price or modify the payload. Always fetch price from the server.
• Reserving inventory when adding to cart. Users abandon carts a lot, so inventory gets unnecessarily “locked up”. Only reserve at checkout and always with an expiry.
• Calling Kafka or payment service inside @Transactional. Long transactions hold locks longer and don’t guarantee consistency anyway. Use outbox.
• Treating Redis as source of truth for inventory. Redis should only be a filter layer.
• Using distributed transaction (2PC/XA) across services. This is complex and slow. Use event-driven sagas with compensating steps (cancel order, refund inventory).

Note: If your platform only has tens of orders per minute, a single database with conditional updates and idempotency keys is sufficient. Redis gates, Kafka queues, and hot SKU isolation are only worth adding when metrics show the DB is truly congested.

10. Conclusion

This article has designed shopping cart and order workflow layer by layer. The cart sits on Redis Hash for speed and eventual consistency. Orders and inventory sit on PostgreSQL for strong consistency.

Three core techniques solve the challenges:

• Conditional update prevents overselling.
• Idempotency key with UNIQUE constraint prevents duplicate orders.
• Transactional outbox ensures no lost events.

During flash sales, a Redis gate running Lua script throttles traffic before hitting the database, while the database remains the source of truth. You can start with the simplest version and add layers as real load demands.