Skip to content

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

37.4 KB
Raw Blame
/**
 * Database cleanup functions for removing old data based on retention policies.
 *
 * @module lib/db/cleanup
 */

import { rollupUsageHistoryBeforeDate } from "@/lib/usage/aggregateHistory";
import { purgeCallLogArtifactDirectory } from "@/lib/usage/callLogArtifacts";

import { getDbInstance } from "./core";
import { getUserDatabaseSettings } from "./databaseSettings";
import {
  describeReclaim,
  reclaimFreedPages,
  type ReclaimFreedPagesOptions,
  type ReclaimFreedPagesResult,
  type ReclaimStopReason,
} from "./reclaimFreedPages";
import {
  collectCallLogArtifactsBefore,
  deleteAllFromTable,
  deleteCallLogArtifacts,
  deleteFromTableBefore,
  deleteFromTableBeforeInBatches,
  tableExists,
  type DeleteByPeriodTarget,
} from "./cleanup/usagePurge";
import { ensureCompressionRunTelemetryTable } from "./compressionRunTelemetry";
import { isFeatureFlagEnabled } from "@/shared/utils/featureFlags";
import {
  pruneDbBackupsOutsideMigrationWindow,
  resolveReusedPreMigrationSha,
} from "./cleanup/backupPrune";

interface CleanupResult {
  deleted: number;
  deletedArtifacts?: number;
  errors: number;
}

function getRetentionSettings() {
  return getUserDatabaseSettings().retention;
}

/**
 * Clean up old quota_snapshots based on retention settings.
 */
export async function cleanupQuotaSnapshots(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.quotaSnapshots;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM quota_snapshots WHERE created_at < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} quota_snapshots older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning quota_snapshots:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old call_logs based on retention settings.
 */
export async function cleanupCallLogs(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.callLogs;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM call_logs WHERE timestamp < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    console.log(`[Cleanup] Deleted ${result.deleted} call_logs older than ${retentionDays} days`);
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning call_logs:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old usage_history based on retention settings.
 */
export async function cleanupUsageHistory(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.usageHistory;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();
  const cutoffDateStr = cutoffISO.split("T")[0];

  const result: CleanupResult = { deleted: 0, errors: 0 };

  // Roll up rows that are about to be deleted into daily_usage_summary so that the
  // analytics route can still surface historical data via the UNION query. The rollup
  // uses the exact same day boundary as the DELETE below, so every deleted row
  // is guaranteed to have been aggregated first.
  //
  // rollupUsageHistoryBeforeDate catches its own errors and reports them via the
  // returned result, so we inspect that rather than relying on a thrown exception.
  // If the rollup failed, abort the DELETE to avoid permanently losing raw usage data
  // that was never aggregated.
  const rollupResult = await rollupUsageHistoryBeforeDate(cutoffDateStr);
  if (rollupResult.errors > 0) {
    console.error(
      "[Cleanup] Aborting usage_history deletion because the pre-delete rollup failed."
    );
    result.errors += rollupResult.errors;
    return result;
  }

  try {
    const stmt = db.prepare("DELETE FROM usage_history WHERE timestamp < ?");
    const runResult = stmt.run(cutoffDateStr);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} usage_history older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning usage_history:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old compression_analytics based on retention settings.
 */
export async function cleanupCompressionAnalytics(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.compressionAnalytics;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM compression_analytics WHERE timestamp < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} compression_analytics older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning compression_analytics:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old compression_engine_breakdown based on retention settings (#14268).
 * Uses retention.compressionAnalytics (same retention window as compression_analytics).
 */
export async function cleanupCompressionEngineBreakdown(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.compressionAnalytics;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    if (!tableExists("compression_engine_breakdown")) return result;

    const stmt = db.prepare("DELETE FROM compression_engine_breakdown WHERE timestamp < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} compression_engine_breakdown older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning compression_engine_breakdown:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old mcp_tool_audit based on retention settings.
 */
export async function cleanupMcpAudit(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.mcpAudit;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM mcp_tool_audit WHERE created_at < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} mcp_tool_audit older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning mcp_tool_audit:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old config_audit_log based on retention settings.
 */
export async function cleanupConfigAudit(
  retentionDays = getRetentionSettings().configAudit
): Promise<CleanupResult> {
  const db = getDbInstance();
  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare(
      "DELETE FROM config_audit_log WHERE datetime(timestamp) < datetime('now', '-' || ? || ' days')"
    );
    const runResult = stmt.run(String(retentionDays));
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} config_audit_log older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning config_audit_log:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old a2a_task_events based on retention settings.
 */
export async function cleanupA2aEvents(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.a2aEvents;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM a2a_task_events WHERE created_at < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} a2a_task_events older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning a2a_task_events:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old memory_entries based on retention settings.
 */
export async function cleanupMemoryEntries(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.memoryEntries;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM memories WHERE created_at < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    // Compact FTS5 segments to reclaim space from tombstoned rows left
    // by the DELETE trigger (memory_fts_ad). Without this, orphaned FTS
    // data/docsize rows grow without bound after retention deletes.
    if (result.deleted > 0) {
      try {
        db.prepare("INSERT INTO memory_fts(memory_fts) VALUES('optimize')").run();
      } catch {
        // Best-effort; FTS compaction failure is non-fatal.
      }
    }

    console.log(
      `[Cleanup] Deleted ${result.deleted} memory_entries older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning memory_entries:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old domain_cost_history based on retention settings. (#6848)
 * The `timestamp` column stores epoch milliseconds (saveCostEntry default
 * is Date.now()), so the cutoff must be in milliseconds to match. (#9625)
 */
export async function cleanupDomainCostHistory(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.domainCostHistory;
  const cutoffEpoch = Date.now() - retentionDays * 86_400_000;

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM domain_cost_history WHERE timestamp < ?");
    const runResult = stmt.run(cutoffEpoch);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} domain_cost_history older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning domain_cost_history:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old compression_cache_stats based on retention settings. (#6848)
 * Uses `created_at` column (DATETIME string).
 */
export async function cleanupCompressionCacheStats(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.compressionCacheStats;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM compression_cache_stats WHERE created_at < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} compression_cache_stats older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning compression_cache_stats:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old xp_audit_log based on retention settings.
 */
export async function cleanupXpAuditLog(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.xpAuditLog;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM xp_audit_log WHERE created_at < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} xp_audit_log older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning xp_audit_log:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old compression_run_telemetry based on retention settings. (#6848)
 * The `timestamp` column stores epoch milliseconds (recordCompressionRun stamps
 * Date.now()), so the cutoff must be in milliseconds to match. Same unit bug as
 * domain_cost_history (#9625), which this function was missed by.
 */
export async function cleanupCompressionRunTelemetry(): Promise<CleanupResult> {
  const db = getDbInstance();
  ensureCompressionRunTelemetryTable();
  const retention = getRetentionSettings();

  const retentionDays = retention.compressionRunTelemetry;
  const cutoffEpoch = Date.now() - retentionDays * 86_400_000;

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    if (!tableExists("compression_run_telemetry")) return result;

    const stmt = db.prepare("DELETE FROM compression_run_telemetry WHERE timestamp < ?");
    const runResult = stmt.run(cutoffEpoch);
    result.deleted = runResult.changes;

    console.log(
      `[Cleanup] Deleted ${result.deleted} compression_run_telemetry older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning compression_run_telemetry:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up expired CCR blocks (#9061).
 *
 * Unlike the tables above, these rows carry their own expiry: the engine writes
 * `expires_at` from the block's TTL, so this needs no retention-days setting of its own.
 * It is the same sweep the engine does opportunistically, run on the operator's schedule
 * so the table cannot sit on rows nobody will read again.
 */
export async function cleanupCcrBlocks(): Promise<CleanupResult> {
  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const { pruneExpiredCcrBlocks } = await import("./ccrBlocks");
    result.deleted = pruneExpiredCcrBlocks(Date.now());
    console.log(`[Cleanup] Deleted ${result.deleted} expired ccr_blocks`);
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning ccr_blocks:", err);
    result.errors++;
  }

  return result;
}

const BATCH_RETENTION_DAYS_DEFAULT = 30; // matches OpenAI's own Batch API output retention window

function getBatchRetentionDays(): number {
  const raw = process.env.OMNIROUTE_BATCH_RETENTION_DAYS;
  if (!raw) return BATCH_RETENTION_DAYS_DEFAULT;
  const parsed = Number.parseInt(raw, 10);
  return Number.isInteger(parsed) && parsed >= 0 ? parsed : BATCH_RETENTION_DAYS_DEFAULT;
}

/**
 * Clean up terminal batches (completed/failed/cancelled/expired) older than the
 * retention window, along with their per-line checkpoints and referenced files.
 *
 * batch_item_checkpoints had no cleanup path at all before this: the only
 * existing sweep (deleteCompletedBatches(), the operator-triggered DELETE
 * /api/v1/batches/delete-completed route) is scoped to `status = 'completed'`
 * with no age filter, and is left untouched here -- it's a public API
 * contract, not the automatic cleanup path. Observed live: 182K checkpoint
 * rows / 5.25 GB, with no batch ever explicitly deleted by an operator.
 *
 * Gated by `BATCH_AND_FILE_AUTO_CLEANUP_ENABLED` (default off, #12999): every
 * existing install would otherwise start deleting terminal batches (and their
 * checkpoints) that today are kept forever, on the very next 6-hourly sweep.
 * Fail closed -- an operator must opt in before this sweep touches anything.
 */
export async function cleanupOldBatches(): Promise<CleanupResult> {
  const result: CleanupResult = { deleted: 0, errors: 0 };

  if (!isFeatureFlagEnabled("BATCH_AND_FILE_AUTO_CLEANUP_ENABLED")) {
    console.log(
      "[Cleanup] Batch auto-cleanup disabled (BATCH_AND_FILE_AUTO_CLEANUP_ENABLED=false); skipping."
    );
    return result;
  }

  try {
    const { deleteTerminalBatchesOlderThan } = await import("./batches");
    const retentionDays = getBatchRetentionDays();
    const { deletedBatches, hasMore } = deleteTerminalBatchesOlderThan(retentionDays);
    result.deleted = deletedBatches;
    console.log(
      `[Cleanup] Deleted ${result.deleted} terminal batches older than ${retentionDays} days` +
        (hasMore ? " (per-run cap reached; the remainder is swept on the next run)" : "")
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning old batches:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clear the content of files past their own `expires_at`.
 *
 * Like ccr_blocks, a file carries its own expiry -- this needs no separate
 * retention-days setting, just an operator-scheduled sweep, since nothing
 * previously enforced expires_at at all. Observed live: 1,874 rows / 5.19 GB
 * of uploaded file content, most long past expiry.
 *
 * Gated by `BATCH_AND_FILE_AUTO_CLEANUP_ENABLED` (default off, #12999): every
 * existing install would otherwise start clearing file content that today is
 * kept until explicitly deleted. Fail closed -- an operator must opt in.
 */
export async function cleanupExpiredFiles(): Promise<CleanupResult> {
  const result: CleanupResult = { deleted: 0, errors: 0 };

  if (!isFeatureFlagEnabled("BATCH_AND_FILE_AUTO_CLEANUP_ENABLED")) {
    console.log(
      "[Cleanup] Expired-file auto-cleanup disabled (BATCH_AND_FILE_AUTO_CLEANUP_ENABLED=false); skipping."
    );
    return result;
  }

  try {
    const { pruneExpiredFiles } = await import("./files");
    result.deleted = pruneExpiredFiles(Math.floor(Date.now() / 1000));
    console.log(`[Cleanup] Deleted ${result.deleted} expired files`);
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning expired files:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up conversation_turn_nodes older than their own retention window (#12453).
 *
 * The nodes are identity-only: the transcript view resolves each turn's display
 * content from the call_logs row `last_correlation_id` points at. Once
 * cleanupCallLogs purges that row the node can never render again, so this
 * window should not outlive `retention.callLogs` in practice — but the two
 * settings are independent knobs (`retention.conversationTurnNodes`, default
 * 30, matching callLogs' default so upgrading changes nothing until an
 * operator overrides one of them). `CALL_LOG_RETENTION_DAYS` configures the
 * separate compliance cleanup path and does not override this window.
 * Deleting an old node only affects reconnect anchors: a conversation resumed
 * after the window mints a new id, which is already the documented
 * anchor-miss behavior of resolveConversationId. `last_seen_at` is indexed
 * (migration 186, #13973 — migration 156 originally missed it). Bounded
 * batches yield between writes so an existing large table cannot park the
 * event loop for the whole cleanup pass.
 */
export async function cleanupConversationTurnNodes(): Promise<CleanupResult> {
  const retention = getRetentionSettings();

  const retentionDays = retention.conversationTurnNodes;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();
  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    result.deleted = await deleteFromTableBeforeInBatches(
      { table: "conversation_turn_nodes", column: "last_seen_at", cutoff: "iso" },
      cutoffISO
    );
    console.log(
      `[Cleanup] Deleted ${result.deleted} conversation_turn_nodes older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning conversation_turn_nodes:", err);
    result.errors++;
  }

  return result;
}

/**
 * Sweep agentic_conversations left without any conversation_turn_nodes (#12453).
 *
 * Runs after cleanupConversationTurnNodes so a root whose whole chain just
 * expired goes in the same pass. The indexed `last_seen_at` predicate bounds
 * the NOT EXISTS probe to roots that are already past the retention window.
 * Deletion is batched for the same event-loop fairness guarantee as the
 * preceding node cleanup.
 */
export async function cleanupAgenticConversations(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();
  const retentionDays = retention.conversationTurnNodes;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();
  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    if (!tableExists("agentic_conversations") || !tableExists("conversation_turn_nodes")) {
      return result;
    }
    const stmt = db.prepare(
      `DELETE FROM agentic_conversations
       WHERE rowid IN (
         SELECT rowid FROM agentic_conversations
         WHERE last_seen_at < ?
           AND NOT EXISTS (
             SELECT 1 FROM conversation_turn_nodes n
             WHERE n.conversation_id = agentic_conversations.id
           )
         LIMIT 10000
       )`
    );
    while (true) {
      const batch = stmt.run(cutoffISO).changes;
      result.deleted += batch;
      if (batch < 10_000) break;
      await new Promise<void>((resolve) => setImmediate(resolve));
    }
    console.log(
      `[Cleanup] Deleted ${result.deleted} orphaned agentic_conversations older than ${retentionDays} days`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning agentic_conversations:", err);
    result.errors++;
  }

  return result;
}

/**
 * Run all cleanup functions if auto-cleanup is enabled.
 */
export async function runAutoCleanup(): Promise<{
  totalDeleted: number;
  totalErrors: number;
  results: Record<string, CleanupResult>;
}> {
  const retention = getRetentionSettings();
  const autoCleanupEnabled = retention.autoCleanupEnabled;

  if (!autoCleanupEnabled) {
    console.log("[Cleanup] Auto-cleanup is disabled");
    return { totalDeleted: 0, totalErrors: 0, results: {} };
  }

  console.log("[Cleanup] Starting auto-cleanup...");

  const results: Record<string, CleanupResult> = {
    quotaSnapshots: await cleanupQuotaSnapshots(),
    callLogs: await cleanupCallLogs(),
    usageHistory: await cleanupUsageHistory(),
    compressionAnalytics: await cleanupCompressionAnalytics(),
    compressionEngineBreakdown: await cleanupCompressionEngineBreakdown(),
    mcpAudit: await cleanupMcpAudit(),
    configAudit: await cleanupConfigAudit(),
    a2aEvents: await cleanupA2aEvents(),
    memoryEntries: await cleanupMemoryEntries(),
    domainCostHistory: await cleanupDomainCostHistory(),
    compressionCacheStats: await cleanupCompressionCacheStats(),
    xpAuditLog: await cleanupXpAuditLog(),
    compressionRunTelemetry: await cleanupCompressionRunTelemetry(),
    proxyLogs: await cleanupProxyLogs(),
    ccrBlocks: await cleanupCcrBlocks(),
    conversationTurnNodes: await cleanupConversationTurnNodes(),
    agenticConversations: await cleanupAgenticConversations(),
    oldBatches: await cleanupOldBatches(),
    expiredFiles: await cleanupExpiredFiles(),
  };

  const totalDeleted = Object.values(results).reduce((sum, r) => sum + r.deleted, 0);
  const totalErrors = Object.values(results).reduce((sum, r) => sum + r.errors, 0);

  console.log(`[Cleanup] Auto-cleanup complete: ${totalDeleted} deleted, ${totalErrors} errors`);

  return { totalDeleted, totalErrors, results };
}

/**
 * Purge ALL quota_snapshots immediately (no retention check).
 */
export async function purgeQuotaSnapshots(): Promise<CleanupResult> {
  const db = getDbInstance();
  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM quota_snapshots");
    const runResult = stmt.run();
    result.deleted = runResult.changes;

    console.log(`[Cleanup] Purged ${result.deleted} quota_snapshots`);
  } catch (err: unknown) {
    console.error("[Cleanup] Error purging quota_snapshots:", err);
    result.errors++;
  }

  return result;
}

/**
 * Purge ALL call_logs immediately (no retention check).
 */
export async function purgeCallLogs(): Promise<CleanupResult> {
  const db = getDbInstance();
  const result: CleanupResult = { deleted: 0, deletedArtifacts: 0, errors: 0 };

  try {
    const runResult = db.prepare("DELETE FROM call_logs").run();
    result.deleted = runResult.changes;

    console.log(`[Cleanup] Purged ${result.deleted} call_logs`);
  } catch (err: unknown) {
    console.error("[Cleanup] Error purging call_logs:", err);
    result.errors++;
  }

  const artifactResult = purgeCallLogArtifactDirectory();
  result.deletedArtifacts = artifactResult.deletedArtifacts;
  result.errors += artifactResult.errors;

  if (artifactResult.errors === 0) {
    console.log(`[Cleanup] Purged ${result.deletedArtifacts} call log artifact(s)`);
  }

  return result;
}

/**
 * Purge ALL request_detail_logs immediately (no retention check).
 */
export async function purgeDetailedLogs(): Promise<CleanupResult> {
  const db = getDbInstance();
  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM request_detail_logs");
    const runResult = stmt.run();
    result.deleted = runResult.changes;

    console.log(`[Cleanup] Purged ${result.deleted} request_detail_logs`);
  } catch (err: unknown) {
    console.error("[Cleanup] Error purging request_detail_logs:", err);
    result.errors++;
  }

  return result;
}

/**
 * Whitelist of periods accepted by {@link resetUsageHistory}. `"all"` wipes
 * every row; any other value deletes rows strictly older than `now - period`.
 */
export const RESET_USAGE_HISTORY_PERIODS = [
  "5m",
  "1h",
  "3h",
  "6h",
  "12h",
  "1d",
  "7d",
  "30d",
  "all",
] as const;

export type ResetUsageHistoryPeriod = (typeof RESET_USAGE_HISTORY_PERIODS)[number];

type TimedResetUsageHistoryPeriod = Exclude<ResetUsageHistoryPeriod, "all">;

const RESET_USAGE_HISTORY_PERIOD_MS: Record<TimedResetUsageHistoryPeriod, number> = {
  "5m": 5 * 60 * 1000,
  "1h": 60 * 60 * 1000,
  "3h": 3 * 60 * 60 * 1000,
  "6h": 6 * 60 * 60 * 1000,
  "12h": 12 * 60 * 60 * 1000,
  "1d": 24 * 60 * 60 * 1000,
  "7d": 7 * 24 * 60 * 60 * 1000,
  "30d": 30 * 24 * 60 * 60 * 1000,
};

export interface ResetUsageHistoryResult extends CleanupResult {
  deletedUsageHistory: number;
  deletedDailySummary: number;
  deletedHourlySummary: number;
  deletedCallLogs: number;
  deletedCallLogArtifacts: number;
  deletedRequestDetailLogs: number;
  deletedProxyLogs: number;
  deletedRelayLogs: number;
  deletedCompressionAnalytics: number;
  deletedCompressionEngineBreakdown: number;
  deletedCompressionRunTelemetry: number;
  deletedRoutingDecisions: number;
  deletedQuotaConsumption: number;
  /**
   * Always 0. `token_ledger` is the transfer record behind token balances and
   * idempotency keys, not usage analytics, so a usage reset never deletes it.
   * The field stays so the response shape of the purge API does not change.
   */
  deletedTokenLedger: number;
  deletedConversationTurnNodes: number;
  deletedAgenticConversations: number;
}

function isResetUsageHistoryPeriod(period: string): period is ResetUsageHistoryPeriod {
  return (RESET_USAGE_HISTORY_PERIODS as readonly string[]).includes(period);
}

/**
 * On-demand, period-scoped reset of usage analytics data (`usage_history`,
 * `daily_usage_summary`, `hourly_usage_summary`).
 *
 * Unlike {@link cleanupUsageHistory} (retention-based background cleanup,
 * which rolls up rows into `daily_usage_summary` before deleting them), this
 * is a destructive user-triggered reset — it intentionally does NOT roll up
 * first, since the whole point is to wipe the data the user selected.
 *
 * @param period - One of {@link RESET_USAGE_HISTORY_PERIODS}. `"all"` wipes
 *   every reset target, including conversation identity metadata; any other
 *   value deletes only time-scoped usage/log rows older than `now - period`.
 *   Throws on an invalid period.
 */
const RESET_TARGETS: Array<
  DeleteByPeriodTarget & { resultKey: keyof ResetUsageHistoryResult; allOnly?: boolean }
> = [
  { table: "usage_history", column: "timestamp", cutoff: "iso", resultKey: "deletedUsageHistory" },
  {
    table: "daily_usage_summary",
    column: "date",
    cutoff: "date",
    resultKey: "deletedDailySummary",
  },
  {
    table: "hourly_usage_summary",
    column: "date_hour",
    cutoff: "dateHour",
    resultKey: "deletedHourlySummary",
  },
  { table: "call_logs", column: "timestamp", cutoff: "iso", resultKey: "deletedCallLogs" },
  {
    table: "request_detail_logs",
    column: "timestamp",
    cutoff: "iso",
    resultKey: "deletedRequestDetailLogs",
  },
  { table: "proxy_logs", column: "timestamp", cutoff: "iso", resultKey: "deletedProxyLogs" },
  {
    table: "relay_logs",
    column: "created_at",
    cutoff: "epochSeconds",
    resultKey: "deletedRelayLogs",
  },
  {
    table: "compression_analytics",
    column: "timestamp",
    cutoff: "iso",
    resultKey: "deletedCompressionAnalytics",
  },
  {
    table: "compression_engine_breakdown",
    column: "timestamp",
    cutoff: "iso",
    resultKey: "deletedCompressionEngineBreakdown",
  },
  {
    table: "compression_run_telemetry",
    column: "timestamp",
    cutoff: "epochMs",
    resultKey: "deletedCompressionRunTelemetry",
  },
  {
    table: "routing_decisions",
    column: "created_at",
    cutoff: "iso",
    resultKey: "deletedRoutingDecisions",
  },
  {
    table: "quota_consumption",
    column: "updated_at",
    cutoff: "epochMs",
    resultKey: "deletedQuotaConsumption",
  },
  {
    table: "conversation_turn_nodes",
    column: "last_seen_at",
    cutoff: "iso",
    resultKey: "deletedConversationTurnNodes",
    allOnly: true,
  },
  {
    table: "agentic_conversations",
    column: "last_seen_at",
    cutoff: "iso",
    resultKey: "deletedAgenticConversations",
    allOnly: true,
  },
];

export async function resetUsageHistory(period: string): Promise<ResetUsageHistoryResult> {
  if (!isResetUsageHistoryPeriod(period)) {
    throw new Error(`Invalid reset period: ${period}`);
  }

  const db = getDbInstance();
  ensureCompressionRunTelemetryTable();
  const result: ResetUsageHistoryResult = {
    deleted: 0,
    deletedUsageHistory: 0,
    deletedDailySummary: 0,
    deletedHourlySummary: 0,
    deletedCallLogs: 0,
    deletedCallLogArtifacts: 0,
    deletedRequestDetailLogs: 0,
    deletedProxyLogs: 0,
    deletedRelayLogs: 0,
    deletedCompressionAnalytics: 0,
    deletedCompressionEngineBreakdown: 0,
    deletedCompressionRunTelemetry: 0,
    deletedRoutingDecisions: 0,
    deletedQuotaConsumption: 0,
    deletedTokenLedger: 0,
    deletedConversationTurnNodes: 0,
    deletedAgenticConversations: 0,
    deletedArtifacts: 0,
    errors: 0,
  };

  try {
    let artifactsToDelete: string[] = [];

    const runReset = db.transaction(() => {
      if (period === "all") {
        for (const target of RESET_TARGETS) {
          (result[target.resultKey] as number) = deleteAllFromTable(target.table);
        }
        return;
      }

      const cutoffIso = new Date(Date.now() - RESET_USAGE_HISTORY_PERIOD_MS[period]).toISOString();
      artifactsToDelete = collectCallLogArtifactsBefore(cutoffIso);
      for (const target of RESET_TARGETS) {
        if (target.allOnly) continue;
        (result[target.resultKey] as number) = deleteFromTableBefore(target, cutoffIso);
      }
    });

    runReset();

    let artifactResult: { deletedArtifacts: number; errors: number };
    if (period === "all") {
      artifactResult = purgeCallLogArtifactDirectory();
    } else {
      artifactResult = deleteCallLogArtifacts(artifactsToDelete);
    }
    result.deletedCallLogArtifacts = artifactResult.deletedArtifacts;
    result.deletedArtifacts = artifactResult.deletedArtifacts;
    result.errors += artifactResult.errors;

    result.deleted = RESET_TARGETS.reduce((sum, t) => sum + (result[t.resultKey] as number), 0);

    console.log(
      `[Cleanup] Reset usage/log data (period=${period}): ${result.deleted} row(s), ` +
        `${result.deletedCallLogArtifacts} call log artifact(s)`
    );
  } catch (err: unknown) {
    console.error("[Cleanup] Error resetting usage history:", err);
    result.errors++;
  }

  return result;
}

/**
 * Clean up old proxy_logs based on retention settings.
 * Uses the same retention period as call_logs (30 days default).
 */
export async function cleanupProxyLogs(): Promise<CleanupResult> {
  const db = getDbInstance();
  const retention = getRetentionSettings();

  const retentionDays = retention.callLogs;
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
  const cutoffISO = cutoffDate.toISOString();

  const result: CleanupResult = { deleted: 0, errors: 0 };

  try {
    const stmt = db.prepare("DELETE FROM proxy_logs WHERE timestamp < ?");
    const runResult = stmt.run(cutoffISO);
    result.deleted = runResult.changes;

    console.log(`[Cleanup] Deleted ${result.deleted} proxy_logs older than ${retentionDays} days`);
  } catch (err: unknown) {
    console.error("[Cleanup] Error cleaning proxy_logs:", err);
    result.errors++;
  }

  return result;
}

// Post-cleanup space reclamation lives in its own module (#12821, kept out of
// this file to stay under the file-size cap) — re-exported for callers/tests.
export {
  reclaimFreedPages,
  type ReclaimFreedPagesOptions,
  type ReclaimFreedPagesResult,
  type ReclaimStopReason,
};
// Backup-directory pruning for the scheduled pass lives in ./cleanup/backupPrune
// (same file-size reason) — re-exported so tests drive the pass's own symbols.
export { pruneDbBackupsOutsideMigrationWindow, resolveReusedPreMigrationSha };

// ──────────────── Background Cleanup Scheduler ────────────────

const CLEANUP_INTERVAL_MS = 6 * 60 * 60 * 1000; // 6 hours
let _cleanupSchedulerTimer: ReturnType<typeof setInterval> | null = null;

/**
 * One scheduled pass: retention cleanup (`runAutoCleanup` already covers
 * proxy_logs), then incremental space reclamation. Exported so tests can drive
 * the exact code path the timers run.
 */
export async function runScheduledCleanupPass(phase: "startup" | "periodic"): Promise<void> {
  const label = phase === "startup" ? "Startup" : "Periodic";
  const result = await runAutoCleanup();
  if (result.totalDeleted > 0) {
    console.log(`[Cleanup] ${label} cleanup freed ${result.totalDeleted} rows.`);
  }

  pruneDbBackupsOutsideMigrationWindow();

  // Always run: it also drains pages left over from a previous capped pass or
  // from deletes made outside this scheduler. Costs a few PRAGMA reads when idle.
  try {
    const reclaim = await reclaimFreedPages();
    if (reclaim.stopReason === "error") {
      console.error(
        `[Cleanup] Space reclamation after ${phase} cleanup stopped early ` +
          `(${describeReclaim(reclaim)}): ${reclaim.error}`
      );
    } else if (reclaim.mode !== "skipped") {
      console.log(
        `[Cleanup] Space reclamation after ${phase} cleanup: ${describeReclaim(reclaim)}.`
      );
    }
  } catch (reclaimErr) {
    console.error(`[Cleanup] Space reclamation after ${phase} cleanup failed:`, reclaimErr);
  }
}
/**
 * Start the background cleanup scheduler. Runs cleanup on startup and then
 * every 6 hours, then reclaims freed pages incrementally (never a blocking
 * full VACUUM — see the reclamation section above and #12821).
 *
 * Without the cleanup itself, tables grow unboundedly (compression_analytics
 * 600K+ rows, usage_history 250K+ rows) causing 1.4GB+ SQLite files and
 * 3-8GB RSS from better-sqlite3 memory mapping.
 */
export function startCleanupScheduler(): void {
  if (_cleanupSchedulerTimer) return;

  // Run cleanup 30s after startup (let the server initialize first).
  setTimeout(async () => {
    try {
      await runScheduledCleanupPass("startup");
    } catch (err) {
      console.error("[Cleanup] Startup cleanup failed:", err);
    }
  }, 30_000);

  // Schedule periodic cleanup every 6 hours.
  _cleanupSchedulerTimer = setInterval(async () => {
    try {
      await runScheduledCleanupPass("periodic");
    } catch (err) {
      console.error("[Cleanup] Periodic cleanup failed:", err);
    }
  }, CLEANUP_INTERVAL_MS);

  // Don't keep the process alive solely for cleanup.
  if (_cleanupSchedulerTimer && typeof _cleanupSchedulerTimer.unref === "function") {
    _cleanupSchedulerTimer.unref();
  }

  console.log("[Cleanup] Background cleanup scheduler started (every 6 hours).");
}

/**
 * Stop the background cleanup scheduler (for tests).
 */
export function stopCleanupScheduler(): void {
  if (_cleanupSchedulerTimer) {
    clearInterval(_cleanupSchedulerTimer);
    _cleanupSchedulerTimer = null;
  }
}