* The run tables of workflows: runs and the steps of each run, and steps that ran alone. Rows are
* written directly, like the graph tables. Each change of a step is conditional on the status it
* leaves, so two sweeps, or a sweep and a user action, never both settle one step.
*/
import {
isWorkflowGraph,
WORKFLOW_BRANCHES,
WORKFLOW_NODE_KINDS,
WORKFLOW_RUN_SOURCES,
WORKFLOW_RUN_STATUSES,
WORKFLOW_STEP_STATUSES,
type WorkflowBranch,
type WorkflowGraph,
type WorkflowNodeKind,
type WorkflowRun,
type WorkflowRunPage,
type WorkflowRunSource,
type WorkflowRunStatus,
type WorkflowStep,
type WorkflowStepStatus,
} from "@openbot/contracts/ipc";
import { type DynamicRecord, isOneOf } from "@openbot/contracts/runtime-values";
import {
checkedColumn,
databaseRow,
databaseRows,
optionalStringColumn,
placeholders,
requiredNumberColumn,
requiredStringColumn,
} from "../database/database-rows";
import type { OpenBotDatabase } from "../openbot-database";
import { webhookReceiptCutoff } from "../webhook-route-store";
import { workflowTransaction } from "./workflow-transaction";
export interface StoredWorkflowRun extends WorkflowRun {
graph: WorkflowGraph;
fromNodeId: string | null;
scheduledFor: string | null;
payload: string | null;
}
export interface StoredWorkflowStep extends WorkflowStep {
kind: WorkflowNodeKind;
input: string;
deliveryId: string | null;
turnId: string | null;
createdAt: string;
sequence: number;
}
const OPEN_STEP_STATUSES = ["running", "approval"] as const satisfies readonly WorkflowStepStatus[];
export function isOpenStep(step: Pick<WorkflowStep, "status">): boolean {
return isOneOf(OPEN_STEP_STATUSES, step.status);
}
const OPEN_RUN_STATUSES = ["running", "approval", "stopping"] as const satisfies readonly WorkflowRunStatus[];
export interface WorkflowWebhookReceipt {
workflowId: string;
nodeId: string;
deliveryId: string;
status: "started" | "ignored";
reason: "busy" | "refused" | null;
runId: string | null;
receivedAt: string;
}
const OUTPUT_STATUSES = ["pending", "sending", "sent", "failed", "uncertain"] as const;
export type WorkflowOutputEnd = Exclude<(typeof OUTPUT_STATUSES)[number], "pending" | "sending">;
* The post of an output step to a messaging place. `sending` is set before the post starts, so a
* post that a stop or a crash cut off becomes `uncertain`: part of it can be in the place.
*/
export interface WorkflowOutputDelivery {
stepId: string;
connectionId: string;
placeId: string;
text: string;
status: (typeof OUTPUT_STATUSES)[number];
reason: string | null;
}
export interface NewWorkflowRun {
workflowId: string;
triggerNodeId: string | null;
fromNodeId: string | null;
source: WorkflowRunSource;
scheduledFor: string | null;
idempotencyKey: string;
revision: number;
graph: WorkflowGraph;
payload: string | null;
}
export interface NewWorkflowStep {
workflowId: string;
runId: string | null;
nodeId: string;
kind: WorkflowNodeKind;
requestId: string;
agentId: string | null;
status: WorkflowStepStatus;
input: string;
output?: string | null;
branch?: WorkflowBranch | null;
error?: string | null;
}
export interface WorkflowStepEnd {
status: Exclude<WorkflowStepStatus, "running" | "approval">;
output?: string | null;
branch?: WorkflowBranch | null;
error?: string | null;
}
export class WorkflowRunStore {
readonly #database: OpenBotDatabase;
readonly #createId: () => string;
constructor(options: { database: OpenBotDatabase; createId?: () => string }) {
this.#database = options.database;
this.#createId = options.createId ?? (() => crypto.randomUUID());
}
* Starts a run, or answers the run that the same key already started. `created` writes what belongs
* to a new run, such as its version and its first steps, in the same transaction.
*/
startRun(
input: NewWorkflowRun,
now = new Date(),
created: (run: StoredWorkflowRun) => void = () => {},
): { run: StoredWorkflowRun; created: boolean } {
return this.#transaction(() => {
const db = this.#database.connection;
const existing = databaseRow(
db
.prepare("SELECT run_id FROM workflow_runs WHERE workflow_id = ? AND idempotency_key = ?")
.get(input.workflowId, input.idempotencyKey),
);
if (existing) {
const run = this.run(requiredStringColumn(existing, "run_id"));
if (run) return { run, created: false };
}
const numbered = databaseRow(
db
.prepare(
"UPDATE workflows SET next_run_number = next_run_number + 1 WHERE workflow_id = ? RETURNING next_run_number - 1 AS number",
)
.get(input.workflowId),
);
if (!numbered) throw new Error("The workflow of a new run does not exist.");
const runId = this.#createId();
const at = now.toISOString();
db.prepare(
`INSERT INTO workflow_runs (run_id, workflow_id, number, trigger_node_id, from_node_id, source, scheduled_for,
idempotency_key, graph_revision, graph_json, payload, status, started_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'running', ?, ?)`,
).run(
runId,
input.workflowId,
requiredNumberColumn(numbered, "number"),
input.triggerNodeId,
input.fromNodeId,
input.source,
input.scheduledFor,
input.idempotencyKey,
input.revision,
JSON.stringify(input.graph),
input.payload,
at,
at,
);
const run = this.run(runId);
if (!run) throw new Error("A new workflow run was not stored.");
created(run);
return { run, created: true };
});
}
hasWebhookReceipt(receipt: Pick<WorkflowWebhookReceipt, "workflowId" | "nodeId" | "deliveryId" | "receivedAt">) {
return (
databaseRow(
this.#database.connection
.prepare(
`SELECT 1 AS found FROM workflow_webhook_receipts
WHERE workflow_id = ? AND node_id = ? AND delivery_id = ? AND received_at >= ?`,
)
.get(receipt.workflowId, receipt.nodeId, receipt.deliveryId, webhookReceiptCutoff(receipt.receivedAt)),
) !== null
);
}
addWebhookReceipt(receipt: WorkflowWebhookReceipt): void {
this.#transaction(() => {
const db = this.#database.connection;
db.prepare("DELETE FROM workflow_webhook_receipts WHERE received_at < ?").run(
webhookReceiptCutoff(receipt.receivedAt),
);
db.prepare(
`INSERT INTO workflow_webhook_receipts (receipt_id, workflow_id, node_id, delivery_id, status, reason, run_id,
received_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(workflow_id, node_id, delivery_id) DO UPDATE SET status = excluded.status,
reason = excluded.reason, run_id = excluded.run_id, received_at = excluded.received_at`,
).run(
this.#createId(),
receipt.workflowId,
receipt.nodeId,
receipt.deliveryId,
receipt.status,
receipt.reason,
receipt.runId,
receipt.receivedAt,
);
});
}
run(runId: string): StoredWorkflowRun | null {
const row = databaseRow(this.#database.connection.prepare(`${RUN_SELECT} WHERE run_id = ?`).get(runId));
return row ? toRun(row) : null;
}
openRuns(): StoredWorkflowRun[] {
return databaseRows(
this.#database.connection
.prepare(`${RUN_SELECT} WHERE status IN (${placeholders(OPEN_RUN_STATUSES)}) ORDER BY started_at, run_id`)
.all(...OPEN_RUN_STATUSES),
).map(toRun);
}
setRunStatus(runId: string, status: WorkflowRunStatus, error: string | null, now = new Date()): boolean {
const at = now.toISOString();
const ended = isOneOf(OPEN_RUN_STATUSES, status) ? null : at;
return (
this.#database.connection
.prepare(
`UPDATE workflow_runs SET status = ?, error = ?, ended_at = ?, updated_at = ?
WHERE run_id = ? AND status IN (${placeholders(OPEN_RUN_STATUSES)}) AND (status <> ? OR error IS NOT ?)`,
)
.run(status, error, ended, at, runId, ...OPEN_RUN_STATUSES, status, error).changes > 0
);
}
reopenRun(runId: string, now = new Date()): boolean {
return (
this.#database.connection
.prepare(
`UPDATE workflow_runs SET status = 'running', error = NULL, ended_at = NULL, updated_at = ?
WHERE run_id = ? AND status IN ('done', 'failed', 'stopped')`,
)
.run(now.toISOString(), runId).changes > 0
);
}
steps(runId: string): StoredWorkflowStep[] {
return databaseRows(
this.#database.connection
.prepare(`${STEP_SELECT} WHERE run_id = ? ORDER BY created_at, attempt, step_id`)
.all(runId),
).map(toStep);
}
step(stepId: string): StoredWorkflowStep | null {
const row = databaseRow(this.#database.connection.prepare(`${STEP_SELECT} WHERE step_id = ?`).get(stepId));
return row ? toStep(row) : null;
}
openSteps(): StoredWorkflowStep[] {
return databaseRows(
this.#database.connection
.prepare(`${STEP_SELECT} WHERE status IN (${placeholders(OPEN_STEP_STATUSES)}) ORDER BY created_at, step_id`)
.all(...OPEN_STEP_STATUSES),
).map(toStep);
}
* The newest step of each node that ended with an answer, of runs and alone. A run from a node
* reads the steps before that node from here. The copy of an earlier answer that a run from a node
* adds is left out: the step that gave the answer has the same one, and the run of that step has
* the graph that it was given in.
*/
latestDone(workflowId: string): Map<string, StoredWorkflowStep> {
const rows = databaseRows(
this.#database.connection
.prepare(
`${STEP_SELECT} WHERE workflow_id = ? AND status = 'done'
AND (run_id IS NULL OR request_id <> run_id || ':' || node_id || ':reused')
ORDER BY created_at, attempt, step_id`,
)
.all(workflowId),
).map(toStep);
return new Map(rows.map((step) => [step.nodeId, step]));
}
* Adds a step, or answers the step that the same request already added. The attempt is the
* node's next one in its run; a step alone is always the first.
*/
addStep(input: NewWorkflowStep, now = new Date()): StoredWorkflowStep {
return this.#transaction(() => {
const db = this.#database.connection;
const existing = databaseRow(
db
.prepare("SELECT step_id FROM workflow_steps WHERE workflow_id = ? AND request_id = ?")
.get(input.workflowId, input.requestId),
);
if (existing) {
const step = this.step(requiredStringColumn(existing, "step_id"));
if (step) return step;
}
const attempt =
input.runId === null
? 1
: requiredNumberColumn(
databaseRow(
db
.prepare(
"SELECT COALESCE(MAX(attempt), 0) + 1 AS attempt FROM workflow_steps WHERE run_id = ? AND node_id = ?",
)
.get(input.runId, input.nodeId),
) ?? {},
"attempt",
);
const stepId = this.#createId();
const at = now.toISOString();
const ended = isOneOf(OPEN_STEP_STATUSES, input.status) ? null : at;
db.prepare(
`INSERT INTO workflow_steps (step_id, workflow_id, run_id, node_id, attempt, request_id, kind, agent_id, status,
input, output, branch, error, started_at, ended_at, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
).run(
stepId,
input.workflowId,
input.runId,
input.nodeId,
attempt,
input.requestId,
input.kind,
input.agentId,
input.status,
input.input,
input.output ?? null,
input.branch ?? null,
input.error ?? null,
at,
ended,
at,
at,
);
const step = this.step(stepId);
if (!step) throw new Error("A new workflow step was not stored.");
return step;
});
}
attachDelivery(stepId: string, deliveryId: string, now = new Date()): void {
this.#database.connection
.prepare("UPDATE workflow_steps SET delivery_id = ?, updated_at = ? WHERE step_id = ? AND delivery_id IS NULL")
.run(deliveryId, now.toISOString(), stepId);
}
attachTurn(stepId: string, turnId: string, now = new Date()): void {
this.#database.connection
.prepare("UPDATE workflow_steps SET turn_id = ?, updated_at = ? WHERE step_id = ? AND turn_id IS NOT ?")
.run(turnId, now.toISOString(), stepId, turnId);
}
awaitApproval(stepId: string, now = new Date()): boolean {
return (
this.#database.connection
.prepare(
"UPDATE workflow_steps SET status = 'approval', updated_at = ? WHERE step_id = ? AND status = 'running'",
)
.run(now.toISOString(), stepId).changes > 0
);
}
settleStep(stepId: string, end: WorkflowStepEnd, now = new Date()): boolean {
const at = now.toISOString();
return (
this.#database.connection
.prepare(
`UPDATE workflow_steps SET status = ?, output = ?, branch = ?, error = ?, ended_at = ?, updated_at = ?,
started_at = COALESCE(started_at, ?)
WHERE step_id = ? AND status IN (${placeholders(OPEN_STEP_STATUSES)})`,
)
.run(
end.status,
end.output ?? null,
end.branch ?? null,
end.error ?? null,
at,
at,
at,
stepId,
...OPEN_STEP_STATUSES,
).changes > 0
);
}
addOutput(output: Pick<WorkflowOutputDelivery, "stepId" | "connectionId" | "placeId" | "text">, now = new Date()) {
const at = now.toISOString();
this.#database.connection
.prepare(
`INSERT OR IGNORE INTO workflow_output_deliveries
(step_id, connection_id, place_id, text, status, created_at, updated_at)
VALUES (?, ?, ?, ?, 'pending', ?, ?)`,
)
.run(output.stepId, output.connectionId, output.placeId, output.text, at, at);
}
output(stepId: string): WorkflowOutputDelivery | null {
const row = databaseRow(
this.#database.connection.prepare("SELECT * FROM workflow_output_deliveries WHERE step_id = ?").get(stepId),
);
return row ? toOutput(row) : null;
}
claimOutput(stepId: string, now = new Date()): boolean {
return (
this.#database.connection
.prepare(
`UPDATE workflow_output_deliveries SET status = 'sending', attempts = attempts + 1, updated_at = ?
WHERE step_id = ? AND status = 'pending'`,
)
.run(now.toISOString(), stepId).changes > 0
);
}
endOutput(stepId: string, status: WorkflowOutputEnd, reason: string | null, now = new Date()): void {
const at = now.toISOString();
this.#database.connection
.prepare(
`UPDATE workflow_output_deliveries SET status = ?, reason = ?, sent_at = ?, updated_at = ?
WHERE step_id = ? AND status IN ('pending', 'sending')`,
)
.run(status, reason, status === "sent" ? at : null, at, stepId);
}
* Runs newest first, after the `before` cursor of the page before when given, and on the first page
* the newest steps alone. The cursor is `<started at>|<run id>`, so runs that started in the same
* millisecond are not skipped.
*/
page(workflowId: string, before: string | undefined, limit: number): WorkflowRunPage {
const db = this.#database.connection;
const cursor = before === undefined ? null : runCursor(before);
const rows = databaseRows(
cursor === null
? db
.prepare(`${RUN_SELECT} WHERE workflow_id = ? ORDER BY started_at DESC, run_id DESC LIMIT ?`)
.all(workflowId, limit + 1)
: db
.prepare(
`${RUN_SELECT} WHERE workflow_id = ? AND (started_at < ? OR (started_at = ? AND run_id < ?))
ORDER BY started_at DESC, run_id DESC LIMIT ?`,
)
.all(workflowId, cursor.startedAt, cursor.startedAt, cursor.runId, limit + 1),
).map(toRun);
const runs = rows.slice(0, limit);
const last = runs.at(-1);
const soloSteps =
before === undefined
? databaseRows(
db
.prepare(
`${STEP_SELECT} WHERE workflow_id = ? AND run_id IS NULL ORDER BY created_at DESC, step_id DESC LIMIT ?`,
)
.all(workflowId, limit),
).map(toStep)
: [];
return {
runs: runs.map((run) => ({ run: publicRun(run), steps: this.steps(run.id).map(publicStep) })),
soloSteps: soloSteps.map(publicStep),
nextBefore: rows.length > limit && last ? `${last.startedAt}|${last.id}` : null,
};
}
#transaction<T>(work: () => T): T {
return workflowTransaction(this.#database, work);
}
}
function runCursor(before: string): { startedAt: string; runId: string } {
const bar = before.lastIndexOf("|");
return bar < 0 ? { startedAt: before, runId: "" } : { startedAt: before.slice(0, bar), runId: before.slice(bar + 1) };
}
export function publicRun(run: StoredWorkflowRun): WorkflowRun {
return {
id: run.id,
workflowId: run.workflowId,
number: run.number,
triggerNodeId: run.triggerNodeId,
source: run.source,
status: run.status,
error: run.error,
startedAt: run.startedAt,
endedAt: run.endedAt,
};
}
export function publicStep(step: StoredWorkflowStep): WorkflowStep {
return {
id: step.id,
workflowId: step.workflowId,
runId: step.runId,
nodeId: step.nodeId,
attempt: step.attempt,
agentId: step.agentId,
status: step.status,
output: step.output,
branch: step.branch,
error: step.error,
startedAt: step.startedAt,
endedAt: step.endedAt,
};
}
const RUN_SELECT = `
SELECT run_id, workflow_id, number, trigger_node_id, from_node_id, source, scheduled_for, graph_json, payload,
status, error, started_at, ended_at
FROM workflow_runs`;
const STEP_SELECT = `
SELECT step_id, workflow_id, run_id, node_id, attempt, kind, agent_id, status, delivery_id, turn_id, input, output,
branch, error, started_at, ended_at, created_at, rowid AS sequence
FROM workflow_steps`;
function toRun(row: DynamicRecord): StoredWorkflowRun {
const graph = JSON.parse(requiredStringColumn(row, "graph_json"));
if (!isWorkflowGraph(graph)) throw new Error("Invalid SQLite column: workflow_runs.graph_json");
return {
id: requiredStringColumn(row, "run_id"),
workflowId: requiredStringColumn(row, "workflow_id"),
number: requiredNumberColumn(row, "number"),
triggerNodeId: optionalStringColumn(row, "trigger_node_id"),
fromNodeId: optionalStringColumn(row, "from_node_id"),
source: checkedColumn(row, "source", WORKFLOW_RUN_SOURCES),
status: checkedColumn(row, "status", WORKFLOW_RUN_STATUSES),
error: optionalStringColumn(row, "error"),
startedAt: requiredStringColumn(row, "started_at"),
endedAt: optionalStringColumn(row, "ended_at"),
scheduledFor: optionalStringColumn(row, "scheduled_for"),
payload: optionalStringColumn(row, "payload"),
graph,
};
}
function toStep(row: DynamicRecord): StoredWorkflowStep {
const branch = row.branch;
return {
id: requiredStringColumn(row, "step_id"),
workflowId: requiredStringColumn(row, "workflow_id"),
runId: optionalStringColumn(row, "run_id"),
nodeId: requiredStringColumn(row, "node_id"),
attempt: requiredNumberColumn(row, "attempt"),
kind: checkedColumn(row, "kind", WORKFLOW_NODE_KINDS),
agentId: optionalStringColumn(row, "agent_id"),
status: checkedColumn(row, "status", WORKFLOW_STEP_STATUSES),
deliveryId: optionalStringColumn(row, "delivery_id"),
turnId: optionalStringColumn(row, "turn_id"),
input: requiredStringColumn(row, "input"),
output: optionalStringColumn(row, "output"),
branch: isOneOf(WORKFLOW_BRANCHES, branch) ? branch : null,
error: optionalStringColumn(row, "error"),
startedAt: optionalStringColumn(row, "started_at"),
endedAt: optionalStringColumn(row, "ended_at"),
createdAt: requiredStringColumn(row, "created_at"),
sequence: requiredNumberColumn(row, "sequence"),
};
}
function toOutput(row: DynamicRecord): WorkflowOutputDelivery {
return {
stepId: requiredStringColumn(row, "step_id"),
connectionId: requiredStringColumn(row, "connection_id"),
placeId: requiredStringColumn(row, "place_id"),
text: requiredStringColumn(row, "text"),
status: checkedColumn(row, "status", OUTPUT_STATUSES),
reason: optionalStringColumn(row, "reason"),
};
}