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
• 📁 Project Structure
• 📋 Prerequisites
• 🛠️ Workflow
• 🖥️ Install Claude Desktop
• 🐍 Install Python
• 📦 Install uv Python package manager
• 🔧 Install MCP Python SDK
• 📁 Create Project Using uv
• ▶️ Run the MCP service
• 🧪 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

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 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 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

     python --version
     

     

   • Check pip installation

     pip --version
     

     

   • Upgrade pip if required

     python -m pip install --upgrade pip
     

📦 Install uv Python package manager

1. uv documentation:

   • Visit https://docs.astral.sh/uv/getting-started/installation.

2. Install uv:

   pip install uv
   

   

3. Verify Installation:

   • Open PowerShell and run the command

     uv --version
     

     

🔧 Install MCP Python SDK

1. mcp documentation:
   • Visit https://pypi.org/project/mcp and https://github.com/modelcontextprotocol/python-sdk.
2. Install SDK and CLI

   pip install mcp
   pip install mcp[cli]
   

   
3. Verify Installation:
   • Open PowerShell and run the command

   pip show mcp
   

   

📁 Create Project Using uv

1. uv documentation

   • Visit https://docs.astral.sh/uv/concepts/projects/init.

2. Initialize uv project

   uv init mcp-server
   cd mcp-server
   

   

3. Copy main.py with leave management tools into project directory Sample MCP Server: main.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:

   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