From: Spine
To: Developers
Date: 2026-04-04
Subject: Orpheus Development Papers #3 - Voting Implementation
Version: 3

Update: the tables have been migrated from Mysql to Postgresql.

When a person upvotes or downvotes a release group, that information is recorded
in the `tgroup_vote` table (user id, group id, upvote and timestamp). An upvote
is true, a downvote is false.

The aggregate votes for a release group are denormalized and stored in the
tgroup_vote_summary table. For the purpose of scoring, the algorithm looks at total
positive votes compared to overall votes.

For example, if a torrent has received two downvotes and three upvotes, there
will be 5 rows (from the five voters) in the tgroup_vote table, and the
tgroup_vote_summary table will contain one row with total = 5, upvote = 3.

If the `tgroup_vote_summary` table is ever corrupted, it may be recreated with
`bin/rebuild-tgroup-vote`.

The user and group vote information are both cached. As the user object can
become quite large, care must be taken to update the cache on writes. This is
probably one of the few places where it is too expensive to simply delete the
key and rely on the next read to refresh it.

Consider three users who have voted on a number of different groups:

Table 1: Upvotes by user
 id_user | upvotes
---------+-----------------------------
       1 | {14,27,30,31,54,68,71,73,78}
       2 | {6,14,49,64,69,70}
       6 | {14,46,54,58,64}
source> select id_user, array_agg(id_tgroup) as upvotes from tgroup_vote where upvote is true group by id_user;

Table 2: Downvotes by user
 id_user | downvotes
---------+--------------
       1 | {58,60,69,72}
       2 | {21,30,60,73}
       6 | {31,68,69}
+--------+--------------
source> select id_user, array_agg(id_tgroup) as downvotes from tgroup_vote where upvote is false group by id_user;

This is summarized in the tgroup_vote_summary table as follows:

Table 3: Votes by release group
 id_tgroup | upvote | total
-----------+--------+-------
         6 |      1 |     1
        14 |      3 |     3
        21 |      0 |     1
        27 |      1 |     1
        30 |      1 |     2
        31 |      1 |     2
        46 |      1 |     1
        49 |      1 |     1
        54 |      2 |     2
        58 |      1 |     2
        60 |      0 |     2
        64 |      2 |     2
        68 |      1 |     2
        69 |      1 |     3
        70 |      1 |     1
        71 |      1 |     1
        72 |      0 |     1
        73 |      1 |     2
        78 |      1 |     1

Additionally, a further denormalization takes place entirely within the cache,
to implement the "people who liked X also liked Y" functionality. This can be
expressed in terms of a query as "Get the list of people who upvoted this group
and see what other groups they upvoted".

As an example, for group 58 (upvoted by user 6), the algorithm is as follows:

Table 4: Votes scores for groups than 58 also upvoted by user 6
 id_tgroup | upvote | total
-----------+--------+-------
        14 |      1 |     1
        46 |      1 |     1
        54 |      1 |     1
        64 |      1 |     1

source>
    select tvs.id_tgroup
    from tgroup_vote_summary tvs
    inner join tgroup_vote tv using (id_tgroup)
    where tv.id_tgroup != 58
        and tvs.score > 0.3125
        and tv.id_user in (
            select tv.id_user
            from tgroup_vote tv
            where tv.id_tgroup = 58
        )
    order by tvs.score desc

As user 6 is the only person who upvoted group 58, the "X likes Y" for that
group consists of only their other (non-group-58) upvotes.

At a later point, user 2 also upvotes group 58. The "X likes Y" now consists of
the upvotes from users 2 and 6:

Table 5: Other groups than 58 also upvoted by users 2, 6
id_tgroup | upvote | total
----------+--------+-------
|       6 |      1 |     1
|      14 |      2 |     2
|      46 |      1 |     1
|      49 |      1 |     1
|      54 |      1 |     1
|      64 |      2 |     2
|      69 |      1 |     2
|      70 |      1 |     1

The difference between Table 4 and Table 5 is the addition of the upvotes by
user 2 (6,14,49,64,69,70). This creates new rows for groups 6, 49, 69 and 70,
and increments the existing scores for 14 and 64 that both users voted on.

Also note that user 6 downvoted group 69. Since a group that has attracted no
upvotes is of no interest to answering the question, group 69 does not appear in
Table 4. Once user 2 upvotes 69, however, it becomes of interest. At this point,
the votes by users 2 and 6 are counted, which results in upvote:1, total:2.

The problem here, is that user downvoted release 58, and won't be factored into
the vote scores... but they too have also downvoted release 69. This means the
scores for both 58 69 arer higher than they should be.

To correct this, the `upvote is true` is removed from the inner select query:
Table 6: Any groups paired with a vote on group 58
 id_tgroup | upvotes | total
-----------+---------+-------
         6 |       1 |     1
        14 |       3 |     3
        27 |       1 |     1
        30 |       1 |     2
        31 |       1 |     2
        46 |       1 |     1
        49 |       1 |     1
        54 |       2 |     2
        64 |       2 |     2
        68 |       1 |     2
        69 |       1 |     3
        70 |       1 |     1
        71 |       1 |     1
        73 |       1 |     2
        78 |       1 |     1
source> select v.id_tgroup, sum(upvote::int) as upvotes, count(*) as total
from tgroup_vote v
inner join (select id_user from tgroup_vote where id_tgroup = 58) a using (id_user)
where v.id_tgroup != 58
group by v.id_tgroup
having upvotes > 0;

Adding the binomial score into the results makes the picture clearer:
Table 7: Binomial score from users who upvoted 58
 id_tgroup | upvotes | total | score
-----------+---------+-------+----------
        64 |       2 |     2 | 0.424928
        14 |       2 |     2 | 0.424928
        70 |       1 |     1 | 0.269784
        54 |       1 |     1 | 0.269784
        49 |       1 |     1 | 0.269784
        46 |       1 |     1 | 0.269784
         6 |       1 |     1 | 0.269784
        69 |       1 |     2 | 0.120834
> select v.id_tgroup,
    sum(upvote::int) upvotes,
    count(*) total,
    binomial_ci(sum(upvote::int)::float, count(*)::float) score
from tgroup_vote v
inner join (select id_user from tgroup_vote where id_tgroup = 58) a using (id_user)
where v.id_tgroup != 58
group by v.id_tgroup
having upvotes > 0;
order by score desc, v.id_tgroup desc;

Versus:
Table 8: Binomial score from users who voted either way on 58
 id_tgroup | ups  | total | score
-----------+------+-------+-----------+
        14 |    3 |     3 |  0.525699 |
        64 |    2 |     2 |  0.424928 |
        54 |    2 |     2 |  0.424928 |
        78 |    1 |     1 |  0.269784 |
        71 |    1 |     1 |  0.269784 |
        70 |    1 |     1 |  0.269784 |
        49 |    1 |     1 |  0.269784 |
        46 |    1 |     1 |  0.269784 |
        27 |    1 |     1 |  0.269784 |
         6 |    1 |     1 |  0.269784 |
        73 |    1 |     2 |  0.120834 |
        68 |    1 |     2 |  0.120834 |
        31 |    1 |     2 |  0.120834 |
        30 |    1 |     2 |  0.120834 |
        69 |    1 |     3 |  0.078241 |
> select v.id_tgroup,
    sum(upvote::int) ups,
    count(*) total,
    binomial_ci(sum(upvote::int)::float, count(*)::float) score
from tgroup_vote AS v
inner join (
    select id_user from tgroup_vote where id_tgroup = 58
) a using (id_user)
where v.id_tgroup != 58
group by v.id_tgroup
having ups > 0
order by score desc, v.id_tgroup desc;

In other words, the scores of 14 and 54 are revised upwards and some new entries
(27, 71, 78, upvoted by user 1) are brought into consideration.
