Open-source AI coworkers that each get a computer of their own: a browser, files and tools, with every action decided before it happens and recorded after. Bring any AG-UI agent.
/**
* A person's standing instructions: keeping them, and guarding them.
*
* THIS FILE HAS TWO HALVES, AND THEY ARE SEPARABLE ON PURPOSE. The half below is the one a person
* touches: create, list, change, remove, switch off. It is asked questions through a Bot, so every
* refusal is a sentence a model can act on, every statement is guarded by the owner, and the owner is
* never taken from an argument a model supplied. Its failures are one person's failures — a bad cron,
* a channel they are not in — and none of them are about two things happening at once.
*
* The other half is the sweep's, and it starts at the marked boundary further down: the ledger read
* on a clock (which routines are due, advancing the next run, opening and closing a run row).
* Nothing there is asked a question by a person, and everything there is about concurrency — several
* replicas reading the same due row in the same second. That is why the halves are worth telling
* apart: only the second one has anything to do with concurrency, and only the second one needs to be
* reasoned about as a race.
*
* NO CLAIM OR LEASE MACHINERY, EVER. Firing mechanics belong to the shared `work_items` queue in
* `server/src/work/queue.ts`, which already owns `for update skip locked`, leases on the database's
* clock, and an attempt count. A second lease grown on the routines table — a `claimed_by`, a
* `locked_until` — is exactly the duplicated firing mechanism #235 exists to prevent: two half-right
* implementations of the same hard thing, one of which nobody tests.
*/
import type { HeadlessWaiting } from "@openbot/toolkit/headless-tools";
import {
and,
asc,
desc,
eq,
gte,
inArray,
isNotNull,
isNull,
lte,
ne,
sql,
} from "drizzle-orm";
import type { Database } from "../db/client";
import {
channelAgents,
channelMemberships,
channels,
routineRuns,
routineSweeps,
routines,
} from "../db/schema";
import { describeCron, nextOccurrence, ScheduleRefusedError } from "./schedule";
const SWEEP_ID = "routines";
export class RoutineNotFoundError extends Error {
constructor(message = "That routine does not exist.") {
super(message);
this.name = "RoutineNotFoundError";
}
}
/** The floor, the cap, a bad zone, a channel that is not the caller's. Carries the sentence verbatim. */
export class RoutineRefusedError extends Error {
constructor(message: string) {
super(message);
this.name = "RoutineRefusedError";
}
}
/** A person may keep this many routines switched on. A constant with a reason, not a setting. */
export const MAX_ENABLED_ROUTINES = 20;
/** Same code-point cap discipline as channel activity. */
export const MAX_INSTRUCTION_CODE_POINTS = 2000;
/** Capped like audit payloads, because a failure is not a promise about length. */
export const MAX_RUN_ERROR = 400;
/**
* How far back the failure count looks.
*
* Twice what the fatigue rule can act on, so the answer is never truncated where it matters, and
* bounded because an unbounded read of a routine's whole history is the wrong shape for something
* called on every failed firing, for ever: a routine that has failed nightly for a year would make
* the count get slower exactly as the routine got worse.
*/
const FAILURE_SCAN_LIMIT = 20;
const NO_SHARED_CHANNEL =
"I can only post into a channel you and I are both in.";
const NO_CHANNEL_AT_ALL =
"We have no channel for me to post into. Start one and ask again.";
const INSTRUCTION_EMPTY = "A routine needs an instruction to carry out.";
const INSTRUCTION_TOO_LONG = `An instruction can be at most ${MAX_INSTRUCTION_CODE_POINTS} characters.`;
const TOO_MANY_ENABLED = `You already have ${MAX_ENABLED_ROUTINES} routines switched on. Switch one off before adding another.`;
/** How many names an ambiguity refusal reads out before it gives up and says "and others". */
const MAX_NAMED_CHANNELS = 5;
export type RoutineRunOutcome = "succeeded" | "failed" | "skipped" | "waiting";
export type Routine = {
id: string;
ownerUserId: string;
agentId: string;
channelId: string;
instruction: string;
cron: string;
timezone: string;
enabled: boolean;
nextRunAt: Date;
lastRunAt: Date | null;
createdAt: Date;
};
/** One row of the routines page: everything it draws, and nothing it would have to parse. */
export type RoutineSummary = {
id: string;
agentId: string;
instruction: string;
/**
* The schedule in words — "Weekdays at 09:00" — for the shapes `describeCron` recognizes, and
* the raw five-field expression for everything else. Prose where prose is possible; never a
* value the client is expected to parse.
*
* Rendering cron exhaustively in English needs a real cron-description library, which this does
* not carry. So a consumer must treat this as opaque display text: show it, never parse it, and
* never compute a time from it. The authoritative next firing is `nextRunAt`, which the store
* and the sweep both derive from the expression itself.
*/
schedule: string;
timezone: string;
enabled: boolean;
nextRunAt: Date;
channelId: string;
/** The target channel's name, or null when the row is gone entirely rather than soft-deleted. */
channelName: string | null;
/** Whether the target channel was deleted. A broken routine is shown, not hidden. */
channelDeleted: boolean;
/** The most recent firing, or null when it has never fired. */
lastRun: { status: RoutineRunOutcome | null; finishedAt: Date | null } | null;
};
export type RoutineInput = {
ownerUserId: string;
agentId: string;
channelId?: string;
instruction: string;
cron: string;
timezone?: string;
};
/**
* One firing, and everything running it headlessly needs — the runner's read, and nobody else's.
*
* The owner comes back because the runner acts AS the owner: the channel it posts into, the thread
* it continues and the grants the turn resolves are all that person's, and a runner that had to be
* told whose they were would be a runner that could be told the wrong answer.
*/
export type RoutineRunContext = {
routineId: string;
ownerUserId: string;
agentId: string;
channelId: string;
instruction: string;
/**
* Read at the moment of running, so a routine switched off after its firing was dispatched (or a
* Run now that raced a pause) is closed as skipped instead of run. Paused never runs.
*/
enabled?: boolean;
};
/** One row of a routine's run history. `status: null` is a run still in flight. */
export type RoutineRunSummary = {
id: string;
status: RoutineRunOutcome | null;
startedAt: Date;
finishedAt: Date | null;
error: string | null;
/** What started it: the schedule, a person's Run now, or an event trigger. */
source: "schedule" | "run_now" | "trigger";
};
export type RoutinePatch = Partial<{
instruction: string;
cron: string;
timezone: string;
channelId: string;
enabled: boolean;
}>;
export type RoutineStore = {
create(input: RoutineInput): Promise<Routine>;
listFor(ownerUserId: string): Promise<RoutineSummary[]>;
update(
ownerUserId: string,
id: string,
patch: RoutinePatch,
): Promise<Routine>;
remove(ownerUserId: string, id: string): Promise<void>;
setEnabled(ownerUserId: string, id: string, enabled: boolean): Promise<void>;
/**
* Run now (the page's Test): opens a run row for the owner's own enabled routine. Refused for a
* paused routine and while another run of it is still open, so a double click is one run.
*/
startManualRun(ownerUserId: string, id: string): Promise<{ runId: string }>;
/** The owner's run history for one routine, newest first. */
listRuns(
ownerUserId: string,
id: string,
limit?: number,
): Promise<RoutineRunSummary[]>;
/* The sweep's half. Deliberately not owner-scoped — see the boundary comment below. */
/** Enabled routines whose next run has arrived, oldest due first. */
dueRoutines(limit: number): Promise<{ id: string; nextRunAt: Date }[]>;
/**
* Compare-and-set the clock forward. False means another sweep got there first.
*
* The CAS always compares against `from`, the stamp the caller read. `computeFrom` is for the
* stale-backlog case: a routine whose stamp is a month behind used to be stepped one occurrence
* per pass — a fifteen-minute routine idle a month stayed silent another fortnight while its
* clock caught up — so the sweep passes `now` here and one advance makes the clock current.
*/
advanceNextRun(id: string, from: Date, computeFrom?: Date): Promise<boolean>;
/** Open a run row. Its status stays null until something finishes it. */
insertRun(routineId: string): Promise<{ runId: string }>;
/**
* The runner's read: an opened run row, joined to the routine it fires.
*
* Not owner-scoped, like everything else in this half — a run id is not something a person names,
* it is something the queue hands back — and deliberately out of RoutineTools' reach: nothing a
* model can call resolves a run id, so nothing a model can call gets an owner out of one. Null
* means the routine was deleted between queueing and running, which takes its runs with it.
*/
runContext(runId: string): Promise<RoutineRunContext | null>;
/**
* The sweep's read, like `dueRoutines` — not owner-scoped. A routine id here comes from a work
* item's own payload, not from a person, so there is no owner to check it against. Answers exactly
* what the consumer's re-read needs before firing: has the routine been deleted or switched off
* since the offer. Null means deleted; otherwise `enabled` says the rest.
*/
routineForFiring(
id: string,
): Promise<{ id: string; enabled: boolean } | null>;
/** Close a run row with its outcome, and the capped error when there was one. */
finishRun(
runId: string,
status: RoutineRunOutcome,
error?: string,
waiting?: HeadlessWaiting,
): Promise<void>;
/**
* Close every run row that has sat open (`status is null`) longer than `olderThanMs` as
* "skipped", with the same reason on all of them. Returns how many rows it closed.
*
* The sweep's reaper, and deliberately NOT scoped to one routine or one firing: it exists for the
* rows nothing else can reach — a server that died mid-turn after the queue item was already
* finished on the 202, or a dispatch-failure close that itself failed. The age bound is what keeps
* it away from live work: a run younger than the cutoff may be a turn some server is still
* running, and closing that row would make the real `finishRun` a silent no-op. "skipped" rather
* than "failed" so an infrastructure death is not counted by the fatigue rule as the routine's own
* failure.
*/
reapAbandonedRuns(olderThanMs: number, error: string): Promise<number>;
recordSweep(owner: string): Promise<void>;
lastSweptAt(): Promise<Date | null>;
/**
* Switch a routine off because its own schedule refuses to advance, and record why.
*
* The sweep's off switch, not a person's, so it is not owner-scoped — like everything else in this
* half, the id comes from the sweep's own read, not from a caller. A routine whose stored cron the
* schedule module refuses (a row written before a validation existed, a hand-edited value) throws
* out of `advanceNextRun` on every pass for ever: its clock never moves, it burns one of the
* pass's due slots each time, and the owner sees a routine that silently stopped. Disabling it
* ends that, and the finished "skipped" run row this writes is the announcement — the sweep has no
* channel to speak in, and the run history is what `listFor` surfaces to the owner.
*/
markUnschedulable(id: string, reason: string): Promise<void>;
/** How many failures the routine has at the tail, for the fatigue rule to read. */
consecutiveFailures(routineId: string): Promise<number>;
};
type RoutineRow = typeof routines.$inferSelect;
/** What drizzle hands the callback of `database.transaction`. */
type Transaction = Parameters<Parameters<Database["transaction"]>[0]>[0];
function toRoutine(row: RoutineRow): Routine {
return {
id: row.id,
ownerUserId: row.ownerUserId,
agentId: row.agentId,
channelId: row.channelId,
instruction: row.instruction,
cron: row.cron,
timezone: row.timezone,
enabled: row.enabled,
nextRunAt: row.nextRunAt,
lastRunAt: row.lastRunAt,
createdAt: row.createdAt,
};
}
/** Trim, then measure in code points like channel activity does — not in UTF-16 units. */
function validInstruction(instruction: string): string {
const trimmed = instruction.trim();
if (trimmed.length === 0) throw new RoutineRefusedError(INSTRUCTION_EMPTY);
if (Array.from(trimmed).length > MAX_INSTRUCTION_CODE_POINTS) {
throw new RoutineRefusedError(INSTRUCTION_TOO_LONG);
}
return trimmed;
}
/**
* The schedule module owns both acceptance and the next occurrence, so its refusal sentence is the
* one a person should read. It is carried through verbatim rather than reworded here: a model that
* gets "Routines may run at most every 15 minutes" can propose a schedule that works, and a model
* that gets "invalid cron" cannot.
*/
function nextRunFor(cron: string, timezone: string, after: Date): Date {
try {
return nextOccurrence(cron, timezone, after);
} catch (error) {
if (error instanceof ScheduleRefusedError) {
throw new RoutineRefusedError(error.message);
}
throw error;
}
}
/**
* "A", "A, B", or five names and "and others" — a sentence, not a list a client renders.
*
* A name alone is not always enough to ask by: a real account turned up six channels all named
* "General Assistant" with the same Bot, and a refusal built from names alone read "General
* Assistant, General Assistant, … and others" — circular, because the person cannot answer it and
* the model cannot map an answer back to a channelId. So every candidate whose name is shared by
* another candidate gets its full id appended in parentheses; the id is the one thing the model can
* pass back as `channelId` when names cannot tell two channels apart, and a person pasting it back
* is ugly but functional. A candidate with a unique name stays bare, so the common case — and the
* sentence's length — is unaffected by a collision elsewhere in the list.
*/
function nameThem(candidates: { id: string; name: string }[]): string {
const counts = new Map<string, number>();
for (const candidate of candidates) {
counts.set(candidate.name, (counts.get(candidate.name) ?? 0) + 1);
}
const labels = candidates.map((candidate) =>
(counts.get(candidate.name) ?? 0) > 1
? `${candidate.name} (${candidate.id})`
: candidate.name,
);
if (labels.length <= MAX_NAMED_CHANNELS) return labels.join(", ");
return [...labels.slice(0, MAX_NAMED_CHANNELS), "and others"].join(", ");
}
export function createRoutineStore(database: Database): RoutineStore {
/**
* Where the reply lands, decided here rather than at the tool boundary.
*
* Discovery confirmed the conversation's own channel is not reachable at the tool layer, so a model
* asked to make a routine either names a channel or names nothing. Both are resolved against what
* the owner and this agent actually share, which is the only check that stops a routine posting
* one person's summary into another person's conversation.
*/
async function resolveChannel(
ownerUserId: string,
agentId: string,
channelId?: string,
): Promise<string> {
if (channelId !== undefined) {
// One query for all four conditions: the channel exists, it is not deleted, the owner is a
// member, and this agent is in it. Any miss is the same refusal, because telling them apart
// would tell a caller which channel ids exist.
const rows = await database
.select({ id: channels.id })
.from(channels)
.innerJoin(
channelMemberships,
and(
eq(channelMemberships.channelId, channels.id),
eq(channelMemberships.userId, ownerUserId),
),
)
.innerJoin(
channelAgents,
and(
eq(channelAgents.channelId, channels.id),
eq(channelAgents.agentId, agentId),
),
)
.where(and(eq(channels.id, channelId), isNull(channels.deletedAt)))
.limit(1);
const found = rows[0];
if (!found) throw new RoutineRefusedError(NO_SHARED_CHANNEL);
return found.id;
}
// Six rows is enough to answer the question: one resolves, more than one refuses, and the
// sentence names at most five before it says "and others".
const candidates = await database
.select({ id: channels.id, name: channels.name })
.from(channels)
.innerJoin(
channelMemberships,
and(
eq(channelMemberships.channelId, channels.id),
eq(channelMemberships.userId, ownerUserId),
),
)
.innerJoin(
channelAgents,
and(
eq(channelAgents.channelId, channels.id),
eq(channelAgents.agentId, agentId),
),
)
.where(isNull(channels.deletedAt))
.orderBy(desc(channels.createdAt), desc(channels.id))
.limit(MAX_NAMED_CHANNELS + 1);
const only = candidates[0];
if (!only) throw new RoutineRefusedError(NO_CHANNEL_AT_ALL);
if (candidates.length > 1) {
// Named, because this refusal goes back to the model: a sentence listing the channels is a
// question it can put to the person, and "be more specific" is not.
throw new RoutineRefusedError(
`You are in more than one channel with me — ${nameThem(
candidates,
)}. Say which one.`,
);
}
return only.id;
}
/**
* How many of this person's routines are switched on. The cap counts these, not rows.
*
* Takes the transaction it must count on: a count made on another pooled connection would not see
* the uncommitted row a racing create is about to add, which is the exact blindness the lock below
* exists to remove.
*/
async function countEnabled(
handle: Transaction,
ownerUserId: string,
): Promise<number> {
const [row] = await handle
.select({ total: sql<number>`count(*)::int` })
.from(routines)
.where(
and(eq(routines.ownerUserId, ownerUserId), eq(routines.enabled, true)),
);
return row?.total ?? 0;
}
/**
* Serialize this owner's cap-guarded writes: count and write under one advisory lock.
*
* The cap used to be a bare count-then-write, and two concurrent creates that both counted 19 both
* inserted — the person held 21. An advisory lock rather than a row lock because the thing being
* guarded is a COUNT: there is no one row whose `for update` covers "how many are enabled".
* Transaction-scoped (`_xact_`), so the commit or the rollback releases it and never us forgetting.
* `hashtext` collisions are harmless — two owners sharing a hash take turns, slower and not wrong.
*/
async function withEnabledCapLock<T>(
ownerUserId: string,
work: (transaction: Transaction) => Promise<T>,
): Promise<T> {
return await database.transaction(async (transaction) => {
await transaction.execute(
sql`select pg_advisory_xact_lock(hashtext(${`routine-cap-${ownerUserId}`}))`,
);
return await work(transaction);
});
}
async function loadOwned(
ownerUserId: string,
id: string,
): Promise<RoutineRow> {
const [row] = await database
.select()
.from(routines)
.where(and(eq(routines.id, id), eq(routines.ownerUserId, ownerUserId)))
.limit(1);
// A routine that is not yours is a routine that does not exist: the `setPinned` rule.
if (!row) throw new RoutineNotFoundError();
return row;
}
async function update(
ownerUserId: string,
id: string,
patch: RoutinePatch,
): Promise<Routine> {
const existing = await loadOwned(ownerUserId, id);
const values: Partial<typeof routines.$inferInsert> = {
updatedAt: new Date(),
};
if (patch.instruction !== undefined) {
values.instruction = validInstruction(patch.instruction);
}
if (patch.channelId !== undefined) {
values.channelId = await resolveChannel(
ownerUserId,
existing.agentId,
patch.channelId,
);
}
if (patch.enabled !== undefined) values.enabled = patch.enabled;
const enabling = patch.enabled === true && !existing.enabled;
const cron = patch.cron ?? existing.cron;
const timezone = patch.timezone ?? existing.timezone;
if (patch.cron !== undefined) values.cron = patch.cron;
if (patch.timezone !== undefined) values.timezone = timezone;
/*
* Recomputed for a new cron, a new zone, and for switching back on. That last one is the subtle
* case: a routine switched off in June still holds June's `next_run_at`, and enabling it without
* recomputing hands the sweep a firing that was due months ago.
*/
if (patch.cron !== undefined || patch.timezone !== undefined || enabling) {
values.nextRunAt = nextRunFor(cron, timezone, new Date());
}
/*
* The cap is re-counted inside the lock, in the same transaction as the write. Counting outside
* and writing inside would keep the very race the lock removes: two enables that both counted 19
* before either committed.
*/
const [row] = await withEnabledCapLock(ownerUserId, async (transaction) => {
if (
enabling &&
(await countEnabled(transaction, ownerUserId)) >= MAX_ENABLED_ROUTINES
) {
throw new RoutineRefusedError(TOO_MANY_ENABLED);
}
return await transaction
.update(routines)
.set({
...values,
// Switching back on starts a new streak; see `consecutiveFailures`. The database's clock,
// like the runs it is compared with, so no skew between hosts moves a run across the line.
...(enabling ? { enabledAt: sql`now()` } : {}),
})
.where(and(eq(routines.id, id), eq(routines.ownerUserId, ownerUserId)))
.returning();
});
if (!row) throw new RoutineNotFoundError();
return toRoutine(row);
}
return {
update,
async create(input) {
const instruction = validInstruction(input.instruction);
const timezone = input.timezone ?? "UTC";
const channelId = await resolveChannel(
input.ownerUserId,
input.agentId,
input.channelId,
);
const nextRunAt = nextRunFor(input.cron, timezone, new Date());
// Counted and inserted under the owner's cap lock, so two creates racing at 19 cannot both
// count 19 and hand the person 21: the second waits, counts 20, and gets the refusal.
const [row] = await withEnabledCapLock(
input.ownerUserId,
async (transaction) => {
if (
(await countEnabled(transaction, input.ownerUserId)) >=
MAX_ENABLED_ROUTINES
) {
throw new RoutineRefusedError(TOO_MANY_ENABLED);
}
return await transaction
.insert(routines)
.values({
id: `routine_${crypto.randomUUID()}`,
ownerUserId: input.ownerUserId,
agentId: input.agentId,
channelId,
instruction,
cron: input.cron,
timezone,
nextRunAt,
})
.returning();
},
);
// An insert that returned nothing is not a missing routine, it is a broken database: loud
// rather than folded into the not-found sentence a caller is meant to be able to trust.
if (!row) throw new Error("inserting a routine returned no row");
return toRoutine(row);
},
async listFor(ownerUserId) {
/*
* The last-run join reads `routine_runs`, which only the sweep's half of this file writes to:
* the page stays empty here until a routine has actually fired.
*
* `distinct on (routine_id) ... order by started_at desc` gives one row per routine, the most
* recent, in a single index scan of `routine_runs_by_routine_idx`. Reading the runs in a
* separate statement rather than a lateral keeps the main query one flat join.
*/
const rows = await database
.select({
routine: routines,
channelName: channels.name,
channelDeletedAt: channels.deletedAt,
// A left join, and the id is selected to tell "no channel row at all" (a hard delete
// somewhere else) from "soft-deleted": both mean the target is unusable, and neither is
// allowed to hide the routine, which is why `channel_id` is not a foreign key.
channelExists: channels.id,
})
.from(routines)
.leftJoin(channels, eq(channels.id, routines.channelId))
.where(eq(routines.ownerUserId, ownerUserId))
.orderBy(desc(routines.createdAt), desc(routines.id));
const routineIds = rows.map((row) => row.routine.id);
const lastRuns = new Map<
string,
{ status: RoutineRunOutcome | null; finishedAt: Date | null }
>();
if (routineIds.length > 0) {
const runRows = await database
.selectDistinctOn([routineRuns.routineId], {
routineId: routineRuns.routineId,
status: routineRuns.status,
finishedAt: routineRuns.finishedAt,
})
.from(routineRuns)
.where(inArray(routineRuns.routineId, routineIds))
.orderBy(
routineRuns.routineId,
desc(routineRuns.startedAt),
// The id breaks the tie. Two runs of one routine can share a `started_at` — a retry in
// the same instant, a clock with coarse resolution — and without a tiebreak `distinct
// on` picks whichever of them the scan reached first, so the page's "last ran" would
// flip between two rows for no reason a person could see.
desc(routineRuns.id),
);
for (const run of runRows) {
lastRuns.set(run.routineId, {
status: run.status,
finishedAt: run.finishedAt,
});
}
}
return rows.map(
({ routine, channelName, channelDeletedAt, channelExists }) => ({
id: routine.id,
agentId: routine.agentId,
instruction: routine.instruction,
schedule: describeCron(routine.cron),
timezone: routine.timezone,
enabled: routine.enabled,
nextRunAt: routine.nextRunAt,
channelId: routine.channelId,
channelName,
channelDeleted: channelExists === null || channelDeletedAt !== null,
lastRun: lastRuns.get(routine.id) ?? null,
}),
);
},
async remove(ownerUserId, id) {
// Hard, unlike a channel: nothing reads a routine that was deleted, and its runs cascade.
const deleted = await database
.delete(routines)
.where(and(eq(routines.id, id), eq(routines.ownerUserId, ownerUserId)))
.returning({ id: routines.id });
if (deleted.length === 0) throw new RoutineNotFoundError();
},
async startManualRun(ownerUserId, id) {
const routine = await loadOwned(ownerUserId, id);
if (!routine.enabled)
throw new RoutineRefusedError(
"This routine is paused, and paused routines never run. Switch it on first.",
);
return await database.transaction(async (transaction) => {
// Serialized per routine so two clicks cannot both see "nothing open" and both open a run.
await transaction.execute(
sql`select pg_advisory_xact_lock(hashtext(${`routine-run-${id}`}))`,
);
const [open] = await transaction
.select({ id: routineRuns.id })
.from(routineRuns)
.where(
and(
eq(routineRuns.routineId, id),
isNull(routineRuns.status),
// Younger than the reaper's cutoff: an older open row is abandoned, not running.
sql`${routineRuns.startedAt} > now() - interval '10 minutes'`,
),
)
.limit(1);
if (open)
throw new RoutineRefusedError(
"This routine is already running. Wait for that run to finish.",
);
const runId = `routine_run_${crypto.randomUUID()}`;
await transaction
.insert(routineRuns)
.values({ id: runId, routineId: id, source: "run_now" });
return { runId };
});
},
async listRuns(ownerUserId, id, limit = 50) {
await loadOwned(ownerUserId, id);
return await database
.select({
id: routineRuns.id,
status: routineRuns.status,
startedAt: routineRuns.startedAt,
finishedAt: routineRuns.finishedAt,
error: routineRuns.error,
source: routineRuns.source,
})
.from(routineRuns)
.where(eq(routineRuns.routineId, id))
.orderBy(desc(routineRuns.startedAt), desc(routineRuns.id))
.limit(Math.min(Math.max(limit, 1), 100));
},
async setEnabled(ownerUserId, id, enabled) {
// One field through the same path, so enabling re-checks the cap and recomputes the next run
// rather than having a second, quieter version of those rules.
await update(ownerUserId, id, { enabled });
},
/* =========================================================================================
* THE SWEEP'S HALF STARTS HERE.
*
* One store, because there is one table and one owner-guarding discipline to keep straight
* about it; but the two halves read nothing alike. Above, every method takes an owner and
* every failure is one person's — a bad cron, a channel they are not in — and none of them is
* about two things happening at once. Below, nobody asks a question: a sweep on a clock reads
* the ledger, and every method has to be read as a race between replicas.
*
* WHOSE CLOCK. The database's, for every moment these methods compare — the same discipline
* `server/src/work/queue.ts` states in its header, for the same reason: a node ninety seconds
* behind once wrote a lease Postgres considered expired on arrival, and two replicas ran the
* same item. `now()` in SQL, never `Date.now()`.
* ========================================================================================= */
async dueRoutines(limit) {
/*
* NOT OWNER-SCOPED, ON PURPOSE. Every other method in this file is guarded by the owner, so
* an unguarded one reads like an oversight; this one is the sweep's read, and the sweep has
* no owner. It runs as no person and looks at every person's routines, which is exactly why
* it returns ids and stamps and nothing a person wrote.
*/
return await database
.select({ id: routines.id, nextRunAt: routines.nextRunAt })
.from(routines)
.where(
and(
eq(routines.enabled, true),
// The comparison Postgres makes against its own clock. A replica's `Date.now()` here
// would decide what is due from a clock the row was never written by.
lte(routines.nextRunAt, sql`now()`),
),
)
// Oldest due first, so a backlog drains in the order it built up. The id is a tiebreak, so
// two routines due in the same instant come back in a fixed order rather than whichever
// the index happened to hand over.
.orderBy(asc(routines.nextRunAt), asc(routines.id))
.limit(limit);
},
async advanceNextRun(id, from, computeFrom) {
const [row] = await database
.select({ cron: routines.cron, timezone: routines.timezone })
.from(routines)
.where(eq(routines.id, id))
.limit(1);
if (!row) return false;
// `computeFrom` moves only where the next occurrence is measured from, never what the CAS
// compares against: the sweep uses it to make a month-stale clock current in one pass, and
// the guarantee that exactly one replica moves the row has to survive that.
const next = nextRunFor(row.cron, row.timezone, computeFrom ?? from);
/*
* THE COMPARE-AND-SET IS THE WHOLE MECHANISM. `where next_run_at = from` means the row only
* moves for the sweep that read that exact stamp: however many replicas see the same due
* routine in the same second, exactly one update matches and the rest change nothing. False
* means another sweep won, which is fine either way — the firing still happens once.
*
* And it happens AFTER the run is offered — `sweep.ts`'s ordering comment is the one to read
* for why. The old fear here was that advancing after offering would double-fire on a crash
* between the two; it doesn't, because the offer key carries the minute the firing was due, so
* a re-offer of the same due stamp collides on `work_items`' `(kind, key)` primary key rather
* than queueing a second run. That holds even for a firing that already finished: `finish`
* marks the row rather than deleting it, so a finished row still conflicts. The compare-and-set
* below is what makes the advance itself safe under the same race, which is a separate claim
* from the ordering.
*
* The equality is on a stamp that round-trips: every writer of `next_run_at` computes it from
* `nextOccurrence`, which lands on a cron boundary with no sub-second part, and the driver
* binds a `Date` at millisecond precision. The column is microsecond-precision, so
* `next_run_at = now()` written anywhere in SQL would put microseconds in a value this
* comparison reads back truncated — and the CAS would stop matching, silently, for ever. This
* one moment is the database's clock via a value the database gave us, not via `now()`.
*/
const moved = await database
.update(routines)
.set({ nextRunAt: next, lastRunAt: from, updatedAt: sql`now()` })
.where(and(eq(routines.id, id), eq(routines.nextRunAt, from)))
.returning({ id: routines.id });
return moved.length > 0;
},
async insertRun(routineId) {
const runId = `routine_run_${crypto.randomUUID()}`;
// `startedAt` defaults to the database's now, and `status` stays null: null is the in-flight
// state, which is the reason that column is nullable rather than defaulted to something.
const [row] = await database
.insert(routineRuns)
.values({ id: runId, routineId })
.returning({ id: routineRuns.id });
if (!row) throw new Error("inserting a routine run returned no row");
return { runId: row.id };
},
async runContext(runId) {
/*
* An inner join, so a run whose routine is gone reads as no row rather than as a firing with
* nothing to say. `remove` is a hard delete and the runs cascade with it, so in practice both
* sides disappear together; the join is what makes that one absence instead of two.
*/
const [row] = await database
.select({
routineId: routines.id,
ownerUserId: routines.ownerUserId,
agentId: routines.agentId,
channelId: routines.channelId,
instruction: routines.instruction,
enabled: routines.enabled,
})
.from(routineRuns)
.innerJoin(routines, eq(routines.id, routineRuns.routineId))
.where(eq(routineRuns.id, runId))
.limit(1);
return row ?? null;
},
async routineForFiring(id) {
// A single select, not owner-scoped — the sweep's read, like `dueRoutines`. A routine id here
// comes from a work item's own payload, not from a person, so there is no owner to check.
const [row] = await database
.select({ id: routines.id, enabled: routines.enabled })
.from(routines)
.where(eq(routines.id, id))
.limit(1);
return row ?? null;
},
async finishRun(runId, status, error, waiting) {
await database
.update(routineRuns)
.set({
status,
...(waiting ? { waiting } : {}),
// The database's clock closes the row, the same as it opened it.
finishedAt: sql`now()`,
// Left alone rather than nulled when there was no error, so finishing a run twice cannot
// erase what the first finish recorded — and the `status is null` guard below is what
// makes that true.
...(error === undefined
? {}
: {
// Measured in code points, like `validInstruction`, so an emoji-bearing error
// cannot be cut mid-surrogate-pair.
error: Array.from(error).slice(0, MAX_RUN_ERROR).join(""),
}),
})
// A run finishes once. The second call — succeeded, then a downstream throw whose catch
// calls finishRun("failed") — matches no row here and is a silent no-op, rather than
// relabeling what the first finish already recorded.
.where(and(eq(routineRuns.id, runId), isNull(routineRuns.status)));
},
async recordSweep(owner) {
await database
.insert(routineSweeps)
.values({ id: SWEEP_ID, owner, sweptAt: sql`now()` })
.onConflictDoUpdate({
target: routineSweeps.id,
set: { owner, sweptAt: sql`now()` },
});
},
async lastSweptAt() {
const [row] = await database
.select({ sweptAt: routineSweeps.sweptAt })
.from(routineSweeps)
.where(eq(routineSweeps.id, SWEEP_ID))
.limit(1);
return row?.sweptAt ?? null;
},
async reapAbandonedRuns(olderThanMs, error) {
/*
* One UPDATE, not a select-then-loop: every row this WHERE matches is abandoned, and there is
* nothing to decide per row that the age bound does not already decide. Both sides of the age
* comparison are the database's clock — `started_at` was written by its `now()`, so measuring
* it against a replica's `Date.now()` would let ninety seconds of skew reap a run some server
* is still running, which is this file's standing clock discipline.
*/
const closed = await database
.update(routineRuns)
.set({
status: "skipped",
finishedAt: sql`now()`,
// Same code-point cap as `finishRun`, so a reap reason cannot be cut mid-surrogate-pair.
error: Array.from(error).slice(0, MAX_RUN_ERROR).join(""),
})
.where(
and(
isNull(routineRuns.status),
lte(
routineRuns.startedAt,
sql`now() - (${olderThanMs} * interval '1 millisecond')`,
),
),
)
.returning({ id: routineRuns.id });
return closed.length;
},
async markUnschedulable(id, reason) {
const disabled = await database
.update(routines)
.set({ enabled: false, updatedAt: sql`now()` })
.where(eq(routines.id, id))
.returning({ id: routines.id });
// Deleted between the sweep's read and this write: gone is gone, and a run row inserted here
// would only violate the foreign key of a routine nobody can see any more.
if (disabled.length === 0) return;
// A finished "skipped" row rather than "failed": no turn ran, so the fatigue rule must not
// count this, and skipped is exactly the vocabulary for a firing that never became a turn.
await database.insert(routineRuns).values({
id: `routine_run_${crypto.randomUUID()}`,
routineId: id,
status: "skipped",
finishedAt: sql`now()`,
error: Array.from(reason).slice(0, MAX_RUN_ERROR).join(""),
});
},
async consecutiveFailures(routineId) {
/*
* Bounded, then counted here. The bound is the point: this is read on every failed firing,
* for ever, and `select ... where routine_id = $1` with no limit gets slower for exactly the
* routines that fail most. The rule only acts on the first handful, so reading twice that
* many and stopping is the whole answer.
*
* Finished runs only — an in-flight run has no outcome yet and must not end the streak.
*/
const rows = await database
.select({ status: routineRuns.status })
.from(routineRuns)
.where(
and(
eq(routineRuns.routineId, routineId),
isNotNull(routineRuns.status),
/*
* A SKIP IS NOT A FAILURE, AND DOES NOT BREAK THE STREAK. It means no turn ran — the
* channel was gone, the dispatch never reached the server, or a restart abandoned the
* run — not that the turn failed, so it is not counted; and it does not reset the
* count either, because a routine whose channel flaps would otherwise never reach the
* fatigue rule — it would disable itself over ten missing channels, or never at all.
* Excluding it here, rather than reading it and skipping over it below, keeps it from
* consuming a slot in the bounded window: a routine that skips twice for every
* failure must still be able to count past ten failures, not top out around six or
* seven because skips ate two-thirds of the rows the window could hold.
*/
ne(routineRuns.status, "skipped"),
/*
* ONLY SINCE IT WAS LAST SWITCHED ON. The rule switches a routine off after ten failures
* and says a person must switch it back on; counting across that would leave the eleventh
* run, the first after the person's fix, switching it straight off again with "failed ten
* times in a row", and never saying the first-failure line at all.
*/
gte(
routineRuns.startedAt,
sql`(select ${routines.enabledAt} from ${routines} where ${routines.id} = ${routineId})`,
),
),
)
.orderBy(desc(routineRuns.startedAt), desc(routineRuns.id))
.limit(FAILURE_SCAN_LIMIT);
let failures = 0;
for (const run of rows) {
if (run.status !== "failed") break;
failures += 1;
}
return failures;
},
};
}