DEV Community

Cover image for How I Built a 90-Day Cash Flow Intelligence Engine
Ugo Chukwu
Ugo Chukwu

Posted on

How I Built a 90-Day Cash Flow Intelligence Engine

#n8n #supabase #react #finops

Building a 90-Day Cash Flow Intelligence Engine with n8n + Supabase + React

TL;DR: In this guide I'll walk you through a full, production-style Cash Flow Intelligence Engine that:

  • Ingests bank, AR, AP, payroll, and expense data
  • Computes daily cash snapshots and 90-day forecasts
  • Supports what-if scenarios (hire 5 people, growth −20%, cut marketing, etc.)
  • Triggers runway alerts
  • Visualises everything in a React dashboard powered by Supabase

You can view or fork the full project here:

👉 GitHub repo: https://github.com/Etherlabs-dev/cashflow-forecasting-engine

Why I Built This

If you've ever tried to answer questions like:

  • "How much cash will we have in 60–90 days?"
  • "Can we afford to hire 5 more people?"
  • "What if our growth slows down 20% next quarter?"
  • "What if we delay AP payments or speed up AR collections?"

…you've probably dealt with:

  • Messy, fragile Excel models
  • Multiple disconnected systems (bank, accounting, payroll, cards, etc.)
  • Zero "scenario" capability without hacking more formulas
  • Cash crunches discovered too late

I wanted a repeatable, API-driven way to answer those questions—something that looks and feels like an internal product, not a spreadsheet someone built in a weekend.

So I built this 90-Day Cash Flow Intelligence Engine as a public project:

  • n8n for automation & orchestration
  • Supabase as the warehouse / truth layer
  • React for the dashboard and case study UI

This post is the technical deep-dive into how it all works.

High-Level Architecture

Here's the mental model:

1. Data Sources

  • Bank transactions (or Plaid)
  • AR invoices (QuickBooks/Xero/etc.)
  • AP bills (same)
  • Payroll runs (Gusto/Deel/etc.)
  • Operating expenses (card feeds, accounting)

2. Automation Layer (n8n)

  • Workflows to pull & normalise data into Supabase
  • Aggregation to daily cash snapshots
  • Forecast engine for 90-day projections
  • Scenario runner (what-if simulations)
  • Risk alerts (runway below threshold)

3. Data Warehouse (Supabase / Postgres)

  • Normalised tables for each source
  • Derived tables: cash events, daily snapshots, forecasts, scenarios, alerts

4. Dashboard (React)

  • Overview: cash & runway
  • Scenarios: baseline vs what-if
  • Working capital: AR/AP aging
  • Case study: narrative, diagrams, and links

Architecture Diagram (Conceptual)

🖼️ Architecture diagram that lays out the system design.

Cash Flow Engine Architecture

Text version:

  • n8n pulls from external APIs and/or seed data
  • n8n writes into Supabase tables
  • n8n also writes derived data (snapshots, forecasts, alerts)
  • React Dashboard uses Supabase JS client to query and render the UI
  • Alerts can be pushed to Slack/email directly from n8n

Tech Stack

  • Automation: n8n
  • Database / API: Supabase (Postgres)
  • Frontend: React + TypeScript + Tailwind + (e.g.) Recharts
  • Backend logic: Python inside n8n Code nodes
  • (Optional) Providers:
    • QuickBooks / Xero (AR/AP)
    • Stripe (payments)
    • Gusto (payroll)
    • Plaid (banking)

Repository Structure

Here's how the repo is organised:

.
├─ README.md
├─ LICENSE
├─ .env.example
│
├─ sql/
│  ├─ schema.sql                  # Supabase schema
│  ├─ seed_sample_data.sql        # 12+ months of sample data
│
├─ n8n/
│  ├─ cashflow_data_sync_supabase.json
│  ├─ cashflow_aggregation_daily.json
│  ├─ cashflow_forecast_engine.json
│  ├─ cashflow_scenario_runner.json
│  ├─ cashflow_risk_alerts.json
│  ├─ cashflow_extended_data_sync_apis.json
│
├─ dashboard-react/
│  ├─ package.json
│  ├─ src/
│  │  ├─ App.tsx
│  │  ├─ lib/supabaseClient.ts
│  │  ├─ components/
│  │  │  ├─ dashboard/
│  │  │  ├─ scenarios/
│  │  │  └─ working-capital/
│  │  └─ pages/ or routes/
│
└─ docs/
   ├─ 01-overview.md
   ├─ 02-architecture.md
   ├─ 03-supabase-setup.md
   ├─ 04-n8n-workflows.md
   ├─ 05-external-apis-and-mapping.md
   └─ 06-dashboard-walkthrough.md
Enter fullscreen mode Exit fullscreen mode

Supabase: Data Model

Core Ideas

I split the schema into layers:

1. Raw source tables

  • bank_transactions
  • ar_invoices
  • ap_bills
  • payroll_runs
  • operating_expenses

2. Unified cash events

  • cash_events (everything becomes "cash in/out on date X, from source Y")

3. Daily aggregates

  • daily_cash_snapshots

4. Forecasts & scenarios

  • forecast_runs
  • cash_forecasts
  • scenario_runs
  • scenario_forecast_deltas

5. Alerts

  • alert_events

Example: bank_transactions

Each row represents a transaction:

CREATE TABLE bank_transactions (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id       uuid NOT NULL,
  bank_account_id  uuid NOT NULL,
  data_source_id   uuid NOT NULL,          -- which provider
  external_id      text NOT NULL,          -- provider ID
  transaction_date date NOT NULL,
  amount           numeric(18, 2) NOT NULL,
  currency         text NOT NULL DEFAULT 'USD',
  description      text,
  category         text,
  metadata         jsonb,
  created_at       timestamptz DEFAULT now()
);
Enter fullscreen mode Exit fullscreen mode

Similar patterns exist for ar_invoices, ap_bills, payroll_runs, and operating_expenses.

Unified: cash_events

The cash_events table is the central "ledger" that normalises all sources into a single format:

CREATE TABLE cash_events (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id       uuid NOT NULL,
  event_date       date NOT NULL,
  direction        text NOT NULL,         -- 'in' or 'out'
  amount           numeric(18, 2) NOT NULL,
  currency         text NOT NULL DEFAULT 'USD',
  source_type      text NOT NULL,         -- 'bank', 'AR', 'AP', 'payroll', 'opex'
  source_table     text NOT NULL,
  source_row_id    uuid NOT NULL,
  data_source_id   uuid NOT NULL,
  metadata         jsonb,
  created_at       timestamptz DEFAULT now()
);
Enter fullscreen mode Exit fullscreen mode

Everything else uses this table as the single source of truth.

Daily Cash Snapshots

The daily_cash_snapshots table pre-aggregates data for dashboards:

CREATE TABLE daily_cash_snapshots (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id       uuid NOT NULL,
  date             date NOT NULL,
  opening_balance  numeric(18, 2) NOT NULL,
  cash_in          numeric(18, 2) NOT NULL,
  cash_out         numeric(18, 2) NOT NULL,
  net_cash         numeric(18, 2) NOT NULL,
  closing_balance  numeric(18, 2) NOT NULL,
  burn_7d_avg      numeric(18, 2),
  burn_30d_avg     numeric(18, 2),
  metadata         jsonb,
  created_at       timestamptz DEFAULT now()
);
Enter fullscreen mode Exit fullscreen mode

This makes the dashboard queries fast and simple.

Forecasts

A forecast_run defines a run; cash_forecasts stores per-day values:

CREATE TABLE forecast_runs (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id   uuid NOT NULL,
  scenario_id  uuid,
  run_label    text NOT NULL,  -- e.g. 'baseline', 'Hire 5 engineers'
  run_at       timestamptz NOT NULL DEFAULT now(),
  assumptions  jsonb
);

CREATE TABLE cash_forecasts (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  forecast_run_id     uuid NOT NULL REFERENCES forecast_runs(id),
  company_id          uuid NOT NULL,
  date                date NOT NULL,
  base_inflows        numeric(18, 2),
  base_outflows       numeric(18, 2),
  base_net_cash       numeric(18, 2),
  base_closing_balance numeric(18, 2),
  best_closing_balance numeric(18, 2),
  worst_closing_balance numeric(18, 2),
  metadata            jsonb,
  created_at          timestamptz DEFAULT now()
);
Enter fullscreen mode Exit fullscreen mode

Scenarios and alerts sit on top of this.

n8n Workflows: The Automation Layer

Let's go through each workflow and what it does.

1. Data Sync → Supabase

File: n8n/cashflow_data_sync_supabase.json

Goal:
Make sure Supabase has up-to-date raw data from bank, AR, AP, payroll, and expenses (or from sample data).

High-level steps:

  1. Cron node – run daily at e.g. 01:00
  2. One branch per data source:
    • Option A: Pull from existing Supabase "staging" tables
    • Option B: Call external APIs (QuickBooks, Stripe, etc.)
  3. Code nodes – map provider-specific responses to internal shapes
  4. Supabase nodes – insert or upsert into:
    • bank_transactions
    • ar_invoices
    • ap_bills
    • payroll_runs
    • operating_expenses
  5. Optionally, another node maps these into cash_events

Screenshot placeholder:

n8n - Data Sync to Supabase

2. Daily Aggregation

File: n8n/cashflow_aggregation_daily.json

Goal:
Take raw cash_events and compute daily_cash_snapshots.

High-level steps:

  1. Trigger (via Cron or "Execute Workflow" from Data Sync)
  2. Supabase select:
    • Pull yesterday's cash_events (or a date range)
  3. Code node:
    • For each day:
      • Calculate opening_balance, cash_in, cash_out, net_cash, closing_balance
      • Optionally compute rolling burn (7/30-day)
  4. Supabase insert:
    • Write rows into daily_cash_snapshots

Why this matters:
Instead of recalculating daily balances on every dashboard load, we pre-aggregate.

Screenshot placeholder:

n8n - Daily Aggregation

3. Forecast Engine

File: n8n/cashflow_forecast_engine.json

Goal:
Generate a 90-day cash forecast (baseline).

High-level steps:

  1. Trigger (e.g. 03:00 daily)
  2. Supabase select:
    • Fetch last N days from daily_cash_snapshots (say 90–180 days)
  3. Code node (Python):
    • Estimate "typical" inflows & outflows
    • Compute growth assumptions (could be constant, linear, or user-defined)
    • For each of the next 90 days:
      • Generate base inflows/outflows and closing balance
      • Derive best/worst case (e.g. plus/minus 1 standard deviation)
  4. Supabase insert:
    • Create a forecast_runs row with run_label = 'baseline'
    • Insert 90 rows into cash_forecasts linked to that run

This is intentionally an approachable heuristic model.
You can swap it later for ARIMA, Prophet, or ML without changing the schema.

Screenshot placeholder:

n8n - Forecast Engine

4. Scenario Runner

File: n8n/cashflow_scenario_runner.json

Goal:
Let the user run "what-if" scenarios like:

  • Hire 5 engineers
  • Growth slows 20%
  • Cut marketing by 30%

How it works:

  1. Webhook node:
    • Accepts JSON body from the React dashboard: 
{
  "scenario_name": "Hire 5 Engineers",
  "growth_adjustment": -0.05,
  "additional_monthly_payroll": 25000
}
Enter fullscreen mode Exit fullscreen mode

  1. Supabase select:

    • Fetch the latest baseline forecast_run and its cash_forecasts

  2. Code node:

    • Apply adjustments:
      • Add extra outflows for additional_monthly_payroll
      • Adjust inflows based on growth_adjustment
    • Recalculate closing balances

  3. Supabase insert:

    • New row in scenario_runs with parameters and base run ID
    • Insert scenario-specific cash_forecasts rows (tagged with scenario_id)
    • Optionally compute and save scenario_forecast_deltas

Now the dashboard can pull baseline vs scenario forecasts and compare them.

Screenshot placeholder:

n8n - Scenario Runner

5. Risk Alerts

File: n8n/cashflow_risk_alerts.json

Goal:
Detect when projected cash drops below a threshold and log/send alerts.

High-level steps:

  1. Cron (e.g. 04:00 daily)
  2. Supabase select:
    • Get the latest baseline forecast_run and its cash_forecasts
  3. Code node:
    • Find the first date where base_closing_balance falls below:
      • 0 or
      • some_threshold (e.g. 2 months of current burn)
    • Compute runway in days
  4. If runway < configured threshold:
    • Insert row in alert_events:
      • alert_type, severity, message, details
    • Optionally send:
      • Slack message
      • Email

Screenshot placeholder:

n8n - Risk Alerts Workflow

6. External APIs (Stripe, QuickBooks, Gusto, Plaid)

File: n8n/cashflow_extended_data_sync_apis.json

Goal:
Show that this engine can talk to real providers, not just sample data.

Each provider branch looks like:

  1. HTTP Request node:

    • Calls the provider API (e.g. Stripe /v1/charges, QuickBooks Invoice/Bill query, Gusto payrolls, Plaid /transactions/get)

  2. Code node:

    • Transform the provider payload into internal shape

  3. Supabase node:

    • Insert into bank_transactions, ar_invoices, ap_bills, or payroll_runs

This is your portfolio evidence that you can integrate real systems.

Screenshot placeholder:

n8n - External APIs Data Sync

React Dashboard: Turning Data into Insight

The dashboard lives in dashboard-react/ and is the "face" of the engine.

Dashboard Overview

Overview Page (/)

Goal: Answer "Are we safe?" in one glance.

It shows:

  1. KPI Cards

    • Current cash balance (latest daily_cash_snapshots.closing_balance)
    • Runway (Base)
    • Runway (Worst)
    • Next 30-day net cash

  2. Main Chart

    • Historical cash (line from daily_cash_snapshots)
    • Forecasted cash (line from baseline cash_forecasts)
    • Optional band for best/worst case

  3. Alerts Panel

    • List of recent alert_events
    • Severity tags (info/warning/critical)
    • Human-readable messages

Data fetching example:

const { data: snapshots } = await supabase
  .from('daily_cash_snapshots')
  .select('*')
  .eq('company_id', companyId)
  .order('date', { ascending: true });

const { data: baselineRun } = await supabase
  .from('forecast_runs')
  .select('*')
  .eq('company_id', companyId)
  .eq('run_label', 'baseline')
  .order('run_at', { ascending: false })
  .limit(1)
  .single();

const { data: baselineForecast } = await supabase
  .from('cash_forecasts')
  .select('*')
  .eq('forecast_run_id', baselineRun.id)
  .order('date', { ascending: true });
Enter fullscreen mode Exit fullscreen mode

Scenarios Page (/scenarios)

Dashboard Scenario

Goal: Answer "What if…?"

It includes:

  1. Scenario Selector

    • Baseline vs scenarios (scenario_runs table)

  2. Comparison Chart

    • Baseline vs selected scenario base_closing_balance curves

  3. Impact Summary

    • Runway difference (days)
    • Minimum cash difference
    • Date of minimum cash per scenario

  4. Scenario Form

    • Input fields for growth adjustment, extra payroll, etc.
    • On submit, it calls the n8n Scenario Runner webhook

HTTP call example from the front-end:

await fetch(import.meta.env.VITE_SCENARIO_WEBHOOK_URL, {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    scenario_name,
    growth_adjustment,
    additional_monthly_payroll,
  }),
});
Enter fullscreen mode Exit fullscreen mode

Once the workflow completes, the UI refreshes the list of scenarios from scenario_runs.

Working Capital Page (/working-capital)

Dashboard Working Capital

Goal: Show where cash is tied up.

Sections:

  1. AR / AP KPIs:

    • Total AR (open invoices)
    • Total AP (open bills)
    • Net working capital (AR − AP)

  2. AR Aging:

    • 0–30, 31–60, 61–90, 90+ days

  3. AP Aging:

    • Same buckets for AP

Data can come from:

  • A view like vw_working_capital_summary, or
  • Direct aggregations over ar_invoices and ap_bills

Case Study Page (/case-study)

This page is mostly content:

  • Problem statement
  • Architecture diagram
  • Key metrics (time saved, earlier detection of crunches)
  • Links to:
    • GitHub repo
    • This dev.to article
    • Any demo video / Substack write-up

This is meant to be portfolio friendly: you can send this page to a client/investor.

Putting It All Together

Here's the lifecycle in practice:

1. Nightly Data Sync (n8n)

  • Ingest raw financial data from external APIs or seed
  • Normalize and insert into Supabase

2. Daily Aggregation + Forecasting (n8n)

  • Compute daily_cash_snapshots
  • Run the forecasting engine to create a 90-day baseline

3. Risk Check (n8n)

  • Evaluate runway; log alerts; ping Slack/email if needed

4. User Exploration (React + Supabase)

  • Open dashboard overview:
    • Check cash & runway
  • Go to scenarios:
    • Try "hire X people" or "growth –20%"
  • Check working capital:
    • See if AR/AP is part of the problem

5. Decision

  • Adjust hiring, spend, or fundraising strategy with data

How You Can Use or Adapt This

A few ways you might repurpose this:

For your own startup

Plug in your real QuickBooks/Stripe/Gusto/Plaid credentials and get a bespoke cash engine.

As a consulting offering

Offer "Cash Flow Intelligence Setup" for SaaS/fintech clients.
Show this repo + dashboards as your portfolio/work samples.

As a learning project

Learn n8n, Supabase and React in a real domain (FinOps, not to-do lists).

Setup (Quick Recap)

  1. Clone the repo
  2. Create a Supabase project; run sql/schema.sql and sql/seed_sample_data.sql
  3. Import n8n workflows from /n8n
  4. Configure Supabase credentials in n8n
  5. Run workflows manually, then enable cron
  6. Configure .env.local in dashboard-react and run the app

Closing Thoughts

This project started as a way to productise something founders and finance teams already do manually—cash forecasting—but in a way that is:

  • ✅ Automated
  • ✅ Observable
  • ✅ Scenario-driven
  • ✅ Developer-friendly

If you're into:

  • FinOps / financial automation
  • No-code/low-code with real engineering patterns
  • Turning workflows into actual products

…then this pattern (n8n + Supabase + React) is extremely powerful.

If you fork this, adapt it, or use it with a real company, I'd love to hear what you build. 🙌

Links

  • 📁 GitHub repo: Link
  • 👤 My Dev.to: Link

You might also love this builds

  • 📝 I Built an Automated Payment Recovery System That Recovers 3x More Failed Payments: Read here

  • 📝 I Built a System That Finds $663K+ in Lost Revenue - Here's the Complete Technical Breakdown: Read here

Feel free to drop questions or suggestions in the comments below! 💬

P.S. — I'm building more financial automation systems like this for my credit card company. Follow me for more breakdowns of what I build.

Top comments (0)