E-commerce and retail fundamentals

Four words candidates use interchangeably, and the schema is where the confusion becomes expensive.

product      the thing a customer shops for. Marketing lives here.
  product_id, title, description, brand, category_id, status

variant      one buyable configuration of that product. Options live here.
  variant_id, product_id, option_values {colour: navy, size: M},
  barcode, weight_grams, status

sku          the stock-keeping unit. Inventory is counted against this.
  sku_id, variant_id, pack_size, supplier_ref

listing      the offer: this SKU, on this channel, by this seller, at this price.
  listing_id, sku_id, channel_id, seller_id, price_minor, currency,
  valid_from, valid_to, listing_status

The conflation that hurts, seen constantly:

  product(id, title, colour, size, price, stock_count)

The flat table cannot express a t-shirt. Navy medium and navy large are separate things to count, ship and photograph, but one thing to describe and rank, and a single row forces you to choose which. Teams that choose product duplicate the description eight times and diverge; teams that choose variant lose the concept a customer actually searches for.

Separating SKU from variant looks like over-modelling until a supplier ships the same navy medium as a single and as a pack of three, or until two variants share one physical item under different barcodes. Inventory is counted against the thing on the shelf, which is the SKU, and price is quoted against the thing on offer, which is the listing.

The listing is the entity most people omit, and it is the one that decides whether you can ever become a marketplace or sell on a second channel. Price, availability and even title vary by channel and by seller, so they belong on an offer row and not on the product. A price column on the product means every subsequent requirement — a different price in Ireland, a marketplace seller undercutting you, a wholesale tier — becomes a schema migration.

The effective dating on the listing is what makes a price change auditable. A mutated price column answers "what does this cost" and nothing else, and the question you will actually be asked is what it cost on the fourteenth of March, by a customer holding a screenshot.

Getting complete, correct, consistent attributes for hundreds of thousands of products from suppliers who each supply a different subset in a different format. The hard part is not storage, it is workflow: a product arrives with a title and a price, and cannot be published until it has images, dimensions, a category, a material and whatever the destination channel demands. So a PIM is an enrichment pipeline with per-attribute ownership, completeness scoring against a channel's requirements, and a review state — not a table of columns. The consequence engineers miss is that publication is a gate rather than a flag, and a product blocked on a missing hazardous-goods field is a merchandising problem with a queue behind it.

As data, not as columns, because the attribute set differs per category and changes weekly. A television needs a screen size and a panel type, a wine needs a vintage and a region, and adding a category must not require a migration. So you carry an attribute definition table — name, data type, unit, allowed values, which categories require it — and store values against it. The cost is that typed validation moves into your code rather than the database, and that querying becomes awkward, which is why the search index rather than the source table serves faceted browse. The taxonomy itself needs to be a graph rather than a tree, since a product legitimately belongs under both "gifts for her" and "handbags", and a strict tree forces duplicates.

They are large, immutable, numerous and required in many renditions, so they belong in an object store with a CDN in front and never in the database. Each image needs a defined role — primary, alternate, swatch, lifestyle — because the front end must pick deterministically rather than take whatever is first. The processing is a pipeline: ingest, validate dimensions and colour space, generate renditions, and publish. Two details cause real incidents. Assets are versioned by URL rather than overwritten, because overwriting means a stale CDN copy shows the old jacket for hours. And asset readiness gates publication, since a product page with a missing hero image converts worse than no product page.

Because every browse, search, category page and recommendation reads it while merchandisers write it a few thousand times a day, giving read-to-write ratios in the thousands. That asymmetry means the write model and the read model should not be the same shape. The source of truth is normalised and validated for correctness; the read path is a denormalised document per product containing everything a page needs, published to a cache, a CDN and a search index. The consequence is a projection you must be able to rebuild and must monitor for staleness, because the failure mode is not an error but a product page showing last week's price. Publication therefore becomes an event with a lag metric somebody owns.

Because attributes are what search and browse run on. A product with no colour attribute cannot be filtered by colour, so it is invisible to the customer who filters — which is most of them — and a product with the wrong category is invisible everywhere. Missing dimensions mean shipping is estimated wrongly and either loses margin or loses the sale at the shipping step. Wrong or absent attributes also drive returns, since "not as described" is a return reason generated by your own data. This is why completeness is tracked per channel as a metric with a target, and why enrichment gets funded: it is one of the few engineering-adjacent programmes with a directly attributable conversion effect.

Everything quietly. The PIM enriches a title, the ERP overwrites it from the supplier feed on the nightly load, and each system is behaving correctly. The symptom is a field that reverts, reported as a flaky UI, and the cause is undefined ownership at attribute granularity rather than at record granularity — merchandising owns the title and the imagery, finance owns the cost, the supplier owns the barcode and the pack size. So the fix is an ownership map and a rule that a non-owning system may read but never write, enforced at the integration rather than by convention. Without it, every feed is a race and the last writer of the night wins.

Because it is a model of the physical world maintained by fallible processes. Items are miscounted at receipt, damaged without being written off, stolen, misplaced on the wrong shelf, or picked and abandoned. Every one of those makes the recorded count diverge from the shelf, and nothing in your software can detect it. Retailers therefore treat the number as an estimate with a known error rate, measured by cycle counting rather than an annual stocktake, and expressed as inventory accuracy — a figure below ninety-eight per cent is common and materially affects what you can promise. The engineering consequence is that availability logic must tolerate being wrong, which is why oversell handling is a designed path rather than an exception, and why "the count said one" is never a defence.

Availability is not a column. It is an expression, and every term in it is a policy decision somebody made.

SKU 88214, single fulfilment location, ATP for a customer today

  on_hand                  physically in the building        140
  reserved_open_orders     picked or awaiting pick           -32
  reserved_carts           soft holds during checkout        -6
  quarantine               received, not yet inspected       -10
  damaged                  written off, not yet removed      -3
  safety_stock             buffer against count error        -15
  ---------------------------------------------------------------
  available now                                               74

  inbound_po_4471          confirmed, due in 3 days         +200
  ---------------------------------------------------------------
  ATP within 3 days                                          274
  ATP within 3 days, oversell tolerance 2%                   279

The two answers are both correct and mean different things. Seventy-four is what you can ship today; two hundred and seventy-four is what you can sell today for delivery in three days. A product page that shows one number has silently chosen one of them, and choosing "available now" costs you every sale you could have supplied from the inbound purchase order.

Safety stock and quarantine are where the interesting arguments live. Quarantine stock physically exists and cannot be sold, so a system reading on_hand from the warehouse will oversell exactly the quantity sitting on the goods-in dock. Safety stock is deliberate suppression to absorb the count error described above, and its size is a function of measured inventory accuracy and the cost of a failed order rather than a round number someone typed.

Including inbound is what makes ATP more than a subtraction, and it is also what makes it a promise you can break. A purchase order is a supplier's intention, so selling against it inherits the supplier's reliability, and the mitigation is to discount the inbound quantity by that supplier's historical on-time rate rather than to count it in full.

The last line is the one that separates candidates. A negative-tolerance allowance — deliberately selling slightly more than you have — is a commercial setting per category, high for a replenishable staple and zero for a one-of-a-kind item, and it belongs in configuration next to safety stock rather than buried in a service.

A soft allocation is a short-lived, expiring claim on stock made when a customer begins checkout, reducing what others can see without committing anything. A hard allocation is a durable assignment of specific units to a specific order once payment is taken, which the warehouse will pick against. The distinction matters because the two have opposite failure preferences. A soft hold must expire, since abandoned carts would otherwise consume your entire inventory within a day, and the expiry has to release the stock even if the process that created it died. A hard allocation must not expire, because a customer has been charged, so releasing it silently is worse than holding stock idle.

One unit, two customers, and the interleaving that a check-then-decrement always eventually produces.

sequenceDiagram
    participant A as Customer A
    participant B as Customer B
    participant S as Stock service
    A->>S: Read available for SKU 88214
    S-->>A: One unit available
    B->>S: Read available for SKU 88214
    S-->>B: One unit available
    A->>S: Place order, decrement to zero
    B->>S: Place order, decrement to minus one
    S-->>B: Accepted, and now unfulfillable

The read and the decrement are two operations with a gap between them, and under flash-sale load that gap is where every oversell comes from. Nothing errors, both customers see a confirmation, and one of them receives an apology email two days later.

The fix is to make the claim itself the atomic operation rather than guarding it with a prior read. A conditional update — decrement where available is greater than zero, and treat zero rows affected as sold out — pushes serialisation into the database, which is the only component that can do it. A reservation row per claim, with a uniqueness constraint on the unit for genuinely unique items, gives the same guarantee and additionally records who holds what.

The expiry is the half candidates forget, and it is what makes reservations usable rather than a denial-of-service surface. A reservation carries a deadline of a few minutes, is extended when the customer progresses through checkout, and is released by a sweeper that does not depend on the abandoning client doing anything. The release must be idempotent, since the sweeper and a late cancellation will both try.

The cost of getting this right is throughput on the hot row. Ten thousand concurrent claims on one SKU serialise on a single record, so the row becomes the bottleneck exactly when it matters. The standard answer is to shard the counter into buckets and claim from one at random, accepting a small number of false sold-out responses when the buckets drain unevenly, or to move the contended decrement into a queue and turn the race into an ordered admission — a virtual waiting room, which is what large drops actually do.

Stock deliberately withheld from sale to absorb the difference between the recorded count and reality, plus demand variability during the replenishment lead time. It is a commercial parameter rather than an engineering one, set per category and often per location by whoever owns the trade-off, because raising it reduces oversells and reduces sales. The inputs are measured inventory accuracy, supplier lead time and its variance, and the cost of a broken promise relative to the cost of a lost sale — which differs enormously between a five-pound consumable and a wedding dress. Engineering's obligation is to make it configurable at the granularity the business needs and to expose the oversell rate it is buying, so the setting can be tuned against evidence.

Sometimes, and the interesting answer is that it is usually a business decision. Zero oversells requires suppressing so much stock that you lose more revenue to false sold-outs than you would to apologies, so most retailers deliberately run a non-zero oversell rate and manage it. What is a bug is an oversell you cannot detect, an oversell with no defined resolution, or an oversell caused by a race rather than by physical drift. The distinction is worth stating in an interview because it reframes the question: the goal is not prevention but a chosen rate with a handling path — substitute, partial ship, backorder with a date, or cancel with an immediate refund and something to compensate — and the ability to say which one happened and how often.

Multi-location turns one number into a matrix, and the aggregate is almost never the number to show.

SKU 88214, network view

location            on_hand  reserved  sellable  ships?  collect?  lead
------------------  -------  --------  --------  ------  --------  --------
DC-North                 90       -32        58  yes     no        next day
DC-South                  0         0         0  yes     no        --
Store-Leeds              12        -2         4  yes     yes       same day
Store-Bristol             6        -1         0  no      yes       same day
Store-Cardiff             3         0         0  no      no        --
                                    ---------------
network total sellable                        62

  Store-Leeds sellable 4, not 10: safety stock of 6 per store, because
  a shop-floor count is less accurate than a racked one.
  Store-Bristol sellable 0: below its safety stock, so collect only.
  Store-Cardiff excluded: not enabled for any customer-facing fulfilment.

The network total is the wrong number for a product page and the right number for merchandising. A customer in Bristol cannot have the Leeds unit delivered same day, so availability is a function of the customer's location and chosen fulfilment method, not a property of the SKU. Showing sixty-two and then failing at the delivery step is the most expensive place in the funnel to be wrong.

Per-location safety stock is higher for stores than for distribution centres for a concrete reason: a shop-floor item may be in a fitting room, behind a counter, or in a customer's hand, and none of those states is recorded. Store inventory accuracy is routinely several points worse than warehouse accuracy, so the same policy expressed as one global number either oversells the stores or suppresses the warehouse.

The ships and collect flags are the part that gets modelled too late. Enablement is per location and per method, changes for operational reasons — a store short-staffed this week stops shipping — and must be settable without a deploy. Systems that infer eligibility from stock alone cannot turn a location off, and the workaround is to zero its inventory, which corrupts the numbers merchandising is planning from.

Sourcing then reads this matrix rather than a total, choosing the location that minimises cost and split count while meeting the promise, which is why the availability query and the sourcing query want the same data and different answers from it.

An append-only record of every movement — received, put away, picked, shipped, returned, adjusted, written off — from which the current count is derived rather than stored. It exists because a mutable stock_count column answers "how many" and nothing else, and the questions you get asked are why the count changed, who changed it, and where the eleven missing units went. The ledger makes shrinkage visible, makes an adjustment an auditable event with a reason code rather than an UPDATE, and lets you reconstruct availability at any past moment when investigating an oversell. The cost is the familiar one: deriving a count by summing movements is too slow for a product page, so you keep a periodic snapshot plus the movements since, rebuildable at any time.

Ephemeral, high-write, low-value state that nonetheless must survive a device change and a two-week gap. That combination is why it is usually neither a plain session nor a first-class order: it is a keyed document with a generous expiry, cheap to write, tolerant of loss, and explicitly not a promise about price or availability. Treating it as durable business data invites the two classic mistakes — validating stock at add-to-cart, which reserves inventory for people who will never buy, and freezing the price at add-to-cart, which either sells at yesterday's price or surprises the customer at checkout. The correct posture is that a cart holds intent, and every price, tax and availability figure in it is a cached display value revalidated at checkout.

The gap between carts created and orders placed, which is around seventy per cent industry-wide and is mostly not a failure. Most carts are wish lists, price comparisons or research, so the metric is only useful when decomposed: abandoned before entering checkout is a merchandising and pricing signal, abandoned at the shipping step is usually cost or delivery-date shock, and abandoned at the payment step is usually a mechanical problem you own. The engineering value is in instrumenting the transitions well enough to tell those apart, because they have different owners and different fixes. Recovery email campaigns get the attention, but the cheapest wins are almost always removing a required field or fixing a payment method that silently fails on one browser.

Because forcing account creation inserts a mandatory step, with a password and an email verification, between a customer holding their card and giving you money — and a measurable fraction of them leave there. The counter-argument is real: accounts give you order history, saved addresses, marketing consent and a fraud signal. The resolution is to defer rather than to omit, capturing the order against an email address and offering account creation on the confirmation page, when the customer has already got what they wanted. The engineering consequence is that the customer entity cannot require credentials, so identity is an email plus an optional login, and later account creation must be able to claim existing orders — which needs a verification step, or you have built an order-history leak.

Conversion is not one number. It is a product of stage rates, and the arithmetic tells you where to spend.

100,000 sessions reaching a product page

stage                     entered    completed   stage rate   cumulative
------------------------  ---------  ----------  -----------  ----------
product page               100,000      28,000       28.0%       28.0%
cart viewed                 28,000      12,600       45.0%       12.6%
checkout started            12,600      10,080       80.0%       10.1%
delivery step               10,080       7,560       75.0%        7.6%
payment step                 7,560       6,880       91.0%        6.9%
order placed                 6,880       6,742       98.0%        6.7%

Recover 5 points at the delivery step: 75% -> 80%
  orders become 6,742 x 80/75 = 7,191   -> +449 orders per 100,000 sessions

Recover 5 points at the payment step: 91% -> 96%
  orders become 6,742 x 96/91 = 7,112   -> +370 orders

Recover 5 points at the product page: 28% -> 33%
  orders become 6,742 x 33/28 = 7,946   -> +1,204 orders

The delivery step is the worst-performing stage and the product page is the most valuable one to fix, because a rate applied to a larger population moves more orders. That is the whole reason for doing the arithmetic rather than attacking the lowest percentage, and it is the mistake most funnel discussions make.

The stage rates also tell you what kind of problem each stage has. A seventy-five per cent delivery step is a pricing and expectation problem — the shipping cost or the delivery date was a surprise — and the fix is to show both earlier, on the product page, which costs a conversion opportunity but recovers more than it spends. A ninety-one per cent payment step is mechanical: declined cards, a failing wallet on one browser, a validation rule rejecting valid postcodes. Those are yours and they are cheap.

The last stage is the one engineers should be paranoid about. Two per cent of customers who pressed pay did not get an order, and that is not abandonment — that is your error rate, and every one of those is a support contact and possibly a charge without an order. It should be alerted on rather than reported monthly.

The instrumentation warning is that funnel stages must be recorded server-side at transition, not inferred from page views. Client analytics lose the sessions that failed, which is precisely the population you are trying to measure, so a funnel built from page-view events reports its own blind spot as success.

By having the client generate a key when it renders the checkout page, sending it with the submission, and enforcing uniqueness on it at the point of persistence so the second attempt fails to insert and returns the first order. The reason it matters more here than elsewhere is that the double-submit is guaranteed: a customer whose response times out will press pay again, and a mobile network will retry for them. A SELECT before INSERT is not a fix, since two concurrent submissions both find nothing. Store a hash of the material fields alongside the key so a reused key carrying a different basket is rejected loudly rather than silently returning somebody else's order, and scope the key to the customer.

The two-phase shape is the retailer's, and the gap between the phases is where the interesting failures live.

sequenceDiagram
    participant C as Customer
    participant O as Order service
    participant P as Payment provider
    participant W as Warehouse
    C->>O: Submits checkout
    O->>P: Authorise 84.00, hold placed
    P-->>O: Approved, auth valid 7 days
    O-->>C: Order confirmed
    O->>W: Allocate and pick
    W-->>O: Dispatched, 62.00 of goods
    O->>P: Capture 62.00 against the authorisation

Authorising at checkout and capturing at dispatch exists because in most jurisdictions you may not take payment for goods you have not shipped, and because the shipped value routinely differs from the ordered value once an item is out of stock or a partial shipment is made. So the authorisation is a permission with an amount ceiling and the capture is the claim.

The expiry is the failure mode. An authorisation is valid for a scheme-defined window, commonly around seven days, and a backordered item that ships on day twelve has nothing left to capture against. The capture is attempted, declines, and the order is dispatched unpaid — which is discovered in reconciliation, not by an alert, unless you built one. The mitigations are to re-authorise before the window closes, to capture at allocation rather than at dispatch for slow lines, or to refuse to accept a backorder longer than the authorisation lifetime.

Partial capture is the second detail. Capturing sixty-two against an authorisation for eighty-four must release the remaining hold, or the customer's available balance stays depressed for a week and you generate a complaint about being charged twice. Multiple captures against one authorisation are how split shipments are paid for, which is another reason the payment record cannot be one row with a status.

The over-capture case is worth knowing: you may not capture more than you authorised, so a price increase, an added item or an underestimated shipping cost requires a fresh authorisation rather than a larger capture — and that authorisation can decline on a card that worked yesterday.

You must never lose the fact of the money. The safe ordering is to persist the order intent durably before calling the payment provider, so a successful authorisation always has a record to attach to, and to make the provider call idempotent so a retry after an unknown outcome does not authorise twice. If persistence of the completed order still fails, the intent plus the authorisation reference is enough for a reconciliation process to complete or void it. The anti-pattern is calling the provider first and writing afterwards, which produces a customer charged for an order that does not exist — invisible to you and extremely visible to them. Reconciling the provider's settlement file against your orders daily is what detects the residue.

Because it depends on the whole basket, the sourcing decision, the destination and the clock, and all four can change during checkout. Cost depends on dimensional weight rather than weight alone, on which locations the items will actually ship from — a two-item basket sourced from two warehouses is two parcels — and on thresholds where free shipping kicks in, which interacts with promotions. The date depends on carrier cut-offs, working days, the destination's holidays and the warehouse's picking capacity today. The engineering consequence is that these are computed by a service with real inputs rather than a lookup table, and that any figure shown on the product page is a promise you will be held to at the delivery step.

Because a price is a value that depends on time, channel, currency, customer segment, quantity and seller, and every one of those is a real requirement rather than a hypothetical. The same item is a different price on the website and in the store, in Ireland and in Britain, at one unit and at a case, for a trade account and for the public, and it was a different price last Tuesday. So price is a set of effective-dated rows keyed by those dimensions, with a resolution order that picks the most specific applicable one. The consequence is that "what does this cost" is a query with parameters, and that the answer must be reconstructible for a past date, because a customer with a screenshot and a regulator with a pricing rule will both ask.

Three promotions that each look correct, applied in the order they happened to be loaded.

Basket: jacket 120.00, jeans 60.00, socks 10.00      subtotal 190.00

Active promotions
  P1  20% off outerwear
  P2  spend over 150, get 15 off the basket
  P3  buy 2 clothing items, cheapest free

Order A -- P1, P2, P3
  P1  jacket 120.00 -> 96.00                        subtotal 166.00
  P2  166.00 > 150, less 15.00                      subtotal 151.00
  P3  cheapest clothing item free, socks -10.00     total    141.00

Order B -- P3, P1, P2
  P3  socks free                                    subtotal 180.00
  P1  jacket -> 96.00                               subtotal 156.00
  P2  156.00 > 150, less 15.00                      total    141.00

Order C -- P2, P1, P3
  P2  190.00 > 150, less 15.00                      subtotal 175.00
  P1  20% off jacket, on 120.00 or on 110.53?       ambiguous
      naive: on the line's original price -> 151.00
  P3  socks already discounted by P2's spread       double count
                                                     total   141.79

Three orderings, three totals, and the customer sees whichever the code produced. Order C is the dangerous one, because a basket-level discount spread across lines changes what a subsequent line-level percentage applies to, and nobody wrote down which base is correct.

The resolution is an explicit, configured order rather than an emergent one. Line-level adjustments resolve before basket-level ones, quantity-based offers resolve before percentages, and each promotion declares the base it applies to — original line price or current line price — so the composition is defined rather than discovered. Publishing that order and testing against it is the whole fix.

Exclusivity rules are the second half. Promotions need a stacking policy — exclusive, stackable, or stackable within a group — plus a priority, because the business intent behind P2 and P3 was almost certainly not that they combine. The default should be that promotions do not stack, and combination is opt-in, since the failure direction of the other default is margin leaking silently.

The output that makes this operable is a per-line audit of adjustments: which promotion, which rule version, which base, which amount, in which order. Without it a disputed total cannot be explained, a margin investigation has no data, and a bug is unreproducible because the promotion has since been edited. The version matters as much as the amount.

The last consideration is where this runs. The total must be computed once, in one service, for the cart, the order, the invoice and the refund — because a second implementation in the refund path is how a customer gets refunded more than they paid.

Because eligibility is evaluated against the basket as a whole, and the set of possible applications grows with the number of items and offers. A "buy three for two" offer across seven eligible items has to choose which items form the groups, and the choice changes the discount; two overlapping offers may each be better alone than together. Doing this exhaustively is intractable for a large basket, so real engines use greedy heuristics with a defined tie-break — best for the customer, typically — and accept that the result is not provably optimal. The consequences are that the total is not a pure function of the basket unless the heuristic is deterministic, that recomputation cost grows with basket size and sits on the checkout path, and that "why did I not get the better deal" is a support question with a real answer.

Single-use codes shared publicly, staff codes leaking, new-customer codes redeemed by the same person under fifty email addresses, and codes stacked in combinations the campaign never intended. The engineering answers are mostly about identity and limits rather than about secrecy: per-code and per-customer redemption limits enforced atomically at order placement, unique codes issued per recipient rather than one shared string, expiry and minimum-spend conditions evaluated server-side, and duplicate-account detection on the signals you have — payment instrument, address, device. Two details matter. The redemption counter must be decremented in the same transaction as the order, or a viral code is redeemed ten thousand times against a limit of one thousand. And redemption must be reversed when an order is cancelled, or your best customers lose their entitlement to a warehouse error.

Two identical baskets, two destinations, and almost nothing shared between the calculations.

Basket: hardback book 20.00 net, children's shoes 30.00 net, laptop 500.00 net

Destination A -- United Kingdom, VAT, prices displayed inclusive
  books                zero rated          0%      0.00
  children's shoes     zero rated          0%      0.00
  laptop               standard           20%    100.00
  delivery 4.99        follows the goods, apportioned
                       apportioned to standard-rated share  0.91
                       500/550 of 4.99 net, at 20%
  tax total                                       100.91
  customer pays        550.00 + 4.99 + 100.91, shown as 655.90 inclusive

Destination B -- Texas, United States, sales tax, prices displayed exclusive
  state rate 6.25% + local 2.00% = 8.25%
  books                taxable            8.25%    1.65
  shoes                taxable            8.25%    2.48
  laptop               taxable            8.25%   41.25
  delivery             taxable in TX               0.41
  tax total                                       45.79
  customer pays        550.00 + 4.99 + 45.79    = 600.78

The rates are the least interesting difference. What breaks systems is that the United Kingdom applies a product-level rate with genuine zero-rating, taxes delivery in proportion to the goods it carries, and requires prices to be displayed inclusive of tax — while Texas applies one destination-derived rate across the basket, treats delivery as taxable in its own right, and displays exclusive. A single implementation with a tax_rate field on the product cannot express either properly.

Nexus is the concept behind the second column and the one candidates have not heard of. Whether you must collect at all depends on your economic connection to the destination state, so the same order is taxable or not depending on your sales volume there over the past year — a business fact, changing over time, that must be data rather than code.

Rounding and ordering are where real money is lost. Tax computed per line and rounded, then summed, differs from tax computed on the total, and both are defensible in different jurisdictions, so the rule must be configurable and recorded. Inclusive pricing makes it worse, because the net is derived from the gross by division and the derived net must still reconcile across lines to the invoice total.

The practical conclusion is that tax is bought rather than built for anything crossing borders, and the engineering work is the integration: a call on the checkout path that must be fast, must be cached, must degrade to an estimate rather than blocking the order, and must record the exact rate and rule version used, since the invoice is a legal document and the refund must reverse the same tax you charged.

Because the number the customer paid is the outcome of a resolution — base price, segment override, promotion stack, tax, delivery — and every part of it will be questioned. A customer disputes the total, finance investigates a margin drop, a regulator asks about a reference price used in a sale claim, and a refund must reverse exactly what was charged including the tax treatment. If the order stores only a total, none of those has an answer, and the promotions have since been edited so the calculation cannot be replayed. So the order carries the full adjustment trail with rule versions and the tax rates applied, snapshotted at placement rather than referenced, because reference data changes and the invoice does not.

Treat a price change as a published, effective-dated event rather than an update. The change is authored, reviewed, given an effective time, and propagated to the read models — page cache, search index, store terminals — with a monitored lag, so the price a customer sees and the price the till charges are the same. Two controls save you. A guard rail rejecting changes outside a sane band catches the misplaced decimal that puts a television at nine pounds, because the story of a pricing error is always a manual file. And a defined rule for in-flight carts and already-placed orders: orders are honoured at the price recorded on them, carts revalidate and tell the customer, and neither silently adopts the new number.

The states are unremarkable. The ownership of the transitions is what candidates have not thought about.

flowchart TD
    A[Placed<br/>customer submits checkout] --> B[Authorised<br/>payment provider approves]
    B --> C[Allocated<br/>sourcing engine picks locations]
    C --> D[Picked and packed<br/>warehouse operative]
    D --> E[Dispatched<br/>carrier scan, payment captured]
    E --> F[Delivered<br/>carrier event]
    B --> G[Cancelled<br/>customer or fraud review or stock failure]
    F --> H[Returned<br/>customer initiates, warehouse confirms]

Reading the triggers rather than the boxes is the point. Only two transitions are caused by your own code — allocation and the capture at dispatch. The rest are caused by a customer, an external payment provider, a warehouse operative or a carrier, which means most of your state changes arrive as unreliable, out-of-order, possibly duplicated events from systems you do not control.

That is why the machine has to be explicit and persisted rather than implied by a set of boolean columns. Transitions are validated against the current state, so a delivery event for a cancelled order is rejected and logged rather than applied, and an event arriving twice is idempotent. A status column mutated by whichever integration spoke last cannot do either, and the symptom is an order that is simultaneously cancelled and delivered.

The cancellation edge from Authorised is the one with commercial weight. There is a point of no return — the moment the pick begins — after which a cancellation becomes a recall or a return rather than a cancellation, and the customer-facing promise has to match it. Systems that offer a cancel button after the pick has started generate the worst class of support contact, because the answer is that the thing they cancelled is going to arrive.

Two states are missing from the diagram deliberately, and naming them is a good signal. Held covers fraud review, payment retry and address verification, where the order is neither progressing nor dead and needs a service level. And partially-anything — partially allocated, partially dispatched, partially returned — cannot be a state at all, which is the subject of the split-shipment item and the reason the header cannot own the status.

The order as a lifecycle rather than a record: capture from every channel, validation, fraud and payment orchestration, sourcing and allocation across locations, releasing work to warehouses and stores, tracking fulfilment, handling cancellation, return and refund, and being the single answer to "what is happening with my order" for the customer, the call centre and the shop floor. It exists because those concerns were otherwise scattered across the webshop, the ERP and the warehouse system, none of which could see a customer's order across channels. Its defining characteristic is that it integrates with systems it does not control on timescales measured in hours, which is why it is event-driven, idempotent and full of reconciliation rather than a CRUD service over an orders table.

One order, two locations, two parcels, and the schema that turns that into a support ticket.

Order 55120
  line 1  jacket   qty 1   from DC-North
  line 2  jeans    qty 2   from DC-North
  line 3  socks    qty 3   from Store-Leeds

Physical reality
  shipment A  DC-North     jacket 1, jeans 2      dispatched Tue, tracking X1
  shipment B  Store-Leeds  socks 2                dispatched Thu, tracking Y7
                           socks 1                short, cancelled

The model that cannot express it
  order(id, status, tracking_number, dispatched_at, total)
  order_line(order_id, sku, qty, price)

  status              -- there is no single status. It is dispatched and
                         partly cancelled and awaiting dispatch.
  tracking_number     -- there are two.
  dispatched_at       -- there are two dates.
  qty on the line     -- ordered 3, shipped 2, cancelled 1. One column.

The model that can
  order(id, placed_at, customer_id, currency)
  order_line(id, order_id, sku, qty_ordered, unit_price_minor)
  allocation(id, order_line_id, location_id, qty, state)
  shipment(id, order_id, carrier, tracking, dispatched_at, state)
  shipment_line(shipment_id, allocation_id, qty)
  refund(id, order_id, reason, amount_minor, created_at)

Status belongs to the shipment and quantity belongs to the allocation, and the order header has neither. Once you accept that, the derived order status is a function over its shipments — a computed roll-up for display — and every awkward case becomes representable rather than requiring a workaround.

The workaround is always the same and always worse: a second order row for the second parcel. It splits the customer's order in their history, breaks the total they were charged against two records, double-counts in reporting, and makes a refund that spans both parcels impossible to attribute. It is the single most common structural mistake in retail systems.

The shipment-line-to-allocation link rather than to the order line is what lets you ship the same SKU from two places, which is routine when one location is short. The quantities then reconcile in one direction: quantity ordered equals quantity shipped plus quantity cancelled plus quantity outstanding, per line, checkable as an invariant and worth asserting continuously.

Payment follows the same shape. Two shipments mean two captures against one authorisation, the short-shipped sock is never captured rather than refunded, and a return generates a refund row linked to the shipment line it came back from. A single total on the order cannot express what was actually collected, which is why the finance reconciliation and the split-shipment design are the same conversation.

Accepting an order for stock you do not have, against expected inbound supply. It is a commercial choice that converts a lost sale into a promise, and the promise is the liability. You commit to a date derived from a supplier's lead time you do not control, to holding the customer's payment authorisation across a window that may outlive it, to communicating slippage before the customer chases, and to allocating the arriving stock to the waiting orders in a fair order rather than to whoever browses first. The last one is a real design decision: first-come allocation against inbound requires a queue per SKU with a position, and without it the newest customer takes the unit somebody has been waiting three weeks for.

That it races the warehouse. A cancellation is trivial before allocation and impossible after the parcel leaves, and in between it is a request that may or may not win — the pick may already be in a tote on a conveyor. So cancellation is modelled as an attempt with an outcome rather than an action with a result, and the point of no return is a real timestamp per fulfilment location rather than a policy sentence. The knock-on work is the tail: releasing allocations, restoring inventory, voiding or refunding the payment depending on whether capture already happened, reversing coupon redemptions and loyalty accrual, and updating anything downstream that already counted the order as revenue.

A cancellation stops something happening, so the money is either never captured or voided before settlement, and there is no return leg. A refund moves money back after the fact, against a captured payment, and must reverse the exact composition of what was charged — the discounted line price, its share of a basket-level promotion, the tax at the rate applied then, and whichever part of the delivery cost is refundable under policy. That is why the refund cannot be a percentage of the total: refunding one of three items in a three-for-two offer may legitimately mean the customer owes money on the two they kept. The engineering requirement is that the refund is calculated from the order's stored adjustment trail rather than recomputed by today's promotion engine.

Not what customers assume. A returned item is not sellable on arrival: it is received into a quarantine or inspection state, graded, and only then either returned to sellable stock, downgraded to an outlet grade, sent to a repair or refurbishment path, or written off. That means the inventory effect of a return is delayed and probabilistic, so a system that increments available stock when the refund is issued will oversell the returns it has not inspected. Two further consequences: the refund and the physical receipt are separate events with a gap you must decide policy for, since refunding on receipt is better for the customer and worse for fraud, and return rates by SKU are a data product in their own right, because a line returning at forty per cent is a catalogue defect.

Sourcing decides which locations fulfil which lines of an order, and it is a trade-off rather than a lookup. Nearest location minimises delivery cost and time; fewest locations minimises parcel count and packing labour; a markdown-avoidance objective prefers to ship from stores holding stock that will otherwise be discounted; capacity constraints exclude a warehouse already at its daily pick limit. These objectives conflict, so the engine carries weights that the business tunes, and the same order sourced under two weightings produces different costs and different customer experiences. The engineering point is that it must be fast enough to run at order placement, deterministic enough to explain after the fact, and re-runnable when a location turns out to be short.

Zero results is the clearest failure in retail search, and almost every instance has a specific, cheap cause.

Query: "wireless earbuds noise cancelling under 100"     results 0

Why, in order of likelihood
  1  "earbuds" is not in the catalogue. Titles say "in-ear headphones".
  2  "noise cancelling" is an attribute value, not free text in the title.
  3  "under 100" is a price filter expressed in prose. Tokenised and
     matched as terms, it excludes everything.
  4  The AND default requires every token to match one document.

Fixes, cheapest first
  synonyms          earbuds -> in-ear headphones, earphones
  attribute mapping "noise cancelling" -> anc = true, as a filter
  query parsing     "under 100" -> price < 100
  relaxation        drop the least selective token and retry, then label
                    the result "showing results for wireless earbuds"
  fallback          never return an empty page: show the category,
                    the closest matches, and a way to broaden

Assumed impact of a zero-result page, for this scenario
  sessions ending in a zero-result page       4.2%
  of those, sessions that then convert        1.1%   versus 6.7% overall

The last block is why this is a business metric rather than a search-quality one. A zero-result page is very close to a lost session, so the zero-result rate is tracked, the top zero-result queries are reviewed weekly by a human, and each one is fixable by a synonym or a mapping in minutes. It is the highest-return manual work in retail search.

Synonyms carry most of the load and cannot be generated from the catalogue alone, because the vocabulary gap is between how customers speak and how suppliers write titles. The source is the query log: queries with no results, queries followed by a reformulation, and queries with results nobody clicked. That feedback loop is the actual system, not the analyser configuration.

Relaxation needs to be visible. Silently dropping a token gives the customer results that do not match what they asked and looks like a broken search, whereas saying which term was ignored preserves trust and lets them re-add it. The same applies to a filter that eliminates everything: telling the customer that the price cap excluded all matches is more useful than an empty grid.

The structural fix underneath is attribute coverage. Points two and three exist because the information was in the catalogue as prose rather than as a typed attribute, so search could not filter on it — which is the same root cause as the faceting problem, and the reason enrichment and search relevance are funded together.

Textual match is the smallest part. Ranking combines it with behavioural signals — click-through and conversion rate for this query, historically — with business signals such as margin, stock cover and whether the item is a strategic line, and with quality signals such as review score and return rate. That mix is what makes retail search different from document search: the best answer is the one most likely to be bought and shipped successfully, not the one most textually similar. The engineering consequence is a feedback loop from orders back into ranking, with the usual hazard that popularity is self-reinforcing, so new products need deliberate exploration or they never accumulate the signals that would rank them.

Badly, unless you decide the interaction explicitly. Facet counts are computed from the index, availability changes constantly, and the two are never in step, so a customer filters to "in stock, size 12, navy" and the count says four while one has just sold. Worse, a facet value with no available products is a dead end that returns zero results from a control you offered. The options are to include availability in the index and accept the reindex volume, to filter at query time against a fast stock service and accept the latency, or to show counts that are approximate and label them as such. All three are defensible; not choosing means the behaviour differs between the count, the grid and the product page, which is the version customers report as a bug.

Merchandising is the deliberate human override of algorithmic ordering: pinning a product to the top of a category, boosting a new season, burying a line being discontinued, or arranging a landing page for a campaign. It fights relevance because it is optimising a different objective — this quarter's margin, a supplier agreement, clearing stock — against the algorithm's proxy for what the customer wants. The engineering job is to make the override expressible, bounded and measurable rather than to resist it: rules with a scope and an expiry, applied as a documented layer after ranking, and reported on so the cost of a pin in conversion terms is visible. Unbounded, undated merchandising rules are how a search results page slowly becomes worse than it was two years ago.

Also-bought, from co-occurrence in orders, which is strong on a product page and drives basket size. Also-viewed, from session co-occurrence, which is better for alternatives when the customer is still comparing. Similar-item, from attributes or embeddings, which is what you show when the item is out of stock or discontinued. Trending, from recent velocity, which is the honest answer on a home page for an anonymous visitor. And personalised, from this customer's history, which needs the most data and pays back the least on a first visit. The placement matters as much as the model: also-bought on a category page is noise, and similar-item on a confirmation page recommends a substitute for something they just bought.

That the customers and products with no history are the ones you most need to get right. A new visitor has no behaviour, so personalisation degrades to popularity, which is why session-based signals — what they have looked at in the last four minutes — carry disproportionate weight in practice. A new product has no co-occurrence data, so it cannot be recommended, cannot accumulate the clicks that would rank it, and quietly stays invisible through the launch window it needed. The mitigations are content-based similarity to bridge the gap for new products, deliberate exploration budget in ranking, and an explicit rule that new lines are merchandised rather than left to the model. Cold start is therefore an inventory and launch problem as much as a modelling one.

Because the queries the customer needs — full-text, fuzzy, faceted, ranked by a learned score — are ones a normalised relational catalogue cannot serve at browse latency. So the catalogue is projected into a denormalised document per product, and the projection is a second source of data that will drift. What follows is operational: you need a lag metric between the source change and the index, a full-reindex path that can run without downtime because the mapping will change, and reconciliation that detects the product present in one and absent in the other. The failure mode is not an outage. It is a discontinued product still buyable, or a price in the results grid that differs from the product page, and both are found by customers rather than by monitoring.

You stop owning the inventory and start owning the trust. The catalogue is now contributed by third parties, so you inherit duplicate and mis-categorised listings and need matching and moderation. Availability is asserted by sellers rather than measured by you, so it is less reliable and you cannot fix it. Money becomes a flow you are holding on somebody else's behalf, which brings payout scheduling, commission accounting and possibly a regulatory obligation. And quality is enforced by policy rather than by operations, so seller performance metrics, suspension and appeal are product features. The architectural summary is that a retailer's hard problem is inventory consistency and a marketplace's is multi-tenancy plus adverse incentives, and the engineering follows.

The single offer that wins the "add to cart" button when several sellers list the same product. It exists because customers will not compare fourteen identical offers, and it is contentious because winning it is most of the sales, so the selection rule is effectively the marketplace's pricing regulator. The inputs are price including delivery, fulfilment speed and reliability, seller performance history and stock depth, and the weighting is a commercial decision with competition-law consequences. Engineering-wise it needs to be computed per product per customer context — since delivery speed depends on the customer's location — cached hard because it sits on the product page, recomputed on price and stock changes, and explainable, because a seller who lost it will ask why and may be entitled to know.

The seller's mental model is the order total. The payout is a different number, and every line of the difference generates a support contact.

Seller S-4410, payout period 1-15 July 2026

gross merchandise value, 214 orders                        18,420.00
returns and cancellations in period                        -1,905.00
                                                          ----------
net sales                                                  16,515.00

commission 12% of net sales                                -1,981.80
per-order fulfilment fee, 214 x 0.35                          -74.90
payment processing 1.4% + 0.20 per order                     -274.01
advertising spend billed in period                           -412.00
chargebacks upheld, 3                                        -186.40
                                                          ----------
earnings                                                   13,585.89

rolling reserve, 5% of net sales, held 90 days                -825.75
reserve released from April period                           +610.20
                                                          ----------
payable this period                                        13,370.34

  VAT and tax withholding applied per jurisdiction on top of the above.

The reserve is the line sellers dispute and the line that exists for a good reason. Returns, chargebacks and disputes arrive weeks after the sale, and once a seller has been paid there is often no practical way to recover money from them, so the marketplace holds a percentage against future liabilities. It is a credit decision expressed as a configuration, higher for a new seller and lower for one with a long clean history.

The rolling nature is what makes the arithmetic non-obvious. This period holds back five per cent and releases what was held ninety days ago, so a seller with flat sales sees a steady payout while a growing seller feels the reserve as a persistent working-capital cost. Explaining that in the seller dashboard prevents most of the tickets.

Returns crossing period boundaries are the second complication. A return in July against a June order reduces July's net sales, which can drive a small seller's payable amount negative — and a negative payout is a debt you must either carry forward, net against the reserve, or invoice. All three need to be modelled, and the one nobody builds is the negative case.

The engineering requirements follow from the fact that this is a financial statement. Every line is derived from immutable events with references the seller can drill into, the calculation is reproducible for a closed period even after fee schedules change, fee rates are effective-dated rather than mutated, and the sum of all payouts plus all fees must reconcile to the money actually received. A payout engine that recomputes from today's rates cannot answer a query about April.

Identity and business verification, tax registration, bank account validation, and — because you are handling money on their behalf — screening obligations that look a lot like a financial institution's. Then the commercial and operational setup: category permissions, which often gate restricted goods, fee schedule, fulfilment method, returns policy, and catalogue ingestion in whatever format the seller can produce. It is a long-running, resumable workflow with third-party checks that fail intermittently and manual review as a designed path, not a signup form. The engineering trap is treating a seller as a user account: the seller is a legal entity with a verification state, a performance record, a financial ledger and a lifecycle including suspension, which is a different entity from whoever logs in.

The customer buys online and collects from a store, which sounds like a fulfilment option and is actually the hardest inventory problem in the business. It requires store-level availability accurate enough to promise against, exposed in real time, when store inventory is the least accurate you have. It requires a reservation that survives until collection, so the unit is removed from shop-floor sale — which staff must physically pick and hold, generating a task and a location. And it requires a firm promise time, so the customer arriving in an hour finds it ready. The engineering consequence is that store systems become part of the online transaction path, with the availability, latency and accuracy expectations of an online system rather than a till.

It forces both to become channel-agnostic, which is more invasive than it sounds. An order placed in a store, returned by post and exchanged online is one customer's history, so the order cannot belong to the webshop and the customer cannot be a web account. That means a customer identity resolvable from an email, a loyalty card, a phone number and a card token, with merging as a first-class operation, and an order model whose channel is an attribute rather than the system it lives in. The payoff is the thing customers actually expect — buy anywhere, return anywhere, one history — and the cost is that promotions, tax, returns policy and stock all now have per-channel variation that must be data.

A drop is not more traffic. It is the same day's traffic arriving in ninety seconds, and the numbers are worth doing before the meeting.

Product drop, 5,000 units, one SKU, advertised for 20:00

Expected arrivals
  registered interest                          180,000
  arriving within the first 60 seconds, 70%    126,000
  peak requests per second, front page          ~4,000/s
  add-to-cart attempts in first 30s              90,000  -> 3,000/s
  checkout submissions                           40,000  -> 1,300/s

Against a normal day
  normal peak page requests                        250/s   -> 16x
  normal peak checkouts                             12/s   -> 108x

The contended resource
  one stock row, 3,000 claims/s, single-row serialisation
  assumed capacity of a conditional decrement        ~800/s
  therefore                                     4x oversubscribed

Consequences
  5,000 units sell out in under 10 seconds
  85,000 add-to-cart attempts must fail cleanly, not time out
  the 121,000 who get nothing still load every page they visit

The asymmetry between the sixteen-times figure and the hundred-and-eight-times figure is the whole design brief. Static and cacheable traffic scales by adding capacity and a CDN, and no amount of capacity fixes a single row taking three thousand writes a second. Those are two different problems and candidates routinely solve only the first.

The contended row is answered by removing the contention rather than by scaling it. Admission control in front of checkout — a queue that issues a bounded number of purchase tokens, roughly the unit count plus a margin — converts a race into an ordered stream, and everyone else is told immediately that they were unsuccessful. That is what a virtual waiting room is, and it also makes the experience honest.

The failure mode to design for is the ninety-five per cent who get nothing. If their add-to-cart attempts time out rather than failing fast, they retry, and the retry storm is larger than the original event. So the sold-out response must be cheap, cacheable, and served without touching the database at all once the counter is exhausted.

Two further items nobody costs. Everything downstream of the sale receives five thousand orders in ten seconds — payment authorisation, fraud screening, confirmation email, warehouse release — and those have their own rate limits, including third parties who may throttle you. And the inventory number must not be cached on the product page during the window, because a cached "in stock" is what generates the retries.

Because the peak is known in advance, is many times the average, and cannot be missed, which turns several ordinary decisions into hard ones. It means the system must be tested at peak load rather than sized by extrapolation, so load testing against production-like data becomes a scheduled programme. It means a change freeze, which pushes the year's risky migrations into a narrower window and shapes the release calendar. It means every third-party dependency on the critical path needs a negotiated limit and a degradation plan, because their capacity is not yours. And it means graceful degradation is decided in advance — which features turn off, in what order — since the alternative is discovering the priority order during the incident.

No, and asserting that it should be is the answer that ends the discussion. The recorded count already disagrees with the shelf because of shrinkage, damage and miscounts, so the strongest consistency your database offers still gives you a number that is wrong. What must be strongly consistent is the claim: the decrement or reservation that decides who gets the last unit is a single atomic operation, and everything else — the count on the product page, the facet count, the network aggregate — is a cached approximation that should be labelled as one. Distinguishing the consistency of the claim from the accuracy of the count is what a strong answer does, and it is also the framing that makes oversell tolerance a sensible thing to configure rather than an admission of failure.

No, and modelling it that way causes specific damage. A cart is unvalidated intent with no promise attached: its prices are display values, its availability is stale, it has no payment, and most of them are abandoned. An order is a commitment with a captured price, a tax treatment, an allocation and a legal obligation on both sides. Making the cart an order in a draft state means your orders table is dominated by rows that will never be orders, your order-placed metrics need filtering, your promotion and tax calculations run on every cart update, and — worst — the transition from intent to commitment stops being an explicit moment where you revalidate everything. That moment is where the price, the stock, the coupon and the address must all be checked again.

Neither, and both, which is the point of the question. Promotion evaluation depends on the whole basket, so it cannot live in a per-item pricing service; but it must produce the same total for the cart, the order, the invoice and the refund, so it cannot live in the cart service either. The workable answer is a single pricing-and-promotions service that takes a basket plus a context and returns a priced basket with a full adjustment trail, called by the cart for display and by order placement authoritatively. What you are guarding against is a second implementation, which is how a refund exceeds the payment: the cart computed one total, the refund path recomputed another, and nobody noticed until the reconciliation.

No, because the incentives differ and that changes the engineering. A supplier is under contract and audited; a seller is a counterparty who may misdescribe a product, list a counterfeit, ship late, manipulate reviews or arbitrage your delivery promise, and your defence is measurement and policy rather than a purchase order. So you build seller performance metrics with consequences, listing moderation, counterfeit and review-abuse detection, dispute resolution between two parties you both serve, and money handling on someone else's behalf with payouts, reserves and tax. None of that exists in a first-party retail stack. The architectural marker is that a marketplace is multi-tenant with adversarial tenants, which is a different threat model, not a bigger catalogue.

Whether you know that this is two questions wearing one coat, and whether you reach for a business decision as well as a lock. The first is the race — two checkouts reading the same availability and both proceeding — and it has a mechanical answer: make the claim atomic with a conditional decrement or a reservation row enforced by a constraint, never a read then a write, with an expiry and an idempotent sweeper releasing abandoned holds.

The second is the drift, and it is the one that separates candidates. The count is wrong before any concurrency is involved, so no locking strategy achieves zero oversells, and the real answer is a chosen oversell rate managed with safety stock tuned against measured inventory accuracy, plus a defined handling path when it happens — substitute, partial ship, backorder with a date, or cancel and compensate — and a metric on how often each occurs.

A weak answer offers a distributed lock and stops, or proposes a transaction spanning the payment provider. The signal is whether the candidate asks what the business would rather lose: a sale it could have made, or a promise it cannot keep.