Records a group can keep — without any one keyholder becoming a target 🔐

The goal

A group wants to keep shared records together — one converging truth — without trusting a server, often offline, knowing any one of them could lose a laptop or be leaned on. So the records live in an ordinary SQLite file each person carries, and access control is baked into the data itself — it has to survive the file being copied, edited offline, and merged. Three properties, no server in the loop:

• Multiple parties each run the database and edit offline.
• Edits merge: two peers reconcile by trading a delta; a subgroup converges to an agreed checkpoint.
• No single keyholder is alone on the critical path — losing or coercing one person doesn't hand over the data.

The engine is cr-sqlite, a CRDT extension to SQLite: per-column merge that's commutative, associative, and idempotent, so replicas collaborate on the same records and converge regardless of merge order. cr-sqlite gives convergence; everything about who may see and change what is layered on top.

Read control is a data-encryption key

You can't stop someone reading bytes on their own disk, so "can Alice read this?" becomes can Alice decrypt it? Each resource (a compartment — patient:alice, notes) has its own versioned data-encryption key (DEK). A record is encrypted cells, one per (record, column), under that resource's DEK:

cell(pk = rid|resource|col, rid, resource, col, ct, dek_ver, author, sig)

A reader gets the DEK one of two ways:

• Admin derives it on demand from one root secret: DEK = HKDF(rootKey, "dek:"+resource, "v"+ver). No resource keys are stored.
• Everyone else is sealed in: on grant, the admin seals that DEK to the member's X25519 key into a keywrap row. Only they can unseal it.

Reading needs no online authority: listRecords decrypts the cells whose key you hold and skips the rest. No grant, same ciphertext, a 🔒 — nothing leaks, because there's no plaintext to hide.

Write control is a signature checked at merge

Encryption stops reads, not writes — anyone can append a row to a file. So every row carries its author's Ed25519 key and a signature, enforced where it can't be bypassed: at merge. An incoming changeset is replayed into a throwaway staging database and checked before it's trusted:

1. System rows (identities, grants, key-wraps, checkpoints) must be signature-valid and admin-authored — establishing who is a writer.
2. Data cells must be signature-valid and authored by a current writer for that resource.

Only if every row passes does it merge. A forged cell, a self-appointed admin, a reader trying to write — all rejected at merge, with a reason. Authenticity rides on each row, not the envelope, so it survives cr-sqlite's transitive gossip.

Local consensus: two files, one state root

Two peers reconcile by trading changesets. To know they've converged without shipping data, each computes a state root — a SHA-256 over the sorted cell + grant state. Equal roots, identical state: compare 64 hex characters, not a database. (One gotcha, documented in the engine: cr-sqlite's db_version is a local clock reassigned on merge, so "everything since checkpoint H" uses a local watermark, not a portable version vector.)

Group consensus: the wipe-and-rebuild ceremony

A whole subgroup converges through a ceremony: a coordinator merges everyone's diff-since-last-checkpoint, optionally rotates every DEK, and records a signed checkpoint (a hash of the agreed state). Then, instead of shipping the merged database back, it hands each member a rebuild slice — the auth/checkpoint state plus only their entitled, non-archived cells — and each member wipes and rebuilds from it. One move does three jobs: compaction, data minimization (you get back only what you're entitled to), and redistribution from the same checkpoint. Evening lock, morning unlock.

No single keyholder: Shamir threshold custody

One uncomfortable shape remains: the admin's root secret can derive every DEK. If any one person can decrypt the whole collective dataset alone, that person becomes a target for extortion — and cryptography has nothing to say to a $5 wrench.

So Shamir's secret sharing splits the custody key into n shares over GF(2⁸) — any k reconstruct it, any k−1 reveal nothing — and seals each share to one custodian's key. sealVault encrypts a payload under a random key and splits that key; unlock is distributed (each custodian re-seals their share to the opener, who combines k). A 4-of-5 quorum survives losing one custodian but needs four people compromised at once. No single keyholder is a point of failure or coercion.

Where the database lives: demand-paged onto encrypted IndexedDB

Ordinary SQLite is demand-paged: its pager reads and writes individual pages (4 KB by default) from the file as queries touch them, holding only a small page cache in RAM — a 50 GB database runs in a few megabytes, and only a :memory: database lives entirely in RAM. Most SQLite-in-the-browser throws that away: sql.js loads the whole file into a Uint8Array on the WASM heap. localRbac keeps the demand-paged model in the browser by injecting a VFS — SQLite's virtual-filesystem seam — so the pager's block reads and writes land in IndexedDB, one page at a time, never the whole file in memory.

The persistent connection opens through EncryptedIDBVFS, a vendored copy of wa-sqlite's IDBBatchAtomicVFS. Each SQLite page becomes an IndexedDB block keyed [path, offset, version], fetched and written on demand, with transparent per-page AES encryption in the VFS layer and batch-atomic commits. The only :memory: database is the throwaway staging connection that authorizes an incoming changeset before it merges (the same staging step from Write control, above) — the store of record is always the encrypted IndexedDB VFS.

Two properties let this run from file://, where most persistent-SQLite options can't: the VFS is Asyncify-based, so it needs no SharedArrayBuffer and therefore none of the COOP/COEP cross-origin-isolation headers that absurd-sql or threaded-OPFS builds require; and the crsqlite.mjs WASM is base64-inlined as a data: URI, so opening the engine costs zero network fetches. Demand-paged, encrypted, offline — from a double-clicked HTML file. (For the bare mechanism — just this VFS, without the encryption or CRDT — see the standalone write-up: Demand-paged SQLite from file://.)

The binary is built here, not installed

The engine underneath is not the published @vlcn.io/wa-sqlite — it is compiled from pinned source by sqlite-wasm-build, using the base variant (cr-sqlite + FTS5), the recipe that reproduces the published package. That matters here more than it would elsewhere, because SQLITE_OMIT_LOAD_EXTENSION is set: nothing can be loaded at runtime, so every capability has to be in the binary. Installing the published build means accepting someone else's answer to "which extensions does this database have" — and for a project whose whole argument is that the rules must survive the file being copied, that is an odd thing to leave to a registry.

Because the variant declares itself under the same package name and exposes the same import specifiers, adopting it moved one line and no source:

"@vlcn.io/wa-sqlite": "file:../../../sqlite-wasm-build/dist/base"

Swapping in geo (R*Tree, Geopoly) or vec (sqlite-vec) is the same line with a different word — which is the reason for building them as separate variants rather than one everything binary. The build, the four variants and the six toolchain failures behind them are written up in One binary, every extension.

A tour of the datalayer

The engine is a headless package an app wires to a UI, deliberately resource- and column-agnostic — records are a generic entity-attribute-value store; "notes" is a demo convention on top. The map:

See it run

The three-user demo is one HTML file loaded thrice — three machines that never talk. Grant the writer, deny the reader, add a note, export a delta, import it next door: the note converges, the reader sees 🔒. Real engine throughout; only the transport (text between iframes vs. a USB stick) is simulated.

References

• rheophile10/localRbac — source & the datalayer tour
• the three-user demo — one self-contained file, loaded thrice
• Adi Shamir, How to Share a Secret, CACM 22(11), 1979 — the threshold-custody primitive
• Cargo cult — access control that isn't enforced is theatre
• xkcd 538 — Security — why no single keyholder should be worth the wrench
• cr-sqlite — convergent replicated SQLite (the CRDT engine)
• wa-sqlite — async WASM SQLite whose IDBBatchAtomicVFS demand-pages onto IndexedDB (the storage engine, wrapped as EncryptedIDBVFS)
• Demand-paged SQLite from file:// — the bare wa-sqlite + IndexedDB-VFS version, without the encryption or CRDT
• sqlite-wasm-build — the pinned-source build this runs on; four variants, each a drop-in @vlcn.io/wa-sqlite
• One binary, every extension — why the build exists, and the six toolchain failures it took
• RheoServ Waumpaum · Offline HRIS — the offline-CRDT lineage this builds on