1. Rowfire
  2. Use cases

Send posts reported by three users to moderators

Open a Zendesk ticket when a third different user reports a post in Supabase, once per post, so moderators see it without counting reports in app code.

  • Supabase
  • Zendesk
  • Slack
  • Trust & Safety
  • once per post

The trigger

One read-only SELECT against Supabase. Rowfire polls it for new rows, and the rule fires once per post.

SELECT p.id AS post_id,
       p.author_id,
       left(p.body, 200) AS excerpt,
       third.reason AS third_reason,
       third.reported_at AS third_report_at
FROM (
  SELECT f.post_id,
         f.reason,
         f.reported_at,
         row_number() OVER (PARTITION BY f.post_id
                            ORDER BY f.reported_at) AS nth
  FROM (
    SELECT DISTINCT ON (r.post_id, r.reporter_id)
           r.post_id,
           r.reason,
           r.created_at AS reported_at
    FROM reports r
    ORDER BY r.post_id, r.reporter_id, r.created_at
  ) f
) third
JOIN posts p ON p.id = third.post_id
JOIN profiles a ON a.id = p.author_id
WHERE third.nth = 3
  AND p.removed_at IS NULL
  AND a.is_test = false

A user reports a post. Then a second user does. Your app saved both rows in reports, and that is all it did: no code runs when the count reaches three, so nobody on the moderation team hears about it until they open the queue and sort by count, if they ever do.

The query notices instead. It keeps each reporter’s first report on a post, numbers them in time order, and returns the post at the third one. The key is post_id and the clock is third_report_at, the moment a third different person reported it. When a poll sees a new row, the rule fires once per post. One person reporting the same post five times still counts as one.

Posts a moderator already removed drop out, and so do posts by your own test accounts. Tables without a schema are read as public, which is where an app on Supabase usually keeps them. Table and column names are a sketch; adapt them to your schema.

The Zendesk side

Bind the Zendesk create ticket action so each post becomes one ticket a moderator can own and close:

Subject: Post {{ post_id }} reported by three users
Body: Latest reason: {{ third_reason }}. Author: {{ author_id }}.
"{{ excerpt }}"

Set the priority and tags your moderation queue uses, such as trust_and_safety and reported_post. The ticket carries the excerpt, so the moderator can triage before opening the app.

A Slack heads-up on the same trigger

Some teams want the channel to see it too. Add a second rule on the same trigger with a Slack action that posts to #trust-and-safety. Each rule keeps its own count, so the Slack message and the ticket fire independently, and one can run in shadow while the other is live.

A lower bar for some reasons

Not every reason should wait for three people. A report for harassment or a threat might need a moderator after the first one. That is a second trigger with nth = 1 and a filter on r.reason, with its own rule. If your app has an API that hides a post until someone reviews it, add it as an integration and bind it to that rule as well.

Backtest the rule over the last few weeks to see how many posts it would have sent, then watch it in shadow before it goes live. For more on reading Supabase, see how to send Slack alerts from Supabase. For another queue nobody watches, see escalate urgent tickets nobody has answered, or try a trigger on the live demo.

Questions

What if one person reports the same post five times?

It counts once. The inner query keeps each reporter's first report on a post, so only three different people can make a post reach the third report.

Will the fourth and fifth reports open more tickets?

No. Only the third report has nth = 3, and the key is post_id, so the rule fires once per post. Later reports still land in your reports table as usual.

Does Rowfire need my Supabase database password?

No. It sends the query to Supabase's read-only query endpoint, which runs it as Supabase's read-only user. You connect with OAuth (Projects: Read and Database: Read) or a personal access token.

Will every post that was ever reported three times fire when I switch it on?

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.