Leave Management — Flask + PostgreSQL

Backend for the leave-management application. Flask exposes REST APIs, SQLAlchemy handles persistence, and PostgreSQL stores employees and leave requests.

Architecture

Claude Desktop
      |
      | MCP / stdio
      v
MCP Server
      |
      | HTTP
      v
Flask API
      |
      | SQLAlchemy
      v
PostgreSQL :5433

Prerequisites

• Windows
• Python 3.12+
• PostgreSQL 17+
• PowerShell

1. Create Project and Virtual Environment

mkdir C:\ai_workspace\leave_management
cd C:\ai_workspace\leave_management

python -m venv .venv
.\.venv\Scripts\Activate.ps1

If PowerShell blocks activation:

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
.\.venv\Scripts\Activate.ps1

2. Install Dependencies

python -m pip install Flask Flask-SQLAlchemy Flask-Migrate psycopg2-binary python-dotenv
python -m pip freeze > requirements.txt

For an existing checkout:

.\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt

3. PostgreSQL Setup

Check PostgreSQL:

psql --version
Get-Service *postgres*

This project uses PostgreSQL on port 5433.

Connect:

psql -h localhost -p 5433 -U postgres

Create the database:

CREATE DATABASE leave_management;

4. Configure .env

Create .env in the project root:

FLASK_APP=run.py
FLASK_ENV=development

DB_HOST=localhost
DB_PORT=5433
DB_NAME=leave_management
DB_USER=postgres
DB_PASSWORD=YOUR_POSTGRES_PASSWORD

Do not commit .env.

Recommended .gitignore:

.venv/
.env
__pycache__/
*.pyc

5. Test Database Connection

db_test.py:

from sqlalchemy import create_engine, text
from dotenv import load_dotenv
import os

load_dotenv()

url = (
    f"postgresql+psycopg2://"
    f"{os.getenv('DB_USER')}:"
    f"{os.getenv('DB_PASSWORD')}@"
    f"{os.getenv('DB_HOST')}:"
    f"{os.getenv('DB_PORT')}/"
    f"{os.getenv('DB_NAME')}"
)

engine = create_engine(url)

with engine.connect() as connection:
    result = connection.execute(text("SELECT version()"))
    print("Database connection successful!")
    print(result.scalar())

Run:

python db_test.py

6. Flask-Migrate

Initialize once:

flask --app run.py db init

Create/apply migrations:

flask --app run.py db migrate -m "Create employees table"
flask --app run.py db upgrade

After adding the leave-request model:

flask --app run.py db migrate -m "Create leave requests table"
flask --app run.py db upgrade

7. Sample Employees

Connect:

psql -h localhost -p 5433 -U postgres -d leave_management

Insert:

INSERT INTO employees
(employee_id, name, email, department, designation, leave_balance, created_at, updated_at)
VALUES
('EMP001', 'Rohit Shinde', 'rohit@example.com', 'Engineering', 'Team Lead', 20, NOW(), NOW()),
('EMP002', 'Amit Sharma', 'amit@example.com', 'HR', 'HR Manager', 15, NOW(), NOW()),
('EMP003', 'Priya Patel', 'priya@example.com', 'Marketing', 'Marketing Executive', 10, NOW(), NOW());

Verify:

SELECT employee_id, name, department, leave_balance
FROM employees;

8. Run Flask

cd C:\ai_workspace\leave_management
.\.venv\Scripts\Activate.ps1
python run.py

Expected:

Running on http://127.0.0.1:5000

9. Test Employee API

Invoke-RestMethod `
  -Uri "http://127.0.0.1:5000/employees/EMP003" `
  -Method GET

Expected data includes:

employee_id   : EMP003
name          : Priya Patel
department    : Marketing
leave_balance : 10

10. Test Leave API

Apply two days:

$body = @{
    employee_id = "EMP003"
    start_date = "2026-08-10"
    end_date = "2026-08-11"
    days = 2
    reason = "Personal leave"
    leave_type = "Personal"
} | ConvertTo-Json

Invoke-RestMethod `
    -Uri "http://127.0.0.1:5000/leave/apply" `
    -Method POST `
    -ContentType "application/json" `
    -Body $body

The request should initially have:

status : Pending

Check pending requests:

Invoke-RestMethod `
  -Uri "http://127.0.0.1:5000/leave/pending" `
  -Method GET

Approve using the returned request_id:

$body = @{
    approved_by = "EMP001"
} | ConvertTo-Json

Invoke-RestMethod `
    -Uri "http://127.0.0.1:5000/leave/request/YOUR_REQUEST_ID/approve" `
    -Method POST `
    -ContentType "application/json" `
    -Body $body

Verify:

Invoke-RestMethod `
  -Uri "http://127.0.0.1:5000/employees/EMP003" `
  -Method GET

After approving 2 days:

leave_balance : 8

API Summary

Method	Endpoint	Purpose
GET	/employees	List employees
GET	/employees/<employee_id>	Employee details
POST	/employees	Create employee
PUT	/employees/<employee_id>	Update employee
DELETE	/employees/<employee_id>	Delete employee
POST	/leave/apply	Apply for leave
GET	/leave/<employee_id>	Employee leave history
GET	/leave/pending	Pending leave requests
GET	/leave/request/<request_id>	Leave request details
POST	/leave/request/<request_id>/approve	Approve leave
POST	/leave/request/<request_id>/reject	Reject leave

Database Verification

SELECT employee_id, name, leave_balance
FROM employees
WHERE employee_id = 'EMP003';

SELECT request_id, employee_id, days, status
FROM leave_requests
WHERE employee_id = 'EMP003';

Design Principle

The Flask application owns business logic and database access. The MCP server calls Flask APIs instead of directly manipulating PostgreSQL:

MCP → Flask Services → SQLAlchemy → PostgreSQL