01The Client
Jabs Group
A construction and cost-management firm running multiple high-end residential, hospitality, and retail projects at once. The team manages every project through email and a stack of shared Excel workbooks — an Invoice Tracker, a firm-wide Automated Cost Report (ACR), and a sprawling contact list exported from Microsoft Dynamics. Stakeholders span the CEO, a Director of Cost Management, senior PMs, and the cost managers who live in the spreadsheets every day.
02The Challenge
The firm processes 100–300+ emails per day across projects. Two recurring operational problems were eating real hours and creating real risk: action items and invoices buried across threads, and a project-relationship database that had rotted into stale, duplicated rows. Meanwhile, the cost-control workbooks that the entire business runs on were reconciled by hand.
What Was Broken:
- Manual invoice logging. Every invoice that arrived had to be opened, read, and hand-keyed into a SharePoint-hosted Excel tracker — repetitive, error-prone, and slow.
- List rot in the contact directory. A Dynamics export full of duplicates, blank states, and compound values (
UK,TX/CA) with no reliable way to ask "which designers are in Florida" or "how often did this person refer us." - Hand-reconciled cost reports. Job codes had to be cross-checked by eye across seven different ledgers in a 25-sheet, ~10,180-formula workbook before any draw could be trusted.
- No system of record. Leads, referrals, and project teams lived in inboxes and people's heads — nothing the VP of Business Development could filter, report on, or present to the CEO.
03What We Built
The engagement started as a tightly-scoped email-to-Excel pilot and grew into three independent production systems, each replacing a manual workflow without disrupting the spreadsheets the team already trusts.
1 — The CRM
A purpose-built system of record for companies, contacts, and the project-lead pipeline — the central hub the firm never had.
- • Next.js 15 + Supabase Postgres (server components & server actions; deny-all RLS, service-role writes only)
- • One-click Excel ingestion of the Dynamics export with automatic dedup (email, then name+company — the source sheets overlapped on 63 of 86 names)
- • Smart company status maintained by DB triggers: New Prospect → Bid For → Worked With, never hand-set
- • Project-leads pipeline with project-type-driven team roles, referral tracking, and an append-only Next Steps history
- • Five cross-table reports, each exportable to Excel for the VP of Business Development
- • Auth gate, audit log, soft-delete recycle bin, and an iMessage business-card capture webhook
2 — AP Invoice Automation
Outlook inbox → n8n → /ingest → OpenAI extraction → Supabase → Review dashboard
- • Fastify service on Render with an n8n workflow watching a dedicated invoice inbox and POSTing every attachment to
/ingest - • LLM extraction that reads each invoice PDF and writes structured rows straight into the existing SharePoint Excel tracker — first data row preserved, no structural changes
- • Next.js review dashboard giving cost managers a human-in-the-loop queue before anything is committed
- • 159 real invoices extracted and reconciled against a verified pre-cutover backup of every production table
3 — ACR Cost-Report Reconciler
Upload an Automated Cost Report workbook, get back a visual reconciliation that cross-checks every job code the way a cost manager would — only instantly.
- • FastAPI service (web + CLI) that validates all 105 fixed job codes across Baseline Budget, Budget Transfer Log, Budget Increase Log, Procurement Tracker, Change Order Log, Invoice Tracker, and Cost Report
- • Built around the firm's real template — 25 sheets, ~10,180 formulas — with no restructuring and no macros, so the workbook still opens in mobile Excel, Numbers, and Google Sheets
- • Vendor-name canonicalization to survive string-equal joins ("Coastline Construction Group" ≠ "Coastline Construction")
04Technical Complexity Solved
- Write around the workbook, not through it. Job codes are the join key and every write is validated against the Job Code Register. Different tabs start on different rows (Invoice Tracker row 5, Change Order Log row 4) — each mapped explicitly rather than assumed.
- Formula-safe recalculation.
openpyxlwrites formula text but doesn't compute it, so the pipeline accounts for headless LibreOffice recalc rather than silently shipping stale totals. - Humans keep the judgment calls. Change-order approval is never inferred from a PDF — new rows default to
Pendinguntil a person decides, matching how approvals actually live in email and conversation. - Backups before every cutover. A bulk clear once wiped 677 legitimate rows — so the system now takes a verifiable JSON + SQLite + Excel snapshot and confirms live counts match before any table is touched.
05Tech Stack
06AI's Role in Development
AI shows up twice over. In the product, an LLM does the actual extraction work — reading invoice PDFs and turning them into validated rows — while deliberately leaving approval decisions to humans.
In the build, Claude was the development partner across all three systems: architecting the CRM schema from a requirements doc and a Dynamics export, designing the dedup and smart-status logic, wiring the Fastify + n8n ingestion path, and reverse-engineering a 25-sheet, ~10,180-formula workbook into a safe write target — shipped as production systems rather than a deck.
07Results & Impact
Inbox triage and manual invoice entry are off the team's plate, the contact directory is finally a queryable system of record, and cost reports reconcile in seconds instead of by hand. The work is now expanding into change-order intake and automated draw-package generation — the next phases of the same pipeline.