SQLite for crawl pipelines.
Serialize writes without losing attempts, evidence, or recovery paths.
Sulayman Bowles · · Updated
Crawler workers finish out of order. Retries redeliver results, browser artifacts arrive after source captures, and reports read while collection continues. Shared connections, per-row commits, replacement writes, and overwritten status tables can turn that concurrency into duplicate work or lost history.
Narrow the write boundary instead. Workers emit immutable result messages; a single writer or serialized writer service validates and commits bounded batches before acknowledging them. Stable keys make redelivery safe, append-only attempts keep history, and rebuildable projections serve reporting.
In this article
How can concurrent crawl workers save results without losing the audit trail?
Keep each fetch attempt as an immutable record and serialize writes through a bounded transaction boundary. Acknowledge a result only after its transaction commits; use stable keys and idempotent writes to make retries safe. Separate attempts from the current-page projection so late renders or repeated requests do not erase history. WAL can help readers coexist with a writer, but it does not turn SQLite into a database with unrestricted concurrent writers.
Sources: SQLite transactions · SQLite UPSERT · SQLite WAL documentation
Read this alongside Build search systems you can verify.
Scope and assumptions
SQLite behavior depends on the linked library version, VFS, filesystem, durability settings, connection pattern, and workload. Official documentation should be rechecked before deployment; WAL is not a shared-database design for independent hosts or a substitute for backups.
- Writer model
- ONE AT A TIME
- Write contract
- IDEMPOTENT
- History
- ATTEMPT-LEVEL
- Primary artifact
- STABLE DUMP
Define what each row represents
Give every table one grain: crawl_run is a bounded execution; url_identity a comparison key under a normalizer version; discovery_edge an observed source relation; fetch_attempt a request attempt; response_artifact a saved payload; rule_evaluation a rule version on an input set. Mixed grains make retries and uniqueness ambiguous.
Use local surrogate IDs where helpful, but constrain domain identity explicitly. Attempts can be unique by run, URL, and sequence, or by a scheduler-generated idempotent attempt ID. Artifacts need hash plus storage namespace; link edges need source artifact and node identity to retain repeated-template context.
Keep normalized query fields alongside immutable evidence. A normalized canonical groups pages while the observed href explains it; integer statuses support queries while raw headers remain hashed artifacts. Model core relations explicitly without discarding JSON when it faithfully captures the source.
| Table | One row represents | Uniqueness anchor |
|---|---|---|
| crawl_run | One bounded execution contract | run_id |
| url_identity | One comparison key under one normalizer | normalizer_version + fetch_key |
| discovery_edge | One observed source-to-reference relation | run + source artifact + node + observed ref |
| fetch_attempt | One request attempt | attempt_id or run + URL + sequence |
| artifact | One immutable stored payload | storage namespace + content hash |
| rule_evaluation | One rule version on one input set | run + URL + rule + version + input hash |
Commit the durable result as one unit
A completed fetch can produce attempt state, response metadata, artifact references, observations, edges, and frontier transitions. Explicitly commit the records forming one durable result together, or define a resumable sequence with valid intermediate states.
Per-row transactions amplify synchronization overhead; whole-crawl transactions hold the writer, defer visibility, enlarge recovery, and make failure costly. Bound batches by messages, bytes, or time and checkpoint which messages committed.
Avoid network and filesystem waits inside transactions. Persist the artifact first, then commit its immutable reference briefly. Storage failure leaves the result retryable; commit failure permits redelivery because uniqueness prevents duplicate logical records.
Define what “durable” means before acknowledging the message. A process crash and a machine power loss are different failure tests. SQLite documents that WAL with synchronous=NORMAL can lose recent committed transactions after power loss, while FULL syncs the WAL at commit. Record that choice and the artifact store’s persistence guarantee; a committed database reference cannot make an unsynced file durable. WAL durability.
- 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.
- Attempt state
- Response metadata
- Artifact references
- Observations
- Edges
- Frontier transitions
Advance projections without replacing evidence
Redelivering a logical result should converge on the same durable state. Use a unique message or domain key with INSERT and ON CONFLICT DO NOTHING for immutable first commits. Reserve DO UPDATE for projections with explicit update policies.
Deduplication also needs consistency. If an attempt_id already exists, compare the incoming payload hash with the stored payload before accepting it as a redelivery. The same ID with different status, sequence or artifact is a conflict to quarantine. In the illustrative SQL below, DO NOTHING alone cannot establish that equality, and the projection step must run only after the message has passed that validation.
INSERT OR REPLACE can delete and recreate rows, affect foreign keys, change identity, and erase first observations. Named conflict targets and selected update columns state which collision is expected and which values may change.
Late messages must not overwrite newer terminal attempts. Guard updates with sequence, event time under a documented clock policy, or state version in the WHERE clause. Retain each attempt even when it cannot advance the projection.
Duplicate result delivery becomes a no-op, and late attempts cannot move current state backward.
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;Control the writer and long-lived readers
WAL lets readers continue while a writer appends commits, but still permits one writer at a time. Serialize worker results, keep transactions short, bound busy waits or retries, and expose queue age to apply backpressure rather than adding competing writers.
Readers retain transaction snapshots. Long reports can prevent checkpoint progress and grow WAL. Close reads promptly, paginate exports against deliberate snapshots, and monitor WAL pages and checkpoint results. SQLite’s documentation explains automatic checkpoints and the write-latency, read-performance, and durability tradeoffs.
WAL’s shared-memory design requires participating processes on the same host; its documentation warns against network filesystems. For independent machines, put write authority behind a service or use a server database. Copying the main file alone during live WAL can omit committed data: backup and archive procedures must respect journal mode.
Check the embedded library, not just a system sqlite3 command. SQLite’s WAL documentation identifies a rare WAL-reset corruption bug fixed in 3.51.3 and later, with listed backports including 3.44.6 and 3.50.7. Concurrent writing and checkpointing across connections are relevant conditions. This revision checked that advisory; the original architecture’s July verification date remains historical. SQLite advisory.
| Component | Responsibility | Metric | Failure response |
|---|---|---|---|
| workers | Produce immutable result messages | completion and redelivery count | Retry delivery, not direct database mutation |
| writer queue | Serialize and bound pending work | depth and oldest message age | Apply collection backpressure |
| writer | Validate and commit bounded batches | commit latency and busy retries | Rollback batch and retry safely |
| readers | Use short consistent snapshots | transaction duration | Cancel or move long export to snapshot copy |
| checkpointer | Keep WAL growth controlled | WAL pages and checkpoint progress | Investigate long readers and write bursts |
Test interruption, export, and restoration
Interrupt before artifact storage, between storage and commit, during a batch, between commit and acknowledgment, and during reporting. After commit but before acknowledgment, redelivery must leave one logical result and an unchanged projection. Before commit, a stored but unreferenced blob may remain; reclaim it only after a grace period and reference reconciliation so cleanup cannot race an in-flight commit.
Schedule quick or full integrity checks for the workload and important archives; check foreign keys separately where used. Back up with a supported SQLite mechanism or controlled closed/checkpointed state, not an informal copy of changing files.
Order exports explicitly and include schema version, run manifest, counts, hashes, and gaps. SQL dumps preserve relations; CSV and JSON support review. Restore the archive and compare run counts and key report queries before declaring it complete.
- 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.
Prove the database can be restored
Idempotent writes and ordered projections protect collection; short transactions and deliberate checkpoints protect operation. Complete the storage contract with integrity checks, stable exports, and a restoration that reproduces run counts and report results.
Sources
- SQLite transaction documentation
Verified Jul 19, 2026
- SQLite UPSERT documentation
Verified Jul 19, 2026
- SQLite write-ahead logging documentation
Verified Jul 19, 2026
- SQLite atomic commit documentation
Verified Jul 19, 2026
- SQLite PRAGMA integrity_check
Verified Jul 19, 2026