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

  `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

``` text
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](https://docs.astral.sh/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

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

``` bash
uv sync
```

The dependencies used by this project can be added with:

``` bash
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`:

``` sql
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:

``` env
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:

``` env
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.

``` sql
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.

``` sql
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.

``` sql
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.

``` sql
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:

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

Insert their leave balances:

``` sql
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:

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

``` json
{
  "employee_id": "EMP001"
}
```

### Submit a leave request

``` json
{
  "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

``` json
{
  "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

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

### Check date availability

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

### List leave requests

``` json
{
  "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

``` text
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:

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

## Development commands

``` bash
# 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](LICENSE) file for details.