Business Automation Case Study // Production Homelab

Automated Coaching Sync & Square Billing Pipeline

Raspberry Pi 4 Self-Hosted Google Calendar API Square Invoices API Human-in-the-Loop Safety SQLite WAL Idempotency Python 3 + FastAPI

Raspberry Pi Daemon Active

As a solo AI consultant and 1-on-1 coach, administrative billing used to consume over 45 minutes of manual cross-referencing every week—manually opening calendar entries, matching client names, checking hourly rates, and typing line items into invoicing software. To eliminate this busywork forever, Elijah engineered a private, local-first Python + SQLite pipeline hosted on his Raspberry Pi 4. It parses completed coaching sessions, stages them behind an air-tight human-in-the-loop review gate, and publishes invoices via Square in under 60 seconds a week.

View Executive ROI Interactive Pipeline Sandbox Automate Your Operations

Quantifiable Business Impact

Executive ROI & Operational Gains

Measured outcomes from replacing manual spreadsheet reconciliation with a deterministic local-first state machine.

100%

Elimination of Missed Sessions

Every completed billable calendar session is captured and reconciled. Zero forgotten billable hours.

< 60 sec

Weekly Billing Review Time

Reduced administrative invoicing overhead from 45+ minutes to a single 1-click batch review on the LAN.

$0 / mo

Recurring Middleware Fees

Eliminated expensive multi-step Zapier and Make subscriptions in favor of a 5-watt self-hosted microservice.

Zero

Accidental Invoices Sent

A strict staging review gate guarantees no invoice ever leaves the system without explicit 1-click approval.

Architecture Walkthrough

Interactive 4-Step Pipeline Architecture

Click through each stage to inspect the technical data contracts, idempotency checks, and safety controls.

STAGE 1: CALENDAR EVENT INGESTION

OAuth Ingestion & Deterministic Regex Filtering

The service communicates with the Google Calendar API via OAuth 2.0 with automated headless token refresh (token.json). Every evening, a systemd timer polls the calendar for completed events within the past 14 days.

Event Parsing Regex

/(?:Success Coaching|Elijah|Success Mentoring)(?:\s+with)?\s+(.+)/i

Extracts client name from calendar titles regardless of client formatting variations.

Safety & Noise Filtering

Discards cancelled, declined, or unconfirmed calendar invitations.
Automatically identifies [Free Discovery] and intro calls to avoid billing consultations.
Computes duration from RFC 3339 timestamps for partial-hour fractional billing.

STAGE 2: IDEMPOTENT SQLITE RECONCILIATION

Transactional Deduplication & Custom Rate Matching

Incoming parsed events are written into a local SQLite database configured with Write-Ahead Logging (WAL). Sessions are deduplicated using calendar event IDs as strict unique constraints.

Database Schema Constraint

CREATE UNIQUE INDEX idx_event ON meetings(calendar_event_id);

Re-running sync 10 times a day can never create duplicate meeting records or double-bill.

Client Rate Table Resolution

Resolves client record by name or email alias against clients table.
Retrieves client-specific agreed rates (e.g., $150/hr vs. customized retainer packages).
Generates staged line items in state PENDING_REVIEW.

STAGE 3: HUMAN-IN-THE-LOOP STAGING GATE

1-Click Local Review Before Financial Dispatch

Financial transactions should never execute fully autonomously without human oversight. Elijah hosts a private, secure FastAPI web dashboard on the local LAN (Raspberry Pi 4:8000).

Granular Line-Item Controls

Toggle individual sessions on/off with responsive checkboxes.
Adjust session rates inline for prorated meetings or credited sessions.
Preview live invoice totals and line item descriptions before submission.

Strict Privacy Architecture

Runs strictly on the local home network with zero public port exposure.
Client names and billing figures never pass through third-party multi-tenant SaaS servers.
Full audit log of who approved each invoice and at what exact timestamp.

STAGE 4: SQUARE INVOICES API DISPATCH

Deterministic Order Creation & Invoicing Webhooks

Upon clicking "Approve Batch", the system talks directly to the official Square SDK. It creates a customer if new, generates an Order with calculated line items, and publishes the invoice.

Square API Idempotency Token

idempotency_key = f"inv_{client_id}_{batch_hash}_{date}"

Even in the event of network dropouts or timeouts, Square guarantees zero duplicate charges.

Order State Machine Lifecycle

Supports DRAFT mode for extra inspection or PUBLISHED for automatic email dispatch.
Updates local SQLite record with square_invoice_id and direct Square Dashboard deep links.
Webhooks track invoice payment status (PAID, UNPAID, REFUNDED).

Live Interactive Sandbox

Try the Human-in-the-Loop Staging Review

Experience the exact workflow in a safe, client-side sandbox using fictional sample data. Zero real client information or external APIs are touched.

LAN Review Dashboard // Simulated Raspberry Pi 4

Filter: Pending Review (2 Sessions Found)

Staged Total: $275.00

Alex Rivera Bi-Weekly AI Productivity Session $150.00

Yesterday, 2:00 PM – 3:00 PM (60 min) • Google Meet #cal_evt_9a48f2 • PENDING APPROVAL

Dr. Elena Vance Systems Engineering Mentorship $125.00

Tuesday, 10:00 AM – 10:45 AM (45 min) • Google Meet #cal_evt_8c11e4 • PENDING APPROVAL

Marcus Chen [Free Discovery] Workflow Audit Intro $0.00

Monday, 3:30 PM (30 min) • Filtered: Free Intro Tag • AUTOMATICALLY EXCLUDED

Engineering Philosophy

Why Custom Code Beats Fragile No-Code Zaps

Why professional automation engineering relies on idempotent code rather than brittle multi-step SaaS subscriptions.

Architectural Dimension	Generic SaaS / Zapier / Make	Elijah's Local-First Pipeline
Deduplication & Idempotency	High Risk Triggers fire multiple times on rescheduled meetings; prone to double-charging clients.	Guaranteed Database-level `UNIQUE(calendar_event_id)` + Square idempotency keys prevent duplicates.
Human-in-the-Loop Oversight	Blind Push Sends emails automatically on trigger; calendar typos turn into embarrassing billing disputes.	Staging Gate FastAPI review dashboard allows Elijah to review, edit, or exclude sessions with 1 click before send.
Operating Cost & SaaS Lock-In	$360 – $840 / year Expensive tiered subscriptions that scale up as your meeting volume increases.	$0 / month Runs continuously on a 5-watt self-hosted Raspberry Pi 4 with zero software fees.
Client Data Privacy	Third-Party Cloud Client names, emails, and financial amounts pass through shared multi-tenant cloud servers.	100% Private OAuth tokens, client lists, and rate tables remain strictly on local encrypted disk.
Reliability & Maintainability	Brittle Silent token expirations, unannounced UI changes, and limited error debugging.	Rock Solid 14 passing `pytest` unit tests, structured logs, and automatic systemd service restart.