# SQLite Body and Lua Programs

This guide describes the first portable body slice. Status words are contractual: **SHIPPED** means code and tests are in this repository, **EXPERIMENTAL** means a runnable or inspectable fixture exists but is not a compatibility promise, and **PLANNED** means design only.

```text
Lua source artifact
  → restricted parser/compiler
  → cursor continuation graph
  → placement + capability check
  → SQLite transactional body
  → Linda assertions/interests with leases
  → Cap'n Web / MessagePort live pipeline
  → bounded semantic sync envelope
  → independent DO SQLite / swarm bodies
```

The boundaries matter: source is not executable authority, a tuple match is not effect authority, an RPC session is not durable history, and a SQLite changeset is not the Cursors protocol.

## SHIPPED: the body contract

`src/sqlite-body.mjs` supplies a native-compatible schema and allowlisted operation compiler for stored sources, compiled programs, mailbox envelopes, tuple assertions, interests, transactional effects, receipts, fetched-resource metadata, and semantic-sync envelopes. The tables are ordinary SQLite tables, placed deliberately:

| Local file | Owned tables |
| --- | --- |
| `self.db` | stored sources and compiled programs |
| `mailbox.db` | mailbox envelopes, receipts, semantic-sync envelopes, and the mailbox outbox |
| `world.db` | tuple assertions, interests, and the world outbox |
| `cache.db` | verified fetched-resource metadata |
| `outbox.db` | effects with no co-located semantic intent |

An effect is inserted in the same attached database transaction as its local semantic intent; only after commit does a scheduler observe pending rows. The shipped persistent backends do not claim crash atomicity across attached files, so an operation batch may target only one database. `SELECT` never sends mail or performs an irreversible network effect.

Four connection profiles describe the authorizer boundary:

| Profile | Intended authority |
| --- | --- |
| `admin` | migrations and local `ATTACH` |
| `internal` | body-owned operational reads/writes |
| `readOnly` | inspectors and analytics projections |
| `appScoped` | named allowlisted application tables |

The JavaScript model enforces these profiles now. The browser query capability prepares exactly one statement, asks SQLite's own `sqlite3_stmt_readonly()` whether it can write, and installs `sqlite3_set_authorizer()` while it runs. SQL text prefixes are not a security boundary. Row count, SQL bytes, parameters, result bytes, VM execution time, message bytes, and request rate are bounded.

Pure deterministic helpers cover lease validity, canonical-byte recognition, and predicates over already-verified capability material. Hash and mailbox helpers remain asynchronous Web Crypto operations at the API boundary because SQLite scalar callbacks are synchronous in the official WASM OO1 API. Effectful operations go through `cursor_outbox`.

## SHIPPED: official SQLite WASM Worker

`browser/sqlite-body-worker.mjs` imports `@sqlite.org/sqlite-wasm` and owns the writable databases in a dedicated Worker. It exposes backend diagnostics plus `open`, `close`, `restart`, bounded read-only `query`, allowlisted `apply`, and restricted Lua compilation. `apply` accepts only `mailbox.enqueue`, `mailbox.receipt`, and `world.assert`; schema, `ATTACH`, and raw write SQL remain internal.

The present ownership is **one writable Worker per page**. This PR does not claim cross-tab singleton ownership. A later SharedWorker, Web Lock leader, or explicit handoff may provide that without changing the body API.

The Worker selects among backends that its loaded build and browser context actually expose:

| Backend | Reality |
| --- | --- |
| upstream `opfs` | preferred persistent baseline when cross-origin isolation and OPFS are present; browser-tested |
| upstream `opfs-sahpool` | selectable persistent backend using a private SyncAccessHandle pool; browser-tested |
| memory | shipped fallback; explicitly transient |
| upstream `opfs-wl` | exposed by the package but not registered here until its operational contract is measured |
| wa-sqlite IDBBatchAtomic/custom VFS | studied alternative and future adapter; not installed, so no performance claim |
| `sql.js-httpvfs` / `sqlite-wasm-http` | read-only fetched-snapshot contenders, not writable body backends |

Roy Hashimoto's wa-sqlite work is the model for keeping VFS choice pluggable: IndexedDB batch-atomic and custom VFS designs can win under different browser constraints. They should join the same harness before selection, rather than being declared universally faster.

Backend selection happens on first open. Attempts to switch a live body or make one Worker own a second body are rejected. Each registry entry reports availability, persistence, read/write capability, isolation requirements, locking, multi-database transaction support, and whether durable cross-file atomicity is actually claimed.

`self.db` attaches `mailbox.db`, `world.db`, `cache.db`, and `outbox.db` before schema initialization, only as local files. A Cloudflare Durable Object database is never attached.

## SHIPPED: Lua as stored source

`src/lua-program.mjs` stores UTF-8 source bytes and their source ID separately from compiled program bytes and program ID. The record includes `language='lua'`, profile/version, optional metadata, declared capabilities, and compilation status.

The example `examples/watch-party-presence.lua` uses only four allowlisted calls. Arguments are JSON-compatible literals inside Lua call syntax. It compiles to `assert`, `interest`, and `checkpoint` graph nodes with renewable, bounded Linda leases. It cannot import, call the host, access properties, execute arbitrary Lua, or directly cause effects.

```text
parse/validate
  → restricted intermediate form
  → cursor program graph
  → placement/capability check
  → execution body
```

This is intentionally not a general Lua AST. A later compiler can recompile the same source record and compare program IDs without rewriting the source artifact.

## SHIPPED: cockpit proof flight

The **Lua + SQLite Machine Lab** runs this slice inside the browser. It opens the dedicated Worker, compiles the watch-party source, displays the content-addressed graph, applies a leased `world.assert` plus its co-located outbox row, and queries both records back. The standalone `/cursor-machine-proof.html` entry uses the same component and is useful when separating body behavior from Open MCT shell startup. See [the operating guide](cursor-machine-cockpit-lab.md).

## EXPERIMENTAL: Sessions and projections

SQLite Session/changesets are the preferred normal-table capture mechanism when a build enables the Session extension. The envelope schema has optional changeset bytes plus required schema ID, causal horizon, capability reference, signature reference, and semantic rows. The installed official browser package does not expose a stable high-level Session API, so this PR does not fake one. Semantic rows remain the portable contract; changesets are an optimization between compatible, validated bodies.

`cursor_tuples` is the first real relational projection. Its indexes push lease horizon constraints into SQLite now. A native virtual-table module may later translate `xBestIndex` constraints for tuple shape, publisher, and horizon into Cursors interests. `cursor_mailbox`, `cursor_history`, `cursor_resources`, and `cursor_receipts` remain planned narrow read-only modules.

## SHIPPED: Cloudflare boundary

`worker/semantic-sync.js` accepts only bounded allowlisted semantic row families after a caller-supplied Cursors signature/capability verifier succeeds. Durable Object SQLite stores independent operational state. It does not receive pages, WAL, arbitrary SQL, or extension callbacks, and it does not turn its physical schema into identity or authority.

## Operational versus analytical formats

| Tool/format | Job | Status |
| --- | --- | --- |
| SQLite | hot mutable mailbox, lease, outbox, receipt, and local join state | SHIPPED slice |
| Arrow | columnar interchange from validated projections | PLANNED |
| Parquet | immutable cold checkpoints/history for analytics | PLANNED |
| DuckDB / DuckDB-Wasm | analytical queries over exported or attached read-only material | PLANNED |

DuckDB does not replace SQLite for mutation. Torrent bencoding remains torrent-local. TIP/TLV islands remain experimental.

## Benchmarks without invented claims

Run `npm run bench:sqlite`. The harness reports transaction-heavy inserts, indexed reads, and registered-function query time for official SQLite WASM memory and `node:sqlite` when available. It lists OPFS and uninstalled fetched-database adapters as unavailable instead of printing fictional numbers.

Run `npm run test:browser` for real Chromium coverage of cross-origin isolation, OPFS and SAH-pool selection, local `ATTACH`, restart persistence, unavailable-backend errors, backend lock-in, co-located mailbox/outbox writes, and SQLite-enforced read-only queries. There is still no universal “fastest browser SQLite” claim: the writable mailbox workload and read-only HTTP-range workload are different benchmarks.

Continue with [SQLite + Lua Cursor Machine](sqlite-lua-cursor-machine.md), [Machine Lab](cursor-machine-cockpit-lab.md), [Nelua portable core](nelua-wasm-core.md), [SQLite and VFS](sqlite-and-vfs.md), and [Cap'n Web Pipeline](capnweb-pipeline.md).