How OpenSEO Reconciles Stale Audits: Automatic Cleanup of Orphaned SEO Crawls

OpenSEO reconciles stale audits by running a periodic reconcileStaleAudits watchdog that queries for audits stuck in "running" status beyond a configurable cutoff time, marks them as failed within database transactions, and cleans up partial results to prevent resource leaks and erroneous credit charges.

The every-app/open-seo repository implements a defensive reconciliation system to handle SEO audits that hang or fail to complete. Understanding how OpenSEO reconciles stale audits is critical for platform operators, as it automatically detects and terminates orphaned crawl jobs while ensuring users are never charged for incomplete work.

How the Stale Audit Reconciler Works

Identifying Stale Audits with Time-Based Cutoffs

In src/server/features/audit/services/auditReconciler.ts, the reconcileStaleAudits function queries the audit table for rows where status equals running and the started_at timestamp exceeds the configured cutoff period (typically several hours). To prevent memory exhaustion during large cleanup operations, the query respects a WATCHDOG_BATCH_LIMIT that processes audits in manageable chunks.

The Core Reconciliation Logic

For each candidate audit, the system invokes reconcileRunningAudit, which performs the following steps:

1. Inspects the scratchpad: Checks if the audit's scratchpad data indicates active progress. If the crawl is still alive, the audit is skipped.
2. Cancels child jobs: Terminates any in-progress child processes, such as active page crawls, preventing orphaned work from continuing to consume credits.
3. Finalizes status: Updates the audit row with status: "failed" and populates error_message with a human-readable explanation (e.g., "audit exceeded maximum run time").
4. Records reconciliation: Sets the reconciled_at timestamp to prevent duplicate processing in subsequent watchdog cycles.

Database Safety and Transaction Locking

To avoid race conditions where an audit completes naturally while reconciliation runs simultaneously, the reconciler wraps status updates inside database transactions. The audit row is locked before any changes are written, ensuring atomic updates and preventing the system from incorrectly marking successfully completed audits as failed.

Key Design Features

The reconciliation system addresses several operational concerns:

• Race Condition Prevention: Uses transactional locking when updating audit status, ensuring final states remain consistent even under high concurrency.
• Credit Safety: Credits are only finalized when an audit reaches completed status. Stale audits are marked failed before credit calculation, preventing erroneous charges.
• Graceful Degradation: Populates the error_message field with specific failure reasons, allowing the UI to display "stale audit" badges for user visibility.
• Scalable Processing: Processes audits in batches defined by WATCHDOG_BATCH_LIMIT, keeping database load constant regardless of backlog size.
• Scratchpad Validation: Examines scratchpad data to distinguish between truly stuck audits and slow but active crawls, reducing false positives.

Implementation Examples

Running the Watchdog Manually

For testing or administrative recovery, you can invoke the reconciler directly with custom parameters:

import { reconcileStaleAudits } from "@/server/features/audit/services/auditReconciler";

// Reconcile audits older than 2 hours with a batch size of 50
await reconcileStaleAudits({
  cutoff: new Date(Date.now() - 2 * 60 * 60 * 1000),
  batchLimit: 50,
});

Server Startup Integration

The watchdog typically initializes within src/server.ts, running on a cron schedule to provide continuous protection:

import { createServer } from "node:http";
import { reconcileStaleAudits } from "@/server/features/audit/services/auditReconciler";

const server = createServer(app);
server.listen(PORT, async () => {
  console.log(`Server listening on ${PORT}`);
  
  // Start stale audit watchdog every 5 minutes
  setInterval(() => reconcileStaleAudits(), 5 * 60 * 1000);
});

Querying Post-Reconciliation Status

After reconciliation runs, verify audit states using the database schema defined in src/db/schema.ts:

import { db } from "@/db/provider";
import { auditTable } from "@/db/schema";
import { eq } from "drizzle-orm";

const audit = await db
  .select()
  .from(auditTable)
  .where(eq(auditTable.id, auditId))
  .get();

console.log(`Audit ${auditId} status: ${audit.status}`);
if (audit.status === "failed") {
  console.log(`Failure reason: ${audit.error_message}`);
  console.log(`Reconciled at: ${audit.reconciled_at}`);
}

Source Code Reference

File	Purpose	GitHub Link
src/server/features/audit/services/auditReconciler.ts	Contains reconcileStaleAudits and reconcileRunningAudit functions that implement the stale audit detection and cleanup logic.	View Source
src/server.ts	Server entry point that initializes the watchdog timer and schedules periodic reconciliation runs.	View Source
src/db/schema.ts	Defines the audit table schema including status, started_at, error_message, and reconciled_at fields.	View Source
src/server/features/audit/services/AuditService.ts	High-level audit orchestration service that triggers runs and interfaces with the reconciler for recovery scenarios.	View Source

Summary

• Automatic Detection: OpenSEO identifies stale audits by comparing started_at timestamps against a configurable cutoff window.
• Safe Termination: The reconcileRunningAudit function marks hanging audits as failed within atomic database transactions, preventing race conditions.
• Resource Protection: Child jobs are explicitly cancelled and scratchpad data is cleaned up to free system resources.
• User Protection: Audits marked as failed through reconciliation do not trigger credit charges, ensuring users only pay for completed work.
• Operational Visibility: The reconciled_at and error_message fields provide audit trails for debugging and UI notifications.

Frequently Asked Questions

What triggers the stale audit reconciliation process?

The reconciliation process triggers automatically via a setInterval timer initialized in src/server.ts, which calls reconcileStaleAudits every few minutes. It can also be invoked manually by administrators to clean up specific time windows or batch sizes.

How long does an audit need to be running before it is considered stale?

By default, audits become candidates for reconciliation after exceeding a configurable cutoff period, typically set to several hours (e.g., 6 hours). This threshold is passed as the cutoff parameter to the reconcileStaleAudits function, allowing operators to adjust sensitivity based on expected crawl durations.

Will users be charged for audits that are marked as stale and failed?

No. The reconciliation system explicitly marks stale audits as failed before any credit finalization logic executes. According to the implementation in auditReconciler.ts, credits are only charged upon successful completion, ensuring that hanging or orphaned audits never result in erroneous billing.

How does OpenSEO prevent race conditions during reconciliation?

The reconciler uses database transactions with row-level locking when updating audit status. This ensures that if an audit completes naturally at the exact moment reconciliation runs, the transaction will respect the completed state and avoid overwriting it with a failed status.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}