Dashboard Report - User Notes

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for User Notes.

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

This dashboard report lists user notes created by staff users within a specific date range. User notes are annotations or comments added by moderators or administrators to a user’s profile, often used to track behavior, issues, or important information about the user.

-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08

WITH user_notes AS (
    SELECT 
        REPLACE(key, 'notes:', '')::int AS user_id,
        notes.value->>'created_at' AS created_at,
        notes.value->>'raw' AS user_note,
        notes.value->>'created_by' AS created_by
    FROM plugin_store_rows,
    LATERAL json_array_elements(value::json) notes
    WHERE plugin_name = 'user_notes'
    ORDER BY 2 DESC 
)
SELECT 
    un.user_id,
    un.created_by AS moderator_user_id,
    un.created_at::date,
    un.user_note as html$user_note
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at BETWEEN :start_date AND :end_date
ORDER BY un.created_at ASC

SQL Query Explanation

This report extracts these notes from the plugin_store_rows table, where they are stored in a JSON format by the user_notes plugin, and presents them in an easily digestible format.

The query operates in several steps:

  • Parameters:
    • The query starts by defining two parameters, :start_date and :end_date, to specify the time period for the report. Both date parameters accept the format of YYYY-MM-DD.
  • Common Table Expression (CTE) - user_notes: The query begins with a CTE named user_notes that extracts and transforms the relevant data from the plugin_store_rows table. This table stores various plugin data in a key-value format, where the key for user notes is prefixed with notes: followed by the user’s ID. The CTE performs the following operations:
    • Filters rows where plugin_name is 'user_notes', ensuring only user notes data is selected.
    • Uses the json_array_elements function in a LATERAL join to expand the JSON array stored in the value column into individual JSON objects, each representing a note.
    • Extracts the user ID from the key by removing the notes: prefix and casting the result to an integer.
    • Extracts the note’s creation date, raw note content, and the ID of the user who created the note from the JSON object.
  • Main Query:
    • Joins the user_notes CTE with the users table to ensure that only notes for existing users are included.
    • Filters notes based on the created_at date to include only those within the specified date range (:start_date to :end_date).
    • Selects the user ID, moderator user ID (the note creator), note creation date, and the note content.
    • Orders the results by the note creation date in ascending order to present the notes chronologically.

Example Results

user moderator_user created_at user_note
user_1 staff_user_2 2024-01-10 example user note with HTML formatting
user_3 staff_user_4 2024-01-14 this is an example note about user_3
… … … …
3 Likes