Case Study 2: E-Commerce Product Catalog

Overview

This case study walks through the development of ShopStream, a full-stack product catalog application for a small e-commerce business. The application features product browsing with search and filtering, image uploads for product listings, a responsive grid layout, and an admin interface for managing inventory. Unlike the chat application in Case Study 1 (which focused on real-time WebSocket communication), this case study emphasizes REST API design, file upload handling, complex database queries, and search functionality.

Requirements

ShopStream must support the following features:

• Product browsing with a responsive grid layout showing product images, titles, and prices
• Search by product name and description with relevant result highlighting
• Filtering by category, price range, and availability (in stock / out of stock)
• Sorting by price (low to high, high to low), name, and newest
• Pagination for large product catalogs
• Product detail pages with multiple images, full description, and specifications
• Image upload for product photos (admin only)
• Admin dashboard for adding, editing, and deleting products
• Responsive design that works on desktop, tablet, and mobile

Architecture Decisions

Technology Stack

We chose PostgreSQL's built-in full-text search over a dedicated search engine like Elasticsearch. For catalogs under 100,000 products, PostgreSQL's tsvector and tsquery provide adequate search performance without the operational overhead of maintaining a separate search cluster. This decision should be revisited if the catalog grows significantly or if advanced search features (fuzzy matching, faceted search with counts) become requirements.

Data Model

products — the core entity: - id (primary key, serial) - name (varchar 200, not null) - description (text) - price (decimal 10,2, not null) - compare_at_price (decimal 10,2, nullable — for showing "was $X, now $Y") - category_id (foreign key to categories) - sku (varchar 50, unique) - stock_quantity (integer, default 0) - is_active (boolean, default true) - specifications (JSONB — flexible key-value pairs like {"weight": "500g", "color": "Blue"}) - tags (ARRAY of varchar — for additional categorization) - search_vector (tsvector — auto-updated for full-text search) - created_at, updated_at

categories — product categories: - id (primary key) - name (varchar 100, unique) - slug (varchar 100, unique — URL-friendly version of name) - description (text) - parent_id (self-referencing foreign key for subcategories)

product_images — multiple images per product: - id (primary key) - product_id (foreign key to products) - url (varchar 500 — the stored file URL) - alt_text (varchar 200) - sort_order (integer — for controlling display order) - is_primary (boolean — the main product image)

The decision to use a separate product_images table rather than an ARRAY column allows each image to have metadata (alt text, sort order) and supports efficient queries for primary images across product lists.

API Design

We designed the API with RESTful principles, paying special attention to the search and filtering capabilities:

Products:
  GET    /api/products                    List/search products
  GET    /api/products/{id}               Get product detail
  POST   /api/products          (admin)   Create product
  PUT    /api/products/{id}     (admin)   Update product
  DELETE /api/products/{id}     (admin)   Delete product

Product Images:
  POST   /api/products/{id}/images  (admin)   Upload image
  DELETE /api/images/{id}           (admin)   Delete image
  PATCH  /api/images/{id}/order     (admin)   Reorder image

Categories:
  GET    /api/categories                   List categories
  POST   /api/categories        (admin)    Create category

The GET /api/products endpoint accepts these query parameters:

?q=wireless+headphones     Search query
&category=electronics      Filter by category slug
&min_price=50              Minimum price
&max_price=200             Maximum price
&in_stock=true             Only show in-stock products
&sort=price_asc            Sort order (price_asc, price_desc, name, newest)
&page=1                    Page number
&per_page=24               Items per page (max 100)

Development Process

Phase 1: Database and Models

We began with the SQLAlchemy models, asking the AI to implement full-text search:

"Create SQLAlchemy models for an e-commerce product catalog. The Product
model should include a PostgreSQL tsvector column for full-text search
that automatically updates when the product name, description, or tags
change. Use a trigger or event listener to keep the search vector in sync."

The search vector configuration uses PostgreSQL's tsvector_update_trigger:

class Product(Base):
    __tablename__ = "products"

    id = Column(Integer, primary_key=True)
    name = Column(String(200), nullable=False)
    description = Column(Text, default="")
    price = Column(Numeric(10, 2), nullable=False)
    compare_at_price = Column(Numeric(10, 2))
    category_id = Column(Integer, ForeignKey("categories.id"))
    sku = Column(String(50), unique=True)
    stock_quantity = Column(Integer, default=0)
    is_active = Column(Boolean, default=True)
    specifications = Column(JSONB, default={})
    tags = Column(ARRAY(String), default=[])
    search_vector = Column(TSVector)
    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())

    category = relationship("Category", back_populates="products")
    images = relationship(
        "ProductImage",
        back_populates="product",
        order_by="ProductImage.sort_order",
    )

    @property
    def primary_image(self):
        for img in self.images:
            if img.is_primary:
                return img
        return self.images[0] if self.images else None

    @property
    def is_on_sale(self):
        return (
            self.compare_at_price is not None
            and self.compare_at_price > self.price
        )

The Alembic migration includes a GIN index on the search vector for fast full-text queries:

# In the Alembic migration
op.create_index(
    "idx_product_search_vector",
    "products",
    ["search_vector"],
    postgresql_using="gin",
)

Phase 2: Search and Filtering API

The product listing endpoint handles search, filtering, sorting, and pagination in a single query:

@router.get("/api/products")
async def list_products(
    q: Optional[str] = None,
    category: Optional[str] = None,
    min_price: Optional[float] = None,
    max_price: Optional[float] = None,
    in_stock: Optional[bool] = None,
    sort: str = "newest",
    page: int = Query(default=1, ge=1),
    per_page: int = Query(default=24, ge=1, le=100),
    db: Session = Depends(get_db),
):
    query = db.query(Product).filter(Product.is_active == True)

    # Full-text search
    if q:
        search_query = func.plainto_tsquery("english", q)
        query = query.filter(
            Product.search_vector.op("@@")(search_query)
        )

    # Category filter
    if category:
        query = query.join(Category).filter(Category.slug == category)

    # Price range filter
    if min_price is not None:
        query = query.filter(Product.price >= min_price)
    if max_price is not None:
        query = query.filter(Product.price <= max_price)

    # Stock filter
    if in_stock is not None:
        if in_stock:
            query = query.filter(Product.stock_quantity > 0)
        else:
            query = query.filter(Product.stock_quantity == 0)

    # Get total count before pagination
    total = query.count()

    # Sorting
    sort_mapping = {
        "price_asc": Product.price.asc(),
        "price_desc": Product.price.desc(),
        "name": Product.name.asc(),
        "newest": Product.created_at.desc(),
    }
    query = query.order_by(sort_mapping.get(sort, Product.created_at.desc()))

    # Pagination
    offset = (page - 1) * per_page
    products = query.offset(offset).limit(per_page).all()

    return {
        "products": [ProductListResponse.from_orm(p) for p in products],
        "total": total,
        "page": page,
        "per_page": per_page,
        "total_pages": (total + per_page - 1) // per_page,
    }

Phase 3: Image Upload System

Product image uploads follow the pattern from Section 19.6, with additional features for handling multiple images per product:

@router.post("/api/products/{product_id}/images")
async def upload_product_image(
    product_id: int,
    file: UploadFile = File(...),
    alt_text: str = Form(default=""),
    is_primary: bool = Form(default=False),
    current_user: User = Depends(require_admin),
    db: Session = Depends(get_db),
):
    product = db.query(Product).get(product_id)
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")

    # Validate file
    extension = Path(file.filename).suffix.lower()
    if extension not in {".jpg", ".jpeg", ".png", ".webp"}:
        raise HTTPException(status_code=400, detail="Invalid image format")

    contents = await file.read()
    if len(contents) > 10 * 1024 * 1024:
        raise HTTPException(status_code=400, detail="Image too large (max 10MB)")

    # Generate unique filename
    filename = f"products/{product_id}/{uuid4().hex}{extension}"

    # Store the file (abstracted for dev/prod)
    url = await storage.save(filename, contents)

    # If this is marked primary, unmark any existing primary
    if is_primary:
        db.query(ProductImage).filter(
            ProductImage.product_id == product_id,
            ProductImage.is_primary == True,
        ).update({"is_primary": False})

    # Get next sort order
    max_order = db.query(func.max(ProductImage.sort_order)).filter(
        ProductImage.product_id == product_id
    ).scalar() or 0

    image = ProductImage(
        product_id=product_id,
        url=url,
        alt_text=alt_text or product.name,
        sort_order=max_order + 1,
        is_primary=is_primary,
    )
    db.add(image)
    db.commit()

    return {"id": image.id, "url": image.url}

The storage layer is abstracted behind an interface so that development uses the local filesystem while production uses S3:

class StorageBackend:
    async def save(self, key: str, content: bytes) -> str:
        raise NotImplementedError

class LocalStorage(StorageBackend):
    async def save(self, key: str, content: bytes) -> str:
        path = Path("uploads") / key
        path.parent.mkdir(parents=True, exist_ok=True)
        path.write_bytes(content)
        return f"/uploads/{key}"

class S3Storage(StorageBackend):
    def __init__(self, bucket: str, region: str):
        self.bucket = bucket
        self.client = boto3.client("s3", region_name=region)

    async def save(self, key: str, content: bytes) -> str:
        self.client.put_object(Bucket=self.bucket, Key=key, Body=content)
        return f"https://{self.bucket}.s3.amazonaws.com/{key}"

# Choose based on environment
storage = (
    S3Storage(settings.s3_bucket, settings.aws_region)
    if settings.environment == "production"
    else LocalStorage()
)

Phase 4: React Frontend

The frontend was built in three stages: product grid, search/filtering, and admin interface.

Product Grid Component:

function ProductGrid({ products }: { products: Product[] }) {
    return (
        <div className="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-3
                        xl:grid-cols-4 gap-6">
            {products.map(product => (
                <ProductCard key={product.id} product={product} />
            ))}
        </div>
    );
}

function ProductCard({ product }: { product: Product }) {
    return (
        <Link to={`/products/${product.id}`}
              className="group bg-white rounded-lg shadow-sm
                         hover:shadow-md transition-shadow">
            <div className="aspect-square overflow-hidden rounded-t-lg">
                <img
                    src={product.primaryImageUrl || '/placeholder.png'}
                    alt={product.name}
                    className="w-full h-full object-cover
                               group-hover:scale-105 transition-transform"
                />
            </div>
            <div className="p-4">
                <h3 className="font-medium text-gray-900 truncate">
                    {product.name}
                </h3>
                <div className="mt-1 flex items-center gap-2">
                    <span className="text-lg font-bold">
                        ${product.price.toFixed(2)}
                    </span>
                    {product.isOnSale && (
                        <span className="text-sm text-gray-500 line-through">
                            ${product.compareAtPrice?.toFixed(2)}
                        </span>
                    )}
                </div>
                {product.stockQuantity === 0 && (
                    <span className="text-sm text-red-600">Out of stock</span>
                )}
            </div>
        </Link>
    );
}

Search and Filter Component:

The search and filter state is synchronized with the URL using React Router's search params, so users can bookmark and share filtered views:

function ProductFilters() {
    const [searchParams, setSearchParams] = useSearchParams();

    const updateFilter = (key: string, value: string | null) => {
        const newParams = new URLSearchParams(searchParams);
        if (value === null || value === '') {
            newParams.delete(key);
        } else {
            newParams.set(key, value);
        }
        newParams.set('page', '1'); // Reset to page 1 on filter change
        setSearchParams(newParams);
    };

    return (
        <aside className="w-64 space-y-6">
            <SearchInput
                value={searchParams.get('q') || ''}
                onChange={(q) => updateFilter('q', q)}
            />
            <CategoryFilter
                selected={searchParams.get('category') || ''}
                onChange={(cat) => updateFilter('category', cat)}
            />
            <PriceRangeFilter
                min={searchParams.get('min_price') || ''}
                max={searchParams.get('max_price') || ''}
                onMinChange={(v) => updateFilter('min_price', v)}
                onMaxChange={(v) => updateFilter('max_price', v)}
            />
            <StockFilter
                value={searchParams.get('in_stock')}
                onChange={(v) => updateFilter('in_stock', v)}
            />
            <SortSelect
                value={searchParams.get('sort') || 'newest'}
                onChange={(v) => updateFilter('sort', v)}
            />
        </aside>
    );
}

The main product listing page reads the URL parameters and passes them to the API:

function ProductListingPage() {
    const [searchParams] = useSearchParams();

    const queryString = searchParams.toString();
    const { data, loading, error } = useApi<ProductListResponse>(
        `/api/products?${queryString}`
    );

    return (
        <div className="flex gap-8">
            <ProductFilters />
            <main className="flex-1">
                {loading && <ProductGridSkeleton />}
                {error && <ErrorMessage message={error} />}
                {data && (
                    <>
                        <div className="mb-4 text-gray-600">
                            {data.total} products found
                        </div>
                        <ProductGrid products={data.products} />
                        <Pagination
                            currentPage={data.page}
                            totalPages={data.totalPages}
                            onPageChange={(p) => {
                                const params = new URLSearchParams(searchParams);
                                params.set('page', String(p));
                                window.history.pushState({}, '', `?${params}`);
                            }}
                        />
                    </>
                )}
            </main>
        </div>
    );
}

Admin Product Editor:

The admin interface includes a multi-image uploader with drag-to-reorder functionality and inline editing of product fields. We used the react-dropzone library for drag-and-drop file uploads and dnd-kit for image reordering.

Phase 5: Search Optimization

After the basic search worked, we improved relevance by weighting different fields:

# Name matches are weighted higher than description matches
search_vector = func.setweight(
    func.to_tsvector('english', Product.name), 'A'
).concat(
    func.setweight(
        func.to_tsvector('english', Product.description), 'B'
    )
)

We also added search suggestions for the autocomplete feature:

@router.get("/api/products/suggestions")
async def search_suggestions(
    q: str = Query(..., min_length=2),
    db: Session = Depends(get_db),
):
    """Return product name suggestions for autocomplete."""
    products = db.query(Product.name).filter(
        Product.name.ilike(f"%{q}%"),
        Product.is_active == True,
    ).limit(8).all()

    return [p.name for p in products]

On the frontend, search suggestions are fetched with debouncing to avoid overwhelming the API:

function SearchInput({ value, onChange }: SearchInputProps) {
    const [localValue, setLocalValue] = useState(value);
    const [suggestions, setSuggestions] = useState<string[]>([]);
    const [showSuggestions, setShowSuggestions] = useState(false);

    // Debounce search
    useEffect(() => {
        const timer = setTimeout(() => onChange(localValue), 300);
        return () => clearTimeout(timer);
    }, [localValue]);

    // Fetch suggestions
    useEffect(() => {
        if (localValue.length < 2) {
            setSuggestions([]);
            return;
        }
        const timer = setTimeout(async () => {
            const results = await api.get<string[]>(
                `/api/products/suggestions?q=${encodeURIComponent(localValue)}`
            );
            setSuggestions(results);
            setShowSuggestions(true);
        }, 200);
        return () => clearTimeout(timer);
    }, [localValue]);

    return (
        <div className="relative">
            <input
                type="text"
                value={localValue}
                onChange={(e) => setLocalValue(e.target.value)}
                onFocus={() => setShowSuggestions(true)}
                onBlur={() => setTimeout(() => setShowSuggestions(false), 200)}
                placeholder="Search products..."
                className="w-full px-4 py-2 border rounded-lg"
            />
            {showSuggestions && suggestions.length > 0 && (
                <ul className="absolute z-10 w-full bg-white border
                               rounded-lg mt-1 shadow-lg">
                    {suggestions.map(s => (
                        <li
                            key={s}
                            className="px-4 py-2 hover:bg-gray-100 cursor-pointer"
                            onMouseDown={() => {
                                setLocalValue(s);
                                onChange(s);
                                setShowSuggestions(false);
                            }}
                        >
                            {s}
                        </li>
                    ))}
                </ul>
            )}
        </div>
    );
}

Challenges Encountered

Challenge 1: Image Optimization

Raw uploaded images were often 3-5 MB each, making the product grid slow to load. We added server-side image processing using the Pillow library to generate thumbnails and optimized versions:

from PIL import Image
import io

def process_product_image(
    content: bytes, max_size: tuple = (1200, 1200)
) -> tuple[bytes, bytes]:
    """Process an uploaded image, returning (full, thumbnail) bytes."""
    img = Image.open(io.BytesIO(content))

    # Convert to RGB if necessary (handles PNG with alpha)
    if img.mode in ("RGBA", "P"):
        img = img.convert("RGB")

    # Resize full image if larger than max
    img.thumbnail(max_size, Image.LANCZOS)
    full_buffer = io.BytesIO()
    img.save(full_buffer, format="JPEG", quality=85)

    # Generate thumbnail
    thumb = img.copy()
    thumb.thumbnail((400, 400), Image.LANCZOS)
    thumb_buffer = io.BytesIO()
    thumb.save(thumb_buffer, format="JPEG", quality=80)

    return full_buffer.getvalue(), thumb_buffer.getvalue()

The product listing uses thumbnails while the detail page loads full-size images.

Challenge 2: URL-Based Filter State

Keeping the URL in sync with filter state proved tricky. When the user changes a filter, the URL must update. When the user navigates back or shares a URL, the filters must be initialized from the URL. We solved this by making useSearchParams the single source of truth for filter state — components read from it and write to it, never maintaining their own parallel state.

Challenge 3: Price Range Filtering UX

A simple min/max price input was not user-friendly because users did not know the price range of available products. We added a price distribution endpoint that returns the min, max, and histogram of prices in the current filtered set:

@router.get("/api/products/price-range")
async def get_price_range(
    category: Optional[str] = None,
    db: Session = Depends(get_db),
):
    query = db.query(
        func.min(Product.price),
        func.max(Product.price),
    ).filter(Product.is_active == True)

    if category:
        query = query.join(Category).filter(Category.slug == category)

    min_price, max_price = query.one()
    return {"min": float(min_price or 0), "max": float(max_price or 0)}

This allowed us to render a price range slider with appropriate bounds.

Performance Optimizations

1. Database query optimization. The initial product listing query performed N+1 queries — one for the product list and one for each product's primary image. We solved this with eager loading: query.options(joinedload(Product.images)).

2. Image lazy loading. Product images below the fold use the loading="lazy" attribute so the browser only loads images as they scroll into view.

3. API response caching. The category list rarely changes, so we cache it for 5 minutes on the backend. Product listing responses are not cached because they depend on filter parameters and stock levels.

4. Search index maintenance. The GIN index on the search_vector column is updated automatically by a PostgreSQL trigger whenever the product name or description changes.

Results

The completed ShopStream application demonstrates:

• Complex query building with multiple optional filters composed dynamically
• Full-text search using PostgreSQL's native capabilities
• Multi-file upload with server-side image processing
• URL-driven state for shareable, bookmarkable filtered views
• Role-based access separating admin operations from public browsing
• Storage abstraction for environment-appropriate file handling
• Responsive design with Tailwind CSS grid utilities

The application handles a catalog of 10,000 products with sub-200ms response times for filtered queries, thanks to proper indexing and query optimization. The search functionality returns relevant results within 50ms for most queries, acceptable for a catalog of this size.

Lessons Learned

1. URL state simplifies everything. Making the URL the source of truth for search and filter state eliminates an entire class of synchronization bugs. Components do not need to coordinate with each other — they all read from and write to the URL.

2. Image processing is essential. Serving raw uploaded images directly destroys page load performance. Always generate optimized versions server-side.

3. PostgreSQL is a capable search engine. For moderate-sized catalogs, PostgreSQL's full-text search eliminates the need for a dedicated search infrastructure. Upgrade to Elasticsearch only when you need features PostgreSQL cannot provide (fuzzy matching, complex faceting, or catalogs with millions of items).

4. AI generates excellent filter query builders. The pattern of composing SQLAlchemy queries with optional filters is something AI assistants generate very reliably. Prompt with: "Build a product listing endpoint with optional filtering by category, price range, and stock status."

5. Separate read and write interfaces. The public product browsing API and the admin management API have different concerns (read performance vs. data validation). Keeping them in separate routers with separate schemas made the code cleaner and the permissions model simpler.