How does the chatbot avoid making up product details?

It only sees retrieved catalogue rows. Vector results past a distance threshold are discarded before the prompt is built, and if nothing survives, the context explicitly says no match was found and the system prompt tells the model to ask the customer for more detail rather than guess.

Why Polish-first rather than translating an English bot?

The catalogue is Polish, the customers are Polish, and the embeddings work best when query and document share a language. Product documents, the system prompt, the recommendation format and the error messages are all written in Polish, so nothing round-trips through a translation step.

Is the source code open?

No. This was client work and the repository is private, so there is no public code or hosted demo to link. The architecture described here is the one in the repository: Flask, LangChain, ChromaDB, OpenAI embeddings and GPT-4o, with the catalogue supplied as an Excel file.

What does something like this cost to run?

The recurring costs are OpenAI embedding calls at index time, one GPT-4o completion per customer message, and hosting. Embeddings are a one-off per catalogue rebuild because the ChromaDB collection is persistent. Completion volume tracks conversation volume, so cost scales with traffic rather than catalogue size.

Can this work with my WooCommerce store instead of a spreadsheet?

Yes. The indexing layer reads a tabular product source and turns each row into a document with metadata. Swapping the Excel reader for the WooCommerce REST API is a change to that one step. The retrieval, filtering and prompting layers stay as they are.

How does it keep track of a conversation?

A session manager issues a UUID per conversation and stores the message history along with a gender-preference field that the retrieval filter reads. The last three exchanges are injected into each prompt. Sessions expire after an hour of inactivity, and the least recently active session is evicted once the configured ceiling is reached.

Polish-Language RAG Chatbot for a WooCommerce Retailer — Case Study

Introduction

A perfume retailer in Poland wanted a shop assistant on their WooCommerce site that could talk to customers in Polish about their own catalogue. What they had to work with was one Excel file. This is what it took to make that file behave like a knowledge base.

The Challenge

The catalogue arrived as a single spreadsheet with Polish column headers, comma-packed fragrance-note lists, an availability flag and a product URL per row. None of it was structured for retrieval, and half the useful signal was buried inside free-text recommendation columns. On top of that, every product in the range is a numbered house fragrance inspired by a well-known designer perfume, and the client's written brief was explicit that the assistant must never repeat those designer names back to a customer. A generic chatbot bolted onto the store would have done exactly the wrong thing: guessed at fragrance notes it did not have, recommended discontinued items, answered in English, and cheerfully named a competitor's product.

The Solution

I built a Flask API with a RAG pipeline behind it. A rebuild script reads the Excel workbook with pandas and openpyxl, writes a UTF-8 CSV, and indexes every row into a persistent ChromaDB collection using OpenAI embeddings. Each row becomes a Polish-language document describing the fragrance, carrying the full original record plus derived fields in metadata. Incoming messages are routed by intent before retrieval happens, so an exact product number never goes through semantic search. GPT-4o then writes the reply in Polish from the retrieved context, under a Polish system prompt that fixes tone, format and the rules it is not allowed to break.

Technical Deep Dive

Excel to knowledge base. The source of truth stays the client's spreadsheet. A rebuild script converts it to CSV with pandas, then builds documents where each product's text body carries the fragrance description in Polish and the metadata dictionary carries every original column plus derived fields: a normalised gender category, a boolean availability flag, a split list of fragrance families, a split list of individual notes, and personality keywords extracted from the free-text column describing who each fragrance is for, by matching Polish word stems such as zmysłow, tajemnicz and eleganck. That metadata is what makes filtered retrieval possible later.

Persistent vector store. Documents are split with a recursive character splitter at 1000 characters with 200 characters of overlap, embedded with OpenAI embeddings, and written to a named ChromaDB collection with a persist directory. On startup the store checks whether the collection exists and is non-empty before rebuilding, so a restart does not re-embed the whole catalogue. Rebuilds are an explicit force flag, not an accident.

Query routing before retrieval. Product-number queries are extracted by regex, normalised to strip leading zeros, and verified against the dataframe before they count as a match, so a partial numeric match cannot resolve to the wrong product. Occasion queries, inspiration-style queries and general recommendation queries each get their own filter set and their own appended prompt instructions. Only those paths hit vector search.

Filtered similarity search. Searches use similarity with scores and pass a ChromaDB filter built from the session state: availability is always constrained to in-stock items, and a gender filter is added whenever the session carries a gender preference. Single filters are emitted as an equality clause and multiple filters are combined into an $and expression, which is the format ChromaDB expects.

Occasion re-ranking. For occasion queries the retriever pulls a wider candidate set, then scores each candidate against the occasion by checking the recommendation and target-audience columns for related Polish and English keywords. The occasion relevance score is averaged with the vector distance, and the top five are re-ordered before they reach the prompt. The occasion signal lives in free-text columns that a raw distance score treats as ordinary description text, so the re-rank is what brings it back into the ranking.

Refusal over invention. The context builder drops retrieved documents whose distance score is above a threshold, so a weak match never enters the prompt dressed as a fact. If nothing survives, the context is an explicit Polish statement that no matching fragrance was found, and the system prompt supplies the exact wording for asking the customer for more detail. LLM call failures return a Polish apology rather than a stack trace.

Sessions and conversation state. An in-memory session manager issues UUID sessions, tracks the last-activity timestamp, expires sessions after an hour, and evicts the least recently active session when it hits its configured ceiling. Each session carries the running message history plus a gender-preference field that the retrieval filter reads, and the last three exchanges are injected into the user prompt so follow-up questions stay in context.

Deployment shape. The Flask app exposes chat, health, session and product-statistics endpoints with CORS enabled for browser clients, and ships with a serverless entry point, a Vercel config that raises the function timeout, and a Netlify function build. Hosted filesystems are ephemeral, so the vector-store bootstrap checks for the hosted-environment flag and rebuilds the index from the bundled CSV on cold start rather than trusting a persisted directory.

Key Features

Answers in Polish, grounded in the catalogue

GPT-4o is instructed in Polish and writes in Polish, from retrieved product rows only. Fragrance notes, groups, availability and the product link all come from the client's own data.

Exact product-number lookup

House fragrances are identified by number. Numbers are normalised and verified against the source data before matching, so a request for one product cannot silently resolve to another with similar digits.

Occasion and note-based recommendation

Customers can ask for something for a date, for the office or with a particular note. Semantic retrieval is combined with keyword relevance against the recommendation columns to rank candidates.

Brand-neutrality guardrail

The catalogue stores the inspiration brand and name, and those fields are never surfaced to the customer. Inspiration-style questions get an elegant redirect to the retailer's own numbered range.

In-stock only

Retrieval filters on a derived availability boolean, so the assistant does not recommend a fragrance the customer cannot buy.

LLM-as-judge evaluation

The codebase carries a separate low-temperature GPT-4o evaluator that scores a reply as JSON across persona, rule compliance, factual accuracy, Polish language quality and helpfulness, with a fallback path when the JSON does not parse. It is a development-time module, not part of the request path.

Results & Impact

✓The full catalogue is indexed and searchable. The project's own documentation records 330 products in the vector database, which matches the row count of the CSV in the repository.
✓The assistant is reproducible from source. A single script converts the client's Excel workbook to a UTF-8 CSV and rebuilds the ChromaDB collection, so the index can be regenerated from the spreadsheet and an OpenAI key.
✓Product answers are traceable. Every recommendation the model makes is assembled from retrieved rows that carry the product number, notes, fragrance group, availability and store link, so a wrong answer is debuggable rather than mysterious.
✓The API surface is complete enough to embed: chat with session continuity, session create and delete, health, and product statistics, with CORS enabled for browser clients.
✓The repository documents two rounds of retrieval work on inspiration-style queries, moving from a hardcoded brand list to semantic detection and richer indexed product descriptions.

Lessons Learned

"Non-English retrieval lives or dies on the document text, not the model. Writing each product document in Polish, in the same vocabulary customers use, did more for retrieval quality than any amount of prompt tuning. Embedding English scaffolding around Polish data would have made every query a translation problem."

"Do not route everything through the vector store. Product-number lookups are an exact-match problem and semantic search is actively bad at them. Adding a deterministic branch in front of retrieval removed a whole class of confidently wrong answers."

"Similarity scores need a floor. Vector search always returns its k nearest neighbours, including when nothing is actually relevant. Dropping results past a distance threshold, and letting the context be empty, is what turns a hallucinating bot into one that says it did not find anything."

"A messy spreadsheet is a feature, not an obstacle, if you enrich at index time. Splitting comma-packed note lists, normalising the gender column and pulling personality keywords out of free text gave me metadata filters that the raw file could never have supported."

Conclusion

This build is a small, honest RAG system: one catalogue, one language, strict rules about what it is allowed to say. The interesting engineering was not the model call but everything around it, the indexing, the routing, the filters and the refusal path.

Frequently asked questions

How does the chatbot avoid making up product details?: It only sees retrieved catalogue rows. Vector results past a distance threshold are discarded before the prompt is built, and if nothing survives, the context explicitly says no match was found and the system prompt tells the model to ask the customer for more detail rather than guess.
Why Polish-first rather than translating an English bot?: The catalogue is Polish, the customers are Polish, and the embeddings work best when query and document share a language. Product documents, the system prompt, the recommendation format and the error messages are all written in Polish, so nothing round-trips through a translation step.
Is the source code open?: No. This was client work and the repository is private, so there is no public code or hosted demo to link. The architecture described here is the one in the repository: Flask, LangChain, ChromaDB, OpenAI embeddings and GPT-4o, with the catalogue supplied as an Excel file.
What does something like this cost to run?: The recurring costs are OpenAI embedding calls at index time, one GPT-4o completion per customer message, and hosting. Embeddings are a one-off per catalogue rebuild because the ChromaDB collection is persistent. Completion volume tracks conversation volume, so cost scales with traffic rather than catalogue size.
Can this work with my WooCommerce store instead of a spreadsheet?: Yes. The indexing layer reads a tabular product source and turns each row into a document with metadata. Swapping the Excel reader for the WooCommerce REST API is a change to that one step. The retrieval, filtering and prompting layers stay as they are.
How does it keep track of a conversation?: A session manager issues a UUID per conversation and stores the message history along with a gender-preference field that the retrieval filter reads. The last three exchanges are injected into each prompt. Sessions expire after an hour of inactivity, and the least recently active session is evicted once the configured ceiling is reached.
How do you know the answers are actually good?: The codebase carries an LLM-as-judge module, a separate GPT-4o evaluator at low temperature that scores a reply as JSON across persona fit, rule compliance, factual accuracy, Polish language quality and helpfulness. It is a development-time harness rather than part of the request path, and it sits alongside scripted integration checks that run against a local server.

Polish-Language RAG Chatbot for a WooCommerce Retailer