Leave Management MCP Server

A Python-based Model Context Protocol (MCP) server for managing employee leave requests using PostgreSQL. It exposes leave-management functionality as MCP tools and resources so an MCP-compatible client can interact with employee records, leave balances, leave requests, and leave calendars.

Features

MCP tools

Tool Description

check_leave_balance Retrieves an employee's available balance for each leave type.

submit_leave_request Submits a leave request, calculates working days (Monday--Friday), and stores the request and its individual leave dates.

review_leave_request Approves or rejects a pending request. Approval deducts the requested days from the employee's leave balance.

get_employee_leave_dates Retrieves an employee's leave dates, with optional status and month filters.

check_date_availability Lists employees who have approved leave on a specified date.

Related MCP server: Leave Management MCP Server

list_leave_requests Lists leave requests, optionally filtered by status and employee.

MCP resources

Resource URI Description

policy://company/leave Returns the company's leave policy.

balances://{employee_id} Returns leave balances for the specified employee.

Technology stack

• Python

• MCP Python SDK (mcp[cli])

• PostgreSQL

• Psycopg 3 (psycopg[binary])

• python-dotenv

• uv for Python project and dependency management

Project structure

leave-management-mcp-server/
├── .env                    # Local database configuration (not committed)
├── .env.example            # Example environment configuration
├── .gitignore
├── pyproject.toml
├── uv.lock                 # Generated by uv, if committed
└── src/
    └── my_first_mcp_server/
        └── __init__.py     # MCP server, tools, resources, and database logic

The GitHub repository can be named leave-management-mcp-server while the Python package directory remains my_first_mcp_server.

Prerequisites

1. Python version compatible with the requires-python setting in pyproject.toml.

2. uv

3. PostgreSQL, with a database created for the application.

4. An MCP-compatible client or MCP Inspector for testing.

Setup

1. Clone the repository

git clone https://github.com/YOUR_USERNAME/leave-management-mcp-server.git
cd leave-management-mcp-server

Replace YOUR_USERNAME with your GitHub username.

2. Install dependencies

uv sync

The dependencies used by this project can be added with:

uv add "mcp[cli]"
uv add "psycopg[binary]"
uv add python-dotenv

uv add adds a dependency to pyproject.toml and updates the lock file. uv sync installs the dependencies declared by the project.

3. Create the PostgreSQL database

For example, create a database named leave_management:

CREATE DATABASE leave_management;

Connect to that database before running the table creation statements below.

4. Configure environment variables

Create a .env file in the project root:

DATABASE_URL=postgresql://postgres:YOUR_PASSWORD@localhost:5432/leave_management

Replace YOUR_PASSWORD with your PostgreSQL password.

The application loads this value using python-dotenv. Keep .env private and do not commit it to Git.

Create a .env.example file for other developers, containing placeholders only:

DATABASE_URL=postgresql://postgres:YOUR_PASSWORD@localhost:5432/leave_management

5. Create the database tables

Run the following SQL while connected to the leave_management database.

Employees

Stores employee details. employee_id is the primary key.

CREATE TABLE employees (
    employee_id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL
);

Leave balances

Stores the available number of days for each employee and leave type. Each employee can have one balance row per leave type.

CREATE TABLE leave_balances (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id VARCHAR(20) NOT NULL,
    leave_type VARCHAR(20) NOT NULL,
    available_days INTEGER NOT NULL DEFAULT 0,

    CONSTRAINT fk_balance_employee
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id),

    CONSTRAINT uk_employee_leave
        UNIQUE (employee_id, leave_type),

    CONSTRAINT ck_leave_type
        CHECK (leave_type IN ('VACATION', 'SICK', 'PERSONAL')),

    CONSTRAINT ck_available_days
        CHECK (available_days >= 0)
);

Leave requests

Stores the overall request, including its date range, number of requested working days, reason, status, and reviewer remarks.

CREATE TABLE leave_requests (
    request_id VARCHAR(20) PRIMARY KEY,
    employee_id VARCHAR(20) NOT NULL,
    leave_type VARCHAR(20) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    days_requested INTEGER NOT NULL,
    reason VARCHAR(500),
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    remarks VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_request_employee
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id),

    CONSTRAINT ck_request_leave_type
        CHECK (leave_type IN ('VACATION', 'SICK', 'PERSONAL')),

    CONSTRAINT ck_request_status
        CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED')),

    CONSTRAINT ck_request_dates
        CHECK (end_date >= start_date),

    CONSTRAINT ck_request_days
        CHECK (days_requested > 0)
);

Individual leave dates

Stores each working date associated with a request. This supports overlap checks and date-based availability queries. Deleting a request also deletes its associated date rows.

CREATE TABLE leave_request_dates (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    request_id VARCHAR(20) NOT NULL,
    leave_date DATE NOT NULL,

    CONSTRAINT fk_leave_request
        FOREIGN KEY (request_id)
        REFERENCES leave_requests(request_id)
        ON DELETE CASCADE,

    CONSTRAINT uk_request_date
        UNIQUE (request_id, leave_date)
);

6. Optional sample data

Insert sample employees:

INSERT INTO employees (employee_id, name, department)
VALUES
    ('EMP001', 'Alice Johnson', 'Engineering'),
    ('EMP002', 'Bob Smith', 'Product');

Insert their leave balances:

INSERT INTO leave_balances (employee_id, leave_type, available_days)
VALUES
    ('EMP001', 'VACATION', 15),
    ('EMP001', 'SICK', 8),
    ('EMP001', 'PERSONAL', 4),
    ('EMP002', 'VACATION', 10),
    ('EMP002', 'SICK', 10),
    ('EMP002', 'PERSONAL', 2);

Run these inserts only once for the sample database. Running them again may cause primary-key or unique-constraint violations.

Run the MCP server with MCP Inspector

Start the MCP development inspector from the repository root:

uv run mcp dev src/my_first_mcp_server/__init__.py

The command launches the MCP development environment and MCP Inspector. Open the Inspector URL printed in the terminal (commonly http://127.0.0.1:6274).

Keep the terminal process running while using Inspector. Stop it with Ctrl+C.

Use Inspector to connect to the server, view its resources and tools, and invoke the tools with test arguments.

Example tool inputs

The exact arguments are validated against the Python tool function signatures.

Check leave balance

{
  "employee_id": "EMP001"
}

Submit a leave request

{
  "employee_id": "EMP001",
  "leave_type": "SICK",
  "start_date": "2026-11-06",
  "end_date": "2026-11-09",
  "reason": "Sick leave"
}

The working-day calculation counts Monday through Friday, inclusive. In this example, Friday 6 November and Monday 9 November are two working days.

Review a leave request

{
  "request_id": "REQ-XXXXXXXX",
  "action": "APPROVE",
  "remarks": "Approved"
}

Use the actual request ID returned by submit_leave_request. Supported actions are APPROVE and REJECT.

Get employee leave dates

{
  "employee_id": "EMP001",
  "status": "APPROVED",
  "month_prefix": "2026-11"
}

Check date availability

{
  "target_date": "2026-11-06"
}

List leave requests

{
  "status": "ALL",
  "employee_id": "EMP001"
}

Leave request lifecycle

1. An employee submits a request.

2. The server validates the employee, leave type, date range, balance, and overlapping pending or approved leave.

3. The server inserts the request into leave_requests and its working dates into leave_request_dates.

4. The request starts with PENDING status.

5. An authorized reviewer approves or rejects the request.

6. On approval, the server deducts the requested days from leave_balances and updates the request status.

7. On rejection, the status changes to REJECTED; the balance is not deducted.

Pending requests do not deduct or reserve leave balance in the current business logic. Public holidays are not excluded by the current Monday--Friday working-day calculation.

Database relationships

employees
   │
   ├──< leave_balances
   │
   └──< leave_requests
             │
             └──< leave_request_dates

• One employee can have multiple leave-balance rows, one per leave type.

• One employee can submit multiple leave requests.

• One leave request can contain multiple individual working dates.

• leave_request_dates.request_id references leave_requests.request_id with ON DELETE CASCADE.

Troubleshooting

DATABASE_URL is missing

• Confirm that .env exists in the repository root.

• Confirm that it contains DATABASE_URL=....

• Verify the database name, username, password, host, and port.

• Ensure the application loads the root .env file.

PostgreSQL connection error

• Confirm PostgreSQL is running.

• Confirm the leave_management database exists.

• Verify the credentials in DATABASE_URL.

• Confirm that the tables were created in the correct database.

MCP tool execution error

• Check the terminal running mcp dev for the Python traceback.

• Verify that the MCP tool arguments match the function signatures.

• Verify that the database contains the expected employee and balance records.

• Check that the request ID and leave type satisfy the database constraints.

AttributeError: 'Connection' object has no attribute 'executemany'

With Psycopg 3, call executemany() on a cursor, not on the connection:

with conn.cursor() as cur:
    cur.executemany(
        """
        INSERT INTO leave_request_dates (request_id, leave_date)
        VALUES (%s, %s)
        """,
        rows
    )

Development commands

# Add the MCP SDK
uv add "mcp[cli]"

# Add PostgreSQL driver
uv add "psycopg[binary]"

# Add .env support
uv add python-dotenv

# Install/synchronize dependencies
uv sync

# Start MCP Inspector
uv run mcp dev src/my_first_mcp_server/__init__.py

Security notes

• Never commit .env, database passwords, API keys, or access tokens.

• Keep .env in .gitignore.

• Use .env.example with placeholder values for documentation.

• Use a restricted PostgreSQL user for application access rather than a database superuser.

• Do not expose raw database exception details to end users in production; log diagnostic details securely.

Future enhancements

Possible next improvements include:

• Company holiday calendar and holiday-aware working-day calculations.

• Role-based authorization for reviewers and employees.

• Reserving leave days for pending requests, if required by company policy.

• Audit history for approval/rejection and balance changes.

• Notifications for request submission and review decisions.

• Automated tests for validation, overlapping requests, transactions, and concurrent submissions.

License

This project is licensed under the MIT License.

See the LICENSE file for details.