Mini Leave Management System (MVP)

A production-ready Leave Management System designed for startups with ~50 employees. Built with Node.js, Express, and Supabase PostgreSQL.

🚀 Features

Employee Management

• ✅ Add new employees with auto-generated employee IDs (EMP0001, EMP0002, etc.)
• ✅ Initialize default leave balance (20 leaves/year)
• ✅ Employee profile management
• ✅ Department-wise organization

Leave Management

• ✅ Apply for leave with business day calculations
• ✅ Approve/Reject leave applications with audit trails
• ✅ Real-time leave balance tracking
• ✅ Leave history and statistics
• ✅ Calendar view for leave planning

Advanced Features

• ✅ Comprehensive edge case handling
• ✅ Business day calculation (excluding weekends)
• ✅ Overlapping leave detection
• ✅ Insufficient balance validation
• ✅ Pre-joining date validation
• ✅ Comprehensive API documentation with Swagger

🛠 Tech Stack

• Backend: Node.js with Express.js
• Database: PostgreSQL via Supabase
• Validation: Joi for request validation
• Documentation: Swagger/OpenAPI 3.0
• Security: Helmet, CORS, Rate limiting
• Deployment: Ready for Render/Heroku/Vercel

📋 Prerequisites

• Node.js (v14 or higher)
• Supabase account and project
• npm or yarn package manager

⚡ Quick Start

1. Clone and Install

   git clone <repository-url>
   cd mini-leave-management-system
   npm install

2. Environment Setup

   cp .env.example .env

   Update .env with your Supabase credentials:

   VITE_SUPABASE_URL=your_supabase_url_here
   VITE_SUPABASE_ANON_KEY=your_supabase_anon_key_here
   PORT=3000
   NODE_ENV=development
   DEFAULT_LEAVE_BALANCE=20

3. Database Setup

   • The migration file will automatically create the required tables and sample data
   • Tables: employees, leaves
   • Functions: Auto-generate employee IDs, calculate business days
   • Sample employees are pre-loaded for testing

4. Start the Server

   npm run dev

5. Access the API

   • API Base URL: http://localhost:3000/api
   • API Documentation: http://localhost:3000/api-docs
   • Health Check: http://localhost:3000/health

📚 API Endpoints

Employee Management

• POST /api/employees - Add new employee
• GET /api/employees - Get all employees (paginated)
• GET /api/employees/:id - Get employee by ID
• GET /api/employees/employee-id/:employeeId - Get employee by employee ID
• GET /api/employees/:id/leave-balance - Get employee leave balance
• GET /api/employees/:id/leaves - Get employee's leave history

Leave Management

• POST /api/leaves/apply - Apply for leave
• GET /api/leaves - Get all leaves (with filtering)
• GET /api/leaves/:id - Get specific leave
• POST /api/leaves/approve/:id - Approve leave
• POST /api/leaves/reject/:id - Reject leave
• GET /api/leaves/balance/:employee_id - Get leave balance
• GET /api/leaves/pending - Get pending leaves (HR view)
• GET /api/leaves/calendar - Get leaves by date range

💻 Example Usage

1. Add Employee

curl -X POST http://localhost:3000/api/employees \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Sonu Anand",
    "email": "sonu@example.com",
    "department": "Engineering",
    "joining_date": "2025-08-01"
  }'

Response:

{
  "success": true,
  "message": "Employee added successfully",
  "data": {
    "id": "uuid",
    "employee_id": "EMP0001",
    "name": "Sonu Anand",
    "email": "sonu@example.com",
    "department": "Engineering",
    "joining_date": "2025-08-01",
    "leave_balance": 20
  }
}

2. Apply for Leave

curl -X POST http://localhost:3000/api/leaves/apply \
  -H "Content-Type: application/json" \
  -d '{
    "employee_id": "employee-uuid",
    "start_date": "2025-08-10",
    "end_date": "2025-08-12",
    "reason": "Family trip"
  }'

Success Response:

{
  "success": true,
  "message": "Leave application submitted successfully",
  "data": {
    "id": "leave-uuid",
    "employee_id": "employee-uuid",
    "start_date": "2025-08-10",
    "end_date": "2025-08-12",
    "reason": "Family trip",
    "status": "pending",
    "days_requested": 3
  }
}

Error Response (Insufficient Balance):

{
  "success": false,
  "error": "Insufficient leave balance",
  "details": "You have 5 days remaining, but requested 10 days",
  "available_balance": 5,
  "requested_days": 10
}

3. Get Leave Balance

curl http://localhost:3000/api/leaves/balance/employee-uuid

Response:

{
  "success": true,
  "data": {
    "employee": {
      "id": "employee-uuid",
      "employee_id": "EMP0001",
      "name": "Sonu Anand",
      "email": "sonu@example.com",
      "department": "Engineering"
    },
    "leave_balance": 17,
    "leave_stats": {
      "total_applied": 3,
      "pending": 1,
      "approved": 2,
      "rejected": 0,
      "total_days_taken": 3
    }
  }
}

🔒 Edge Cases Handled

1. Pre-joining Date Validation

{
  "success": false,
  "error": "Invalid leave dates",
  "details": "Cannot apply for leave before joining date"
}

2. Insufficient Leave Balance

{
  "success": false,
  "error": "Insufficient leave balance",
  "details": "You have 5 days remaining, but requested 10 days",
  "available_balance": 5,
  "requested_days": 10
}

3. Overlapping Leaves

{
  "success": false,
  "error": "Overlapping leave request",
  "details": "You already have a pending or approved leave request for the specified dates",
  "overlapping_leaves": [...]
}

4. Invalid Dates

{
  "success": false,
  "error": "Invalid leave dates",
  "details": "End date must be after or equal to start date"
}

5. Employee Not Found

{
  "success": false,
  "error": "Employee not found",
  "details": "No employee found with the provided ID"
}

🏗 Database Schema

Employees Table

- id (UUID, Primary Key)
- employee_id (Text, Unique, Auto-generated: EMP0001, EMP0002...)
- name (Text, Required)
- email (Text, Unique, Required)
- department (Text, Required)
- joining_date (Date, Required)
- leave_balance (Integer, Default: 20)
- created_at, updated_at (Timestamps)

Leaves Table

- id (UUID, Primary Key)
- employee_id (UUID, Foreign Key)
- start_date, end_date (Dates, Required)
- reason (Text, Required)
- status (Enum: pending, approved, rejected)
- days_requested (Integer, Auto-calculated)
- applied_at (Timestamp)
- processed_at (Timestamp)
- processed_by (Text)
- comments (Text)

🎯 Business Logic

Leave Balance Calculation

• Default balance: 20 days per year
• Business days only (excludes weekends)
• Balance deducted only on approval
• Balance restored if approved leave is later rejected

Auto-calculations

• Employee ID: Auto-generated (EMP0001, EMP0002...)
• Business Days: Calculated excluding weekends
• Leave Balance: Updated via database triggers
• Timestamps: Automatic tracking of application and processing times

🔧 Configuration

Environment Variables

VITE_SUPABASE_URL=your_supabase_url
VITE_SUPABASE_ANON_KEY=your_supabase_anon_key
PORT=3000
NODE_ENV=development
DEFAULT_LEAVE_BALANCE=20

Rate Limiting

• 100 requests per 15 minutes per IP
• Configurable in src/server.js

Security Features

• Helmet for security headers
• CORS enabled
• Input validation with Joi
• SQL injection prevention via Supabase
• Row Level Security (RLS) enabled

🚀 Deployment

Option 1: Render

1. Connect your GitHub repository
2. Set environment variables
3. Deploy automatically

Option 2: Heroku

heroku create your-app-name
heroku config:set VITE_SUPABASE_URL=your_url
heroku config:set VITE_SUPABASE_ANON_KEY=your_key
git push heroku main

Option 3: Vercel

1. Install Vercel CLI: npm i -g vercel
2. Run: vercel
3. Set environment variables in dashboard

📊 Assumptions & Improvements

Current Assumptions

• 20 days annual leave balance
• Business days exclude weekends only (no holidays)
• Single leave type (can be extended)
• Manual approval process (no auto-approval)
• Email notifications not included

Potential Improvements

1. Role-based Access Control: HR, Manager, Employee roles
2. Email Notifications: Automated notifications for applications/approvals
3. Holiday Calendar: Country/region-specific holidays
4. Multiple Leave Types: Sick, vacation, personal, etc.
5. Leave Carryover: Unused leaves to next year
6. Mobile App: React Native mobile application
7. Analytics Dashboard: Leave trends and insights
8. Bulk Operations: Approve/reject multiple leaves
9. Leave Policies: Configurable leave policies per department
10. Integration: Calendar apps, HR systems integration

🧪 Testing

Run Tests

npm test

Health Check

curl http://localhost:3000/health

Sample Data

The system includes 5 sample employees for testing:

• John Doe (Engineering)
• Jane Smith (HR)
• Mike Johnson (Marketing)
• Sarah Wilson (Finance)
• David Brown (Engineering)

📖 Documentation

• API Documentation: Available at /api-docs when server is running
• Postman Collection: Can be generated from Swagger documentation
• Database Schema: Documented in migration files

🤝 Support

For technical support or feature requests:

1. Check the API documentation at /api-docs
2. Review the error response format for debugging
3. Ensure all environment variables are properly set
4. Verify Supabase connection and permissions

🔄 Version History

• v1.0.0: Initial MVP release with core functionality
  • Employee management
  • Leave application and approval
  • Balance tracking
  • Comprehensive validation
  • API documentation

---

Built with ❤️ for efficient leave management