samurai-techlead

Posted on Jan 6

Building a Secure E-Commerce Chat Agent with Stored Procedures

#ai #agents #sql #llm

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:

Share database schema with the LLM
Ask the LLM to generate SQL from user input
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

Identify intent: order inquiry
No order ID provided
Call get_latest_order
Use returned order_id
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.

DEV Community