Content
# TechShop AI Chatbot
A Text-to-SQL chatbot that understands natural language questions about store data — products, sales, revenue, customers — and answers them using live database queries. No SQL knowledge required.
Built with Next.js 16 · Spring Boot 3.5 · Spring AI 1.0 · GPT-4o · MCP Toolbox · PostgreSQL 16 · Docker Compose.
---
## Table of Contents
- [Architecture](#architecture)
- [Database Design](#database-design)
- [How a Query Flows](#how-a-query-flows)
- [Services & Ports](#services--ports)
- [Credentials](#credentials)
- [Getting Started](#getting-started)
- [Stopping the Stack](#stopping-the-stack)
- [Environment Variables](#environment-variables)
- [Project Structure](#project-structure)
- [Things to Try](#things-to-try)
- [Local Development](#local-development)
- [Security](#security)
---
## Architecture
```mermaid
graph TD
subgraph Browser
UI["Next.js 16 Chat UI<br/>localhost:3000"]
end
subgraph Docker Network
FE["Next.js Server<br/>(API Proxy Route)<br/>:3000"]
BE["Spring Boot 3.5<br/>+ Spring AI 1.0<br/>:8080"]
MCP["Google MCP Toolbox<br/>SSE endpoint<br/>:5000"]
PG[("PostgreSQL 16<br/>:5432")]
end
subgraph External
GPT["OpenAI API<br/>GPT-4o"]
end
UI -->|HTTP| FE
FE -->|/api/** proxy| BE
BE -->|Chat completions| GPT
BE -->|SSE / tool calls| MCP
MCP -->|SQL – read-only role| PG
BE -->|JPA – chat history| PG
```
---
## Database Design
### store_db — Business Data
```mermaid
erDiagram
category {
serial id PK
varchar name
text description
}
product {
serial id PK
varchar name
text description
varchar sku UK
int category_id FK
boolean is_active
timestamptz created_at
}
price {
serial id PK
int product_id FK
numeric amount
char currency
date effective_from
date effective_to "NULL = current price"
}
customer {
serial id PK
varchar first_name
varchar last_name
varchar email UK
varchar phone
varchar city
varchar country
timestamptz created_at
}
sale {
serial id PK
int customer_id FK
varchar status "completed | cancelled"
numeric total_amount
timestamptz created_at
}
sale_item {
serial id PK
int sale_id FK
int product_id FK
int quantity
numeric unit_price "snapshot at purchase time"
}
category ||--o{ product : "has"
product ||--o{ price : "priced via"
product ||--o{ sale_item : "appears in"
customer ||--o{ sale : "places"
sale ||--o{ sale_item : "contains"
```
Two things worth noting: `effective_to IS NULL` means that's the current active price, and revenue queries should always filter `sale.status = 'completed'` to exclude cancelled orders.
### chat_db — Conversation History
```mermaid
erDiagram
conversation {
bigserial id PK
varchar title
timestamptz created_at
timestamptz updated_at
}
chat_message {
bigserial id PK
bigint conversation_id FK
varchar role "user | assistant"
text content
timestamptz created_at
}
conversation ||--o{ chat_message : "contains"
```
---
## How a Query Flows
```mermaid
sequenceDiagram
actor User
participant UI as Next.js UI
participant BE as Spring Boot
participant GPT as GPT-4o
participant MCP as MCP Toolbox
participant DB as PostgreSQL (store_db)
User->>UI: "What are the top 3 products by revenue?"
UI->>BE: POST /api/chat { conversationId, message }
BE->>BE: Load chat history from chat_db
BE->>GPT: System prompt + history + user message
GPT->>BE: Tool call → get-database-schema
BE->>MCP: get-database-schema
MCP->>DB: SELECT from information_schema + pg_description
DB-->>MCP: Full schema with COMMENT metadata
MCP-->>BE: Schema result
BE-->>GPT: Schema result
GPT->>BE: Tool call → execute-sql (SELECT ...)
BE->>MCP: execute-sql
MCP->>DB: SELECT (read-only role — DDL/DML blocked at DB level)
DB-->>MCP: Query result
MCP-->>BE: Result rows
BE-->>GPT: Result rows
GPT-->>BE: Natural-language answer (Markdown)
BE->>BE: Persist user + assistant messages in chat_db
BE-->>UI: { reply, conversationId }
UI-->>User: Rendered Markdown response
```
---
## Services & Ports
| Service | URL | Port |
|---------|-----|------|
| Chat UI | [http://localhost:3000](http://localhost:3000) | 3000 |
| Backend API | [http://localhost:8080/api/conversations](http://localhost:8080/api/conversations) | 8080 |
| MCP Toolbox SSE | [http://localhost:5000/mcp/store-tools/sse](http://localhost:5000/mcp/store-tools/sse) | 5000 |
| PostgreSQL | `localhost:5432` | 5432 |
All four ports are mapped to the host, so you can connect with any DB client or hit the REST API directly.
---
## Credentials
Default values from `.env.example` — fine for local dev, change before sharing or deploying.
**Admin user** (Spring Boot + provisioning)
| | |
|-|-|
| Host | `localhost:5432` |
| User | `appuser` |
| Password | `apppassword` |
| Databases | `store_db`, `chat_db` |
**Read-only user** (MCP Toolbox only)
| | |
|-|-|
| Host | `localhost:5432` |
| User | `mcp_readonly` |
| Password | `mcp_readonly_pass` |
| Database | `store_db` |
| Permissions | SELECT only — writes are blocked at the DB level |
**OpenAI** — you need to supply your own API key (`sk-…`). Model is `gpt-4o`.
To connect with DBeaver, TablePlus, or similar:
```
Host: localhost
Port: 5432
Database: store_db
User: appuser
Password: apppassword
SSL: disable
```
---
## Getting Started
You only need Docker Desktop and an OpenAI API key. No Java or Node.js required locally.
```bash
git clone https://github.com/<your-username>/db-agent.git
cd db-agent
cp .env.example .env
# open .env and set OPENAI_API_KEY=sk-your-key-here
docker compose up --build
```
First build takes 3–5 minutes while Maven and npm download dependencies. After that, cached layers make restarts fast.
Open [http://localhost:3000](http://localhost:3000) and start asking questions.
To run in the background: `docker compose up --build -d`
Check status: `docker compose ps`
Follow logs for a specific service:
```bash
docker compose logs -f backend
docker compose logs -f frontend
docker compose logs -f mcp-toolbox
docker compose logs -f postgres
```
---
## Stopping the Stack
```bash
# Stop containers, keep database data
docker compose down
# Stop containers and delete all data (seed data re-inserts on next start)
docker compose down -v
# Restart a single service after a code change
docker compose up --build backend
```
---
## Environment Variables
| Variable | Default | Description |
|----------|---------|-------------|
| `POSTGRES_USER` | `appuser` | Admin PostgreSQL user |
| `POSTGRES_PASSWORD` | `apppassword` | Admin password |
| `POSTGRES_STORE_DB` | `store_db` | Business data database |
| `POSTGRES_CHAT_DB` | `chat_db` | Chat history database |
| `MCP_READONLY_USER` | `mcp_readonly` | Read-only role for MCP Toolbox |
| `MCP_READONLY_PASSWORD` | `mcp_readonly_pass` | Read-only role password |
| `OPENAI_API_KEY` | *(required)* | Your OpenAI API key |
| `BACKEND_PORT` | `8080` | Host port for Spring Boot |
| `FRONTEND_CORS_ORIGIN` | `http://localhost:3000` | Allowed CORS origin |
| `BACKEND_URL` | `http://backend:8080` | Backend URL used inside the frontend container |
---
## Project Structure
```
db-agent/
├── .env.example
├── docker-compose.yml
│
├── database/
│ ├── store_db/
│ │ └── init.sql # DDL + seed data + mcp_readonly role + COMMENT ON metadata
│ └── chat_db/
│ └── init.sql # conversation + chat_message tables
│
├── mcp-toolbox/
│ └── tools.yaml # MCP source + 3 tools + toolset definition
│
├── backend/
│ └── agent-store-chat/ # Spring Boot 3.5 / Spring AI 1.0
│ ├── Dockerfile
│ ├── pom.xml
│ └── src/main/
│ ├── resources/application.yml
│ └── java/com/example/storechat/
│ ├── StoreChatApplication.java
│ ├── config/AiConfig.java
│ ├── entity/
│ ├── repository/
│ ├── dto/
│ ├── service/
│ │ ├── AgentService.java # AI loop + system prompt
│ │ └── ChatHistoryService.java
│ └── controller/
│ ├── ChatController.java
│ ├── ConversationController.java
│ └── GlobalExceptionHandler.java
│
└── frontend/
└── chat-ui/ # Next.js 16, TypeScript, Tailwind CSS
├── Dockerfile
├── package.json
└── src/
├── app/
│ ├── api/[...path]/route.ts # Proxy → Spring Boot
│ ├── chat/[conversationId]/page.tsx
│ ├── layout.tsx
│ └── page.tsx
├── components/
│ ├── sidebar/Sidebar.tsx
│ └── chat/
│ ├── ChatWindow.tsx
│ ├── MessageBubble.tsx
│ └── ChatInput.tsx
├── lib/
│ ├── api.ts
│ └── utils.ts
└── types/index.ts
```
---
## Things to Try
- "What are the top 3 best-selling products by revenue?"
- "How much total revenue did TechShop earn in Q4 2024?"
- "Which customer has spent the most money overall?"
- "What is the current price of the UltraBook Pro 15?"
- "How many units of each Electronics product were sold?"
- "List all cancelled orders with the customer name and amount."
- "Who are our top 5 customers by number of orders?"
---
## Local Development
If you want to iterate without rebuilding Docker images:
**Backend** (requires Java 21 + Maven)
```bash
cd backend/agent-store-chat
export SPRING_DATASOURCE_URL=jdbc:postgresql://localhost:5432/chat_db
export SPRING_DATASOURCE_USERNAME=appuser
export SPRING_DATASOURCE_PASSWORD=apppassword
export SPRING_AI_OPENAI_API_KEY=sk-...
export MCP_TOOLBOX_URL=http://localhost:5000
mvn spring-boot:run
```
**Frontend** (requires Node.js 20+)
```bash
cd frontend/chat-ui
echo "BACKEND_URL=http://localhost:8080" > .env.local
npm install
npm run dev
```
---
## Security
The LLM cannot write to or delete data. Three independent layers enforce this:
```
Layer 1 — System Prompt
└─ LLM is instructed to write SELECT-only statements
Layer 2 — MCP Tool Description
└─ execute-sql explicitly lists forbidden operations
(INSERT, UPDATE, DELETE, TRUNCATE, DROP, ALTER, CREATE, GRANT, REVOKE)
Layer 3 — PostgreSQL Role
└─ mcp_readonly has CONNECT + SELECT only
Any write or DDL is rejected at the database level
regardless of what the LLM generates
```
---
MIT License
MCP Config
Below is the configuration for this MCP Server. You can copy it directly to Cursor or other MCP clients.
mcp.json
Connection Info
You Might Also Like
No recommendations available