I wanted a project small enough to finish but complete enough to be real: a shop that sells T-shirts and trousers, with an AI assistant that answers questions about the store. Three moving parts — a backend, a frontend, and a RAG chatbot — each kept as simple as it can be while still resembling something you’d actually ship.

This is Part 1 of 3:

• Part 1 — The backend (you’re here)
• Part 2 — The frontend
• Part 3 — The RAG chatbot

The whole thing runs in containers on a local Kubernetes cluster, but you don’t need any of that to follow along — the backend is just a Python process talking to Postgres.

The stack, and why

• FastAPI for the API. You write plain Python functions, annotate the inputs, and get request validation plus an interactive /docs page for free.
• PostgreSQL for storage. One database holds both the relational data (products, orders) and, later, the chatbot’s embeddings.
• SQLAlchemy to describe tables as Python classes instead of raw SQL.

Nothing exotic. The goal is to understand each layer, not to collect frameworks.

Modeling the data

The schema is the part worth getting right early, because everything else leans on it. Here’s the products table as a SQLAlchemy model:

class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(200), nullable=False)
    description: Mapped[str] = mapped_column(Text, default="")
    category: Mapped[str] = mapped_column(String(50), index=True)   # "tshirt" | "trousers"
    price_cents: Mapped[int] = mapped_column(Integer, nullable=False)
    image_url: Mapped[str] = mapped_column(String(500), default="")
    stock: Mapped[int] = mapped_column(Integer, default=0)

One detail that matters more than it looks: money is stored as integer cents, not a float. 19.99 as a floating-point number is actually 19.9899999…, and those rounding errors compound the moment you start summing a cart. Store 1999, divide by 100 only when you display it.

The rest of the schema follows the same shape — users, orders, and order_items — plus one table we’ll ignore until Part 3 (doc_chunks, where the chatbot’s embeddings live). Order items snapshot the price at purchase time, so changing a product’s price later doesn’t silently rewrite past orders.

One database session per request

Every request needs a short-lived connection to the database, and it must be cleaned up afterwards — even if the request errors. FastAPI’s dependency system makes this a few lines:

def get_db():
    db = SessionLocal()
    try:
        yield db          # hand the session to the route
    finally:
        db.close()        # always runs, even on error

Any endpoint that adds db: Session = Depends(get_db) gets a fresh session, and FastAPI closes it when the response is sent. You write the business logic; the framework handles the lifecycle.

The catalog API

Three read-only endpoints cover browsing: list products, search them, and fetch one by id.

@router.get("/products", response_model=list[ProductOut])
def list_products(category: str | None = None, db: Session = Depends(get_db)):
    stmt = select(Product)
    if category:
        stmt = stmt.where(Product.category == category)
    return db.scalars(stmt.order_by(Product.id)).all()

Search is a case-insensitive partial match using SQL’s ILIKE — no separate search engine, just the database doing what it’s good at:

@router.get("/products/search", response_model=list[ProductOut])
def search_products(q: str, db: Session = Depends(get_db)):
    pattern = f"%{q}%"
    stmt = select(Product).where(
        or_(Product.name.ilike(pattern), Product.description.ilike(pattern))
    )
    return db.scalars(stmt.order_by(Product.id)).all()

There’s one trap here that cost me a few confused minutes: declare /products/search before /products/{id}. FastAPI matches routes top to bottom, so if {id} comes first, a request for /products/search gets parsed as “fetch the product with id search” and your search endpoint becomes unreachable. Order matters.

Keeping the API shape separate from the database

Notice response_model=ProductOut above. ProductOut is a Pydantic model — a description of the JSON the API returns, deliberately separate from the SQLAlchemy table:

class ProductOut(BaseModel):
    id: int
    name: str
    description: str
    category: str
    price_cents: int
    image_url: str
    stock: int

    model_config = ConfigDict(from_attributes=True)   # read fields off the ORM object

This decoupling pays off later: you can add internal columns without leaking them to the outside world, and the API contract stays stable even as the table changes. from_attributes=True lets Pydantic read straight off the ORM object, so the route can just return product.

Getting it running

For a learning project, I create tables at startup rather than managing migrations:

Base.metadata.create_all(bind=engine)   # create any missing tables

That’s the simplest possible start. For a real app with a schema that evolves over time, you’d reach for a migration tool like Alembic — but that’s a complication this project doesn’t need yet.

A small seed script inserts ~15 sample products, and then you can poke at the API immediately:

curl localhost:8000/api/products
curl "localhost:8000/api/products/search?q=cotton"

Or open http://localhost:8000/docs and click through the endpoints — FastAPI generates that page from the same type hints that power validation.

That’s the foundation: a typed API over a clean schema, with the database doing the searching. In Part 2 we put a React frontend in front of it.