KAHIBARO
Discord Login Register ☰
☰

31.7. Inventory

Table of Contents

Understanding Inventory in an E‑Commerce Backend

Inventory is how your e‑commerce backend knows what is in stock, what can be sold, and when to stop accepting orders. In this chapter you will connect inventory to concepts you have already built in this project, such as products, orders, and background jobs, and you will focus on the practical details that are unique to inventory management.

Inventory is not only “a number on a product.” It affects almost every part of an e‑commerce system: product listing, cart behavior, payments, order processing, and even background synchronization with external systems.

Core Inventory Concepts

At a minimum, a simple e‑commerce backend must answer these questions:

It is useful to distinguish between a few related quantities:

TermMeaningExample
On‑hand quantityPhysically in your warehouseYou physically have 100 T‑shirts
Reserved quantityItems promised to existing, not yet shipped, orders20 T‑shirts reserved for pending orders
Available quantityCan still be sold: on‑hand minus reserved80 T‑shirts available to new customers
Safety stockMinimum you want to keep as buffer, you do not sell below thisKeep 10 T‑shirts in reserve, not for sale

A common simple model is:

$$\text{available} = \text{on\_hand} - \text{reserved}$$

If you do not explicitly track reserved, then you often use just one field like stock or quantity, and decrease it as soon as an order is placed.

Important rule: Never let available stock become negative. Your inventory logic and database constraints must prevent $available < 0$.

In many beginner projects, you start with a minimal model:

Later, you can extend this with reservations, warehouses, and more detailed states.

Data Modeling for Inventory

Inventory information is related to products, but it often deserves its own table or at least a clear structure, especially when you deal with variants or multiple warehouses.

Simple Single‑Warehouse Model

For the first version of the e‑commerce backend, you can model inventory with a simple relationship from product to stock.

Example SQL schema:

sql 
CREATE TABLE products (
    id           SERIAL PRIMARY KEY,
    name         TEXT NOT NULL,
    sku          TEXT UNIQUE NOT NULL,
    price_cents  INTEGER NOT NULL,
    -- other product fields ...
    stock        INTEGER NOT NULL DEFAULT 0,  -- current available stock
    is_active    BOOLEAN NOT NULL DEFAULT TRUE
);

In this model:

You might also want to protect stock with a constraint:

sql 
ALTER TABLE products
    ADD CONSTRAINT stock_non_negative CHECK (stock >= 0);

Rule: Use a database check constraint to ensure stock >= 0. Application bugs should not be able to store invalid inventory values.

Separating Inventory Into Its Own Table

Once you add complexity, such as variants or warehouses, you often create a separate table:

sql 
CREATE TABLE product_inventory (
    id           SERIAL PRIMARY KEY,
    product_id   INTEGER NOT NULL REFERENCES products(id),
    warehouse_id INTEGER NOT NULL REFERENCES warehouses(id),
    on_hand      INTEGER NOT NULL DEFAULT 0,
    reserved     INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT non_negative_on_hand CHECK (on_hand >= 0),
    CONSTRAINT non_negative_reserved CHECK (reserved >= 0),
    CONSTRAINT unique_product_warehouse UNIQUE (product_id, warehouse_id)
);

Then, you can compute total available quantity for a product by summing over warehouses:

sql 
SELECT
    SUM(on_hand - reserved) AS available
FROM product_inventory
WHERE product_id = $1;

For this course project you can stay with a single warehouse, but thinking in terms of on_hand and reserved makes it simple to extend later.

Inventory for Product Variants

Many e‑commerce sites sell variants, for example T‑shirts with different sizes and colors. In that case, you should not store stock on the base Product but instead on the variant entity, such as ProductVariant or ProductOption.

Example:

sql 
CREATE TABLE product_variants (
    id          SERIAL PRIMARY KEY,
    product_id  INTEGER NOT NULL REFERENCES products(id),
    sku         TEXT UNIQUE NOT NULL,
    attributes  JSONB NOT NULL, -- e.g. {"size": "M", "color": "blue"}
    stock       INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT stock_non_negative CHECK (stock >= 0)
);

Then cart items and order items refer to the product_variant_id instead of the product_id. Only the variant has stock.

Inventory APIs and Responses

Your inventory logic appears in several API endpoints, not only in dedicated inventory endpoints.

Returning Stock Information with Products

When listing products, you can include minimal inventory info so the frontend can decide whether to show a “Buy” or “Out of stock” button.

Example product response:

json 
{
  "id": 123,
  "name": "Blue T-Shirt",
  "price": 19.99,
  "stock": 8,
  "is_in_stock": true
}

If you do not want to expose exact stock numbers, provide only a boolean or a coarse label.

Example:

json 
{
  "id": 123,
  "name": "Blue T-Shirt",
  "price": 19.99,
  "availability": "in_stock"  // "out_of_stock", "low_stock"
}

You can derive availability from stock using simple rules:

Inventory Endpoints for Admins

For managing inventory, an admin interface or API is necessary. Typical endpoints:

Example admin request to adjust inventory:

http 
POST /admin/products/123/inventory/adjust
Content-Type: application/json
{
  "delta": -5,
  "reason": "Damaged items removed"
}

Your backend:

Inventory Adjustment History

Keeping a log of changes is useful for debugging inventory bugs or reconciling with physical counts.

Example table:

sql 
CREATE TABLE inventory_movements (
    id           SERIAL PRIMARY KEY,
    product_id   INTEGER NOT NULL REFERENCES products(id),
    quantity     INTEGER NOT NULL,
    reason       TEXT NOT NULL,
    created_at   TIMESTAMP NOT NULL DEFAULT NOW()
);

When you adjust stock by delta, you also insert a row:

sql 
INSERT INTO inventory_movements (product_id, quantity, reason)
VALUES ($1, $2, $3);

Then, you change the product stock:

sql 
UPDATE products
SET stock = stock + $2
WHERE id = $1;

Both should happen in the same database transaction so they are always consistent.

Integrating Inventory with Cart and Orders

Inventory is closely tied to the shopping cart and the order lifecycle. In this course you already work on Cart and Orders in other chapters. Here we focus specifically on where inventory checks and updates should occur.

You need three main checks:

  1. When adding items to cart.
  2. When updating cart quantities.
  3. When converting a cart into an order and confirming payment.

Checking Stock When Adding to Cart

When a user adds something to the cart, perform a stock check:

  1. Load current stock (or available) for the product or variant.
  2. Compute the total desired quantity in the cart for this product.
  3. Compare it with available stock.
  4. If too high, reject or cap at maximum allowed.

Example flow when calling POST /cart/items:

  1. Cart currently has 1 unit of product 123.
  2. User tries to add 3 more.
  3. Product 123 has stock = 2.
  4. Total desired = 1 + 3 = 4, which is > 2.
  5. Backend responds with an error or adjusts to 2.

Example error response:

json 
{
  "detail": "Only 2 items of product 123 are available in stock."
}

The “Oversell” Problem

Checking stock only at cart time is not enough. Between the moment the customer adds items to the cart and the moment they pay, other customers may buy the same product.

Solution:

At checkout:

  1. Load all cart items.
  2. For each, check current stock.
  3. If any product has insufficient stock, fail the order with details.
  4. Otherwise, create the order and reduce stock atomically.

When to Decrease Stock: Reservation vs Deduction

You have two common strategies:

StrategyDescriptionProsCons
Deduct on orderReduce stock as soon as order is created / payment confirmedSimpleNo explicit reservation concept
Reserve then deductReserve stock when order is placed, deduct when shippedModels real process more closelyMore complex logic and cleanups

For this course project, a practical, simple strategy is:

If you implement reservations later, you can:

Atomic Stock Deduction with SQL

To avoid race conditions when multiple users order the same item at the same time, you must update stock atomically. There are several approaches.

Approach 1: “Update Where Stock Is Enough”

Use a single SQL update with a condition:

sql 
UPDATE products
SET stock = stock - $1
WHERE id = $2
  AND stock >= $1;

Then, in your application:

  1. Execute this update inside a transaction.
  2. Check how many rows were affected.
    • If 1, success.
    • If 0, it means there was not enough stock, maybe because another order took it.
  3. If 0 rows affected, roll back and respond with an error.

Example pseudo code:

python 
def deduct_stock(product_id: int, quantity: int):
    with db.transaction():
        rows = db.execute(
            "UPDATE products "
            "SET stock = stock - %s "
            "WHERE id = %s AND stock >= %s",
            (quantity, product_id, quantity),
        )
        if rows.rowcount == 0:
            raise NotEnoughStockError()

Critical rule: Always use a conditional update, for example WHERE stock >= quantity, to ensure you never go below zero even if multiple orders run at the same time.

Approach 2: `SELECT ... FOR UPDATE`

Another way is to lock the row explicitly:

sql 
BEGIN;
SELECT stock
FROM products
WHERE id = $1
FOR UPDATE;
-- check stock in application
UPDATE products
SET stock = stock - $2
WHERE id = $1;
COMMIT;

This is more explicit but fully correct. For this project, the conditional update approach is usually enough.

Handling Backorders and Preorders

Basic inventory logic stops someone from ordering when stock is 0. E‑commerce sites often support more advanced scenarios.

No Backorders

This is the simplest mode:

Allow Backorders

Backorders allow customers to order even when you do not have stock yet. You must decide:

You can model this with extra columns:

sql 
ALTER TABLE products
    ADD COLUMN allow_backorder BOOLEAN NOT NULL DEFAULT FALSE;

Then, in your stock check:

Example logic:

python 
if not product.allow_backorder and product.stock < requested:
    raise NotEnoughStockError()
# else, proceed, even if stock becomes negative

For this course project, you can mention backorders as an extension but keep implementation to non‑backorder logic for clarity.

Preorders

Preorders apply to products that are not yet released. Typical behavior:

You might:

Preorders complicate shipping and order states, so they are usually considered an advanced feature.

Consistency, Concurrency, and Race Conditions

Inventory is a classic example of a concurrency problem. If you do everything serially in one process, you may think “my logic is fine,” but in real deployments with multiple workers or instances, your backend must handle concurrent operations.

Common Inventory Race Condition

Imagine:

Bad implementation:

  1. Both requests call SELECT stock FROM products WHERE id = 1 and get the value 1.
  2. Both check if stock >= 1 and pass.
  3. Both run UPDATE products SET stock = stock - 1 WHERE id = 1.
  4. Final stock is -1. Oversell happened.

Correct implementation avoids this by:

Transaction Boundaries

You will integrate stock updates with order creation and payment handling. A simplified order creation transaction might look like:

  1. Begin transaction.
  2. Create order row.
  3. Create order items.
  4. Deduct stock with conditional updates for each order item.
  5. Commit transaction.

If any step fails:

If you integrate payment processing, you must consider where to place “charge the customer.” Many systems:

For this course project, you can simplify and:

Integrating Inventory with Background Jobs

Background jobs can be useful for inventory tasks that are not time critical or that require communication with external systems. Since you have a Background Jobs chapter in this project, here you focus on how background processing relates to inventory.

Examples of Inventory Background Tasks

  1. Synchronizing with external systems
    • Example: Your warehouse management system provides a daily file with actual stock counts.
    • A background job reads the file, compares counts, and pushes updates to your database.
  2. Recomputing aggregated inventory
    • If you store inventory at SKU or warehouse level, you might periodically recompute product‑level is_in_stock or availability fields.
  3. Handling slow external APIs
    • Example: For drop‑shipped items, you call supplier APIs to check real‑time availability.
    • Instead of doing this in the user request, you schedule a job to refresh availability in the background.
  4. Triggering low stock alerts
    • A nightly job examines products where stock <= threshold and sends emails or creates tasks for the purchasing team.

Example: Low Stock Alert Job

Pseudo code for a scheduled background job:

python 
LOW_STOCK_THRESHOLD = 5
def check_low_stock():
    products = db.fetch_all(
        "SELECT id, name, stock "
        "FROM products "
        "WHERE stock <= %s",
        (LOW_STOCK_THRESHOLD,)
    )
    for p in products:
        send_email_to_admins(
            subject=f"Low stock alert: {p['name']}",
            body=f"Product {p['name']} (ID {p['id']}) has stock {p['stock']}."
        )

You could schedule this job to run once per day with Celery or another task queue.

Caution with Background Stock Adjustments

While background tasks are useful, they must still respect concurrency and consistency. If a background job adjusts stock at the same time as an order is placed, you must:

Example risky pattern:

sql 
-- Risky: may overwrite stock changes from orders
UPDATE products SET stock = $new_count WHERE id = $id;

Better:

Inventory Reporting and Derived Data

From your inventory tables and movement logs, you can build simple reports that are useful even in a beginner project.

Examples:

sql 
  SELECT id, name, stock
  FROM products
  ORDER BY stock ASC
  LIMIT 20;
sql 
  SELECT
      DATE(created_at) AS date,
      SUM(quantity) AS net_change
  FROM inventory_movements
  WHERE product_id = $1
  GROUP BY DATE(created_at)
  ORDER BY date;
sql 
  SELECT p.id, p.name, p.stock
  FROM products p
  LEFT JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.id, p.name, p.stock
  HAVING COUNT(oi.id) = 0 AND p.stock > 0;

These queries support decisions like which products to discount or remove.

Putting It All Together in the Project

For this e‑commerce backend project, a reasonable first version of inventory might follow these decisions:

  1. Model
    • Single stock integer on products or on product_variants.
    • CHECK (stock >= 0) constraint.
    • Optional inventory_movements table to record changes.
  2. API behavior
    • Product listing returns at least is_in_stock or stock.
    • Cart operations validate requested quantities against stock.
    • Checkout revalidates stock before creating orders.
    • Admin endpoints for adjusting stock with reasons.
  3. Concurrency
    • Use atomic updates like UPDATE ... SET stock = stock - :qty WHERE id = :id AND stock >= :qty.
    • Treat failure to update as “not enough stock.”
  4. Background jobs (optional but recommended)
    • Daily job to send low stock emails.
    • Potential job to archive old inventory movements.

By implementing these pieces, your e‑commerce backend will correctly handle one of the most critical aspects of online selling: ensuring you do not sell what you do not have, while still giving customers an accurate and responsive shopping experience.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3096 / 24h
Impressum / Imprint
Datenschutz / Data Policy