Employee Leave Management — MCP Server

📌 Check out GenAI and System Design videos

An MCP (Model Context Protocol) server built with FastMCP that connects to a Supabase PostgreSQL database and lets Claude Desktop manage employee leave — checking balances, applying for leave, approvals, history, and more.

---

Architecture

┌───────────────────────────────────────────────────────┐
│  Claude Desktop (MCP Host)                            │
│                                                       │
│  "How many leaves does EMP001 have left this year?"   │
│                                                       │
│  Claude ──► get_leave_balance("EMP001", 2025)         │
│          ◄── [{Annual: 13 remaining}, {Sick: 8}, …]   │
│                                                       │
│  Claude: "Aditya has 13 Annual, 8 Sick, and 5 Casual  │
│           leave days remaining for 2025."             │
└───────────────────────────────────────────────────────┘
          │ stdio (subprocess)
          ▼
┌─────────────────────┐        ┌──────────────────────┐
│  server.py          │        │  Supabase (Postgres)  │
│  FastMCP + tools    │◄──────►│  employees            │
│                     │  REST  │  leave_types          │
│                     │  API   │  leave_balances       │
└─────────────────────┘        │  leave_requests       │
                               └──────────────────────┘

---

Exposed Tools

Tool	What it does
list_leave_types	All leave categories (Annual, Sick, Casual, …) with default allocation
list_employees	Active employees, optional department filter
get_employee_info	Look up one employee by code or email
get_leave_balance	Remaining / used / total days by employee & year
apply_for_leave	Submit a leave request (validates balance & date overlaps)
get_leave_history	All requests for an employee, filterable by year/status
get_leave_request	Full details of a single request by ID
cancel_leave_request	Employee withdraws a pending request
approve_leave_request	Manager approves — auto-deducts days from balance
reject_leave_request	Manager rejects with optional note

Resources

• leave://schema — full schema overview attachable as context

Prompts

• leave_assistant — structured conversation starter for the HR assistant

---

Quick Start

Step 1 — Create a Supabase project

1. Go to https://supabase.com and create a free project.
2. After the project is ready, go to Project Settings → API and copy:
   • Project URL → SUPABASE_URL
   • Project API Keys → anon public key (or service_role key for full access) → SUPABASE_KEY

Step 2 — Set up the database

1. In the Supabase Dashboard, open the SQL Editor → New Query.
2. Paste the full contents of setup_database.sql and click Run.

This creates four tables (employees, leave_types, leave_balances, leave_requests) plus a convenience view (leave_balances_view) and seeds 8 employees with realistic 2025 balances.

Step 3 — Configure environment variables

cd demo_projects/mcp
cp .env.example .env
# Edit .env with your Supabase URL and Key

.env contents:

SUPABASE_URL=https://your-project-ref.supabase.co
SUPABASE_KEY=your-anon-or-service-role-key

Step 4 — Install dependencies

uv sync

Step 5 — Test interactively (without Claude Desktop)

#uv run fastmcp dev server.py
uv run python server.py
# Opens MCP Inspector at http://localhost:6274

---

Connect to Claude Desktop

Locate the config file

OS	Path
macOS	~/Library/Application Support/Claude/claude_desktop_config.json
Windows	%APPDATA%\Claude\claude_desktop_config.json
Linux	~/.config/Claude/claude_desktop_config.json

Add the server block

{
  "mcpServers": {
    "employee-leave": {
      "command": "uv",
      "args": [
        "--directory",
        "/absolute/path/to/project_root",
        "run",
        "python",
        "server.py"
      ]
    }
  }
}

Important: Use the absolute path in --directory. You can get it by running pwd in the project folder.

Note: You may need to provide the full path to the uv executable in the command field. Run which uv (macOS/Linux) or where uv (Windows) to find it.

Restart Claude Desktop

Quit and reopen Claude Desktop. Click the + ("Add files, connectors, and more") icon in the chat input area, then hover over Connectors — you should see employee-leave listed there.

Note: Claude for Desktop is not yet available on Linux. Linux users can use the MCP Inspector or build a custom MCP client instead.

---

Connect to VS Code (GitHub Copilot)

VS Code supports MCP servers natively through GitHub Copilot agent mode. The config format is different from Claude Desktop.

Locate / create the config file

Scope	File
Workspace (team-shared)	.vscode/mcp.json in your project root
User profile (all workspaces)	Run MCP: Open User Configuration from the Command Palette

Add the server block

Create or edit .vscode/mcp.json:

{
  "servers": {
    "employee-leave": {
      "command": "uv",
      "args": [
        "--directory",
        "/absolute/path/to/project_root",
        "run",
        "python",
        "server.py"
      ]
    }
  }
}

Note: VS Code uses "servers" (not "mcpServers") and requires the "type": "stdio" field. Use the absolute path in --directory.

Tip: You may need to provide the full path to uv in "command". Run which uv to find it.

Start the server

1. Open the Command Palette (Ctrl+Shift+P) and run MCP: List Servers.
2. Select employee-leave and choose Start.
3. When prompted, confirm you trust the server.
4. Open the Chat view (Ctrl+Alt+I), switch to Agent mode, and the employee-leave tools will be available.

Verify tools are loaded

In the Chat view, select Configure Tools (or the tools icon) to see all tools provided by the employee-leave server and toggle individual tools on/off.

Troubleshooting

If the server fails to start, run MCP: List Servers → select the server → Show Output to view logs.

---

Example Conversations with Claude

You: How many leaves does Aditya have left for 2025?

Claude: [calls get_employee_info("EMP001"), then get_leave_balance("EMP001", 2025)]
        Aditya Sharma (EMP001) has:
        • Annual leave  : 13 days remaining (5 used of 18)
        • Sick leave    :  8 days remaining (2 used of 10)
        • Casual leave  :  5 days remaining (1 used of 6)

You: Apply for annual leave for EMP002 from 10th July to 18th July 2025.

Claude: [calls apply_for_leave("EMP002", "Annual", "2025-07-10", "2025-07-18")]
        Leave request submitted!
        • Employee   : Priya Nair (EMP002)
        • Type       : Annual
        • Dates      : 10 Jul – 18 Jul 2025  (7 business days)
        • Status     : Pending
        • Remaining after approval: 8 Annual days

You: Approve request ID 3.

Claude: [calls approve_leave_request(3)]
        Leave request #3 has been approved. 7 days deducted from Priya's Annual balance.

---

Database Schema

employees          -- employee_code, full_name, email, department, position
leave_types        -- name, description, default_days
leave_balances     -- one row per (employee, leave_type, year)
leave_balances_view -- denormalised view with remaining_days computed
leave_requests     -- status: pending | approved | rejected | cancelled

---

Project Files

demo_projects/mcp/
├── server.py             ← FastMCP server (all tools, resources, prompts)
├── setup_database.sql    ← Run once in Supabase SQL Editor
├── pyproject.toml        ← Dependencies (fastmcp, supabase, python-dotenv)
├── .env.example          ← Copy to .env and fill in credentials
└── README.md             ← This file