15926786676

Committed 27 Jun 2025 12:54PM UTC coverage: 39.941% (-0.2%) from 40.122%

Build # 15926786676

Build Type

push

github

Committed by

web-flow

Commit Message

Merge pull request #12045 from hicommonwealth/tim/rank-updates

Run Details

1850 of 5017 branches covered (36.87%)

Branch coverage included in aggregate %.

12 of 50 new or added lines in 2 files covered. (24.0%)

1 existing line in 1 file now uncovered.

3278 of 7822 relevant lines covered (41.91%)

36.97 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

0.0

/libs/model/src/aggregates/super-admin/ReRankThreads.command.ts

import {
  cache,
  CacheNamespaces,
  Command,
  InvalidInput,
} from '@hicommonwealth/core';
import * as schemas from '@hicommonwealth/schemas';
import { CommunityTierMap } from '@hicommonwealth/shared';
import { QueryTypes } from 'sequelize';
import { config } from '../../config';
import { models } from '../../database';
import { isSuperAdmin } from '../../middleware';

// Will only rerank threads that are up to 1-week-old (avoids reranking every thread in the DB)
export function RerankThreads(): Command<typeof schemas.RerankThreads> {
  return {
    ...schemas.RerankThreads,
    auth: [isSuperAdmin],
    body: async ({ payload }) => {
      if (config.APP_ENV === 'production')
        throw new InvalidInput('Not allowed in production');

      const { community_id } = payload;
      if (community_id) {
        const community = await models.Community.findByPk(community_id);
        if (!community) throw new InvalidInput('Community not found');
      }

      let ranks:
        | [
            {
              thread_id: number;
              community_rank: string;
              global_rank: string;
            }[],
            number,
          ]
        | undefined;
      await models.sequelize.transaction(async (transaction) => {
        if (!community_id) {
          await models.ThreadRank.truncate({ transaction });
        } else {
          await models.sequelize.query(
            `
              DELETE
              FROM "ThreadRanks"
                USING "Threads"
              WHERE "ThreadRanks".thread_id = "Threads".id
                AND "Threads".community_id = :community_id;
            `,
            { replacements: { community_id }, transaction },
          );
        }

        if (!community_id) {
          await cache().deleteNamespaceKeys(CacheNamespaces.GlobalThreadRanks);
          await cache().deleteNamespaceKeys(
            CacheNamespaces.CommunityThreadRanks,
          );
        } else {
          await cache().deleteKey(
            CacheNamespaces.CommunityThreadRanks,
            community_id,
          );
        }

        ranks = (await models.sequelize.query(
          `
            WITH ranks AS (SELECT T.id,
                                  T.user_tier_at_creation,
                                  T.view_count,
                                  T.created_at,
                                  CO.tier                      as community_tier,
                                  T.community_id,
                                  SUM(COALESCE(C.user_tier_at_creation, 0)) as comment_total,
                                  SUM(COALESCE(R.user_tier_at_creation, 0)) as reaction_total
                           FROM "Threads" T
                                  LEFT JOIN "Comments" C ON C.thread_id = T.id
                                  LEFT JOIN "Reactions" R ON R.thread_id = T.id
                                  JOIN "Communities" CO ON CO.id = T.community_id
                           WHERE T.created_at > NOW() - INTERVAL '7 weeks'
                             AND T.marked_as_spam_at IS NULL
                             AND T.deleted_at IS NULL
                             AND T.user_tier_at_creation IS NOT NULL
                             AND C.marked_as_spam_at IS NULL
                             AND CO.tier >= ${CommunityTierMap.ManuallyVerified}
                             AND LENGTH(T.body) >= 32
                             AND LENGTH(C.body) >= 32
                             AND CO.id != 'common'
                             ${community_id ? 'AND T.community_id = :community_id' : ''}
                           GROUP BY T.id, T.user_tier_at_creation, T.view_count, T.created_at, CO.tier, T.community_id),
                 base_ranks AS (SELECT id,
                                       view_count * :viewCountWeight +
                                       user_tier_at_creation * :creatorTierWeight +
                                       EXTRACT(EPOCH FROM created_at)::INTEGER / 60 * :createdDateWeight +
                                       comment_total * :commentWeight +
                                       reaction_total * :reactionWeight AS base_rank,
                                       community_tier,
                                       community_id
                                FROM ranks)
            INSERT
            INTO "ThreadRanks" (thread_id, community_rank, global_rank, updated_at)
            SELECT br.id,
                   br.base_rank,
                   br.base_rank + (br.community_tier * :communityTierWeight),
                   NOW()
            FROM base_ranks br
            RETURNING thread_id, community_rank, global_rank;
          `,
          {
            type: QueryTypes.UPDATE,
            replacements: {
              community_id,
              viewCountWeight: config.HEURISTIC_WEIGHTS.VIEW_COUNT_WEIGHT,
              creatorTierWeight:
                config.HEURISTIC_WEIGHTS.CREATOR_USER_TIER_WEIGHT,
              createdDateWeight: config.HEURISTIC_WEIGHTS.CREATED_DATE_WEIGHT,
              commentWeight: config.HEURISTIC_WEIGHTS.COMMENT_WEIGHT,
              reactionWeight: config.HEURISTIC_WEIGHTS.LIKE_WEIGHT,
              communityTierWeight:
                config.HEURISTIC_WEIGHTS.COMMUNITY_TIER_WEIGHT,
            },
            transaction,
          },
        )) as unknown as [
          {
            thread_id: number;
            community_rank: string;
            global_rank: string;
          }[],
          number,
        ];

        const communityIds = (
          await models.Thread.findAll({
            attributes: ['id', 'community_id'],
            where: {
              id: ranks[0].map((r) => r.thread_id),
            },
          })
        ).reduce(
          (acc, val) => {
            acc[String(val.id)] = val.community_id;
            return acc;
          },
          {} as Record<string, string>,
        );

        const communityRankUpdates: {
          [community_id: string]: { value: string; score: number }[];
        } = {};
        const globalRankUpdates: { value: string; score: number }[] = [];
        for (const { thread_id, community_rank, global_rank } of ranks[0]) {
          const commmunityId = communityIds[String(thread_id)];
          if (!communityRankUpdates[commmunityId]) {
            communityRankUpdates[commmunityId] = [];
          }
          communityRankUpdates[commmunityId].push({
            value: String(thread_id),
            score: Number(community_rank),
          });
          globalRankUpdates.push({
            value: String(thread_id),
            score: Number(global_rank),
          });
        }

        for (const communityId in communityRankUpdates) {
          if (communityRankUpdates[communityId].length > 0) {
            await cache().addToSortedSet(
              CacheNamespaces.CommunityThreadRanks,
              communityId,
              communityRankUpdates[communityId],
            );
          }
        }

        if (globalRankUpdates.length > 0) {
          await cache().addToSortedSet(
            CacheNamespaces.GlobalThreadRanks,
            'all',
            globalRankUpdates,
          );
        }
      });

      return { numThreadsReranked: ranks ? ranks[0].length : 0 };
    },
  };
}

1	import {
2	cache,
3	CacheNamespaces,
4	Command,
5	InvalidInput,
6	} from '@hicommonwealth/core';
7	import * as schemas from '@hicommonwealth/schemas';
8	import { CommunityTierMap } from '@hicommonwealth/shared';
9	import { QueryTypes } from 'sequelize';
10	import { config } from '../../config';
11	import { models } from '../../database';
12	import { isSuperAdmin } from '../../middleware';
13
14	// Will only rerank threads that are up to 1-week-old (avoids reranking every thread in the DB)
15	export function RerankThreads(): Command<typeof schemas.RerankThreads> {
NEW 16	return {	×
17	...schemas.RerankThreads,
18	auth: [isSuperAdmin],
19	body: async ({ payload }) => {
NEW 20	if (config.APP_ENV === 'production')	×
NEW 21	throw new InvalidInput('Not allowed in production');	×
22
NEW 23	const { community_id } = payload;	×
NEW 24	if (community_id) {	×
NEW 25	const community = await models.Community.findByPk(community_id);	×
NEW 26	if (!community) throw new InvalidInput('Community not found');	×
27	}
28
29	let ranks:
30	\| [
31	{
32	thread_id: number;
33	community_rank: string;
34	global_rank: string;
35	}[],
36	number,
37	]
38	\| undefined;
NEW 39	await models.sequelize.transaction(async (transaction) => {	×
NEW 40	if (!community_id) {	×
NEW 41	await models.ThreadRank.truncate({ transaction });	×
42	} else {
NEW 43	await models.sequelize.query(	×
44	`
45	DELETE
46	FROM "ThreadRanks"
47	USING "Threads"
48	WHERE "ThreadRanks".thread_id = "Threads".id
49	AND "Threads".community_id = :community_id;
50	`,
51	{ replacements: { community_id }, transaction },
52	);
53	}
54
NEW 55	if (!community_id) {	×
NEW 56	await cache().deleteNamespaceKeys(CacheNamespaces.GlobalThreadRanks);	×
NEW 57	await cache().deleteNamespaceKeys(	×
58	CacheNamespaces.CommunityThreadRanks,
59	);
60	} else {
NEW 61	await cache().deleteKey(	×
62	CacheNamespaces.CommunityThreadRanks,
63	community_id,
64	);
65	}
66
NEW 67	ranks = (await models.sequelize.query(	×
68	`
69	WITH ranks AS (SELECT T.id,
70	T.user_tier_at_creation,
71	T.view_count,
72	T.created_at,
73	CO.tier as community_tier,
74	T.community_id,
75	SUM(COALESCE(C.user_tier_at_creation, 0)) as comment_total,
76	SUM(COALESCE(R.user_tier_at_creation, 0)) as reaction_total
77	FROM "Threads" T
78	LEFT JOIN "Comments" C ON C.thread_id = T.id
79	LEFT JOIN "Reactions" R ON R.thread_id = T.id
80	JOIN "Communities" CO ON CO.id = T.community_id
81	WHERE T.created_at > NOW() - INTERVAL '7 weeks'
82	AND T.marked_as_spam_at IS NULL
83	AND T.deleted_at IS NULL
84	AND T.user_tier_at_creation IS NOT NULL
85	AND C.marked_as_spam_at IS NULL
86	AND CO.tier >= ${CommunityTierMap.ManuallyVerified}
87	AND LENGTH(T.body) >= 32
88	AND LENGTH(C.body) >= 32
89	AND CO.id != 'common'
90	${community_id ? 'AND T.community_id = :community_id' : ''}	×
91	GROUP BY T.id, T.user_tier_at_creation, T.view_count, T.created_at, CO.tier, T.community_id),
92	base_ranks AS (SELECT id,
93	view_count * :viewCountWeight +
94	user_tier_at_creation * :creatorTierWeight +
95	EXTRACT(EPOCH FROM created_at)::INTEGER / 60 * :createdDateWeight +
96	comment_total * :commentWeight +
97	reaction_total * :reactionWeight AS base_rank,
98	community_tier,
99	community_id
100	FROM ranks)
101	INSERT
102	INTO "ThreadRanks" (thread_id, community_rank, global_rank, updated_at)
103	SELECT br.id,
104	br.base_rank,
105	br.base_rank + (br.community_tier * :communityTierWeight),
106	NOW()
107	FROM base_ranks br
108	RETURNING thread_id, community_rank, global_rank;
109	`,
110	{
111	type: QueryTypes.UPDATE,
112	replacements: {
113	community_id,
114	viewCountWeight: config.HEURISTIC_WEIGHTS.VIEW_COUNT_WEIGHT,
115	creatorTierWeight:
116	config.HEURISTIC_WEIGHTS.CREATOR_USER_TIER_WEIGHT,
117	createdDateWeight: config.HEURISTIC_WEIGHTS.CREATED_DATE_WEIGHT,
118	commentWeight: config.HEURISTIC_WEIGHTS.COMMENT_WEIGHT,
119	reactionWeight: config.HEURISTIC_WEIGHTS.LIKE_WEIGHT,
120	communityTierWeight:
121	config.HEURISTIC_WEIGHTS.COMMUNITY_TIER_WEIGHT,
122	},
123	transaction,
124	},
125	)) as unknown as [
126	{
127	thread_id: number;
128	community_rank: string;
129	global_rank: string;
130	}[],
131	number,
132	];
133
NEW 134	const communityIds = (	×
135	await models.Thread.findAll({
136	attributes: ['id', 'community_id'],
137	where: {
NEW 138	id: ranks[0].map((r) => r.thread_id),	×
139	},
140	})
141	).reduce(
142	(acc, val) => {
NEW 143	acc[String(val.id)] = val.community_id;	×
NEW 144	return acc;	×
145	},
146	{} as Record<string, string>,
147	);
148
149	const communityRankUpdates: {
150	[community_id: string]: { value: string; score: number }[];
NEW 151	} = {};	×
NEW 152	const globalRankUpdates: { value: string; score: number }[] = [];	×
NEW 153	for (const { thread_id, community_rank, global_rank } of ranks[0]) {	×
NEW 154	const commmunityId = communityIds[String(thread_id)];	×
NEW 155	if (!communityRankUpdates[commmunityId]) {	×
NEW 156	communityRankUpdates[commmunityId] = [];	×
157	}
NEW 158	communityRankUpdates[commmunityId].push({	×
159	value: String(thread_id),
160	score: Number(community_rank),
161	});
NEW 162	globalRankUpdates.push({	×
163	value: String(thread_id),
164	score: Number(global_rank),
165	});
166	}
167
NEW 168	for (const communityId in communityRankUpdates) {	×
NEW 169	if (communityRankUpdates[communityId].length > 0) {	×
NEW 170	await cache().addToSortedSet(	×
171	CacheNamespaces.CommunityThreadRanks,
172	communityId,
173	communityRankUpdates[communityId],
174	);
175	}
176	}
177
NEW 178	if (globalRankUpdates.length > 0) {	×
NEW 179	await cache().addToSortedSet(	×
180	CacheNamespaces.GlobalThreadRanks,
181	'all',
182	globalRankUpdates,
183	);
184	}
185	});
186
NEW 187	return { numThreadsReranked: ranks ? ranks[0].length : 0 };	×
188	},
189	};
190	}

hicommonwealth / commonwealth / 15926786676

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous