# MCP-Driven Data Management System

This repository contains the code for a multi-tiered data management system that allows users to interact with heterogeneous databases (MySQL and PostgreSQL) using natural language. It leverages the FastMCP framework for server-side tool orchestration and integrates a Large Language Model (LLM) for intelligent query processing and response generation.
Table of Contents

1. Overview

2. Architecture

3. Key Features

4. Setup and Installation

   - Prerequisites
   - Environment Variables (.env)
   - Database Configuration (Aiven Console for MySQL and Supabase for Postgres)
    - Running the Server
   - Running the Client
5. Usage
6. Troubleshooting

## 1. Overview

This system provides a conversational interface for performing Create, Read, Update, and Delete (CRUD) operations across multiple databases. It intelligently routes user requests to the appropriate backend tools and presents results in a user-friendly format, including advanced data formatting options.
## 2. Architecture

The system follows a layered architecture:

**Client Layer** (client1.py - **Streamlit UI**): The user-facing web application for natural language interaction.

**Application Layer** (main1.py - **FastMCP Server**): The core orchestration layer that hosts database interaction tools, integrates with the LLM, and manages data flow between the client and databases.

**AI/LLM Layer** (**OpenAI API**): An external Large Language Model responsible for understanding user intent, selecting tools, extracting arguments, and generating natural language responses.

**Database Layer** (Aiven console & Supabase - **MySQL & PostgreSQL**): The persistent storage for customer, product, and sales data.

**Conceptual Diagram:**

```
+-------------------+
|   User (Human)    |
+---------+---------+
          |
          | (Types Query)
          v
+-------------------+
|   Client Layer    |
|   (client1.py)    |
|   (Streamlit UI)  |
+---------+---------+
          |
          | (Async HTTP Request)
          v
+-------------------+
|  Application Layer|
| (main1.py)|
|  (FastMCP Server) |
+---------+---------+
          |  ^
          |  | (API Calls)
          |  |
          |  v
+---------+---------+
|   AI / LLM Layer  |
|   (OpenAI API)    |
+-------------------+
          |  ^
          |  | (DB Connections & Queries)
          |  |
          |  v
+---------+---------+
|   Database Layer  |
+---------+---------+
|  MySQL RDS        |
|  - Customers      |
|  - Sales          |
|  - Careplan       |
+-------------------+
|  PostgreSQL RDS   |
|  - products       |
+-------------------+

```

## 3. Key Features

    - Natural Language Interface: Interact with databases using conversational prompts.

    - Intelligent Tool Routing: LLM-powered selection of the correct database tool (sqlserver_crud, postgresql_crud, sales_crud) based on user intent.

    - Heterogeneous Database Support: Manages data across MySQL (for Customers, Sales, ProductsCache) and PostgreSQL (for master products data).

    - Cross-Database Operations: The sales_crud tool performs conceptual "joins" by linking sales records (MySQL) with customer data (MySQL) and product details (MySQL ProductsCache, mirrored from PostgreSQL).

    - Dynamic Data Formatting: Client-side dropdown allows users to select how sales data is displayed:

        - - Data Format Conversion: Formats sale_date for readability.

        - - Decimal Value Formatting: Limits prices to two decimal places.

        - - String Concatenation: Combines customer names and product details into single fields.

        - - Null Value Removal/Handling: Demonstrates filtering out records with null values or replacing nulls with placeholders.

    - Asynchronous Communication: Efficient, non-blocking client-server and server-database interactions using asyncio.

    - Automated Database Seeding: The server automatically sets up and populates necessary tables on startup.

## 4. Setup and Installation
**Prerequisites**

    Python 3.10+

    pip (Python package installer)

**Environment Variables (.env)**
Create a .env file in the root directory of your project (where main1.py and client1.py reside) and populate it with your database credentials and API keys:
```
# OpenAI API Key for LLM integration
GROQ_API_KEY=your_api_key

# MCP Server URL (if deploying, this will be your server's public URL)
MCP_SERVER_URL=http://localhost:8000 # Change for deployment --> it will be the public URL for AWS EC2 instance and the onrender.com link for the Render Web service.
!!!!ADD THE PORT NUMBER AFTER THE URL --->    <url>:8000/


# MySQL Configuration
MYSQL_HOST=your_mysql_rds_endpoint
MYSQL_PORT=3306
MYSQL_USER=your_mysql_username
MYSQL_PASSWORD=your_mysql_password
MYSQL_DB=your_mysql_database_name

# PostgreSQL Configuration
PG_HOST=your_postgresql_rds_endpoint
PG_PORT=5432
PG_DB=your_postgresql_database_name
PG_USER=your_postgresql_username
PG_PASSWORD=your_postgresql_password

PG_SALES_HOST=your_postgresql_rds_endpoint
PG_SALES_PORT=5432
PG_SALES_DB=your_postgresql_database_name
PG_SALES_USER=your_postgresql_username
PG_SALES_PASSWORD=your_postgresql_password
```

**Database Configuration (AWS RDS)**

1. Create MySQL on Aiven Console
2. Create PostgreSQL Supabase Instance

> Note down their endpoint, IP address, password, host name, username, and DB name!!! Check the `.env` file format above ☝️

**Install Python Dependencies**

Navigate to your project directory in the terminal and install the required Python packages:

```pip install fastmcp mysql-connector-python psycopg2-binary groq uvicorn pyodbc streamlit pandas pillow openai fastmcp mcp mcp-server python-dotenv asyncio langchain_groq langchain_core plotly```

**Running the Server**

Go to [Render](render.com) and sign up. Then create a **Web Service**

While setting it up, give your repository's (the one with the server code) public link. 

The server (main1.py) hosts the database tools and communicates with the LLM.

```nohup python -u main1.py > server.log 2>&1 &```

This command will:

    Initialize and seed your MySQL and PostgreSQL databases (dropping and recreating tables if they exist).

    Start the FastMCP server, typically listening on http://localhost:8000.

**Running the Client**

The client (client1.py) provides the Streamlit web interface. We are currently running it using the Streamlit cloud.

```streamlit run client1.py```

This will open the Streamlit application in your web browser.

## 5. Usage

Once both the server and client are running, you can interact with the system through the Streamlit chat interface.

Sample Prompts:
- **"Add a new customer named John Smith with email john.smith@email.com"**
- **"Update the price of Widget to $29.99"**
- **"Delete customer Alice Johnson from the database"**
- **"Create a new product called 'Premium Gadget' priced at $149.99 with description 'High-end gadget'"**
- **"Change Bob Wilson's email to bob.wilson@newcompany.com"**
- **"Show me all sales data with a bar chart of total sales by product"**
- **"Display customer information with a pie chart showing distribution by first letter of name"**
- **"List all products with prices and create a histogram of price distribution"**
- **"Show sales trends over time with a line chart"**
- **"Show me sales where total price is greater than 50 with a visualization"**
- **"Display only customer names and emails, excluding other fields"**
- **"List products with prices between $20 and $50, sorted by price"**
- **"Show me the highest selling product by quantity"**
- **"Show me customers who haven't made any purchases yet"**
- **"Visualize sales distribution by time of day"**
- **”Show me the sales data in a visual form”**

## 6. Troubleshooting

-> _mysql_connector.MySQLInterfaceError: Commands out of sync: This often occurs during database seeding if multiple SQL commands are sent too rapidly on the same cursor, or if autocommit is mismanaged. The current seed_databases implementation attempts to mitigate this by separating commands and managing transactions explicitly.

-> If you are unable to use the `pyodbc` library, depending on the OS of the deployment machine - use the appropriate cmd. Check [here](https://stackoverflow.com/questions/2960339/unable-to-install-pyodbc-on-linux)

-> For UI issues, check your `client1.py` file

- **Database Connection Errors (e.g., "server not accessible", "timed out"):**

        Verify your .env variables are correct (host, port, user, password, URL).

        Ensure your DB instances are "Available".

- **"No Tools Discovered" / LLM Errors:**

        Ensure your main1.py is running and accessible at the MCP_SERVER_URL specified in your .env.

        Verify your GROQ_API_KEY is correct and has sufficient permissions.

        Check server logs for any errors during tool registration or LLM calls.

- **"List Sales" shows no results:**

        The Sales table starts empty. You must first use a "record a sale" prompt to add transactions.

        Confirm that the "record a sale" command returns a success message, indicating the data was inserted.

        Verify that the customer_id and product_id in your sales records have matching entries in the Customers and ProductsCache tables, respectively.