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.
Would something along these lines work better (adjusted to fit the scorable query format - this is a test one for the data explorer ):
-- date :start_date
-- date :end_date
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.
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.
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.
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
That’s interesting. I definitely didn’t account for that.
Would adding a small buffer like
AND u.created_at + INTERVAL '1 SECOND' > iu.redeemed_at compensate for that without impacting it too much?
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.