How to create Zendesk tickets from a SQL query

Many teams already have the query: trials that never activated, orders paid but not shipped after a day, customers who gave a low NPS score. Someone opens the report, finds the new rows and files tickets by hand. Rowfire, an open source tool for PostgreSQL, MySQL and MariaDB, turns that query into Zendesk tickets: one for each new row, at most as often as you allow per customer, with a backtest and a shadow run before the first real ticket.

Why not a script on a schedule

A script can call Zendesk’s API. The hard part is everything after: the same row comes back on every run until someone fixes it, so you need a record of what you already filed. Retries on one invoice should be one ticket, not three. And the first run shouldn’t open a ticket for every row in history. Rowfire handles those three by design: a key and a cadence on each rule, a ledger of every send, and triggers that start at now.

1. The query

A trigger is one read-only SELECT, with a clock and a key:

SELECT a.id          AS account_id,
       a.name        AS account,
       a.owner_name,
       a.owner_email,
       t.trial_ends_at
FROM trials t
JOIN accounts a ON a.id = t.account_id
WHERE t.activated_at IS NULL
  AND t.trial_ends_at < now() + interval '3 days'

That one finds an absence, a trial that never activated, which no line of application code runs for. The clock is trial_ends_at and the key is account_id. See the events your app can’t emit for more of these.

2. The rule

Once per account: a stalled trial gets one ticket, however many times the query sees it. For recurring problems, such as declined cards or detractor scores, pick once per account per week or month instead.

3. The Zendesk action

Connect Zendesk with an agent email and an API token, then fill in create_ticket. Every field can use the row’s columns:

field	value
Subject	Trial for {{ account }} ends without activation
Description	Hi {{ owner_name }}, your trial ends soon and ...
Requester email	{{ owner_email }}
Priority	high
Tags	["trial", "activation"]

The token is added at send time and never stored with the request, so what Rowfire records and shows is the ticket body, never the credential.

4. Backtest, shadow, live

The backtest replays the rule over past days: how many tickets it would have opened, with example rows. On the demo’s sample data, one ticket per account per week turns 211 declined charges into 105 tickets; the other 106 were retries. Shadow mode then renders each ticket against live data and records it without sending. Promote the rule when the tickets look right.

Also tell Slack

The same trigger can post to Slack on its own cadence, polled once for both. See Slack and Zendesk alerts when a card is declined, rescue trials that never activated, and how to send a Slack alert from a SQL query.

Try it in the demo: a Demo inbox draws the tickets as Zendesk would, with no Zendesk account.