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 = falseA 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.