Subtle changes, bugs and feedback

I don't seem to be getting notifications for DMs recently. By which I mean, if I tap the 3 lines in the top left of every page, the little envelope in the top right corner doesn't go yellow like it should.

I get it.

And the way that works is that I read unread_huddles from the profiles table.

That number is updated when new huddles are created, and decremented when an unread huddle is viewed.

That code is:

WITH u AS (
    SELECT COALESCE(COUNT(*) OVER(), 0) AS total
      FROM flags ff
      JOIN (
               SELECT hp.huddle_id
                     ,f.last_modified
                 FROM huddle_profiles hp
                      JOIN flags f ON f.item_type_id = 5
                                  AND f.item_id = hp.huddle_id
                 LEFT JOIN read r ON r.profile_id = 66188
                                 AND r.item_type_id = 5
                                 AND r.item_id = f.item_id
                 LEFT JOIN read r2 ON r2.profile_id = 66188
                                  AND r2.item_type_id = 5
                                  AND r2.item_id = 0
                WHERE hp.profile_id = 66188
                  AND f.last_modified > COALESCE(
                                            COALESCE(
                                                r.read,
                                                r2.read
                                            ),
                                            TIMESTAMP WITH TIME ZONE '1970-01-01 12:00:00'
                                        )
           ) AS h ON ff.parent_item_id = h.huddle_id
                 AND ff.parent_item_type_id = 5
                 AND ff.last_modified >= h.last_modified
      LEFT JOIN ignores i ON i.profile_id = 66188
                         AND i.item_type_id = 3
                         AND i.item_id = ff.created_by
     WHERE i.profile_id IS NULL
     GROUP BY h.huddle_id
     LIMIT 1
)
UPDATE profiles
   SET unread_huddles = COALESCE((SELECT total FROM u), 0)
 WHERE profile_id = 66188;

I've checked that, and it looks good.

SELECT unread_huddles FROM profiles WHERE profile_id = 66188;
 unread_huddles
----------------
              0
(1 row)

It says you have zero... so now I'll send you a DM.

SELECT unread_huddles FROM profiles WHERE profile_id = 66188;
 unread_huddles
----------------
              1
(1 row)

That should now restore the orange logo.

Hypothesis for what happened... I nuked a spammer who had sent you a DM, you can therefore never read the DM and thus it would never reset the counter correctly. Something like that.

But I've manually triggered it now, so it should be good.