Background

Recently, I tried to build a GPT-like chat agent for a specific e-commerce platform.

Not a generic chatbot. Not a demo.

A real product-facing agent that could answer questions like:

• "Where is my order?"
• "Can I get a refund?"
• "Show me my last purchase"

To do this, the agent had to access real production data — orders, shipments, payments.

That’s when I hit an important problem.

---

The Problem I Faced

As soon as an agent needs database access, a tempting idea appears:

“Why not just let the LLM generate SQL from the user’s question?”

It sounds simple. It works in demos.

But in a real e-commerce system, it quickly becomes dangerous.

---

Scenario

You are building a chat application for a specific e-commerce platform.

The chat agent supports:

• Order status inquiries
• Shipping & delivery tracking
• Refund eligibility checks
• Purchase history summaries
• Account-related questions

This is not a general-purpose GPT. It is a platform-bound assistant with access to sensitive user data.

---

The Core Problem

Chat agents often need database access.

A common but dangerous approach is:

1. Share database schema with the LLM
2. Ask the LLM to generate SQL from user input
3. Execute that SQL directly

This approach fails in production.

---

Why LLM-Generated SQL Is a Bad Idea

Security Risks

• Prompt injection can manipulate queries
• Over-broad SELECT * queries expose sensitive data
• Privilege escalation becomes possible

Hallucinated Queries

• Non-existent tables or columns
• Incorrect joins
• Wrong business logic

No Governance

• No clear contract of allowed queries
• Hard to audit data access
• Schema changes break prompts

---

Core Principle

LLMs should decide what intent to fulfill, not how to query data.

This is the same principle used in traditional backend systems.

---

The Right Architecture: Stored Procedures as Agent Tools

High-Level Flow

Key Design Rules

• Database logic lives in stored procedures
• LLM never sees table names or columns
• Agent only chooses which procedure to call

---

Step 1: Define Stored Procedures (Database Layer)

Get the Latest Order

CREATE PROCEDURE get_latest_order (
IN p_user_id BIGINT
)
BEGIN
SELECT
order_id,
order_status,
total_amount,
created_at
FROM orders
WHERE user_id = p_user_id
ORDER BY created_at DESC
LIMIT 1;
END;

✔ Enforces user-level data isolation ✔ No schema exposure to the agent

---

Get Shipping Status for an Order

CREATE PROCEDURE get_order_shipping_status (
IN p_order_id BIGINT,
IN p_user_id BIGINT
)
BEGIN
SELECT
shipping_provider,
tracking_number,
shipping_status,
estimated_delivery
FROM shipments
WHERE order_id = p_order_id
AND user_id = p_user_id;
END;

✔ Ownership validation at DB level ✔ Prevents cross-user access

---

Step 2: Expose Procedures as Agent Tools

The LLM never sees SQL.

It sees tools.

[
{
"name": "get_latest_order",
"description": "Get the most recent order for the authenticated user",
"parameters": {}
},
{
"name": "get_order_shipping_status",
"description": "Get shipping details for a specific order",
"parameters": {
"order_id": "number"
}
}
]

User identity is injected by the system — not the LLM.

---

Step 3: Agent Reasoning Flow

User Message

"Where is my last order?"

Agent Behavior

1. Identify intent: order inquiry
2. No order ID provided
3. Call get_latest_order
4. Use returned order_id
5. Call get_order_shipping_status

---

Step 4: User Response

Your latest order was placed on March 2.

Status: Shipped
Carrier: Yamato
Tracking Number: 1234-5678
Estimated Delivery: March 6

---

Advanced Example: Refund Eligibility

Stored Procedure

CREATE PROCEDURE check_refund_eligibility (
IN p_order_id BIGINT,
IN p_user_id BIGINT
)
BEGIN
SELECT
CASE
WHEN order_status = 'DELIVERED'
AND DATEDIFF(NOW(), delivered_at) <= 7
THEN 'ELIGIBLE'
ELSE 'NOT_ELIGIBLE'
END AS refund_status
FROM orders
WHERE order_id = p_order_id
AND user_id = p_user_id;
END;

Business rules stay out of prompts.

---

Why This Design Works

Security by Default

• No arbitrary SQL execution
• Strict access boundaries
• Minimal blast radius

No Hallucinations

• LLM cannot invent tables or joins
• Only allowed operations are callable

Strong Observability

Each call can be logged:

user_id=123
tool=check_refund_eligibility
order_id=88921
timestamp=...

---

Stored Procedures vs LLM-Generated SQL

Aspect	LLM SQL	Stored Procedures
Security	❌ Risky	✅ Safe
Hallucinations	❌ Common	✅ Impossible
Auditing	❌ Hard	✅ Easy
Schema changes	❌ Break prompts	✅ Isolated
Enterprise-ready	❌ No	✅ Yes

---

Final Thoughts

Chat agents feel new — but backend fundamentals still apply.

Treat your database like a private API.

Stored procedures provide:

• Safety
• Stability
• Observability
• Enterprise-grade control

This is how you build real, production-ready e-commerce chat agents — not demos.