Skip to content

A one-of-a-kind resume builder that keeps your privacy in mind. Completely secure, customizable, portable, open-source and free forever. Try it out today!

58.7 KB
Raw Blame
import type { ApplicationDocumentKind, EditableFields } from "../../dto/application";
import type { DbOrTx } from "@reactive-resume/db/client";
import type {
	AiMetadata,
	ApplicationClosedReason,
	ApplicationStatus,
	ApplicationTimelineEntry,
	InterviewDetails,
} from "@reactive-resume/schema/applications/data";
import { createHash } from "node:crypto";
import { ORPCError } from "@orpc/client";
import { and, arrayContains, count, desc, eq, inArray, isNotNull, isNull, sql } from "drizzle-orm";
import { db } from "@reactive-resume/db/client";
import * as schema from "@reactive-resume/db/schema";
import { lintResumeForAts } from "@reactive-resume/resume/ats";
import { copyCoverLetterStyle } from "@reactive-resume/resume/cover-letter";
import { latestStageEntryId } from "@reactive-resume/schema/applications/data";
import { SUBMITTED_DOCUMENT_LOCKED } from "@reactive-resume/schema/applications/errors";
import { isDateOnlyFollowUp } from "@reactive-resume/schema/applications/follow-up";
import { canonicalOpportunityUrl } from "@reactive-resume/schema/applications/posting-url";
import { careerWorkspaceDataSchema, EMPTY_WORKSPACE } from "@reactive-resume/schema/career";
import { coverLetterSchema } from "@reactive-resume/schema/cover-letter/data";
import { generateId } from "@reactive-resume/utils/string";
import { requestRunCancellation } from "../agent/cancellation";
import { prepareRunAt } from "../career/scheduling";
import { coverLetterService, linkLetterToApplication } from "../cover-letters/service";
import { writeLetterVersion } from "../cover-letters/versions";
import { isRetriedCreate, lockUser, recordCreateRequest } from "../documents/ownership";
import { resumeService } from "../resume/service";
import { writeVersion } from "../resume/version-history";
import { getStorageService, uploadFile } from "../storage/service";
import { activeApplications } from "./access";
import { assertUntrackedPosting, findTrackedPosting } from "./duplicates";
import { syncFollowUp } from "./follow-up";
import { applicationTransitionValues, ensureApplicationPipeline, savedStageOf } from "./lanes";

/**
 * Saves a new application inside the caller's transaction, under the user's lock: Add application, and Copy for a job
 * when the job is saved with its copy. Returns its ID. With a client-chosen ID, `request` is what the client asked for:
 * a retry of the same request returns the application without saving again, and any other use of the ID is refused.
 */
export async function insertApplication(
	tx: DbOrTx,
	input: {
		userId: string;
		id?: string | undefined;
		request?: unknown;
		fields: EditableFields & { company: string; role: string };
		stage?: {
			status?: ApplicationStatus | undefined;
			laneId?: string | undefined;
			closedReason?: ApplicationClosedReason | null | undefined;
		};
		at?: Date;
	},
) {
	const id = input.id ?? generateId();
	const created = input.id ? { table: "application" as const, id, userId: input.userId, request: input.request } : null;
	await lockUser(tx, input.userId);
	if (created && (await isRetriedCreate(tx, created))) return id;
	await assertUntrackedPosting(tx, input.userId, input.fields.sourceUrl);
	const { values } = await applicationTransitionValues(tx, input.userId, input.stage ?? {}, undefined, input.at);
	const [row] = await tx
		.insert(schema.application)
		.values({
			id,
			userId: input.userId,
			...input.fields,
			followUpDateOnly:
				input.fields.followUpDateOnly ??
				isDateOnlyFollowUp({
					followUpAt: input.fields.followUpAt ?? null,
					followUpDateOnly: input.fields.followUpDateOnly,
				}),
			...values,
		})
		.returning();
	if (created) await recordCreateRequest(tx, created);
	if (row) {
		await recordSentResume(row, tx);
		if (row.coverLetterId)
			await linkLetterToApplication(tx, { userId: input.userId, letterId: row.coverLetterId, applicationId: row.id });
		if (row.followUpAt) await syncFollowUp(tx, row);
	}
	return id;
}

function timelineDate(value: Date | string): Date {
	return value instanceof Date ? value : new Date(value);
}

function atFromDateString(date: string, existing?: Date | string): Date {
	const [year, month, day] = date.split("-").map(Number);
	if (!year || !month || !day) throw new ORPCError("BAD_REQUEST", { message: "Enter the date as YYYY-MM-DD." });

	const existingDate = existing ? timelineDate(existing) : undefined;

	const parsed = new Date(
		Date.UTC(
			year,
			month - 1,
			day,
			existingDate?.getUTCHours() ?? 12,
			existingDate?.getUTCMinutes() ?? 0,
			existingDate?.getUTCSeconds() ?? 0,
			existingDate?.getUTCMilliseconds() ?? 0,
		),
	);
	if (timelineDay(parsed) !== date) throw new ORPCError("BAD_REQUEST", { message: "Enter the date as YYYY-MM-DD." });

	return parsed;
}

function noteEntry(text: string, date?: string): Extract<ApplicationTimelineEntry, { type: "note" }> {
	return { id: generateId(), type: "note", text, at: date ? atFromDateString(date) : new Date() };
}

function interviewAt(value: string): Date {
	const parsed = new Date(value);
	if (Number.isNaN(parsed.getTime()))
		throw new ORPCError("BAD_REQUEST", { message: "Enter a valid interview date and time." });
	return parsed;
}

function interviewEntry(at: string, details: InterviewDetails): ApplicationTimelineEntry {
	return { id: generateId(), type: "interview", at: interviewAt(at), ...details };
}

function timelineDay(value: Date | string) {
	return timelineDate(value).toISOString().slice(0, 10);
}

function sortTimeline(activity: ApplicationTimelineEntry[]): ApplicationTimelineEntry[] {
	return [...activity].sort(
		(a, b) =>
			new Date(b.at).getTime() - new Date(a.at).getTime() ||
			(b.type === "stage" ? (b.writeSequence ?? 0) : 0) - (a.type === "stage" ? (a.writeSequence ?? 0) : 0),
	);
}

/** Lane-only transitions also anchor the current stage. Retirement preserves the last event's identity. */
function currentStageAnchor(activity: ApplicationTimelineEntry[], status: ApplicationStatus) {
	const id = latestStageEntryId(activity);
	return (
		activity.find((entry) => entry.id === id && entry.type === "stage" && entry.stage === status) ??
		sortTimeline(activity).find((entry) => entry.type === "stage" && entry.stage === status)
	);
}

function assertCurrentStageAnchorLatest(
	activity: ApplicationTimelineEntry[],
	status: ApplicationStatus,
	anchorId?: string,
) {
	// Capture identity before editing dates; re-resolving after a date edit could select another lane's event.
	const anchor = anchorId ? activity.find((entry) => entry.id === anchorId) : currentStageAnchor(activity, status);
	if (!anchor)
		throw new ORPCError("BAD_REQUEST", {
			message: "This application's timeline is missing its current stage. Reload the application and try again.",
		});
	if (anchor.type === "stage" && anchor.dateUnknown) return;
	const anchorDay = timelineDay(anchor.at);
	if (activity.some((entry) => entry.type === "stage" && !entry.dateUnknown && timelineDay(entry.at) > anchorDay)) {
		throw new ORPCError("BAD_REQUEST", { message: "Choose a current stage date on or after the other stage dates." });
	}
}

function appliedAtFromTimeline(activity: ApplicationTimelineEntry[], fallback: Date): Date {
	const sorted = sortTimeline(activity).filter(
		(entry) => entry.type !== "stage" || entry.canonicalTransition !== false,
	);
	const applied = sorted.find((entry) => entry.type === "stage" && entry.stage === "applied");
	const fallbackEntry = sorted.at(-1);
	return applied ? timelineDate(applied.at) : fallbackEntry ? timelineDate(fallbackEntry.at) : fallback;
}

// All reads/writes filter on userId — the single ownership guard every route funnels through.
async function requireOwned(id: string, userId: string) {
	const [row] = await db
		.select()
		.from(schema.application)
		.where(and(eq(schema.application.id, id), activeApplications(userId)));
	if (!row) throw new ORPCError("NOT_FOUND");
	return row;
}

export async function assertApplicationVersion(
	reader: DbOrTx,
	userId: string,
	expected: { id: string; updatedAt: Date },
) {
	const [current] = await reader
		.select({ updatedAt: schema.application.updatedAt })
		.from(schema.application)
		.where(and(eq(schema.application.id, expected.id), activeApplications(userId)))
		.for("share");
	if (!current) throw new ORPCError("NOT_FOUND");
	if (current.updatedAt.getTime() !== expected.updatedAt.getTime())
		throw new ORPCError("CONFLICT", { message: "The application changed. Review it and try again." });
}

async function assertOwnedResume(userId: string, resumeId: string | null | undefined) {
	if (!resumeId) return;
	await resumeService.getById({ id: resumeId, userId });
}

async function assertOwnedCoverLetter(userId: string, coverLetterId: string | null | undefined) {
	if (!coverLetterId) return;
	await coverLetterService.getById({ id: coverLetterId, userId });
}

// Stages at which an application has been sent.
const SENT_STAGES = new Set<ApplicationStatus>(["applied", "screening", "interview", "offer"]);

/** Whether a stage means the application has already been sent (and so carries a submission record). */
export const isSentStage = (status: ApplicationStatus) => SENT_STAGES.has(status);

type ApplicationRow = typeof schema.application.$inferSelect;

type SubmissionHistory = Pick<
	ApplicationRow,
	"status" | "activity" | "sentResumeVersionId" | "sentCoverLetterVersionId" | "submittedAt"
>;

/**
 * Whether this application has already been submitted at any point in its life. The immediate status alone is not
 * enough: a Closed job may have been Applied earlier, and reopening it must not turn a late-linked draft into a new
 * sent receipt. `submittedAt` is durable (timeline edits cannot remove it); sent versions and a stage entry in a sent
 * stage are fallbacks for rows created before the marker existed. appliedAt is not sound (Saved rows carry a default).
 */
export const hasSubmitted = (row: SubmissionHistory) =>
	row.submittedAt != null ||
	isSentStage(row.status) ||
	Boolean(row.sentResumeVersionId) ||
	Boolean(row.sentCoverLetterVersionId) ||
	row.activity.some((entry) => entry.type === "stage" && isSentStage(entry.stage));

/**
 * Once an application has been sent (Applied or later), its linked resume is saved as a "sent" version, named after
 * the company, with its Check score at the time, and so is its linked letter. The application keeps pointing at
 * them, so it can open exactly what went out while the documents move on.
 */
export async function recordSentResume(
	row: ApplicationRow,
	client: DbOrTx,
	previouslySubmitted = false,
): Promise<ApplicationRow> {
	if (!SENT_STAGES.has(row.status)) return row;
	const changes: Partial<ApplicationRow> = {};
	const recordedAt = row.submittedAt ?? new Date();
	// The first sent snapshot also fixes the durable lifetime submission marker. Later timeline edits cannot erase it.
	if (!row.submittedAt) changes.submittedAt = recordedAt;
	const [workspace] = await client
		.select()
		.from(schema.careerWorkspace)
		.where(and(eq(schema.careerWorkspace.applicationId, row.id), eq(schema.careerWorkspace.userId, row.userId)));
	if (!workspace?.submissionReview && !row.submittedAt && !previouslySubmitted) {
		const data = careerWorkspaceDataSchema.parse({ ...EMPTY_WORKSPACE, ...workspace?.data });
		const submissionReview = {
			at: recordedAt.toISOString(),
			checklist: [...data.checklist],
			documents: Boolean(row.resumeId || row.coverLetterId || row.resumeFileUrl || row.coverLetterUrl),
			answers: data.answers.length > 0,
			answerVersionId: null,
			stageEntryId:
				row.activity.findLast(
					(entry) => entry.type === "stage" && entry.stage === "applied" && entry.canonicalTransition !== false,
				)?.id ?? null,
		};
		await client
			.insert(schema.careerWorkspace)
			.values({ applicationId: row.id, userId: row.userId, data, submissionReview })
			// Recording review metadata must not make unchanged answers look edited after submission.
			.onConflictDoUpdate({
				target: schema.careerWorkspace.applicationId,
				set: { submissionReview, updatedAt: sql`${schema.careerWorkspace.updatedAt}` },
			});
	}

	if (row.resumeId && !row.sentResumeVersionId) {
		const [resume] = await client
			.select()
			.from(schema.resume)
			.where(
				and(eq(schema.resume.id, row.resumeId), eq(schema.resume.userId, row.userId), isNull(schema.resume.trashedAt)),
			)
			.for("update");
		if (!resume)
			throw new ORPCError("NOT_FOUND", { message: "The linked resume is unavailable. Restore it before submitting." });
		const version = await writeVersion(client, {
			resumeId: row.resumeId,
			userId: row.userId,
			data: resume.data,
			aiOperationIds: resume.aiOperationIds,
			kind: "sent",
			name: row.company,
		});
		changes.sentResumeVersionId = version.id;
		changes.sentCheckScore = lintResumeForAts(resume.data).score;
	}

	// The letter sent with it is kept the same way.
	if (row.coverLetterId && !row.sentCoverLetterVersionId) {
		const [stored] = await client
			.select()
			.from(schema.coverLetter)
			.where(
				and(
					eq(schema.coverLetter.id, row.coverLetterId),
					eq(schema.coverLetter.userId, row.userId),
					isNull(schema.coverLetter.trashedAt),
				),
			)
			.for("update");
		if (!stored)
			throw new ORPCError("NOT_FOUND", { message: "The linked letter is unavailable. Restore it before submitting." });
		const letter = coverLetterSchema.parse(stored);
		if (letter.sourceResumeId && (letter.senderLinked || letter.designLinked)) {
			const [source] = await client
				.select()
				.from(schema.resume)
				.where(
					and(
						eq(schema.resume.id, letter.sourceResumeId),
						eq(schema.resume.userId, row.userId),
						isNull(schema.resume.trashedAt),
					),
				)
				.for("update");
			if (source) {
				const linked = copyCoverLetterStyle(source.data, letter.style.sectionId, letter.style.itemId);
				letter.style = {
					...letter.style,
					...(letter.senderLinked ? { basics: linked.basics, picture: linked.picture } : {}),
					...(letter.designLinked ? { metadata: linked.metadata } : {}),
				};
			}
		}
		const version = await writeLetterVersion(client, { letter, userId: row.userId, kind: "sent", name: row.company });
		changes.sentCoverLetterVersionId = version.id;
	}

	if (Object.keys(changes).length === 0) return row;
	const [updated] = await client
		.update(schema.application)
		.set(changes)
		.where(eq(schema.application.id, row.id))
		.returning();
	return updated ?? row;
}

async function assertOwnedResumes(userId: string, resumeIds: (string | null | undefined)[]) {
	const uniqueResumeIds = [...new Set(resumeIds.filter((id): id is string => !!id))];
	await Promise.all(uniqueResumeIds.map((resumeId) => assertOwnedResume(userId, resumeId)));
}

function storageKeyFromApplicationUrl(userId: string, value: string | null | undefined) {
	if (!value) return null;

	let pathname: string;
	try {
		pathname = value.startsWith("/") ? value : new URL(value).pathname;
	} catch {
		return null;
	}

	// Only the single file uploadFile writes (pictures/<uuid>.pdf, older ones <timestamp>.pdf): an edited URL
	// must never point delete at a folder or another kind of file.
	const key = pathname.match(/^\/(?:api\/)?(uploads\/[^/]+\/pictures\/[\w-]+\.pdf)$/)?.[1];
	return key?.startsWith(`uploads/${userId}/`) ? key : null;
}

async function deleteApplicationAttachments(
	userId: string,
	applications: Pick<EditableFields, "resumeFileUrl" | "coverLetterUrl">[],
) {
	const candidateKeys = [
		...new Set(
			applications.flatMap((application) => [
				storageKeyFromApplicationUrl(userId, application.resumeFileUrl),
				storageKeyFromApplicationUrl(userId, application.coverLetterUrl),
			]),
		),
	].filter((key): key is string => !!key);

	if (candidateKeys.length === 0) return;

	const remainingApplications = await db
		.select({
			resumeFileUrl: schema.application.resumeFileUrl,
			coverLetterUrl: schema.application.coverLetterUrl,
		})
		.from(schema.application)
		.where(eq(schema.application.userId, userId));

	const referencedKeys = new Set(
		remainingApplications.flatMap((application) => [
			storageKeyFromApplicationUrl(userId, application.resumeFileUrl),
			storageKeyFromApplicationUrl(userId, application.coverLetterUrl),
		]),
	);
	const keys = candidateKeys.filter((key) => !referencedKeys.has(key));

	if (keys.length === 0) return;
	const storageService = getStorageService();
	await Promise.allSettled(keys.map((key) => storageService.delete(key)));
}

function applicationThreads(reader: DbOrTx, userId: string, applicationIds: string[]) {
	if (!applicationIds.length) return Promise.resolve([]);
	return reader
		.select({ id: schema.agentThread.id, runId: schema.agentThread.activeRunId })
		.from(schema.agentThread)
		.where(and(eq(schema.agentThread.userId, userId), inArray(schema.agentThread.applicationId, applicationIds)));
}

/** Captured run IDs cannot cancel a newer conversation started after a restore. */
async function stopApplicationRuns(threads: { runId: string | null }[]) {
	await Promise.allSettled(
		threads.flatMap((thread) => (thread.runId ? [requestRunCancellation(thread.runId, "APPLICATION_TRASHED")] : [])),
	);
}

/** Preserve rows, foreign keys and files; serialize with application/document writers before hiding active work. */
async function trashApplications(userId: string, ids: string[]) {
	const result = await db.transaction(async (tx) => {
		await lockUser(tx, userId);
		const rows = await tx
			.select()
			.from(schema.application)
			.where(and(activeApplications(userId), inArray(schema.application.id, ids)))
			.orderBy(schema.application.id)
			.for("update");
		const threads = await applicationThreads(
			tx,
			userId,
			rows.map((row) => row.id),
		);
		for (const row of rows) {
			// Millisecond tokens stay distinct even when restore and re-trash occur within one clock tick.
			const at = new Date(Math.max(Date.now(), row.updatedAt.getTime() + 1));
			await tx
				.update(schema.application)
				.set({ trashedAt: at, updatedAt: at })
				.where(and(eq(schema.application.id, row.id), activeApplications(userId)));
		}
		if (rows.length)
			await tx
				.update(schema.careerJob)
				.set({ status: "canceled", lease: null, leaseUntil: null })
				.where(
					and(
						eq(schema.careerJob.userId, userId),
						inArray(
							schema.careerJob.scheduleId,
							tx
								.select({ id: schema.careerSchedule.id })
								.from(schema.careerSchedule)
								.where(
									and(
										eq(schema.careerSchedule.userId, userId),
										inArray(
											schema.careerSchedule.applicationId,
											rows.map((row) => row.id),
										),
									),
								),
						),
						inArray(schema.careerJob.status, ["queued", "running"]),
					),
				);
		return { deleted: rows.length, threads };
	});
	await stopApplicationRuns(result.threads);
	return { deleted: result.deleted };
}

async function lockedTrash(tx: DbOrTx, input: { userId: string; id: string; trashedAt: Date }) {
	await lockUser(tx, input.userId);
	const [row] = await tx
		.select()
		.from(schema.application)
		.where(and(eq(schema.application.id, input.id), eq(schema.application.userId, input.userId)))
		.for("update");
	if (!row) throw new ORPCError("NOT_FOUND");
	if (!row.trashedAt || row.trashedAt.getTime() !== input.trashedAt.getTime())
		throw new ORPCError("CONFLICT", { message: "This application changed in Trash. Reload before trying again." });
	return row;
}

/**
 * A rescheduled round moves its briefings with it (24h, 2h or the morning before the new time); a removed round pauses
 * them and says so in Today. Either way, work already queued for the old time is canceled.
 */
async function followInterviewSchedules(
	tx: DbOrTx,
	userId: string,
	applicationId: string,
	interviewId: string,
	at: Date | null,
) {
	const schedules = await tx
		.select()
		.from(schema.careerSchedule)
		.where(
			and(
				eq(schema.careerSchedule.userId, userId),
				eq(schema.careerSchedule.applicationId, applicationId),
				eq(schema.careerSchedule.interviewId, interviewId),
			),
		)
		.orderBy(schema.careerSchedule.id)
		.for("update");
	if (!schedules.length) return;
	await tx
		.update(schema.careerJob)
		.set({ status: "canceled", lease: null, leaseUntil: null })
		.where(
			and(
				eq(schema.careerJob.userId, userId),
				inArray(
					schema.careerJob.scheduleId,
					schedules.map((row) => row.id),
				),
				inArray(schema.careerJob.status, ["queued", "running"]),
			),
		);
	for (const schedule of schedules)
		await tx
			.update(schema.careerSchedule)
			.set(
				at
					? {
							nextRunAt: prepareRunAt(at, (schedule.lead ?? "24h") as "24h" | "2h" | "morning", schedule.timezone),
							lastQueuedAt: null,
						}
					: { enabled: false },
			)
			.where(eq(schema.careerSchedule.id, schedule.id));
	if (at || !schedules.some((schedule) => schedule.enabled)) return;
	await tx.insert(schema.careerNotification).values({
		userId,
		applicationId,
		key: `interview-removed:${generateId()}`,
		notice: { type: "briefing-paused" },
		url: `/dashboard/career?schedule=${encodeURIComponent(schedules[0]?.id ?? "")}`,
	});
}

function documentFields(kind: ApplicationDocumentKind) {
	return kind === "resume"
		? ({
				file: "resumeFile",
				url: "resumeFileUrl",
				name: "resumeFileName",
			} as const)
		: ({
				file: "coverLetterFile",
				url: "coverLetterUrl",
				name: "coverLetterName",
			} as const);
}

type UploadedDocumentFields = Pick<
	EditableFields,
	"resumeFileUrl" | "resumeFileName" | "coverLetterUrl" | "coverLetterName"
>;

type DocumentFiles = { resumeFile?: File | undefined; coverLetterFile?: File | undefined };

// One request owns the uploads and record write; browser dismissal cannot strand an upload.
async function withDocumentFiles<T>(
	userId: string,
	files: DocumentFiles,
	save: (fields: UploadedDocumentFields) => Promise<T>,
) {
	const uploadedFields: UploadedDocumentFields = {};
	try {
		// ponytail: at most two sequential uploads; use allSettled if measured latency warrants concurrency.
		for (const kind of ["resume", "cover-letter"] as const) {
			const fields = documentFields(kind);
			const file = files[fields.file];
			if (!file) continue;
			if (file.type !== "application/pdf") {
				throw new ORPCError("BAD_REQUEST", { message: "Attach a PDF file." });
			}
			const uploaded = await uploadFile({
				userId,
				data: new Uint8Array(await file.arrayBuffer()),
				contentType: file.type,
			});
			uploadedFields[fields.url] = uploaded.url;
			uploadedFields[fields.name] = file.name;
		}
		return await save(uploadedFields);
	} catch (error) {
		// The record may already have committed before later work failed. Never delete referenced files.
		await deleteApplicationAttachments(userId, [uploadedFields]).catch(() => {});
		throw error;
	}
}

const stripUserId = <T extends { userId: string; activity?: ApplicationTimelineEntry[] }>(row: T) => {
	const { userId: _userId, ...rest } = row;
	return rest.activity ? { ...rest, activity: sortTimeline(rest.activity) } : rest;
};

// Hash the stored snapshot before display sorting; a timestamp alone can collide within one millisecond.
const applicationReplyVersion = (row: ApplicationRow) => createHash("sha256").update(JSON.stringify(row)).digest("hex");
const applicationContactsVersion = (row: Pick<ApplicationRow, "contacts">) =>
	createHash("sha256").update(JSON.stringify(row.contacts)).digest("hex");
const applicationView = (row: ApplicationRow) => ({
	...stripUserId(row),
	replyVersion: applicationReplyVersion(row),
	contactsVersion: applicationContactsVersion(row),
});

const applicationSummarySelect = {
	id: schema.application.id,
	userId: schema.application.userId,
	company: schema.application.company,
	role: schema.application.role,
	location: schema.application.location,
	salary: schema.application.salary,
	status: schema.application.status,
	closedReason: schema.application.closedReason,
	resumeId: schema.application.resumeId,
	resumeFileUrl: schema.application.resumeFileUrl,
	coverLetterUrl: schema.application.coverLetterUrl,
	coverLetterId: schema.application.coverLetterId,
	sentResumeVersionId: schema.application.sentResumeVersionId,
	sentCoverLetterVersionId: schema.application.sentCoverLetterVersionId,
	source: schema.application.source,
	tags: schema.application.tags,
	sourceUrl: schema.application.sourceUrl,
	matchScore: schema.application.matchScore,
	followUpAt: schema.application.followUpAt,
	followUpNote: schema.application.followUpNote,
	followUpDateOnly: schema.application.followUpDateOnly,
	contacts: schema.application.contacts,
	activity: sql<ApplicationTimelineEntry[]>`(select coalesce(jsonb_agg(case when entry->>'type' = 'note'
		then entry || '{"text":"Followed up"}'::jsonb else entry end), '[]'::jsonb)
		from jsonb_path_query(${schema.application.activity},
		'$[*] ? (@.type == "stage" || @.type == "interview" || (@.type == "note" && exists(@.followedUp)))') as entry)`,
	appliedAt: schema.application.appliedAt,
	createdAt: schema.application.createdAt,
	updatedAt: schema.application.updatedAt,
	laneId: schema.application.laneId,
};

export const applicationService = {
	findPosting: (input: { userId: string; sourceUrl: string }) => findTrackedPosting(db, input.userId, input.sourceUrl),
	list: async (input: {
		userId: string;
		status?: ApplicationStatus;
		laneId?: string;
		tags?: string[];
		limit?: number;
		offset?: number;
	}) => {
		const where = and(
			activeApplications(input.userId),
			input.status ? eq(schema.application.status, input.status) : undefined,
			input.laneId ? eq(schema.application.laneId, input.laneId) : undefined,
			input.tags && input.tags.length > 0 ? arrayContains(schema.application.tags, input.tags) : undefined,
		);
		// Bulk imports share an update timestamp; the ID keeps their pages stable.
		const query = db
			.select()
			.from(schema.application)
			.where(where)
			.orderBy(sql`${schema.application.updatedAt} desc nulls last`, desc(schema.application.id));
		const paged = input.limit !== undefined || input.offset !== undefined;
		const [rows, totals] = await Promise.all([
			paged ? query.limit(input.limit ?? 20).offset(input.offset ?? 0) : query,
			paged ? db.select({ total: count() }).from(schema.application).where(where) : undefined,
		]);

		return { items: rows.map(applicationView), total: totals?.[0]?.total ?? rows.length };
	},

	listSummary: async (input: {
		userId: string;
		status?: ApplicationStatus;
		laneId?: string;
		tags?: string[];
		limit?: number;
		offset?: number;
		includeInitialDetails?: number;
	}) => {
		const where = and(
			activeApplications(input.userId),
			input.status ? eq(schema.application.status, input.status) : undefined,
			input.laneId ? eq(schema.application.laneId, input.laneId) : undefined,
			input.tags && input.tags.length > 0 ? arrayContains(schema.application.tags, input.tags) : undefined,
		);
		const query = db
			.select(applicationSummarySelect)
			.from(schema.application)
			.where(where)
			.orderBy(sql`${schema.application.updatedAt} desc nulls last`, desc(schema.application.id));
		const paged = input.limit !== undefined || input.offset !== undefined;
		const [rows, totals] = await Promise.all([
			paged ? query.limit(input.limit ?? 20).offset(input.offset ?? 0) : query,
			paged ? db.select({ total: count() }).from(schema.application).where(where) : undefined,
		]);

		const items = rows.map(stripUserId);
		const initialDetailCount = input.includeInitialDetails ?? 0;
		if (initialDetailCount === 0 || items.length === 0) {
			return { items, total: totals?.[0]?.total ?? rows.length };
		}

		// Web keeps SQL order within each stage. Mobile differs only when the cutoff
		// falls inside a timestamp tie, so only that group needs an ID sort.
		const stageOrder: readonly ApplicationStatus[] = ["interview", "offer", "screening", "applied", "saved", "closed"];
		const buckets = new Map<ApplicationStatus, typeof items>();
		for (const item of items) {
			if (item.status === "closed" && input.status === undefined) continue;
			const bucket = buckets.get(item.status);
			if (bucket) bucket.push(item);
			else buckets.set(item.status, [item]);
		}
		const webCandidates = stageOrder.flatMap((status) => buckets.get(status) ?? []).slice(0, initialDetailCount);
		const candidateIds = new Set(webCandidates.map((item) => item.id));
		const boundary = webCandidates.at(-1);
		if (boundary) {
			const sameGroup = (item: (typeof items)[number]) =>
				item.status === boundary.status && item.updatedAt.getTime() === boundary.updatedAt.getTime();
			const mobileCount = webCandidates.filter(sameGroup).length;
			const tied = items.filter(sameGroup);
			for (const item of tied.toSorted((a, b) => a.id.localeCompare(b.id)).slice(0, mobileCount)) {
				candidateIds.add(item.id);
			}
		}
		if (candidateIds.size === 0) return { items, total: totals?.[0]?.total ?? rows.length };

		// Fetch candidates together so the initial response adds one bounded query, never one query per row.
		const fullRows = await db
			.select()
			.from(schema.application)
			.where(and(activeApplications(input.userId), inArray(schema.application.id, [...candidateIds])));
		const details = new Map(fullRows.map((row) => [row.id, applicationView(row)]));
		return {
			items: items.map((item) => {
				const initialDetail = details.get(item.id);
				return initialDetail ? { ...item, initialDetail } : item;
			}),
			total: totals?.[0]?.total ?? rows.length,
		};
	},

	/**
	 * Earlier applications at the same company: by default those that ended without a response (older clients word
	 * their notice that way); with `anyOutcome`, every closed one, with its outcome.
	 */
	priorNoResponse: (input: {
		userId: string;
		company: string;
		excludeId?: string | undefined;
		anyOutcome?: boolean | undefined;
	}) => {
		const at = input.anyOutcome
			? sql<Date>`coalesce((select max((entry->>'at')::timestamptz) from jsonb_array_elements(${schema.application.activity}) entry
   where entry->>'type' = 'stage' and entry->>'stage' = 'closed'), ${schema.application.updatedAt})`.mapWith(
					schema.application.updatedAt,
				)
			: sql<Date>`coalesce((select max((entry->>'at')::timestamptz) from jsonb_array_elements(${schema.application.activity}) entry
   where entry->>'type' = 'stage' and entry->>'closedReason' = 'no-response'), ${schema.application.updatedAt})`.mapWith(
					schema.application.updatedAt,
				);
		const ghosted = sql`exists
     (select 1 from jsonb_array_elements(${schema.application.activity}) entry where entry->>'type' = 'stage' and entry->>'closedReason' = 'no-response')`;
		// A reopened application that once went unanswered still counts, and still reads as "no reply".
		const outcome = input.anyOutcome
			? sql`(${schema.application.status} = 'closed' or ${ghosted})`
			: sql`((${schema.application.status} = 'closed' and ${schema.application.closedReason} = 'no-response') or ${ghosted})`;
		const closedReason = sql<ApplicationClosedReason | null>`case
     when ${schema.application.status} = 'closed' then ${schema.application.closedReason}
     when ${ghosted} then 'no-response'
     end`;
		return db
			.select({
				id: schema.application.id,
				company: schema.application.company,
				role: schema.application.role,
				at,
				closedReason,
			})
			.from(schema.application)
			.where(
				and(
					activeApplications(input.userId),
					sql`lower(regexp_replace(trim(${schema.application.company}), '\\s+', ' ', 'g')) = lower(regexp_replace(trim(${input.company}), '\\s+', ' ', 'g'))`,
					input.excludeId ? sql`${schema.application.id} <> ${input.excludeId}` : undefined,
					outcome,
				),
			)
			.orderBy(desc(at), schema.application.id)
			.limit(10);
	},

	getById: async (input: { id: string; userId: string }) => {
		return applicationView(await requireOwned(input.id, input.userId));
	},

	create: async (
		input: EditableFields &
			DocumentFiles & {
				userId: string;
				/** The new application's ID, chosen by the client so a retry can't save it twice. */
				newId?: string | undefined;
				company: string;
				role: string;
				status?: ApplicationStatus | undefined;
				laneId?: string | undefined;
				closedReason?: ApplicationClosedReason | null | undefined;
				stageEnteredAt?: string | undefined;
			},
	) => {
		const { userId, status, laneId, stageEnteredAt, closedReason, resumeFile, coverLetterFile, newId, ...fields } =
			input;
		const file = async (value: File | undefined) =>
			value && {
				name: value.name,
				size: value.size,
				type: value.type,
				sha256: createHash("sha256")
					.update(new Uint8Array(await value.arrayBuffer()))
					.digest("hex"),
			};
		const request = {
			operation: "applications.create",
			fields,
			stage: { status, laneId, closedReason, stageEnteredAt },
			files: { resume: await file(resumeFile), coverLetter: await file(coverLetterFile) },
		};
		// A retry of a create that already landed (a lost response, a second press) returns the same application.
		if (newId && (await isRetriedCreate(db, { table: "application", id: newId, userId, request }))) return newId;
		await assertOwnedResume(userId, fields.resumeId);
		await assertOwnedCoverLetter(userId, fields.coverLetterId);
		return withDocumentFiles(userId, { resumeFile, coverLetterFile }, (uploadedFields) =>
			db.transaction((tx) =>
				insertApplication(tx, {
					userId,
					id: newId,
					request,
					fields: { ...fields, ...uploadedFields },
					stage: { status, laneId, closedReason },
					at: stageEnteredAt ? atFromDateString(stageEnteredAt) : new Date(),
				}),
			),
		);
	},
	importMany: async (input: {
		userId: string;
		items: (EditableFields & {
			company: string;
			role: string;
			status?: ApplicationStatus | undefined;
			laneId?: string | undefined;
			closedReason?: ApplicationClosedReason | null | undefined;
			stageEnteredAt?: string | undefined;
			applicationDate?: string | undefined;
		})[];
	}) => {
		if (!input.items.length) return { imported: 0 };
		await assertOwnedResumes(
			input.userId,
			input.items.map((item) => item.resumeId),
		);
		const coverLetterIds = new Set(input.items.map((item) => item.coverLetterId).filter((id) => !!id));
		await Promise.all([...coverLetterIds].map((id) => assertOwnedCoverLetter(input.userId, id)));
		return db.transaction(async (tx) => {
			await lockUser(tx, input.userId);
			const pipeline = await ensureApplicationPipeline(tx, input.userId);
			const values = [];
			const urls = new Set<string>();
			for (const { status, laneId, closedReason, stageEnteredAt, applicationDate, ...fields } of input.items) {
				await assertUntrackedPosting(tx, input.userId, fields.sourceUrl);
				const url = fields.sourceUrl && canonicalOpportunityUrl(fields.sourceUrl);
				if (url && urls.has(url))
					throw new ORPCError("CONFLICT", { message: "Import contains the same posting twice." });
				if (url) urls.add(url);
				if (applicationDate && stageEnteredAt && stageEnteredAt < applicationDate)
					throw new ORPCError("BAD_REQUEST", { message: "Stage date must be on or after the application date." });
				const appliedDate = applicationDate ? atFromDateString(applicationDate) : undefined;
				const enteredAt = stageEnteredAt
					? atFromDateString(stageEnteredAt)
					: status === "applied" && appliedDate
						? appliedDate
						: new Date();
				const transition = await applicationTransitionValues(
					tx,
					input.userId,
					{ status, laneId, closedReason },
					undefined,
					enteredAt,
					pipeline,
				);
				let activity = (transition.values.activity ?? []).map((entry) => ({
					...entry,
					...(!stageEnteredAt && transition.values.status === "applied" && appliedDate ? { at: appliedDate } : {}),
					...(!stageEnteredAt && !(transition.values.status === "applied" && appliedDate) ? { dateUnknown: true } : {}),
				}));
				if (
					appliedDate &&
					(transition.values.status !== "applied" ||
						(!!stageEnteredAt && enteredAt.getTime() !== appliedDate.getTime()))
				) {
					const sent = await applicationTransitionValues(
						tx,
						input.userId,
						{ status: "applied" },
						undefined,
						appliedDate,
						pipeline,
					);
					activity = [...(sent.values.activity ?? []), ...activity.map((entry) => ({ ...entry, writeSequence: 2 }))];
				}
				values.push({
					id: generateId(),
					userId: input.userId,
					...fields,
					followUpDateOnly: fields.followUpDateOnly ?? isDateOnlyFollowUp({ followUpAt: fields.followUpAt ?? null }),
					...transition.values,
					activity,
					...(appliedDate ? { appliedAt: appliedDate } : {}),
				});
			}
			const rows = await tx.insert(schema.application).values(values).returning();
			for (const row of rows) {
				await recordSentResume(row, tx, true);
				if (row.followUpAt) await syncFollowUp(tx, row);
				if (row.coverLetterId)
					await linkLetterToApplication(tx, {
						userId: input.userId,
						letterId: row.coverLetterId,
						applicationId: row.id,
					});
			}
			return { imported: rows.length };
		});
	},

	update: async (
		input: EditableFields &
			DocumentFiles & {
				id: string;
				userId: string;
				canDeleteAttachments?: boolean;
				status?: ApplicationStatus | undefined;
				laneId?: string | undefined;
				stageEnteredAt?: string | undefined;
				closedReason?: ApplicationClosedReason | null | undefined;
			},
	) => {
		const existing = await requireOwned(input.id, input.userId);

		const {
			id,
			userId,
			status,
			laneId,
			stageEnteredAt,
			closedReason,
			resumeFile,
			coverLetterFile,
			canDeleteAttachments = false,
			...fields
		} = input;
		const assertAttachmentPermission = (current: typeof existing) => {
			if (
				!canDeleteAttachments &&
				((current.resumeFileUrl &&
					(resumeFile || (fields.resumeFileUrl !== undefined && fields.resumeFileUrl !== current.resumeFileUrl))) ||
					(current.coverLetterUrl &&
						(coverLetterFile ||
							(fields.coverLetterUrl !== undefined && fields.coverLetterUrl !== current.coverLetterUrl))))
			)
				throw new ORPCError("FORBIDDEN", {
					message: "Replacing or removing an attachment requires api:delete permission.",
				});
		};
		assertAttachmentPermission(existing);
		if (
			(existing.sentResumeVersionId && fields.resumeId !== undefined && fields.resumeId !== existing.resumeId) ||
			(existing.sentCoverLetterVersionId &&
				fields.coverLetterId !== undefined &&
				fields.coverLetterId !== existing.coverLetterId)
		) {
			throw new ORPCError("BAD_REQUEST", {
				message:
					"Recorded submitted documents cannot be replaced. Prepare a copy to keep the submitted versions intact.",
				data: { reason: SUBMITTED_DOCUMENT_LOCKED },
			});
		}
		await assertOwnedResume(userId, fields.resumeId);
		await assertOwnedCoverLetter(userId, fields.coverLetterId);

		return withDocumentFiles(userId, { resumeFile, coverLetterFile }, async (uploadedFields) => {
			const result = await db.transaction(async (tx) => {
				await lockUser(tx, userId);
				const [current] = await tx
					.select()
					.from(schema.application)
					.where(and(eq(schema.application.id, id), activeApplications(userId)))
					.for("update");
				if (!current) throw new ORPCError("NOT_FOUND");
				if (fields.followUpAt !== undefined && fields.followUpDateOnly === undefined)
					fields.followUpDateOnly =
						fields.followUpAt?.getTime() === current.followUpAt?.getTime()
							? current.followUpDateOnly
							: isDateOnlyFollowUp({ followUpAt: fields.followUpAt });
				await assertUntrackedPosting(tx, userId, fields.sourceUrl, id);
				assertAttachmentPermission(current);
				if (
					(current.sentResumeVersionId && fields.resumeId !== undefined && fields.resumeId !== current.resumeId) ||
					(current.sentCoverLetterVersionId &&
						fields.coverLetterId !== undefined &&
						fields.coverLetterId !== current.coverLetterId)
				)
					throw new ORPCError("BAD_REQUEST", {
						message:
							"Recorded submitted documents cannot be replaced. Prepare a copy to keep the submitted versions intact.",
						data: { reason: SUBMITTED_DOCUMENT_LOCKED },
					});
				const { values, transition } = await applicationTransitionValues(
					tx,
					userId,
					{ status, laneId, closedReason },
					current,
					stageEnteredAt ? atFromDateString(stageEnteredAt) : new Date(),
				);
				if (
					!transition.changed &&
					Object.values(fields).every((value) => value === undefined) &&
					Object.keys(uploadedFields).length === 0
				)
					return applicationView(current);
				const [updated] = await tx
					.update(schema.application)
					.set({
						...fields,
						...uploadedFields,
						...values,
					})
					.where(and(eq(schema.application.id, id), activeApplications(userId)))
					.returning();

				if (!updated) throw new ORPCError("NOT_FOUND");
				if (
					fields.followUpAt !== undefined ||
					fields.followUpNote !== undefined ||
					fields.followUpDateOnly !== undefined
				)
					await syncFollowUp(tx, updated);
				if (current.status === "saved" && !hasSubmitted(current) && updated.resumeId !== current.resumeId) {
					const [workspace] = await tx
						.select()
						.from(schema.careerWorkspace)
						.where(and(eq(schema.careerWorkspace.applicationId, id), eq(schema.careerWorkspace.userId, userId)));
					if (workspace)
						await tx
							.update(schema.careerWorkspace)
							.set({
								data: {
									...workspace.data,
									checklist: workspace.data.checklist.filter((index) => index !== 0 && index !== 1),
								},
								updatedAt: new Date(),
							})
							.where(eq(schema.careerWorkspace.applicationId, id));
				}
				// A sent snapshot belongs to the moment the application first enters a sent stage, or to an explicit
				// submission. Later metadata saves and stage advances must not turn a late-linked draft into "sent".
				const result =
					!hasSubmitted(current) && isSentStage(updated.status) ? await recordSentResume(updated, tx) : updated;
				// Every explicit letter-link write reconciles both sides, including an equal-ID save that may be
				// repairing a legacy null reverse or multiple aliases.
				if (fields.coverLetterId !== undefined) {
					if (fields.coverLetterId !== current.coverLetterId && current.coverLetterId)
						await linkLetterToApplication(tx, {
							userId,
							letterId: current.coverLetterId,
							applicationId: null,
							clearApplicationId: id,
						});
					if (fields.coverLetterId)
						await linkLetterToApplication(tx, {
							userId,
							letterId: fields.coverLetterId,
							applicationId: id,
						});
				}
				return applicationView(result);
			});
			if (fields.resumeFileUrl !== undefined || fields.coverLetterUrl !== undefined || resumeFile || coverLetterFile) {
				await deleteApplicationAttachments(userId, [existing]).catch(() => {});
			}
			return result;
		});
	},

	attachDocument: (input: {
		id: string;
		userId: string;
		kind: ApplicationDocumentKind;
		file: File;
		canDeleteAttachments?: boolean;
	}) => {
		const fields = documentFields(input.kind);
		return applicationService.update({
			id: input.id,
			userId: input.userId,
			[fields.file]: input.file,
			canDeleteAttachments: input.canDeleteAttachments ?? false,
		});
	},

	removeDocument: (input: { id: string; userId: string; kind: ApplicationDocumentKind }) => {
		const fields = documentFields(input.kind);
		return applicationService.update({
			id: input.id,
			userId: input.userId,
			[fields.url]: null,
			[fields.name]: null,
			canDeleteAttachments: true,
		});
	},

	// Persist AI-owned enrichment (match score + freeform metadata). Separate from the editable
	// update path so these fields are only ever written by the AI procedures.
	setAiResult: (input: {
		id: string;
		userId: string;
		matchScore?: number | null;
		aiMetadata?: AiMetadata | null;
		check?: (tx: DbOrTx) => Promise<void>;
	}) =>
		db.transaction(async (tx) => {
			await lockUser(tx, input.userId);
			await input.check?.(tx);
			const [updated] = await tx
				.update(schema.application)
				.set({
					...(input.matchScore !== undefined ? { matchScore: input.matchScore } : {}),
					...(input.aiMetadata !== undefined ? { aiMetadata: input.aiMetadata } : {}),
				})
				.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
				.returning();

			if (!updated) throw new ORPCError("NOT_FOUND");
			return applicationView(updated);
		}),

	addNote: async (input: { id: string; userId: string; text: string; date?: string | undefined }) => {
		// Append in a single statement (activity || [event]) so concurrent notes can't drop each
		// other via read-then-write; ownership is enforced by the WHERE clause.
		const event = noteEntry(input.text, input.date);
		const [updated] = await db
			.update(schema.application)
			.set({ activity: sql`${schema.application.activity} || ${JSON.stringify([event])}::jsonb` })
			.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
			.returning();

		if (!updated) throw new ORPCError("NOT_FOUND");
		return applicationView(updated);
	},

	deleteContact: (input: { id: string; userId: string; index: number; expectedVersion: string }) =>
		db.transaction(async (tx) => {
			const [existing] = await tx
				.select()
				.from(schema.application)
				.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
				.for("update");
			if (!existing) throw new ORPCError("NOT_FOUND");
			if (applicationContactsVersion(existing) !== input.expectedVersion || !existing.contacts[input.index])
				throw new ORPCError("CONFLICT", { message: "The contacts changed. Reload them before removing the contact." });
			const [updated] = await tx
				.update(schema.application)
				.set({ contacts: existing.contacts.filter((_, index) => index !== input.index) })
				.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
				.returning({ id: schema.application.id });
			if (!updated) throw new ORPCError("NOT_FOUND");
		}),

	/** Records contact within the locked visit; an explicit reply also clears the owned follow-up and its schedule. */
	logFollowUp: (input: { id: string; userId: string; reply?: { expectedVersion: string } | undefined }) =>
		db.transaction(async (tx) => {
			if (input.reply) await lockUser(tx, input.userId);
			const [existing] = await tx
				.select()
				.from(schema.application)
				.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
				.for("update");
			if (!existing) throw new ORPCError("NOT_FOUND");
			if (existing.status === "saved" || existing.status === "closed")
				throw new ORPCError("BAD_REQUEST", { message: "Follow-ups can only be logged for open, sent applications." });
			if (input.reply && applicationReplyVersion(existing) !== input.reply.expectedVersion)
				throw new ORPCError("CONFLICT", { message: "The application changed. Reload it before logging your reply." });
			const event: ApplicationTimelineEntry = {
				...noteEntry(input.reply ? "Replied" : "Followed up"),
				followedUp: {
					...(input.reply ? { kind: "reply" as const } : {}),
					stageEntryId: latestStageEntryId(
						existing.activity.filter((entry) => entry.type === "stage" && entry.canonicalTransition !== false),
					),
				},
			};
			const [updated] = await tx
				.update(schema.application)
				.set({
					activity: sql`${schema.application.activity} || ${JSON.stringify([event])}::jsonb`,
					...(input.reply ? { followUpAt: null, followUpNote: null, followUpDateOnly: false } : {}),
				})
				.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
				.returning();
			if (!updated) throw new ORPCError("NOT_FOUND");
			if (input.reply) await syncFollowUp(tx, updated);
			return applicationView(updated);
		}),

	addInterview: async (input: InterviewDetails & { id: string; userId: string; at: string }) => {
		const { id, userId, at, ...details } = input;
		const event = interviewEntry(at, details);
		// Same atomic append as addNote so a concurrent note/stage move can't be dropped.
		const [updated] = await db
			.update(schema.application)
			.set({ activity: sql`${schema.application.activity} || ${JSON.stringify([event])}::jsonb` })
			.where(and(eq(schema.application.id, id), activeApplications(userId)))
			.returning();

		if (!updated) throw new ORPCError("NOT_FOUND");
		return applicationView(updated);
	},

	updateInterview: (
		input: Partial<{ [K in keyof InterviewDetails]: InterviewDetails[K] | undefined }> & {
			id: string;
			userId: string;
			entryId: string;
			at?: string | undefined;
		},
	) => {
		const { id, userId, entryId, at, ...details } = input;
		const patch = Object.fromEntries(Object.entries(details).filter(([, value]) => value !== undefined));

		return db.transaction(async (tx) => {
			const [existing] = await tx
				.select()
				.from(schema.application)
				.where(and(eq(schema.application.id, id), activeApplications(userId)))
				.for("update");
			if (!existing) throw new ORPCError("NOT_FOUND");

			const target = existing.activity.find((entry) => entry.id === entryId);
			if (!target) throw new ORPCError("NOT_FOUND");
			if (target.type !== "interview") {
				throw new ORPCError("BAD_REQUEST", { message: "Timeline entry is not an interview." });
			}

			const activity = existing.activity.map((entry) =>
				entry.id === entryId ? { ...entry, ...patch, ...(at !== undefined ? { at: interviewAt(at) } : {}) } : entry,
			);

			const [updated] = await tx
				.update(schema.application)
				.set({ activity })
				.where(and(eq(schema.application.id, id), activeApplications(userId)))
				.returning();

			if (!updated) throw new ORPCError("NOT_FOUND");
			// Only a new time moves briefings; re-saving the same time would cancel a briefing that's running.
			const moved = at !== undefined && interviewAt(at).getTime() !== new Date(target.at).getTime();
			if (moved) await followInterviewSchedules(tx, userId, id, entryId, interviewAt(at));
			return applicationView(updated);
		});
	},

	updateTimelineEntry: (input: {
		id: string;
		userId: string;
		entryId: string;
		date?: string | undefined;
		text?: string | undefined;
	}) => {
		return db.transaction(async (tx) => {
			const [existing] = await tx
				.select()
				.from(schema.application)
				.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
				.for("update");
			if (!existing) throw new ORPCError("NOT_FOUND");

			const anchor = currentStageAnchor(existing.activity, existing.status);
			const activity = existing.activity.map((entry) => {
				if (entry.id !== input.entryId) return entry;

				// Interviews hold an exact time; a day-granular date edit here could move them to the wrong local day.
				if (entry.type === "interview") {
					throw new ORPCError("BAD_REQUEST", {
						message: "Interview entries must be edited with updateInterview (update_application_interview).",
					});
				}

				if (entry.type !== "note" && input.text !== undefined) {
					throw new ORPCError("BAD_REQUEST", { message: "Only note timeline entries have editable text." });
				}

				return {
					...entry,
					...(input.date !== undefined
						? { at: atFromDateString(input.date, entry.at), ...(entry.type === "stage" ? { dateUnknown: false } : {}) }
						: {}),
					...(entry.type === "note" && input.text !== undefined ? { text: input.text } : {}),
				};
			});

			if (!activity.some((entry) => entry.id === input.entryId)) throw new ORPCError("NOT_FOUND");
			if (input.date !== undefined && activity.some((entry) => entry.id === input.entryId && entry.type === "stage"))
				assertCurrentStageAnchorLatest(activity, existing.status, anchor?.id);

			const [updated] = await tx
				.update(schema.application)
				.set({
					activity,
					appliedAt: existing.activity.some(
						(entry) => entry.id === input.entryId && (entry.type !== "stage" || entry.canonicalTransition === false),
					)
						? existing.appliedAt
						: appliedAtFromTimeline(activity, existing.appliedAt),
				})
				.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
				.returning();

			if (!updated) throw new ORPCError("NOT_FOUND");
			return applicationView(updated);
		});
	},

	deleteTimelineEntry: (input: { id: string; userId: string; entryId: string }) => {
		return db.transaction(async (tx) => {
			const [existing] = await tx
				.select()
				.from(schema.application)
				.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
				.for("update");
			if (!existing) throw new ORPCError("NOT_FOUND");

			const entry = existing.activity.find((item) => item.id === input.entryId);
			if (!entry) throw new ORPCError("NOT_FOUND");

			const anchor = currentStageAnchor(existing.activity, existing.status);
			if (entry.type === "stage" && anchor?.id === entry.id) {
				throw new ORPCError("BAD_REQUEST", { message: "The current stage can't be deleted. Change the stage first." });
			}

			const activity = existing.activity.filter((item) => item.id !== input.entryId);
			if (entry.type === "stage") assertCurrentStageAnchorLatest(activity, existing.status, anchor?.id);

			const [updated] = await tx
				.update(schema.application)
				.set({
					activity,
					appliedAt: existing.activity.some(
						(entry) => entry.id === input.entryId && (entry.type !== "stage" || entry.canonicalTransition === false),
					)
						? existing.appliedAt
						: appliedAtFromTimeline(activity, existing.appliedAt),
				})
				.where(and(eq(schema.application.id, input.id), activeApplications(input.userId)))
				.returning();

			if (!updated) throw new ORPCError("NOT_FOUND");
			if (entry.type === "interview") await followInterviewSchedules(tx, input.userId, input.id, entry.id, null);
			return applicationView(updated);
		});
	},

	delete: async (input: { id: string; userId: string }) => {
		const result = await trashApplications(input.userId, [input.id]);
		if (!result.deleted) throw new ORPCError("NOT_FOUND");
	},

	listTrash: async (input: { userId: string; limit: number; offset: number }) => {
		const scope = and(eq(schema.application.userId, input.userId), isNotNull(schema.application.trashedAt));
		const [items, [total]] = await Promise.all([
			db
				.select({
					id: schema.application.id,
					company: schema.application.company,
					role: schema.application.role,
					trashedAt: schema.application.trashedAt,
				})
				.from(schema.application)
				.where(scope)
				.orderBy(desc(schema.application.trashedAt), schema.application.id)
				.limit(input.limit)
				.offset(input.offset),
			db.select({ count: count() }).from(schema.application).where(scope),
		]);
		return {
			items: items.flatMap((row) => (row.trashedAt ? [{ ...row, trashedAt: row.trashedAt }] : [])),
			total: total?.count ?? 0,
		};
	},

	restore: (input: { userId: string; id: string; trashedAt: Date }) =>
		db.transaction(async (tx) => {
			const row = await lockedTrash(tx, input);
			await assertUntrackedPosting(tx, input.userId, row.sourceUrl, row.id);
			await tx
				.update(schema.application)
				.set({ trashedAt: null, updatedAt: new Date(Math.max(Date.now(), row.updatedAt.getTime() + 1)) })
				.where(
					and(
						eq(schema.application.id, input.id),
						eq(schema.application.userId, input.userId),
						eq(schema.application.trashedAt, input.trashedAt),
					),
				);
		}),

	purge: async (input: { userId: string; id: string; trashedAt: Date }) => {
		const { row, threads } = await db.transaction(async (tx) => {
			const row = await lockedTrash(tx, input);
			const threads = await applicationThreads(tx, input.userId, [input.id]);
			await tx
				.delete(schema.application)
				.where(
					and(
						eq(schema.application.id, input.id),
						eq(schema.application.userId, input.userId),
						eq(schema.application.trashedAt, input.trashedAt),
					),
				);
			return { row, threads };
		});
		await stopApplicationRuns(threads);
		await Promise.allSettled(
			threads.map((thread) => getStorageService().delete(`uploads/${input.userId}/agent/${thread.id}`)),
		);
		await deleteApplicationAttachments(input.userId, [row]);
	},

	bulkUpdate: (input: {
		userId: string;
		ids: string[];
		status?: ApplicationStatus | undefined;
		laneId?: string | undefined;
		closedReason?: ApplicationClosedReason | null | undefined;
		addTags?: string[] | undefined;
		expectedStages?:
			| {
					id: string;
					status: ApplicationStatus;
					laneId?: string | null | undefined;
					closedReason: ApplicationClosedReason | null;
					stageEntryId?: string | null | undefined;
			  }[]
			| undefined;
	}) =>
		db.transaction(async (tx) => {
			await lockUser(tx, input.userId);
			const pipeline = await ensureApplicationPipeline(tx, input.userId);
			const scope = and(inArray(schema.application.id, input.ids), activeApplications(input.userId));
			const rows = await tx.select().from(schema.application).where(scope).for("update");
			const expected = new Map(input.expectedStages?.map((stage) => [stage.id, stage]));
			// Each moved row's stage as saved, so the client's next move of it can start from there.
			const stages: ReturnType<typeof savedStageOf>[] = [];
			const saved = (row: ApplicationRow) => stages.push(savedStageOf(row));
			let updated = 0;
			for (const row of rows) {
				// Rows are locked, so this check and the write below see the same stage: a move made since wins, even one
				// that has come back to the same stage. Every move, lane-only ones too, adds a stage change, so a client that
				// sends one is checked on it rather than on the lane, which also changes without a move (a retired lane's
				// rows going to its replacement, a legacy row getting its default lane).
				const stage = expected.get(row.id);
				if (
					stage &&
					(stage.status !== row.status ||
						(stage.closedReason ?? null) !== (row.closedReason ?? null) ||
						(stage.stageEntryId === undefined
							? stage.laneId !== undefined && (stage.laneId ?? null) !== (row.laneId ?? null)
							: stage.stageEntryId !== latestStageEntryId(row.activity)))
				)
					continue;
				updated += 1;
				const { values, transition } = await applicationTransitionValues(
					tx,
					input.userId,
					input,
					row,
					new Date(),
					pipeline,
				);
				const tags = [...new Set([...row.tags, ...(input.addTags ?? [])])];
				if (!transition.changed && tags.length === row.tags.length) {
					saved(row);
					continue;
				}
				const [written] = await tx
					.update(schema.application)
					.set({ ...values, tags })
					.where(and(eq(schema.application.id, row.id), activeApplications(input.userId)))
					.returning();
				saved(written ?? row);
				if (written && transition.canonicalTransition && !hasSubmitted(row)) await recordSentResume(written, tx);
			}
			return { updated, stages };
		}),

	bulkDelete: (input: { userId: string; ids: string[] }) => {
		return trashApplications(input.userId, input.ids);
	},

	// Raw counts for Insights; funnel/sankey/tiles are derived client-side from these.
	stats: async (input: { userId: string }) => {
		const scope = activeApplications(input.userId);

		const byStage = await db
			.select({ status: schema.application.status, count: sql<number>`count(*)::int` })
			.from(schema.application)
			.where(scope)
			.groupBy(schema.application.status);

		const bySource = await db
			.select({ source: schema.application.source, count: sql<number>`count(*)::int` })
			.from(schema.application)
			.where(scope)
			.groupBy(schema.application.source);

		const total = byStage.reduce((sum, row) => sum + row.count, 0);

		return {
			total,
			byStage,
			bySource: bySource
				.filter((row): row is { source: string; count: number } => !!row.source)
				.sort((a, b) => b.count - a.count),
		};
	},

	listTags: async (input: { userId: string }) => {
		const rows = await db
			.select({ tag: sql<string>`distinct unnest(${schema.application.tags})` })
			.from(schema.application)
			.where(activeApplications(input.userId));

		return rows.map((row) => row.tag).sort((a, b) => a.localeCompare(b));
	},
};