При использовании плагина геймификации очки начисляются за отработанные приглашения.
Это легко злоупотребляется: пользователь рассылает приглашения и отработывает их с помощью дублирующего (фейкового) аккаунта.
Когда такой фейковый аккаунт удаляется администратором, поведение плагина геймификации (и системы приглашений) зависит от того, как было отправлено приглашение.
Если приглашение было привязано к конкретному адресу электронной почты и пользователь удалён, то как запись invite, так и записи user_invite удаляются безвозвратно (!). При следующем запуске задачи Sidekiq UpdateScoresFor* плагин геймификации снова вычтет начисленные очки из общего счёта.
Если приглашение не было привязано к конкретному адресу электронной почты и пользователь удалён, то запись invited_users удаляется, но запись invite остаётся. Значение redemption_count не уменьшается. С одной точки зрения это логично, поскольку отработка приглашения уже состоялась, даже если пользователь был удалён позже. Однако плагин геймификации использует redemption_count для расчёта счёта, поэтому очки не будут вычтены.
В процессе отладки я также обнаружил, что задача подсчёта очков учитывает только приглашения, созданные менее 10 дней назад. Таким образом, если приглашение отработано через 11 дней, очки вообще не начисляются. Я хотел бы сказать: «Наверное, нужно смотреть на поле updated_at вместо created_at», но при увеличении счётчика отработок приглашения временная метка updated_at не обновляется.
Не подойдет ли что-то вроде этого (адаптированное под формат оцениваемых запросов — это тестовый запрос для Data Explorer):
-- [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
При удалении пользователя он удаляется из таблицы invited_users, поэтому больше не будет учитываться в подсчёте. Если удаление произошло в течение 10 дней, счётчик автоматически скорректируется; если прошло больше времени, потребуется ручное обновление очков.
Использование даты redeemed_at учтёт те приглашения, которые были созданы более 10 дней назад.
Условие AND iu.user_id <> i.invited_by_id также исключит самовызовы.
Присоединение таблицы users и добавление условия AND u.created_at > iu.redeemed_at позволит исключить приглашение уже существующих пользователей.
Это работает не очень хорошо. Иногда пользователь создаётся чуть раньше, чем происходит искупление. Не знаю почему. В основном это десятые доли секунды, но я также нашёл несколько случаев с десятками секунд.
Протестировано на реальной базе данных.
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
Не компенсирует ли добавление небольшого буфера, например AND u.created_at + INTERVAL '1 SECOND' > iu.redeemed_at, это, не оказывая при этом слишком сильного влияния?
Мне это кажется излишней «магией», так как мы не знаем, что вызывает задержку.
Я подозреваю, что задержка вызвана большой очередью Sidekiq или чем-то подобным. Хотя большинство задержек составляет менее одной секунды, около 3% находятся в диапазоне от 0:00:01 до 0:15:00 (от одной секунды до 15 минут).
И полпроцента составляют задержки в порядке дней, что похоже на тот вид злоупотреблений, который мы пытаемся предотвратить. Поэтому, хотя это и эффективно, это принесет больше вреда, чем пользы из-за большого количества ложных срабатываний.
):