ADR: Schema state, unreleased development packs, and backups
- Status: Accepted
- Date: 2026-09-03
- Related: SQL database contract, database.md, backups.md
Bookclerk’s library schema used to grow as an incremental chain (named fragments V2–V29). No production databases exist, so that chain is not an upgrade path. Developers reset local DBs. The host still auto-applies the current development pack on library open.
Integer 0 cannot distinguish a fresh SQLite database from an applied
development schema (PRAGMA user_version defaults to 0). A future release
cut must not treat an existing unreleased database as an empty predecessor
to frozen v1.
SQL_CONTRACT_VERSION = 1 / SQL-v1 is the SQL grammar/ABI, not a
library schema freeze. There is no frozen production schema v1.
Internal and CLI state is not an integer identity:
Uninitialized
Unreleased { base_version, checksum }
Frozen { version, checksum }
SCHEMA_VERSION is the highest frozen revision this binary knows. Today
it is 0 because host_migration_plan() is empty — that means “there are
no frozen schema revisions,” not “this database is at schema zero.”
Unreleased always records its frozen base explicitly. Today that is
Unreleased { base_version: 0, checksum } (pre-v1 development). After a
future v1 freeze:
- a release database may be
Frozen { version: 1, checksum } - a development database with additional unreleased DDL becomes
Unreleased { base_version: 1, checksum }
Malformed / missing checksum / contradictory markers are a failure class,
not a state to continue from. Do not infer uninitialized or unreleased from
integer 0 alone.
On disk, bookclerk_schema_migrations stores namespace, state (unreleased |
frozen), version, checksum, app_version, and applied_at. Primary
key is (namespace, state, version). Host library and Bookclerk-owned
binding bootstrap use namespace bookclerk. Plugin-owned evolution uses a
separate bookclerk_plugin_migrations journal in the binding database, not this
table and not the bookclerk namespace. For unreleased host rows, version
is the frozen base (0 before any freeze). Host logic keys off state
plus that base. PRAGMA user_version is a frozen-version cache only.
bookclerk db version prints uninitialized,
unreleased@base<n>+<checksum>, or frozen@<version>+<checksum>.
host_migration_plan()
is empty. Live schema lives in unreleased_ops (already-separated
MigrationOps). Fresh databases apply those ops (frozen ups, then the
current unreleased list) and persist
Unreleased { base_version: SCHEMA_VERSION, checksum }. Joined scripts
(current_canonical_schema(), unreleased_sql()) are derived for
diagnostics, export, and execute_batch fixtures; they are not re-parsed
to recover statement boundaries.
current_canonical_schema() is not permanently equal to
unreleased_sql(). Today the frozen plan is empty, so they coincide. After a
freeze it is concatenated frozen ups plus whatever is again unreleased.
There is no production schema v1 freeze in this tree.
Connect transitions:
| Database | Library open |
|---|---|
Uninitialized |
Apply frozen canonical base known to this binary, then the current unreleased bucket; persist current state |
Matching Unreleased { base_version, checksum } |
No-op (base_version must equal this binary’s SCHEMA_VERSION) |
| Mismatched unreleased checksum or frozen base | Fail closed (cargo reset --yes) unless a later explicit exact-checksum promote exists |
Frozen { version, checksum } |
Verify frozen checksums; apply remaining frozen steps; then apply the current unreleased bucket if one exists. Resulting state is Unreleased { base_version: latest_frozen, checksum } when the bucket is non-empty |
Frozen newer / unknown / checksum mismatch |
Fail closed |
Pre-state-machine DB (user_version ≫ 0, no state row) |
Unsupported / malformed; reset |
A future freeze that copies unreleased_ops into
HostMigrationStep { version: 1 } must not apply v1 ups on top of an
existing Unreleased { base_version: 0, … } database just because both
historically used integer zero. Default: documented development reset.
Optional later promote: exact-checksum marker rewrite only.
Never auto-downgrade on daemon or CLI library open.
A recovery point is one complete logical database state at a specific time. The physical repository may reuse immutable canonical objects from earlier recovery points. Restore never replays older manifests (no chained incrementals).
DbCapabilities.consistentBackupRead means the adapter can expose one
stable logical state while Bookclerk reads schema, rows, and identity.
DbCapabilities.atomicUnitRestore means destructive restore of one logical
unit does not leave that unit partially replaced after an ordinary failure.
Orchestration keys on these flags, never on sqlite/postgres/d1 plugin ids. The host owns IDs,
timestamps, manifests, retention, and lookup. The durable artifact is
canonical Bookclerk content (admitted schema + portable DbValue chunks),
not VACUUM / pg_dump / D1 REST / native pages.
| Adapter | Consistent capture | Complete unit restore |
|---|---|---|
| SQLite | Read transaction over admitted tables; paged ORDER BY rows as DbValue |
Restore transaction + PRAGMA defer_foreign_keys (never toggle foreign_keys on the pool); PRAGMA foreign_key_check before commit; sqlite_sequence high-water |
| Postgres | REPEATABLE READ transaction |
Transactional replace; bookclerk_identity high-water (not native sequences) |
| D1 | Not advertised. Sequential HTTP is not a consistent image. | Not advertised. Sequential REST DROP/INSERT is not complete per-unit replacement. |
A recovery point never silently omits an expected table or fabricates SQL
NULL for an unrepresentable value. Restore eligibility does not require the
same adapter that captured the backup.
Missing backup capability fails backup/restore closed. Restore never merges. Restore does not auto-migrate the host library and does not run plugin-owned migrations. Integrity (manifest parse, supported format, every object digest, admitted schema, typed cells) is verified before any destructive action. The manifest is published only after every referenced object exists.
Crash-safe object write: unique same-directory temp → fsync the file →
no-clobber hard-link install → fsync the parent directory. Concurrent
writers that lose the install verify the winner's object. Incomplete
staging remains invisible. List/restore ignore unpublished work. Object
publication and GC/prune take an exclusive repository lock (backups/.lock)
so in-progress objects are not collected before the manifest commit.
Archive extraction refuses .., absolute paths, symlinks, hardlinks, and
other non-file/directory tar types, and enforces entry-count, per-entry,
total-expanded, and gzip-decoded stream budgets. Stored objects declare
uncompressed length in the envelope and refuse gzip expansion past that
cap. Temporary unpack directories are removed on success and failure.
Retention prunes automatic pre-migrate recovery points only. Never prune
manual backups. Reachability GC deletes objects no retained manifest
references. bookclerk db backup create is manual;
bookclerk db backup list lists by time; backup verify / restore accept
path, id, or timestamp. Duplicate / ambiguous lookup fails rather than picking
arbitrarily.
Skip capture only for Uninitialized. Unreleased databases with data are
backup-capable. Library schema at capture is the schema that matches the
recorded SchemaState (frozen vN under a newer binary exports vN, not the
latest pack).
--include-plugin-databases enumerates plugin_databases registry rows
(not the plugin-databases/ directory). Each logical unit is
(plugin_id, binding), opened through the active adapter session. A failed
unit fails the requested bundle. Units are replaced individually; a bundle is
not one transaction across independent databases. Plugin schema/version
markers restore as ordinary rows; the plugin may issue host-mediated
BookclerkSQL in its binding after startup. Restore does not run plugin
migrations.
Library-only restore preserves the target registry. Included restore rebinds
registry rows to the target adapter’s physical placement (never source
unit_ref).
A later design may journal canonical Bookclerk transactions at commit
boundaries and restore base recovery point + committed change segments.
Required future invariants would include: journal entry atomic with DB
commit; transaction boundaries; all mutations including DDL and plugin DDL
without stealing plugin migration ownership; replay idempotency; retention
tied to the oldest retained base recovery point. This repository’s object
store is intended to remain the base layer for that work. Do not emit a
change journal until that design lands.
Bookclerk defines migration identity, order, versioning, compatibility, and
recovery for host schema (host_migration_plan, numeric
HostMigrationStep.version, checksum chain, explicit downgrade when every
step has down). A database claiming frozen host version N must verify all
retained/applied host migration checksums required by that version, not
merely the final marker. Production schema remains unreleased / version 0.
Plugins declare an ordered migration history at startup using opaque
plugin-owned migration IDs and BookclerkSQL operations. Bookclerk assigns
no semantic meaning to those IDs (not integer versions, not contiguous
numbers, not semver, not timestamps, not UUIDs, no version - 1
predecessor). Bookclerk owns validation, immutable-history verification,
serialization, atomic execution, bookkeeping, backup/restore integration,
and stale-session fencing.
Registration (BookclerkPlugin.databaseMigrations(binding)):
- Occurs at binding initialization, before ordinary execute.
- The plugin presents the complete ordered sequence (opaque
id+ already separatedschema/dataBookclerkSQL operations). Resource limits are independent: migration count (maxListPage), operations per migration (maxPluginMigrationOps), total operations (maxPluginMigrationTotalOps), per-SQL bytes (maxScalarBytes), and aggregate id+SQL UTF-8 (maxPluginMigrationRegistrationBytes). - Empty list means no plugin-owned migrations.
- The host proves the entire sequence with one evolving
SqlTypeEnv(migration B typechecks against the schema produced by A). - A malformed registration fails before the binding is available for jobs.
- The plugin cannot inspect or rewrite the durable host journal.
Durable history is an exact prefix of the current registration:
durable plugin migration history == prefix(current registered history)
For every durable journal row, migration_id and checksum must match the
registration at that position, in order. Fail closed if an already-applied
migration was edited, removed, renamed, or reordered, or if durable history
is longer than the installed plugin (older/incompatible plugin). Only a new
suffix is pending.
The journal is bookclerk_plugin_migrations(ordinal, migration_id, checksum, applied_at)
inside the binding database. ordinal is host-private storage order only and
is not a plugin migration version. Every host table inside a binding
(journal, serialization slots, atomic receipts, catalog tables, the
bookclerk_src wrap alias) carries the bookclerk_ prefix, and that prefix
is the only name space reserved from plugin SQL (besides engine catalogs and
schema-qualified names). There is no native SQL escape hatch.
Each pending suffix migration is one atomic unit (serialization-slot
mutation + ops + journal append) in a short transaction. Concurrent
Bookclerk instances registering the same sequence converge without a
process-spanning or walk-spanning lock: each host re-reads durable history
before applying the next suffix item; uniqueness/conflict or an ambiguous
commit reply causes another journal read; matching (id, checksum) at the
expected ordinal is success; contradictory history fails closed; transient
failures retry under the existing bounded policy. The serialization-slot
SQL runs only inside that same atomic unit — it does not hold a lock
across later independent migration transactions.
There is no host-managed plugin downgrade. A plugin that wants to undo an earlier change registers a new forward migration. Operational rollback uses a compatible backup/recovery point. Destructive restore writes captured schema/data/journal exactly and does not walk the installed plugin's migrations. On the next ordinary startup, registration occurs; restored history must be an exact prefix of the installed sequence, then the pending suffix applies. Restored history newer than or incompatible with the installed plugin fails closed.
Stale-session fencing captures the complete history digest
(history@{n}+{digest} of ordered (migration_id, checksum) pairs) when
reconciliation completes. Ordinary execute re-reads the journal and fails
the session if the digest changed (peer advanced or rewritten history).
Fencing does not use a plugin-chosen latest ID.
Host-owned binding bootstrap remains binding_bootstrap_ops
(Schema ops: receipts, SQL catalog, bookclerk_plugin_migrations, serialization
slots). BINDING_SCHEMA_VERSION = 0 until a binding freeze. Connect:
| Binding | Open |
|---|---|
Uninitialized |
Apply bootstrap ops + unreleased marker (one atomic unit; retry uniqueness / unavailable after re-read) |
Matching Unreleased { base_version, checksum } |
No-op |
| Mismatched checksum / unexpected frozen | Fail closed (restore or drop the binding) |
Then register/prove plugin migrations and apply the pending suffix.
Ordinary binding execute uses GuestSqlPolicy::binding_owned (query/DML).
Only the host-controlled migration path uses binding_migration (bounded
CREATE/DROP IF [NOT] EXISTS). ALTER and CREATE TABLE AS stay refused.
There is no backend-native escape hatch (pg_dump, VACUUM INTO, D1 REST
migrate, sqlite .dump). Cross-adapter backup/restore uses canonical
Bookclerk data/schema representations; backups include the plugin migration
journal as ordinary captured rows plus the history digest in unit metadata.
HostMigrationStep { version, introduced_in, steps, down }
MigrationOp = Schema(canonical stmt) | Data(canonical stmt)
The op list is the source of truth. Human-readable SQL
(unreleased_sql(), current_canonical_schema(), HostMigrationStep::up_sql)
is derived by joining already-separated statements with ;\n for
diagnostics/export/tests. Apply, checksum, and backup walk the ordered ops.
Each MigrationOp is exactly one BookclerkSQL statement; Schema must be
admitted DDL and Data admitted DML, both proven through the SQL-v1
typechecker before adapter execution. A packer failure on static or imported
migration SQL fails closed (never checksumed as one opaque statement).
host_migration_plan() stays empty. Live schema is unreleased_ops
(mostly Schema, plus seed Data inserts). Checksums hash the
length-prefixed ordered statement list, not a joined script. db migrate --to <older> applies down only when every step has it; otherwise restore a
backup.
Invariants (locked with synthetic plans, not a v1 freeze):
- Integer version order for host schema; fail closed on contradictory
bookclerk_schema_migrationsrows. Plugin history uses opaque IDs and exact-prefixbookclerk_plugin_migrationsverification, not host numeric versions. - Retry uniqueness / duplicate-object / unavailable after re-read; matching
host version+checksum or plugin
(id, checksum)at the expected ordinal is success; FK / CHECK / NOT NULL are not races. - Crash: marker/journal row not visible ⇒ retry the same unit.
- Forward-only on library/binding open; rolling binaries fail closed on unknown newer frozen host state or plugin history that is not a prefix.
- Multi-node: portable
bookclerk_slots(schema:{namespace}for host;bookclerk_plugin_migrationsfor plugin suffix). Stale sessions re-check the plugin history digest at execute boundaries. - Backup capture includes the plugin journal and history digest; restore into a newer app stays fail-closed until registration/suffix apply is an ordinary open (not inside the restore transaction).
- Oldest frozen host revision is derived from
host_migration_plan()(Nonewhile the plan is empty). There is no independentMIN_SUPPORTED_SCHEMA_VERSIONconstant.
bookclerk db version|backup|restore|migrate uses
connect_without_migrate.
With an empty frozen plan, migrate --to is a no-op. Time-based
restore is how operators move independently of schema revision.
- Declaring production schema v1
- D1 Time Travel / portable PITR / canonical change journaling
- Transactional atomicity across library DB + independent plugin DBs
- Using semver as
bookclerk_schema_migrations.version
Plugins register a complete ordered history at startup
(databaseMigrations). Host library frozen steps remain a separate numeric
MigrationPlan engine. Plugin IDs are opaque; the host journal ordinal is
storage order only.
| Concern | Rule |
|---|---|
| Per-plugin / per-binding history | Private bookclerk_plugin_migrations journal in the binding DB (not bookclerk_schema_migrations, not namespace bookclerk). |
| Ordered progression | Registration order. Host proves BookclerkSQL with one evolving SqlTypeEnv. |
| Durable vs registered | durable history == prefix(current registered history); anything else fails closed. |
| DDL outside registration | Fail closed. Ordinary binding execute is DML/query only. |
| Multi-node concurrency | Each apply unit includes serialization-slot mutation + ops + journal append; re-read durable history on conflict or ambiguous result. No lock spans later independent transactions. |
| Retries / interrupted recovery | Uniqueness / unavailable after re-read; matching (id, checksum) at expected ordinal ⇒ success; absent row ⇒ retry same migration; contradictory history ⇒ fail closed. |
| Upgrade | Only a new suffix is pending. Stored history longer than registration ⇒ older plugin, fail closed. |
| Downgrade | Never. New forward migration or restore a recovery point. |
| Restore | Restore captured journal exactly. Do not migrate inside the restore transaction. Next ordinary open registers and may apply the suffix. |
| Stale session | Binding execute re-checks the complete history digest. |
Acceptance includes opaque IDs, evolving type environment, crash/retry, two nodes racing the same suffix, old-session fencing, edited/renamed/reordered history rejection, older-plugin rejection, backup → restore → suffix apply (SQLite / Postgres; D1 does not advertise backup flags).
- Fresh databases apply the unreleased pack once and record
unreleased@base0+<checksum>until a freeze exists. - Editing
unreleased_opsagainst an existing development DB is a hard fail + reset, not a silent reshape. - There is no incremental V2–V29 chain and no
migrations_legacymodule. Current-schema FK/UNIQUE coverage lives on the greenfield pack. - A later freeze can concatenate frozen ups + a new unreleased bucket
through
current_canonical_schema()without unwinding callers. - Schema-apply retries uniqueness / duplicate-object races and transient unavailability after re-reading durable state. Foreign key, CHECK, and NOT NULL failures are not schema-apply races.
Source: docs/adr/schema-versioning.md at main · View as Markdown