Database & migrations

Chorus uses an append-only, checksummed migration ledger. The numbered files in src/db/schema/ are migrations: each exact file is committed once, recorded, and skipped on later starts. Chorus currently supports SurrealDB 3.2.4 with surrealdb JavaScript SDK 2.0.4; Compose and CI pin those versions rather than floating on a major tag.

Startup sequence

After connecting, Chorus performs these steps before bootstrap, workers, or the HTTP listener:

1. Load every .surql file whose name matches NNN-lowercase-name.surql.
2. Require unique versions contiguous from 000, and hash the exact UTF-8 bytes with SHA-256.
3. Idempotently bootstrap schema_migration plus the singleton schema_migration_state:singleton (ledger format 2), transactionally persisting whether the pre-bootstrap database was an empty fresh_install or a populated legacy_adoption.
4. Require the database ledger to be an exact prefix of the binary catalog.
5. Before the first pending migration in 066–069, require the epoch-specific quiescence acknowledgement when the initial ledger belongs to an existing schema below 069.
6. Before pending migration 075 on an existing database, require its separate one-start quiescence acknowledgement.
7. Apply each pending whole file in its own transaction.

One migration transaction executes the file, compare-and-sets the singleton version, and creates its ledger row containing filename, checksum, app schema version, build commit, and timestamp. All three effects commit together or roll back together. Whole-file execution also means semicolons in comments or string literals are no longer split by application code.

Competing startup replicas write the same singleton and deterministic migration record. A verified SurrealDB write conflict retries the complete transaction with bounded full jitter; after one replica wins, the other observes the matching ledger row and skips it.

Fail-closed checks

Startup aborts before serving when any of these is true:

• a filename is malformed, duplicated, or leaves a version gap;
• an applied filename or checksum differs from the binary;
• the database ledger is not an exact prefix, is internally inconsistent, or is ahead;
• the ledger format is unsupported;
• migration SQL, schema coercion, permissions, or an explicit THROW fails;
• a domain/state conflict is not a verified retryable database write conflict.

Never edit or rename an applied file. A comment change changes its checksum.

Required quiescence for migrations 066–069

Migrations 066, 067, 068, and 069 install database-enforced graph, memory-admission, workstream-link admission, and approval-action integrity protocols. An old Chorus process does not write the new protocol fields, so allowing it to remain connected during this transition can create rows that the new binary must reject. The startup guard therefore fails closed whenever migrations 066–069 are pending, except for a runner whose initial ledger snapshot is both initialization_origin=fresh_install and current_version=-1. A populated pre-ledger database is durably classified as legacy_adoption, even though its first ledger version is also -1, and requires the acknowledgement before any numbered migration runs. A database originally installed fresh but later upgrading from 065, 066, 067, or 068 requires it as well.

Concurrent runners that both snapshot a completely unapplied fresh ledger may race safely. A lagging runner that first observes partial progress fails closed at the unsafe boundary and can be restarted after the winning runner records 069. Once 069 is recorded, restarts no longer need the acknowledgement.

The acknowledgement is an exact, non-secret operator assertion—not a password and not a mechanism that disconnects writers. Use this sequence:

1. Back up SurrealDB and record the current migration-ledger version.

2. Stop every old Chorus database writer: API replicas, embedded or standalone workers, dispatchers with direct storage access, cron/maintenance processes, and one-off scripts. Verify that no old Chorus process retains a SurrealDB connection. Keep SurrealDB itself running.

3. Set the following value on exactly the new replica that will perform the upgrade:

   CHORUS_MIGRATION_069_QUIESCENCE_ACK=I_HAVE_STOPPED_ALL_OLD_CHORUS_DB_WRITERS_FOR_MIGRATIONS_066_TO_069

   Any other non-empty value, including surrounding whitespace or a shortened confirmation, aborts configuration. A missing value aborts before the first unsafe schema transaction.

4. Start one new Chorus replica. Wait for the schema log to report Ledger current=69 (or newer) and for health checks to succeed before starting any additional new replicas. Review the migration-069 preflight counts first: fingerprint-valid terminal history may remain, but executable legacy gates without both a committed execution lease and a covering token TTL are snapshotted with their dependents in approval_lifecycle_quarantine. The migration never synthesizes those values; create a fresh gate after upgrade when the action is still needed.

5. Remove CHORUS_MIGRATION_069_QUIESCENCE_ACK from the environment and restart/scale the new replica set normally. Do not leave the assertion permanently configured or reuse it as a general migration bypass.

For Compose, put the exact value temporarily in the deployment .env, start only the chorus service at one replica after the old writers are down, confirm the ledger line, then delete the value from .env before scaling out. If startup aborts while the ledger remains below 069, keep old writers stopped, correct the problem, and retry the same bounded sequence. If a migration has committed, prefer forward repair; do not restart an old writer against the advanced schema.

Required quiescence for migration 075

Migration 075 changes Inbox receipts and the durable stream contract. Old Chorus writers must not write the affected tables while it commits. Back up SurrealDB, stop every old API, worker, dispatcher, maintenance job, and one-off process with a DB connection, then set this exact value on one new Chorus replica:

CHORUS_MIGRATION_075_QUIESCENCE_ACK=I_HAVE_STOPPED_ALL_OLD_CHORUS_DB_WRITERS_FOR_MIGRATION_075

Start only that replica and wait for Ledger current=75 plus a healthy readiness check. Then remove the variable and restart the new replica before scaling or reconnecting clients. Chorus rejects the acknowledgement when 075 is not pending, so it cannot be left in Compose as a standing bypass. If startup fails before 075 commits, keep old writers stopped, repair the cause, and retry the same bounded step.

First ledger adoption

On an existing pre-ledger database, ledger bootstrap atomically records initialization_origin=legacy_adoption. Because the current catalog includes the unsafe 066–069 transition, the quiescence acknowledgement is required before the runner applies even 000; a missing acknowledgement leaves the numbered ledger empty. With the guard satisfied, the binary applies and records the complete contiguous catalog in order. Migrations 000–040 are the one-time adoption baseline. Back up first and treat that start as a migration window. In particular:

• 024 performs its legacy memory-type normalization scans one final time;
• 025 clears wrong-dimension embeddings and rebuilds the 1024-dimensional HNSW index one final time;
• subsequent starts execute neither file.

The legacy DML returns NONE, so those scans do not materialize every changed record into the startup process. Re-populate cleared embeddings with bun run reembed -- --namespace <namespace> when required.

Fresh install, configured bootstrap, and upgrade

Path	Behavior
Fresh	Apply and record the full catalog, then zero-config creates the local admin only when no configured bootstrap exists and storage is empty.
Configured bootstrap	Migrations commit first. A missing, invalid, or unsuccessful CHORUS_BOOTSTRAP then aborts startup before listening.
Restart	Validate every recorded checksum and skip the exact applied prefix.
Upgrade	Back up, deploy code with only new highest-numbered files, and let startup commit pending migrations before accepting traffic.

For the executable Compose backup, restore, destructive confirmation, and disposable rollback drill, see Backup, restore & rollback.

Migration 041-enrollment.surql backfills and revokes every unredeemed pre-v2 invite, then enforces one org, one central membership, and the durable configured-bootstrap singleton. Legacy codes are intentionally not grandfathered; create a new invite after upgrading.

Migration 042: strict SIWE and credential inventory

Before applying 042-siwe-challenge.surql, startup emits a read-only inventory such as:

[schema] Preflight 042-siwe-challenge.surql: {"affected_api_keys":2,"quarantined_api_keys":1,"revoked_api_keys":1,"uppercase_wallet_rows":1,"wallet_casefold_collisions":0}

• affected_api_keys is every legacy key missing explicit kind or issued_via metadata.
• quarantined_api_keys and revoked_api_keys are the same fail-closed set: active expiring credentials with no trustworthy issuance provenance.
• uppercase_wallet_rows inventories identity, auth-request, and API-key wallet values that will be normalized to lowercase. wallet_casefold_collisions must be zero.
• If two identity rows collapse to the same lowercase wallet, startup aborts with the canonical wallet and conflicting identity IDs before mutation. Resolve ownership explicitly and retry; the migration never chooses an identity on the operator’s behalf.
• Permanent legacy keys remain active but are labeled kind=permanent, issued_via=legacy_migration.
• Unsafe expiring keys are revoked with a timestamp/reason and labeled kind=session, issued_via=legacy_revoked_by_siwe_042.

The migration then normalizes wallet-bearing rows, enforces lowercase identity/auth-request/key fields plus the unique identity-wallet index, creates the purpose-bound challenge table, deletes every legacy free-standing nonce, and removes the nonce table. Invite challenges persist only a domain-separated digest, SHA-256("chorus-invite-v2\\0" + code); raw codes are neither stored in the challenge nor echoed in errors.

Operational sequence:

1. Back up SurrealDB and capture the preflight line before the migration window. Stop if wallet_casefold_collisions is nonzero and resolve every listed identity pair first.
2. Notify owners that listed sessions will stop working immediately after 042 commits.
3. Reissue wallet sessions through POST /auth/challenge (purpose=login) followed by the exact signed message at POST /auth/siwe-login. Reissue non-wallet automation credentials through an authenticated admin key create/rotation workflow; do not un-revoke quarantined hashes.
4. Confirm new keys have explicit kind and issued_via, then verify the old key fails over both HTTP and stateless MCP.

If 042 has not committed, fix configuration/code and restart. If it has committed, a code rollback does not and must not reactivate revoked credentials or resurrect the nonce table. Prefer forward repair and reissue. The only full data rollback is restoring the pre-042 backup, which also rolls back every later write; use it only during a coordinated outage.

Migration 043: generation-bound leases

043-generation-leases.surql expands signal, task, run-attempt, and dispatcher rows with an immutable lease generation plus acquisition, heartbeat, and expiry timestamps. It also creates the durable lease_rollout_state:singleton operator record and append-only quarantine records. The migration deliberately does not invent generations or rewrite active legacy leases.

New acquisitions negotiate Chorus-Lease-Version: 1; an unnegotiated HTTP client receives 428 before storage is called. Every holder mutation must present the exact owner and generation minted for that acquisition. Reacquiring as the same identity produces a different generation, so a delayed heartbeat, ack, cancel, release, terminate, or dispatcher shutdown from the old process cannot affect replacement work. Expiry decisions use database time, and reapers compare a bounded snapshot of all lease fields before mutating.

Stop every old binary before quiescing

The durable quiesce flag is enforced by generation-aware servers and workers. An old server binary does not know that flag and can create a new legacy lease after inventory. Stop or upgrade all old API replicas, dispatchers, and workers before quiesce, and keep them stopped for the entire drain. Quiescing only the new replica is not a fence for an old process.

Use this resumable rollout after 043 is applied:

# 1. Back up the database; stop/upgrade every old binary and verify it is gone.
chorus admin leases inventory
chorus admin leases quiesce

# 2. Wait until the recorded wait_until. The protocol minimum is 14,400,000 ms (4 h).
# 3. Drain bounded pages until remaining is exactly zero; repeating is safe.
chorus admin leases drain --limit 100
chorus admin leases status

# 4. Verify, then enforce. Enforce performs a fresh legacy inventory.
chorus admin leases verify
chorus admin leases enforce

Acquisition is disabled from quiesced through verified and resumes only after enforced. drain cannot start before the durable wait_until, and a caller cannot configure a wait below the protocol maximum of four hours. Legacy active rows are terminalized into quarantine; they are never requeued and never assigned a guessed generation. If verify or enforce finds a legacy row, drain again or find the old process that recreated it. Each step advances a durable revision, so an operator can inspect status and resume after a crash without restarting the sequence.

Migration 046: federation v1 quarantine and v2 authority

Before applying 046-federation-v2.surql, startup inventories peer rows carrying legacy raw secrets, hashes, or scopes:

[schema] Preflight 046-federation-v2.surql: {"legacy_peers":1,"plaintext_secret_peers":1,"hashed_secret_peers":1,"scoped_peers":1}

The migration deliberately does not convert those rows into trusted v2 peers. In one transaction it copies only the old hash/scopes/expiry into metadata.federation_v1_quarantine, erases the raw secret and every v1 authority field, creates encrypted-key/trust/request/receipt tables, and adds dedicated signal provenance. If any DDL or DML fails, both quarantine and erasure roll back.

After commit, v1 calls cannot authenticate and code rollback must not be used to revive them. Create reciprocal v2 trust explicitly: one instance mints generation 1, the other installs it over an authenticated operator channel, and each stores only AES-GCM ciphertext. See Federation v2 for establishment and rotation. If immediate rollback is unavoidable, restore the coordinated pre-046 backup; that also discards all later writes.

Migration 047: authoritative task graph

Before applying 047-authoritative-task-graph.surql, startup inventories legacy blocked_by and blocks projections:

[schema] Preflight 047-authoritative-task-graph.surql: {"legacy_tasks":6,"candidate_dependencies":5,"valid_dependencies":2,"unsafe_dependencies":3,"missing_task_dependencies":1,"cyclic_task_dependencies":2,"graph_limit_exceeded_entries":0,"dependency_limit_exceeded_entries":0,"dependent_limit_exceeded_entries":0,"ambiguous_projection_entries":1}

Safe dependencies become durable task_dependency relations, up to 100 per dependent task. Missing, cyclic, invalid, cross-ring, duplicate, over-limit, and ambiguous reverse-projection entries go to task_dependency_quarantine; bounded traversal exhaustion uses graph_limit_exceeded, distinct from a proven cyclic_task_dependency. The migration then rebuilds every task projection from the valid relations and records graph revision 1. DDL, quarantine, backfill, and projection rebuild share one transaction. If any step fails, the ledger remains at 046 and legacy arrays remain unchanged.

Capture the preflight metrics and a database backup before rollout. Investigate every quarantine row after commit rather than manually restoring array fields: those arrays are read-only generated projections under the new runtime.

Migrations 049, 051, and 054: federated receipts and admission fencing

049-federation-lease-receipts.surql adds the exact opaque lease generation to persisted request and receipt rows. A federated peer must replay that signed generation on holder operations; it is never reconstructed from identity or timestamps.

051-federation-claim-admission-fence.surql expands request outcomes with provisional, makes the receipt optional until reconciliation, records finalization time, and creates the always-present lease_acquisition_fence:singleton used by the first crash-safe admission protocol.

054-striped-lease-acquisition-fence.surql expands that serialization point into 128 fixed stripes. A successful native or federated acquisition deterministically mutates one stripe in the same transaction as its resource CAS; losing CAS attempts do not touch a stripe. Every rollout phase transition mutates all stripes plus the legacy singleton atomically. Quiescence therefore conflicts with an acquisition admitted against an older phase—even before an inventory row exists—without forcing unrelated acquisitions through one global write hotspot.

A provisional row is deliberately not a successful replay result. After restart, the exact retry reconciles the persisted peer/signal/generation and produces one signed final receipt. If rollout quiescence or lease expiry won, reconciliation releases only the provisional generation and stores a rejection. Do not delete provisional rows manually: they are durable recovery state, and an operator should investigate any that remain old enough to exceed the configured claim TTL.

Migration 060: idempotent federation delivery

060-federation-idempotent-delivery.surql gives each delivered intent a content and intent digest, keeps exact- and idempotent-replay counters separate, and permits multiple proof-specific receipts for one stable request intent. A caller-selected idempotency key is scoped to the authenticated peer and principal: the same key plus the same canonical intent can safely recover after an ambiguous transport failure, while a changed intent is rejected. Each fresh valid signed proof still receives its own proof-bound receipt. Pre-060 rows have no intent digest and therefore remain exact-replay-only.

Migration 061: object-storage reconciliation

061-object-storage-reconciliation.surql adds durable pre-write guards and generation-fenced operation leases for uploads and deletes. An object write is prepared in SurrealDB before provider I/O; metadata settlement and retry state survive process failure. Artifact deletion and tiered memory-body deletion retain explicit pending markers until physical absence is confirmed. Memory body keys include an immutable revision so a later content revision never aliases an earlier body.

Provider inventory is observational and fail-closed. Keys are classified as referenced, guarded, or quarantined across repeated scans; an unknown or legacy key is never deleted merely because no current database reference was found. Do not manually remove active guards or quarantined keys. Investigate the operation row and provider inventory, repair ownership/reference state, and let the reconciler settle it with the recorded lease generation.

Quiesce and drain every old writer before rolling out code that uses 061. Every provider mutation must create its guard before provider I/O; mixed old/new writers weaken the evidence available to reconciliation. Upload grace is five minutes, and a guarded PUT is abort-bounded to four minutes so it remains at least 30 seconds inside that grace period. Delete grace is 30 seconds, reconciliation dead-letters after eight failed attempts, and custom storage providers must honor AbortSignal.

The reconciler deletes only operation-owned unreferenced keys, verifies absence before terminal settlement, and does not treat committed/completed history as an active guard. Quota authority, protocol-version fencing, and durable inventory checkpoints are introduced by migration 063.

Migration 062: durable provider work

062-durable-provider-work.surql moves embedding and LLM-extraction intent, attempt count, retry time, lease ownership, and terminal state into SurrealDB. Memory creation and content revision write the relevant provider intents atomically with the memory mutation. Workers claim one bounded item with a fresh immutable generation; stale replicas cannot settle a replacement lease. Content drift supersedes obsolete work, malformed provider payloads dead-letter, and retryable transport failures use bounded backoff. LLM-derived memories and relations use deterministic IDs, so replacement workers can repeat settlement without creating duplicates.

The admin health snapshot exposes fixed-cardinality counts for both work kinds. Treat dead_letter > 0, expired leases, or a sustained pending + retry_wait increase as operational signals. Terminal rows are recovery/audit evidence; do not delete them as a way to retry work.

Migration 063: object-storage quota authority and protocol fence

Migration 063-object-storage-quota.surql moves quota and unresolved-operation admission from process-local reconstruction to SurrealDB-owned global and namespace authority rows. Each protocol-v1 upload reserves bytes and unresolved capacity before provider I/O; settlement consumes the exact reservation atomically with metadata. Pre-063 active operations become immutable protocol-v0 dead-letter tombstones with accounting retained.

A database-clock, generation-fenced inventory lease persists each prefix cursor and commits page observations with its checkpoint in one transaction. Another replica can resume an incomplete scan without treating it as absence evidence. Inventory is observational: unknown objects are quarantined, never deleted merely because the current database has no reference.

An ambiguous upload tombstone remains charged even when a cleanup attempt observes provider absence, because a timed-out PUT can arrive late. A referenced delete dead letter instead recovers through an exact type-aware successor guard: metadata and unresolved ownership transfer atomically, provider deletion remains abort-bounded, and quota is released only after verified absence and generation-fenced settlement. Repeating that recovery is idempotent. Never delete guards, reservations, unresolved rows, inventory checkpoints, or tombstones manually.

After 063 commits, rolling back code alone is unsafe. The required, no-default upload_protocol_version fence makes 061/062 writers fail before starting new provider I/O, and protocol-v0 rows are immutable. Prefer forward repair. A full rollback requires a coordinated restore of the pre-063 database backup and matching object-store state, discarding every later write.

Migration 064: durable memory administration jobs

Migration 064-durable-memory-admin-jobs.surql adds bounded durable import/export jobs, immutable input/output chunks, 256 import lock stripes, generation-fenced leases and checkpoints, exact output line/byte metadata, and database-owned capacity counters. It also upgrades any active legacy ID-only re-embed scan to a fresh compound (created_at,id) database-clock snapshot.

Snapshot membership is fixed at admission; memory bodies and relations are read when a job processes each member, so an export is not a historical byte-for-byte snapshot. Job ownership, attempts, paging metadata, and terminal results survive restart. Use the admin job surfaces to resume or inspect work; do not edit chunks, checkpoints, counters, or lease fields directly.

Migration 065: durable delete-recovery links

Migration 065-object-storage-delete-recovery.surql records each immutable delete tombstone’s successor, root source, and eventual terminal operation. Resolution propagates through the durable link chain before older source rows are eligible for bounded retention, preserving recovery proof without keeping every ancestor operation forever.

Migration 068: bounded workstream links

Migration 068-workstream-link-capacity.surql adds one internal serialization authority per workstream and requires admission protocol v1 on every new link. Existing links are marked as legacy v0 rows and remain deletable. New statements issued by an older client after commit cannot create links, but a transaction already in flight can retain its pre-migration schema view. That is why the rollout procedure requires every old writer to be stopped before migration rather than relying on the field/event fence alone.

The first v1 link mutation lazily reconciles the indexed durable link count into the authority. At or above capacity, new links fail closed. Unlinking repairs the count transactionally and releases capacity one row at a time. Context reads independently fail on the 513th descriptor rather than truncate, protecting reads if legacy/manual writes left an over-capacity corpus.

Adding a migration

• Add the next highest contiguous migration number; never reuse or skip a number.
• Do not modify a migration that any environment may have applied.
• Put related DDL and DML in the same file when they must commit atomically.
• Prefer expand → backfill → contract across releases for destructive shape changes.
• Bump CHORUS_SCHEMA_VERSION; CI’s schema-version guard enforces this.
• Run unit catalog tests and required SurrealDB 3.2 integration tests. InMemory ledger tests prove orchestration only, not SCHEMAFULL enforcement or database contention.

Rollback

Migrations are forward-only. Chorus never deletes ledger rows and does not run automatic down migrations.

• A code rollback is safe only while the applied changes are backward-compatible.
• Destructive changes require a staged expand/backfill/contract rollout.
• If an incompatible migration committed, restore the pre-migration database backup.

The integration gate proves restart skip, failed DDL/DML rollback, correction of an unapplied file, two-runner contention, checksum drift rejection, and one-time application of the full catalog against a real pinned SurrealDB process.