One SQLite database per user, and one per crawl

Four kinds of database, with different jobs and different lifetimes.

• System. Accounts, sessions, and the registry that maps a scan token to the file that holds it. Queried before anything else can be loaded.
• Write models, one per user. An append-only event stream per aggregate: the account, its schedules, its websites, its affiliate record. Physical isolation, so one user's history is a file no query for another user can reach.
• Scan databases, one per crawl. The scan's event stream, the content-addressed bytes of every page and asset fetched, and the projection tables the checks query.
• The read model. One shared database of projections: lists, dashboards, rankings, and the queues the background worker polls.

Commands append events and never update in place. Projections are derived and disposable: the scan projections can be rebuilt from the events in the same file, which has already paid for itself twice when a check needed a column nobody had projected.

A crawl is a natural unit of work with a beginning, an end, and no relationship to any other crawl. Giving it its own database made several problems disappear at once.

The export is the original. The download is a consistent snapshot of the working file, not a serialisation written by a code path that only runs when somebody clicks. There is no export to keep in step with the schema, because there is no export.

Deleting is a deletion. Removing a scan is removing a file. No cascade, no orphan rows, no tombstones, and nothing to prove to yourself about what is left behind.

Blast radius. A corrupt scan file breaks one report. The rest of the site does not notice, and the page that would have rendered it logs the failure and shows what it still has.

Storage that scales down. Page and asset bodies are content-addressed by hash, so a logo on every page is stored once. Crawls of sites that reuse assets heavily are much smaller than the sum of their fetches.

The background worker does not open any database. It polls the application for work and posts results back with an API key:

GET  /api/queries/scans-to-crawl      -> scans wanting attention, least recently served first
GET  /api/queries/scan-frontier       -> the next batch of URLs for one scan
POST /api/commands/record-fetch-result
POST /api/commands/record-fetch-failed
POST /api/commands/complete-scan

Every queue is a read-model table treated as a to-do list: scans to crawl, notifications to send, fix plans to write, security reviews to generate, schedules that are due, recordings to purge. Each one is a projection, so the queue cannot drift from the state it is derived from: re-project and the queue is correct again. Settling something already settled re-projects instead of failing, which is what clears a stale row.

Ordering is least-recently-served rather than first-in-first-out, so a large crawl cannot starve the small ones behind it. The frontier lives inside the scan's own database, so restarting the worker resumes mid-crawl with no coordination and no lost URLs.

The nice property is that the worker is stateless and replaceable. It can be restarted, moved to another machine or run twice while a deployment rolls, and the only thing that decides what happens next is the read model.

Migrations are the hard part. A versioned list of migrations applied by index is fine until a counter drifts, and then a migration is silently skipped on the one database that matters. That happened here: production's read-model version ran one ahead of the list, and the next appended migration never ran, which took out every scan command until it was understood. The rule since: migrations that create tables contain only CREATE ... IF NOT EXISTS and are re-run on every open, and new columns are applied by inspecting the table rather than by counting.

Cross-cutting queries need the read model. Anything that spans users or scans, such as the ranking tables, can only come from projections. If a question was not projected, it cannot be asked without opening thousands of files, and the fix is a new projection and a back-fill, not a clever join.

Schema drift lives in many places. Old scan files outlive the code that wrote them, so opening one upgrades it: add the columns it is missing, leave the rest alone. Every scan database on disk is a different age.

Backups are file backups. That is simpler than it sounds for the write models, and awkward for a crawl that is being written to while it is copied, which is what snapshotting is for.

Reading SQL schema files from disk at runtime does not survive a standalone build, because the file layout that __dirname resolves against is not the one you built from. Schemas live in code here for that reason.

An instrumentation hook is compiled for the edge runtime as well as Node when middleware exists, so anything that reaches SQLite from it has to sit inside a runtime check that the bundler can drop; an early return is not enough, because the import is still reached during bundling.

Synchronous SQLite in server components is a good fit: better-sqlite3 in a render is a function call, and the component is already running on the server. Most of the pages on this site read from the read model synchronously and render.

For a product whose deliverable is a database, yes. The isolation was a nice-to-have that turned into the feature, and the event stream has paid for itself every time a report needed reshaping from history nobody had thought to keep in a column.

For a product whose deliverable is a dashboard, probably not. One Postgres database and a decent schema would be less work and fewer sharp edges. The question to ask is whether any single file in the system is something a user would want to hold.