Repository Public
1/** Job queue API used by the web process. The worker (src/worker) runs the jobs. */2import { j, sql } from "@/lib/db";3import { ApiError } from "@/lib/http";4import { audit } from "@/lib/audit";5import { rateLimit } from "@/lib/ratelimit";6import { getSettings } from "./settings";78export type JobKind = "outline" | "generate" | "edit" | "translate" | "verify_sources";9export type JobStatus = "queued" | "running" | "succeeded" | "failed" | "cancelled";10interface JobRow { id: string; kind: JobKind; status: JobStatus; sheet_id: string | null; input: unknown; output: unknown; stage: string; progress: unknown; error: unknown; attempts: number; model: string | null; prompt_version: string | null; cancel_requested: boolean; created_at: Date; started_at: Date | null; finished_at: Date | null; cost: string | null; in_tokens: number | null; out_tokens: number | null }1112const view = (r: JobRow) => ({13 id: r.id, kind: r.kind, status: r.status, sheetId: r.sheet_id, stage: r.stage, progress: r.progress, output: r.output, error: r.error as { code: string; message: string } | null,14 attempts: r.attempts, model: r.model, promptVersion: r.prompt_version, cancelRequested: r.cancel_requested,15 createdAt: r.created_at.toISOString(), startedAt: r.started_at?.toISOString() ?? null, finishedAt: r.finished_at?.toISOString() ?? null,16 usage: { costUsd: Number(r.cost ?? 0), inputTokens: r.in_tokens ?? 0, outputTokens: r.out_tokens ?? 0 },17});18export type JobView = ReturnType<typeof view>;1920const SELECT = sql`21 g.id, g.kind, g.status, g.sheet_id, g.input, g.output, g.stage, g.progress, g.error, g.attempts, g.model, g.prompt_version, g.cancel_requested, g.created_at, g.started_at, g.finished_at,22 (SELECT sum(cost_usd) FROM generation_usage u WHERE u.job_id = g.id) AS cost,23 (SELECT sum(input_tokens + cache_read_tokens + cache_write_tokens)::int FROM generation_usage u WHERE u.job_id = g.id) AS in_tokens,24 (SELECT sum(output_tokens)::int FROM generation_usage u WHERE u.job_id = g.id) AS out_tokens`;2526/**27 * Queues a job. `idempotencyKey` (sent by the browser, one per click) makes a retried request return the job that28 * already exists instead of paying for a second generation.29 */30export async function enqueueJob(input: { kind: JobKind; sheetId?: string | null; payload: unknown; userId: string; idempotencyKey?: string | null; ip?: string | null }): Promise<JobView> {31 if (input.idempotencyKey) {32 const [existing] = await sql<JobRow[]>`SELECT ${SELECT} FROM generation_jobs g WHERE g.idempotency_key = ${input.idempotencyKey}`;33 if (existing) return view(existing);34 }35 if (input.kind !== "verify_sources") {36 const s = await getSettings();37 await rateLimit(`jobs:${input.userId}`, s.aiJobsPerHour, 3600);38 }39 if (input.sheetId && (input.kind === "edit" || input.kind === "translate" || input.kind === "verify_sources")) {40 const [busy] = await sql`SELECT 1 FROM generation_jobs WHERE sheet_id = ${input.sheetId} AND status IN ('queued', 'running') AND kind IN ('edit', 'translate', 'verify_sources')`;41 if (busy) throw new ApiError(409, "job_in_progress", "Another request is already running on this sheet");42 }43 const [row] = await sql<{ id: string }[]>`44 INSERT INTO generation_jobs (kind, sheet_id, input, created_by, idempotency_key, max_attempts)45 VALUES (${input.kind}, ${input.sheetId ?? null}, ${j(input.payload)}, ${input.userId}, ${input.idempotencyKey ?? null}, ${input.kind === "verify_sources" ? 2 : 3})46 ON CONFLICT (idempotency_key) DO NOTHING RETURNING id`;47 if (!row) { // two identical requests raced: the other one created the job48 const [existing] = await sql<JobRow[]>`SELECT ${SELECT} FROM generation_jobs g WHERE g.idempotency_key = ${input.idempotencyKey ?? ""}`;49 if (existing) return view(existing);50 throw new ApiError(409, "conflict", "The request could not be queued; try again");51 }52 await sql`SELECT pg_notify('sheet_jobs', ${row.id})`;53 await audit({ userId: input.userId, actor: "admin", action: `job.queued.${input.kind}`, entity: "job", entityId: row.id, meta: { sheetId: input.sheetId ?? null }, ip: input.ip });54 return getJob(row.id);55}5657export async function getJob(id: string): Promise<JobView> {58 const [row] = await sql<JobRow[]>`SELECT ${SELECT} FROM generation_jobs g WHERE g.id = ${id}`;59 if (!row) throw new ApiError(404, "not_found", "Job not found");60 return view(row);61}6263export async function cancelJob(id: string, userId: string): Promise<JobView> {64 await getJob(id);65 // A queued job is cancelled on the spot; a running one is asked to stop and the worker aborts the model call.66 await sql`UPDATE generation_jobs SET status = 'cancelled', finished_at = now(), stage = 'cancelled' WHERE id = ${id} AND status = 'queued'`;67 await sql`UPDATE generation_jobs SET cancel_requested = true WHERE id = ${id} AND status = 'running'`;68 await audit({ userId, actor: "admin", action: "job.cancel", entity: "job", entityId: id });69 return getJob(id);70}7172export async function listJobs(q: { sheetId?: string; limit: number }) {73 const rows = await sql<JobRow[]>`SELECT ${SELECT} FROM generation_jobs g WHERE (${q.sheetId ?? null}::uuid IS NULL OR g.sheet_id = ${q.sheetId ?? null}) ORDER BY g.created_at DESC LIMIT ${q.limit}`;74 return rows.map(view);75}7677export async function queueHealth() {78 const [r] = await sql<{ queued: number; running: number; failed_24h: number; oldest: Date | null }[]>`79 SELECT count(*) FILTER (WHERE status = 'queued')::int AS queued, count(*) FILTER (WHERE status = 'running')::int AS running,80 count(*) FILTER (WHERE status = 'failed' AND finished_at > now() - interval '24 hours')::int AS failed_24h,81 min(created_at) FILTER (WHERE status = 'queued') AS oldest FROM generation_jobs`;82 const [e] = await sql<{ queued: number }[]>`SELECT count(*)::int AS queued FROM export_jobs WHERE status IN ('queued', 'running')`;83 const [w] = await sql<{ beat_at: Date; info: unknown }[]>`SELECT beat_at, info FROM worker_heartbeat WHERE name = 'worker'`;84 const age = w ? (Date.now() - w.beat_at.getTime()) / 1000 : null;85 return { queued: r.queued, running: r.running, failed24h: r.failed_24h, oldestQueuedAt: r.oldest?.toISOString() ?? null, exportsPending: e.queued, workerAlive: age !== null && age < 45, workerSeenSecondsAgo: age === null ? null : Math.round(age), workerInfo: w?.info ?? null };86}87