Repository Public
1import "../../scripts/_env";2import os from "node:os";3import { j, sql } from "@/lib/db";4import { errInfo, log } from "@/lib/log";5import { audit } from "@/lib/audit";6import { AiError, resolveModel } from "@/ai/client";7import { runEdit, runGenerate, runOutline, runTranslate, type JobCtx } from "@/ai/pipeline";8import { PROMPT_VERSION } from "@/ai/prompts";9import { checkSources } from "@/ai/sources";10import { mustParseSheet } from "@/schema";11import { getSettings } from "@/server/settings";12import { getSheetRow, saveDoc } from "@/server/sheets";13import { storage } from "@/server/storage";14import { chromiumAvailable, closeBrowser, runExport } from "./export";1516/**17 * Sheet worker: one process that runs the long jobs — Claude calls (outline, generation, edit, translation),18 * reference checks and exports. Jobs live in PostgreSQL and are claimed with FOR UPDATE SKIP LOCKED, so a job is19 * never run twice at the same time and none is lost when this process or the web process restarts:20 * - at start-up, jobs left "running" by a previous worker go back to the queue;21 * - on SIGTERM the current jobs are aborted and re-queued without counting an attempt;22 * - handlers are idempotent (a generation that already produced its revision returns it).23 */24const WORKER = `${os.hostname()}:${process.pid}`;25const AI_CONCURRENCY = 2;26let stopping = false;27const running = new Map<string, AbortController>();2829interface Job { id: string; kind: "outline" | "generate" | "edit" | "translate" | "verify_sources"; sheet_id: string | null; input: unknown; attempts: number; max_attempts: number; created_by: string | null }3031async function claim(): Promise<Job | null> {32 const [job] = await sql<Job[]>`33 UPDATE generation_jobs SET status = 'running', locked_by = ${WORKER}, locked_at = now(), started_at = coalesce(started_at, now()), attempts = attempts + 1, stage = 'starting', prompt_version = ${PROMPT_VERSION}34 WHERE id = (SELECT id FROM generation_jobs WHERE status = 'queued' AND run_after <= now() AND attempts < max_attempts ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1)35 RETURNING id, kind, sheet_id, input, attempts, max_attempts, created_by`;36 return job ?? null;37}3839async function verifySources(ctx: JobCtx, sheetId: string) {40 const first = mustParseSheet((await getSheetRow(sheetId)).doc);41 await ctx.stage("verify_sources", { done: 0, total: first.sources.length });42 const { checks } = await checkSources(first, (done, total) => void ctx.stage("verify_sources", { done, total }));43 // The author may have kept editing: apply the verdicts to the newest copy, by source id, and only to sources44 // whose locator is the one that was checked.45 for (let attempt = 0; ; attempt++) {46 const row = await getSheetRow(sheetId);47 const doc = mustParseSheet(row.doc);48 const at = new Date().toISOString();49 const next = { ...doc, sources: doc.sources.map((s) => { const c = checks.find((x) => x.id === s.id); const was = first.sources.find((x) => x.id === s.id); return c && was && was.doi === s.doi && was.url === s.url && was.title === s.title ? { ...s, status: c.status, checkedAt: at } : s; }) };50 try {51 const saved = await saveDoc({ sheetId, doc: next, expectedVersion: row.version, origin: "source_check", summary: `Vérification des sources : ${checks.filter((c) => c.status === "resolved").length}/${checks.length} trouvées`, jobId: ctx.jobId, userId: ctx.userId });52 return { checks, version: saved.version, revisionId: saved.revisionId, changed: saved.changed };53 } catch (e) { if (attempt >= 2 || !(e instanceof Error && "code" in e && (e as { code: string }).code === "version_conflict")) throw e; }54 }55}5657async function runJob(job: Job): Promise<void> {58 const controller = new AbortController();59 running.set(job.id, controller);60 const settings = await getSettings();61 const deadline = Date.now() + settings.aiJobTimeoutMinutes * 60_000;62 let timedOut = false, cancelled = false;63 // Cancellation requested from the studio and the wall-clock limit are both enforced here, once a second.64 const watch = setInterval(() => {65 void sql<{ cancel_requested: boolean }[]>`SELECT cancel_requested FROM generation_jobs WHERE id = ${job.id}`.then(([r]) => { if (r?.cancel_requested) { cancelled = true; controller.abort(); } }).catch(() => {});66 if (Date.now() > deadline) { timedOut = true; controller.abort(); }67 }, 1000);68 const ctx: JobCtx = {69 jobId: job.id, userId: job.created_by, signal: controller.signal,70 stage: async (stage, progress = {}) => { await sql`UPDATE generation_jobs SET stage = ${stage}, progress = ${j(progress)}, locked_at = now() WHERE id = ${job.id} AND status = 'running'`; },71 };72 const started = Date.now();73 try {74 if (job.kind !== "verify_sources") await sql`UPDATE generation_jobs SET model = ${await resolveModel(settings)} WHERE id = ${job.id}`;75 let output: unknown;76 switch (job.kind) {77 case "outline": output = await runOutline(ctx, job.input); break;78 case "generate": output = await runGenerate(ctx, job.input); break;79 case "edit": output = await runEdit(ctx, job.input, job.sheet_id!); break;80 case "translate": output = await runTranslate(ctx, job.input, job.sheet_id!); break;81 case "verify_sources": output = await verifySources(ctx, job.sheet_id!); break;82 }83 await sql`UPDATE generation_jobs SET status = 'succeeded', stage = 'done', output = ${j(output)}, finished_at = now(), locked_by = NULL, error = NULL WHERE id = ${job.id}`;84 log("info", "job.succeeded", { jobId: job.id, kind: job.kind, ms: Date.now() - started });85 } catch (e) {86 const ai = e instanceof AiError ? e : null;87 if (stopping && controller.signal.aborted && !cancelled && !timedOut) {88 // Shutdown: give the job back untouched.89 await sql`UPDATE generation_jobs SET status = 'queued', stage = 'queued', attempts = greatest(attempts - 1, 0), locked_by = NULL WHERE id = ${job.id}`;90 } else if (cancelled) {91 await sql`UPDATE generation_jobs SET status = 'cancelled', stage = 'cancelled', finished_at = now(), locked_by = NULL WHERE id = ${job.id}`;92 log("info", "job.cancelled", { jobId: job.id, kind: job.kind });93 } else if (ai?.retryable && job.attempts < job.max_attempts && !timedOut) {94 const delay = 30 * 2 ** (job.attempts - 1);95 await sql`UPDATE generation_jobs SET status = 'queued', stage = 'waiting_retry', run_after = now() + ${delay} * interval '1 second', locked_by = NULL, error = ${j({ code: ai.code, message: ai.message })} WHERE id = ${job.id}`;96 log("warn", "job.retry", { jobId: job.id, kind: job.kind, code: ai.code, inSeconds: delay });97 } else {98 const error = timedOut ? { code: "timeout", message: `The job exceeded its time limit (${settings.aiJobTimeoutMinutes} min)` }99 : ai ? { code: ai.code, message: ai.message }100 : e instanceof Error && "code" in e && "status" in e ? { code: String((e as { code: unknown }).code), message: e.message }101 : { code: "internal", message: "Internal error (see the worker log)" };102 await sql`UPDATE generation_jobs SET status = 'failed', stage = 'failed', error = ${j(error)}, finished_at = now(), locked_by = NULL WHERE id = ${job.id}`;103 log("error", "job.failed", { jobId: job.id, kind: job.kind, ...error, ...(ai ? {} : errInfo(e)) });104 await audit({ userId: job.created_by, actor: "worker", action: "job.failed", entity: "job", entityId: job.id, meta: { kind: job.kind, code: error.code } });105 }106 } finally { clearInterval(watch); running.delete(job.id); }107}108109// ── Exports ─────────────────────────────────────────────────────────────────────────────────────────110111let exporting = false;112async function exportsTick(): Promise<void> {113 if (exporting || stopping) return;114 exporting = true;115 try {116 for (;;) {117 const [job] = await sql<{ id: string; sheet_id: string; revision_id: string | null; publication_id: string | null; format: "svg" | "png" | "pdf" | "json"; options: { scale?: number; blockId?: string | null }; is_public: boolean; attempts: number }[]>`118 UPDATE export_jobs SET status = 'running', locked_by = ${WORKER}, locked_at = now(), attempts = attempts + 1119 WHERE id = (SELECT id FROM export_jobs WHERE status = 'queued' AND run_after <= now() ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1) RETURNING *`;120 if (!job || stopping) { if (job) await sql`UPDATE export_jobs SET status = 'queued', attempts = attempts - 1 WHERE id = ${job.id}`; break; }121 const started = Date.now();122 try {123 const assetId = await runExport(job);124 await sql`UPDATE export_jobs SET status = 'succeeded', asset_id = ${assetId}, finished_at = now(), error = NULL, locked_by = NULL WHERE id = ${job.id}`;125 log("info", "export.succeeded", { exportId: job.id, format: job.format, ms: Date.now() - started });126 } catch (e) {127 const message = e instanceof Error ? e.message.split("\n")[0].slice(0, 400) : "Export failed";128 if (job.attempts < 2) await sql`UPDATE export_jobs SET status = 'queued', run_after = now() + interval '20 seconds', error = ${message}, locked_by = NULL WHERE id = ${job.id}`;129 else await sql`UPDATE export_jobs SET status = 'failed', error = ${message}, finished_at = now(), locked_by = NULL WHERE id = ${job.id}`;130 log("error", "export.failed", { exportId: job.id, format: job.format, attempt: job.attempts, detail: message });131 }132 }133 } finally { exporting = false; }134}135136// ── Housekeeping ────────────────────────────────────────────────────────────────────────────────────137138async function housekeeping(): Promise<void> {139 await sql`DELETE FROM webauthn_challenges WHERE expires_at < now() - interval '1 hour'`;140 await sql`DELETE FROM rate_limits WHERE window_start < now() - interval '2 days'`;141 await sql`DELETE FROM sessions WHERE expires_at < now() - interval '30 days' OR revoked_at < now() - interval '30 days'`;142 await sql`DELETE FROM setup_tokens WHERE expires_at < now() - interval '30 days'`;143 await sql`DELETE FROM preview_tokens WHERE expires_at < now() - interval '7 days'`;144 // Studio exports are downloads, not archives: files older than a week are removed (public PDFs are kept while145 // their publication is the current one).146 const old = await sql<{ id: string; storage_key: string }[]>`147 SELECT a.id, a.storage_key FROM assets a WHERE a.kind = 'export' AND a.deleted_at IS NULL AND a.created_at < now() - interval '7 days'148 AND NOT EXISTS (SELECT 1 FROM export_jobs e JOIN sheets s ON s.publication_id = e.publication_id WHERE e.asset_id = a.id AND e.is_public)`;149 for (const a of old) { await storage().remove(a.storage_key); await sql`UPDATE assets SET deleted_at = now() WHERE id = ${a.id}`; }150 await sql`DELETE FROM export_jobs WHERE created_at < now() - interval '30 days' AND NOT is_public`;151}152153async function main() {154 const st = await storage().health();155 if (!st.ok) throw new Error(`STORAGE_ROOT is not usable (${st.detail})`);156 const chrome = await chromiumAvailable();157 if (!chrome.ok) log("warn", "worker.chromium_missing", { detail: chrome.detail });158 // Whatever a previous worker left in flight goes back to the queue. (One worker process is assumed: with several,159 // this would have to look at locked_by and a heartbeat per worker.) A job that has already used all its attempts —160 // for instance one that kills the process each time — is closed instead of being retried for ever.161 await sql`UPDATE generation_jobs SET status = 'failed', stage = 'failed', finished_at = now(), locked_by = NULL, error = ${j({ code: "interrupted", message: "The job was interrupted too many times and was stopped" })} WHERE status IN ('running', 'queued') AND attempts >= max_attempts`;162 const back = await sql`UPDATE generation_jobs SET status = 'queued', stage = 'queued', locked_by = NULL WHERE status = 'running' RETURNING id`;163 const backE = await sql`UPDATE export_jobs SET status = 'queued', locked_by = NULL WHERE status = 'running' RETURNING id`;164 log("info", "worker.started", { worker: WORKER, requeued: back.length + backE.length, chromium: chrome.ok, promptVersion: PROMPT_VERSION });165166 const beat = async () => { await sql`INSERT INTO worker_heartbeat (name, beat_at, info) VALUES ('worker', now(), ${j({ worker: WORKER, running: running.size, chromium: chrome.ok, chromiumDetail: chrome.ok ? "" : chrome.detail, promptVersion: PROMPT_VERSION })})167 ON CONFLICT (name) DO UPDATE SET beat_at = now(), info = EXCLUDED.info`; };168 await beat();169 const beatTimer = setInterval(() => void beat().catch(() => {}), 15_000);170 const houseTimer = setInterval(() => void housekeeping().catch((e) => log("warn", "housekeeping.failed", errInfo(e))), 3600_000);171 void housekeeping().catch(() => {});172173 let wake: (() => void) | null = null;174 const nudge = () => { wake?.(); };175 const listener = await sql.listen("sheet_jobs", nudge).catch(() => null);176177 const stop = () => { if (stopping) return; stopping = true; log("info", "worker.stopping", { running: running.size }); for (const c of running.values()) c.abort(); nudge(); };178 process.on("SIGTERM", stop); process.on("SIGINT", stop);179180 while (!stopping) {181 try {182 while (running.size < AI_CONCURRENCY && !stopping) {183 const job = await claim();184 if (!job) break;185 log("info", "job.started", { jobId: job.id, kind: job.kind, attempt: job.attempts });186 void runJob(job).finally(nudge);187 }188 void exportsTick().catch((e) => log("error", "exports.tick", errInfo(e)));189 } catch (e) { log("error", "worker.loop", errInfo(e)); }190 await new Promise<void>((resolve) => { wake = resolve; setTimeout(resolve, 2000); });191 }192 clearInterval(beatTimer); clearInterval(houseTimer);193 const until = Date.now() + 20_000;194 while ((running.size > 0 || exporting) && Date.now() < until) await new Promise((r) => setTimeout(r, 200));195 await closeBrowser(); await listener?.unlisten().catch(() => {}); await sql.end({ timeout: 5 });196 process.exit(0);197}198main().catch((e) => { log("error", "worker.fatal", errInfo(e)); process.exit(1); });199