Project Walkthrough: Building an E-Commerce Backend with Transactional Order Placement
Design and build the backend for an e-commerce checkout flow in PHP: products and inventory tracking, a cart, order creation wrapped in a database transaction with inventory decrement and payment recording, idempotency keys on the place-order endpoint, and dispatching an order-placed event to a queue for asynchronous confirmation notifications — combining database transactions, concurrency, idempotency, and queueing lessons already covered in the course.
This final guided project is the most concurrency-sensitive of the three: an e-commerce checkout has to get money and inventory right even when two customers try to buy the last item at the same second, or when a flaky network causes a client to submit the same 'place order' request twice. We'll build the backend for a simplified store — products with tracked stock, a cart, and an order-placement flow — and spend most of our attention on the parts that are easy to get subtly wrong: wrapping the order, the inventory decrement, and the payment record in one all-or-nothing database transaction; adding an idempotency key so a retried request can't create two orders or charge twice; and dispatching the actual confirmation email to a background queue instead of sending it inline and making the customer wait on an SMTP round trip. Every piece here draws directly on earlier lessons — transactions, row locking, the idempotency-keys lesson, and the queueing/background-jobs lesson — applied together in one realistic flow.
Project Overview and Requirements
The backend needs to support: browsing products (each with a name, price, and stock_quantity); a cart that a customer builds up over multiple requests (we'll back it with the database, keyed by a cart ID stored in the session, rather than storing the whole cart in the session itself, so it survives across devices and is easier to reason about transactionally); and orders, created from a cart's contents at checkout time. The one operation the entire project centers on is POST /orders — placing an order — because it's where every hard problem in this domain shows up at once.
Placing an order must, atomically, do three things: verify there's still enough stock for every item in the cart (since time has passed since the item was added to the cart, and other customers may have bought the same item meanwhile), decrement stock_quantity for each purchased item, and create both an orders row and its order_items rows plus a payments row recording the (simulated, for this project) charge. If any one of those steps fails — insufficient stock discovered mid-transaction, the simulated payment gateway declining the charge — none of them should take effect; a half-completed order (money charged but no order recorded, or an order recorded but stock never decremented) is a serious, real-world-costly bug class.
On top of correctness under a single request, the endpoint must also be safe against being called twice with the same intent — a customer double-clicking "Place Order", or a client retrying after a timeout that the server actually handled. That's what an idempotency key solves. And because sending a confirmation email shouldn't make the customer's browser wait on an external mail service, the actual notification work is dispatched to a queue and processed by a separate worker process.
• products(id, name, price, stock_quantity); cart backed by the database, not just the session. • POST /orders: verify stock, decrement inventory, create order + payment record — all atomically. • Idempotency key required on POST /orders to make retries safe. • Order confirmation is dispatched to a queue, not sent synchronously inline.
Schema Design: Products, Cart, and Orders
The schema: products (id, name, price_cents, stock_quantity — prices stored as integer cents, per the classic advice against using floating-point for money, covered in the numeric-types lesson); carts (id, user_id, created_at) and cart_items (cart_id, product_id, quantity); orders (id, user_id, status, total_cents, idempotency_key, created_at) and order_items (order_id, product_id, quantity, unit_price_cents — snapshotting the price at purchase time, since a later price change shouldn't retroactively alter a historical order); and payments (id, order_id, amount_cents, status, gateway_reference, created_at).
orders.idempotency_key carries a unique index — this single constraint is what makes safe retries possible, because it lets the database itself reject (or, more usefully, let the application detect and short-circuit on) a second insert attempt with the same key, rather than relying purely on application-level logic that could itself race. products.stock_quantity needs no special schema treatment beyond being an integer, but the way it's read and updated inside the transaction is where the real correctness work happens, covered next.
It's worth noting the deliberate snapshot of unit_price_cents onto order_items rather than joining back to products.price_cents at display time — orders are historical records, and a product's price changing next month must never silently rewrite what a customer was actually charged for a completed order. This is a common real-world data-modeling mistake: treating a foreign-key join as a substitute for capturing a fact as of the moment it happened.
• Store money as integer cents, never floats, to avoid rounding errors. • orders.idempotency_key has a unique index — the actual mechanism enabling safe retries. • order_items snapshots unit_price_cents at purchase time instead of joining live product prices. • payments table records amount, status, and a gateway reference per order.
Core Implementation: The Order Placement Transaction
OrderService::placeOrder() is the heart of the project. It begins a database transaction, then for each cart item, re-reads the product row with SELECT ... FOR UPDATE (a row lock, from the transactions and locking lesson) rather than trusting a stock number read earlier in the request — this closes the classic race condition where two concurrent checkouts both read "3 in stock", both decide there's enough for their 2-item purchase, and both proceed, overselling by one unit. FOR UPDATE makes the second transaction's read block until the first transaction commits or rolls back, so it sees the already-decremented number and can correctly detect insufficient stock instead of racing past the check.
After confirming stock for every item, the service decrements stock_quantity for each product, inserts the orders and order_items rows, calls out to the (simulated) payment gateway, and inserts the payments row — all before committing. If the stock check fails for any item, or the simulated payment is declined, the entire transaction is rolled back: the stock decrements that already happened earlier in the loop are undone automatically because they were never committed, which is exactly the guarantee a transaction exists to provide, and precisely why all of this logic lives inside one transaction rather than being three separate database calls with application-level cleanup on failure.
The payment gateway call is written as if it were an external HTTP call to a real processor (Stripe, Braintree, etc.) — in a real system, calling an external service from inside a database transaction is itself a tradeoff worth naming explicitly: holding the transaction (and its row locks) open for the duration of a network call increases lock contention and the blast radius of a slow gateway. A more advanced design reserves the stock inside a short transaction, calls the gateway outside any transaction, and then finalizes the order in a second short transaction — but for this project's scope, doing the simulated charge inline keeps the all-or-nothing guarantee simplest to reason about, with the tradeoff called out explicitly rather than hidden.
• SELECT ... FOR UPDATE locks each product row before checking/decrementing stock, preventing overselling. • Stock decrement, order/order_items insert, and payment insert all happen inside one transaction. • Any failure (insufficient stock, declined payment) rolls back everything, including earlier decrements in the same request. • Calling an external gateway inside a transaction is a real tradeoff — named explicitly, not hidden.
Idempotency: Making Retries Safe
The client is required to generate a unique idempotency key (typically a UUID) per checkout attempt and send it as an Idempotency-Key header, exactly as described in the earlier idempotency-keys lesson. Before doing any work, OrderService::placeOrder() checks whether an order already exists with that key: if one does, it returns the existing order's response immediately rather than processing anything again — meaning a client that times out waiting for a response and retries with the same key gets back the result of the original attempt (whatever it was) instead of creating a second order or charging the customer twice.
The uniqueness guarantee is anchored at the database level, not just in application logic, via the unique index on orders.idempotency_key — the flow is: check for an existing order with this key; if none, attempt the insert inside the transaction; if the insert violates the unique constraint (because a concurrent request with the same key beat this one to it), catch that specific database error and treat it exactly like the 'already exists' case, fetching and returning the row the other request created. This closes a subtle race that a check-then-insert without a unique constraint would leave open: two nearly-simultaneous requests with the same key could both pass the initial 'does it exist' check before either has inserted.
It's worth being precise about what an idempotency key protects: it lives at the level of one specific write operation ("place this order"), not the entire user session, and a well-behaved client generates a *new* key for a genuinely new checkout attempt (a different cart, or the same cart after abandoning and restarting) while *reusing* the same key only when retrying an attempt that may or may not have succeeded. Getting this distinction wrong in either direction either defeats the protection (new key every retry) or wrongly blocks legitimate repeat purchases (same key reused for unrelated orders).
• Client sends an Idempotency-Key header (typically a UUID) with every place-order request. • Existing order with that key → return its result immediately, no reprocessing. • Unique DB index on idempotency_key is the actual guarantee; a race on insert is caught and treated as 'already exists'. • A genuinely new checkout attempt needs a new key; a retry of the same attempt reuses the same key.
Queues and Notifications: Order Confirmation
Once the transaction commits successfully, the API response can return to the customer immediately — but the order isn't fully 'done' from the business's perspective until a confirmation email goes out. Sending that email synchronously, inside the request, would make the customer's checkout wait on an SMTP round trip (or a third-party email API call) that has nothing to do with whether their order succeeded, and any transient failure of the email provider would have no business affecting the HTTP response for a successfully placed order.
Instead, right after the transaction commits, the service dispatches an OrderPlaced event/job onto a queue (Redis-backed, via the queueing lesson's patterns, or a simple database-backed jobs table for a smaller deployment) carrying just the order ID — deliberately not the full order payload, since the worker that eventually processes the job should re-fetch current data rather than trust a potentially stale snapshot serialized minutes or hours earlier if the queue is backed up. A separate worker process (php artisan queue:work-style, or a custom polling script) picks up the job, loads the order, renders and sends the confirmation email, and marks the job complete.
This decoupling has a second benefit beyond response latency: if the email provider is down, the job simply retries (with backoff, per the queueing lesson's failure-handling guidance) without affecting order placement at all — the order already committed successfully regardless of the notification's fate. It's important that placing the order and sending the notification are allowed to succeed or fail independently; conflating them (e.g. rolling back the order because the email failed) would be a serious design mistake, since a notification failure should never undo a legitimately placed and paid order.
• OrderPlaced job dispatched to a queue only after the order transaction has committed. • Job carries the order ID, not a serialized snapshot — the worker re-fetches current data. • A separate worker process consumes the queue and sends the actual email. • Notification failures retry independently and never roll back an already-placed order.
Testing and Extending the Checkout Flow
Testing this project means specifically testing the failure and concurrency paths, not just the happy path: a PHPUnit test asserts that placing an order for more units than are in stock leaves stock_quantity unchanged and returns an error rather than a partial order; another asserts that calling placeOrder() twice with the same idempotency key returns the same order ID both times and inserts exactly one row into orders; and, where the test environment supports it, a test using two concurrent connections against a real database (not SQLite, which doesn't support real row locking the same way) verifies that SELECT ... FOR UPDATE actually serializes two simultaneous purchases of the last unit of stock, rather than trusting the code to be race-free from reading it.
From here, the natural extensions mirror what a real payment/checkout system eventually needs: webhook handling for asynchronous payment confirmations (some gateways confirm a charge after your request already returned, requiring a separate webhook endpoint that itself needs idempotent handling), partial refunds and order cancellation (each its own transaction touching inventory and payments symmetrically to placement), and inventory reservations with an expiry (holding stock for a cart for, say, 15 minutes before releasing it back, to avoid stock being silently claimed by an abandoned cart indefinitely). None of these require rethinking the core pattern established here — a transaction around the state change, an idempotency key on any externally-triggerable write, and a queue for anything that doesn't need to block the response.
• Test that an over-stock order attempt leaves inventory unchanged and returns an error. • Test that a repeated idempotency key produces exactly one order, not two. • Concurrency tests need a real database engine with real row locking, not SQLite. • Natural extensions: payment webhooks, refunds/cancellation, timed inventory reservations.
Code Examples
SELECT ... FOR UPDATE locks each product row before the stock check, so a concurrent transaction attempting to buy the same product blocks until this one commits or rolls back, closing the overselling race. The whole stock-decrement/order/payment sequence lives inside one transaction so any failure rolls all of it back together, and the queue push happens only after commit so a notification problem can never undo a successfully placed and paid order. A unique-constraint violation on idempotency_key is caught and treated as 'this order already exists', returning the winning request's result instead of erroring.
The job re-queries the order rather than trusting stale data carried in the payload, since a queue can be backed up and this job might run minutes after being enqueued. The commented worker loop shows the retry-on-failure pattern from the queueing lesson: a failed send (e.g. the mail provider is temporarily down) gets retried with backoff instead of silently dropping the confirmation.
The first test calls placeOrder() twice with the same key and asserts exactly one order exists and stock was decremented only once — proving the idempotency guard works, not just that it exists in the code. The second test asserts that an over-stock attempt leaves stock completely unchanged, proving the transaction actually rolls back rather than partially applying the decrement before the check fails.
// Reading stock, then checking it in PHP without locking the row $stock = $db->query("SELECT stock_quantity FROM products WHERE id = {$id}")->fetchColumn(); if ($stock >= $qty) { /* decrement and insert order */ }
$stmt = $db->prepare('SELECT stock_quantity FROM products WHERE id = :id FOR UPDATE'); $stmt->execute(['id' => $id]); // ... check, then decrement, all inside the same transaction
Without FOR UPDATE, two concurrent requests can both read the same stock number before either decrements it, both conclude there's enough, and both proceed — overselling the product. Locking the row makes the second transaction wait and see the already-decremented value.
// Sending the confirmation email inline, inside the same request $service->placeOrder(...); $mailer->send($order['email'], 'Order confirmed', ...); // blocks the response on SMTP
// Commit the order transaction, then push a job onto a queue for a worker to process $queue->push('OrderPlaced', ['order_id' => $orderId]);
Sending email synchronously ties the customer's checkout response time to an unrelated external service, and a transient mail-provider failure has no business affecting a successfully placed order's HTTP response. Dispatching to a queue decouples the two entirely.
// Generating a brand-new idempotency key on every retry of the same request $key = bin2hex(random_bytes(16)); // regenerated each attempt
// Client generates ONE key per checkout attempt and reuses it for any retry of that same attempt
An idempotency key only protects against duplicate processing if the same key is reused across retries of the same logical request. Generating a fresh key on every retry defeats the entire mechanism — each retry looks like a brand-new order to the server.
What specific problem does SELECT ... FOR UPDATE solve in the order placement flow? A. It speeds up the query by adding an index automatically B. It prevents two concurrent transactions from both reading stale stock counts and overselling the same product C. It encrypts the row's data while it's being read D. It automatically retries the query if it fails
B. It prevents two concurrent transactions from both reading stale stock counts and overselling the same product — FOR UPDATE locks the selected row so a second transaction trying to read/update the same product must wait until the first commits or rolls back, closing the classic read-then-write race condition that causes overselling.
Why is the OrderPlaced job dispatched to the queue only AFTER the database transaction commits, not before or during? A. Queues can only accept jobs after a transaction, as a technical limitation B. So that if the transaction rolls back (e.g. payment declined), no notification is ever sent for an order that was never actually placed C. It has no effect on correctness; it's done for performance only D. Because PHP requires all queue operations to happen after database commits
B. So that if the transaction rolls back (e.g. payment declined), no notification is ever sent for an order that was never actually placed — Dispatching before the commit risks notifying a customer about an order that then fails to commit (e.g. because the payment was declined), since the queue push isn't part of the database transaction and can't be rolled back with it.
A client calls POST /orders, times out waiting for a response, and retries with the same Idempotency-Key. What should happen if the original request actually succeeded on the server? A. The retry creates a second, duplicate order B. The retry is rejected with a generic error C. The retry returns the result of the original successful order, without creating a new one or charging again D. The server has no way to know and must ask the customer to check their bank statement
C. The retry returns the result of the original successful order, without creating a new one or charging again — That's precisely the guarantee an idempotency key provides: the server recognizes the key has already been processed and returns the original result, making the retry safe.
Why does order_items store unit_price_cents directly rather than looking up the current price from the products table when displaying an order? A. Storing a copy is faster to write in SQL B. products.price_cents doesn't support being read more than once C. An order is a historical record; if the product's price changes later, past orders must still reflect what the customer actually paid at purchase time D. It's required by PCI compliance rules for all monetary fields
C. An order is a historical record; if the product's price changes later, past orders must still reflect what the customer actually paid at purchase time — Joining to a live products.price_cents would make a price change retroactively alter what historical orders appear to have charged, which is factually wrong — the order must snapshot the price as of the moment of purchase.
<?php
declare(strict_types=1);
final class OrderService
{
public function __construct(
private PDO $db,
private PaymentGatewayInterface $gateway,
private QueueInterface $queue
) {}
public function placeOrder(int $userId, array $cartItems, string $idempotencyKey): array
{
// Fast path: an order with this key already exists — return it, do no new work.
$existing = $this->findByIdempotencyKey($idempotencyKey);
if ($existing !== null) {
return $existing;
}
$this->db->beginTransaction();
try {
$totalCents = 0;
$lockedItems = [];
foreach ($cartItems as $item) {
$stmt = $this->db->prepare(
'SELECT id, price_cents, stock_quantity FROM products WHERE id = :id FOR UPDATE'
);
$stmt->execute(['id' => $item['product_id']]);
$product = $stmt->fetch(PDO::FETCH_ASSOC);
if ($product === false || $product['stock_quantity'] < $item['quantity']) {
throw new InsufficientStockException(
"Not enough stock for product {$item[
…<?php
declare(strict_types=1);
final class OrderPlacedJob
{
public function __construct(private PDO $db, private MailerInterface $mailer) {}
public function handle(array $payload): void
{
$orderId = (int) $payload['order_id'];
// Re-fetch current data rather than trusting a payload snapshotted
// whenever this job was originally enqueued, which may have been queued
// for a while behind other work.
$stmt = $this->db->prepare(
'SELECT o.id, o.total_cents, u.email, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.id = :id'
);
$stmt->execute(['id' => $orderId]);
$order = $stmt->fetch(PDO::FETCH_ASSOC);
if ($order === false) {
// Order vanished or was never committed — nothing to notify about.
return;
}
$this->mailer->send(
to: $order['email'],
subject: "Order #{$order['id']} confirmed",
body: sprintf(
"Hi %s,\n\nYour order #%d for $%.2f has been confirmed.\n\nThanks for shopping with us!",
$order['name'],
$order['id'],
…<?php
declare(strict_types=1);
use PHPUnit\Framework\TestCase;
final class OrderIdempotencyTest extends TestCase
{
public function testRepeatedIdempotencyKeyCreatesOnlyOneOrder(): void
{
$db = new PDO('sqlite::memory:');
$db->exec(file_get_contents(__DIR__ . '/../schema.sql'));
$db->exec("INSERT INTO products (id, name, price_cents, stock_quantity)
VALUES (1, 'Keyboard', 5000, 10)");
$db->exec("INSERT INTO users (id, name, email, password_hash, role)
VALUES (1, 'Ada', '[email protected]', 'x', 'customer')");
$gateway = new FakeAlwaysSucceedsGateway();
$queue = new FakeQueue();
$service = new OrderService($db, $gateway, $queue);
$cart = [['product_id' => 1, 'quantity' => 2]];
$key = 'idem-key-123';
$first = $service->placeOrder(1, $cart, $key);
$second = $service->placeOrder(1, $cart, $key);
$this->assertSame($first['order_id'], $second['order_id']);
$count = $db->query('SELECT COUNT(*) FROM orders')->fetchColumn();
$this->assertSame(1, (int) $count);
$stock = $db->query('SELECT stock_quantity FROM products
…