Rotating encryption keys under a database per household, with no downtime

The textbook answer is envelope encryption: give every household its own data key, wrap each data key under a master key, and then rotation is just re-wrapping a few thousand small blobs instead of re-encrypting every row. We considered it and did not do it, and the reason is the platform. The product runs entirely at the edge, in a serverless runtime with no key management service inside it, and every household key is *derived* from the master secret with HKDF rather than stored. A wrapped data key would have to live somewhere, which puts a lookup on the read path — the hot path — before a single row can be decrypted.

Derivation keeps the read path at zero lookups: the value and the household id are enough to open it. The price is that a master rotation really does mean touching every row. This post is about paying that price without a maintenance window.

The first design anyone proposes is lazy migration: when a member signs in, quietly re-encrypt their household's data under the new key. It is appealing because it needs no background machinery.

It does not work here, and the reason is worth stating because it generalises. Count the things that decrypt a household's data with nobody logged in: sixteen scheduled jobs (one of them the re-key sweep itself), inbound email parsing, calendar feed subscriptions, the shared-tablet kiosk, external share links, the AI tool endpoints, automations. A household can go a month without a human session and still have its data read and written daily by all of those.

So a login-driven re-encryption is not a migration mechanism. It is at best an optimization on top of one. The thing you actually need first is for the system to be able to read *both* keys at once — and once you have that, re-encryption stops being a migration and becomes housekeeping whose only purpose is letting you eventually delete the old key.

So the envelope grew a field naming the key that sealed it:

v1:<iv>:<tag>:<ct>                    legacy — implicitly key id 0, generation 0
v2:<kid>.<gen>:<iv>:<tag>:<ct>        kid names the master secret

`kid` says which master secret sealed the value. `gen` is a per-household generation counter folded into the key derivation, so a single household can be re-sealed on its own — say, after a device is lost — without rotating the master key for everyone. Both live in the envelope, which is what keeps the read path free of side lookups: a decryptor needs nothing but the value and the household id.

Two secrets hold the ring: `ENCRYPTION_KEYRING`, a JSON map of key id to value, and `ENCRYPTION_ACTIVE_KID`, the one new writes seal under. Reads try the key the envelope names; writes always use the active one. A value whose key id is not in the ring fails to decrypt with a specific, logged error rather than garbage — which matters later, because that error is what a finished retirement looks like from the read path.

The detail we are most glad we got right is the smallest: key id 0, generation 0 is still written as `v1:`. Those are the same key — the version prefix is only a record of which code wrote the value — so a deployment that never rotates emits output byte-identical to what it emitted before any of this existed, and `v2:` appears in the data only when someone actually rotates. Every rollback story, every backup, every "is this a new bug or an old one" question got simpler for one line of encoding.

The re-key engine walks a household's database table by table, column by column, selecting the values that still carry an old key id. It cannot take a lock — there is no maintenance window, the household is using the product — so between reading a value and writing its replacement, a member may have changed that row.

Every update is therefore a compare-and-set on the exact ciphertext that was read:

UPDATE "app_chores__entries"
   SET "notes" = ?
 WHERE rowid = CAST(? AS INTEGER)
   AND "notes" = ?

If the row moved, the statement matches nothing, and the engine counts it as skipped rather than clobbering a newer value with a re-encryption of an older one. The next pass picks it up, already sealed under the new key by whoever wrote it. Comparing ciphertext rather than plaintext is what makes this sound: every write draws a fresh random IV, so even a member re-saving the identical text produces different bytes, and an unchanged ciphertext is proof that nobody wrote.

That leaves a nastier problem. A statement reporting `changes: 0` is ambiguous: it might mean the compare-and-set lost a race, or it might mean the statement never ran because something upstream failed. Those are byte-identical in the result, and they mean opposite things. So the engine re-reads the rows that reported no change and classifies each one by what is actually in the column now. A count you did not take is not a zero, and a write you cannot confirm is not a success.

Somewhere in the database is a row recording how far a household has been re-keyed. It is tempting to think of it as a progress note. It is not:

**That row is the claim an operator deletes a key on.**

Almost every defect we found — and the review log for this engine runs to well over a hundred across dozens of rounds — was a different way that row could go green while data was still sealed under the old key. Once you hold that sentence in your head, the right answers get obvious and the wrong ones get loud. A pass that could not read a table is incomplete and records nothing. A pass ends with a second full sweep, because an app can create a table while the first sweep is running. The sweep over the key-value store is fenced by a write-generation counter, because that store has no compare-and-set to lean on.

The test we would give anyone touching this code is one question, asked of every branch: can this settle the claim without having actually moved everything?

There is a canary value in the key-value store that the deploy self-check decrypts, so a wrong secret is caught in minutes rather than by a member noticing their calendar is full of garbage.

With a keyring, one canary is not enough — it only proves the key it happens to be sealed under. So it became one canary per key id, every key in the ring verified on every run. That fixed the obvious hole and introduced a subtler one, which took another review round to see: the check enumerated the keys in *the configuration*. A key accidentally dropped from `ENCRYPTION_KEYRING` was therefore never checked at all, and the self-check reported a clean bill of health for a key whose rows had just become unreadable.

The rule that falls out of that is the most portable thing in this post. **Coverage has to follow the data, not the config.** The check now lists the canaries that exist in storage and reports any whose key is missing from the ring — which means retiring a key includes deleting its canary, and that deletion is how an operator says the retirement was deliberate. An accidental drop and an intended retirement must not look identical to a monitor.

The first key was a special case, and it stayed one longer than we expected. The original secret arrives through a differently-named environment variable than the ring does — an alias that supplies key id 0 and nothing else. While that alias is deployed it supplies key 0 on its own, so "remove key 0 from the ring" named an edit that changes nothing, and an operator who made it would read a still-healthy ring as a failed retirement.

The fix was not to declare key 0 permanently un-retirable, which is what we believed for a while. It was to make the alias a *measured* condition. The retirement check reports a specific refusal — key 0 is supplied by the deprecated alias, so removing it from the ring does nothing, do the cutover first — and the cutover is a no-op deploy that moves the same bytes into the ring under an explicit key id before the alias is deleted. After that, key 0 gates exactly like every other key.

There is a guard alongside it worth copying: if the alias and the ring both supply key 0 with *different* values, the ring refuses to parse and the worker fails loudly everywhere. Two sources for one key id is precisely how a rotation seals data under the wrong secret, and equality is what makes the cutover safe to do as a separate, boring deploy.

A key must also outlive every copy of every row it sealed, and backups are the copy people forget. Our database has a 30-day point-in-time restore window, so a key becomes deletable 30 days after the last household finished moving off it — the backup window, not the re-key, sets that clock.

The limit that does not go away is custody, and it is not a code problem. Every key that ever sealed a row has to be producible for as long as any copy of that row exists, which means a named vault, one item per key id, more than one person who can reach it, and an actual demonstration that the stored copy opens real data — not an assumption that it would. A rotation system whose old keys nobody can produce is not a rotation system; it is a way to lose data on a schedule.

For the operator, a rotation is six steps, and the system refuses to let them happen out of order:

1. Put the new key in custody and prove the stored copy opens a test value.
2. Add it to `ENCRYPTION_KEYRING` under a new key id. Nothing changes yet; the ring can now read it.
3. Point `ENCRYPTION_ACTIVE_KID` at it. From this deploy on, every write seals under the new key.
4. Wait. A nightly sweep re-keys households in the background, each one recording its own completion claim.
5. Thirty days after the last household converged, the retirement check opens. Remove the old key id from the ring, and delete its canary to record that the removal was deliberate.
6. Keep the old key in custody anyway. Exports and offline copies can outlive the backup window, and a key is never deleted from the vault.

That is why the procedure's first rule is to rotate by **adding** a key, never by changing a key's value in place. Setting a new value for an existing key id with a secrets command re-keys nothing: it makes every value that key sealed unreadable at once, and the backups hold the same bytes, so they are unreadable too. The only recovery is restoring the previous value from custody. Each key's value is immutable for the life of the deployment. The ring is the thing that changes.

• What we do and don't store
• Apps run inside your family hub and still can't read your family's data