Lab 0: Introduction to MCP Database Integration

​

Objectives

• Define Model Context Protocol and its core benefits for database integration
• Identify key components of an MCP server architecture with databases
• Understand the Zava Retail use case and its business requirements
• Recognize enterprise patterns for secure, scalable database access
• List the tools and technologies used throughout this learning path

​

The challenge: AI meets real-world data

• Real-time data access — AI assistants query live databases and APIs
• Secure integration — Controlled access with authentication and permissions
• Natural language interface — Business users ask questions in plain English
• Standardized protocol — Works across different AI platforms and tools

​

Meet Zava Retail

• 8 physical stores across Washington state (Seattle, Bellevue, Tacoma, Spokane, Everett, Redmond, Kirkland)
• 1 online store for e-commerce sales
• A diverse product catalog including tools, hardware, garden supplies, and building materials
• Multi-level management with store managers, regional managers, and executives

​

Business requirements

1. Analyze sales performance across stores and time periods
2. Track inventory levels and identify restocking needs
3. Understand customer behavior and purchasing patterns
4. Discover products through semantic search
5. Generate reports with natural language queries
6. Maintain data security with role-based access control

​

Architecture overview

┌─────────────────────────────────────────────────────────────┐
│                    VS Code AI Client                        │
│                  (Natural Language Queries)                 │
└─────────────────────┬───────────────────────────────────────┘
                      │ HTTP/SSE
                      ▼
┌─────────────────────────────────────────────────────────────┐
│                     MCP Server                              │
│  ┌─────────────────┐ ┌─────────────────┐ ┌───────────────┐  │
│  │   Tool Layer    │ │  Security Layer │ │  Config Layer │  │
│  │ • Query Tools   │ │ • RLS Context   │ │ • Environment │  │
│  │ • Schema Tools  │ │ • User Identity │ │ • Connections │  │
│  │ • Search Tools  │ │ • Access Control│ │ • Validation  │  │
│  └─────────────────┘ └─────────────────┘ └───────────────┘  │
└─────────────────────┬───────────────────────────────────────┘
                      │ asyncpg
                      ▼
┌─────────────────────────────────────────────────────────────┐
│                PostgreSQL Database                          │
│  ┌─────────────────┐ ┌─────────────────┐ ┌───────────────┐  │
│  │  Retail Schema  │ │   RLS Policies  │ │   pgvector    │  │
│  │ • Stores        │ │ • Store-based   │ │ • Embeddings  │  │
│  │ • Customers     │ │   Isolation     │ │ • Similarity  │  │
│  │ • Products      │ │ • Role Control  │ │   Search      │  │
│  │ • Orders        │ │ • Audit Logs    │ │               │  │
│  └─────────────────┘ └─────────────────┘ └───────────────┘  │
└─────────────────────┬───────────────────────────────────────┘
                      │ REST API
                      ▼
┌─────────────────────────────────────────────────────────────┐
│                  Azure OpenAI                               │
│               (Text Embeddings)                             │
└─────────────────────────────────────────────────────────────┘

​

Key components

• FastMCP Framework — Modern Python MCP server implementation
• Tool Registration — Declarative tool definitions with type safety
• Request Context — User identity and session management
• Error Handling — Robust error management and logging

• Connection Pooling — Efficient asyncpg connection management
• Schema Provider — Dynamic table schema discovery
• Query Executor — Secure SQL execution with RLS context
• Transaction Management — ACID compliance and rollback handling

• Row Level Security — PostgreSQL RLS for multi-tenant data isolation
• User Identity — Store manager authentication and authorization
• Access Control — Fine-grained permissions and audit trails
• Input Validation — SQL injection prevention and query validation

• Semantic Search — Vector embeddings for product discovery
• Azure OpenAI Integration — Text embedding generation
• Similarity Algorithms — pgvector cosine similarity search

​

Technology stack

​

Core technologies

​

Production stack

​

Real-world usage scenarios

​

Scenario 1: Store manager performance review

​

Scenario 2: Semantic product search

​

Security model preview

-- Store managers see only their store's data
CREATE POLICY store_manager_policy ON retail.orders
  FOR ALL TO store_managers
  USING (store_id = get_current_user_store());

-- Regional managers see multiple stores
CREATE POLICY regional_manager_policy ON retail.orders
  FOR ALL TO regional_managers
  USING (store_id = ANY(get_user_store_list()));

• Store Manager ID — Unique identifier for RLS context
• Role Assignment — Permissions and access levels
• Session Management — Secure authentication tokens
• Audit Logging — Complete access history