This guide explains how to create triggered custom badge queries in Discourse, including the types of badges, constraints for triggered badges, and an example query.
Required user level: Administrator
This feature is disabled by default. To enable it, follow this guide.
When defining badges in Discourse, you’ll encounter a “Trigger” option with the following choices:
- Update daily
- When a user acts on a post
- When a user edits or creates a post
- When a user changes trust level
- When a user is edited or created
- After a post is processed
These triggers force badges to run at intervals more frequent than daily, ensuring users are notified of new badges closer to when the action occurred.
Types of badges
There are two types of badges you can define:
- Badges that target posts
- Badges that do not target posts
All badge SQL definitions require you to select the columns user_id and granted_at. If your badge targets posts, you must also select a column named post_id.
If these columns are not directly available, you can alias them. For example:
u.id as user_id
Constraints for triggered badges
Since triggered badges can run once a minute, you need to provide more “hinting” in the badge definition. It’s not sufficient to return the full set of badges; you must also provide hints about how to execute the badge on a subset.
User-based triggers
If your trigger is user-based, supply a clause on how to filter it based on :user_ids.
Post-based triggers
If your trigger is post-based, supply information on how to trigger it based on :post_ids.
Remember that a full backfill runs daily regardless, so you must consider this and include handling of the
:backfillparameter.
Your triggered badge query will always include the :backfill param and either the :post_ids param or :user_ids param.
Example of a triggered badge query
Here’s an example of a badge triggered “when a user acts on a post”. In this case, “delta” applications will receive the :post_ids parameter:
SELECT p.user_id, p.id post_id, p.updated_at granted_at
FROM badge_posts p
WHERE p.like_count >= 25
AND (:backfill OR p.id IN (:post_ids) )
The clause (:backfill OR p.id IN (:post_ids) ) allows filtering of the results. When the daily job runs, :backfill is true, so the entire set is scanned. When delta jobs run, :backfill is false, and :post_ids is set.
Why manual hinting is necessary
The badge grant query runs your badge query in a “subquery”. Often, the PostgreSQL optimizer struggles when scanning the full set when the clause is on the main query. While it may handle trivial queries well, it can falter with more complex aggregates.
To avoid potential issues, this constraint was added, allowing you to apply filters in the most appropriate location.
Need help?
If you’re having trouble writing a badge query, post a question in support - describe what you’re trying to achieve and include your work in progress. The community will try to help.
Last edited by @hugh 2024-07-16T03:15:08Z
Check document
Perform check on document: