# 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

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

```powershell
mkdir C:\ai_workspace\leave_management
cd C:\ai_workspace\leave_management

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

If PowerShell blocks activation:

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

## 2. Install Dependencies

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

For an existing checkout:

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

## 3. PostgreSQL Setup

Check PostgreSQL:

```powershell
psql --version
Get-Service *postgres*
```

This project uses PostgreSQL on port **5433**.

Connect:

```powershell
psql -h localhost -p 5433 -U postgres
```

Create the database:

```sql
CREATE DATABASE leave_management;
```

## 4. Configure `.env`

Create `.env` in the project root:

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

```text
.venv/
.env
__pycache__/
*.pyc
```

## 5. Test Database Connection

`db_test.py`:

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

```powershell
python db_test.py
```

## 6. Flask-Migrate

Initialize once:

```powershell
flask --app run.py db init
```

Create/apply migrations:

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

After adding the leave-request model:

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

## 7. Sample Employees

Connect:

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

Insert:

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

```sql
SELECT employee_id, name, department, leave_balance
FROM employees;
```

## 8. Run Flask

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

Expected:

```text
Running on http://127.0.0.1:5000
```

## 9. Test Employee API

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

Expected data includes:

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

## 10. Test Leave API

Apply two days:

```powershell
$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:

```text
status : Pending
```

Check pending requests:

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

Approve using the returned `request_id`:

```powershell
$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:

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

After approving 2 days:

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

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

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

```text
MCP → Flask Services → SQLAlchemy → PostgreSQL
```