Content
# AI-Powered Leave Management System with Claude, MCP, and SQLite
This is a small sample, AI-powered leave management application developed for learning purposes. It integrates Claude for natural language interactions, FastMCP as the modular command interface, and SQLite for simple, persistent data storage. The system supports basic employee and leave management operations (CRUD), leave balance tracking, and history management. Designed as a lightweight example to demonstrate how AI tools can be combined with Python and databases in a modular architecture.
---
## 📚 Table of Contents
- [🚀 Features](#-features)
- [📁 Project Structure](#-project-structure)
- [📋 Prerequisites](#-prerequisites)
- [🛠️ Workflow](#-workflow)
- [🖥️ Install Claude Desktop](#-install-claude-desktop)
- [🐍 Install Python](#-install-python)
- [📦 Install `uv` Python package manager](#-install-uv-python-package-manager)
- [🔧 Install MCP Python SDK](#-install-mcp-python-sdk)
- [📁 Create Project Using `uv`](#-create-project-using-uv)
- [▶️ Run the MCP service](#️-run-the-mcp-service)
- [🧪 Sample Questions to Ask the Leave Management System](#-sample-questions-to-ask-the-leave-management-system)
---
## 🚀 Features
- ✅ Natural language leave management with **Claude AI**
- ✅ Modular command interface using **FastMCP**
- ✅ Lightweight and fast **SQLite** backend
- ✅ Create, read, update, and delete (CRUD) operations for:
- Employees
- Leave history
- ✅ Automatic leave balance adjustment
- ✅ Leave conflict prevention (no duplicates)
- ✅ Utility tools like leave ID lookup
- ✅ Sample data and test cases included
- ✅ Fully local and privacy-preserving
- ✅ Compatible with **Claude Desktop Developer Tools**
---
## 📁 Project Structure
```text
mcp-server/
├── main.py # MCP server logic for leave management
├── pyproject.toml # Python dependencies for the MCP server
└── README.md # Project documentation
```
---
## 📋 Prerequisites
- **Operating System**: Windows 10 or later
- **Software**:
- Claude Desktop
- Python 3.13+
- `uv` (package manager)
- `mcp` Python SDK
---
## 🛠️ Workflow
1. Install **Claude Desktop** on Windows
2. Start Claude Desktop in **Developer Mode**
3. Install **Python**, **uv**, and **mcp**
4. Initialize a project using `uv init`
5. Add `main.py` to the project
6. Run `uv run mcp install main.py`
7. Verify and test integrated tools in Claude
---
## 🖥️ Install Claude Desktop
### ➤ Windows
1. **Download Installer**:
- Visit [Claude Desktop Download](https://claude.ai/download) and click **Download for Windows** to get `Claude-Setup-x64.exe`.
2. **Install Claude Desktop**:
- Execute `Claude-Setup-x64.exe` and follow the installation prompts.
- After installation, restart computer if required.
- After restarting computer, Claude Desktop runs in the system tray.
3. **Enable Developer Mode**:
- Click the ☰ menu → `Help` → `Enable Developer Mode`
- Developer tools will now be availablemenue and click on `Enable Developer Mode`
---
## 🐍 Install Python
1. **Download Installer**:
- Visit [Python Download](https://www.python.org/downloads/) and click **Download for Windows** to get `python-3.13.4-amd64.exe`.
2. **Run the Installer**:
- Execute `Claude-Setup-x64.exe` and follow the installation prompts.
3. **Verify Installation**:
- Open PowerShell and run the command
```poweshell
python --version
```
- Check pip installation
```poweshell
pip --version
```
- Upgrade pip if required
```poweshell
python -m pip install --upgrade pip
```
---
## 📦 Install `uv` Python package manager
1. **`uv` documentation**:
- Visit [https://docs.astral.sh/uv/getting-started/installation](https://docs.astral.sh/uv/getting-started/installation/#standalone-installer).
2. **Install `uv`**:
```poweshell
pip install uv
```
3. **Verify Installation**:
- Open PowerShell and run the command
```poweshell
uv --version
```
---
## 🔧 Install MCP Python SDK
1. **`mcp` documentation**:
- Visit [https://pypi.org/project/mcp](https://pypi.org/project/mcp/) and [https://github.com/modelcontextprotocol/python-sdk](https://github.com/modelcontextprotocol/python-sdk).
2. **Install SDK and CLI**
```poweshell
pip install mcp
pip install mcp[cli]
```
3. **Verify Installation**:
- Open PowerShell and run the command
```poweshell
pip show mcp
```
---
## 📁 Create Project Using `uv`
1. **`uv` documentation**
- Visit [https://docs.astral.sh/uv/concepts/projects/init](https://docs.astral.sh/uv/concepts/projects/init/).
2. **Initialize uv project**
```poweshell
uv init mcp-server
cd mcp-server
```
3. **Copy main.py with leave management tools into project directory**
Sample MCP Server: `main.py`
```py
import sqlite3
from mcp.server.fastmcp import FastMCP
from typing import List, Optional
DB_PATH = "leave_manager.db"
def init_db():
"""Initialize database with employees (with name) and leave_history tables."""
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS employees (
employee_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
balance INTEGER NOT NULL
)
''')
c.execute('''
CREATE TABLE IF NOT EXISTS leave_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employee_id TEXT NOT NULL,
leave_date TEXT NOT NULL,
FOREIGN KEY(employee_id) REFERENCES employees(employee_id)
)
''')
# Insert sample data if empty
c.execute('SELECT COUNT(*) FROM employees')
if c.fetchone()[0] == 0:
sample_employees = [
("E1", "Alice Johnson", 18),
("E2", "Bob Smith", 20),
("E3", "Charlie Lee", 15),
("E4", "Diana King", 10),
("E5", "Ethan Brown", 5),
]
c.executemany('INSERT INTO employees (employee_id, name, balance) VALUES (?, ?, ?)', sample_employees)
sample_leaves = [
("E1", "2024-12-25"),
("E1", "2025-01-01"),
("E3", "2025-03-15"),
("E3", "2025-03-18"),
("E4", "2025-02-14"),
("E4", "2025-04-10"),
("E4", "2025-05-05"),
("E5", "2025-01-15"),
("E5", "2025-03-01"),
("E5", "2025-04-01"),
("E5", "2025-05-01"),
("E5", "2025-05-15"),
]
c.executemany('INSERT INTO leave_history (employee_id, leave_date) VALUES (?, ?)', sample_leaves)
conn.commit()
conn.close()
init_db()
def get_conn():
"""Get a new connection to the SQLite DB."""
return sqlite3.connect(DB_PATH)
mcp = FastMCP("LeaveManager")
# --- EMPLOYEE CRUD ---
@mcp.tool()
def add_employee(employee_id: str, name: str, initial_balance: int = 20) -> str:
"""
Create a new employee with the given ID, name, and initial leave balance.
"""
conn = get_conn()
c = conn.cursor()
c.execute('SELECT 1 FROM employees WHERE employee_id = ?', (employee_id,))
if c.fetchone():
conn.close()
return "Employee ID already exists."
c.execute('INSERT INTO employees (employee_id, name, balance) VALUES (?, ?, ?)', (employee_id, name, initial_balance))
conn.commit()
conn.close()
return f"Employee {employee_id} ({name}) added with {initial_balance} leave day(s)."
@mcp.tool()
def get_employee(employee_id: str) -> str:
"""
Retrieve employee details (ID, name, leave balance) by employee_id.
"""
conn = get_conn()
c = conn.cursor()
c.execute('SELECT employee_id, name, balance FROM employees WHERE employee_id = ?', (employee_id,))
row = c.fetchone()
conn.close()
if not row:
return "Employee ID not found."
return f"ID: {row[0]}, Name: {row[1]}, Leave Balance: {row[2]} day(s)"
@mcp.tool()
def update_employee(employee_id: str, name: Optional[str] = None, balance: Optional[int] = None) -> str:
"""
Update employee's name and/or leave balance. At least one must be provided.
"""
if name is None and balance is None:
return "No update parameters provided."
conn = get_conn()
c = conn.cursor()
c.execute('SELECT 1 FROM employees WHERE employee_id = ?', (employee_id,))
if not c.fetchone():
conn.close()
return "Employee ID not found."
if name is not None and balance is not None:
c.execute('UPDATE employees SET name = ?, balance = ? WHERE employee_id = ?', (name, balance, employee_id))
elif name is not None:
c.execute('UPDATE employees SET name = ? WHERE employee_id = ?', (name, employee_id))
else:
c.execute('UPDATE employees SET balance = ? WHERE employee_id = ?', (balance, employee_id))
conn.commit()
conn.close()
return f"Employee {employee_id} updated successfully."
@mcp.tool()
def delete_employee(employee_id: str) -> str:
"""
Delete an employee and all their leave history from the system.
"""
conn = get_conn()
c = conn.cursor()
c.execute('SELECT 1 FROM employees WHERE employee_id = ?', (employee_id,))
if not c.fetchone():
conn.close()
return "Employee ID not found."
c.execute('DELETE FROM leave_history WHERE employee_id = ?', (employee_id,))
c.execute('DELETE FROM employees WHERE employee_id = ?', (employee_id,))
conn.commit()
conn.close()
return f"Employee {employee_id} and their leave history removed."
@mcp.tool()
def list_employees() -> str:
"""
List all employees with their ID, name, and leave balance.
"""
conn = get_conn()
c = conn.cursor()
c.execute('SELECT employee_id, name, balance FROM employees ORDER BY employee_id')
rows = c.fetchall()
conn.close()
if not rows:
return "No employees found."
lines = [f"{row[0]}: {row[1]} - {row[2]} leave days" for row in rows]
return "Employees:\n" + "\n".join(lines)
# --- LEAVE HISTORY CRUD ---
@mcp.tool()
def add_leave(employee_id: str, leave_date: str) -> str:
"""
Add a leave date entry for the specified employee.
"""
conn = get_conn()
c = conn.cursor()
c.execute('SELECT balance FROM employees WHERE employee_id = ?', (employee_id,))
row = c.fetchone()
if not row:
conn.close()
return "Employee ID not found."
if row[0] <= 0:
conn.close()
return "Insufficient leave balance."
# Check if leave date already exists
c.execute('SELECT 1 FROM leave_history WHERE employee_id = ? AND leave_date = ?', (employee_id, leave_date))
if c.fetchone():
conn.close()
return "Leave date already exists for this employee."
# Insert leave
c.execute('INSERT INTO leave_history (employee_id, leave_date) VALUES (?, ?)', (employee_id, leave_date))
# Update balance
c.execute('UPDATE employees SET balance = balance - 1 WHERE employee_id = ?', (employee_id,))
conn.commit()
conn.close()
return f"Leave added for {employee_id} on {leave_date}."
@mcp.tool()
def get_leaves(employee_id: str) -> str:
"""
Retrieve all leave dates for an employee.
"""
conn = get_conn()
c = conn.cursor()
c.execute('SELECT leave_date FROM leave_history WHERE employee_id = ? ORDER BY leave_date', (employee_id,))
rows = c.fetchall()
conn.close()
if not rows:
return "No leave records found or employee does not exist."
dates = ", ".join(row[0] for row in rows)
return f"Leave dates for {employee_id}: {dates}"
@mcp.tool()
def update_leave(leave_id: int, new_date: str) -> str:
"""
Update the leave date by leave history record ID.
"""
conn = get_conn()
c = conn.cursor()
c.execute('SELECT employee_id, leave_date FROM leave_history WHERE id = ?', (leave_id,))
row = c.fetchone()
if not row:
conn.close()
return "Leave record ID not found."
employee_id, old_date = row
# Check if new date already exists for that employee
c.execute('SELECT 1 FROM leave_history WHERE employee_id = ? AND leave_date = ?', (employee_id, new_date))
if c.fetchone():
conn.close()
return "The new leave date already exists for this employee."
# Update date
c.execute('UPDATE leave_history SET leave_date = ? WHERE id = ?', (new_date, leave_id))
conn.commit()
conn.close()
return f"Leave date updated from {old_date} to {new_date} for employee {employee_id}."
@mcp.tool()
def delete_leave(leave_id: int) -> str:
"""
Delete a leave entry by leave record ID and restore leave balance.
"""
conn = get_conn()
c = conn.cursor()
c.execute('SELECT employee_id FROM leave_history WHERE id = ?', (leave_id,))
row = c.fetchone()
if not row:
conn.close()
return "Leave record ID not found."
employee_id = row[0]
# Delete leave
c.execute('DELETE FROM leave_history WHERE id = ?', (leave_id,))
# Restore balance
c.execute('UPDATE employees SET balance = balance + 1 WHERE employee_id = ?', (employee_id,))
conn.commit()
conn.close()
return f"Leave record {leave_id} deleted and leave balance restored for employee {employee_id}."
# Example: leave record retrieval by employee and date (not mandatory for CRUD but useful)
@mcp.tool()
def find_leave_id(employee_id: str, leave_date: str) -> str:
"""
Find the leave record ID for a given employee and leave date.
"""
conn = get_conn()
c = conn.cursor()
c.execute('SELECT id FROM leave_history WHERE employee_id = ? AND leave_date = ?', (employee_id, leave_date))
row = c.fetchone()
conn.close()
if not row:
return "Leave record not found."
return f"Leave record ID: {row[0]}"
if __name__ == "__main__":
mcp.run()
```
---
## ▶️ Run the MCP service
1. **Run mcp server using `main.py` file**:
```poweshell
uv run mcp install main.py
```
2. **Verify the MCP tools**:
- This will configure and register tools with Claude
- Restart Claude Desktop (Quit & Open) and verify in Tools Panel
- You should see LeaveManager and all listed tools
3. **Verify the MCP server configuration**:
---
## 🧪 Sample Questions to Ask the Leave Management System
### ✅ Employee Management
🔹 Add / Create Employee
- "Add a new employee with ID E6, name Fatima Noor, and initial balance of 12."
- "Create an employee called Omar Khan with ID E7 and 20 leave days."
🔹 Get Employee Info
- "What are the details of employee E1?"
- "Show me the information for employee E4."
🔹 Update Employee
- "Update the name of employee E3 to Charlie Lin."
- "Set the leave balance of E5 to 8."
- "Change the name to Alice J. and balance to 22 for employee E1."
🔹 Delete Employee
- "Remove employee E2 from the system."
- "Delete employee E6 and all their leave history."
🔹 List Employees
- "List all employees and their current leave balances."
- "Show me everyone in the system."
### ✅ Leave Management
🔹 Add Leave
- "Add a leave for E1 on 2025-07-01."
- "Mark leave on 2025-08-15 for employee E3."
🔹 Get Leave History
- "Show all leave dates for E1."
- "What are the leaves taken by employee E4?"
🔹 Update Leave
- "Update leave with ID 3 to 2025-03-17."
- "Change the leave with record ID 11 to 2025-06-01."
🔹 Delete Leave
- "Delete leave record with ID 4 and restore balance."
- "Remove the leave ID 12."
🔹 Find Leave ID
- "What is the leave ID for E1 on 2024-12-25?"
- "Find leave record for E5 on 2025-03-01."
### 🧪 Boundary & Validation Tests
🔸 Duplicate or Conflicting Entries
- "Try adding a leave for E5 on 2025-03-01 again." (Should trigger: leave already exists)
- "Add employee E1 again." (Should trigger: ID already exists)
🔸 Insufficient Balance
- "Add leave for E5 on 2025-06-01." (Should fail if balance is 0)
🔸 Update Nonexistent Entities
- "Update employee E99 to Ghost User."
- "Delete leave record ID 999."
### 📸 Samples
