Formerly KrillinAI, VideoTranslation and Dubbing Tool for 120+ Languages. Open-source AI workspace for creators, powered by Codex. Video downloader, video translation, create videos, images, audios, and edits with Agents in one place. For your Social Media like YouTube, TikTok, Instagram, etc.
TypeScript agent-skillscodex-clideskop-appdubbingimage-generationlocalizationskillsttsvideo-editingvideo-generationvideo-transcriptionvideo-translation
import type {
ScheduleActorType,
ScheduleDiagnosticEvent,
ScheduleDiagnosticEventType,
ScheduleRunTrace,
ScheduleTriggerType
} from '@opencreator/protocol';
import type Database from 'better-sqlite3';
import { nanoid } from 'nanoid';
import type {
InsertScheduleInput,
InsertScheduleOperationInput,
ScheduleOperationActor,
ScheduleOperationRecord,
ScheduleRecord,
UpdateScheduleInput
} from './types.js';
type ScheduleRow = {
id: string;
thread_id: string | null;
name: string;
cron: string;
timezone: string;
enabled: number;
prompt: string;
prompt_hash: string;
prompt_preview_redacted: string;
profile: string;
cwd: string;
canonical_cwd: string;
model: string | null;
reasoning: ScheduleRecord['reasoning'];
sandbox: ScheduleRecord['sandbox'];
timeout_ms: number | null;
concurrency_policy: ScheduleRecord['concurrencyPolicy'];
misfire_policy: ScheduleRecord['misfirePolicy'];
next_run_at: string | null;
last_run_at: string | null;
last_run_id: string | null;
last_status: ScheduleRecord['lastStatus'];
pending_trigger: number;
created_at: string;
updated_at: string;
deleted_at: string | null;
};
type ScheduleOperationRow = {
id: string;
schedule_id: string;
operation: ScheduleOperationRecord['operation'];
status: ScheduleOperationRecord['status'];
run_id: string | null;
actor_type: string | null;
actor_run_id: string | null;
error_code: string | null;
error_message: string | null;
created_at: string;
};
type ScheduleRunTraceRow = {
operation_id: string;
schedule_id: string;
operation: ScheduleTriggerType;
actor_type: string | null;
actor_run_id: string | null;
operation_created_at: string;
operation_error_code: string | null;
schedule_thread_id: string | null;
run_id: string;
run_thread_id: string | null;
triggered_at: string | null;
started_at: string | null;
ended_at: string | null;
run_updated_at: string;
public_status: ScheduleRunTrace['status'];
queue_state: string;
run_error_code: string | null;
approval_requested_at: string | null;
};
type ScheduleRepositoryOptions = {
idFactory?: () => string;
operationIdFactory?: () => string;
now?: () => string;
};
type UpdateBinding = {
id: string;
[key: string]: string | number | null;
};
const scheduleColumns = `
id, thread_id, name, cron, timezone, enabled, prompt, prompt_hash, prompt_preview_redacted,
profile, cwd, canonical_cwd, model, reasoning, sandbox, timeout_ms, concurrency_policy,
misfire_policy, next_run_at, last_run_at, last_run_id, last_status, pending_trigger,
created_at, updated_at, deleted_at
`;
export class ScheduleRepository {
private readonly idFactory: () => string;
private readonly operationIdFactory: () => string;
private readonly now: () => string;
constructor(private readonly db: Database.Database, options: ScheduleRepositoryOptions = {}) {
this.idFactory = options.idFactory ?? (() => `sch_${nanoid()}`);
this.operationIdFactory = options.operationIdFactory ?? (() => `schop_${nanoid()}`);
this.now = options.now ?? (() => new Date().toISOString());
}
create(input: InsertScheduleInput): ScheduleRecord {
const id = this.idFactory();
const now = this.now();
this.db
.prepare(
`
INSERT INTO schedules (
id, thread_id, name, cron, timezone, enabled, prompt, prompt_hash, prompt_preview_redacted,
profile, cwd, canonical_cwd, model, reasoning, sandbox, timeout_ms, concurrency_policy,
misfire_policy, next_run_at, pending_trigger, created_at, updated_at
) VALUES (
@id, @threadId, @name, @cron, @timezone, @enabled, @prompt, @promptHash, @promptPreviewRedacted,
@profile, @cwd, @canonicalCwd, @model, @reasoning, @sandbox, @timeoutMs, @concurrencyPolicy,
@misfirePolicy, @nextRunAt, 0, @createdAt, @updatedAt
)
`
)
.run({
id,
threadId: input.threadId ?? null,
name: input.name,
cron: input.cron,
timezone: input.timezone,
enabled: booleanToInteger(input.enabled),
prompt: input.prompt,
promptHash: input.promptHash,
promptPreviewRedacted: input.promptPreviewRedacted,
profile: input.profile,
cwd: input.cwd,
canonicalCwd: input.canonicalCwd,
model: input.model ?? null,
reasoning: input.reasoning ?? null,
sandbox: input.sandbox,
timeoutMs: input.timeoutMs ?? null,
concurrencyPolicy: input.concurrencyPolicy,
misfirePolicy: input.misfirePolicy,
nextRunAt: input.nextRunAt ?? null,
createdAt: now,
updatedAt: now
});
const created = this.getById(id);
if (created === null) throw new Error(`Created schedule not found: ${id}`);
return created;
}
getById(id: string): ScheduleRecord | null {
const row = this.db
.prepare<string>(`SELECT ${scheduleColumns} FROM schedules WHERE id = ? AND deleted_at IS NULL`)
.get(id) as ScheduleRow | undefined;
return row === undefined ? null : mapSchedule(row);
}
list(): ScheduleRecord[] {
const rows = this.db
.prepare(
`
SELECT ${scheduleColumns}
FROM schedules
WHERE deleted_at IS NULL
ORDER BY created_at DESC, id DESC
`
)
.all() as ScheduleRow[];
return rows.map(mapSchedule);
}
listProfileReferences(profile: string): Array<{ id: string; name: string }> {
return this.db
.prepare<string>(
`
SELECT id, name
FROM schedules
WHERE profile = ? AND deleted_at IS NULL
ORDER BY updated_at DESC, id DESC
`
)
.all(profile) as Array<{ id: string; name: string }>;
}
update(id: string, input: UpdateScheduleInput): ScheduleRecord | null {
const entries = updateEntries(input);
if (entries.length === 0) return this.getById(id);
const bindings: UpdateBinding = { id };
const assignments = entries.map(([column, value], index) => {
const key = `value${index}`;
bindings[key] = value;
return `${column} = @${key}`;
});
bindings.updatedAt = this.now();
assignments.push('updated_at = @updatedAt');
this.db
.prepare(
`
UPDATE schedules
SET ${assignments.join(', ')}
WHERE id = @id AND deleted_at IS NULL
`
)
.run(bindings);
return this.getById(id);
}
softDelete(id: string): boolean {
const now = this.now();
const result = this.db
.prepare(
`
UPDATE schedules
SET deleted_at = @now,
updated_at = @now
WHERE id = @id AND deleted_at IS NULL
`
)
.run({ id, now });
return result.changes > 0;
}
listDue(now: string): ScheduleRecord[] {
const rows = this.db
.prepare<string>(
`
SELECT ${scheduleColumns}
FROM schedules
WHERE enabled = 1
AND deleted_at IS NULL
AND next_run_at IS NOT NULL
AND next_run_at <= ?
ORDER BY next_run_at ASC, id ASC
`
)
.all(now) as ScheduleRow[];
return rows.map(mapSchedule);
}
getNextEnabled(): ScheduleRecord | null {
const row = this.db
.prepare(
`
SELECT ${scheduleColumns}
FROM schedules
WHERE enabled = 1
AND deleted_at IS NULL
AND next_run_at IS NOT NULL
ORDER BY next_run_at ASC, id ASC
LIMIT 1
`
)
.get() as ScheduleRow | undefined;
return row === undefined ? null : mapSchedule(row);
}
listPendingTriggers(): ScheduleRecord[] {
const rows = this.db
.prepare(
`
SELECT ${scheduleColumns}
FROM schedules
WHERE enabled = 1
AND deleted_at IS NULL
AND pending_trigger = 1
ORDER BY updated_at ASC, id ASC
`
)
.all() as ScheduleRow[];
return rows.map(mapSchedule);
}
setPendingTrigger(id: string, pending: boolean): ScheduleRecord | null {
return this.update(id, { pendingTrigger: pending });
}
recordRun(input: {
id: string;
runId: string;
ranAt: string;
status: ScheduleRecord['lastStatus'];
}): ScheduleRecord | null {
return this.update(input.id, {
lastRunAt: input.ranAt,
lastRunId: input.runId,
lastStatus: input.status,
pendingTrigger: false
});
}
recordSkipped(input: {
id: string;
status: Extract<ScheduleRecord['lastStatus'], 'skipped' | 'queued' | 'failed'>;
}): ScheduleRecord | null {
return this.update(input.id, { lastStatus: input.status });
}
reconcileLastRunStatuses(): number {
const result = this.db
.prepare<{ updatedAt: string }>(
`
UPDATE schedules
SET last_status = (
SELECT runs.public_status
FROM runs
WHERE runs.id = schedules.last_run_id
),
updated_at = @updatedAt
WHERE deleted_at IS NULL
AND last_run_id IS NOT NULL
AND last_status IN ('queued', 'running')
AND EXISTS (
SELECT 1
FROM runs
WHERE runs.id = schedules.last_run_id
AND runs.created_by = 'schedule'
AND runs.source_id = schedules.id
AND runs.public_status IN ('succeeded', 'failed', 'canceled')
)
`
)
.run({ updatedAt: this.now() });
return result.changes;
}
insertOperation(
input: InsertScheduleOperationInput,
actor: ScheduleOperationActor
): ScheduleOperationRecord {
const id = this.operationIdFactory();
const createdAt = this.now();
const actorRunId = actor.type === 'agent' ? actor.runId : null;
this.db
.prepare(
`
INSERT INTO schedule_operations (
id, schedule_id, operation, status, run_id, actor_type, actor_run_id,
error_code, error_message, created_at
) VALUES (
@id, @scheduleId, @operation, @status, @runId, @actorType, @actorRunId,
@errorCode, @errorMessage, @createdAt
)
`
)
.run({
id,
scheduleId: input.scheduleId,
operation: input.operation,
status: input.status,
runId: input.runId ?? null,
actorType: actor.type,
actorRunId,
errorCode: input.errorCode ?? null,
errorMessage: input.errorMessage ?? null,
createdAt
});
return {
id,
scheduleId: input.scheduleId,
operation: input.operation,
status: input.status,
runId: input.runId ?? null,
actorType: actor.type,
actorRunId,
diagnosticEvent: diagnosticEventForOperation(input.operation),
errorCode: input.errorCode ?? null,
errorMessage: input.errorMessage ?? null,
createdAt
};
}
listOperations(scheduleId: string, limit = 50): ScheduleOperationRecord[] {
const rows = this.db
.prepare<{ scheduleId: string; limit: number }>(
`
SELECT id, schedule_id, operation, status, run_id, actor_type, actor_run_id,
error_code, error_message, created_at
FROM schedule_operations
WHERE schedule_id = @scheduleId
ORDER BY created_at DESC, rowid DESC
LIMIT @limit
`
)
.all({ scheduleId, limit }) as ScheduleOperationRow[];
return rows.map(mapOperation);
}
getRunTrace(runId: string): ScheduleRunTrace | undefined {
const row = this.db
.prepare<string>(
`
SELECT
operation.id AS operation_id,
operation.schedule_id,
operation.operation,
operation.actor_type,
operation.actor_run_id,
operation.created_at AS operation_created_at,
operation.error_code AS operation_error_code,
schedule.thread_id AS schedule_thread_id,
run.id AS run_id,
run.thread_id AS run_thread_id,
run.triggered_at,
run.started_at,
run.ended_at,
run.updated_at AS run_updated_at,
run.public_status,
run.queue_state,
run.error_code AS run_error_code,
(
SELECT MAX(approval.requested_at)
FROM approvals approval
WHERE approval.run_id = run.id
) AS approval_requested_at
FROM schedule_operations operation
JOIN schedules schedule ON schedule.id = operation.schedule_id
JOIN runs run ON run.id = operation.run_id
WHERE operation.run_id = ?
AND operation.operation IN ('run_now', 'timer_trigger', 'run_queued')
AND run.created_by = 'schedule'
AND run.source_id = operation.schedule_id
ORDER BY operation.created_at DESC, operation.rowid DESC
LIMIT 1
`
)
.get(runId) as ScheduleRunTraceRow | undefined;
if (row === undefined) return undefined;
const threadId = row.run_thread_id ?? row.schedule_thread_id;
if (threadId === null) return undefined;
return mapRunTrace(row, threadId);
}
}
function mapSchedule(row: ScheduleRow): ScheduleRecord {
return {
id: row.id,
threadId: row.thread_id,
name: row.name,
cron: row.cron,
timezone: row.timezone,
enabled: row.enabled === 1,
prompt: row.prompt,
promptHash: row.prompt_hash,
promptPreviewRedacted: row.prompt_preview_redacted,
profile: row.profile,
cwd: row.cwd,
canonicalCwd: row.canonical_cwd,
model: row.model,
reasoning: row.reasoning,
sandbox: row.sandbox,
timeoutMs: row.timeout_ms,
concurrencyPolicy: row.concurrency_policy,
misfirePolicy: row.misfire_policy,
nextRunAt: row.next_run_at,
lastRunAt: row.last_run_at,
lastRunId: row.last_run_id,
lastStatus: row.last_status,
pendingTrigger: row.pending_trigger === 1,
createdAt: row.created_at,
updatedAt: row.updated_at,
deletedAt: row.deleted_at
};
}
function mapOperation(row: ScheduleOperationRow): ScheduleOperationRecord {
return {
id: row.id,
scheduleId: row.schedule_id,
operation: row.operation,
status: row.status,
runId: row.run_id,
actorType: parseActorType(row.actor_type),
actorRunId: row.actor_run_id,
diagnosticEvent: diagnosticEventForOperation(row.operation),
errorCode: row.error_code,
errorMessage: row.error_message,
createdAt: row.created_at
};
}
function mapRunTrace(row: ScheduleRunTraceRow, threadId: string): ScheduleRunTrace {
const errorCode = row.run_error_code ?? row.operation_error_code;
const events: ScheduleDiagnosticEvent[] = [];
const triggerEvent = diagnosticEventForOperation(row.operation);
if (triggerEvent !== undefined) {
events.push({
type: triggerEvent,
occurredAt: row.operation_created_at,
operationId: row.operation_id
});
}
if (row.started_at !== null) {
events.push({
type: 'SCHEDULE_RUN_STARTED',
occurredAt: row.started_at
});
}
if (row.approval_requested_at !== null) {
events.push({
type: 'SCHEDULE_RUN_WAITING_APPROVAL',
occurredAt: row.approval_requested_at
});
}
if (isTerminalRunStatus(row.public_status)) {
events.push({
type: 'SCHEDULE_RUN_COMPLETED',
occurredAt: row.ended_at ?? row.run_updated_at,
...(errorCode === null ? {} : { errorCode })
});
}
events.sort((left, right) => left.occurredAt.localeCompare(right.occurredAt));
return {
scheduleId: row.schedule_id,
threadId,
runId: row.run_id,
triggerType: row.operation,
actorType: parseActorType(row.actor_type),
actorRunId: row.actor_run_id,
scheduledAt: row.triggered_at ?? row.operation_created_at,
startedAt: row.started_at,
endedAt: row.ended_at,
status: row.public_status,
queueReason:
row.operation === 'run_queued'
|| row.queue_state === 'queued'
|| row.public_status === 'queued'
? 'thread_active'
: null,
errorCode,
events
};
}
function diagnosticEventForOperation(
operation: ScheduleOperationRecord['operation']
): ScheduleDiagnosticEventType | undefined {
if (
operation === 'run_now'
|| operation === 'timer_trigger'
|| operation === 'run_queued'
) {
return 'SCHEDULE_TRIGGERED';
}
if (operation === 'queue_trigger') return 'SCHEDULE_TRIGGER_QUEUED';
if (operation === 'skip_misfire' || operation === 'skip_concurrency') {
return 'SCHEDULE_TRIGGER_SKIPPED';
}
if (operation === 'binding_repair') return 'SCHEDULE_THREAD_REPAIRED';
if (operation === 'binding_repair_failed') return 'SCHEDULE_THREAD_REPAIR_FAILED';
return undefined;
}
function parseActorType(value: string | null): ScheduleActorType | null {
if (value === 'user' || value === 'agent' || value === 'timer' || value === 'migration') {
return value;
}
return null;
}
function isTerminalRunStatus(status: ScheduleRunTrace['status']): boolean {
return status === 'succeeded' || status === 'failed' || status === 'canceled';
}
function updateEntries(input: UpdateScheduleInput): Array<[string, string | number | null]> {
const entries: Array<[string, string | number | null]> = [];
addIfOwn(entries, input, 'threadId', 'thread_id');
addIfOwn(entries, input, 'name', 'name');
addIfOwn(entries, input, 'cron', 'cron');
addIfOwn(entries, input, 'timezone', 'timezone');
addBooleanIfOwn(entries, input, 'enabled', 'enabled');
addIfOwn(entries, input, 'prompt', 'prompt');
addIfOwn(entries, input, 'promptHash', 'prompt_hash');
addIfOwn(entries, input, 'promptPreviewRedacted', 'prompt_preview_redacted');
addIfOwn(entries, input, 'profile', 'profile');
addIfOwn(entries, input, 'cwd', 'cwd');
addIfOwn(entries, input, 'canonicalCwd', 'canonical_cwd');
addIfOwn(entries, input, 'model', 'model');
addIfOwn(entries, input, 'reasoning', 'reasoning');
addIfOwn(entries, input, 'sandbox', 'sandbox');
addIfOwn(entries, input, 'timeoutMs', 'timeout_ms');
addIfOwn(entries, input, 'concurrencyPolicy', 'concurrency_policy');
addIfOwn(entries, input, 'misfirePolicy', 'misfire_policy');
addIfOwn(entries, input, 'nextRunAt', 'next_run_at');
addIfOwn(entries, input, 'lastRunAt', 'last_run_at');
addIfOwn(entries, input, 'lastRunId', 'last_run_id');
addIfOwn(entries, input, 'lastStatus', 'last_status');
addBooleanIfOwn(entries, input, 'pendingTrigger', 'pending_trigger');
return entries;
}
function addIfOwn<Key extends keyof UpdateScheduleInput>(
entries: Array<[string, string | number | null]>,
input: UpdateScheduleInput,
key: Key,
column: string
): void {
if (Object.prototype.hasOwnProperty.call(input, key)) {
entries.push([column, input[key] as string | number | null]);
}
}
function addBooleanIfOwn<Key extends keyof UpdateScheduleInput>(
entries: Array<[string, string | number | null]>,
input: UpdateScheduleInput,
key: Key,
column: string
): void {
if (Object.prototype.hasOwnProperty.call(input, key)) {
entries.push([column, booleanToInteger(input[key] as boolean)]);
}
}
function booleanToInteger(value: boolean): number {
return value ? 1 : 0;
}