Credit referral partners when an account first pays
Tell your referral platform when a referred account in MariaDB pays its first invoice, once per account, through any REST API. Trigger SQL included.
- MariaDB
- Any REST API
- Slack
- Partnerships
- once per account
The trigger
One read-only SELECT against MariaDB. Rowfire polls it for new rows, and the rule fires once per account.
SELECT a.id AS account_id,
a.name AS account_name,
p.id AS partner_id,
p.name AS partner_name,
p.external_ref AS partner_ref,
f.amount AS first_invoice_amount,
f.currency,
f.paid_at AS first_paid_at
FROM (
SELECT i.account_id,
i.amount,
i.currency,
i.paid_at,
ROW_NUMBER() OVER (PARTITION BY i.account_id
ORDER BY i.paid_at, i.id) AS nth
FROM invoices i
WHERE i.status = 'paid'
AND i.amount > 0
) f
JOIN accounts a ON a.id = f.account_id
JOIN partners p ON p.id = a.referred_by_partner_id
WHERE f.nth = 1
AND a.is_test = 0
AND p.is_active = 1A partner sends you a customer. The customer signs up with the partner’s code, starts a trial, and weeks later pays. Your billing code saved the invoice and moved on. Nothing told the partner program, so someone exports a report at the end of the month and credits partners by hand.
The query finds the moment instead. It numbers each account’s paid invoices in
time order and returns the first one, for accounts that came in through an
active partner. The key is account_id and the clock is first_paid_at, when
that first invoice was paid. When a poll sees a new row, the rule fires
once per account. Free invoices and test accounts drop out. MariaDB is read
in its own dialect, and its times are read as UTC. Table and column names are
a sketch; adapt them to your schema.
The REST API side
Add your referral or affiliate platform as an integration: its base URL, its auth header and one action that records a conversion. Templates fill the body from the row:
POST /conversions
{"partner": "{{ partner_ref }}", "customer": "{{ account_id }}", "amount": "{{ first_invoice_amount }}", "currency": "{{ currency }}"}
The platform’s API key is added at send time, so it is never stored with the recorded request. If you track commissions in your own app, point the action at your own endpoint instead. It is the same kind of integration.
A heads-up for the partnerships team
Add a second rule on the same trigger with a Slack action that posts to
#partnerships:
{{ account_name }} paid its first invoice ({{ first_invoice_amount }} {{ currency }}). Referred by {{ partner_name }}.
Each rule keeps its own count, so the Slack message can go live while the platform call is still in shadow.
Commission on every invoice
Some programs pay partners a share of every payment, not just the first. That
is a second trigger without the nth = 1 filter, keyed by invoice instead of
account, with a once-per-invoice rule. A refund that should take credit back
is its own trigger too: Rowfire emits moments, not state, so it never undoes a
call it already made.
A backtest shows how many partner credits the rule would have sent over past weeks. It reads rows as they are now, so an invoice that was paid and later refunded is not counted: treat the number as a floor.
For another moment money changes hands, see celebrate trials that convert to paid. For another call to an outside service, see send flagged orders to a fraud service, or try a trigger on the live demo.
Questions
Will the account's second invoice credit the partner again?
No. Only the first paid invoice has nth = 1, and the key is account_id with a once-per-account cadence, so each referred account fires one time.
Will accounts that paid before I switch it on fire?
No. The poller starts every trigger at now, so history never fires. A backtest shows what the rule would have done over past days, without sending anything.
How fast does the partner hear about it?
Triggers are polled, within a minute by default. That is plenty for a commission or a partner dashboard; it is not built for messages that must arrive instantly.
Does Rowfire write anything back to MariaDB?
No. It reads through a read-only session and never writes to the database. Marking the referral as paid stays in your app, or in an API you bind as a second action.