Finance Automation
invoicing billing
How to Build a Client Billing Dashboard
Track all client billing in one dashboard with payment status and history.
Jay Banlasan
The AI Systems Guy
You should not need to query three systems to know who owes you money. I built a client billing dashboard with automated tracking that shows every client's billing status, payment history, and outstanding balance in one view.
One screen. Every client. Every dollar.
What You Need Before Starting
- Python 3.8+
- Invoice database
- A web framework (Flask for simplicity)
- Client data source
Step 1: Aggregate Billing Data
import sqlite3
def get_billing_overview(conn):
rows = conn.execute("""
SELECT client_id,
COUNT(*) as total_invoices,
SUM(CASE WHEN status='paid' THEN amount ELSE 0 END) as paid,
SUM(CASE WHEN status='sent' THEN amount ELSE 0 END) as outstanding,
SUM(CASE WHEN status='sent' AND due_date < date('now') THEN amount ELSE 0 END) as overdue,
MAX(created_at) as last_invoice_date
FROM invoices GROUP BY client_id
""").fetchall()
return [{"client": r[0], "total_invoices": r[1], "paid": r[2],
"outstanding": r[3], "overdue": r[4], "last_invoice": r[5]} for r in rows]
Step 2: Build the Dashboard API
from flask import Flask, jsonify
app = Flask(__name__)
@app.route("/api/billing/overview")
def billing_overview():
conn = sqlite3.connect("invoices.db")
data = get_billing_overview(conn)
totals = {
"total_outstanding": sum(d["outstanding"] for d in data),
"total_overdue": sum(d["overdue"] for d in data),
"clients": data
}
return jsonify(totals)
@app.route("/api/billing/client/<client_id>")
def client_detail(client_id):
conn = sqlite3.connect("invoices.db")
invoices = conn.execute(
"SELECT invoice_number, amount, status, created_at, due_date, paid_at FROM invoices WHERE client_id=? ORDER BY created_at DESC",
(client_id,)
).fetchall()
return jsonify([{"number": i[0], "amount": i[1], "status": i[2],
"created": i[3], "due": i[4], "paid": i[5]} for i in invoices])
Step 3: Add Payment History Charts
def get_monthly_revenue(conn, months=12):
rows = conn.execute("""
SELECT strftime('%Y-%m', paid_at) as month, SUM(amount) as revenue
FROM invoices WHERE status='paid' AND paid_at IS NOT NULL
GROUP BY month ORDER BY month DESC LIMIT ?
""", (months,)).fetchall()
return [{"month": r[0], "revenue": r[1]} for r in rows]
Step 4: Add Alert Indicators
def get_billing_alerts(conn):
alerts = []
overdue = conn.execute(
"SELECT client_id, invoice_number, amount, due_date FROM invoices WHERE status='sent' AND due_date < date('now')"
).fetchall()
for row in overdue:
days = (datetime.now() - datetime.strptime(row[3], "%Y-%m-%d")).days
alerts.append({"client": row[0], "invoice": row[1], "amount": row[2],
"days_overdue": days, "severity": "high" if days > 30 else "medium"})
return sorted(alerts, key=lambda x: -x["days_overdue"])
Step 5: Serve the Dashboard
from datetime import datetime
@app.route("/")
def dashboard():
return """<!DOCTYPE html>
<html><body>
<h1>Billing Dashboard</h1>
<div id="overview"></div>
<script>
fetch('/api/billing/overview')
.then(r => r.json())
.then(data => {
document.getElementById('overview').innerHTML =
'<p>Outstanding: $' + data.total_outstanding.toFixed(2) + '</p>' +
'<p>Overdue: $' + data.total_overdue.toFixed(2) + '</p>';
});
</script>
</body></html>"""
if __name__ == "__main__":
app.run(port=5000)
What to Build Next
Add revenue forecasting based on outstanding invoices and historical payment patterns. Knowing what you billed is good. Knowing when you will actually collect it is better.
Related Reading
- AI-Powered Reporting That Actually Gets Read - building dashboards that drive action
- Building Your First Automation: A Complete Guide - automation fundamentals
- How Systems Compound Over Time - billing data compounds over time
Want this system built for your business?
Get a free assessment. We will map every system your business needs and show you the ROI.
Get Your Free Assessment