Business Dashboard Backend

Overview

This project integrates Aroflo data (via n8n workflows) into a Drizzle ORM/D1-backed backend, exposing secure Hono API/webhook endpoints for ingestion and analytics. The system is designed for extensibility, supporting additional Aroflo entities and workflows as business needs grow.

• Data Sources: Aroflo API (tasks, contacts, clients, invoices, etc.) via n8n
• Ingestion: n8n normalizes and POSTs data to Hono webhook endpoints
• Persistence: Drizzle ORM with D1 database, supporting historical and real-time data
• API: Hono endpoints for ingestion and analytics
• Extensibility: Easily add new endpoints/entities as needed

See docs/aroflo-integration.md, docs/data-model.md, docs/webhooks.md, and docs/n8n-workflows.md for details.

---

Quick Links

• Aroflo Integration
• Data Model
• Webhook API
• n8n Workflows
• Roadmap

Project Handover & Documentation: Ahead Business Dashboard

This document provides a complete overview of the Ahead Business Dashboard project, including its architecture, data workflows, and key implementation details. It is intended for a new developer taking over or contributing to the project.

1. Overview & Core Concepts

The Ahead BusinessDashboard is a custom analytics platform that consolidates data from three primary sources—Aroflo (operational), Xero (financial), and Timeero (workforce)—into a unified, real-time dashboard.

The system's architecture is built on three core components:

1. n8n (ETL Layer): A dedicated n8n instance acts as the "Extract, Transform, Load" (ETL) engine. It runs scheduled workflows that connect to the Aroflo and Xero APIs, pull the necessary data, normalize it, and then securely POST it to our backend.
2. Backend API (Ingestion & Data Layer): A lightweight API built with Hono.js and deployed on a Cloudflare Worker. Its sole purpose is to receive normalized data from the n8n webhooks, validate it, and store it in a Cloudflare D1 database using the Drizzle ORM.
3. Frontend Dashboard (Presentation Layer): A Next.js application (using OpenNext) deployed on a Cloudflare Worker. It fetches the processed data from our backend API and displays it through a series of interactive charts and metric cards.

This decoupled architecture ensures that the data ingestion logic (n8n) is separate from the data storage (Backend) and presentation (Frontend), making the system modular and easier to maintain.

2. Project Structure

The project is a monorepo containing the backend, frontend, and all related configurations.

• packages/backend/: Contains the Hono.js API, Drizzle schema, and database migrations.
  • wrangler.toml: The configuration file for the backend Cloudflare Worker. This is where database bindings and environment variables are set.
• packages/frontend/: The Next.js application for the dashboard UI.
  • app/: Contains the page routes and UI components.
  • lib/api.ts: Functions for fetching data from our backend API.
• n8n-workflows/: Contains the JSON exports of all n8n workflows. These can be imported into a new n8n instance to replicate the data ingestion pipelines.
• docs/: Contains markdown documentation on the data model, integrations, and project roadmap.

3. Data Flow: The n8n Workflows

The n8n workflows are the heart of the data ingestion process. They run on a schedule to keep the dashboard data fresh. You can find the JSON for these in the n8n-workflows/ directory and a video walkthrough here.

Key Workflows & Logic:

Workflow Name	Source	Purpose & Key Logic-
[AROFLO] [CLIENTS] ...	Aroflo	Fetches all client data from Aroflo. It then joins this with Aroflo invoice data to calculate and aggregate a totalSpend for each client. This workflow is crucial for understanding client value.-
[AROFLO] [TASKS] ...	Aroflo	Pulls all task (job) data from Aroflo, including status, client info, and assigned technicians. This is the primary source for operational metrics like "Jobs Completed" and "Jobs Booked."-
[XERO] [INVOICES] ...	Xero	Fetches all sales invoices (ACCREC) from Xero. Key Quirk: It parses the Reference field for a job number (e.g., "JN:12345") to link Xero financial data back to specific Aroflo jobs. This linkage is critical for job-level profitability metrics.-
[XERO] [EXPENSES] ...	Xero	Fetches all bills (ACCPAY) from Xero. This is the source for direct costs, material spend, and overheads. Quirk: The accuracy of metrics like "Net Profit" and "Break-Even Revenue" depends heavily on how well these expenses are categorized in Xero.-
[XERO] [BALANCE] & [PROFITLOSS]	Xero	These workflows fetch the standard Balance Sheet and Profit & Loss reports from Xero, providing high-level financial data for the dashboard.-

Important Note on n8n Hosting: These workflows are currently running on my personal n8n instance. For a production-ready setup, they will need to be migrated to a dedicated, self-hosted, or cloud-hosted n8n account for the client. The exported JSON files are provided for this purpose.

Important Note on n8n Hosting: These workflows are currently running on my personal n8n instance. For a production-ready setup, they will need to be migrated to a dedicated, self-hosted, or cloud-hosted n8n account for the client. The exported JSON files are provided for this purpose.

4. Local Development & Deployment

• Local Setup:
  1. Run npm install at the root of the monorepo.
  2. Run npm run dev:backend to start the Hono API locally.
  3. Run npm run dev:frontend to start the Next.js frontend locally. The dashboard will be available at http://localhost:3000.
• Deployment:
  • The project is deployed to Cloudflare Workers using the unified npm run deploy script.
  • The script handles building and deploying both the backend and frontend.
  • Secrets (API keys, etc.) are managed via Wrangler secrets and must be set up in the new Cloudflare account. See wrangler.backend.toml for required secrets.

5. Key Areas & "Quirks" for the New Developer

This is the most critical section. Understanding these points will save significant time and prevent confusion.

• Timeero Integration (Currently ON HOLD):

  • Goal: The primary objective is to automatically calculate "On-site Time" vs. "Windshield (Travel) Time" for the Efficiency Dashboard.
  • The Challenge/Quirk: Timeero's native reports are not suitable because they require technicians to manually check in/out of every single job, which is an operational bottleneck the client wants to avoid.
  • The Proposed Workaround: The plan is to use the Timeero API to pull the raw GPS "breadcrumb trail" for each technician's entire day. Then, our custom backend logic will cross-reference these GPS points against the known job site addresses from Aroflo. If a technician's location is within a certain radius of a job site, that time is tagged as "On-site." All other time is tagged as "Windshield."
  • Current Status: This custom logic is complex and requires significant development. The initial investigation is complete, but the full implementation is on hold pending a new budget block. The client needs a definitive answer on whether this is achievable before continuing their Timeero subscription.

• Data Reconciliation (Aroflo vs. Xero):

  • The Challenge/Quirk: The client has previously noted discrepancies between revenue figures in Aroflo and the dashboard.
  • The Rule: Xero is the single source of truth for all financial data. The dashboard pulls all financial metrics (Revenue, Expenses, Profit) directly from Xero reports and invoices. Aroflo is used for operational data (Jobs, Clients, etc.).
  • The Link: The key mechanism linking Xero financials to Aroflo jobs is the Job Number (e.g., "JN:12345") in the Reference field of Xero invoices. If this is missing, the revenue cannot be tied to a specific job and may appear as "Other Revenue" on the dashboard. This is a manual process that the client's bookkeeper needs to ensure is followed consistently for accurate job-level profitability.

• Custom n8n Xero Node:

  • The Quirk: The standard n8n Xero node does not have all the necessary API scopes (specifically accounting.reports.read) to pull financial reports like the P&L and Balance Sheet.
  • The Solution: A custom n8n credential node was created (n8n-nodes-xero-custom) to solve this. This node must be installed on the n8n instance that runs these workflows. The n8n-nodes-xero-custom.tar.gz file is included in the repository for this purpose.