SQLite for Crawl Pipelines: Idempotency, WAL, and Bounded Concurrency

Direct answer: SQLite web crawler

SQLite can support a bounded web crawler when URL identity is unique, writes use short transactions and idempotent upserts, one process coordinates write pressure, WAL behavior is measured, leases are recoverable, and artifacts remain outside oversized database rows.

Original research artifact

A normalized crawl schema, idempotent UPSERT, transaction pattern, WAL checklist, and migration boundary table.

What this page adds

Specify the concurrency and recovery envelope where SQLite is useful instead of arguing that it is either universally sufficient or never production-ready.

Related research

• SEO audit provenance
• crawl frontier design
• AI agent evaluation traces

Define table grain and identity before tuning the database

Each table needs one sentence that defines a row. A crawl_run row is one bounded execution. A url_identity row is one comparison identity under a normalizer version. A discovery_edge row is one observed relation from a source artifact. A fetch_attempt row is one network attempt. A response_artifact row identifies one saved payload. A rule_evaluation row is one rule version applied to one input set. If a table mixes several grains, uniqueness and retries become ambiguous.

Use surrogate IDs for local joins where helpful, but enforce domain uniqueness with explicit constraints. A fetch attempt might be unique on run, URL identity, and attempt number or on a generated attempt ID whose creation is idempotent at the scheduler. An artifact can use a content hash plus storage namespace. A link edge needs source artifact and node identity so repeated template edges remain explainable.

Store normalized fields for queries and immutable raw values for evidence. The normalized canonical target supports grouping; the observed href supports debugging. The parsed status is an integer; response headers can also remain as a hashed raw artifact. Do not rely on a JSON blob for every core relation, and do not discard the JSON when it is the faithful captured form.

Make the transaction match the durable unit of work

SQLite automatically starts transactions, but a crawl writer should define them explicitly. One completed fetch commonly creates an attempt update, response metadata, artifact reference, extracted observations, discovered edges, and frontier transitions. If those records describe one durable result, commit them together or design a resumable sequence whose intermediate states are valid.

Avoid one transaction per individual row; it amplifies synchronization overhead. Avoid unbounded transactions for an entire crawl; they hold the writer, grow recovery work, delay visibility, and make failure expensive. Batch by a bounded number of result messages, bytes, or time while retaining a checkpoint that identifies which messages were committed.

Keep database transactions free of network and filesystem work when possible. Store the artifact durably first, then write its immutable reference in a short transaction. If artifact storage fails, the result remains retryable. If the database commit fails, redelivery can safely repeat the message because uniqueness keys prevent duplicate logical records.

• Begin and end transactions in the writer, not inside unrelated helper functions.
• Keep every transaction bounded by records, bytes, and wall time.
• Do not wait on HTTP, browser, or remote object storage while holding the writer.
• Acknowledge a worker result only after the durable transaction commits.
• Record the result-message ID used for deduplication.
• Retry SQLITE_BUSY with a bounded policy rather than an infinite loop.

Use upserts to converge, not to erase history

Idempotency means that redelivering the same logical result produces the same durable state. Define a unique key for the message or domain row and use INSERT with ON CONFLICT DO NOTHING when the first committed record must remain immutable. Use DO UPDATE only for a current projection whose update policy is explicit.

Avoid INSERT OR REPLACE for evidence records. Replace behavior can delete and recreate a row, interact with foreign keys, change row identity, and overwrite the first observation. An upsert with a named conflict target and selected columns states which conflict is expected and which values may change.

Guard current-state updates with monotonic conditions. A late attempt should not overwrite a newer terminal attempt merely because its message arrived last. Compare attempt sequence, event time under a documented clock policy, or explicit state version in the update WHERE clause. Keep every attempt row even when the projection rejects it.

BEGIN IMMEDIATE;

INSERT INTO fetch_attempt (attempt_id, run_id, url_id, sequence, status, artifact_id)
VALUES (:attempt_id, :run_id, :url_id, :sequence, :status, :artifact_id)
ON CONFLICT(attempt_id) DO NOTHING;

INSERT INTO url_current (run_id, url_id, sequence, status, attempt_id)
VALUES (:run_id, :url_id, :sequence, :status, :attempt_id)
ON CONFLICT(run_id, url_id) DO UPDATE SET
  sequence = excluded.sequence,
  status = excluded.status,
  attempt_id = excluded.attempt_id
WHERE excluded.sequence > url_current.sequence;

COMMIT;

Use WAL with an explicit concurrency and checkpoint model

SQLite WAL mode lets readers continue while a writer appends committed changes, but there is still one writer at a time. That is a feature to design around, not a limit to disguise with many competing write connections. Funnel worker results through a writer queue, keep transactions short, set a bounded busy timeout or retry policy, and expose queue age as backpressure.

Readers see a consistent snapshot for their transaction. A long-running report can therefore prevent checkpoint progress and allow the WAL file to grow. Close read transactions promptly, paginate large exports against a deliberate snapshot, and monitor WAL pages plus checkpoint results. The official WAL documentation describes automatic checkpoint behavior and the tradeoff between write latency, read performance, and durability settings.

WAL requires processes sharing a database to operate on the same host because of its shared-memory design; the official documentation warns against using it across a network filesystem. If independent machines need concurrent writes, move the write authority behind a service or use a server database. Copying only the main database while live WAL state exists can also lose committed data, so backup and archive workflows must understand the journal mode.

Operate recovery, integrity, and export as first-class paths

Test interruption at every boundary: before artifact storage, after artifact storage but before database commit, during a batch, after commit but before acknowledgment, and during reporting. The expected result should be either no committed logical change or one complete idempotent result. A recovery test is stronger evidence than assuming atomic commit from ordinary runs.

Run quick or full integrity checks at a cadence appropriate to the workload and before important archives. Verify foreign-key consistency separately when foreign keys are part of the contract. Back up through a supported SQLite backup mechanism or a controlled closed/checkpointed state rather than copying an actively changing file set casually.

Produce stable exports with explicit ordering, schema version, run manifest, row counts, hashes, and gap states. A SQL dump can preserve relational content; CSV and JSON can serve review workflows. Validate that a restored database reproduces the same run counts and key report queries. The archive is complete only when restoration has been exercised.

• Pin or inventory the actual SQLite library version used in production.
• Review current official advisories before relying on WAL behavior.
• Exercise crash and duplicate-delivery tests against the writer.
• Monitor queue age, commit latency, WAL size, and checkpoint progress.
• Run integrity and foreign-key checks before durable archive.
• Restore a backup and compare deterministic report hashes.