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!
TypeScript agent-skillsaidsh-pluginhacktoberfestjavascriptmcp-serverreactresume-builderself-hostedtypescript
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));
},
};