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

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

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

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

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

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

psql --version
Get-Service *postgres*

psql -h localhost -p 5433 -U postgres

CREATE DATABASE leave_management;

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

.venv/
.env
__pycache__/
*.pyc

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

python db_test.py

flask --app run.py db init

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

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

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

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());

SELECT employee_id, name, department, leave_balance
FROM employees;

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

Running on http://127.0.0.1:5000

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

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

$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

status : Pending

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

$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

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

leave_balance : 8

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

MCP → Flask Services → SQLAlchemy → PostgreSQL