sithbit-migrate: moving a store between backends

sithbit-migrate copies an existing SithBit store into another one — the tool you run to graduate a single-box SQLite deployment onto a cloud backend (DynamoDB + SQS + S3, Azure Tables + Queues + Blob, Postgres — including Cloud SQL, with GCS as the S3 bucket — Turso, or Cloudflare) without losing a single account, message, or queued job.

It is a one-shot, offline copy, not a live replicator: point it at a quiescent source (stop sithbitd and account-api first), let it run to completion, then bring the servers back up against the new store.

What it moves

In one pass, in this order:

1. Accounts — every wallet row: timezone, the sealed mail-password secret (copied verbatim — see the caveats), the do-not-disturb exclusion set and its exposure opt-in, the outbound suspend flag, and the rolling outbound-usage totals (as observed at the migration instant — see the caveats).
2. Mailboxes and messages — the full mailbox tree per wallet and every message copy, each carrying its exact chain-pipeline state (received / pinned / sent / settled / no_key / chain_failed / local) plus the cid and on-chain message id it had reached.
3. Blob bytes — every stored message body, byte-for-byte, including any orphaned blob referenced by no message.
4. The job queue — outstanding live jobs are drained onto the target, and dead-lettered jobs are re-enqueued there as fresh live work.

Configuration: two stores in one file

Unlike every other binary — which carries a single [store] section — the migrator reads one store and writes another, so it holds two independent StoreConfigs under [source] and [target]. Both inherit the same dev-friendly defaults, so an empty file migrates the local SQLite store onto itself (a harmless no-op).

The config resolves through the standard layering: in-code defaults → sithbit_migrate.toml (or the file named by SITHBIT_MIGRATE_CONFIG) → .env → .env.$APP_ENV → real environment. Individual settings override from the environment as SITHBIT_MIGRATE_{PATH} with __ per nesting level — e.g. SITHBIT_MIGRATE_TARGET__KIND=aws, SITHBIT_MIGRATE_TARGET__AWS__TABLE=sithbit-prod.

A SQLite-to-AWS sithbit_migrate.toml:

# The store you are leaving (typically the local SQLite one).
[source]
kind = "sqlite"
database = "sithbit.db"
credential_key_file = "credential.key"
[source.blobs]
kind = "local"
path = "blobs"

# The store you are moving to.
[target]
kind = "aws"
# The same credential key MUST come across (see caveats).
credential_key_file = "credential.key"
[target.aws]
region = "us-east-1"
table = "sithbit"
queue_prefix = "sithbit"
[target.blobs]
kind = "s3"
endpoint = "https://s3.us-east-1.amazonaws.com"
bucket = "sithbit-mail"
access_key = "…"
secret_key = "…"

The target’s tables, queues, and lease store are created idempotently at open time, exactly as they are when a server first boots against them. The one exception is an S3 blob bucket: like the servers, the migrator assumes an S3 bucket already exists (kind = "azure" blob containers are auto-created; S3 buckets — including GCS buckets used through the interop endpoint — are not). Create the bucket before you run.

Dry-run, then commit

The migration only happens with --commit. Without it, the tool does a dry run: it opens both stores, reads and enumerates the entire source (proving every field, mailbox, blob, and job is reachable), and writes nothing to the target. Use it to validate connectivity and credentials, and to see the counts before you commit:

# 1. Rehearse. Opens both stores, touches nothing on the target.
sithbit-migrate

# 2. For real. Copies everything.
sithbit-migrate --commit

Both modes print a one-line summary of what was seen (dry-run) or moved (commit):

source=Sqlite target=Aws mode=commit, accounts: 1 (0 suspended), mailboxes: 2, \
messages: 3, blobs: 4 (119 bytes), jobs: 2 live, 1 dead re-enqueued (0 skipped)

The account/mail/blob copies are idempotent — a re-run overwrites rather than duplicates — so a migration interrupted partway can simply be re-run. The job-queue drain is the one exception (it consumes the source); see the caveats.

v1 caveats — read before you commit

This is a faithful data copy, not a perfect clone. The known, deliberate lossy points for the first version:

• The credential key is not re-sealed. The sealed mail secret is copied as ciphertext, still encrypted under the source’s credential.key. That key file must be carried to the new deployment and configured on the target, or every stored mail password is orphaned and users must set new ones. Back it up first, migrate it alongside the store.
• Timestamps are not preserved. created_at / updated_at metadata is reset to the migration time on the target; message internaldate and chain state are preserved, account/row bookkeeping timestamps are not.
• Outbound-usage bucket timing is not preserved. The suspend flag comes across verbatim, and the rolling hour/day outbound-recipient totals are replayed as observed at the migration instant — but the underlying per-hour buckets are not visible through the store surface, so on the target the totals age relative to the migration time rather than the original send times. Practical effect: a sender’s remaining allowance is right at cutover, and the copied usage rolls off within the following hour/day windows.
• UID identity is reallocated. Each mailbox is rebuilt fresh, so its uidvalidity and uidnext are assigned anew on the target. Message UIDs are re-issued in ascending order (preserving relative order), but a client’s cached UIDs from the old store are invalidated — expect clients to resync, exactly as they would after a uidvalidity bump.
• Dead-lettered jobs come back as live. Each dead job’s payload is re-enqueued as a fresh live job on the target; its dead status, failure reason, and attempt history are not carried over (full dead-letter fidelity was declined for v1). A dead payload that no longer deserializes is counted “skipped” and left on the source, not moved.
• The live-job drain is at-least-once, and assumes a quiescent source. Live jobs are consumed from the source and enqueued on the target; a crash mid-move re-drives a job rather than dropping it, so a job caught in flight can land on the target twice (the workers tolerate a duplicate). This only holds if no workers are running against the source during the migration — stop the source’s servers first.
• Wallets with mail but no account row are skipped. Enumeration is driven by the account table, so a stray mailbox/message belonging to a wallet that has no account row is not seen. In a healthy store every mailbox has an owning account, so this affects only orphaned rows.
• Login nonces are not migrated. They are single-use, short-TTL login challenges with no value after a cutover; in-flight logins simply retry.

Runbook

1. Back up the source, especially credential.key.
2. Stop sithbitd and account-api against the source store.
3. Provision the target’s prerequisites (S3 bucket if using one; credentials/region for the backend).
4. Write sithbit_migrate.toml with [source] and [target], carrying the credential key across.
5. sithbit-migrate (dry run) — confirm the counts and that both stores open.
6. sithbit-migrate --commit.
7. Repoint sithbitd / account-api [store] at the new backend and start them.
8. Verify (log in, list mail, watch the chain workers drain), then retire the old store.