Ignite-Zava -and-PostgreSQL-Sample

This MCP server bundle provides a pair of store-specific MCP endpoints that connect to an Azure PostgreSQL database containing the Zava Retail data. The supplier and finance MCP servers expose a secure, schema-aware interface that supports multi-table schema retrieval, role-based data access, and SQL execution with Row Level Security (RLS). You can interact with the servers via the built-in VS Code MCP support or directly from your applications by invoking the Python-based MCP entrypoints. Tools available include get_multiple_table_schemas to fetch multiple table schemas in one call, execute_sales_query to run parameterized PostgreSQL queries with RLS enforcement, get_current_utc_date for timestamp utilities, and semantic_search_products for semantic product lookups. The RLS user IDs in the local VS Code configuration simulate distinct store managers and ensure data isolation according to the chosen role.

Prerequisites:

• Docker Desktop (for any containerized deployments) or Python 3.9+ and Git installed locally
• Git to clone the repository
• Azure CLI installed and authenticated (az login)
• Access to OpenAI models text-embedding-3-small and optionally gpt-4o-mini

Install and run steps:

1. Clone the repository git clone https://github.com/gloveboxes/Zava-MCP-Server-and-PostgreSQL-Sample cd Zava-MCP-Server-and-PostgreSQL-Sample

2. Optional: Set up Azure resources using the provided infra scripts (as described in the README) to deploy Azure AI Foundry models and an Azure PostgreSQL database. This step creates .env with connection details.

   • Windows (PowerShell): cd infra && ./deploy.ps1
   • macOS/Linux (Bash): cd infra && ./deploy.sh

3. Prepare the local environment for running MCP servers.

   • Ensure Python 3.9+ is installed.
   • Install dependencies (example): python -m pip install -r requirements.txt

4. Run the MCP servers. The README indicates two servers are started via Python modules: python -m app.mcp.supplier_server python -m app.mcp.finance_server

5. Verify the servers are reachable. If using VS Code MCP integration, configure the mcp.json settings (outlined in the README) to point to http://127.0.0.1:8000/mcp with proper x-rls-user-id headers for each server.

6. If automatic database initialization fails, you can initialize manually: cd infra ./init-azure-db.sh

Prerequisites are summarized above; ensure network access to Azure resources and OpenAI model endpoints as described in the project documentation.

Tips and common issues:

• Ensure that the .env file generated during deployment contains the correct Azure Postgres connection details and that network access (firewall rules) allows your local machine to reach the Azure database.
• The RLS configuration relies on the x-rls-user-id header; when testing locally in VS Code, set the header values per server configuration to simulate different store managers.
• If you encounter issues with pgvector extension availability, re-run the manual Azure DB initialization script (./init-azure-db.sh) after ensuring PostgreSQL has the required extensions enabled.
• The Tools section lists available MCP commands; use get_multiple_table_schemas before execute_sales_query to reduce ambiguity and ensure correct column usage.
• For clean teardown, ./cleanup.sh will remove Azure resources as described in the documentation.