Hi everyone,

I want to share a custom development I've created to improve our interaction and follow-up on our forums. This is a bot that checks discussions from the last 15 days and reminds discussion creators to provide feedback on the responses they have received.

What does the bot do?

Queries the forum database to review discussions created in the last 15 days.
Identifies discussions where the discussion creator has received responses but hasn't provided any feedback for at least 7 days after the last response.
Automatically replies mentioning the discussion creator, reminding them that they have comments without feedback, encouraging them to review the received responses.

Why is this bot useful?

Improves interaction: It helps users remember to give feedback on the responses they receive, fostering a more participatory and grateful community.
Encourages follow-up: Users can follow up on their questions, helping to close topics and resolve doubts effectively.
Optimizes forum usefulness: With more feedback, we can know which responses are helpful and improve the quality of the content on the forum.

How can you implement this bot in your forum?

This is the tricky part for non-technical users. For now I'm going to share the database query and other additional steps, but in the future this could be transformed into a Flarum extension.

In my case I have a Laravel web application integrated with some forum features. I have created a command for artisan that is executed daily using a cronjob.

SQL query to find threads with replies where the OP has not responded since creation.

SELECT d.id, d.slug, d.is_private, d.is_locked, username
FROM discussions d
    INNER JOIN (
        SELECT discussion_id, MAX(created_at) AS last_post_date, user_id
        FROM posts
        GROUP BY discussion_id, user_id
        HAVING COUNT(*) = 1
) p ON d.id = p.discussion_id AND d.user_id = p.user_id
INNER JOIN users u ON d.user_id = u.id
WHERE d.created_at >= DATE_SUB(CURDATE(), INTERVAL $lastXDays DAY)
AND DATEDIFF(CURDATE(), d.created_at) > 6 -- Days since discussion creation
AND d.is_locked = 0
AND d.user_id <> d.last_posted_user_id
AND d.last_posted_user_id <> 123 -- User Bot
AND d.user_id NOT IN (
    SELECT user_id
    FROM group_user
    WHERE group_id IN (1, 3) -- Staff
)
AND d.id NOT IN (
    SELECT discussion_id
    FROM discussion_tag
    WHERE tag_id = 15 -- Solved
)

Generate a suitable message to use as a response for the bot. Example:

Dear @'.$username.',

We noticed that you have not yet provided an additional response or comment in the topic. To keep the discussion active and useful for all community members, we encourage you to review and respond to the responses received. Please note that if there is no activity on the topic in the next few days, it may be necessary to close or lock the discussion. Your participation is valuable to us.

Thank you for contributing to our community!

Prepare the request to the Flarum API that will include the previous message.

$client = Http::accept('application/json')->withToken(env('FORUM_REMINDER_BOT_TOKEN'), 'Token')
    ->post(env('APP_URL') . '/foro/api/posts', [
        'data' => [
            'attributes' => [
                'content' => $message
            ],
            'relationships' => [
                'discussion' => [
                    'data' => [
                        'id' => $discussionId,
                        'type' => 'discussions'
                    ]
                ]
            ]
        ]
]);

Generate a developer token in your database for use by the bot.
Create a task in your server cronjob to run the command as often as you want.

FAQ

Why the name "ghost" discussions? Users act as such, they appear once and do not return.
I haven't understood anything, can you explain it better? Sorry... I already warned that it requires some technical knowledge, in any case you can ask me something specific and I will try to help you.
Which user will use the bot to participate? Whichever you decide, in my case I have created a new user, given it a personalized avatar and then specified it in the query "User Bot", as well as in the creation of the token.
Can't the bot be too intrusive? It is prepared to respond only once, if it detects that the last response is from the bot itself, it will not write again. It's simply a reminder to generate activity on the forum and hopefully get responses from users. I did some manual testing and got 20% success, for me it's still better than nothing.
I don't want the bot to write in discussions where the creator is an administrator or moderator. The query excludes several user groups, you can see the note included as "Staff".
I don't want the bot to reply to discussions that have already been marked resolved. Discussions based on a tag can also be excluded, see the "Solved" note.
Can you make the bot close discussions after X days without a response? Yes, but it takes time to develop and I haven't had time to implement it yet.

I am sure that the query can be improved, it is a first approximation and with time and tests I will see how it evolves. Yesterday I deployed it in production and I have not yet seen participation from the bot, suggestions for improvement or bug reports are accepted.