# MCP Server And PostgreSQL Sample Retail

> A Model Context Protocol (MCP) server that provides comprehensive customer sales database access for Zava Retail DIY Business. This server enables AI assistants to query and analyze retail sales data through a secure, schema-aware interface.

- **Type:** MCP server
- **Install:** `agentstack add mcp-microsoft-mcp-server-and-postgresql-sample-retail`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [microsoft](https://agentstack.voostack.com/s/microsoft)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [microsoft](https://github.com/microsoft)
- **Source:** https://github.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail

## Install

```sh
agentstack add mcp-microsoft-mcp-server-and-postgresql-sample-retail
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# MCP Server and PostgreSQL Sample - Retail Sales Analysis

## Learn MCP with Database Integration through Hands-on Examples

[](https://GitHub.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail/graphs/contributors)
[](https://GitHub.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail/issues)
[](https://GitHub.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail/pulls)
[](http://makeapullrequest.com)

[](https://discord.com/invite/ByRwuEEgH4)

Follow these steps to get started using these resources:

1. **Fork the Repository**: Click [here to fork](https://github.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail/fork)
2. **Clone the Repository**: `git clone https://github.com/YOUR-USERNAME/MCP-Server-and-PostgreSQL-Sample-Retail.git`
3. **Join The Azure AI Foundry Discord**: [Meet experts and fellow developers](https://discord.com/invite/ByRwuEEgH4)

### 🌐 Multi-Language Support

#### Supported via GitHub Action (Automated & Always Up-to-Date)

[French](./translations/fr/README.md) | [Spanish](./translations/es/README.md) | [German](./translations/de/README.md) | [Russian](./translations/ru/README.md) | [Arabic](./translations/ar/README.md) | [Persian (Farsi)](./translations/fa/README.md) | [Urdu](./translations/ur/README.md) | [Chinese (Simplified)](./translations/zh/README.md) | [Chinese (Traditional, Macau)](./translations/mo/README.md) | [Chinese (Traditional, Hong Kong)](./translations/hk/README.md) | [Chinese (Traditional, Taiwan)](./translations/tw/README.md) | [Japanese](./translations/ja/README.md) | [Korean](./translations/ko/README.md) | [Hindi](./translations/hi/README.md) | [Bengali](./translations/bn/README.md) | [Marathi](./translations/mr/README.md) | [Nepali](./translations/ne/README.md) | [Punjabi (Gurmukhi)](./translations/pa/README.md) | [Portuguese (Portugal)](./translations/pt/README.md) | [Portuguese (Brazil)](./translations/br/README.md) | [Italian](./translations/it/README.md) | [Polish](./translations/pl/README.md) | [Turkish](./translations/tr/README.md) | [Greek](./translations/el/README.md) | [Thai](./translations/th/README.md) | [Swedish](./translations/sv/README.md) | [Danish](./translations/da/README.md) | [Norwegian](./translations/no/README.md) | [Finnish](./translations/fi/README.md) | [Dutch](./translations/nl/README.md) | [Hebrew](./translations/he/README.md) | [Vietnamese](./translations/vi/README.md) | [Indonesian](./translations/id/README.md) | [Malay](./translations/ms/README.md) | [Tagalog (Filipino)](./translations/tl/README.md) | [Swahili](./translations/sw/README.md) | [Hungarian](./translations/hu/README.md) | [Czech](./translations/cs/README.md) | [Slovak](./translations/sk/README.md) | [Romanian](./translations/ro/README.md) | [Bulgarian](./translations/bg/README.md) | [Serbian (Cyrillic)](./translations/sr/README.md) | [Croatian](./translations/hr/README.md) | [Slovenian](./translations/sl/README.md) | [Ukrainian](./translations/uk/README.md) | [Burmese (Myanmar)](./translations/my/README.md)

**If you wish to have additional translations languages supported are listed [here](https://github.com/Azure/co-op-translator/blob/main/getting_started/supported-languages.md)**

## Introduction

This sample demonstrates how to build and deploy a comprehensive **Model Context Protocol (MCP) server** that provides AI assistants with secure, intelligent access to retail sales data through PostgreSQL. The project showcases enterprise-grade features including **Row Level Security (RLS)**, **semantic search capabilities**, and **Azure AI integration** for real-world retail analytics scenarios.

**Key Use Cases:**
- **AI-Powered Sales Analytics**: Enable AI assistants to query and analyze retail sales data through natural language
- **Secure Multi-Tenant Access**: Demonstrate Row Level Security implementation where different store managers can only access their store's data
- **Semantic Product Search**: Showcase AI-enhanced product discovery using text embeddings
- **Enterprise Integration**: Illustrate how to integrate MCP servers with Azure services and PostgreSQL databases

**Perfect for:**
- Developers learning to build MCP servers with database integration
- Data engineers implementing secure multi-tenant analytics solutions
- AI application developers working with retail or e-commerce data
- Anyone interested in combining AI assistants with enterprise databases

## Join the Azure AI Foundry Discord Community
Share your experiences of MCP and meet the experts and product groups 

[](https://discord.com/invite/ByRwuEEgH4)

# Sales Analysis MCP Server

A Model Context Protocol (MCP) server that provides comprehensive customer sales database access for Zava Retail DIY Business. This server enables AI assistants to query and analyze retail sales data through a secure, schema-aware interface.

## 📚 Complete Implementation Guide

For a detailed breakdown of how this solution is built and how to implement similar MCP servers, see our comprehensive **[Sample Walkthrough](Sample_Walkthrough.md)**. This guide provides:

- **Architecture Deep Dive**: Component analysis and design patterns
- **Step-by-Step Building**: From project setup to deployment
- **Code Breakdown**: Detailed explanation of MCP server implementation
- **Advanced Features**: Row Level Security, semantic search, and monitoring
- **Best Practices**: Security, performance, and development guidelines
- **Troubleshooting**: Common issues and solutions

Perfect for developers who want to understand the implementation details and build similar solutions.

## 🤖 What is MCP (Model Context Protocol)?

**Model Context Protocol (MCP)** is an open standard that enables AI assistants to securely access external data sources and tools in real-time. Think of it as a bridge that allows AI models to connect with databases, APIs, file systems, and other resources while maintaining security and control.

### Key Benefits:
- **Real-time Data Access**: AI assistants can query live databases and APIs
- **Secure Integration**: Controlled access with authentication and permissions  
- **Tool Extensibility**: Add custom capabilities to AI assistants
- **Standardized Protocol**: Works across different AI platforms and tools

### New to MCP?

If you're new to Model Context Protocol, we recommend starting with Microsoft's comprehensive beginner resources:

**📖 [MCP for Beginners Guide](https://aka.ms/mcp-for-beginners)**

This resource provides:
- Introduction to MCP concepts and architecture
- Step-by-step tutorials for building your first MCP server
- Best practices for MCP development
- Integration examples with popular AI platforms
- Community resources and support

Once you understand the basics, return here to explore this advanced retail analytics implementation!

## 📚 Comprehensive Learning Guide: /walkthrough

This repository includes a complete **12-module learning walkthrough** that deconstructs this MCP retail server sample into digestible, step-by-step lessons. The walkthrough transforms this working example into a comprehensive educational resource perfect for developers who want to understand how to build production-ready MCP servers with database integration.

### What You'll Learn

The walkthrough covers everything from basic MCP concepts to advanced production deployment, including:

- **MCP Fundamentals**: Understanding the Model Context Protocol and its real-world applications
- **Database Integration**: Implementing secure PostgreSQL connectivity with Row Level Security
- **AI-Enhanced Features**: Adding semantic search capabilities with Azure OpenAI embeddings
- **Security Implementation**: Enterprise-grade authentication, authorization, and data isolation
- **Tool Development**: Building sophisticated MCP tools for data analysis and business intelligence
- **Testing & Debugging**: Comprehensive testing strategies and debugging techniques
- **VS Code Integration**: Configuring AI Chat for natural language database queries
- **Production Deployment**: Containerization, scaling, and cloud deployment strategies
- **Monitoring & Observability**: Application Insights, logging, and performance monitoring

### Learning Path Overview

The walkthrough follows a progressive learning structure designed for developers of all skill levels:

| Module | Focus Area | Description | Time Estimate |
|--------|------------|-------------|---------------|
| **[00-Introduction](walkthrough/00-Introduction/README.md)** | Foundation | MCP concepts, Zava Retail case study, architecture overview | 30 minutes |
| **[01-Architecture](walkthrough/01-Architecture/README.md)** | Design Patterns | Technical architecture, layered design, system components | 45 minutes |
| **[02-Security](walkthrough/02-Security/README.md)** | Enterprise Security | Azure authentication, Row Level Security, multi-tenant isolation | 60 minutes |
| **[03-Setup](walkthrough/03-Setup/README.md)** | Environment | Docker setup, Azure CLI, project configuration, validation | 45 minutes |
| **[04-Database](walkthrough/04-Database/README.md)** | Data Layer | PostgreSQL schema, pgvector, RLS policies, sample data | 60 minutes |
| **[05-MCP-Server](walkthrough/05-MCP-Server/README.md)** | Core Implementation | FastMCP framework, database integration, connection management | 90 minutes |
| **[06-Tools](walkthrough/06-Tools/README.md)** | Tool Development | MCP tool creation, query validation, business intelligence features | 75 minutes |
| **[07-Semantic-Search](walkthrough/07-Semantic-Search/README.md)** | AI Integration | Azure OpenAI embeddings, vector search, hybrid search strategies | 60 minutes |
| **[08-Testing](walkthrough/08-Testing/README.md)** | Quality Assurance | Unit testing, integration testing, performance testing, debugging | 75 minutes |
| **[09-VS-Code](walkthrough/09-VS-Code/README.md)** | Development Experience | VS Code configuration, AI Chat integration, debugging workflows | 45 minutes |
| **[10-Deployment](walkthrough/10-Deployment/README.md)** | Production Ready | Containerization, Azure Container Apps, CI/CD pipelines, scaling | 90 minutes |
| **[11-Monitoring](walkthrough/11-Monitoring/README.md)** | Observability | Application Insights, structured logging, performance metrics | 60 minutes |
| **[12-Best-Practices](walkthrough/12-Best-Practices/README.md)** | Production Excellence | Security hardening, performance optimization, enterprise patterns | 45 minutes |

**Total Learning Time**: ~12-15 hours of comprehensive hands-on learning

### 🎯 How to Use the Walkthrough

**For Beginners**:
1. Start with [Module 00: Introduction](walkthrough/00-Introduction/README.md) to understand MCP fundamentals
2. Follow the modules sequentially for a complete learning experience
3. Each module builds on previous concepts and includes practical exercises

**For Experienced Developers**:
1. Review the [Main Walkthrough Overview](walkthrough/README.md) for a complete module summary
2. Jump to specific modules that interest you (e.g., Module 07 for AI integration)
3. Use individual modules as reference material for your own projects

**For Production Implementation**:
1. Focus on Modules 02 (Security), 10 (Deployment), and 11 (Monitoring)
2. Review Module 12 (Best Practices) for enterprise guidelines
3. Use the code examples as production-ready templates

### 🚀 Quick Start Options

**Option 1: Complete Learning Path** (Recommended for newcomers)
```bash
# Clone and start with the introduction
git clone https://github.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail.git
cd MCP-Server-and-PostgreSQL-Sample-Retail/walkthrough
# Follow along starting with 00-Introduction/README.md
```

**Option 2: Hands-On Implementation** (Jump right into building)
```bash
# Start with setup and build as you learn
cd walkthrough/03-Setup
# Follow the setup guide and continue through implementation modules
```

**Option 3: Production Focus** (Enterprise deployment)
```bash
# Focus on production-ready aspects
# Review modules: 02-Security, 10-Deployment, 11-Monitoring, 12-Best-Practices
```

### 📋 Learning Prerequisites

**Recommended Background**:
- Basic Python programming experience
- Familiarity with REST APIs and databases
- General understanding of AI/ML concepts
- Basic command-line and Docker knowledge

**Not Required (but helpful)**:
- Prior MCP experience (we cover this from scratch)
- Azure cloud experience (we provide step-by-step guidance)
- Advanced PostgreSQL knowledge (we explain concepts as needed)

### 💡 Learning Tips

1. **Hands-On Approach**: Each module includes working code examples you can run and modify
2. **Progressive Complexity**: Concepts build gradually from simple to advanced
3. **Real-World Context**: All examples use realistic retail business scenarios
4. **Production Ready**: Code examples are designed for actual production use
5. **Community Support**: Join our [Discord community](https://discord.com/invite/ByRwuEEgH4) for help and discussions

### 🔗 Related Resources

- **[MCP for Beginners](https://aka.ms/mcp-for-beginners)**: Essential background reading
- **[Sample Walkthrough](Sample_Walkthrough.md)**: High-level technical overview
- **[Azure AI Foundry](https://azure.microsoft.com/en-us/products/ai-foundry)**: Cloud platform used in examples
- **[FastMCP Framework](https://github.com/jlowin/fastmcp)**: Python MCP implementation framework

**Ready to start learning?** Begin with **[Module 00: Introduction](walkthrough/00-Introduction/README.md)** or explore the **[complete walkthrough overview](walkthrough/README.md)**.

## Prerequisites

1. Docker Desktop installed
2. Git installed
3. **Azure CLI**: Install and authenticate with Azure CLI
4. Access to OpenAI `text-embedding-3-small` model and optionally `gpt-4o-mini` model.

## Getting Started

Open a terminal window and running the following commands:

1. Authenticate with Azure CLI

    ```bash
    az login
    ```

2. Clone the repository

    ```bash
    git clone https://github.com/gloveboxes/Zava-MCP-Server-and-PostgreSQL-Sample
    ```

3. Navigate to the project directory

    ```bash
    cd Zava-MCP-Server-and-PostgreSQL-Sample
    ```

### Deploy Azure Resources

Run the following scripts to automate the deployment of Azure resources needed for the MCP server.

The deployment scripts will automatically deploy the `text-embedding-3-small` model. During deployment, you'll have the option to also include the `gpt-4o-mini` model. Note that `gpt-4o-mini` is **not required** for this project and is only included for potential future enhancements.

**Choose the script for your platform:**

#### Windows (PowerShell)

```powershell
# Run from the project root directory
cd infra && ./deploy.ps1
```

#### macOS/Linux (Bash)

```bash
# Run from the project root directory
cd infra && ./deploy.sh
```

## Running the MCP Server

The easiest way to run the complete stack (PostgreSQL + MCP Server) is using Docker Compose:

### Start the Stack

```bash
# Start PostgreSQL and MCP Server
docker compose up -d

# View logs
docker compose logs -f

# View MCP Server Logs
docker compose logs -f mcp_server

# View the PostgreSQL Logs
docker compose logs -f pg17

# Stop the stack
docker compose down -v
```

## Usage

The following assumes you'll be using the built-in VS Code MCP server support.

1. Open the project in VS Code. From the terminal, run:

    ```bash
    code .
    ```

2. Start one or more MCP servers using the configurations in `.vscode/mcp.json`. The file contains four different server configurations, each representing a different store manager role:

   - Each configuration uses a unique RLS (Row Level Security) user ID
   - These user IDs simulate different store manager identities accessing the database
   - The RLS system restricts data access based on the manager's assigned store
   - This mimics real-world scenarios where store managers sign in with different Entra ID accounts

    ```json
    {
        "servers": {
            "zava-sales-analysis-headoffice"

…

## Source & license

This open-source MCP server is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [microsoft](https://github.com/microsoft)
- **Source:** [microsoft/MCP-Server-and-PostgreSQL-Sample-Retail](https://github.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/mcp-microsoft-mcp-server-and-postgresql-sample-retail
- Seller: https://agentstack.voostack.com/s/microsoft
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.