# AuditScratchpad Durable Object Explained: Temporary SQLite Storage for SEO Crawls

> Learn about the AuditScratchpad Durable Object, a temporary SQLite storage solution for SEO crawls. It manages URL frontiers, ensures atomic writes, and validates data before cleanup.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: internals
- Published: 2026-08-20

---

**The `AuditScratchpad` Durable Object is a SQLite-backed transient storage layer that isolates crawl state from the primary Postgres database, handling URL frontier management, atomic write operations, and final audit validation before automatic cleanup.**

In the `every-app/open-seo` codebase, the `AuditScratchpad` serves as a critical architectural component for the site audit workflow. This Cloudflare Durable Object provides dedicated, per-audit SQLite storage that keeps write-heavy crawl operations off the central database while enabling fast, atomic operations on crawl state.

## Why Use a Durable Object for Audit State?

Traditional serverless architectures struggle with long-running crawl workflows. The `AuditScratchpad` solves this by leveraging Cloudflare's **Durable Objects** with **SQLite storage** to create an isolated, durable compute instance tied to each audit ID.

The source code at [`src/server/features/audit/AuditScratchpad.ts`](https://github.com/every-app/open-seo/blob/main/src/server/features/audit/AuditScratchpad.ts) defines this pattern to achieve three goals:

- **Performance**: Local SQLite writes avoid network round trips to Postgres during the crawl loop
- **Reliability**: Built-in persistence and atomic operations survive worker restarts
- **Isolation**: Each audit gets its own storage namespace, preventing cross-contamination

## Core Responsibilities

### URL Frontier Management

The scratchpad maintains the complete crawl frontier in three states:

- **Queued URLs**: Discovered but not yet claimed for crawling
- **Leased URLs**: Claimed by a worker chunk, pending results
- **Crawled URLs**: Successfully fetched and processed

This state machine lives entirely within the Durable Object's SQLite instance, as implemented in `src/server/features/audit/AuditScratchpad.ts#L4-L9`.

### Atomic, Idempotent Operations

All methods execute synchronously inside the DO's SQLite instance, making each RPC call effectively atomic. The implementation uses `OR IGNORE` and `OR REPLACE` clauses for inserts, ensuring that retries never produce duplicate rows. See `src/server/features/audit/AuditScratchpad.ts#L16-L19` for the SQL patterns.

### Budget-Aware Link Storage

Because internal link graphs can grow without bound, the `AuditScratchpad` monitors SQLite database size and stops persisting new links once reaching a configurable budget of approximately **500 MiB**. This prevents hitting Cloudflare's per-object SQLite storage cap, as detailed at `src/server/features/audit/AuditScratchpad.ts#L78-L84`.

## Lifecycle: From Seed to Destruction

The `AuditScratchpad` follows a strict lifecycle tied to the audit workflow:

1. **Seeding**: Populated with start URL and optional sitemap URLs
2. **Active crawling**: Workers claim chunks, return results, new URLs are enqueued
3. **Finalization**: Run validation checks against complete crawl data
4. **Cleanup**: Explicit destruction on success, or automatic purge after 7 days via alarm

The cleanup alarm guarantee prevents storage leaks from failed or abandoned audits, implemented at `src/server/features/audit/AuditScratchpad.ts#L10-L14`.

## Final Audit Validation

After crawling completes, the Durable Object performs two critical checks using its local data:

### Broken Link Detection

Identifies links whose target pages returned status codes ≥ 400 and were successfully fetched. This query runs entirely in SQLite before results are returned to the main workflow. Implementation at `src/server/features/audit/AuditScratchpad.ts#L58-L71`.

### Orphan Page Detection

Finds 2xx pages with no inbound links (excluding redirects) when the link graph is complete. These represent discoverability issues in the site architecture. See `src/server/features/audit/AuditScratchpad.ts#L79-L87`.

## Working with AuditScratchpad: Code Examples

```typescript
// Obtain a typed stub for a specific audit ID
import { getAuditScratchpad } from "./AuditScratchpad";

const auditId = "12345";
const scratchpad = getAuditScratchpad(auditId);

```

### Initialize the Audit

```typescript
// Seed the start URL (called once when the audit begins)
await scratchpad.seedStart("https://example.com");

// Add URLs discovered from a sitemap (optional)
await scratchpad.seedSitemapUrls([
  "https://example.com/about",
  "https://example.com/contact",
]);

```

### Execute Crawl Chunks

```typescript
// Claim a batch of URLs for the next crawl chunk
const chunkNo = 1;
const urlsToCrawl = await scratchpad.claimChunk(chunkNo, 20);

// ...crawl the URLs, then persist results:
await scratchpad.recordBatch({
  crawledUrls: ["https://example.com/about"],
  pages: [
    {
      pageId: "p1",
      url: "https://example.com/about",
      statusCode: 200,
      fetchClass: "ok",
      redirectUrl: null,
    },
  ],
  links: [
    {
      sourcePageId: "p1",
      sourceUrl: "https://example.com/about",
      targetUrl: "https://example.com/contact",
      anchor: "Contact",
      isNofollow: false,
    },
  ],
  discovered: [{ url: "https://example.com/blog", depth: 1 }],
});

```

### Monitor and Finalize

```typescript
// Retrieve frontier statistics at any point
const stats = await scratchpad.getStats();
// stats → { attempted: 1, pending: 19, seen: 21 }

// After the crawl finishes, run the final checks
const { brokenLinks, orphanPages } = await scratchpad.runFinalizeChecks({
  startUrl: "https://example.com",
  crawlCompleted: true,
});

// Clean up the DO (called on successful audit or explicit deletion)
await scratchpad.destroy();

```

## Key Source Files

| File | Purpose |
|------|---------|
| [`src/server/features/audit/AuditScratchpad.ts`](https://github.com/every-app/open-seo/blob/main/src/server/features/audit/AuditScratchpad.ts) | Core Durable Object implementation with SQLite schema and queries |
| [`src/env.d.ts`](https://github.com/every-app/open-seo/blob/main/src/env.d.ts) | TypeScript declarations for the `AUDIT_SCRATCHPAD` namespace binding |
| [`src/server.ts`](https://github.com/every-app/open-seo/blob/main/src/server.ts) | Runtime registration of the Durable Object class |
| [`src/server/workflows/SiteAuditWorkflow.ts`](https://github.com/every-app/open-seo/blob/main/src/server/workflows/SiteAuditWorkflow.ts) | Integration showing scratchpad usage in the full audit pipeline |

## Summary

- **The `AuditScratchpad` Durable Object** provides transient, per-audit SQLite storage that isolates crawl state from Postgres
- **Atomic operations** using `OR IGNORE` / `OR REPLACE` ensure idempotency across retries
- **Budget enforcement** at ~500 MiB prevents unbounded link storage from hitting platform limits
- **Automatic cleanup** via 7-day alarm guarantees storage doesn't leak from failed audits
- **Final validation** runs broken-link and orphan-page detection using complete local data before destruction

## Frequently Asked Questions

### How does AuditScratchpad prevent duplicate data on retries?

All insert operations use SQLite's `OR IGNORE` or `OR REPLACE` clauses, making them naturally idempotent. Because methods execute atomically within the Durable Object's SQLite instance, retrying a failed RPC call produces the same final state as a single successful call.

### What happens if an audit fails and destroy() is never called?

A cleanup alarm registered at construction guarantees automatic deletion after **7 days**. This prevents storage leaks from crashed workers, deployment interruptions, or workflow timeouts. The alarm implementation is at `src/server/features/audit/AuditScratchpad.ts#L10-L14`.

### Why not store crawl state directly in Postgres?

The crawl loop generates **write-heavy, temporary data** that would overwhelm a central Postgres instance with concurrent updates. The Durable Object pattern colocates storage with compute, eliminating network latency and reducing database load while providing stronger consistency guarantees for the frontier state machine.

### Can the 500 MiB link budget be configured?

Yes, the budget is configurable via the implementation at `src/server/features/audit/AuditScratchpad.ts#L78-L84`. The default of approximately 500 MiB provides safety margin below Cloudflare's per-object SQLite limits while accommodating most site audits.