Give Your E-Commerce App a Memory: Adding Agents That Actually Remember Your Customers
Have you ever tried shopping online and thought the app didn’t quite get you? You look at leather jackets every week, you told the chatbot you dislike polyester, and yet it keeps offering the same old generic suggestions. This is a common problem—most e-commerce apps treat each interaction as if it’s brand new.
Imagine if your app could remember? What if, when you said “I mentioned last week that I love leather jackets,” the app actually recalled that? That’s precisely what we’re developing—a smart shopping assistant powered by AI, which remembers your preferences thanks to Microsoft Agent Framework and SQL Server.
<p>Traditional chatbots in e-commerce face a major flaw—they forget everything as soon as the session ends. Here’s a quick example:</p>
<p><strong>Monday:</strong> > Customer: “I need a warm winter jacket, something in leather.”<br/>> Bot: “Absolutely! Here are some great leather jackets…”</p>
<p><strong>Wednesday:</strong> > Customer: “Show me more options like we discussed.”<br/>> Bot: “I’m sorry, can you remind me of what you wanted?”</p>
<p>The customer shared their preferences, invested time in the conversation, and then the app just... forgot. This isn’t merely frustrating—it’s a lost chance. Each preference shared by a customer is valuable data that could improve their experience in the future.</p>
<p>Essentially, our aim is straightforward:</p>
<ol>
<li><strong>Natural conversations</strong> — customers can chat freely about their likes and dislikes.</li>
<li><strong>Active memory</strong> — when they log off and return later, the app still knows they prefer leather to polyester.</li>
<li><strong>Intelligent recommendations</strong> — the app uses previous conversations to suggest products tailored to their tastes.</li>
</ol>
<p>The challenge lies not in creating a chatbot—that’s relatively easy these days—but in providing it with <em>lasting and scalable memory</em>.</p>
<p>Our setup comprises three key components: a FastAPI backend that serves a single-page app, conversational agents designed with the Microsoft Agent Framework, and SQL Server for lasting memory.</p>
<span class="lia-media-object lia-media-is-center lia-media-size-default" data-image-alt=""><button class="lia-media-unstyled-btn" type="button" aria-haspopup="true" aria-label="Enlarge Image"><img src="https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS00NTI0MDIxLWk0ZlVTZA?image-dimensions=999x187&revision=1" width="999" height="187" alt="Architecture diagram"/></button></span>
<p>Architecture diagram</p>
<p>The cornerstone of our system is the <strong>history provider</strong>. This component seamlessly integrates into the framework, managing the loading and saving of conversation history automatically. The agent doesn’t control its memory; instead, the framework handles it through this provider abstraction.</p>
<p>Microsoft Agent Framework is an open-source Python tool for developing AI agents. It acts as the link connecting your application logic with the large language model (LLM)—handling sessions, conversation history, and tool execution so you can focus on what the agent is designed to <em>achieve</em>.</p>
<p>Wondering why opt for this instead of creating your own solution?</p>
<ul>
<li><strong>Session management</strong> — includes built-in capabilities for tracking user sessions.</li>
<li><strong>Context providers</strong> — a clear setup for integrating history or user profiles before each LLM call.</li>
<li><strong>Provider pattern</strong> — switch out your storage backend (SQL Server, Cosmos DB, in-memory) without altering your agent code.</li>
<li><strong>Tool integration</strong> — set up functions the agent can call, with the framework managing the execution cycle.</li>
</ul>
<p>At its core, creating an agent looks like this:</p>
<pre class="lia-code-sample language-"><code>from agent_framework import Agentagent = Agent(
client=chat_client,
instructions=”You are a helpful assistant.”,
)
session = agent.create_session()
response = await agent.run(“Hello!”, session=session)
print(response)
<p>This configuration creates a stateless agent—no memory retained between calls. To add memory, we implement a context provider for loading and saving messages:</p>
<pre class="lia-code-sample language-"><code>from agent_framework import Agent, BaseHistoryProviderclass MyHistoryProvider(BaseHistoryProvider):
async def get_messages(self, session_id, **kwargs):
Load messages from your storage
return load_from_db(session_id)
async def save_messages(self, session_id, messages, **kwargs):
# Persist messages to your storage
save_to_db(session_id, messages)agent = Agent(
client=chat_client,
instructions=”You are a helpful assistant.”,
context_providers=[MyHistoryProvider()]
)
<p>The framework invokes get_messages() before each execution and save_messages() afterwards. Now your agent retains memory, without needing to manually coordinate loading/saving in every handler.</p>
<p>A database supports this memory provider. Why choose SQL Server over alternatives like PostgreSQL?</p>
<p>Both databases handle conversation history well, but SQL Server offers advantages for our specific needs:</p>
<div class="styles_lia-table-wrapper__h6Xo9 styles_table-responsive__MW0lN">
<table>
<thead>
<tr>
<td><p>Consideration</p></td>
<td><p>SQL Server</p></td>
<td><p>PostgreSQL</p></td>
</tr>
</thead>
<tbody>
<tr>
<td><strong>Local development</strong></td>
<td>One Docker command, minimal configuration</td>
<td>Needs pg_hba.conf and postgresql.conf setup</td>
</tr>
<tr>
<td><strong>Cloud transition</strong></td>
<td>Docker → Azure SQL Database, zero code changes</td>
<td>Cloud options differ, often requiring driver adjustments</td>
</tr>
<tr>
<td><strong>Managed scaling</strong></td>
<td>Azure SQL auto-scales and supports large databases</td>
<td>Scaling varies, adding complexity</td>
</tr>
<tr>
<td><strong>Free tier</strong></td>
<td>10 free databases per Azure subscription</td>
<td>Varies by provider</td>
</tr>
<tr>
<td><strong>Agent framework compatibility</strong></td>
<td>First-class integration with mssql_python driver</td>
<td>Requires custom driver integration</td>
</tr>
</tbody>
</table>
</div>
<p>In short, while PostgreSQL is a reliable database, SQL Server provides a <em>seamless continuum</em> from local development to a global managed service—same engine, queries, and connection driver. When you scale from prototype to production, you’ll change just a connection string without redesigning your architecture.</p>
<p>Now, let’s dive into how to set this up.</p>
<p>Getting SQL Server running locally is as simple as using one Docker command:</p>
<pre class="lia-code-sample language-"><code>docker run -d `–name sql -e "ACCEPT_EULA=Y"
-e “MSSQL_SA_PASSWORD=YourStrong!Passw0rd” -p 1433:1433
-v sqlvolume:/var/opt/mssql `
mcr.microsoft.com/mssql/server:2022-latest
<p>Next, we need local LLMs via Ollama—specifically Llama 3.1 for conversational quality and Phi-3 Mini for quick structured recommendations:</p>
<pre class="lia-code-sample language-"><code>foundry download llama3.1foundry download phi3:mini
<p>Then, let’s install our Python dependencies:</p>
<pre class="lia-code-sample language-"><code>cd commerce-agentuv sync
uv pip install fastapi uvicorn httpx
<p>The database schema is straightforward—Users, Sessions, and ChatHistory. ChatHistory is tied to a session, and sessions belong to users, ensuring each user has a unique conversation history.</p>
<pre class="lia-code-sample language-"><code>CREATE TABLE Users (
Id INT IDENTITY PRIMARY KEY,
Username NVARCHAR(100) UNIQUE NOT NULL,
DisplayName NVARCHAR(200) NOT NULL,
CreatedAt DATETIME2 DEFAULT GETUTCDATE())
CREATE TABLE Sessions (
Id NVARCHAR(100) PRIMARY KEY,
UserId INT NOT NULL FOREIGN KEY REFERENCES Users(Id),
CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
LastActiveAt DATETIME2 DEFAULT GETUTCDATE()
)
CREATE TABLE ChatHistory (
Id INT IDENTITY PRIMARY KEY,
SessionId NVARCHAR(100) NOT NULL FOREIGN KEY REFERENCES Sessions(Id),
Role NVARCHAR(50),
Content NVARCHAR(MAX),
CreatedAt DATETIME2 DEFAULT GETUTCDATE()
)
<p>Every message, from both the customer and the assistant, gets saved with a timestamp and role. When the agent needs context, it retrieves the full conversation history for that session.</p>
<p>Here's where Microsoft Agent Framework shines. It features the BaseHistoryProvider concept. By extending it and implementing get_messages() and save_messages(), the framework manages the rest, calling get_messages() before each agent run to pull in context, and save_messages() afterwards to store new messages:</p>
<pre class="lia-code-sample language-"><code>from agent_framework import BaseHistoryProvider, Messageclass CommerceHistoryProvider(BaseHistoryProvider):
def init(self, source_id: str = “commerce-history”):
super().init(source_id)
async def get_messages(
self, session_id: str | None, *, state: dict[str, Any] | None = None, **kwargs: Any
) -> list[Message]:
if not session_id:
return []
conn = get_conn()
cursor = conn.cursor()
cursor.execute("""
SELECT Role, Content FROM ChatHistory
WHERE SessionId = ?
ORDER BY CreatedAt
""", (session_id,))
rows = cursor.fetchall()
conn.close()
return [Message(role=role, text=content) for role, content in rows]
async def save_messages(
self,
session_id: str | None,
messages: Sequence[Message],
*,
state: dict[str, Any] | None = None,
**kwargs: Any,
) -> None:
if not session_id:
return
conn = get_conn()
cursor = conn.cursor()
for msg in messages:
text = msg.text or ""
if not text and msg.contents:
text = "".join(c.text for c in msg.contents if hasattr(c, "text"))
cursor.execute(
"INSERT INTO ChatHistory (SessionId, Role, Content) VALUES (?, ?, ?)",
(session_id, msg.role, text)
)
conn.commit()
conn.close()<p>That’s your memory layer set up! The framework calls these methods at appropriate times, so there's no need to manually handle loading or saving history in your routes.</p>
<p>Creating the agent becomes quite straightforward:</p>
<pre class="lia-code-sample language-"><code>from agent_framework import Agenthistory_provider = CommerceHistoryProvider()
chat_client = create_chat_client()
agent = Agent(
client=chat_client,
instructions=(
“You are a friendly shopping assistant. Help users discover products they’ll love. ”
“Ask about their interests and preferences. Remember their input. ”
“Be conversational and warm.”
),
context_providers=[history_provider]
)
<p>The key detail here is the context_providers parameter. By including our history provider, the agent automatically receives the user’s conversation history to provide context for generating responses. This eliminates the need for manual handling!</p>
<p>When a user sends a message, here’s what happens step-by-step:</p>
<pre class="lia-code-sample language-"><code>app.post("/api/chat")async def chat(req: ChatRequest):
user = get_user(req.username)
if not user:
raise HTTPException(status_code=401, detail=”Not logged in”)
session_id = get_or_create_session(user["id"])
session = agent.create_session(session_id=session_id)
response = await agent.run(req.message, session=session)
return {"response": str(response)}<p>Behind the scenes, we do the following: 1. Look up (or create) a session for this user. 2. The framework invokes get_messages() to load all prior conversation data. 3. The LLM gains access to the entire history plus the new message and produces a contextual response. 4. The framework calls save_messages() to save the new exchange.</p>
<p>So when the customer says, “I mentioned I like leather jackets,” the agent actually <em>remembers</em> because of the preserved history.</p>
<p>The real advantage arises when you merge memory with recommendations. Since we have full conversation history, we can match what the customer has said against our product selections:</p>
<pre class="lia-code-sample language-"><code>app.post("/api/recommendations")async def recommendations(req: RecommendationRequest):
user = get_user(req.username)
session_id = get_or_create_session(user[“id”])
history = get_session_history(session_id)
if not history:
all_prods = get_all_products()[:6]
return {"best_match": all_prods[0], "other": all_prods[1:]}
matched = score_products(history)
return {
"best_match": matched[0] if matched else None,
"other": matched[1:] if len(matched) > 1 else matched,
"message": f"Based on your preferences, {user['display_name']}!"
}<p>The score_products() function analyses conversation history, extracts preferences, and sorts products accordingly. If a customer states they adore outdoor gear but dislike synthetic materials, that feedback shapes the recommendations they receive.</p>
<p>Incorporating persistent memory in your e-commerce agent is more than just a tech challenge; it transforms customer relationships:</p>
<ul>
<li><strong>Customers feel acknowledged</strong> — no need to repeat themselves.</li>
<li><strong>Recommendations improve continuously</strong> — the more they chat, the better you know them.</li>
<li><strong>Sessions build upon each other</strong> — each visit adds to the last rather than starting over.</li>
</ul>
<p>The Microsoft Agent Framework simplifies this process. You set up a history provider, integrate it via context_providers, and let the framework manage the lifecycle. SQL Server provides robust, queryable storage. Plus, the clean provider interface makes cloud transitions easy.</p>
<p>We aimed to keep things simple — using Foundry Local for LLM, SQL Server through a Docker container, all running smoothly on your laptop. This is ideal for prototyping and testing the concept. But what about scaling up to serve real customers? Let's tackle that next!</p>
<p>The good part: because we’ve employed SQL Server locally, transitioning to production is seamless—no migrations are necessary.</p>
<p><a href="https://learn.microsoft.com/en-us/azure/azure-sql/database/" target="_blank" rel="noopener noreferrer">Azure SQL Database</a> is the managed version of what you’ve been executing in Docker. It boasts the same engine, T-SQL, and connection driver. Your CommerceHistoryProvider code remains unchanged—simply update the connection string.</p>
<p>Benefits of switching to Azure SQL Database include:</p>
<div class="styles_lia-table-wrapper__h6Xo9 styles_table-responsive__MW0lN">
<table>
<thead>
<tr>
<td><p>Feature</p></td>
<td><p>Importance for agents</p></td>
</tr>
</thead>
<tbody>
<tr>
<td><strong>Auto-scaling</strong></td>
<td><p>Conversation spikes during sales? The database automatically adjusts compute resources.</p></td>
</tr>
<tr>
<td><strong>10 free databases per subscription</strong></td>
<td><p>Experiment with individual databases for agents or environments without incurring costs during development.</p></td>
</tr>
<tr>
<td><strong>Built-in high availability</strong></td>
<td><p>99.99% SLA means your agent's memory stays intact even if a container crashes.</p></td>
</tr>
<tr>
<td><strong>Geo-replication</strong></td>
<td><p>Deliver fast access for users globally with read replicas nearby.</p></td>
</tr>
<tr>
<td><strong>Automatic backups</strong></td>
<td><p>Recover data from up to 35 days ago. Accidentally deleted the ChatHistory table? Just roll back!</p></td>
</tr>
</tbody>
</table>
</div>
<p>As your user base expands, so will the conversation history. One individual can accumulate thousands of messages over months. Multiply that by millions of users, and you’re facing substantial storage needs.</p>
<p><a href="https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale" target="_blank" rel="noopener noreferrer">Azure SQL Hyperscale</a> is tailored to handle this:</p>
<ul>
<li><strong>Up to 100 TB</strong> of storage means your conversation history can grow without needing annoying partition adjustments.</li>
<li><strong>No licensing fees</strong> — Hyperscale operates without licensing costs, charging only for compute and storage.</li>
<li><strong>Instant scaling</strong> — easily add read replicas for analytics needs (e.g., tracking trending preferences).</li>
<li><strong>Quick database snapshots</strong> — create copies of production without extensive wait times for restoration.</li>
</ul>
<p>This is how the transition will appear in code. Your local setup looks like this:</p>
<pre class="lia-code-sample language-"><code>DB_CONFIG = {
"server": "localhost",
"port": 1433,
"user": "sa",
"password": "YourStrong!Passw0rd",
"database": "agentdb"}
<p>For your production setup on Azure SQL, it shifts to:</p>
<pre class="lia-code-sample language-"><code>DB_CONFIG = {
"server": "your-agent-db.database.windows.net",
"port": 1433,
"user": "agent-app",
"password": os.environ["AZURE_SQL_PASSWORD"],
"database": "agentdb"}
<p>With the same schema and queries, alongside an unchanging CommerceHistoryProvider, the transition from a Docker container to a globally distributed managed database is seamless—no extra effort necessary; it just works, and faster!</p>
<p>Here’s a glimpse of Steve interacting with the assistant about outdoor gear, with Foundry chosen as the recommendation provider. Notice how the suggestions capture his preferences:</p>
<span class="lia-media-object lia-media-is-center lia-media-size-default" data-image-alt=""><button class="lia-media-unstyled-btn" type="button" aria-haspopup="true" aria-label="Enlarge Image"><img src="https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS00NTI0MDIxLVp0a05DWQ?image-dimensions=999x624&revision=1" width="999" height="624" alt="Steve interacting with shopping assistant"/></button></span>
<p>Next, here's Marla, a completely different user with her own unique tastes. The same app, the same agent—but her conversation history and recommendations are exclusively tailor-made for her:</p>
<span class="lia-media-object lia-media-is-center lia-media-size-default" data-image-alt=""><button class="lia-media-unstyled-btn" type="button" aria-haspopup="true" aria-label="Enlarge Image"><img src="https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS00NTI0MDIxLTdHSlM0bg?image-dimensions=999x624&revision=1" width="999" height="624" alt="Marla interacting with shopping assistant"/></button></span>
<p>Each user enjoys their own isolated conversation history. The agent accurately recalls what <em>they</em> have said, distinct from others. That’s the true power of session-based memory backed by SQL Server.</p>
<p>Here’s how to get started:</p>
<ol>
<li>Clone the repository: <a class="lia-external-url" href="https://github.com/softchris/ecommerce-agent-memory" target="_blank" rel="noopener noreferrer">https://github.com/softchris/ecommerce-agent-memory</a></li>
<li>Install dependencies (don’t forget to check the prerequisites in the README): <code>uv sync</code></li>
<li>Run the application: <code>uv run uvicorn app:app --reload --port 8000</code></li>
<li>Open your browser and visit <strong>http://localhost:8000</strong>. Log in as Marla or Steve, start chatting! Share your likes. Log out, come back, and ask for recommendations. The agent will remember you.</li>
</ol>
<p>This illustrates the difference between a simple chatbot and a genuine assistant that understands your customers.</p>
<p>Ready to create your own memory-enabled agent? Here are some resources to explore next:</p>
<ul>
<li>📖 <a href="https://learn.microsoft.com/en-us/agent-framework/" target="_blank" rel="noopener noreferrer"><strong>Microsoft Agent Framework Documentation</strong></a> — essential reading to grasp agents, context providers, sessions, tool usage, and more.</li>
<li>🧪 <a href="https://github.com/microsoft/Foundry-Local/tree/main/samples/python" target="_blank" rel="noopener noreferrer"><strong>Foundry Local Python Samples</strong></a> — hands-on examples to help you get set up quickly without worrying about cloud dependencies.</li>
<li>🛍 <a href="https://github.com/softchris/ecommerce-agent-memory" target="_blank" rel="noopener noreferrer"><strong>Source code for this project</strong></a> — full e-commerce agent solution with persistent SQL Server memory. Clone it, run it, and modify it for your needs.</li>
</ul>Share this content:
Discover more from Qureshi
Subscribe to get the latest posts sent to your email.
