I Built a System That Found $663K in Lost Revenue
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
- System Architecture
- Tech Stack
- Database Schema
- Detection Logic
- n8n Workflows
- Implementation Guide
- Results
- Lessons Learned
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 │
└─────────────────────────────────────────────────┘
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()
);
[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';
}
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;
}
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 };
}
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]
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;
[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]
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 } }];
[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
2. Run Database Schema
# In Supabase SQL Editor
# Copy/paste schema.sql
# Run all CREATE TABLE statements
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
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
- Import
01-stripe-data-sync.json
- Test with Stripe test mode
- Verify data in Supabase
- Schedule for 6 AM daily
Day 4: Usage Collection
- Import
02-usage-collection.json
- Connect to your product API
- Test with sample usage data
- Schedule for 7 AM daily
Day 5: Detection Logic
- Import
03-detect-outdated-pricing.json
- Create test scenarios
- Verify detections created
- 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',
'[email protected]',
'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
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;
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
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';
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
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)
- n8n workflows (importable JSON)
- Detection algorithms (JavaScript)
- Setup guide
- Test data
Repository and Documentation: github.com/Etherlabs-dev/revenue_leakage_system
Questions?
Drop a comment below or:
- GitHub Issues: Report bugs or request features
- Email: [email protected]
- Twitter: @chukwuaugustus
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.