Creating triggered custom badge queries

:bookmark: 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.

:person_raising_hand: Required user level: Administrator

:warning: 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:

  1. Badges that target posts
  2. 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.

:information_source: 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.

:exclamation: Remember that a full backfill runs daily regardless, so you must consider this and include handling of the :backfill parameter.

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.

:information_source: Badge triggers can be complex. Often, “daily” updates suffice, and you can skip the more intricate aspects of triggered queries.

Last edited by @hugh 2024-07-16T03:15:08Z

Check documentPerform check on document:
34 Likes