The problem
I own the full accounts receivable and collections cycle at a venture-backed medical device company that grew from $5M to $16M in annual revenue in a single year. The account base grew with it, from a manageable list to 370+ active customers spread across four sales regions.
The process that worked at $5M did not survive that growth. Every Monday meant opening a fresh aging report, manually working out which Regional Sales Director owned each delinquent account, assembling a separate email for each of them, and trying to remember what had already been said to whom. Nothing was tracked except in my own head and a spreadsheet. Accounts that needed escalation got escalated when I happened to notice them.
The failure mode was not laziness or missing effort. It was that the volume had outgrown the method, and no amount of working harder inside the old process was going to fix a structural problem.
What I built
A single-tenant web application that ingests the weekly aging report and turns it into a routed, tracked, auditable collections workflow.
Ingest. Drop in the weekly aging report CSV. The parser reads it, diffs it against the previous week, and identifies what is new, what moved between aging buckets, and what resolved.
Route. Every invoice is matched to its owning Regional Sales Director through a two-stage resolution: exact customer ID match first, then normalized name match as a fallback. Accounts that fail both are not silently dropped. They surface in a dedicated "Needs Setup" queue with the specific reason they failed, so a routing gap is visible rather than invisible.
Communicate. The system drafts one consolidated Monday email per RSD rather than a scattered set of one-off messages, grouped into escalation tiers (Action Required, Formal Notices, Input Needed, Collections Watch). Every send is logged.
Track. A complete activity log per account: calls, voicemails, emails, promises to pay, and the follow-up dates attached to them. The 120-day escalation checklist auto-populates from that log, so an account cannot sit at the final escalation stage with an unanswered prerequisite.
Report. Weekly metrics reporting on DSO, BPDSO, Collection Gap, and Average Collection Time, generated as PDFs for executive review.
Technical decisions worth explaining
Routing failures carry partial identity forward
The most instructive bug in the system was one I designed around rather than fixed after the fact.
When an account fails to route, there are three distinct reasons: no location record matched at all, the location matched but carries no region tag, or the region exists but has no active sales director assigned. The naive implementation returns a uniform null on all three.
That is wrong, and quietly so. An account whose CRM record has no region tag still has a real CRM record. Nulling its identity on the routing-failure path would exclude it from downstream exports, which means the accounts already flagged as needing attention would be exactly the ones silently missing from the reports meant to surface them.
export function routeWithContext(ctx, customerId, customerName) {
let loc = ctx.byExternalId.get(customerId)
let method = 'external_id'
if (!loc) {
loc = ctx.byNameLower.get((customerName || '').trim().toLowerCase())
method = 'name'
}
// Identity and routing are separate questions. An account whose CRM record
// carries no region tag still has a real CRM record, and its activity should
// still export. Nulling that ID on the failure paths below would silently
// exclude exactly the accounts that already need attention.
if (!loc) return {
rsd_id: null, region: null, method: null, crm_location_id: null,
reason: 'No CRM location match (no ID match, no exact name match)'
}
if (!loc.region) return {
rsd_id: null, region: null, method, crm_location_id: loc.crm_id || null,
reason: `CRM location "${loc.name}" has no region tag`
}
const rsdId = ctx.rsdByRegion.get(loc.region)
if (!rsdId) return {
rsd_id: null, region: loc.region, method, crm_location_id: loc.crm_id || null,
reason: `Region "${loc.region}" has no active director`
}
return { rsd_id: rsdId, region: loc.region, method, crm_location_id: loc.crm_id, reason: null }
}Each failure path returns a specific, human-readable reason. That reason renders directly in the Needs Setup queue, so the person resolving it knows what to fix instead of guessing.
A production incident that changed the write pattern
The first version of the CSV importer did the obvious thing: loop over rows, look up what each row needs, write it. It worked on test data and failed on a real-sized report, hitting the platform's cap on outbound calls per invocation.
The rewrite inverted the pattern. Preload every reference table once before the loop, resolve routing in memory, then write in batched chunks rather than per row.
// Preload everything the loop needs so nothing inside it hits the DB per row.
// The per-row pattern is what triggered "too many API requests by a single
// invocation" on a real-sized report.
const [overrides, prevInvoices, existingCustomers, routingCtx] = await Promise.all([
getOverrides(db),
db.prepare(`SELECT ... WHERE source = 'aging_report' AND status != 'resolved'`).all(),
db.prepare('SELECT id FROM customers').all(),
buildRoutingContext(db)
])
async function runBatched(db, stmts, chunkSize = 150) {
for (let i = 0; i < stmts.length; i += chunkSize) {
await db.batch(stmts.slice(i, i + chunkSize))
}
}The lesson generalizes past this codebase: a pattern that is merely slow on test data can be a hard failure on production volume, and the fix is usually to move the work out of the loop rather than to optimize inside it.
Baseline import detection
The first-ever import has no previous week to compare against. Without special handling, every open invoice would report as "new this week" and every account would appear to have skipped its escalation steps.
The system detects the first import, flags it as a baseline snapshot, suppresses the new-this-week diff, and offers a one-time action to backdate prior contact history so accounts already discussed before the tool existed are not permanently blocked on a checklist item nobody could satisfy retroactively. That action is only available while the baseline is still the most recent snapshot, which is a deliberate guardrail against applying it to real data later.
Architecture
Single serverless worker serving a React front end, a REST API, and a Model Context Protocol endpoint from one deployment. SQLite-compatible edge database. Nineteen tables with versioned migrations.
Authentication is enforced before anything is served, including static assets. Password plus TOTP second factor, HMAC-signed HttpOnly session cookies. Failed logins return a generic error with a fixed delay so the response does not reveal which factor was wrong. Deployed without credentials configured, the application serves nothing at all rather than falling open.
The MCP endpoint implements the full OAuth 2.1 flow (discovery, dynamic client registration, PKCE, refresh tokens) so the system can be queried and updated in natural language through an AI assistant, with one-time-use authorization codes and revocation by secret rotation.
Result
The weekly collections cycle went from a manual half-day rebuild to an import and a review. Escalation happens on a defined cadence rather than when someone notices. Every communication has a record. Routing gaps are visible as a queue rather than invisible as an omission.
More to the point of how I work: the original problem was described to me as a performance issue. It was a structural one. The distinction mattered, because you cannot work harder out of a process that does not scale, you can only rebuild it.