DEV Community

Cover image for I Built a System That Finds $663K+ in Lost Revenue - Here's the Complete Technical Breakdown
Ugo Chukwu
Ugo Chukwu

Posted on

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

#financialautomation

Here's the Complete Technical Breakdown

Last month, I deployed an automated revenue leakage detection system for a B2B SaaS company.

The result: $663,000 recovered in the first year.

Today I'm sharing the complete technical architecture, code, and lessons learned so you can build this yourself.

Table of Contents

The Problem

B2B SaaS companies with usage-based pricing face systematic revenue leakage:

4 Types of Leakage:

1. Outdated Pricing

  • Customer signed up in 2022 at $99/month
  • Pricing increased to $149/month in 2023
  • Customer never migrated
  • Loss: $50/month per customer

2. Missing Overages

  • Plan includes 10,000 API calls
  • Customer uses 25,000 calls
  • Overage charges not applied
  • Loss: $75/month (15K calls × $5/1000)

3. Incorrect Discounts

  • Customer gets 15% "annual commitment" discount
  • But they're actually on monthly billing
  • Loss: $22/month (15% of $149)

4. Feature Leakage

  • Customer on Basic plan ($49/month)
  • Has premium features enabled ($199/month plan)
  • Loss: $150/month

The Scale of the Problem

  • Average B2B SaaS loses 10-15% of revenue to leakage
  • Manual audits cover only 10-20% of customers
  • Detection lag: 2-3 months on average
  • 80-90% of leakage goes undetected

For a company with $5M ARR:

  • Potential annual leakage: $500K-750K
  • With 100% automated detection: $400K-600K recoverable

System Architecture

I designed this as 5 interconnected layers:

┌─────────────────────────────────────────────────┐
│         LAYER 1: DATA COLLECTION                │
│                                                  │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐     │
│  │  Stripe  │  │ Product  │  │ Contract │     │
│  │   API    │  │   API    │  │   Data   │     │
│  └────┬─────┘  └────┬─────┘  └────┬─────┘     │
│       └─────────────┴──────────────┘            │
└──────────────────────┬──────────────────────────┘
                       ▼
┌─────────────────────────────────────────────────┐
│         LAYER 2: DATABASE (Supabase)            │
│                                                  │
│  customers | contract_pricing | actual_charges  │
│  product_usage | pricing_rules | detections     │
└──────────────────────┬──────────────────────────┘
                       ▼
┌─────────────────────────────────────────────────┐
│         LAYER 3: VALIDATION ENGINE (n8n)        │
│                                                  │
│  ┌──────────────┐  ┌──────────────┐            │
│  │   Pricing    │  │   Overages   │            │
│  │   Detector   │  │   Detector   │            │
│  └──────────────┘  └──────────────┘            │
└──────────────────────┬──────────────────────────┘
                       ▼
┌─────────────────────────────────────────────────┐
│         LAYER 4: ALERTING                       │
│                                                  │
│  Slack Notifications | Email Reports            │
└──────────────────────┬──────────────────────────┘
                       ▼
┌─────────────────────────────────────────────────┐
│         LAYER 5: RECOVERY WORKFLOW              │
│                                                  │
│  Investigation Queue | Customer Communication   │
└─────────────────────────────────────────────────┘
Enter fullscreen mode Exit fullscreen mode

Tech Stack

Core Technologies

Database: Supabase (Postgres)

  • Why: Managed Postgres with real-time subscriptions
  • Cost: $0-25/month (free tier sufficient for testing)
  • Alternative: Plain Postgres + RLS manually

Automation: n8n

  • Why: Visual workflows, self-hostable, no vendor lock-in
  • Cost: $0/month (self-hosted) or $20-50/month (cloud)
  • Alternative: Zapier, Make.com, or custom Python scripts

Billing Integration: Stripe API

  • Why: Most common billing platform for SaaS
  • Works with: Chargebee, Recurly, or custom billing

Alerts: Slack API

  • Why: Real-time notifications where team already works
  • Alternative: Email, Discord, Teams

Infrastructure

  • Hosting: Self-hosted on $20/month VPS
  • Cost: ~$50/month total
  • Scales to: 1,000+ customers easily

Database Schema

Core Tables 

-- 1. CUSTOMERS (baseline data)
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    stripe_customer_id TEXT UNIQUE NOT NULL,
    email TEXT,
    name TEXT,
    current_plan TEXT,
    current_price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. CONTRACT PRICING (what they SHOULD pay)
CREATE TABLE contract_pricing (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID REFERENCES customers(id),
    plan_name TEXT NOT NULL,
    base_price DECIMAL(10,2) NOT NULL,
    included_usage JSONB,
    overage_rates JSONB,
    discount_percent DECIMAL(5,2),
    effective_date DATE NOT NULL
);

-- Example row:
-- {
--   "plan_name": "pro",
--   "base_price": 149.00,
--   "included_usage": {
--     "api_calls": 50000,
--     "storage_gb": 100
--   },
--   "overage_rates": {
--     "api_calls_per_1000": 5.00,
--     "storage_gb": 2.00
--   }
-- }

-- 3. ACTUAL CHARGES (what they ARE paying)
CREATE TABLE actual_charges (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID REFERENCES customers(id),
    stripe_invoice_id TEXT,
    billing_period_start DATE,
    base_charge DECIMAL(10,2),
    overage_charges DECIMAL(10,2),
    total_charged DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 4. PRODUCT USAGE (actual usage data)
CREATE TABLE product_usage (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID REFERENCES customers(id),
    usage_period_start DATE,
    usage_data JSONB,  -- {"api_calls": 75000, "storage_gb": 150}
    created_at TIMESTAMP DEFAULT NOW()
);

-- 5. LEAKAGE DETECTIONS (violations found)
CREATE TABLE leakage_detections (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID REFERENCES customers(id),
    detection_date DATE DEFAULT CURRENT_DATE,
    leakage_type TEXT,  -- 'outdated_pricing', 'missing_overage'
    severity TEXT,      -- 'low', 'medium', 'high'
    monthly_leakage_amount DECIMAL(10,2),
    description TEXT,
    status TEXT DEFAULT 'new',
    created_at TIMESTAMP DEFAULT NOW()
);

-- 6. PRICING RULES (business rules)
CREATE TABLE pricing_rules (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    plan_name TEXT NOT NULL,
    current_base_price DECIMAL(10,2) NOT NULL,
    included_usage JSONB,
    overage_rates JSONB,
    updated_at TIMESTAMP DEFAULT NOW()
);
Enter fullscreen mode Exit fullscreen mode

[Full schema with indexes and constraints on GitHub]

Detection Logic

1. Outdated Pricing Detection

Concept: Compare contract price vs. actual charge

// Simplified logic
function detectOutdatedPricing(customer, pricingRule, actualCharge) {
  const shouldPay = pricingRule.current_base_price;
  const actuallyPaying = actualCharge.base_charge;
  const difference = shouldPay - actuallyPaying;

  if (difference > 1) { // Meaningful threshold
    return {
      leakageDetected: true,
      type: 'outdated_pricing',
      monthlyLeakage: difference,
      severity: calculateSeverity(difference),
      description: `Customer should pay $${shouldPay}/mo but is paying $${actuallyPaying}/mo`
    };
  }

  return { leakageDetected: false };
}

function calculateSeverity(amount) {
  if (amount > 500) return 'high';
  if (amount > 100) return 'medium';
  return 'low';
}
Enter fullscreen mode Exit fullscreen mode

Edge Cases Handled:

  • Mid-cycle pricing changes (prorated)
  • Grandfathered plans (flagged separately)
  • Currency conversions
  • Trial periods

2. Missing Overages Detection

Concept: Calculate expected vs. actual overage charges

function detectMissingOverages(customer, contract, usage, actualCharge) {
  const included = contract.included_usage;
  const rates = contract.overage_rates;
  const actual = usage.usage_data;

  let expectedOverages = 0;
  let details = [];

  // Check each usage metric
  Object.keys(included).forEach(metric => {
    if (actual[metric] > included[metric]) {
      const excess = actual[metric] - included[metric];
      const charge = calculateOverageCharge(metric, excess, rates);

      expectedOverages += charge;
      details.push({
        metric,
        included: included[metric],
        actual: actual[metric],
        excess,
        charge
      });
    }
  });

  const actualOverageCharged = actualCharge.overage_charges || 0;
  const difference = expectedOverages - actualOverageCharged;

  if (difference > 1) {
    return {
      leakageDetected: true,
      type: 'missing_overage',
      expectedOverage: expectedOverages,
      actualCharged: actualOverageCharged,
      monthlyLeakage: difference,
      details: details
    };
  }

  return { leakageDetected: false };
}

function calculateOverageCharge(metric, excess, rates) {
  // Example: API calls charged per 1,000
  if (metric === 'api_calls') {
    return Math.ceil(excess / 1000) * rates.api_calls_per_1000;
  }

  // Example: Storage charged per GB
  if (metric === 'storage_gb') {
    return excess * rates.storage_gb;
  }

  return 0;
}
Enter fullscreen mode Exit fullscreen mode

Edge Cases Handled:

  • Fractional usage (round up/down per contract)
  • Multiple usage metrics
  • Tiered pricing (first 50K calls at $5/1K, next 50K at $4/1K)
  • Grace periods (first 5% overage free)

3. Incorrect Discount Detection

Concept: Verify discount qualifications

function detectIncorrectDiscounts(customer, contract) {
  const hasDiscount = contract.discount_percent > 0;

  if (!hasDiscount) {
    return { leakageDetected: false };
  }

  // Check qualification based on reason
  let qualifies = false;

  switch (contract.discount_reason) {
    case 'annual_commitment':
      // Verify they have annual contract
      const contractLength = calculateMonths(
        contract.effective_date,
        contract.end_date
      );
      qualifies = contractLength >= 12;
      break;

    case 'volume_discount':
      // Check if usage meets threshold
      const usage = getRecentUsage(customer);
      qualifies = usage.api_calls >= 100000; // Example threshold
      break;

    case 'referral':
      // One-time only, check if applied multiple periods
      qualifies = !hasBeenAppliedBefore(customer, 'referral');
      break;
  }

  if (!qualifies) {
    const discountAmount = contract.base_price * (contract.discount_percent / 100);

    return {
      leakageDetected: true,
      type: 'incorrect_discount',
      monthlyLeakage: discountAmount,
      reason: contract.discount_reason,
      description: `Customer has ${contract.discount_percent}% discount but doesn't qualify`
    };
  }

  return { leakageDetected: false };
}
Enter fullscreen mode Exit fullscreen mode

n8n Workflows

Workflow 1: Daily Stripe Data Sync

Purpose: Pull billing data from Stripe daily

[Schedule: 6 AM Daily]
    ↓
[HTTP: GET Stripe Subscriptions]
    ↓
[Function: Transform Data]
    ↓
[Supabase: Upsert Customer]
    ↓
[Supabase: Insert Charge Record]
    ↓
[Slack: Notify Completion]
Enter fullscreen mode Exit fullscreen mode

Code in Function Node:

// Transform Stripe subscription data
const subscriptions = $input.all();
const transformed = [];

for (const item of subscriptions) {
  const sub = item.json;

  transformed.push({
    stripe_customer_id: sub.customer,
    stripe_invoice_id: sub.latest_invoice,
    plan_name: sub.plan.nickname,
    total_charged: sub.plan.amount / 100, // Convert cents to dollars
    billing_period_start: new Date(sub.current_period_start * 1000).toISOString(),
    billing_period_end: new Date(sub.current_period_end * 1000).toISOString()
  });
}

return transformed;
Enter fullscreen mode Exit fullscreen mode

[See complete workflow JSON on GitHub]

Workflow 2: Detect Outdated Pricing

Purpose: Find customers on old pricing daily

[Schedule: 8 AM Daily]
    ↓
[Supabase: Get All Customers]
    ↓
[Loop: For Each Customer]
    ↓
    [Supabase: Get Current Pricing Rule]
        ↓
    [Supabase: Get Latest Charge]
        ↓
    [Function: Compare Pricing]
        ↓
    [IF: Leakage Detected?]
        ↓ YES
        [Supabase: Insert Detection]
            ↓
        [Slack: Alert If High Severity]
Enter fullscreen mode Exit fullscreen mode

Detection Function:

const customer = $('Get All Customers').first().json;
const pricingRule = $('Get Current Pricing Rule').first().json;
const actualCharge = $('Get Latest Charge').first().json;

// What they SHOULD pay
const shouldPay = parseFloat(pricingRule.current_base_price);

// What they ARE paying
const actuallyPaying = parseFloat(actualCharge.base_charge);

// Calculate difference
const difference = shouldPay - actuallyPaying;

if (difference > 1) {
  // Determine severity
  let severity = 'low';
  if (difference > 500) severity = 'high';
  else if (difference > 100) severity = 'medium';

  return [{
    json: {
      customer_id: customer.id,
      customer_email: customer.email,
      leakage_type: 'outdated_pricing',
      severity: severity,
      monthly_leakage_amount: difference,
      should_charge: shouldPay,
      actually_charged: actuallyPaying,
      description: `Should pay $${shouldPay}/mo, currently paying $${actuallyPaying}/mo. Loss: $${difference}/mo`,
      leakage_detected: true
    }
  }];
}

return [{ json: { leakage_detected: false } }];
Enter fullscreen mode Exit fullscreen mode

[See all 5 workflows on GitHub]

Implementation Guide

Phase 1: Setup (Day 1-2)

1. Create Supabase Project

# Sign up at supabase.com
# Create new project
# Copy URL and anon key
Enter fullscreen mode Exit fullscreen mode

2. Run Database Schema

# In Supabase SQL Editor
# Copy/paste schema.sql
# Run all CREATE TABLE statements
Enter fullscreen mode Exit fullscreen mode

3. Set Up n8n

# Option A: Self-hosted (recommended for production)
docker run -it --rm \
  --name n8n \
  -p 5678:5678 \
  -v ~/.n8n:/home/node/.n8n \
  n8nio/n8n

# Option B: Cloud (easier for testing)
# Sign up at n8n.io
Enter fullscreen mode Exit fullscreen mode

4. Configure Credentials

  • Add Supabase credentials in n8n
  • Add Stripe API key (HTTP Header Auth)
  • Add Slack webhook URL

Phase 2: Build Core Workflows (Day 3-5)

Day 3: Stripe Data Sync

  1. Import 01-stripe-data-sync.json
  2. Test with Stripe test mode
  3. Verify data in Supabase
  4. Schedule for 6 AM daily

Day 4: Usage Collection

  1. Import 02-usage-collection.json
  2. Connect to your product API
  3. Test with sample usage data
  4. Schedule for 7 AM daily

Day 5: Detection Logic

  1. Import 03-detect-outdated-pricing.json
  2. Create test scenarios
  3. Verify detections created
  4. Schedule for 8 AM daily

Phase 3: Test with Real Data (Day 6-7)

Create Test Scenarios

-- Scenario 1: Customer on old pricing
INSERT INTO customers VALUES (
  gen_random_uuid(),
  'cus_test_001',
  'test@example.com',
  'Test Company',
  'pro',
  99.00  -- Old price
);

INSERT INTO pricing_rules VALUES (
  gen_random_uuid(),
  'pro',
  149.00,  -- Current price
  '{"api_calls": 50000}',
  '{"api_calls_per_1000": 5.00}'
);

-- Run detection workflow
-- Expected: 1 detection with $50/month leakage
Enter fullscreen mode Exit fullscreen mode

Verify Results

SELECT 
  c.email,
  ld.leakage_type,
  ld.monthly_leakage_amount,
  ld.description
FROM leakage_detections ld
JOIN customers c ON ld.customer_id = c.id
WHERE ld.detection_date = CURRENT_DATE;
Enter fullscreen mode Exit fullscreen mode

Phase 4: Production Deployment (Week 2)

1. Connect Real Data Sources

  • Switch from Stripe test mode to live
  • Connect actual product API
  • Import real contract data

2. Gradual Rollout

// Start with small subset
WHERE c.created_at > '2024-01-01'
LIMIT 10

// Then expand
LIMIT 50

// Then all
-- Remove LIMIT
Enter fullscreen mode Exit fullscreen mode

3. Tune Alert Thresholds

// Adjust based on false positive rate
if (difference > 1000) severity = 'critical';  // Add new tier
else if (difference > 500) severity = 'high';
else if (difference > 100) severity = 'medium';
else severity = 'low';
Enter fullscreen mode Exit fullscreen mode

4. Train Finance Team

  • Dashboard walkthrough
  • Investigation workflow
  • Customer communication templates

Results

Real Client: B2B SaaS ($5M ARR)

Implementation: 6 weeks

Team Size: 1 developer (me) + 1 finance lead (client)

Before:

  • Manual audits: 20 customers/month
  • Time spent: 40 hours/month
  • Coverage: 10% of customer base
  • Leakage found: $50K/month (in audited accounts)
  • Time to detect: 2-3 months

After:

  • Automated monitoring: 200 customers (100%)
  • Time spent: 5 hours/month (monitoring only)
  • Coverage: 100% daily
  • Leakage found: $65K/month
  • Time to detect: 1 day

Year 1 Results:

  • Total leakage detected: $780K
  • Recovery rate: 85% (early detection = easier recovery)
  • Amount recovered: $663K
  • Time saved: 420 hours (35 hours/month × 12)

ROI Calculation:

Annual Value:
- Revenue recovered: $663,000
- Time saved: 420 hrs × $200/hr = $84,000
- Total value: $747,000

Annual Cost:
- Implementation: $15,000 (one-time)
- Ongoing: $30,000 (maintenance)
- Total cost: $45,000

ROI: ($747,000 - $45,000) / $45,000 = 1,560%
Payback period: 18 days
Enter fullscreen mode Exit fullscreen mode

Lessons Learned

What Worked Well

1. Start Simple
Built outdated pricing detection first. Proved value quickly. Then expanded to other types.

2. Edge Cases Are 80% of Work
The basic logic took 2 days. Handling edge cases (prorations, currency, grace periods) took 3 weeks.

3. Prevention > Detection
Added "pre-flight checks" that catch errors BEFORE invoices go out. More valuable than post-facto detection.

4. Make It Actionable
Detections without a recovery workflow = alerts that get ignored. Built investigation queue and communication templates.

What I'd Do Differently

1. Prioritize by Recency
Initially sorted detections by amount. Should have prioritized recent leakage (easier to recover).

2. Add Customer Context
Should have included customer health score, LTV, and churn risk in alerts. Some leakage isn't worth fixing if customer is churning anyway.

3. Build Dashboard First
Spent too long on detection logic before building visibility. Should have built simple dashboard first for buy-in.

4. Document Edge Cases Earlier
Waited until implementation to document all the pricing edge cases. Should have done comprehensive discovery upfront.

Common Pitfalls

False Positives Kill Trust

Initial version flagged legitimate pricing variations as leakage. After 2-3 false positives, finance team stopped trusting alerts.

Solution: Added "confidence score" and manual review queue for medium confidence detections.

Over-Engineering

Tried to detect every possible leakage type simultaneously. Got overwhelmed.

Solution: Ship one detection type, prove value, iterate.

Ignoring Data Quality

Assumed Stripe data was perfect. It's not. Found:

  • Duplicate subscriptions (from migrations)
  • Orphaned subscriptions (customer deleted)
  • Incorrect metadata (from manual overrides)

Solution: Added data quality checks before detection runs.

Next Steps

Improvements I'm Building

1. Historical Analysis
Currently only detects ongoing leakage. Adding:

  • "How much did we lose in past 12 months?"
  • "What % of leakage is each type?"
  • Trend analysis

2. Customer Communication Automation
Manual communication is bottleneck. Building:

  • Automatic "pricing update" emails for outdated pricing
  • Self-service portal for customers to update payment method
  • Proactive alerts ("you're approaching overage limit")

3. Recovery Optimization
Not all leakage is worth recovering. Adding:

  • Customer health score integration
  • Churn risk consideration
  • Recovery ROI prediction

4. Dashboard UI
Currently using Supabase Studio. Building:

  • Custom React dashboard
  • Real-time leakage ticker
  • Drill-down by customer/type/severity
  • Export to CSV for finance

Get the Code

Everything is open-source on GitHub:

  • Database schema (SQL)database/schema.sql
  • n8n workflows (importable JSON)n8n-workflows/*.json
  • Detection algorithms (JavaScript)
  • Setup guide: docs/setup-guide.md
  • Test data: database/sample-data.sql

Repository and Documentation: github.com/Etherlabs-dev/revenue_leakage_system

Questions?

Drop a comment below or:

About Me

I run a credit card company with 200+ users. Built this system for my own company first.

Now I build financial automation systems for B2B SaaS companies.

Follow for more financial automation tutorials.

Top comments (0)