How to Create Automated Time-Off Request Systems

Time-off requests should not require three emails and a form. I built a system to automate time off request approval that lets employees submit requests, routes them to the right manager, checks for conflicts, and updates the calendar. All without manual intervention.

The whole flow takes seconds instead of days.

What You Need Before Starting

• Python 3.8+
• A database (SQLite or PostgreSQL)
• A notification channel (Slack or email)
• Google Calendar API access (optional, for calendar sync)

Step 1: Set Up the Request Database

import sqlite3

def init_pto_db(db_path="pto_tracker.db"):
    conn = sqlite3.connect(db_path)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS pto_requests (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            employee_name TEXT,
            employee_email TEXT,
            manager_email TEXT,
            start_date TEXT,
            end_date TEXT,
            days_requested INTEGER,
            reason TEXT,
            status TEXT DEFAULT 'pending',
            submitted_at TEXT,
            decided_at TEXT
        )
    """)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS pto_balances (
            employee_email TEXT PRIMARY KEY,
            total_days INTEGER DEFAULT 20,
            used_days INTEGER DEFAULT 0
        )
    """)
    conn.commit()
    return conn

Step 2: Submit a Request

from datetime import datetime

def submit_request(conn, employee_name, employee_email, manager_email,
                   start_date, end_date, reason):
    days = (datetime.fromisoformat(end_date) - datetime.fromisoformat(start_date)).days + 1

    balance = conn.execute(
        "SELECT total_days - used_days FROM pto_balances WHERE employee_email=?",
        (employee_email,)
    ).fetchone()

    if balance and balance[0] < days:
        return {"error": f"Insufficient balance. Available: {balance[0]} days, Requested: {days}"}

    conn.execute(
        "INSERT INTO pto_requests (employee_name, employee_email, manager_email, start_date, end_date, days_requested, reason, submitted_at) VALUES (?,?,?,?,?,?,?,?)",
        (employee_name, employee_email, manager_email, start_date, end_date, days, reason, datetime.now().isoformat())
    )
    conn.commit()
    return {"status": "submitted", "days": days}

Step 3: Check for Team Conflicts

def check_conflicts(conn, start_date, end_date, manager_email):
    conflicts = conn.execute("""
        SELECT employee_name, start_date, end_date
        FROM pto_requests
        WHERE manager_email = ?
        AND status = 'approved'
        AND start_date <= ? AND end_date >= ?
    """, (manager_email, end_date, start_date)).fetchall()

    return [{"name": c[0], "start": c[1], "end": c[2]} for c in conflicts]

Step 4: Auto-Approve or Route for Review

def process_request(conn, request_id):
    req = conn.execute(
        "SELECT * FROM pto_requests WHERE id=?", (request_id,)
    ).fetchone()

    conflicts = check_conflicts(conn, req[4], req[5], req[3])

    if len(conflicts) == 0 and req[6] <= 3:
        conn.execute(
            "UPDATE pto_requests SET status='approved', decided_at=? WHERE id=?",
            (datetime.now().isoformat(), request_id)
        )
        conn.execute(
            "UPDATE pto_balances SET used_days = used_days + ? WHERE employee_email=?",
            (req[6], req[2])
        )
        conn.commit()
        return "auto_approved"
    else:
        return "needs_review"

Step 5: Notify and Update Calendar

import smtplib
from email.mime.text import MIMEText

def notify_approval(employee_email, start_date, end_date, status):
    msg = MIMEText(
        f"Your time-off request for {start_date} to {end_date} has been {status}."
    )
    msg["Subject"] = f"PTO Request {status.title()}"
    msg["To"] = employee_email

    with smtplib.SMTP("smtp.gmail.com", 587) as server:
        server.starttls()
        server.login("[email protected]", "app-password")
        server.send_message(msg)

What to Build Next

Add a dashboard showing team availability at a glance. Connect to Google Calendar so approved time-off blocks the employee's calendar automatically. The request system is step one. Visibility across the team is where it becomes actually useful.

Related Reading

• How to Audit Your Operations for AI Opportunities - finding automation opportunities in your workflows
• Building Your First Automation: A Complete Guide - the step-by-step framework for any automation
• The Automation Decision Tree - which processes should you automate first