The shared doc says "transitioning to IES booking platform" and designs the whole workflow to live inside it — account assigned in IES, IES sends the email and text, collector logs into IES each morning to see her queue.
IES is Intuit Enterprise Suite — the accounting platform we're migrating QuickBooks Online
to. It's in our own notes: sandbox realm 9341456604789019, "IES Sandbox Company
US 59bc", production blocked "waiting for IES to come online".
It's a finance system of record. No collector task queues, no SMS, no offer expiry timers, no approve-and-send. Handing that plan to Colin or Intuit asks for something that doesn't exist.
The doc isn't wasted — its field list is right, and so is the instinct "don't build another spreadsheet". What it gets wrong is the home. This plan keeps the data model and re-homes the workflow.
Your question — how does IES know they missed a payment? The honest answer:
IES has no concept of a "missed payment". It has invoices with due dates and payments applied against them. "Overdue" is something you derive: invoice balance > 0 and due date in the past.
For IES to derive it correctly, two things must already have happened:
That second one is the problem. Square settles in daily lump deposits, not per-customer payments. Today that lump arrives in QuickBooks as a bank feed line and Elena matches it to invoices by hand. So IES's view of "who missed" is only ever as fresh, and as accurate, as that reconciliation.
Trigger collections off IES and you're triggering off the reconciliation cadence. In collections, where recovery drops hard with every week of delay, that's the wrong clock.
The recurring invoice flips to failed the moment the card declines, carrying the
decline reason. report_missed_payments.js already reads exactly this,
every day, across all six cities.
These are three completely different problems wearing the same coat:
| Square says | What it actually is | Right response |
|---|---|---|
| Expired / replaced card | An admin problem. They never chose to stop paying. | "Update your card" link. Should never reach a human, let alone a discount. |
| Insufficient funds | A timing problem. Usually pays on retry. | Retry on a smarter date, gentle nudge. |
| Lost / stolen / do not honour | A genuine collections problem. | Enter the escalation ladder. |
IES cannot tell these apart — it only ever sees an unpaid invoice. Square can. Sorting the expired cards out at the top is probably the single biggest recovery win here, and it costs us nothing but a link.
It isn't either/or. They answer different questions, and the workflow needs both:
| Question | Answer from | Why |
|---|---|---|
| Did a payment fail, and why? | Square | Real time, with the decline reason. IES has no concept of the event. |
| Did money land? | Square | Same day. IES confirms later, after reconciliation. |
| How many months behind? | Square | Miss streaks already computed in report_zombie_accounts.js. |
| What's the total owed? | IES | Carries write-offs, reductions, refunds and anything not billed through Square. |
| What have we discounted? | IES | A reduction is a write-off — the books have to hold it. |
Square is the event source. IES is the balance source. The portal listens to Square to decide when to act, and reads IES to decide how much to ask for.
Worth flagging early because it's an Elena conversation, not a developer one:
when Caet gives 50% off, the books have to carry the write-off. Square only ever
sees the reduced amount landing — it has no idea the rest was forgiven. Today that's tracked in
write-off columns on the Google ledger (ledger_config.json has one per year). In IES it
becomes a credit memo or write-off entry, and the portal should be what triggers it, so the discount
never has to be re-keyed.
Balance comes from Square (outstanding recurring invoices) plus the CRM's package price. That's enough to run the whole ladder and make reduction offers. When IES lands it becomes the authority, shown against the Square figure with any disagreement flagged. Nothing waits on the migration.
That settles the biggest question hanging over this. Every write-off, reduced sale and refund decision back to 2023 lands in IES, so the audit trail survives the sheet. The ledger isn't being deleted, it's being superseded — it just stops being useful once the same information lives somewhere queryable.
So the problem isn't where does the history go. It's what has to be true on the day it stops being maintained — because the sheet is currently doing live work, not just holding records.
| Job | Takes over | Ready? |
|---|---|---|
| Historical write-offs, reduced sales, refunds | IES | MIGRATING |
| Current balance per debtor | IES | NEEDS IES LIVE |
| Month-by-month payment grid | Square (already has it) | HAVE IT |
| Caet's reminder / offer tracking | The portal | BUILD |
| Telling staff what someone owes | IES → CRM push | DOESN'T EXIST |
Today, someone who needs to know what a customer owes looks at the ledger. When it goes, IES holds the truth — but nobody works in IES. Caet works in the CRM. The collectors work in the CRM. If the balance doesn't get pushed across, the sheet's real job just quietly stops being done, and people start quoting numbers from memory or from Square, which doesn't know about any of the reductions.
The IES → CRM push isn't a nice-to-have on the side. It's the thing that replaces the ledger's day job.
| Leg | Today | Needed |
|---|---|---|
| Square → ledger | BUILT scripts/leads/ledger_sync.js pulls Square daily, matches sheet rows, writes amounts and generates a CRM CSV | Repoint at IES instead of the sheet |
| Square → IES | MANUAL lump settlements matched by hand in the Banking tab | Automate — this is what kills the reconciliation lag in section 2 |
| IES → CRM | DOESN'T EXIST | The genuinely new piece — and the ledger's actual replacement |
| Portal → IES | DOESN'T EXIST | Reductions post as write-offs, or we recreate the same gap somewhere new |
The good news is that the first leg is already built. ledger_sync.js
does Square → ledger → CRM CSV today, so this isn't inventing a path — it's
repointing it at IES and making the return leg real.
The ledger stops being maintained when it and IES have agreed for a few weeks running — not on a chosen date. Two measures disagreeing is exactly how we've caught bad numbers before. Pick a date instead and any migration gap surfaces after the sheet has gone cold, with nothing left to check against.
One ledger per market. The Square half is a non-issue — all six token sets and every recurring
location are already configured in city_config.json, and
ledger_sync.js already loops cities. The sheet half is the work, because
ledger_config.json is currently written for New York alone.
The New York config isn't generic — it's hard-coded to that sheet's exact geometry:
ref D · name C · sales E · balance Q · months from AE · write-offs U/V/W (2024/25/26) ·
reduced sales X/Y/Z/AA (2023–26) · refunds AB/AC/AD
If six other sheets grew up separately — a column inserted here, a different year range there, a different tab name — then reading them with the wrong column letters won't throw an error. It will return a number. Just the wrong one. Write-offs read from the refunds column produce balances that look entirely plausible and are quietly wrong, on the very data we're migrating as the permanent record.
That's the one thing here that could corrupt IES rather than merely delay it.
ledger_config.json. Cheap, and it's the
step that de-risks everything after it.The audit needs the seven spreadsheet IDs, and each sheet shared with
studio1-automation@studio1-automation.iam.gserviceaccount.com — the
service account key is already in the repo and working.
You're right. IES is the finance spine. General ledger, balances, invoices, write-offs, refunds, the P&L — that's its job and it does it properly. We shouldn't be building a second one, and this plan shouldn't have implied it.
What we're building is the layer above it: the debt collection spine. Your name for it is the accurate one.
IES is authoritative about money but it is not operational. It has no events, no triggers, no queues, no comms — and it's reconciliation-lagged by design. It can tell you what someone owes. It can't tell you that a card declined this morning, whose job it is to chase, what's already been tried, or what happens tomorrow if nobody replies.
| Layer | Owns | Character |
|---|---|---|
| IES — finance spine | Balances, invoices, write-offs, reductions, refunds, the books | Authoritative. Slow. Nobody works in it. |
| Collection spine — this build | Miss events, ladder stage, comms history, offers, arrangements, Monterey handover | Real-time. Event-driven. Where the work happens. |
| CRM | The client, the package, who's assigned | Where staff actually sit. |
| Square | Payments, declines and reasons | The event source feeding both. |
So the ladder, the offers, the arrangements and the Monterey handover all belong on the collection spine, reading balances from IES rather than being built inside it.
Each of these sits across two systems, so today nobody has to answer them — and if collections gets built in isolation first, they get answered by accident, in code, and discovered later:
That's what "just doing this one component is missing a trick" looks like in practice.
| Consumer | Needs from the spine | Status today |
|---|---|---|
| The ladder + discount offers | Miss events, stage, comms, offers, write-back | This document |
| Caet's queue | Everything above, filtered to stage 4 | One cog, near the end — sections 6 and 8 |
| Payment arrangements | Terms, missed-instalment detection, re-entry to the ladder | BUILD |
| Missed payments | Square declines + reasons | RUNNING |
| Refunds & chargebacks | Adjustments against the right customer | RUNNING — reported, not reconciled back |
| Balances into the CRM | Shared plumbing — IES → CRM | DOESN'T EXIST — needed by everyone, collections is just its biggest user |
Skeleton the collection spine for all of it, implement the ladder first.
And Phase 1 — the payment watch — still ships now, because it reads Square directly and depends on none of this.
Your point — "this would be the new way we do all collections, and if people don't answer after putting them into a workflow they progress along the path to get to Caet" — is the biggest change from v1 of this plan, and it's a better design.
Caet stops being the front door and becomes the last human stage. Every missed payment enters one path. Most of it never reaches a person.
Payment fails. Decline reason read. Expired cards split off immediately to a card-update flow — they're not collections. Everything else enters the ladder.
15% off to settle now, then a forward check on the next payment: is it going to be OK, or would they like to speak to someone. Replies are handled automatically. This is where most recoverable money comes back, and where it's cheapest. Detailed in section 5.
Escalating sequence, firmer each time, still automated. A call task can be raised here without the account leaving the workflow.
Ignored everything automated. Assigned to one of the 8 collectors for live contact. Payment arrangements can be set up here.
The portal in this document. Only accounts that ignored every stage above. Offer, follow-up, expiry, final notice — with the full contact history already attached, so she can see everything that's been tried.
Offer expired, no payment, one business day's grace. Exported, stamped, handed over.
At any stage. Account leaves the ladder, sequences stop, status updates. If it had already gone to Monterey, that's a red alert — pull it back before we pay a fee on money we collected ourselves.
Your call: make it a two-way text, offer a small discount to pay now, then check whether the next payment is going to be OK or whether they'd like to speak to someone. That last part is the best idea in this plan — it turns a collections touch into a retention touch.
modules/convo-intel/engine.js — AI conversation engine, built for ICONIC. Reads a
transcript, works out where the conversation is, replies./api/healing/webhook/ghl in serve_reports.js — already receives and
processes inbound GHL messages./pay — the payment portal is live. Needs a discount parameter added so the link can
carry the reduced amount.So this is largely assembly, not a new build.
That forward-looking question is the part worth protecting. Someone who replies HELP is a payment-arrangement candidate, not a debtor — they've self-identified before missing anything. Catching them there keeps the account alive instead of walking it down to Monterey.
Detected from Square, not from what they say. Exits the ladder, confirmation sent, next-payment check fires.
Exits the ladder into a payment arrangement, task raised for the collections team. Never gets pushed further down the path while an arrangement is being set up.
Card-update link. No discount — see the guardrail below.
Checked against Square automatically. Found → apologise and close. Not found → straight to a human to verify, never argued with by a bot.
Hard stop — human immediately. No automated reply handles a cancellation or a complaint.
Opt-out honoured instantly, no further texts. Account continues down the ladder by email and phone only.
Stage 2.
15% at day 1 is far cheaper than 50–75% at day 45, and that arithmetic is why this is a good idea. But on a recurring base it has a failure mode worth designing out up front: if a missed payment reliably produces 15% off, some customers will learn to let the card decline every month. A one-time cost quietly becomes a permanent 15% price cut on that account — and it travels by word of mouth.
Five rules that keep the upside without the trap:
The AI should classify the reply and pick from approved copy — not generate free text about what someone owes. Anything it can't confidently classify goes to a human. That's the shadow-first pattern convo-intel already uses: run it in draft mode for the first couple of weeks, review what it would have sent, then let it send once the branches look right.
The spreadsheet in the middle is quietly doing four jobs: reminder system, payment tracker, reduction tracker, reporting tool. Three of those four automate away entirely from data we already hold. The fourth — deciding the offer — stays hers, with the numbers put in front of her.
"I have no current method of being notified of any payments being made to these accounts, as their balance remains open on the finance."
Two live failure modes: someone pays after being marked for collections and Monterey chases them anyway (we pay a fee on money we already collected); or someone part-pays a reduced offer, nobody updates the ledger, and a paying client gets a final notice.
Both close with a watch on the Square feed that already runs daily. It needs nothing from IES, nothing from Colin, and nothing from GHL — which is why it's Phase 1.
Nothing gets typed twice. Every field has exactly one owner.
| Field | Owner | How we get it | Status |
|---|---|---|---|
| Client name, phone, email, address | CRM | Colin's account feed | ASK COLIN |
| Assigned collector | CRM | Colin's account feed | ASK COLIN |
| Package price | CRM | Colin's account feed | ASK COLIN |
| Payment failed + decline reason | Square | report_missed_payments.js | HAVE IT |
| Payments received | Square | Daily pull, all 6 cities | HAVE IT |
| Months missed, amount outstanding | Square | report_zombie_accounts.js | HAVE IT |
| Authoritative balance owed | IES | Intuit API, per customer | IES NOT LIVE |
| Write-off / reduction on the books | IES | Portal triggers credit memo | IES NOT LIVE |
| Ladder stage + collection status | Portal | Supabase | BUILD |
| Offer amount + % reduction | Portal | Rules engine + Caet override | BUILD |
| Offer, follow-up and expiry dates | Portal | Auto-set from offer date | BUILD |
| Payment arrangement terms | Portal | Entered once by collector | BUILD |
| Communication history | Portal | Logged on every GHL send, all stages | BUILD |
| Date sent to Monterey | Portal | Stamped on export | BUILD |
In Workflow (1–2) With Collector (3) Reduction Offered Follow-Up Due Final Notice Sent Payment Arrangement Paid in Full Ready for Collections Sent to Monterey Closed
Replaces "check the spreadsheet". Built nightly, sorted by urgency, nothing to search for.
Red first, always: money landing where it shouldn't outranks routine follow-ups. That ordering is the point of the screen.
Balance, package price, suggested reduction, resulting figure. She can change it. Email and text come pre-written with the amount, the expiry date and the payment link already in them. One click sends both, and the follow-up date, expiry date and status set themselves — that's the spreadsheet row, written for her.
One button. CSV with exactly what Monterey needs — name, balance, offer made, expiry, date sent to collections, phone, email, address. Every account on it gets stamped Sent to Monterey with the date, so nothing can go twice.
Nearly free once the data exists, and it's what makes this worth doing at the business level: recovery rate by ladder stage (how much comes back at stage 1 vs stage 4 — that number tells you whether the discounts are even necessary), open offers by value, acceptance rate by reduction tier, monthly discount exposure, Monterey recovery vs handover. Nobody can answer any of that today.
You said: "we would just give an option based on the balance — say 50% or 75% off." That becomes a small table the portal reads. Placeholders — you set the real numbers.
| Balance owed | Standard offer | Escalation on expiry | Approval |
|---|---|---|---|
| Under $500 | 50% off | 75% off | Caet |
| $500 – $1,500 | 50% off | 75% off | Caet |
| $1,500 – $3,000 | 50% off | 75% off | Manager for 75% |
| Over $3,000 | 50% off | — | Manager, both tiers |
| When | What happens | Who acts |
|---|---|---|
| Square reports a failed payment | Decline reason read. Expired card → card-update flow. Otherwise → stage 1 | Portal |
| Stages 1–2 | Automated text/email sequences, charge retries | GHL, automatic |
| No response by stage 3 threshold | Assigned to a collector for calls | Portal assigns |
| No response by stage 4 threshold | Lands in Caet's queue, offer suggested, history attached | Portal |
| Offer day 0 | Offer email + text sent. Follow-up and expiry dates set | Caet clicks Send |
| Follow-up date (default +7) | Follow-up drafted, surfaces in queue | Caet clicks Send |
| Expiry date (default +14) | Final notice email + text drafted | Caet clicks Send |
| Expiry + 1 business day, no payment | Status → Ready for Collections | Portal, automatic |
| Monterey export run | Status → Sent to Monterey, date stamped | Caet clicks Export |
| Any payment, any stage | Drops out of the ladder, sequences stop. If already at Monterey → red alert | Portal, automatic |
| Balance hits zero | Status → Paid in Full, account closes | Portal, automatic |
| Arrangement payment missed | Flagged, reminder drafted, follow-up rescheduled | Portal drafts, human sends |
Stages 1–2 send automatically; stage 4 never does. Routine dunning is safe to automate — a reduction offer changes what someone owes, so it stays draft-and-approve, per your call.
| System | What we need | Where we stand |
|---|---|---|
| Square | Failed payments + decline reasons + payments, all cities | HAVE IT Running daily. Both reports already built on it. |
| GHL | Stage 1–2 sequences; send Caet's offer email + text; log every send | HAVE IT Live on two sub-accounts. Needs a collections sender + number decided. |
| Inbound SMS + AI replies | Receive replies, classify them, answer or escalate | MOSTLY HAVE IT modules/convo-intel/engine.js is the conversation engine; /api/healing/webhook/ghl already processes inbound messages. Needs collections branches + ANTHROPIC_API_KEY. |
| /pay portal | Carry the discounted amount on the pay-now link | SMALL BUILD Route is live (Reports/pay.html) — needs a discount parameter and one-time-use handling. |
| IES (Intuit) | Read balances; write reductions as write-offs | NOT LIVE But the OAuth + API client is already written (scripts/qb/qb_auth.js) and tested against the Intuit sandbox — a realm swap and a query, not a new build. |
| CRM (Colin) | Account feed; status write-back | TWO ASKS See below. |
| Supabase | Stages, offers, dates, comms log | HAVE IT Already the persistence layer for the reporting stack. |
A sequencing argument, not a vendor preference. IES isn't live and its date is TBD; the CRM is Colin's roadmap and Colin's timeline. If the workflow lives in either, nothing ships until someone else's project lands. On our stack, Phase 1 ships now against Square alone, and IES and the CRM plug into something already working. Caet still gets one screen — she just doesn't wait for a migration to get it.
Accounts marked for collections are watched against the Square feed. Payment lands → Caet is told the same day. Also catches part-payments against live reduction offers.
Needs nothing from IES, Colin or GHL. It's the thing she's actually frightened of losing with the ledger gone, and it runs on feeds that already fire daily.
Miss detection with decline-reason sorting, expired-card flow split off, the two-way text with the 15% pay-now offer and the next-payment check, reply classification and escalation, discount eligibility rules, quiet-hours scheduling. This is where the volume gets handled and the recovery economics change — and it reduces what reaches every human downstream.
Ship it in shadow mode first: it drafts every reply for a fortnight, nobody receives them, and we check the branches before letting it send.
Collector assignment, Caet's queue with full ladder history, offer builder on the rules table, auto-flip to Ready for Collections, one-click Monterey CSV, arrangement tracking. The spreadsheet dies at the end of this phase.
Balance switches to IES, shown against Square with disagreements flagged. Reductions post back as write-offs. Manager dashboard: recovery by stage, discount exposure, Monterey performance.
No dates until you've picked the platform and Colin's given a timeline — I'd rather size these once than guess.
Confirmed the systems will share an ID. That removes the one genuine technical risk in v1 of this plan — no fuzzy name-and-phone matching, so CRM ↔ Square ↔ IES joins cleanly and the balances can be trusted. We do need to know which field carries it, but that's a question, not a problem.
Per account: client name, phone, email, address, package price, assigned collector, and the shared ID. A nightly CSV drop is fine — it doesn't need to be a live API.
So anyone opening the account in the CRM sees the current ladder stage rather than a stale record. Without it the portal still works — the CRM just stays read-only.
| Risk | How we handle it |
|---|---|
| IES date is TBD — plan stalls waiting for it | Phases 1–3 don't need IES. Balance comes from Square + CRM meanwhile; IES swaps in at Phase 4. |
| Triggering off the wrong clock | Square drives the ladder, not IES's reconciled position. Section 2 is the reason. |
| Automated stages annoy good clients | Expired cards split off before stage 1. Payment at any stage stops every sequence immediately. Tone escalates gradually, not from the first message. |
| The pay-now discount trains people to miss | The main risk in the stage 1 design, and the reason for the five eligibility rules in section 5. Tracked from day one as repeat-miss rate among discount takers vs everyone else — if it climbs, the discount is the cause. |
| An AI says something wrong about someone's debt | It classifies replies and picks approved copy — it does not compose free text about money. Cancellations, complaints and disputed payments go straight to a human. Shadow mode for the first fortnight. |
| Texts sent at the wrong hour | Scheduled in the customer's local time — we bill across Central and Eastern. STOP honoured instantly. |
| Seven ledgers with drifted column layouts | The one risk that could corrupt IES rather than delay it — wrong column letters return plausible numbers, not errors. Audit all seven sheets and reconcile each against Square before migrating, per section 4. |
| Ledger switched off before the portal is ready | Ship Phase 1 first, and run the spreadsheet in parallel until Phase 3 has been agreeing with it for a couple of weeks. |
| Reductions drift out of the books | Portal is what triggers the write-off, so the discount is never re-keyed. Needs Elena's sign-off on treatment. |
| Client PII in a web portal | Same gated-key pattern the existing PII reports use, plus a login. No PII in URLs, no-cache on downloads. |
| A wrong offer goes out | Nothing auto-sends at stage 4. Portal drafts, Caet approves. Overrides logged. |
| Double-handling with Monterey | Sent-to-Monterey stamp makes a second send impossible, and the payment watch catches anyone who pays after handover. |