Never stop coding. Free MIT AI gateway: one endpoint, 375 providers (150+ free), 1,400+ models Kimi, Claude, GPT, Gemini, GLM, DeepSeek, MiniMax. Works with Claude Code, Codex, Cursor, OpenCode, Cline & Copilot. Quota-aware auto-fallback, RTK+Caveman compression saves 15-95% tokens, MCP/A2A, Desktop/PWA. Built by 900+ contributors
TypeScript a2aai-agentsai-gatewayanthropicclaudeclaude-codeclinecodexcopilotcursordeepseekfree-aigeminikimillm-gatewaymcpopenaiopenai-proxyqwentoken-saver
import { getDbInstance, rowToCamel, objToSnake } from "./core";
import { deleteFile, deleteFileOwnedBy } from "./files";
import { v4 as uuidv4 } from "uuid";
import { logger } from "../../../open-sse/utils/logger.ts";
const log = logger("DB_BATCHES");
function parseBatchRow(row: any): BatchRecord {
const camel = rowToCamel(row) as any;
if (camel.metadata && typeof camel.metadata === "string") {
try {
camel.metadata = JSON.parse(camel.metadata);
} catch {
camel.metadata = null;
}
}
if (camel.errors && typeof camel.errors === "string") {
try {
camel.errors = JSON.parse(camel.errors);
} catch {
camel.errors = null;
}
}
if (camel.usage && typeof camel.usage === "string") {
try {
camel.usage = JSON.parse(camel.usage);
} catch {
camel.usage = null;
}
}
// Normalize numeric date fields to ensure they are valid numbers
const coerceNum = (v: any): number | null => {
if (typeof v === "number" && Number.isFinite(v)) return v;
if (v == null) return null;
const n = Number(v);
return Number.isFinite(n) ? n : null;
};
camel.createdAt = coerceNum(camel.createdAt) ?? 0;
camel.inProgressAt = coerceNum(camel.inProgressAt);
camel.expiresAt = coerceNum(camel.expiresAt);
camel.finalizingAt = coerceNum(camel.finalizingAt);
camel.completedAt = coerceNum(camel.completedAt);
camel.failedAt = coerceNum(camel.failedAt);
camel.expiredAt = coerceNum(camel.expiredAt);
camel.cancellingAt = coerceNum(camel.cancellingAt);
camel.cancelledAt = coerceNum(camel.cancelledAt);
return camel as BatchRecord;
}
export interface BatchRecord {
id: string;
endpoint: string;
completionWindow: string;
status:
| "validating"
| "failed"
| "in_progress"
| "finalizing"
| "completed"
| "expired"
| "cancelling"
| "cancelled";
inputFileId: string;
outputFileId?: string | null;
errorFileId?: string | null;
createdAt: number;
inProgressAt?: number | null;
expiresAt?: number | null;
finalizingAt?: number | null;
completedAt?: number | null;
failedAt?: number | null;
expiredAt?: number | null;
cancellingAt?: number | null;
cancelledAt?: number | null;
requestCountsTotal: number;
requestCountsCompleted: number;
requestCountsFailed: number;
metadata?: Record<string, any> | null;
apiKeyId?: string | null;
errors?: any | null;
model?: string | null;
usage?: any | null;
outputExpiresAfterSeconds?: number | null;
outputExpiresAfterAnchor?: string | null;
}
export type BatchItemCheckpointStatus = "pending" | "processing" | "completed" | "errored";
export interface BatchItemCheckpoint {
batchId: string;
lineNumber: number;
customId: string | null;
status: BatchItemCheckpointStatus;
result: any | null;
error: any | null;
createdAt: number;
updatedAt: number;
}
function parseJsonColumn(value: unknown): any | null {
if (value == null) return null;
if (typeof value !== "string") return value;
try {
return JSON.parse(value);
} catch {
return null;
}
}
function parseBatchItemCheckpoint(row: any): BatchItemCheckpoint {
return {
batchId: row.batch_id,
lineNumber: Number(row.line_number),
customId: row.custom_id ?? null,
status: row.status,
result: parseJsonColumn(row.result_json),
error: parseJsonColumn(row.error_json),
createdAt: Number(row.created_at),
updatedAt: Number(row.updated_at),
};
}
export function createBatch(
batch: Omit<
BatchRecord,
| "id"
| "createdAt"
| "requestCountsTotal"
| "requestCountsCompleted"
| "requestCountsFailed"
| "status"
> & { status?: BatchRecord["status"] }
): BatchRecord {
const db = getDbInstance();
const id = "batch_" + uuidv4().replaceAll("-", "").substring(0, 24);
const createdAt = Math.floor(Date.now() / 1000);
const record: BatchRecord = {
...batch,
id,
createdAt,
status: batch.status || "validating",
requestCountsTotal: 0,
requestCountsCompleted: 0,
requestCountsFailed: 0,
errors: batch.errors || null,
model: batch.model || null,
usage: batch.usage || null,
outputExpiresAfterSeconds: batch.outputExpiresAfterSeconds || null,
outputExpiresAfterAnchor: batch.outputExpiresAfterAnchor || null,
};
const snakeRecord = objToSnake({
...record,
metadata: record.metadata ? JSON.stringify(record.metadata) : null,
errors: record.errors ? JSON.stringify(record.errors) : null,
usage: record.usage ? JSON.stringify(record.usage) : null,
}) as any;
const keys = Object.keys(snakeRecord);
const values = Object.values(snakeRecord);
const placeholders = keys.map(() => "?").join(", ");
db.prepare(`INSERT INTO batches (${keys.join(", ")}) VALUES (${placeholders})`).run(...values);
return record;
}
export function getBatch(id: string): BatchRecord | null {
const db = getDbInstance();
const row = db.prepare("SELECT * FROM batches WHERE id = ?").get(id);
if (!row) return null;
return parseBatchRow(row);
}
export function updateBatch(id: string, updates: Partial<BatchRecord>): boolean {
const db = getDbInstance();
const snakeUpdates = objToSnake(updates) as any;
if (snakeUpdates.metadata && typeof snakeUpdates.metadata !== "string") {
snakeUpdates.metadata = JSON.stringify(snakeUpdates.metadata);
}
if (snakeUpdates.errors && typeof snakeUpdates.errors !== "string") {
snakeUpdates.errors = JSON.stringify(snakeUpdates.errors);
}
if (snakeUpdates.usage && typeof snakeUpdates.usage !== "string") {
snakeUpdates.usage = JSON.stringify(snakeUpdates.usage);
}
const keys = Object.keys(snakeUpdates);
if (keys.length === 0) return false;
const setClause = keys.map((k) => `${k} = ?`).join(", ");
const values = Object.values(snakeUpdates);
const result = db.prepare(`UPDATE batches SET ${setClause} WHERE id = ?`).run(...values, id);
return result.changes > 0;
}
export function ensureBatchItemCheckpoints(
batchId: string,
items: Array<{ lineNumber: number; customId: string | null }>
): void {
if (items.length === 0) return;
const db = getDbInstance();
const now = Math.floor(Date.now() / 1000);
const insert = db.prepare(`
INSERT OR IGNORE INTO batch_item_checkpoints (
batch_id,
line_number,
custom_id,
status,
result_json,
error_json,
created_at,
updated_at
)
VALUES (?, ?, ?, 'pending', NULL, NULL, ?, ?)
`);
const tx = db.transaction(() => {
for (const item of items) {
insert.run(batchId, item.lineNumber, item.customId, now, now);
}
});
tx();
}
export function countBatchItemCheckpoints(batchId: string): number {
const db = getDbInstance();
const row = db
.prepare("SELECT COUNT(*) AS c FROM batch_item_checkpoints WHERE batch_id = ?")
.get(batchId) as { c: number } | undefined;
return row ? Number(row.c) : 0;
}
export function listBatchItemCheckpoints(batchId: string): BatchItemCheckpoint[] {
const db = getDbInstance();
const rows = db
.prepare(
`
SELECT batch_id, line_number, custom_id, status, result_json, error_json, created_at, updated_at
FROM batch_item_checkpoints
WHERE batch_id = ?
ORDER BY line_number ASC
`
)
.all(batchId);
return rows.map((row) => parseBatchItemCheckpoint(row));
}
export function markBatchItemProcessing(
batchId: string,
item: { lineNumber: number; customId: string | null }
): void {
const db = getDbInstance();
const now = Math.floor(Date.now() / 1000);
db.prepare(
`
INSERT INTO batch_item_checkpoints (
batch_id,
line_number,
custom_id,
status,
result_json,
error_json,
created_at,
updated_at
)
VALUES (?, ?, ?, 'processing', NULL, NULL, ?, ?)
ON CONFLICT(batch_id, line_number) DO UPDATE SET
custom_id = excluded.custom_id,
status = 'processing',
result_json = NULL,
error_json = NULL,
updated_at = excluded.updated_at
`
).run(batchId, item.lineNumber, item.customId, now, now);
}
export function markBatchItemResult(
batchId: string,
item: { lineNumber: number; customId: string | null },
result: any
): void {
const db = getDbInstance();
const now = Math.floor(Date.now() / 1000);
db.prepare(
`
UPDATE batch_item_checkpoints
SET custom_id = ?,
status = 'completed',
result_json = ?,
error_json = NULL,
updated_at = ?
WHERE batch_id = ? AND line_number = ?
`
).run(item.customId, JSON.stringify(result), now, batchId, item.lineNumber);
}
export function markBatchItemError(
batchId: string,
item: { lineNumber: number; customId: string | null },
error: any
): void {
const db = getDbInstance();
const now = Math.floor(Date.now() / 1000);
db.prepare(
`
UPDATE batch_item_checkpoints
SET custom_id = ?,
status = 'errored',
result_json = NULL,
error_json = ?,
updated_at = ?
WHERE batch_id = ? AND line_number = ?
`
).run(item.customId, JSON.stringify(error), now, batchId, item.lineNumber);
}
export function listBatches(apiKeyId?: string, limit: number = 20, after?: string): BatchRecord[] {
const db = getDbInstance();
const resolvedAfterBatch = after ? getBatch(after) : null;
// #14481 item 5/LEDGER-20: `getBatch()` applies no owner filter, so an
// `after` cursor belonging to a DIFFERENT tenant used to still resolve and
// its `created_at` was used as the pagination bound — an existence +
// timestamp oracle for another tenant's batch. When this call IS
// owner-scoped (`apiKeyId` set), treat a foreign-owned cursor exactly like
// an unknown one (ignore it) instead of trusting its timestamp.
const afterBatch =
apiKeyId && resolvedAfterBatch && resolvedAfterBatch.apiKeyId !== apiKeyId
? null
: resolvedAfterBatch;
let rows: any[];
if (apiKeyId) {
if (afterBatch) {
rows = db
.prepare(
"SELECT * FROM batches WHERE api_key_id = ? AND (created_at < ? OR (created_at = ? AND id < ?)) ORDER BY created_at DESC, id DESC LIMIT ?"
)
.all(apiKeyId, afterBatch.createdAt, afterBatch.createdAt, after, limit);
} else {
rows = db
.prepare(
"SELECT * FROM batches WHERE api_key_id = ? ORDER BY created_at DESC, id DESC LIMIT ?"
)
.all(apiKeyId, limit);
}
} else if (afterBatch) {
rows = db
.prepare(
"SELECT * FROM batches WHERE (created_at < ? OR (created_at = ? AND id < ?)) ORDER BY created_at DESC, id DESC LIMIT ?"
)
.all(afterBatch.createdAt, afterBatch.createdAt, after, limit);
} else {
rows = db.prepare("SELECT * FROM batches ORDER BY created_at DESC, id DESC LIMIT ?").all(limit);
}
return rows.map((row) => parseBatchRow(row));
}
export function countBatches(apiKeyId?: string): number {
const db = getDbInstance();
if (apiKeyId) {
const row = db
.prepare("SELECT COUNT(*) as c FROM batches WHERE api_key_id = ?")
.get(apiKeyId) as { c: number } | undefined;
return row ? Number(row.c) : 0;
} else {
const row = db.prepare("SELECT COUNT(*) as c FROM batches").get() as { c: number } | undefined;
return row ? Number(row.c) : 0;
}
}
export function getPendingBatches(): BatchRecord[] {
const db = getDbInstance();
const rows = db
.prepare(
"SELECT * FROM batches WHERE status IN ('validating', 'in_progress', 'finalizing', 'cancelling')"
)
.all();
return rows.map((row) => parseBatchRow(row));
}
export function getTerminalBatches(): BatchRecord[] {
const db = getDbInstance();
const rows = db
.prepare(
"SELECT * FROM batches WHERE status IN ('completed', 'failed', 'cancelled', 'expired') ORDER BY created_at ASC"
)
.all();
return rows.map((row) => parseBatchRow(row));
}
export function deleteBatch(id: string): boolean {
const db = getDbInstance();
const batch = getBatch(id);
if (!batch) return false;
db.prepare("DELETE FROM batch_item_checkpoints WHERE batch_id = ?").run(id);
// Soft-delete associated files (input, output, error) — but only when no
// OTHER batch still references the same file id (#13681). A file shared
// across batches (e.g. one input file reused for several batch submissions)
// must survive as long as any sibling batch still points at it.
if (batch.inputFileId && !isFileReferencedByOtherBatch(batch.inputFileId, [id])) {
try {
deleteFile(batch.inputFileId);
} catch {
/* ignore */
}
}
if (batch.outputFileId && !isFileReferencedByOtherBatch(batch.outputFileId, [id])) {
try {
deleteFile(batch.outputFileId);
} catch {
/* ignore */
}
}
if (batch.errorFileId && !isFileReferencedByOtherBatch(batch.errorFileId, [id])) {
try {
deleteFile(batch.errorFileId);
} catch {
/* ignore */
}
}
const result = db.prepare("DELETE FROM batches WHERE id = ?").run(id);
return result.changes > 0;
}
/**
* Scope of a `deleteBatchesMatching` sweep. The intent is explicit on purpose:
* a caller either names the API key whose batches it may sweep, or states
* `allTenants: true` — there is no default that widens to the whole instance.
*/
export type DeleteCompletedBatchesScope = { apiKeyId: string } | { allTenants: true };
/** Both sweep modes commit in chunks of this many batches (SEC-D, LEDGER-4). */
export const INSTANCE_SWEEP_CHUNK = 200;
/**
* Upper bound on the number of `INSTANCE_SWEEP_CHUNK`-sized chunks a single
* `deleteCompletedBatches` call may run (#13680). `sweepLoop` is a synchronous
* `for (;;)` over `better-sqlite3` — with no cap, one request could hold the
* Node.js event loop for as long as it takes to sweep every completed batch on
* the instance. 25 × 200 = 5000 batches/request is a judgment call, not a hard
* constraint; any caller with more to sweep gets `hasMore: true` back and
* resumes by calling again — resumption falls out naturally from rowid
* ordering plus delete-as-you-go (already-swept rows are gone, so the next
* SELECT picks up the next-lowest surviving rowid on its own; no cursor field
* needed).
*/
export const MAX_CHUNKS_PER_REQUEST = 25;
/**
* True when some batch OTHER than one of `excludeBatchIds` still references
* `fileId` as its input/output/error file (#13681). Used before soft-deleting
* a file to avoid nulling content a surviving batch still needs. Binds
* `fileId` three times; when `excludeBatchIds` is empty the `NOT IN (...)`
* clause is dropped entirely rather than emitted empty (`NOT IN ()` is invalid
* SQL, and getting the guard wrong there would silently match everything).
*/
export function isFileReferencedByOtherBatch(fileId: string, excludeBatchIds: string[]): boolean {
const db = getDbInstance();
if (excludeBatchIds.length === 0) {
const row = db
.prepare(
"SELECT 1 FROM batches WHERE input_file_id = ? OR output_file_id = ? OR error_file_id = ? LIMIT 1"
)
.get(fileId, fileId, fileId);
return !!row;
}
const marks = excludeBatchIds.map(() => "?").join(",");
const row = db
.prepare(
`SELECT 1 FROM batches WHERE (input_file_id = ? OR output_file_id = ? OR error_file_id = ?) AND id NOT IN (${marks}) LIMIT 1`
)
.get(fileId, fileId, fileId, ...excludeBatchIds);
return !!row;
}
/**
* Delete the batches matching `whereSql`/`params` and the files they
* reference. Shared by `deleteCompletedBatches()` (the operator-triggered
* DELETE /api/v1/batches/delete-completed route -- exact contract preserved:
* only `status = 'completed'`, no age filter) and
* `deleteTerminalBatchesOlderThan()` (the automatic cleanup sweep -- every
* terminal status, gated by age). Keeping the
* collect-ids / soft-delete-files / delete-checkpoints / delete-batches
* sequence in one place means both call sites stay in sync with the batches
* schema and with the ownership rules below.
*
* `{ apiKeyId }` additionally restricts the match to that key's own batches,
* exactly like `listBatches`/`countBatches`. `{ allTenants: true }` covers
* every matching row regardless of owner and is reserved for an authenticated
* dashboard session or an internal cron sweep with no untrusted caller — an
* ordinary inference key that reached this without its own id would otherwise
* delete every tenant's batches and null out their file contents
* (GHSA-wvxc-jp3v-5mg5). A missing/empty `apiKeyId` without `allTenants`
* throws instead of silently widening the sweep, and a scope carrying BOTH
* `apiKeyId` and `allTenants` is rejected rather than widened.
*
* Batches whose `api_key_id` IS NULL are intentionally OUT of a key-scoped sweep:
* a bulk destructive sweep must never reach records the key does not own, so
* unowned batches are only swept by `{ allTenants: true }`. The single-item routes
* apply the same rule through `canAccessOwnedRecord` in
* `src/app/api/v1/_helpers/apiKeyScope.ts` (a null owner is denied to every
* non-session caller — GHSA-2jm2-mpx8-6523).
*
* In key mode the file half is owner-scoped too: only files whose api_key_id
* is the caller's are soft-deleted; a referenced file another tenant owns (or
* an unowned one) is left intact and is not counted in deletedFiles.
*
* The file soft-deletes, the checkpoint DELETE and the batches DELETE for a
* set of batch ids run in one transaction, so a mid-sweep failure rolls that
* set back — within a chunk, no batch row is left pointing at a file whose
* content was already nulled. BOTH modes walk the matching batches in chunks
* of `INSTANCE_SWEEP_CHUNK` ids and commit that unit once per chunk
* (SEC-D; key mode since the omni-code-sec proof run, LEDGER-4/20/21): the
* SQLite write lock is held for one chunk at a time and never across chunks,
* so the lowest-privilege caller — any valid API key — cannot hold the
* instance's single writer for the length of its whole sweep. Each chunk stays
* atomic: a failure inside chunk N leaves chunks < N committed, chunk N fully
* rolled back, and rethrows. Each chunk's file soft-deletes now check whether
* some batch OUTSIDE that chunk still references the file
* (`isFileReferencedByOtherBatch`, #13681) — a file shared with a
* non-completed sibling batch, or with a completed batch a LATER chunk hasn't
* reached yet, survives this chunk. The only case that check cannot see is
* pure timing: chunk 1 commits and nulls a file, then — before chunk 2 runs —
* a NEW batch is created reusing that same file id. That race is inherent to
* per-chunk commits and stays a known, accepted edge case; everything else
* (a concurrently existing sibling, in any status, in any chunk) is now
* guarded. The returned totals sum the chunks.
*
* A single call commits at most `MAX_CHUNKS_PER_REQUEST` chunks (#13680):
* `better-sqlite3` is synchronous, so an unbounded loop would hold the event
* loop for as long as it takes to sweep the whole key's/instance's backlog.
* When the cap is hit with more rows still pending, the call returns
* `hasMore: true` instead of continuing; the caller simply calls again (the
* DELETE route on its next request, the cleanup job on its next scheduled
* run). Resumption needs no cursor: swept rows are gone, `rowid` only
* increases, so the next call's `ORDER BY rowid LIMIT ?` picks up exactly
* where the previous call left off.
*
* The loop must make progress: it remembers the first id of the previous chunk
* and throws if the next chunk starts with the same id — the DELETE removed
* nothing (e.g. a trigger ignored it), and re-selecting the same rows would
* spin forever (LEDGER-22). Rows vanishing under a concurrent deleter are fine:
* the next chunk then starts with a different id or is empty.
*
* The ids of a unit are bound as `IN (?, …)` placeholders. No statement ever
* binds more than `INSTANCE_SWEEP_CHUNK` ids in either mode, so a tenant or
* instance with tens of thousands of matching batches never hits SQLite's
* default SQLITE_MAX_VARIABLE_NUMBER (32766 since 3.32).
*/
function deleteBatchesMatching(
whereSql: string,
params: unknown[],
scope: DeleteCompletedBatchesScope
): {
deletedBatches: number;
deletedFiles: number;
hasMore: boolean;
} {
const scopeObj = scope && typeof scope === "object" ? scope : {};
const allTenants = "allTenants" in scopeObj && scopeObj.allTenants === true;
const apiKeyId = "apiKeyId" in scopeObj ? scopeObj.apiKeyId : undefined;
if (!allTenants && (typeof apiKeyId !== "string" || apiKeyId.trim() === "")) {
throw new Error("deleteBatchesMatching: apiKeyId required unless allTenants");
}
// Presence, not truthiness: `{ allTenants: true, apiKeyId: "" }` (or null) is a
// caller that named both fields and must be refused, not widened (LEDGER-18).
if (allTenants && "apiKeyId" in scopeObj) {
throw new Error("deleteBatchesMatching: apiKeyId and allTenants are mutually exclusive");
}
const db = getDbInstance();
// One consistent unit: file soft-deletes → checkpoints → batch rows for a
// given set of batch ids. Both modes run — and commit — it once per chunk of
// INSTANCE_SWEEP_CHUNK ids, so a large sweep never holds one write lock for
// the whole table (SEC-D, LEDGER-4) while each chunk stays atomic.
const sweepIds = db.transaction((ids: string[]) => {
if (ids.length === 0) return { deletedBatches: 0, deletedFiles: 0 };
const marks = ids.map(() => "?").join(",");
const rows = db
.prepare(
`SELECT input_file_id, output_file_id, error_file_id FROM batches WHERE id IN (${marks})`
)
.all(...ids) as Array<{
input_file_id: string | null;
output_file_id: string | null;
error_file_id: string | null;
}>;
const fileIds = new Set<string>();
for (const row of rows) {
if (row.input_file_id) fileIds.add(row.input_file_id);
if (row.output_file_id) fileIds.add(row.output_file_id);
if (row.error_file_id) fileIds.add(row.error_file_id);
}
let deletedFiles = 0;
for (const fid of fileIds) {
// #13681: a file referenced by a batch outside this chunk (a matching
// batch a later chunk has not reached yet, or any batch the WHERE does
// not match — non-terminal, or too young for the age-gated sweep) must
// survive this chunk's sweep. Applies to BOTH callers of
// deleteBatchesMatching: the DELETE route and the automatic cleanup.
if (isFileReferencedByOtherBatch(fid, ids)) continue;
try {
// Key mode: only the key's OWN files. A batch may reference a file
// another tenant (or nobody) owns; a bulk destructive sweep must not
// reach it (SEC-C). Instance mode keeps the unconditional soft delete.
const removed = allTenants ? deleteFile(fid) : deleteFileOwnedBy(fid, apiKeyId as string);
if (removed) deletedFiles++;
} catch (err) {
log.warn("deleteCompletedBatches: file soft-delete failed", {
fid,
err: err instanceof Error ? err.message : String(err),
});
}
}
db.prepare(`DELETE FROM batch_item_checkpoints WHERE batch_id IN (${marks})`).run(...ids);
const result = db.prepare(`DELETE FROM batches WHERE id IN (${marks})`).run(...ids);
return { deletedBatches: result.changes, deletedFiles };
});
// The one chunk loop both modes share: select the next chunk of ids, sweep
// it in its own committed transaction, sum. The only difference between the
// modes is the SELECT that produces the next chunk. No outer transaction —
// the write lock is released between chunks (LEDGER-4/20/21).
const sweepLoop = (nextIds: () => string[]) => {
const totals = { deletedBatches: 0, deletedFiles: 0, hasMore: false };
let previousFirstId: string | null = null;
let chunkCount = 0;
for (;;) {
const ids = nextIds();
if (ids.length === 0) break;
// Chunk cap (#13680): a single request commits at most
// MAX_CHUNKS_PER_REQUEST chunks. This peek doesn't delete or count
// anything — it only tells the caller whether more work remains so it
// can call again (resumption is natural: already-swept rows are gone).
if (chunkCount >= MAX_CHUNKS_PER_REQUEST) {
totals.hasMore = true;
break;
}
// Forward-progress guard (LEDGER-22): the chunk is re-selected from the
// table after each commit, so a repeated first id means the previous
// DELETE removed nothing and the loop would spin forever. A concurrent
// deleter only makes rows vanish, which yields a different first id.
if (ids[0] === previousFirstId) {
throw new Error(`deleteBatchesMatching: no progress — chunk repeated (id ${ids[0]})`);
}
previousFirstId = ids[0];
const part = sweepIds(ids);
totals.deletedBatches += part.deletedBatches;
totals.deletedFiles += part.deletedFiles;
chunkCount++;
}
return totals;
};
const toIds = (rows: unknown[]) => (rows as Array<{ id: string }>).map((r) => r.id);
if (!allTenants) {
const keyChunk = db.prepare(
`SELECT id FROM batches WHERE ${whereSql} AND api_key_id = ? ORDER BY rowid LIMIT ?`
);
return sweepLoop(() => toIds(keyChunk.all(...params, apiKeyId, INSTANCE_SWEEP_CHUNK)));
}
const nextChunk = db.prepare(`SELECT id FROM batches WHERE ${whereSql} ORDER BY rowid LIMIT ?`);
return sweepLoop(() => toIds(nextChunk.all(...params, INSTANCE_SWEEP_CHUNK)));
}
export function deleteCompletedBatches(scope: DeleteCompletedBatchesScope): {
deletedBatches: number;
deletedFiles: number;
hasMore: boolean;
} {
return deleteBatchesMatching("status = 'completed'", [], scope);
}
/**
* Automatic sweep for the daily cleanup job (see lib/db/cleanup.ts): unlike
* deleteCompletedBatches() above, this covers every terminal status -- a
* failed, cancelled, or expired batch's checkpoints are just as done as a
* completed one's, but had no cleanup path at all before this. Gated by age
* so a batch's results stay retrievable for a while after finishing, matching
* OpenAI's own Batch API retention behavior.
*
* Observed live: batch_item_checkpoints had grown to 182K rows / 5.25 GB with
* no batch ever explicitly deleted by an operator -- the manual
* delete-completed route existed, but nothing ever called it automatically,
* and it does not cover failed/cancelled/expired batches either.
*
* Always instance-wide: called only by the internal cron sweep, never by an
* untrusted API-key-authenticated caller, so it always uses the allTenants
* path rather than taking a scope of its own.
*/
export function deleteTerminalBatchesOlderThan(days: number): {
deletedBatches: number;
deletedFiles: number;
hasMore: boolean;
} {
const cutoffEpochSeconds = Math.floor(Date.now() / 1000) - days * 24 * 60 * 60;
return deleteBatchesMatching(
`status IN ('completed', 'failed', 'cancelled', 'expired')
AND COALESCE(completed_at, failed_at, cancelled_at, expired_at, created_at) < ?`,
[cutoffEpochSeconds],
{ allTenants: true }
);
}