KAHIBARO
Discord Login Register ☰
☰

31.3. Product Management

Table of Contents

Overview

In an e‑commerce backend, “product management” is everything related to storing, updating, and serving product data to clients. This includes product details, pricing, inventory links, categories, and media like images.

In this chapter you will focus on how to design and implement product management for the e‑commerce backend project. Other chapters will cover categories, inventory, and file handling in more depth, so here you will mostly connect to them and focus on product‑specific concerns.

Defining Product Requirements

Before writing any code, you should define what a “product” means in your system. Even for a simple shop, a product usually has at least:

Think in terms of API consumers:

Try to write a short example of a product in JSON, which will guide your model:

json 
{
  "id": 123,
  "name": "Wireless Mouse",
  "slug": "wireless-mouse",
  "description": "Ergonomic wireless mouse with 2.4 GHz receiver.",
  "sku": "MOUSE-001-BLK",
  "price": 24.99,
  "currency": "USD",
  "sale_price": 19.99,
  "is_active": true,
  "category_id": 5,
  "image_url": "https://cdn.example.com/products/mouse-1.jpg"
}

This JSON is not final, but it shows the core shape you will work with.

Database Model for Products

The database design of products must integrate nicely with the rest of the project’s schema. For this chapter you only need the core product table and its direct fields. Relationships like categories, inventory, and images will be refined in their own chapters, but you will still reference them here.

A simple products table can look like this:

ColumnTypeNotes
idbigint, PKAutogenerated primary key
nametextProduct name shown to customers
slugtext, uniqueURL‑friendly unique identifier
descriptiontextLong description
short_desctext, nullableShort description for listings
skutext, uniqueStock keeping unit, internal code
price_centsintegerPrice stored in smallest unit
sale_price_centsinteger, nullableOptional sale price
currencychar(3)ISO currency code, for example "USD"
is_activebooleanControls visibility in the shop
created_attimestamptzRecord creation time
updated_attimestamptzLast update time
category_idbigint, FKReference to categories table

Use integer cents for pricing, not floating‑point types:

Rule: Store money as an integer number of the smallest currency unit, for example cents, not as a floating‑point type.
For example:

  • $price\_cents = \text{round}(price\_dollars \times 100)$

This avoids rounding problems that can show up with float or double.

A basic SQL definition could be:

sql 
CREATE TABLE products (
    id                  BIGSERIAL PRIMARY KEY,
    name                TEXT NOT NULL,
    slug                TEXT NOT NULL UNIQUE,
    description         TEXT NOT NULL,
    short_description   TEXT,
    sku                 TEXT NOT NULL UNIQUE,
    price_cents         INTEGER NOT NULL CHECK (price_cents >= 0),
    sale_price_cents    INTEGER CHECK (sale_price_cents >= 0),
    currency            CHAR(3) NOT NULL,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    category_id         BIGINT REFERENCES categories(id),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

You can add database‑side triggers or application logic to keep updated_at in sync.

Product Domain Rules

Your backend should enforce important product rules consistently, both at the API layer and in the database or domain layer.

Typical rules:

You can enforce the sale price rule in domain logic:

python 
def validate_prices(price_cents: int, sale_price_cents: int | None) -> None:
    if price_cents < 0:
        raise ValueError("Price must be non-negative")
    if sale_price_cents is not None and sale_price_cents > price_cents:
        raise ValueError("Sale price must be less than or equal to regular price")

And optionally as a database constraint:

sql 
ALTER TABLE products
ADD CONSTRAINT sale_price_lte_price
CHECK (
    sale_price_cents IS NULL
    OR sale_price_cents <= price_cents
);

Product Models and DTOs

In a typical Python + FastAPI + SQLAlchemy setup, you will have different layers of models:

Example SQLAlchemy model (simplified):

python 
from sqlalchemy import Column, Integer, String, Boolean, Text, ForeignKey
from sqlalchemy.orm import relationship
from app.db.base_class import Base  # your project’s base class
class Product(Base):
    __tablename__ = "products"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(Text, nullable=False)
    slug = Column(String, unique=True, nullable=False, index=True)
    description = Column(Text, nullable=False)
    short_description = Column(Text, nullable=True)
    sku = Column(String, unique=True, nullable=False, index=True)
    price_cents = Column(Integer, nullable=False)
    sale_price_cents = Column(Integer, nullable=True)
    currency = Column(String(3), nullable=False)
    is_active = Column(Boolean, nullable=False, default=True)
    category_id = Column(Integer, ForeignKey("categories.id"), nullable=True)
    category = relationship("Category", back_populates="products")

Example Pydantic models:

python 
from pydantic import BaseModel, Field, validator
from typing import Optional
class ProductBase(BaseModel):
    name: str = Field(..., max_length=255)
    slug: str = Field(..., max_length=255)
    description: str
    short_description: Optional[str] = None
    sku: str = Field(..., max_length=64)
    price: float = Field(..., gt=0)
    sale_price: Optional[float] = Field(None, gt=0)
    currency: str = Field(..., min_length=3, max_length=3)
    is_active: bool = True
    category_id: Optional[int] = None
    @validator("sale_price")
    def check_sale_price(cls, sale_price, values):
        price = values.get("price")
        if sale_price is not None and price is not None and sale_price > price:
            raise ValueError("Sale price must be less than or equal to price")
        return sale_price
class ProductCreate(ProductBase):
    pass
class ProductUpdate(BaseModel):
    name: Optional[str] = None
    slug: Optional[str] = None
    description: Optional[str] = None
    short_description: Optional[str] = None
    price: Optional[float] = Field(None, gt=0)
    sale_price: Optional[float] = Field(None, gt=0)
    currency: Optional[str] = Field(None, min_length=3, max_length=3)
    is_active: Optional[bool] = None
    category_id: Optional[int] = None
class ProductOut(ProductBase):
    id: int
    class Config:
        orm_mode = True

Notice the separation:

In the repository or service layer you will convert between price (float or decimal in the API) and price_cents (int in the database).

Product CRUD Operations

Your product management API will at least support:

OperationHTTP MethodURIWho uses it
Create productPOST/admin/productsAdmins
List productsGET/productsPublic
Get productGET/products/{slug}Public
Update productPUT/PATCH/admin/products/{id}Admins
Delete/archiveDELETE/admin/products/{id}Admins

For the public API, prefer lookups by slug so URLs are stable and readable:

For the admin API, using numeric id is often simpler and fine.

Example create endpoint (FastAPI style, simplified, without auth):

python 
from fastapi import APIRouter, Depends, HTTPException, status
from sqlalchemy.orm import Session
from app.api.deps import get_db
from app.schemas.product import ProductCreate, ProductOut
from app import crud
router = APIRouter(prefix="/admin/products", tags=["admin:products"])
@router.post("", response_model=ProductOut, status_code=status.HTTP_201_CREATED)
def create_product(
    product_in: ProductCreate,
    db: Session = Depends(get_db),
):
    # Check if slug or SKU already exists
    existing_by_slug = crud.product.get_by_slug(db, slug=product_in.slug)
    if existing_by_slug:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Slug already exists",
        )
    existing_by_sku = crud.product.get_by_sku(db, sku=product_in.sku)
    if existing_by_sku:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="SKU already exists",
        )
    product = crud.product.create(db, obj_in=product_in)
    return product

Example public listing endpoint:

python 
@router.get("/products", response_model=list[ProductOut])
def list_products(
    db: Session = Depends(get_db),
    limit: int = 20,
    offset: int = 0,
):
    products = crud.product.get_multi_public(
        db,
        limit=limit,
        offset=offset,
    )
    return products

For the project, you will connect these endpoints with authentication and authorization in other chapters.

Product Filtering and Pagination

In an e‑commerce store, listing products means more than just “SELECT * FROM products”. You need filters and ordering.

Common filters:

Define query parameters like:

Example FastAPI signature:

python 
from typing import Optional
@router.get("/products", response_model=list[ProductOut])
def list_products(
    db: Session = Depends(get_db),
    category: Optional[str] = None,
    search: Optional[str] = None,
    min_price: Optional[float] = None,
    max_price: Optional[float] = None,
    on_sale: Optional[bool] = None,
    sort: str = "newest",
    page: int = 1,
    page_size: int = 20,
):
    products = crud.product.search_public(
        db=db,
        category_slug=category,
        search=search,
        min_price=min_price,
        max_price=max_price,
        on_sale=on_sale,
        sort=sort,
        page=page,
        page_size=page_size,
    )
    return products

Inside the repository, you can translate these into SQLAlchemy queries.

Example SQLAlchemy filter logic:

python 
from sqlalchemy import select, or_, and_
from sqlalchemy.orm import Session
from app.models import Product, Category
def search_public(
    db: Session,
    category_slug: str | None,
    search: str | None,
    min_price: float | None,
    max_price: float | None,
    on_sale: bool | None,
    sort: str,
    page: int,
    page_size: int,
):
    query = select(Product).where(Product.is_active == True)
    if category_slug:
        query = (
            query
            .join(Product.category)
            .where(Category.slug == category_slug)
        )
    if search:
        pattern = f"%{search.lower()}%"
        query = query.where(
            or_(
                Product.name.ilike(pattern),
                Product.description.ilike(pattern),
            )
        )
    if min_price is not None:
        query = query.where(Product.price_cents >= int(min_price * 100))
    if max_price is not None:
        query = query.where(Product.price_cents <= int(max_price * 100))
    if on_sale is True:
        query = query.where(Product.sale_price_cents.isnot(None))
    elif on_sale is False:
        query = query.where(Product.sale_price_cents.is_(None))
    if sort == "price_asc":
        query = query.order_by(Product.price_cents.asc())
    elif sort == "price_desc":
        query = query.order_by(Product.price_cents.desc())
    else:
        # default sort
        query = query.order_by(Product.created_at.desc())
    offset = (page - 1) * page_size
    query = query.limit(page_size).offset(offset)
    return db.execute(query).scalars().all()

Pagination and filtering will need careful performance tuning when you add indexes, but this pattern is a solid starting point.

Product Status and Soft Deletion

A real store rarely hard‑deletes products. Historical orders still reference them, and analytics need them.

There are two typical approaches:

  1. Use an is_active flag:
    • Keep the row.
    • Hide it from public listings.
    • Possibly still show it in admin and in past orders.
  2. Use both is_active and a deleted_at timestamp, often called soft deletion.

For this project, an is_active field is usually enough. Use it systematically:

Delete endpoint example that uses soft deletion instead of actual delete:

python 
@router.delete("/admin/products/{product_id}", status_code=204)
def archive_product(
    product_id: int,
    db: Session = Depends(get_db),
):
    product = crud.product.get(db, id=product_id)
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")
    product.is_active = False
    db.add(product)
    db.commit()
    return

You can still offer a “hard delete” in admin if necessary, for example when a product has no orders.

Product Pricing and Display Logic

The API that returns a product should provide a clear “effective price” that the frontend can show without guessing.

You can either:

Having the backend compute it is usually better for consistency. For example, extend the response model:

python 
class ProductOut(BaseModel):
    id: int
    name: str
    slug: str
    description: str
    short_description: Optional[str]
    sku: str
    price: float
    sale_price: Optional[float]
    currency: str
    is_active: bool
    category_id: Optional[int]
    effective_price: float
    class Config:
        orm_mode = True

And in your service layer:

python 
def to_product_out(product: Product) -> ProductOut:
    price = product.price_cents / 100
    sale_price = (
        product.sale_price_cents / 100 if product.sale_price_cents is not None else None
    )
    effective_price = sale_price if sale_price is not None else price
    return ProductOut(
        id=product.id,
        name=product.name,
        slug=product.slug,
        description=product.description,
        short_description=product.short_description,
        sku=product.sku,
        price=price,
        sale_price=sale_price,
        currency=product.currency,
        is_active=product.is_active,
        category_id=product.category_id,
        effective_price=effective_price,
    )

You can also add flags like is_on_sale:

python 
is_on_sale = sale_price is not None and sale_price < price

This makes it easy for clients to show sale badges.

Connecting Products with Categories and Inventory

This chapter focuses on products, but you must keep in mind how products will connect to categories and inventory, which have their own chapters.

You will typically:

Some common patterns:

ConcernMinimal approachMore advanced approach
Categoriesproducts.category_idproduct_categories join table
Inventoryproducts.stock_quantityinventory_items table per warehouse
Mediaproducts.image_urlproduct_images table

For the project:

When designing endpoints consider both:

To avoid circular dependencies or heavy joins everywhere, you can create a “rich product” response model that includes nested objects:

python 
class CategorySummary(BaseModel):
    id: int
    name: str
    slug: str
class InventorySummary(BaseModel):
    in_stock: bool
    stock_quantity: int
class ProductDetail(ProductOut):
    category: Optional[CategorySummary]
    inventory: InventorySummary

Then use this model only for detail views, for example GET /products/{slug}, while list views use the lighter ProductOut model.

Typical Product Management Workflows

To wrap up, here is how a standard product management flow looks from the admin side and the customer side.

Admin workflow:

  1. Admin creates a product draft:
    • POST /admin/products
    • Fills in basic details, price, SKU, and chooses category.
  2. Admin uploads product images:
    • Uses file upload endpoints described in the “Working with Files” and “Image Uploads” chapters.
  3. Admin adjusts inventory:
    • Uses “Inventory” or “Stock” related endpoints.
  4. Admin publishes the product:
    • Sets is_active = true if it is not already.

Customer workflow:

  1. Customer opens a category page:
    • GET /products?category=some-category&sort=newest&page=1
    • Backend returns only is_active = true products.
  2. Customer opens a product detail page:
    • GET /products/some-product-slug
    • Backend returns product with category, effective price, sale information, and availability.
  3. Customer adds product to cart:
    • Uses endpoints described in the “Shopping Cart” chapter.
    • Backend uses product and inventory info to validate the operation.

Your product management implementation must support both these workflows reliably and efficiently.

As you continue the project, you will integrate these product concepts with categories, images, inventory, cart, and orders, but the core idea remains: a clear and consistent product model, with strict domain rules, and well designed CRUD and listing APIs.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

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