Send overdue invoices to collections
Send invoices still unpaid a week past due from MariaDB to your collections API and a finance channel in Slack, once per invoice, with no app code.
- MariaDB
- Any REST API
- Slack
- Finance
- once per invoice
The trigger
One read-only SELECT against MariaDB. Rowfire polls it for new rows, and the rule fires once per invoice.
SELECT i.id AS invoice_id,
i.number AS invoice_no,
a.id AS account_id,
a.name AS account_name,
a.billing_email,
i.amount_due,
i.currency,
i.due_date,
TIMESTAMP(i.due_date) + INTERVAL 7 DAY AS overdue_at
FROM invoices i
JOIN accounts a ON a.id = i.account_id
WHERE i.status = 'open'
AND i.paid_at IS NULL
AND i.amount_due > 0
AND a.is_test = 0An invoice that goes unpaid makes no noise. The customer doesn’t pay, no code
runs, and the invoice sits at open until someone in finance runs the aging
report. This trigger lists every open invoice with money still due, and the
clock, overdue_at, is a week after its due date. The key is invoice_id,
and the rule fires once per invoice. Table and column names are a sketch;
adapt them to your schema.
Test accounts are filtered out with is_test, so a QA tenant’s unpaid invoice
never reaches collections. The query runs in MariaDB’s own dialect, read-only.
The REST API side
Collections usually lives somewhere else: an accounting system, a dunning
service, or a small internal service of your own. Add it as an integration
with its base URL and API key, and define one action as data: the method, the
path and a body template. For example, a POST to /cases with:
{
"external_ref": "{{ invoice_id }}",
"invoice": "{{ invoice_no }}",
"customer": "{{ account_name }}",
"email": "{{ billing_email }}",
"amount": "{{ amount_due }}",
"currency": "{{ currency }}",
"due": "{{ due_date }}"
}
The key is added at send time and never stored with the request. If the template names a column the row doesn’t have, the delivery fails rather than sending a case with a hole in it.
Tell finance too
Add a second rule on the same trigger with a Slack action, so the team sees each hand-off in its own channel:
{{ invoice_no }} for {{ account_name }} is a week overdue: {{ amount_due }} {{ currency }}, due {{ due_date }}. Sent to collections.
Each rule keeps its own count. One can run in shadow while the other is live.
A reminder before it’s late
The same shape works earlier. Change the clock to three days before the due
date (TIMESTAMP(i.due_date) - INTERVAL 3 DAY) and send a Braze event with
external_id set to {{ account_id }}, so the customer gets a reminder while paying on time is still
possible. That is a second trigger, since the clock is part of the query.
Before it goes live
Backtest the rule over the last few months to see how many invoices it would have sent. The backtest reads rows as they are now, so invoices that were paid late are invisible to it: treat the count as a floor. Then run it in shadow, which renders each request without sending it, and promote it when the requests look right.
For card payments that fail rather than invoices that go unpaid, see escalate the third declined charge in a month. For another finance alert, see flag large refunds to finance. To build a rule end to end on sample data, try the live demo.
Questions
Will invoices that are already overdue all fire on day one?
No. A new trigger starts at now, so history never fires. Only invoices whose overdue_at passes after the trigger is switched on reach collections.
What if the customer pays on day six?
Then paid_at is set and the invoice no longer matches, so nothing fires. Only invoices still open when overdue_at passes are sent.
Our due dates are in local time. Does that matter?
MariaDB sessions run in UTC, and a date or DATETIME is read as UTC. If yours holds local wall-clock time, convert it in the query with CONVERT_TZ.
Does Rowfire update the invoice when it hands it off?
No. It is read-only and never writes to the database it reads. Whatever your collections system does next happens in that system.