# SQLite, JS/WASM VFS, ATTACH, and Cursor state

> Implementation status: the official SQLite WASM dependency, dedicated Worker, backend registry, attached schemas, SQLite-native read-only authorizer, transactional outbox, Chromium tests, and benchmark harness now ship as an architecture slice. OPFS remains browser-capability-dependent; Sessions and virtual tables remain feature-detected experiments. See [SQLite Body and Lua Programs](sqlite-body-and-lua-programs.md).

SQLite should be the boring local machine underneath Cursors, not another wire protocol wearing a database costume.

## The three truths

| Role | Examples | Recovery expectation |
| --- | --- | --- |
| portable truth | signed cursor records, grants, receipts, immutable encrypted blocks | independently hash/signature verifiable |
| operational truth | mailbox queue, nonce replay journal, quotas, local cache, settings | transactionally consistent for one body/service |
| derived view | search index, analytics, materialized timeline, cockpit cache | safe to rebuild |

## Browser SQLite: use the real database

For browser bodies, prefer the official SQLite JS/WASM build in a Worker and an OPFS-backed VFS when available. Modern SQLite also offers `opfs-wl` and `opfs-sahpool` variants with different locking/portability tradeoffs. Cursors should probe capabilities and choose a supported VFS rather than maintaining its own pretend filesystem unless a real requirement appears.

A useful local layout is several small databases with explicit responsibilities:

```text
self.db
  identity metadata
  local policy
  device/body state

mailbox.db
  inbox
  outbox
  receipts
  tombstones
  sync cursors

world.db
  assertions
  interests
  leases
  continuation indexes

cache.db
  resource descriptors
  immutable-block presence
  derived projections
```

Then compose them using ordinary SQLite:

```sql
ATTACH DATABASE 'mailbox.db' AS mailbox;
ATTACH DATABASE 'world.db'   AS world;
ATTACH DATABASE 'cache.db'   AS cache;
```

A local body can query across these schemas without inventing an application-level join protocol. Cross-file transactions provide SQLite rollback semantics, but a backend must separately prove durable crash atomicity before Cursors relies on it. Until then, semantic intent and its outbox row stay in the same attached database.

`ATTACH` is especially attractive for Cursors because a curve or app can mount a database view without requiring every table to live forever in one giant file. Detaching a derived/cache database must not destroy signed history.

## Do not ATTACH a remote Durable Object database

Cloudflare Durable Object SQLite is an embedded database behind a Durable Object's storage API. It is **not** a remote SQLite file that a browser VFS should mount or page-sync.

Trying to make local SQLite and DO SQLite share database pages would couple together:

- SQLite version and page format;
- WAL/locking behavior;
- browser VFS semantics;
- Cloudflare's storage implementation;
- partial failure and network retries.

That is dramatically more fragile than syncing the mailbox semantics Cursors already understands.

The cleaner boundary is:

```text
local SQLite transaction
      │
      ▼
mailbox delta / envelope / tombstone / receipt
      │
      ▼
Cursors exchange
      │
      ├── LAN / peer
      ├── Cap'n Web HTTP/WebSocket
      └── Cloudflare mailbox DO
      │
      ▼
remote DO SQLite transaction
```

Both ends use SQLite because SQLite is good. They do not need to pretend they are one database file.

## Sync rows, not pages

Each replicated mailbox row should have a stable semantic ID and enough causal metadata to make replay idempotent. The local SQLite schema can maintain a compact sync journal:

```sql
CREATE TABLE sync_log (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  object_id TEXT NOT NULL,
  family TEXT NOT NULL,
  operation TEXT NOT NULL,
  causal_parent TEXT,
  created_at INTEGER NOT NULL,
  remote_receipt TEXT
);

CREATE UNIQUE INDEX sync_object_operation
ON sync_log(object_id, operation);
```

A sync cycle is then approximately:

```text
BEGIN IMMEDIATE
  commit local mailbox changes
  append sync_log rows
COMMIT

exchange unsatisfied log rows
receive remote rows + tombstones

BEGIN IMMEDIATE
  INSERT ... ON CONFLICT ...
  record receipts / high-water marks
COMMIT
```

This works through peer, LAN, HTTP, Cap'n Web, or Durable Object carriers and remains understandable when one of them disappears.

## ATTACH as a sync staging tool

`ATTACH` becomes useful again when a body receives a complete SQLite snapshot or checkpoint as an immutable local file. Do not merge it blindly into the live database. Attach it as a staging schema:

```sql
ATTACH DATABASE 'incoming-checkpoint.db' AS incoming;

SELECT ... FROM incoming.mailbox
EXCEPT
SELECT ... FROM main.mailbox;
```

Validate signed heads, tombstones, capability context, and conflict policy before copying accepted rows into the live database. This is much safer than replacing the live DB file because somebody sent a newer timestamp.

## VFS responsibility

The VFS should solve local persistence and local block access, not distributed consensus.

A VFS may map an immutable attached snapshot onto content-addressed blocks:

```text
SQLite read
  → VFS file/range
  → local block cache
  → peer / HTTP / swarm resolver on miss
  → verify hash
  → return bytes
```

But writes belong to a writable local database/journal first. Publishing is a separate Cursors operation producing a checkpoint, successor cursor, and receipt.

This keeps the failure boundary sane:

```text
SQLite transaction committed locally
        !=
remote mailbox synchronized
        !=
remote effect accepted
```

## Multi-tab behavior

OPFS locking matters. A dedicated browser Worker should own each writable SQLite database and its page should communicate with that owner through MessagePort or eventually Cap'n Web over MessagePort.

Do not let several tabs independently open the same writable OPFS database and then try to fix contention in mailbox logic. Database locking already has a job description.

The shipped ownership is intentionally smaller than a cross-tab service:

```text
Page A → dedicated Worker A → OPFS VFS → mailbox.db
Page B → dedicated Worker B → its own explicitly selected body
```

One writable Worker per page gives each body one place to serialize writes and emit sync-log events. Cross-tab singleton ownership requires a later SharedWorker, Web Lock leader, or explicit handoff; it is not implied by this diagram.

## Durable Object SQLite

Cloudflare SQLite Durable Objects remain a good remote coordination store for bounded mailbox state:

```sql
CREATE TABLE envelopes (
  id TEXT PRIMARY KEY,
  sender TEXT NOT NULL,
  recipient TEXT NOT NULL,
  family TEXT NOT NULL,
  ciphertext BLOB NOT NULL,
  created_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL
);

CREATE TABLE tombstones (
  id TEXT PRIMARY KEY,
  deleted_at INTEGER NOT NULL,
  reason TEXT NOT NULL
);
```

The exact physical schema may evolve. The stable contract is the mailbox/cursor semantics and idempotent IDs, not a promise that local and Cloudflare schemas are byte-identical.

## Cap'n Web fits above SQLite

Cap'n Web is useful for reducing network round trips between the browser SQLite worker/body and remote fabric bodies. For example, a client can pipeline:

```text
remote.mailbox(self)
  .sync(after)
  .apply(receipt)
```

without waiting between dependent calls.

That does **not** mean SQLite queries themselves become remote RPC. Keep SQL local to the body that owns the database and move bounded semantic operations across the capability boundary.

## Read-only fetched databases: use the old work, measure the current work

[Phiresky's `sql.js-httpvfs`](https://github.com/phiresky/sql.js-httpvfs) and the accompanying [2021 HTTP-range design article](https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/) remain excellent prior art. They demonstrate that a read-only SQLite file can become useful after fetching its header, schema, and only the indexed pages needed by a query. Virtual read heads, covering indexes, explicit file sizes, and range-aware hosting all belong in the `curso-range` experiment.

That does not make `sql.js-httpvfs` the current writable baseline or an unmeasured performance winner. Its repository describes the VFS as demonstration-grade, without VFS tests or cache eviction, and recommends wa-sqlite for new custom VFS work. It is based on `sql.js`, while the writable body uses the official SQLite WASM package.

The follow-up harness must compare at least:

| Contender | What it is allowed to prove |
| --- | --- |
| [`sql.js-httpvfs`](https://github.com/phiresky/sql.js-httpvfs) | mature HTTP-range ideas and an optimized read-only reference workload |
| [`wa-sqlite`](https://github.com/rhashimoto/wa-sqlite) custom VFS | current pluggable VFS APIs, IndexedDB storage, and custom cache/fetch policies |
| [`sqlite-wasm-http`](https://github.com/mmomtchev/sqlite-wasm-http) | a newer experimental HTTP VFS built around official SQLite WASM |
| `curso-range` | content-addressed manifests, per-block hash verification, peer/webseed carriers, cancellation, and Cursors admission |

No candidate is “leading” for Cursors until the same browser matrix measures cold start, random page reads, covering-index queries, cache hits, transferred bytes, concurrent readers, interrupted recovery, corruption rejection, and bundle cost. Writable mailbox/outbox throughput is a separate benchmark and stays on official OPFS/SAH-pool unless those measurements justify another registered backend.

Fetched databases are immutable and read-only. They may be attached for staging, history, catalogs, or analytics. They never carry a live mailbox WAL or rollback journal; every fetched block is hash-verified, and signed semantic rows are validated before copying accepted material into writable local databases.

## Implemented handoff and what remains

The official SQLite Worker now exposes the bounded local body through Cap’n Web MessagePort. A local cursor-resume transaction co-locates `cursor_resume_intents` and its mailbox outbox row; the browser scheduler can pipeline an authenticated CloudMailbox append and receipt read-back through `POST /`, then complete the local outbox. It does not move SQL, WAL, pages, or a remote database attachment across that boundary. See [Cap’n Web Pipeline](capnweb-pipeline.md).

The remaining work is:

1. Move the cockpit’s older ad-hoc local mailbox store onto this body API.
2. Add LAN, WebRTC, and REST fallback schedulers around the same semantic outbox rows.
3. Benchmark `opfs`, `opfs-sahpool`, and a selected wa-sqlite writable adapter in supported browsers.
4. Compare `sql.js-httpvfs`, wa-sqlite, `sqlite-wasm-http`, and `curso-range` over immutable content-addressed snapshots.
The desirable result is deeply unexciting: SQLite handles transactions, the VFS handles local files, Cursors handles distributed meaning, and transports move bounded operations. Each layer gets one job instead of all of them becoming a bespoke replication engine.