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

The goal

Start from a stubborn premise: a group wants to keep shared records together — one converging truth — but they don't trust a server to hold it, they're often offline, and any one of them could lose a laptop or be leaned on. The usual answer, "put it behind a login on a server," fails all three at once. So the records live in an ordinary SQLite file that each person carries, and the whole apparatus of access control has to be baked into the data itself, so it survives the file being copied, edited on a plane, and merged with someone else's copy.

Three properties have to hold with no server in the loop:

• Multiple parties can each run the database independently and make changes offline.
• Their changes merge for local consensus — two people reconcile by handing each other a delta — and for group consensus — a whole subgroup converges to one agreed checkpoint.
• No single keyholder is alone on the critical path for compromising the archive. Losing one person — or one person being coerced — doesn't hand over the data.

The engine underneath is cr-sqlite, a CRDT extension to SQLite: per-column merge that is commutative, associative, and idempotent, so replicas that have seen the same set of changes converge regardless of order. cr-sqlite handles convergence. Everything about who may see and change what is layered on top, and that layer is the interesting part.

Read control is a data-encryption key

You cannot stop someone reading the bytes on their own disk, so "can Alice read this record?" can't be a policy check — it has to be can Alice decrypt it? Every record is encrypted, and the right to read is the possession of a key.

The unit of permission is a resource (a compartment — patient:alice, notes, whatever the app decides). Each resource has its own data-encryption key (DEK), and a version counter so it can be rotated. A record is a set of encrypted cells — one row per (record, column) — each ciphertext produced by AES-256-GCM under its resource's current DEK:

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

Where does a reader's DEK come from? Two paths, and this is the crux of the DEK design:

• The admin derives it. The admin never stores resource keys — they're computed on demand from a single root secret with HKDF: DEK = HKDF(rootKey, info = "dek:" + resource, salt = "v" + ver). One root secret deterministically fans out into a key for every resource and every version.
• Everyone else is sealed into it. When the admin grants Alice a role on a resource, they derive that resource's DEK and seal it to Alice's X25519 public key (a sealed box), writing the result into a keywrap row. Alice — and only Alice — can unseal it with her private key.

So reading is mechanical and needs no authority online. listRecords walks the cells, and for each one asks "do I hold this (resource, dek_ver) key?" — deriving it (admin) or unsealing it from a keywrap (everyone else). Cells it can decrypt fold into records by last-writer-wins; cells it can't are simply skipped. A user with no grant holds the exact same ciphertext and sees a 🔒. There is nothing to leak because there is no plaintext to hide in the first place.

Write control is a signature checked at merge

Encryption stops unauthorized reads. It does nothing about unauthorized writes — anyone can append a row to a SQLite file. So every row carries its author's Ed25519 public key and a signature over its canonical bytes, and the enforcement happens where it can't be bypassed: at merge.

When you import someone's changeset, it is not trusted and applied. It's first replayed into a throwaway staging SQLite connection, where the asserted rows become readable, and every row is checked:

1. System rows first — identities, grants, key-wraps, DEK versions, checkpoints. Each must be signature-valid and admin-authored (identities may be self-signed). These establish a trustworthy picture of who is a writer. If any is tainted, the whole changeset is rejected before data is even looked at.
2. Data rows next — every cell must be signature-valid and its author must hold a non-revoked writer grant for that cell's resource, judged against the effective grant state (the trusted grants already in your database, union the admin-verified ones just staged).

Only if every row passes does the changeset merge into the real database. A forged cell, a self-appointed admin (a conflicting admin root), a reader trying to write — all are rejected at merge, and the import reports why. The authenticity travels with each row, not as a signature on the envelope, which matters because cr-sqlite changes keep their origin and gossip transitively through relays: a per-envelope signature would be meaningless two hops later, but a per-row one holds forever.

Local consensus: two files, one state root

Two people reconcile with no server by trading changesets. To know they've converged without shipping the data, each computes a state root — a SHA-256 over the sorted cell and grant state. Equal roots mean identical state. It's a cheap, private "we're in sync" check: you compare 64 hex characters, not a database.

A subtlety that cost real debugging time: cr-sqlite's db_version is a local clock — it gets reassigned to the merging replica's clock on apply, so version vectors aren't portable between peers. "Give me everything since checkpoint H" therefore can't use a shipped version vector; it uses a local watermark — the db_version this replica held when it adopted H — and exports its own writes past that mark. The gotcha is documented right where it bites, in the engine.

Group consensus: the wipe-and-rebuild ceremony

Local merge converges two peers. A whole subgroup needs a ceremony. A coordinator (the admin) collects everyone's diff-since-the-last-checkpoint, merges them, optionally rotates every DEK, and records a signed checkpoint — a hash of the agreed state, its epoch, its parent, and the member roster. The chain of checkpoints is the group's history of agreed states.

Then the elegant part. Instead of shipping the merged database back wholesale, the coordinator builds each member a rebuild slice: a self-contained changeset with the auth and checkpoint state, plus only the non-archived cells for resources that member is entitled to read. Each member wipes their local store and rebuilds from the slice. One move does three jobs at once:

• Compaction — tombstoned records and superseded history fall away.
• Data minimization — you get back exactly what you're entitled to, nothing more. Yesterday's access you no longer have simply isn't in your new store.
• Redistribution — everyone starts the next cycle from the same agreed checkpoint.

"Evening lock, morning unlock": the group converges at day's end, optionally rotates keys, and each person walks away with a fresh minimal slice for tomorrow.

No single keyholder: Shamir threshold custody

Everything so far still has an uncomfortable shape: the admin's root secret can derive every DEK. If custody of the whole archive can rest on one person, that person is a target — and cryptography has nothing to say to a $5 wrench. The fix isn't a stronger cipher; it's arranging things so no one person is worth the wrench in the first place.

A group should be able to keep records together without making any single one of their number particularly interesting to knee-breakers.

That's exactly what Shamir's 1979 How to Share a Secret buys. To seal a payload — say, a full database dump — for custody, sealVault:

1. generates a random data-key and encrypts the payload under it (AEAD);
2. Shamir-splits the data-key into n shares such that any k reconstruct it and any k−1 reveal nothing;
3. seals share i to custodian i's X25519 public key.

The math is a polynomial of degree k−1 over the finite field GF(2⁸), one per secret byte: the constant term is the secret, the other coefficients are random, and each custodian gets the polynomial evaluated at a distinct point. Any k points determine the polynomial (Lagrange interpolation at x = 0 recovers the secret); fewer than k leave every value equally likely. It is the one primitive here that has no WebCrypto equivalent, so it's the one thing hand-rolled — about 60 lines, and deliberately not a cipher: it only governs who can reassemble the key. Confidentiality still rests on the AEAD.

Unlock is distributed — no hot-seat where all the shares briefly sit in one place. Each custodian, at their own machine, re-seals their share to a chosen opener; the opener gathers k of them, reconstructs the key, and decrypts. Wrong or insufficient shares fail closed on the AEAD tag, and the reconstructed key is zeroed after use. A sensible default quorum is 4-of-5: you can lose one custodian and still open the vault, but an adversary needs to compromise four people at once. No one keyholder is a single point of either failure or coercion.

A tour of the datalayer

The engine is a headless package — no DOM, no build tooling — that an app wires to a UI. It is deliberately resource- and column-agnostic: records are a generic entity-attribute-value store, and "notes" (title/body columns, seeds, the UI) is a demo convention layered on top, not baked into the core. Here's the map.

See it run

The three-user demo is one HTML file loaded three times — three independent machines that never talk to each other. Give the admin a role to the writer, deny the reader, add a note, export a delta, import it in the next frame: the writer's note converges, the reader sees 🔒. Every frame is the real engine; the only simulation is that "sending a delta between machines" is modelled as moving text between iframes instead of over a USB stick.

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
• xkcd 538 — Security — why no single keyholder should be worth the wrench
• cr-sqlite — convergent replicated SQLite (the CRDT engine)
• RheoServ Waumpaum · Offline HRIS — the offline-CRDT lineage this builds on