Schema v60 and incremental migration

Claim: post-launch schema bumps must upgrade in place

NT² Vault keeps each vault’s authoritative catalog in local SQLite—titles, categories, attachment metadata, search indexes, and the section store that holds identity and unlock material. The browser file lives under OPFS; desktop shells use a native file. Either way, the database is not a disposable cache. It is the durable home of encrypted payloads and the relational scaffolding around them.

That durability only matters if the schema can evolve without treating every user vault as a development scratchpad.

Our claim is blunt: after launch, opening a vault upgrades its SQLite schema incrementally from a fixed launch baseline. Today that means vaults stamped at v59 move forward—starting with v60—without recreating the database file. New vaults still receive the full greenfield CREATE script and stamp at the current schema version. Pre-launch databases below the baseline remain unsupported. Schema bumps are product events that preserve user data, not rebuild rituals that ask people to export, delete, and start over.

Schema v60 is a concrete public fact: it is the current vault SQLite schema version. The more important fact is how we got there for vaults that already existed when the product left the lab.

Constraint: squash-and-reject is fine before users; fatal after

Before launch, a local-first product can afford a hard rule that feels cruel in production: if the on-disk schema version is behind the app, refuse to open and tell the developer to recreate. Historical migration steps can be squashed into one greenfield CREATE. Agents stop maintaining fifty fragile ALTER paths. Tests exercise “create at current,” not “walk every ancient version.” For a pre-release vault that never held real life data, wipe-and-recreate is honesty, not cruelty.

That policy collapses the day real vaults exist in the wild.

A launched vault stamped at the squash baseline still has years of credentials, notes, attachment rows, and search indexes ahead of it. The next release will need a nullable column, an index, or a small table tweak. If “behind current” still means “recreate,” every additive change becomes a forced migration theater: export a backup, delete the vault, import again, hope nothing was missed. Users experience schema evolution as data loss risk. Support experiences it as a ritual of last resort. Engineers experience it as a veto on shipping honest DDL.

There is a second failure mode that looks softer but is worse: silent wipe. Open the vault, notice the CREATE shape drifted, replace the file, present an empty list. Local-first products that treat OPFS like a regenerable cache teach people that “local” means “expendable.” Zero-knowledge does not rescue you if the only copy of ciphertext lived in the file you just replaced.

So the constraint is sharp. Pre-launch, squash and reject outdated versions. Post-launch, the launch baseline is a floor you may upgrade from, not a ceiling that forbids change. You need two complementary paths: greenfield for empty databases, and incremental steps for databases that already carry someone’s life.

You also need a hard line below the floor. Unsupported pre-baseline files should still fail closed with a recreate path—not attempt to resurrect every historical step from v1 through the squash. The squash remains final for that era. Continuity begins at launch.

Design: greenfield CREATE plus ordered steps from v59

The vault SQLite schema has one current stamp—today 60—and one launch baseline—59. Opening a vault reads the stored version and branches.

flowchart TD
  open[Open vault SQLite] --> read[Read schema version]
  read -->|null / empty create path| green[Apply full greenfield DDL]
  green --> stampNow[Stamp current version]
  read -->|version below 59| reject[Refuse — recreate required]
  read -->|59 to current minus one| loop[Apply each pending step]
  loop --> step[Additive DDL for next version]
  step --> stamp[Stamp that version]
  stamp --> loop
  loop -->|caught up| ok[Open continues]
  read -->|already current| ok
  read -->|newer than app| update[Refuse — update the app]

Greenfield remains authoritative for shape. A brand-new vault applies the full CREATE script—tables, indexes, FTS scaffolding, section store—then stamps the current schema version. Agents and humans keep that script aligned with reality. Incremental steps are not a second, divergent schema; they are the bridge from yesterday’s stamp to today’s CREATE.

Incremental migration runs only when the stored version is at or above the launch baseline and still behind the app. Each pending version has an ordered step: typically additive ALTER TABLE … ADD COLUMN, applied with “add if missing” discipline so a step can be safe to re-enter. After each step succeeds, the database stamps that version before the next. Opening a v59 vault under a v60 app applies the v60 step and stamps 60. Opening an already-v60 vault is a no-op on the migration path.

Below baseline still rejects. A database from the pre-launch era is not promised a magical walk through retired SQL. Recovery stays human-scale: recreate or restore from a portable backup when the product still supports that path. That refusal is deliberate: it keeps the supported migration surface finite.

Ahead of the app also rejects. If a vault somehow carries a future version stamp, the old client must not invent downgrades. The honest message is to update the app. Schema authority lives with the software that understands the CREATE shape and the step registry.

The first post-launch step—v59 → v60—is intentionally boring: nullable columns on an existing peer table so optional hub pairing can store what it needs without rewriting rows. Boring is the point. The runner matters more than the columns. Once in-place upgrades work, every later bump ships as another versioned step from the previous stamp, with greenfield DDL updated when the CREATE shape changes.

This design lives next to other local durability choices: the vault file in OPFS rather than an IndexedDB emulation layer, SQLite work off the UI thread so migration and queries do not freeze chrome, and a list UI that pages instead of loading the whole catalog into memory. Schema evolution is part of that same promise—the database is real, and real databases need grown-up upgrades.

Trade-off: two truths to maintain, and a narrower rescue surface

Incremental migration is not free.

We maintain two representations of “current.” Greenfield CREATE must describe a fresh vault at schema 60 (and whatever comes next). Each bump also needs a step that takes N − 1 to N without destroying rows. Drift between those two is a bug class: a column in CREATE but missing from the step, or a step that assumes data the CREATE never produced. The discipline is mandatory—bump the version only with a matching step, greenfield sync when CREATE changes, and a documented version row—not optional polish.

We give up resurrecting pre-baseline history. Engineers who joined after the squash do not get a museum of v1–v58 ALTER scripts. That is a maintenance win and a support narrowing. Users on ancient pre-launch builds are asked to recreate rather than pretend every lab database is forever. Continuity is a product promise starting at launch, not an infinite archaeology project.

We prefer additive DDL. Nullable columns and careful backfills preserve encrypted rows. Destructive shape changes—drop tables, rewrite ciphertext, force a new envelope layout—are not silent open-path events. They need an explicit product major and user consent. That slows some refactors. It also stops “schema bump” from becoming a euphemism for data loss.

We accept that migration runs on open. A user who updates the PWA and unlocks may spend a brief moment applying steps before the vault is usable. That cost should stay small for additive changes. It is still a cost we choose over asking people to rebuild their archive for every column.

What we gain is the property local-first products advertise and rarely keep: your vault file survives the product’s own evolution. Ciphertext stays put. Indexes stay put. The section store that holds salt and verifier stays put. Schema version is a stamp, not a detonation fuse.

What we refuse

We refuse wipe-on-every-schema-bump after launch. Additive evolution must not require deleting the OPFS (or desktop) vault file as the default upgrade path.

We refuse silent recreate when CREATE drifts. An empty vault presented as “updated” is a data-loss bug wearing a release note.

We refuse resurrecting every pre-launch migration as supported surface. The squash to v59 is the floor. Below it, fail closed with recreate—not a best-effort replay of retired SQL.

We refuse bumping the schema version without a matching incremental step. A greenfield-only bump leaves launch-baseline vaults stranded behind a recreate wall we already rejected.

We refuse destructive open-path migrations without consent. Dropping user tables, rewriting sealed payloads, or discarding rows to “make DDL clean” is not an automatic side effect of unlock.

We refuse treating the vault database like regenerable cache. Local SQLite is authoritative storage. Migration policy must sound like a database product, not like a temporary PWA experiment.

Close: continuity is a local-first feature

Schema v60 is a number. Incremental migration is the policy that makes the number safe for people who already entrusted a vault to their device.

Greenfield CREATE keeps new vaults honest. Ordered steps from the v59 launch baseline keep existing vaults continuous. Rejecting pre-baseline and future stamps keeps the supported surface finite. Together they say the same thing as our storage stack: the vault file is real, so evolving it must not pretend it is disposable.

For where that file lives in the browser, read Why our vault SQLite database lives in OPFS, not IndexedDB. For how query work stays off the UI thread while the database opens and migrates, read SQLite in a Web Worker is not optional. For how the list stays fast once the schema has grown indexes and rows, read Never load the whole vault into Svelte state.

If you want a vault whose local database can ship schema bumps without a wipe ritual, try NT² Vault or read more at nt2.me.