Shadow

For anyone that noticed the 30 seconds of downtime a few minutes ago, that was to upgrade us to lemmy 0.19.10.

Changes are listed here - https://join-lemmy.org/news/2025-03-19_-_Lemmy_Release_v0.19.10_and_Developer_AMA

This is not the version with breaking API changes, there should be no impact to any clients.

Enjoy!

F

cross-posted from: https://lemmy.ca/post/40761824

Sorry everyone I know how much you love the attention she gives you, but I've implemented some quick and dirty filtering for private messaging.

We now have the ability to automatically mark PM's as deleted or read, depending on content inside of them. If we accidentally filter something you legitimately wanted (ie, not Nicole) please let me know.

If any other instances would like to implement this, here's the code. Note that you'll need to set your hostname at the top here for some reason I haven't exactly identified.

SET lemmy.protocol_and_hostname = 'https://lemmy.ca/';

CREATE TABLE private_message_filters (
    id SERIAL PRIMARY KEY,
    phrase TEXT NOT NULL,
    behavior VARCHAR(10) NOT NULL CHECK (behavior IN ('delete', 'mark_read'))
);

CREATE OR REPLACE FUNCTION filter_private_messages()
RETURNS trigger AS $$
DECLARE
    banned_phrase_record private_message_filters%ROWTYPE;
BEGIN
    FOR banned_phrase_record IN 
        SELECT * FROM private_message_filters
    LOOP
        IF LOWER(TRIM(NEW.content)) ILIKE '%' || LOWER(TRIM(banned_phrase_record.phrase)) || '%' THEN
            IF banned_phrase_record.behavior = 'delete' THEN
                NEW.deleted := true;
                RETURN NEW;
            ELSIF banned_phrase_record.behavior = 'mark_read' THEN
                NEW.read := true;
                RETURN NEW;
            END IF;
        END IF;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_filter_private_messages
AFTER INSERT ON private_message
FOR EACH ROW
EXECUTE FUNCTION filter_private_messages();

To add filter words:

insert into private_message_filters (behavior, phrase) values ('delete', 'spamtestdelete');
insert into private_message_filters (behavior, phrase) values ('mark_read', 'spamtestread');

If you want to quickly disable / enable filtering while testing:

ALTER TABLE private_message DISABLE TRIGGER trg_filter_private_messages;
ALTER TABLE private_message ENABLE TRIGGER trg_filter_private_messages;

I'll leave it up to you to figure out what phrases to filter on. MAKE SURE YOU TEST. If there's an error, private messaging could break completely. You should not get an error message from the UI while sending a message with a banned word.

Edit: I like flamingos-cant's solution here better: https://lemmy.ca/post/40761824/15209462

Sorry everyone I know how much you love the attention she gives you, but I've implemented some quick and dirty filtering for private messaging.

We now have the ability to automatically mark PM's as deleted or read, depending on content inside of them. If we accidentally filter something you legitimately wanted (ie, not Nicole) please let me know.

If any other instances would like to implement this, here's the code. Note that you'll need to set your hostname at the top here for some reason I haven't exactly identified.

SET lemmy.protocol_and_hostname = 'https://lemmy.ca/';

CREATE TABLE private_message_filters (
    id SERIAL PRIMARY KEY,
    phrase TEXT NOT NULL,
    behavior VARCHAR(10) NOT NULL CHECK (behavior IN ('delete', 'mark_read'))
);

CREATE OR REPLACE FUNCTION filter_private_messages()
RETURNS trigger AS $$
DECLARE
    banned_phrase_record private_message_filters%ROWTYPE;
BEGIN
    FOR banned_phrase_record IN 
        SELECT * FROM private_message_filters
    LOOP
        IF LOWER(TRIM(NEW.content)) ILIKE '%' || LOWER(TRIM(banned_phrase_record.phrase)) || '%' THEN
            IF banned_phrase_record.behavior = 'delete' THEN
                NEW.deleted := true;
                RETURN NEW;
            ELSIF banned_phrase_record.behavior = 'mark_read' THEN
                NEW.read := true;
                RETURN NEW;
            END IF;
        END IF;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_filter_private_messages
AFTER INSERT ON private_message
FOR EACH ROW
EXECUTE FUNCTION filter_private_messages();

To add filter words:

insert into private_message_filters (behavior, phrase) values ('delete', 'spamtestdelete');
insert into private_message_filters (behavior, phrase) values ('mark_read', 'spamtestread');

If you want to quickly disable / enable filtering while testing:

ALTER TABLE private_message DISABLE TRIGGER trg_filter_private_messages;
ALTER TABLE private_message ENABLE TRIGGER trg_filter_private_messages;

I'll leave it up to you to figure out what phrases to filter on. MAKE SURE YOU TEST. If there's an error, private messaging could break completely. You should not get an error message from the UI while sending a message with a banned word.

Sorry for the downtime! Unfortunately our secondary firewall took over for some reason, and haproxy failed to properly come up.

I'll be scheduling a maintenance window in the next few days to do some further digging, so I can make sure this is fully resolved.

With some hacking at the database and APIs, I've put together a python script that allows backfilling of posts. I've now pulled in the most recent 50 posts of anyone that one of our users is following.

You may now bask in cats. https://pixelfed.ca/i/web/profile/797991200484212751

Hopefully that'll help it feel a little less empty!