How to Build an Overdue Invoice Alert System

Chasing late payments is time you will never get back. I built an overdue invoice alert system with automated follow-up that tracks every open invoice, sends graduated reminders, and escalates to you only when a payment is seriously past due.

The system chases the money. You step in only when it matters.

What You Need Before Starting

• Python 3.8+
• Invoice database (from system 342)
• Email SMTP credentials
• Escalation rules

Step 1: Define Reminder Schedule

REMINDER_SCHEDULE = [
    {"days_overdue": 1, "type": "gentle", "subject": "Invoice #{number} - Payment Reminder"},
    {"days_overdue": 7, "type": "firm", "subject": "Invoice #{number} - Payment Past Due"},
    {"days_overdue": 14, "type": "urgent", "subject": "Invoice #{number} - Urgent: Payment Required"},
    {"days_overdue": 30, "type": "final", "subject": "Invoice #{number} - Final Notice"},
]

REMINDER_TEMPLATES = {
    "gentle": "Hi {name},\n\nJust a friendly reminder that invoice #{number} for ${amount} was due on {due_date}. If you have already sent payment, please disregard this message.\n\nThanks,\n{sender}",
    "firm": "Hi {name},\n\nInvoice #{number} for ${amount} is now 7 days past due (original due date: {due_date}). Please arrange payment at your earliest convenience.\n\nBest,\n{sender}",
    "urgent": "Hi {name},\n\nThis is an urgent reminder regarding invoice #{number} for ${amount}, now 14 days past due. Please process payment immediately or contact us to discuss.\n\n{sender}",
    "final": "Hi {name},\n\nFinal notice: Invoice #{number} for ${amount} is 30 days past due. If payment is not received within 5 business days, we will need to pause services and begin collection procedures.\n\n{sender}"
}

Step 2: Find Overdue Invoices

import sqlite3
from datetime import datetime

def get_overdue_invoices(conn):
    today = datetime.now().strftime("%Y-%m-%d")
    rows = conn.execute("""
        SELECT invoice_number, client_id, amount, due_date, last_reminder_sent
        FROM invoices
        WHERE status = 'sent' AND due_date < ?
        ORDER BY due_date ASC
    """, (today,)).fetchall()

    overdue = []
    for num, client, amount, due, last_reminder in rows:
        days = (datetime.now() - datetime.strptime(due, "%Y-%m-%d")).days
        overdue.append({
            "number": num, "client_id": client, "amount": amount,
            "due_date": due, "days_overdue": days, "last_reminder": last_reminder
        })

    return overdue

Step 3: Determine Which Reminders to Send

def get_pending_reminders(overdue_invoices):
    to_send = []

    for invoice in overdue_invoices:
        for schedule in REMINDER_SCHEDULE:
            if invoice["days_overdue"] >= schedule["days_overdue"]:
                if invoice["last_reminder"] != schedule["type"]:
                    to_send.append({**invoice, "reminder_type": schedule["type"],
                                   "subject": schedule["subject"].format(number=invoice["number"])})
                    break

    return to_send

Step 4: Send Reminders and Log

import smtplib
from email.mime.text import MIMEText

def send_reminder(invoice, client_email, smtp_config):
    template = REMINDER_TEMPLATES[invoice["reminder_type"]]
    body = template.format(
        name=invoice.get("client_name", "there"),
        number=invoice["number"],
        amount=f"{invoice['amount']:.2f}",
        due_date=invoice["due_date"],
        sender="Jay"
    )

    msg = MIMEText(body)
    msg["Subject"] = invoice["subject"]
    msg["To"] = client_email

    with smtplib.SMTP(smtp_config["host"], smtp_config["port"]) as server:
        server.starttls()
        server.login(smtp_config["user"], smtp_config["password"])
        server.send_message(msg)

    print(f"Sent {invoice['reminder_type']} reminder for #{invoice['number']}")

def update_reminder_status(conn, invoice_number, reminder_type):
    conn.execute(
        "UPDATE invoices SET last_reminder_sent=? WHERE invoice_number=?",
        (reminder_type, invoice_number)
    )
    conn.commit()

Step 5: Run Daily

def run_overdue_check(conn, smtp_config, client_lookup):
    overdue = get_overdue_invoices(conn)
    reminders = get_pending_reminders(overdue)

    for reminder in reminders:
        client = client_lookup.get(reminder["client_id"], {})
        send_reminder(reminder, client.get("email", ""), smtp_config)
        update_reminder_status(conn, reminder["number"], reminder["reminder_type"])

    print(f"Sent {len(reminders)} payment reminders. {len(overdue)} total overdue.")

# Schedule: daily at 10am
# 0 10 * * * python3 /path/to/overdue_alerts.py

What to Build Next

Add payment analytics that tracks average days-to-pay per client. Clients who consistently pay late might need different terms. Clients who always pay on time deserve recognition. The alerts are reactive. The analytics are proactive.

Related Reading

• Building Your First Automation: A Complete Guide - automation fundamentals
• The Feedback Loop That Powers Everything - payment tracking feedback
• How to Audit Your Operations for AI Opportunities - billing automation opportunities