Dashboard Report - User Notes

[SaraDev (Sara Devlaeminck)]

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

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
…	…	…	…