1. Rowfire
  2. Use cases

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 = 0

An 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.