Dashboard Report - Unaccepted Policies

data & reporting
, , ,
1

This is an SQL version of the Dashboard Report for Unaccepted Policies.

:discourse: This report requires the Discourse Policy plugin to be enabled.

This dashboard report identifies topics with policies that have not been accepted by certain users. This report helps admins ensure users comply with post policies, identify who hasn’t accepted required policies, enforce forum rules, manage policy updates, and confirm that users agree to guidelines.

SELECT distinct t.id AS topic_id, gu.user_id AS user_id
    FROM post_policies pp
    JOIN post_policy_groups pg on pg.post_policy_id = pp.id
    JOIN posts p ON p.id = pp.post_id AND p.deleted_at is null
    JOIN topics t ON t.id = p.topic_id AND t.deleted_at is null
    JOIN group_users gu ON gu.group_id = pg.group_id
    LEFT JOIN policy_users pu ON
        pu.user_id = gu.user_id AND
        pu.post_policy_id = pp.id AND
        pu.accepted_at IS NOT NULL AND
        pu.revoked_at IS NULL AND
        (pu.expired_at IS NULL OR pu.expired_at < pu.accepted_at) AND
        ((pu.version IS NULL AND pp.version IS NULL) OR
        (pp.version IS NOT NULL AND pu.version IS NOT NULL AND pu.version = pp.version))
WHERE pu.id IS NULL

SQL Query Explanation

This query performs the following operations:

  • Joins the post_policies, post_policy_groups, posts, topics, and group_users tables to create a list of topics and users who are subject to post policies based on their group membership.
  • Uses a left join with the policy_users table to find instances where a user has not accepted the policy (pu.id IS NULL), ensuring that only current and relevant policy acceptances are considered (not revoked, not expired, and matching the current version of the policy).
  • Selects distinct topic_id and user_id pairs, indicating topics with policies that have not been accepted by certain users.

Example Results

topic user
Policy Topic 1 user_1
Policy Topic 1 user_2
Policy Topic 2 user_1
… …