Store reputation changes in UserActions (aka. reputation Ledger)

[Betree]

Today's reputation system is mainly based on a background job that checks all the actions that took place in the last x seconds, and updates the reputation in the Users table accordingly. There are some drawbacks with this approach:

• It's difficult to re-compute (and thus to audit) the reputation of a user
• It's difficult to compute some stats around the reputation changes, like a leaderboard to surface who gained the most reputation during the last x days (#209)
• It's difficult to surface what impacts the reputation in the frontend, and thus for users to understand these changes (#116)
• Limiting the reputation gains for a specific action (like it has been proposed for #56) would be really tedious to achieve with the current system.
• It's difficult to revert reputation changes. For example, it would be a pain to implement if we decide that removing a comment should revert the points gained/lost with the votes.

Proposal

Thanks to the UserActions table, we already have an history of all the actions that are taking place on the platform. We should attach two new columns to this table, to describe the reputation changes linked to these actions:

• authorReputationChange: reputation change for the person who triggered the action.
• targetReputationChange: reputation change for the person who's targeted by the action.

Computing the reputation for a user would then be as simple as summing the values in this table:

SELECT (
  COALSECE(SUM(actions_by_user.authorReputationChange), 0)
  + COALSECE(SUM(actions_targetting_user.targetReputationChange), 0)
) AS "reputation"
FROM "Users" u
LEFT JOIN "UserActions" actions_by_user ON actions_by_user.user_id = u.id
LEFT JOIN "UserActions" actions_targetting_user ON actions_targetting_user.target_user_id = u.id

In order to preserve the compatibility with existing code, and to avoid impacting performances too much, we should keep the apps/cf_jobs/lib/jobs/reputation.ex job and refactor it to update reputations based on the query above.

I would also recommend migrating all the old UserAction entries to this new system, but we must make sure it doesn't impact performance too much.

[Betree]

Change integrated into the ledger, though users.reputation is not using these values yet. Alos pushing a fix for the query in https://github.com/CaptainFact/captain-fact-api/pull/363.

To investigate the diffs:

WITH reputations AS (
  SELECT
    id,
    username,
    reputation,
    ( SELECT coalesce(sum(CASE WHEN user_id = u.id THEN author_reputation_change ELSE target_reputation_change END), 0) 
      FROM "users_actions" AS u0 
      WHERE ((u0."user_id" = u.id) OR (u0."target_user_id" = u.id))
    ) AS computed
  FROM users u
  WHERE u.reputation != 0
  ORDER BY u.id
) SELECT * FROM reputations
WHERE reputation != computed