Capacity-Aware CRM Deal Routing
A workflow-automation system that replaced manual sales-lead assignment with capacity-aware routing across two CRM portals. Instead of simple round-robin, the engine calculates each team member's projected workload over their next five working days — factoring in open deals weighted by pipeline stage, outstanding tasks, real working hours, and time off — and routes each new opportunity to whoever genuinely has capacity, queueing and escalating to a manager when the whole team is at capacity. Built with exactly-once guarantees, fail-closed error handling, authenticated webhooks, and a spreadsheet console so managers can change the rules without touching code. Shipped with 115 automated tests and a staged rollout plan with rollback at every level.
The problem
New deals arrived in the CRM with no owner and were assigned by hand. Manual assignment can't see workload — someone with fifteen open deals and a stack of overdue tasks gets the same share as someone with three. Working hours, availability and time off weren't considered at all. And with two portals sharing some staff, a person's load in one pool genuinely reduces their capacity in the other, so round-robin was never going to work. There was no record of why a deal went to a given person, and no alert when deals sat unassigned.
What was built
Five coordinated workflows, a shared assignment engine, five data tables, a six-tab manager spreadsheet, and 17 additive CRM properties.
- Intake — verifies the CRM's webhook signature, derives which pool the deal belongs to, protects any deal a human already claimed, dedupes redelivered events, and claims the event atomically before touching anything.
- Dispatcher — every five minutes in business hours: builds one capacity snapshot per batch, converts each person's open deals and tasks into minutes of work, divides by their real usable minutes after time off, and assigns to the lowest projected utilisation under 100%.
- Booking sync — reflects real-world booking state back onto deals.
- Daily reconciliation — missed deals, ownership drift, stuck queues, unmapped rules, stale cursors, and execution-budget risk, emailed as an operations report.
- Config publisher — validates the manager's spreadsheet and swaps the entire configuration atomically; one bad cell rejects the whole publish and leaves the last valid config running.
The hard part
Two engineering problems worth naming.
Exactly-once with no concurrency control. The hosting platform can't set per-workflow concurrency to 1, so nothing stopped two dispatcher runs overlapping and double-assigning. Read-then-update locking races with itself. The solution was an insert-then-verify-lowest-row-id claim: every run inserts a claim row for the window, reads back, and only the run holding the lowest live row ID proceeds — all others exit before any CRM access.
An API error is never an empty workload. The original build, on a failed workload search, continued with partial data — meaning a transient error could cause a genuinely wrong assignment. It now aborts that pool for the cycle, logs the error, and retries next cycle. That single rule was the highest-impact fix in the build.
What can be verified
- 115 automated checks — 64 on the engine, 51 running the real production code inside a mocked platform runtime.
- The test suite was proven to catch the bugs: the fixes were stashed, the suite re-run against the pre-fix code, and it failed on the expected assertions.
- Delivered in a state where no automatic owner write is possible until the client enables it, with rollback documented at four levels.
The workflow
(select to enlarge)
On numbers: every figure above is an artefact count or a measured technical value. No business-outcome metric, whether time saved, revenue or conversion, was captured on these engagements, so none is claimed.
On status: reflects repository evidence and platform backups, not a live systems check.