Skip to content

0030 — Size-wise stock (variants) for sized products (Nov 2026)

Status: accepted for implementation (2026-11-01). Migration 121. Opt-in per product; every existing product is unchanged.

Stock architecture before this change

One number per product: shop_items.stock_quantity. It is moved only by deltas — a guarded decrement at order commit (commitOrderCore, after priceLines locks the row FOR UPDATE), a restock on cancel/refund (restockOrderItems), a purchase receipt (IncreaseStockAndCostTx) and an explicit admin adjustment (AdjustStock). A product edit deliberately never writes stock. The gateway-paid path re-runs the same priceLines/commitOrderCore from CommitOrderFromIntent, and a paid order that cannot be fulfilled becomes a Shopping Credit via payment_stock_recoveries (ADR 0028). This change reuses all of it; there is no second inventory system.

Decisions

  1. shop_item_variants: one row per (product, size) — S, M, L, XL, XXL — with its own stock_quantity >= 0, an is_enabled flag and a stable id. UNIQUE (shop_item_id, size).
  2. Opt-in. shop_items.has_size_variants defaults to FALSE. Electronics and anything without sizes keep one stock number and behave exactly as before. Nothing distributes existing stock across sizes: turning sizes on requires the admin to enter each size's quantity.
  3. The parent total cannot drift. For a size-enabled product shop_items.stock_quantity is derived by a trigger (sum of enabled sizes) on every change to either table. The parent-level writers that would corrupt it (AdjustStock, purchase-receipt stock increase) refuse a size-enabled product with a clear error; stock is changed per size.
  4. Authoritative stock is the selected size's. priceLines resolves the variant (by id, or by size name) under FOR UPDATE after the parent row lock (fixed lock order: parent, then variant), and checks quantity against that size. commitOrderCore decrements that size with a guarded UPDATE … AND stock_quantity >= $n; it can never touch another size.
  5. Restock returns to the same size, via order_items.variant_id (history is never rewritten; old lines have variant_id NULL).
  6. Error codes are unchanged (OUT_OF_STOCK, INSUFFICIENT_STOCK, PRODUCT_UNAVAILABLE) plus SIZE_REQUIRED. A missing, disabled or unknown size after payment capture is classified OUT_OF_STOCK for that size, so the existing Shopping Credit recovery credits it once; before payment it is SIZE_REQUIRED, whose message tells an old app to update.
  7. Older apps cannot bypass the size. They send no variant_id; the quote/intent for a size-enabled product is refused with SIZE_REQUIRED ("update AIM Store from Google Play"). The legacy single-item PlaceOrder excludes size products outright.
  8. Admin edits are per size, stale-safe and audited. PUT /admin/products/:id/size-variants changes only the sizes sent, each with expected_quantity so an edit made against old numbers is refused (409), and writes an audit row. Turning sizes off, or disabling a size, is refused while an open checkout holds that size (VARIANTS_IN_USE).
  9. Low-stock display (app): stock > 5 "In Stock"; 1–5 "Only N left"; 0 "Out of Stock" — from the selected size's live stock; cards show the sum of enabled sizes.

Known limits

  • A purchase bill line for a size-enabled product cannot raise stock through the purchase-receipt path; add the received units per size in the Admin Portal.
  • Enabling sizes replaces the product's single stock number by the sum of the quantities the admin enters (the old number is recorded in the audit row).
  • Contention on a hot product is serialised on its parent row (as it already was).

Rollback

rollback_121.sql refuses while any product is size-enabled or any order line references a size; disable sizes first and export the order lines.