HVAC CFO Assistant Agent

Inspiration

HVAC contractors run razor-thin margins. A single labor overrun, a batch of unsubmitted change orders, or a billing lag of just a few percentage points can quietly erase profit on a multi-million-dollar project — and no one notices until the job closes.

We wanted to build something that a CFO could open on a Monday morning, drop in their portfolio's raw data exports, and get a plain-English briefing: which projects are bleeding money, why, and exactly what to do about it by Friday.

Traditional dashboards show you color-coded cells. We wanted an agent that pushes answers unprompted.

What It Does

The HVAC CFO Assistant Agent ingests up to nine CSV files exported from field systems — contracts, budgets, labor logs, material deliveries, billing history, change orders, RFIs, field notes, and billing line items — and autonomously analyzes the entire portfolio.

It processes over 1.2 million raw labor records, cleans and normalizes them, aggregates them to approximately 405 project-level summaries, and sends those summaries to a Gemini 2.5 Flash model.

The agent streams back a live CFO briefing via Server-Sent Events (SSE):

Executive summary
Ranked findings per project
Dollar-quantified root causes
Concrete recovery actions with named owners and deadlines

A demo mode with synthetic data lets anyone see the full pipeline without uploading real files.

How We Built It

The stack is Next.js 14 on the frontend and backend, deployed to Vercel. The UI has a terminal aesthetic with CRT scanlines and glow effects, showing the agent's reasoning as it runs.

We built four upload strategies to handle file sizes ranging from small to 1M+ row CSVs in serverless environments:

Standard multipart
Chunked assembly in /tmp
Sequential direct-append
Vercel Blob for durable cross-request persistence

The core aggregation engine (aggregator.ts, ~1,300 lines) uses PapaParse for streaming CSV parsing and implements all data-cleaning logic:

Normalizing 47,000+ labor role-name variants
Parsing three mixed date formats
Stripping currency symbols
Deduplicating material deliveries using IQR outlier detection
Computing the labor cost formula: (ST + OT×1.5) × rate × burden

Results are reduced to project-level summaries with DuckDB-style in-memory rollups before being sent to the Gemini API.

UI components were scaffolded and iterated using v0 and Recharts for per-project drill-down charts.

Challenges We Ran Into

The biggest technical challenge was getting 1.2 million rows through a serverless function reliably. Vercel's function instances don’t share /tmp across requests, so naïve chunked uploads would fail on the final assembly step — producing mysterious ENOENT errors.

We built three progressively more robust upload strategies before landing on sequential direct-append and Vercel Blob as the reliable solutions. We even built a dedicated /error-analysis page with a full diagnostic tree, root-cause breakdown, and remediation checklist to debug these failures in production.

A second challenge was data quality: the labor logs contained over 47,000 unique role-name strings for what are really a few dozen roles — we had to write a normalization map comprehensive enough to catch variants like
"JM Pipefitter" → "Journeyman Pipefitter" at scale.

Accomplishments That We're Proud Of

We're proud that the agent produces genuinely actionable output — not just “this project is over budget” but clear, accountable guidance like:

Owner: project manager. Deliverable: freeze overtime and rebalance crews on PRJ-0042 within 5 business days to recover \$87K.

Every finding is dollar-quantified and assigned to a named function.

We’re also proud of the aggregation architecture: reducing 1.2 million rows to a Gemini-sized context window without losing the signal is the kind of production engineering that makes LLM-powered tools work at scale.

The live SSE streaming UI — watching the agent ingest files, clean data, and reason through findings in real time — makes the agent’s work transparent and trustworthy rather than a black box.

What We Learned

We learned that the hard part of an AI-powered data tool isn’t the AI — it's the data pipeline.

The normalization, cleaning, deduplication, and aggregation work took the majority of the engineering effort.

We also learned that serverless environments require deliberate upload architecture — you can’t treat /tmp as a reliable shared filesystem across function invocations.

Finally, prompt engineering for structured JSON output from Gemini required careful schema design and fallback parsing to handle cases where the model returns slightly malformed JSON under load.

What's Next for the HVAC CFO Assistant Agent

Next, we’ll connect the agent directly to live ERP and project-management systems, eliminating the CSV upload step entirely and enabling continuous margin monitoring with alerts when a project crosses a risk threshold.

We’ll also add a conversational layer, so a CFO can ask follow-up questions like:

“Show me all commercial projects with billing gaps over 5%.”

and get answers without re-running the full pipeline.

Long term, the agent’s pattern-detection could feed a bid-risk model — learning from historical margin erosion to flag under-priced bids before contracts are signed.

v0 proof : https://v0.app/chat/cfo-dashboard-design-tCnRhr3DuVZ