Shopkeeper Agent: Full-Stack LangGraph NL2SQL Platform for E-Commerce Data

The Problem: Why NL2SQL Needs More Than a Prompt

Sending a natural-language question directly to a language model and asking it to write SQL often fails in real enterprise settings. The model does not know your actual table names, field names, or the specific values stored in categorical columns. It guesses, and those guesses produce queries that reference the wrong table, pick the wrong column, or use a value that is not in the data.

Shopkeeper Agent addresses this by building a metadata knowledge base first. Before any query runs, the project extracts the warehouse's table definitions, field names, indicator definitions, and the actual values stored in dimension columns. These are written into three stores: Qdrant holds vector embeddings of fields and indicators for semantic recall, Elasticsearch holds the actual field values for keyword and range retrieval, and MySQL holds the complete structured metadata as the authoritative source.

At query time, the agent retrieves related fields, indicators, and values from all three stores, assembles that context, and then asks the language model to write SQL grounded in real table structure. This multi-stage approach reduces hallucinated column names and wrong indicator interpretations.

Architecture: Two Pipelines and Five Technology Layers

The project is organized around two main workflows.

The first is metadata knowledge base construction. A script reads the teaching data warehouse in MySQL, extracts all table definitions, field metadata, indicator definitions, and field values, then writes them to Qdrant (vectors), Elasticsearch (full-text index), and MySQL (structured store). The BAAI/bge-large-zh-v1.5 embedding model, served through Hugging Face's TEI container, converts text to vectors.

The second is the question-answering pipeline. A user sends a natural-language question to the FastAPI backend. LangGraph orchestrates a multi-stage workflow: it queries all three stores to retrieve relevant fields, indicators, and values; assembles the context into a structured prompt; calls the language model to generate SQL; validates the SQL; and executes it against the data warehouse. Results stream back to the React frontend over SSE.

The technology stack is explicit in the README: MySQL and SQLAlchemy for the warehouse and metadata store, Qdrant for vector search, Elasticsearch for full-text field value search, TEI for embedding, LangGraph for agent orchestration, LangChain for LLM and embedding abstraction, FastAPI for the backend API, and React with Vite and Tailwind CSS for the frontend.

Getting the Stack Running Locally

The setup sequence has several steps. Start with the repository clone and backend dependency installation:

git clone https://github.com/didilili/shopkeeper-agent.git
cd shopkeeper-agent
uv sync

Copy the environment template and add your LLM API key:

cp .env.example .env

The .env file requires one variable:

LLM_API_KEY=your_real_api_key

The default configuration in conf/app_config.yaml uses Silicon Flow's OpenAI-compatible API with GLM-5.1. Any OpenAI-compatible endpoint works by changing model_name and base_url in that file.

Download the embedding model before starting the Docker services:

uv run hf download BAAI/bge-large-zh-v1.5 --local-dir docker/embedding/bge-large-zh-v1.5

Then start the full infrastructure:

docker compose -f docker/docker-compose.yaml up -d

This brings up MySQL on port 3306, Elasticsearch on 9200, Kibana on 5601, Qdrant on 6333, and the embedding service on 8081. The MySQL init scripts run automatically on first startup.

Building the Metadata Knowledge Base and Running the Backend

After the Docker services are up, build the metadata knowledge base by running the construction script:

uv run python -m app.scripts.build_meta_knowledge -c conf/meta_config.yaml

This writes field metadata to MySQL, vectorizes fields and indicators into Qdrant, and indexes field values into Elasticsearch. The README notes that this step is the foundation for question quality: missing or incomplete metadata means the agent retrieves less relevant context and produces worse SQL.

Start the FastAPI backend with:

uv run fastapi dev main.py

The query endpoint accepts POST requests at http://127.0.0.1:8000/api/query. The default LLM connection is configured in conf/app_config.yaml:

llm:
    model_name: Pro/zai-org/GLM-5.1
    api_key: ${oc.env:LLM_API_KEY}
    base_url: https://api.siliconflow.cn/v1

SSE responses stream three event types: progress (node execution updates), result (final query output), and error (any global exception during processing). To switch to a different OpenAI-compatible provider, update model_name and base_url in that file.

Frontend Setup and the Teaching Curriculum

The React frontend lives in the frontend/ directory and uses Vite with pnpm:

cd frontend
pnpm install
pnpm dev

Vite proxies /api requests to the FastAPI backend at http://127.0.0.1:8000. If the backend runs on a different address, copy frontend/.env.example to frontend/.env and set VITE_DEV_PROXY_TARGET to the correct URL.

The project is the companion code repository for the ai-agents-from-zero tutorial series at didilili.github.io. The README describes chapter-aligned Git branches so learners can follow the construction of the system step by step, starting from the data warehouse basics and advancing through metadata indexing, LangGraph orchestration, and full-stack integration. The online tutorial is free and in Chinese.

Limitations: Metadata Dependency and Python Version

The quality of SQL generation depends entirely on the completeness of the metadata knowledge base. If your warehouse has poorly documented field names, missing indicator definitions, or sparse field value coverage in Elasticsearch, the hybrid retrieval returns weaker context and the generated SQL will be less accurate. The system is only as good as the metadata you put into it.

The project requires Python 3.14 or later. At the time of the last push, this is an unusually recent requirement that rules out environments still on Python 3.11 or 3.12. The pyproject.toml confirms this constraint.

The last push was on 2026-05-18. The README describes the project as complete with all tutorial chapters and chapter branches in place, but active feature development appears to have paused.

Shopkeeper Agent Compared to a Simpler Text-to-SQL Approach

A simpler alternative is to use an LLM's text-to-SQL capability directly: provide the table schema in the system prompt and ask the model to write the query. Tools like Vanna.ai take this approach, storing schema and sample queries in a vector database and retrieving them at query time.

Shopkeeper Agent is more complex because it treats field values, indicators, and table fields as separate retrieval targets. This adds setup overhead, a longer infrastructure dependency list, and a metadata build step that Vanna-style systems skip. The trade-off is richer context: knowing not just the table structure but also which specific values appear in categorical columns reduces the chance the model invents a value that does not exist in the data. For a teaching warehouse with known data, the direct approach is faster to set up. For a production warehouse with hundreds of tables and thousands of distinct values, the structured metadata approach is more defensible.

Editorial conclusion

Shopkeeper Agent is a good project for developers who want a concrete, end-to-end LangGraph example that goes beyond toy prompts: it includes a runnable stack, real hybrid retrieval, streaming results, and a React frontend. It requires Python 3.14 or later, Docker, and a compatible LLM API key. It is not a production-ready product out of the box: the metadata knowledge base must be built for your own warehouse schema, and SQL generation quality depends on the completeness of that metadata. Confirm that your data warehouse schema and field values are fully indexed before testing the question-answering quality. The last push was on 2026-05-18.

Frequently asked questions

Official sources

1. didilili/shopkeeper-agent on GitHub
2. Issues
3. License: MIT
4. Project website
5. README