Ops

Stop-gap ops wiki

To be moved to some shared space with other ForumMagnum instances

How to make comment fully publicly invisible

This assumes the comment was previously deleted by admin/moderator through the UI and now is tombstoned

UPDATE "Comments" SET deleted = true WHERE _id = '<commentId>';
UPDATE "Comments" SET "deletedPublic" = false WHERE _id = '<commentId>';

How to get WAU and MAU

WAU

SELECT 
    TO_CHAR(DATE_TRUNC('week', "createdAt"), 'YYYY-MM-DD') AS week_start,
    COUNT(DISTINCT "userId") AS weekly_active_users
FROM 
    "LWEvents"
WHERE 
    "createdAt" >= '2023-11-06' AND
    name = 'post-view'
GROUP BY 
    week_start
ORDER BY 
    week_start;

MAU

SELECT 
    TO_CHAR(DATE_TRUNC('month', "createdAt"), 'YYYY-MM-DD') AS month_start,
    COUNT(DISTINCT "userId") AS monthly_active_users
FROM 
    "LWEvents"
WHERE 
    "createdAt" >= '2023-11-01' AND
    name = 'post-view'
GROUP BY 
    month_start
ORDER BY 
    month_start;

The manual procedure for deleting personally identifying data

TBD whether we'll actually delete entire rows, or just delete the identifying fields. But for now, do this:

Unsubscribe the user from any Sendgrid lists they might be on. The Deactivate User checkbox on their account settings page will do it, or you can log in to Sendgrid and remove them (in Marketing/Contacts, search by email address).

First get the userId:

SELECT _id FROM "Users" WHERE email = '[email protected]';

Then check whether the user has any posts with that userId, comments with that userId, or revisions with that documentId. Posts and comments can be soft-deleted with the deleteComment function (which you can run by checking the Delete Content checkbox on the user's account when you're an admin).

Then clear the user data:

UPDATE "Users" SET
"username" = null,
"displayName" = null,
"previousDisplayName" = null,
"fullName" = null,
"email" = null,
"emails" = null,
"profile" = null,
"services" = null,
slug = null,
"oldSlugs" = null,
"lastUsedTimezone" = null,
"mapLocation" = null,
"mapMarkerText" = null,
"htmlMapMarkerText" = null,
"paymentEmail" = null,
"paymentInfo" = null,
"profileImageId" = null,
"profileTagIds" = '{}'::jsonb[],
wu_uuid = null,
first_name = null,
last_name = null,
avatar = null,
biography = null,
biography_latest = null,
"bookmarkedPostsMetadata" = '{}'::jsonb[]
WHERE email = '[email protected]';

And then check whether they have any Revisions:

SELECT COUNT(*) FROM "Revisions" WHERE "documentId" = 'theId';

If they do, figure out what to do about it, do that, and then document it here.