Building a Secure E-Commerce Chat Agent with Stored Procedures

Source: Dev.to

Background

Recently, I tried to build a GPT‑like chat agent for a specific e‑commerce platform – not a generic chatbot, not a demo, but a real product‑facing assistant 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 and 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 unsafe approach is:

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

In production this approach fails.

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 mirrors traditional backend design.

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 by 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 → call get_latest_order.
3. Use the returned order_id to 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, e.g.:

user_id=123
tool=check_refund_eligibility
order_id=88921
timestamp=2026-01-06T12:34:56Z

Stored Procedures vs. LLM‑Generated SQL

Final Thoughts

Chat agents feel new, but backend fundamentals still apply.

Treat your database like a private API.

Stored procedures give you:

• Safety
• Stability
• Observability
• Enterprise‑grade control

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