Gamification and invitations

Bug
1

When using the Gamification plugin, points are rewarded for redeemed invitations.
This is easily abused by sending out invites and redeeming them with a duplicate (fake) account.
When such a fake account is removed by the admin, the behavior of the gamification plugin (and the invite system) depends on how the invite was sent.

  • If the invite was tied to a specific email address and the user is removed, then both the invite record and user_invite records are hard(!) deleted. The next time the Sidekiq UpdateScoresFor* job runs, the gamification plugin will deduct the awarded points from the score again.

  • If the invite was not tied to a specific email address and the user is removed, then the invited_users record will be removed, but the invite record will stay. The redemption_count value will not be decreased. This makes sense from one point of view because the invite redemption has taken place, even though the user has been removed afterwards. But the Gamification plugin uses the redemption_count to calculate the score so the points will not be deducted.

While debugging this, I also found that the score job will only consider invites that are less than 10 days old. So if an invite is redeemed after 11 days, no points will be awarded at all. I was going to say 'I guess it needs to look at updated_at instead of created_at but when the redemption count for the invite is increased, the updated_at timestamp is not being touched.

11 Likes
3

Would something along these lines work better (adjusted to fit the scorable query format - this is a test one for the data explorer :slight_smile:):

-- [params]
-- date :start_date
-- date :end_date

SELECT 
    invited_by_id AS user_id,
    COUNT(*) AS user_invites,
    COUNT(*) * 10 AS invite_score
FROM invited_users iu
  JOIN invites i ON i.id = iu.invite_id
  JOIN users u ON u.id = iu.user_id
WHERE iu.redeemed_at::date BETWEEN :start_date AND :end_date
  AND iu.user_id <> i.invited_by_id 
  AND u.created_at > iu.redeemed_at
GROUP BY invited_by_id
ORDER BY user_invites DESC

When a user is deleted it clears them from the invited_users table, so would no longer be in the count. If the deletion happened within 10 days then it would auto-corrected, if longer then it would need a manual score refresh.

Using the redeemed_at date would account for those invites which were created longer than 10 days ago.

AND iu.user_id <> i.invited_by_id would also exclude self invites.

Joining in the users table and adding AND u.created_at > iu.redeemed_at would also exclude inviting existing users.

3 Likes
4

That would be a good approach, except for one thing:

This does not work well. Sometimes the user is created slightly before the redemption took place. No idea why. Mostly tenths of seconds, but I found a few of tens of seconds as well.

Tested on a real database.

select 
  iu.redeemed_at iu_AS redeemed_at, 
  u.created_at AS u_created_at, 
  u.created_at > iu.redeemed_at AS u_created_gt_iu_redeemed 
from invited_users iu 
left join users u on u.id = iu.user_id 
where iu.redeemed_at is not null
order by iu.id desc;

       iu_redeemed_at       |        u_created_at        | u_created_gt_iu_redeemed 
----------------------------+----------------------------+--------------------------
 2023-09-08 00:00:47.557057 | 2023-09-08 00:00:48.376446 | t
 2023-08-25 20:09:03.486362 | 2023-08-25 20:09:03.201357 | f
 2023-08-15 23:38:32.271709 | 2023-08-15 23:38:33.570299 | t
 2023-08-14 10:44:34.19912  | 2023-08-14 10:44:35.429371 | t
 2023-08-12 13:41:10.428013 | 2023-08-12 13:41:11.733973 | t
 2023-07-31 17:58:13.511289 | 2023-07-31 17:57:50.427111 | f
 2023-07-23 00:56:33.455185 | 2023-07-23 00:55:47.999263 | f
 2023-07-19 08:42:44.908096 | 2023-07-19 08:42:46.040201 | t
 2023-06-30 09:11:38.829692 | 2023-06-30 09:11:39.618586 | t
 2023-06-30 08:37:02.322192 | 2023-06-30 08:37:03.133769 | t
 2023-06-29 16:24:01.705616 | 2023-06-29 16:24:02.55067  | t
 2023-06-29 12:53:33.245688 | 2023-06-29 12:53:34.067159 | t
1 Like
5

That’s interesting. I definitely didn’t account for that. :slight_smile:

Would adding a small buffer like AND u.created_at + INTERVAL '1 SECOND' > iu.redeemed_at compensate for that without impacting it too much?

6

I find this a bit too much “black magic” since we don’t know what is causing the delay.

I suspect the delay is caused by a large sidekiq queue or something similar and although the majority is under one second, about 3% is in the range of 0:00:01 - 0:15:00 (one second to 15 minutes).

And half a percent is in the order of days, which seems the kind of abuse we’re trying to prevent. So although this is effective, it would be doing more bad than good because of the amount of false positives.

2 Likes