import crypto from "node:crypto"; import { setTimeout } from "node:timers/promises"; import { subDays } from "date-fns"; import { QueryTypes } from "sequelize"; import { toError } from "@shared/utils/error"; import { Hour } from "@shared/utils/time"; import env from "@server/env"; import Logger from "@server/logging/Logger"; import { TaskPriority } from "./base/BaseTask"; import type { PartitionInfo, Props } from "./base/CronTask"; import { CronTask, TaskInterval } from "./base/CronTask"; import { sequelize, sequelizeReadOnly } from "@server/storage/database"; /** * Number of hours to add to age to smooth the decay curve, * preventing brand new content from having disproportionately * high scores compared to content just a few hours old. */ const TIME_OFFSET_HOURS = 2; /** * Weight multipliers for different activity types relative to base score */ const ACTIVITY_WEIGHTS = { revision: 1.0, comment: 1.2, reaction: 0.8, view: 0.5, }; /** * Batch size for processing updates - kept small to minimize lock contention */ const BATCH_SIZE = 100; /** * Delay between batches in milliseconds to reduce sustained database pressure */ const INTER_BATCH_DELAY_MS = 500; /** * Base name for the working table used to track documents to process */ const WORKING_TABLE_PREFIX = "popularity_score_working"; /** * Number of hours over which to spread a single run. Partitions are scheduled * one minute apart across this window, so each handles a small slice of * documents. */ const SPREAD_HOURS = 1; interface DocumentScore { documentId: string; score: number; } export default class UpdateDocumentsPopularityScoreTask extends CronTask { /** * Unique table name for this task run to prevent conflicts with concurrent runs */ private workingTable: string = ""; public async perform({ partition, scheduledAt }: Props) { // Only run every X hours, skip other hours. Partitions of a single run are // spread over several hours, so this is based on when the run was // scheduled rather than when this partition happens to execute. const scheduledHour = new Date(scheduledAt ?? Date.now()).getHours(); if (scheduledHour % env.POPULARITY_UPDATE_INTERVAL_HOURS !== 0) { Logger.debug( "task", `Skipping popularity score update, will run at next ${env.POPULARITY_UPDATE_INTERVAL_HOURS}-hour interval (scheduled hour: ${scheduledHour})` ); return; } const now = new Date(); // Use UTC day boundaries to match how document_insights.date is written by // RollupDocumentInsightsTask (which derives dates via toISOString). Local // timezone could shift the window by a day in some deployments. const today = now.toISOString().slice(0, 10); const thresholdDate = subDays( now, env.POPULARITY_ACTIVITY_THRESHOLD_WEEKS * 7 ) .toISOString() .slice(0, 10); // Generate unique table name for this run to prevent conflicts const dateStr = now.toISOString().slice(0, 19).replace(/[-:T]/g, ""); const uniqueId = crypto.randomBytes(4).toString("hex"); this.workingTable = `${WORKING_TABLE_PREFIX}_${dateStr}_${uniqueId}`; try { // Clean up any stale working tables left behind by previous crashed runs await this.cleanupStaleWorkingTables(); // Setup: Create working table and populate with active document IDs const activeCount = await this.setupWorkingTable( thresholdDate, today, partition ); if (activeCount === 0) { Logger.info("task", "No documents with recent activity found"); return; } Logger.info( "task", `Found ${activeCount} documents with recent activity` ); // Process documents in independent batches let totalUpdated = 0; let totalErrors = 0; let batchNumber = 0; while (true) { batchNumber++; try { const updated = await this.processBatch(thresholdDate, today); totalUpdated += updated; Logger.debug( "task", `Batch ${batchNumber}: updated ${updated} documents` ); // A short batch means the working table is drained. if (updated < BATCH_SIZE) { break; } // Add delay between batches to reduce sustained pressure on the database await setTimeout(INTER_BATCH_DELAY_MS); } catch (error) { totalErrors++; Logger.error( `Batch ${batchNumber} failed after retries`, toError(error) ); // Remove failed batch from working table to prevent infinite loop await this.skipCurrentBatch(); } } Logger.info( "task", `Completed updating popularity scores: ${totalUpdated} documents updated, ${totalErrors} batch errors` ); } catch (error) { Logger.error( "Failed to update document popularity scores", toError(error) ); throw error; } finally { // Always clean up the working table await this.cleanupWorkingTable(); } } /** * Creates an unlogged working table and populates it with document IDs * that have recent activity. Unlogged tables are faster because they * skip WAL logging, and data loss on crash is acceptable here. * * @returns the number of documents added to the working table. */ private async setupWorkingTable( thresholdDate: string, today: string, partition: PartitionInfo ): Promise { // Drop any existing table first to avoid type conflicts from previous crashed runs await sequelize.query(`DROP TABLE IF EXISTS ${this.workingTable} CASCADE`); // Create unlogged table - faster than regular tables as it skips WAL logging await sequelize.query(` CREATE UNLOGGED TABLE ${this.workingTable} ( "documentId" UUID PRIMARY KEY, processed BOOLEAN DEFAULT FALSE ) `); const [startId, endId] = this.getPartitionBounds(partition); // Populate with documents that have recent activity OR a current non-zero // score (so dormant docs decay back to zero once activity falls out of the // window). Must be valid: published and not deleted. Process in chunks to // avoid long-running queries. Read from replica to avoid excessive locking. // The nil UUID is never generated, so no real document can be skipped by // starting the cursor there. let lastId = "00000000-0000-0000-0000-000000000000"; let insertedCount = 0; const chunkSize = 1000; while (true) { // Step 1: Read candidate document IDs from readonly replica to avoid // locking. The two sources are queried separately so that each can be // served by an index — combining them into a single OR forces a scan of // every document in the partition. Limiting each branch and then the // union still yields the globally lowest `chunkSize` ids, so keyset // pagination remains correct. const candidates = await sequelizeReadOnly.query<{ id: string; eligible: boolean; }>( ` WITH candidates AS ( ( SELECT DISTINCT di."documentId" AS id FROM document_insights di WHERE di.date >= :thresholdDate::date AND di.date <= :today::date AND di."documentId" >= :startId AND di."documentId" <= :endId AND di."documentId" > :lastId ORDER BY id LIMIT :limit ) UNION ( SELECT d.id FROM documents d WHERE d."popularityScore" > 0 AND d.id >= :startId AND d.id <= :endId AND d.id > :lastId ORDER BY id LIMIT :limit ) ) SELECT c.id, (d.id IS NOT NULL) AS eligible FROM candidates c LEFT JOIN documents d ON d.id = c.id AND d."publishedAt" IS NOT NULL AND d."deletedAt" IS NULL ORDER BY c.id LIMIT :limit `, { replacements: { thresholdDate, today, lastId, startId, endId, limit: chunkSize, }, type: QueryTypes.SELECT, } ); if (candidates.length === 0) { break; } // Step 2: Insert the eligible IDs into the working table on primary const ids = candidates.filter((c) => c.eligible).map((c) => c.id); if (ids.length > 0) { const result = await sequelize.query<{ documentId: string }>( ` INSERT INTO ${this.workingTable} ("documentId") SELECT * FROM unnest(ARRAY[:ids]::uuid[]) ON CONFLICT ("documentId") DO NOTHING RETURNING "documentId" `, { replacements: { ids }, type: QueryTypes.SELECT, } ); insertedCount += result.length; } lastId = candidates[candidates.length - 1].id; if (candidates.length < chunkSize) { break; } } Logger.debug( "task", `Populated working table with ${insertedCount} documents in ${Math.ceil(insertedCount / chunkSize)} chunks` ); // Create index on processed column for efficient batch selection await sequelize.query(` CREATE INDEX ON ${this.workingTable} (processed) WHERE NOT processed `); return insertedCount; } /** * Processes a batch of documents with retry logic. */ private async processBatch( thresholdDate: string, today: string ): Promise { // Step 1: Get batch of document IDs to process const batch = await sequelize.query<{ documentId: string }>( ` SELECT "documentId" FROM ${this.workingTable} WHERE NOT processed ORDER BY "documentId" LIMIT :limit `, { replacements: { limit: BATCH_SIZE }, type: QueryTypes.SELECT, } ); if (batch.length === 0) { return 0; } const documentIds = batch.map((b) => b.documentId); // Step 2: Calculate scores outside of a transaction const scores = await this.calculateScoresForDocuments( documentIds, thresholdDate, today ); // Step 3: Update document scores await this.updateDocumentScores(scores); // Step 4: Mark batch as processed await this.markBatchProcessed(documentIds); return documentIds.length; } /** * Calculates popularity scores for a set of documents by summing weighted, * time-decayed daily activity counts from the document_insights rollup. * This is a read-only operation that doesn't require locks. */ private async calculateScoresForDocuments( documentIds: string[], thresholdDate: string, today: string ): Promise { const results = await sequelizeReadOnly.query<{ documentId: string; total_score: string; }>( ` WITH batch_docs AS ( SELECT * FROM unnest(ARRAY[:documentIds]::uuid[]) AS t(id) ) SELECT bd.id AS "documentId", COALESCE(SUM( (di."revisionCount" * :revisionWeight + di."commentCount" * :commentWeight + di."reactionCount" * :reactionWeight + di."viewCount" * :viewWeight) / POWER( GREATEST( (:today::date - di.date) * 24 + :timeOffset, 0.1 ), :gravity ) ), 0) AS total_score FROM batch_docs bd LEFT JOIN document_insights di ON di."documentId" = bd.id AND di.date >= :thresholdDate::date AND di.date <= :today::date GROUP BY bd.id `, { replacements: { documentIds, thresholdDate, today, gravity: env.POPULARITY_GRAVITY, timeOffset: TIME_OFFSET_HOURS, revisionWeight: ACTIVITY_WEIGHTS.revision, commentWeight: ACTIVITY_WEIGHTS.comment, reactionWeight: ACTIVITY_WEIGHTS.reaction, viewWeight: ACTIVITY_WEIGHTS.view, }, type: QueryTypes.SELECT, } ); return results.map((r) => ({ documentId: r.documentId, score: parseFloat(r.total_score) || 0, })); } /** * Updates document scores in a minimal transaction. * Uses individual updates to minimize lock duration and contention. */ private async updateDocumentScores(scores: DocumentScore[]): Promise { if (scores.length === 0) { return; } // Update documents in a single batch to improve performance and reduce round-trips. // We use unnest with multiple arrays to ensure the IDs and scores stay aligned. // We also use SKIP LOCKED to avoid waiting on locked rows from other concurrent tasks. // NO KEY UPDATE does not block the KEY SHARE locks taken by inserts that reference documents. await sequelize.query( ` WITH lockable AS ( SELECT id FROM documents WHERE id = ANY(ARRAY[:ids]::uuid[]) FOR NO KEY UPDATE SKIP LOCKED ) UPDATE documents AS d SET "popularityScore" = s.score FROM ( SELECT * FROM unnest(ARRAY[:ids]::uuid[], ARRAY[:scores]::double precision[]) AS s(id, score) ) AS s WHERE d.id = s.id AND d.id IN (SELECT id FROM lockable) `, { replacements: { ids: scores.map((s) => s.documentId), scores: scores.map((s) => s.score), }, } ); } /** * Marks documents as processed in the working table */ private async markBatchProcessed(documentIds: string[]): Promise { await sequelize.query( ` UPDATE ${this.workingTable} SET processed = TRUE WHERE "documentId" = ANY(ARRAY[:documentIds]::uuid[]) `, { replacements: { documentIds }, } ); } /** * Marks current batch as processed without updating scores. * Used when a batch fails repeatedly to prevent infinite loops. */ private async skipCurrentBatch(): Promise { await sequelize.query( ` UPDATE ${this.workingTable} SET processed = TRUE WHERE "documentId" IN ( SELECT "documentId" FROM ${this.workingTable} WHERE NOT processed ORDER BY "documentId" LIMIT :limit ) `, { replacements: { limit: BATCH_SIZE } } ); } /** * Drops any stale working tables that were left behind by runs interrupted * before cleanup could occur (e.g. worker killed mid-run). Only removes * tables created well before the current run's window to avoid race * conditions with concurrent runs. */ private async cleanupStaleWorkingTables(): Promise { try { const cutoff = new Date(Date.now() - (SPREAD_HOURS + 2) * Hour.ms) .toISOString() .slice(0, 19) .replace(/[-:T]/g, ""); const tables = await sequelize.query<{ tablename: string }>( `SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE :prefix`, { replacements: { prefix: `${WORKING_TABLE_PREFIX}%`, }, type: QueryTypes.SELECT, } ); const prefixLen = WORKING_TABLE_PREFIX.length + 1; // +1 for underscore for (const { tablename } of tables) { const timestamp = tablename.slice(prefixLen, prefixLen + 14); if (timestamp < cutoff) { Logger.info("task", `Dropping stale working table: ${tablename}`); await sequelize.query(`DROP TABLE IF EXISTS "${tablename}" CASCADE`); } } } catch (error) { Logger.warn("Failed to clean up stale working tables", { error }); } } /** * Removes the working table */ private async cleanupWorkingTable(): Promise { try { await sequelize.query( `DROP TABLE IF EXISTS ${this.workingTable} CASCADE` ); } catch (error) { Logger.warn("Failed to clean up working table", { error }); } } public get cron() { return { interval: TaskInterval.Hour, partitionWindow: SPREAD_HOURS * Hour.ms, }; } public get options() { return { attempts: 1, priority: TaskPriority.Background, }; } }