Migrate from SQLite¶

Purpose¶

Move an installation that still runs a SQLite release onto the Postgres release. Earlier releases kept their data in two SQLite files, the control store (DATABASE_PATH) and the application store (APPLICATION_DATABASE_PATH). The current release keeps both sets of tables in one PostgreSQL database at DATABASE_URL. This one-time procedure copies the two files into a freshly migrated database with verified row counts. After that, the Postgres release is deployed against the database.

The import only reads copies of the SQLite files. The live files stay where they are as the source of a rollback to the SQLite release.

The procedure runs in three places:

On the host, as the service account: stopping the services, the SQLite backups and shared.env.
On the host, as the Postgres superuser: creating the role and the databases.
On your workstation: a machine with a checkout of the Postgres release. The host runs only prebuilt release artifacts, because building from source there can run out of memory. The migration and the import therefore run from the workstation, through an SSH tunnel to the host's Postgres, and the deployment starts from the workstation as for every release.

Prerequisites¶

A maintenance window. Every Mission Control service is stopped from step 5 until the Postgres release is started, and nothing writes to the SQLite files in between.
The SQLite stores come from the last SQLite release, the one the installation runs now. The import compares every table and column with the Postgres schema and refuses a difference.
PostgreSQL 16 or newer on the host, reachable from the services. On the reference VPS it runs on the same host and listens on 127.0.0.1:5432.
Access to the Postgres superuser on the host (for example sudo -u postgres psql) to create the role and the databases. The service account needs no Postgres privilege beyond owning its database; the deploy user needs no sudo.
sqlite3 on the host, and psql, pg_dump and pg_restore of the server's major version: the deployment helper refuses client tools of another major version.
On the workstation:
- A checkout of the release you will deploy. Merge the Postgres release into main and push it: the deployment requires local HEAD to match origin/main, as described in Deploy to an existing VPS. Merge it with a merge commit or a fast-forward, not a squash, because the first Postgres deployment needs an earlier Postgres commit as its fallback (step 1).
- Node.js 24 or newer and pnpm 11.22.0. The import reads SQLite through Node's built-in node:sqlite module, which needs Node.js 24.
- psql 16 or newer, and ssh and scp access to the host as the service account.
- Docker, for the release artifact builds described in Deploy to an existing VPS.
- python3, only if a value must be repaired (see Repair a refused value).
Write access to the service configuration in ~/.config/mission-control/ (shared.env and the per-service environment files) and to the service account's systemd user units.
Free space:
- On the host's Postgres volume: at least three times the combined size of the two SQLite files, plus about 1 GB. This covers the imported tables and indexes and the write-ahead log of the single import transaction.
- On the host's backup filesystem: the size of the two SQLite files for their copies, plus what the deployment helper needs for its pg_dump backup (at least 1 GiB beyond the database size).
- Under the host's release directory: room for the candidate and fallback releases, which the deployment helper's preflight checks. Before the cutover, remove release directories you no longer need under /home/mission-control/mission-control/releases/, but keep the SQLite release that runs now (readlink -f /home/mission-control/mission-control/current prints it): the rollback to SQLite starts it again.
- On the workstation: the size of the two SQLite files for the copies.

This guide uses these fictional values:

Item	Value
Role and database	`mission_control`
Check database for the rehearsal	`mission_control_import_check`
Password	`replace-with-a-generated-password`
SSH host alias of the service account	`mission-control-vps`
SQLite files	under `/home/mission-control/.local/share/mission-control/data/`
Backup directory on the host	`/home/mission-control/.local/share/mission-control/backups/pre-postgres-20260924T090000Z`
Directory for the copies on the workstation	`$HOME/mission-control-cutover-20260924`
Local tunnel port	`15432`

Before invoking the deployment helper, set all explicit deployment settings in the workstation shell. Replace the fictional SSH alias and service-account paths in this guide with the same installation values.

Read the real SQLite paths on the host from the current shared.env:

grep DATABASE_PATH "$HOME/.config/mission-control/shared.env"

Expected result: the DATABASE_PATH and APPLICATION_DATABASE_PATH lines. Use those paths wherever this guide shows the fictional ones.

Procedure¶

Steps 1–4 prepare the cutover while the SQLite release keeps serving. Steps 5–13 are the maintenance window.

1. Prepare the workstation and the release artifacts¶

Working directory: the repository root of the workstation checkout.

Bring the checkout to the release you will deploy:

git switch main
git pull --ff-only origin main

The first Postgres deployment needs a fallback artifact that is itself a Postgres release. The deployment helper stages the fallback beside the candidate and checks that it loads the pg driver. The helper also refuses a fallback that is not an ancestor of the candidate, or whose release directory already exists on the host. The SQLite release that runs now cannot be the fallback. Use the newest earlier commit of the Postgres release: the second parent of a merge commit, or the parent of the candidate after a fast-forward. Such a commit exists on main only if the Postgres release was merged with its history; a squash merge leaves no earlier Postgres commit to use. Then build both artifacts now: each build takes several minutes and needs no database.

CANDIDATE_COMMIT="$(git rev-parse HEAD)"
FALLBACK_COMMIT="$(git rev-parse --verify --quiet "$CANDIDATE_COMMIT^2" || git rev-parse "$CANDIDATE_COMMIT~1")"
git diff --quiet "$FALLBACK_COMMIT" "$CANDIDATE_COMMIT" -- platform/packages/database/drizzle-pg &&
  echo "fallback $FALLBACK_COMMIT has the candidate's migrations"
ssh mission-control-vps test ! -e "/home/mission-control/mission-control/releases/$FALLBACK_COMMIT" &&
  echo "fallback release directory is free"
RELEASE_REF="$FALLBACK_COMMIT" RELEASE_OUTPUT_DIR=/private/tmp/mc-fallback scripts/build_release_artifact.sh
RELEASE_REF="$CANDIDATE_COMMIT" RELEASE_OUTPUT_DIR=/private/tmp/mc-candidate scripts/build_release_artifact.sh

Expected result: both echo lines print, and each build ends with Verified artifact: and Manifest: lines. If the first echo line is missing, the fallback is not a Postgres release with the candidate's migrations; do not continue until you have a fallback that passes both checks. Keep CANDIDATE_COMMIT and FALLBACK_COMMIT for step 12.

Install the release's dependencies. The Postgres release adds the pg driver, so install again after pulling it:

cd platform
pnpm install --frozen-lockfile

2. Create the role and the databases as the superuser¶

Working directory: any, on the host, as a user who can reach the Postgres superuser. Generate a password without characters that need escaping in a URL, for example with openssl rand -hex 32, then run:

sudo -u postgres psql

create role mission_control login password 'replace-with-a-generated-password';
create database mission_control owner mission_control template template0 locale_provider libc lc_collate 'C' lc_ctype 'C';
create database mission_control_import_check owner mission_control template template0 locale_provider libc lc_collate 'C' lc_ctype 'C';

Expected result: CREATE ROLE and two CREATE DATABASE lines. The libc C collation and character type keep text ordering bytewise and lower() ASCII-only, as they were on SQLite. db:migrate and the import refuse a database created otherwise, including one with C.UTF-8, before writing anything. The role owns both databases and their public schemas and needs no other privilege. To keep the password out of the superuser's psql history, create the role without it and set it with \password mission_control. mission_control_import_check serves only the rehearsal of step 4.

Recommended: make room in the server's lock table for a later database restore. scripts/rollback_vps.sh --restore-database replaces the whole database in one transaction, which holds about 6,700 locks, close to the default capacity of about 7,800. As the superuser, run:

alter system set max_locks_per_transaction = 256;

Then restart PostgreSQL, for example with sudo systemctl restart postgresql, and check the value with show max_locks_per_transaction;, which prints 256. The restart does not affect the SQLite release, which does not use Postgres. Without the change, a restore that overflows the lock table fails and leaves the database unchanged.

3. Connect the workstation to the database¶

Working directory: any, on the workstation. Every later step that runs on the workstation needs this tunnel and these settings. In a separate terminal, open the tunnel and leave it running:

ssh -N -L 15432:127.0.0.1:5432 mission-control-vps

It prints nothing while it runs. Keep it open while you run step 4 and steps 8–10; stop it with Ctrl-C when you are done, and open it again the same way when you need it. If it reports bind: Address already in use, choose another local port and use it as PGPORT below.

In the terminal where you run the database commands, load the connection settings. read -rs waits for the password without echoing it: paste it and press Enter. The password then stays out of the shell history and off every command line.

export PGHOST=127.0.0.1 PGPORT=15432 PGUSER=mission_control PGDATABASE=mission_control
read -rs PGPASSWORD
export PGPASSWORD
export DATABASE_URL="postgres://$PGUSER:$PGPASSWORD@$PGHOST:$PGPORT/$PGDATABASE"
psql -X -Atc 'select current_user, current_database()'

Expected result: mission_control|mission_control. psql reads the PG* variables; db:migrate and import-sqlite read DATABASE_URL. A hexadecimal password needs no percent-encoding in the URL. The commands work in bash and zsh. Close this terminal after the cutover, or run unset PGPASSWORD DATABASE_URL.

4. Rehearse the import against the check database¶

This step is recommended. A dry run on fresh copies finds any value the import refuses, and measures the import's time and memory, while the SQLite release still serves. Correct what it reports before the window, as described in Failure recovery.

On the host, as the service account, copy the running stores with SQLite's backup command, which takes a consistent snapshot while the services run:

rehearsal=/home/mission-control/.local/share/mission-control/backups/sqlite-rehearsal-20260923
mkdir -m 700 "$rehearsal"
sqlite3 /home/mission-control/.local/share/mission-control/data/mission-control.sqlite \
  ".backup '$rehearsal/mission-control.sqlite'"
sqlite3 /home/mission-control/.local/share/mission-control/data/mission-control-applications.sqlite \
  ".backup '$rehearsal/mission-control-applications.sqlite'"

On the workstation, copy them:

mkdir -m 700 "$HOME/mission-control-rehearsal-20260923"
scp "mission-control-vps:/home/mission-control/.local/share/mission-control/backups/sqlite-rehearsal-20260923/*.sqlite" \
  "$HOME/mission-control-rehearsal-20260923/"

With the tunnel of step 3 open, load the connection settings of step 3 with PGDATABASE=mission_control_import_check instead of mission_control. Then, from platform/ of the workstation checkout, migrate the check database and run the import as a dry run:

pnpm --filter @mission-control/database db:migrate
pnpm --filter @mission-control/database import-sqlite --dry-run \
  --control "$HOME/mission-control-rehearsal-20260923/mission-control.sqlite" \
  --application "$HOME/mission-control-rehearsal-20260923/mission-control-applications.sqlite"

Remove the rehearsal: as the superuser, run drop database mission_control_import_check;. On the host, run rm -r /home/mission-control/.local/share/mission-control/backups/sqlite-rehearsal-20260923. On the workstation, run rm -r "$HOME/mission-control-rehearsal-20260923". The copies hold the stores' secrets.

Expected result: Applied 3 migration(s); 3 recorded in total., then the import output described in step 9. A storage_collation_refused message instead means that the check database was not created as in step 2; see Failure recovery. The dry run performs the whole import inside one transaction and rolls it back. Its checked line ends with (dry run: rolled back at the end) and its total: line with (dry run: rolled back, nothing written). Its exit status is 0. Plan the window from the elapsed: line.

5. Stop the services¶

Working directory: any, on the host, as the service account.

systemctl --user stop mission-control-console.service mission-control-agent-runtime.service \
  mission-control-collector.service mission-control-gateway.service
systemctl --user is-active mission-control-gateway.service mission-control-console.service \
  mission-control-agent-runtime.service mission-control-collector.service

Expected result: four inactive lines. Stop every other process that writes to the SQLite files too, such as a bootstrap or credential-rotation command.

6. Back up the SQLite stores, the environment files and the release path¶

Working directory: any, on the host, as the service account. Make consistent single-file copies with SQLite's backup command. Never use cp on a live store: a copy without its -wal file silently loses committed data.

backup=/home/mission-control/.local/share/mission-control/backups/pre-postgres-20260924T090000Z
mkdir -m 700 "$backup"
sqlite3 /home/mission-control/.local/share/mission-control/data/mission-control.sqlite \
  ".backup '$backup/mission-control.sqlite'"
sqlite3 /home/mission-control/.local/share/mission-control/data/mission-control-applications.sqlite \
  ".backup '$backup/mission-control-applications.sqlite'"
sqlite3 "$backup/mission-control.sqlite" 'pragma integrity_check;'
sqlite3 "$backup/mission-control-applications.sqlite" 'pragma integrity_check;'
sha256sum /home/mission-control/.local/share/mission-control/data/mission-control.sqlite \
  /home/mission-control/.local/share/mission-control/data/mission-control-applications.sqlite > "$backup/live-sqlite.sha256"
(cd "$backup" && sha256sum mission-control.sqlite mission-control-applications.sqlite > backup-sqlite.sha256)
for name in shared gateway runtime collector console; do
  install -m 600 "$HOME/.config/mission-control/$name.env" "$backup/$name.env"
done
readlink -f /home/mission-control/mission-control/current > "$backup/previous-release"

The checksums of the live files are taken after the copies on purpose. When a store still has committed transactions in its -wal file, for example after a crash, the backup command writes them into the main file as it closes.

Expected result: each integrity check prints ok. The backup directory holds:

both copies and their checksums (backup-sqlite.sha256, with names relative to the directory);
the checksums of the live files (live-sqlite.sha256);
the previous shared.env, with the SQLite paths, and the four per-service environment files;
the path of the running SQLite release (previous-release).

The pre-Postgres release cannot run once shared.env holds only DATABASE_URL. Before the cutover, keep copies of the SQLite files and of the previous environment files in the backup directory; a rollback to that release restores the previous shared.env, and a SQLite file from its copy only if the live file changed, before the previous release is started.

7. Copy the SQLite copies to the workstation¶

Working directory: a new directory on the workstation.

mkdir -m 700 "$HOME/mission-control-cutover-20260924"
cd "$HOME/mission-control-cutover-20260924"
scp "mission-control-vps:/home/mission-control/.local/share/mission-control/backups/pre-postgres-20260924T090000Z/*.sqlite" \
  "mission-control-vps:/home/mission-control/.local/share/mission-control/backups/pre-postgres-20260924T090000Z/backup-sqlite.sha256" .
shasum -a 256 -c backup-sqlite.sha256

Expected result: mission-control.sqlite: OK and mission-control-applications.sqlite: OK. On a Linux workstation, sha256sum -c works as well. Import from these copies, never from the live files. The copies in the host's backup directory stay untouched as the rollback's safety copy.

8. Migrate the database¶

Working directory: platform/ of the workstation checkout, with the tunnel and the settings of step 3 (PGDATABASE=mission_control).

pnpm --filter @mission-control/database db:migrate

Expected result: Applied 3 migration(s); 3 recorded in total. The count grows with later releases; the database is current when the command applies nothing new on a second run. The command first checks the database's collation, character type and locale provider, and refuses with storage_collation_refused a database not created as in step 2, before applying anything; see Failure recovery.

9. Run the import¶

Working directory: platform/ of the same checkout, with the same settings. Pass absolute paths: the command runs from packages/database through pnpm --filter, so a relative path resolves against that directory. The tool reads the target from DATABASE_URL. Do not use its --url option, which would put the password on the command line.

pnpm --filter @mission-control/database import-sqlite \
  --control "$HOME/mission-control-cutover-20260924/mission-control.sqlite" \
  --application "$HOME/mission-control-cutover-20260924/mission-control-applications.sqlite" \
  2>&1 | tee "$HOME/mission-control-cutover-20260924/import.log"

The whole import is one transaction. The tool first checks everything it can check without writing: the files, the target database, the table and column sets, orphan rows, every value and every JSON document. Then it copies every table and compares each table's row count with SQLite. It commits only when every count matches. A refusal or a failure writes nothing, with the one exception described under Failure recovery.

Expected result: pnpm echoes the command it runs, then the tool prints lines like these. The counts, times and paths come from your stores; this example is a small test installation:

control: /Users/operator/mission-control-cutover-20260924/mission-control.sqlite (journal_mode=wal; -wal absent; -shm absent)
application: /Users/operator/mission-control-cutover-20260924/mission-control-applications.sqlite (journal_mode=wal; -wal absent; -shm absent)
checked 148 tables, 92 rows: no orphan rows, every value converts; copying in one transaction
agent_definition_content: sqlite=1 postgres=1 ok
agent_test_evidence: sqlite=0 postgres=0 ok
…
virtual_key_secret_version: sqlite=2 postgres=2 ok
total: 148 tables, sqlite=92 postgres=92 (committed)
elapsed: 0.9s, peak memory 203 MB

The two source lines show each file's journal mode and any -wal or -shm file beside it. There is one ok line per table, and peak memory is the tool's maximum resident memory. The tool exits with status 0; 1 means refused or failed, and 2 means a usage error such as a missing --application. Through tee, read the last lines of the log rather than $?. On a failure, pnpm adds an ERR_PNPM_RECURSIVE_RUN_FIRST_FAIL line after the tool's message; the message above it names the cause.

Keep the log with the host's backups:

scp "$HOME/mission-control-cutover-20260924/import.log" \
  "mission-control-vps:/home/mission-control/.local/share/mission-control/backups/pre-postgres-20260924T090000Z/"

If you skipped step 4, you can first run the same command with --dry-run. Then run psql -X -c 'vacuum' before the import: the rolled-back rows of a dry run stay behind as dead space until vacuumed.

In a built checkout, node --disable-warning=ExperimentalWarning dist/import/cli.js, run with the same arguments from platform/packages/database, runs the same tool without pnpm. Without that flag, Node prints ExperimentalWarning: SQLite is an experimental feature, which is harmless.

10. Verify the database¶

Working directory: platform/ of the same checkout, with the same settings.

pnpm --filter @mission-control/database db:migrate
psql -X -Atc 'select count(*) from organization'
psql -X -Atc "select datcollate, datctype from pg_database where datname = current_database()"
psql -X -c 'analyze'

Expected result:

Applied 0 migration(s); 3 recorded in total.
The number of Organizations the SQLite release served.
C|C.
ANALYZE, after lines such as WARNING: permission denied to analyze "pg_authid", skipping it. The warnings are expected: the database owner cannot analyze the server-wide system catalogs. analyze gives the planner statistics for the freshly loaded tables before the services start.

A second import is refused because the database is no longer empty, which protects the imported rows. Stop the tunnel of step 3.

11. Set `DATABASE_URL` in `shared.env`¶

Working directory: any, on the host, as the service account. The SQLite paths stayed in shared.env until now. Until this step, you could abandon the cutover and start the SQLite release again unchanged.

Edit ~/.config/mission-control/shared.env. Remove the DATABASE_PATH and APPLICATION_DATABASE_PATH lines entirely; an empty assignment still counts as defined, and the deployment helper and the service start guard refuse it. Add:

DATABASE_URL='postgres://mission_control:replace-with-a-generated-password@127.0.0.1:5432/mission_control'

Keep the password in the user part of the URL. Percent-encode any reserved character in it. A password= query parameter is refused. Single-quote the value and put no comment on the line: the deployment helper, the start guard and systemd each read the file with their own parser. Remove DATABASE_PATH and APPLICATION_DATABASE_PATH from any per-service environment file that still defines them. Then restrict shared.env and check the files:

chmod 600 "$HOME/.config/mission-control/shared.env"
grep -E '^(export )?(DATABASE_PATH|APPLICATION_DATABASE_PATH)=' "$HOME"/.config/mission-control/*.env

Expected result: grep prints nothing. From here on, the SQLite release cannot start without the rollback below.

12. Deploy the Postgres release¶

Working directory: the repository root of the workstation checkout. Deploy the two artifacts of step 1 with the existing-VPS helper, as described in Deploy to an existing VPS. In a new terminal, set CANDIDATE_COMMIT and FALLBACK_COMMIT again with the commands of step 1.

DEPLOY_RELEASE_ARTIFACT="/private/tmp/mc-candidate/release-$CANDIDATE_COMMIT.tar.gz" \
DEPLOY_RELEASE_MANIFEST="/private/tmp/mc-candidate/release-$CANDIDATE_COMMIT.json" \
DEPLOY_FALLBACK_ARTIFACT="/private/tmp/mc-fallback/release-$FALLBACK_COMMIT.tar.gz" \
DEPLOY_FALLBACK_MANIFEST="/private/tmp/mc-fallback/release-$FALLBACK_COMMIT.json" \
DEPLOY_PYTHON_BIN=/home/mission-control/.local/share/uv/python/cpython-3.14.7-linux-x86_64-gnu/bin/python3.14 \
scripts/deploy_vps.sh

The helper backs up the imported database with pg_dump, runs db:migrate (nothing to apply), verifies that the database is migrated, starts the four services and checks them. Expected result: deployed verified artifact <candidate commit>, followed by the compatible fallback and backup paths.

For this first Postgres deployment, the staged fallback is the earlier Postgres commit of step 1. Its migrations match the candidate's, so it runs against the same database: it is a way back to earlier Postgres code, not to SQLite. The helper never rolls back on its own; the deployment bullets under Failure recovery say what to do for each phase it can stop in.

13. Verify the services¶

Working directory: any, on the host, which reaches the private control listener:

curl --fail http://127.0.0.1:4001/healthz
curl --fail http://127.0.0.1:4001/readyz

Expected result: {"status":"ok"} and {"status":"ready"}, with the control port your installation uses. Then sign in to the Console as an existing member and confirm that your Organizations, Teams, virtual keys, Agents and Chat history are present. The import carries every member, so there is nothing to bootstrap: do not run bootstrap against the imported database, which would add a Default Team to an Organization that has none. Existing sessions and keys keep working; no new key or session is needed.

Once the services are verified, delete the workstation copies with rm -r "$HOME/mission-control-cutover-20260924". The backup directory on the host keeps the copies and the import log.

Expected result¶

The Postgres release serves the imported data from one database at DATABASE_URL. shared.env no longer defines DATABASE_PATH or APPLICATION_DATABASE_PATH. The backup directory on the host holds the two SQLite copies, the checksums, the previous environment files, the previous release path and the import log. The live SQLite files remain unchanged at their former paths, and the SQLite release stays in its release directory for a rollback.

Verification¶

The import log lists every table as ok, and its total: line has equal SQLite and Postgres counts and ends with (committed).
db:migrate applies nothing, and the services start. A database that misses a migration stops the gateway and the Console with STORAGE_MIGRATION_REQUIRED: run pnpm --filter @mission-control/database db:migrate.
Private /readyz is ready, inference without a key returns 401, and members see their existing data.
select application_name from pg_stat_activity where datname = 'mission_control' lists mission-control-gateway, and mission-control-console while the Console serves requests. Each process closes a pooled connection that stays idle for 10 seconds, so sign in to the Console just before you run the query.

Failure recovery¶

A refusal before the copy starts ends with Nothing was written. Fix the cause and rerun. A failure during the copy ends with The import was rolled back: nothing was written. Fix the cause and rerun. (The dry run was rolled back: … for a dry run). In both cases the database stays empty: correct the cause and run the same command again. After a rolled-back attempt, run psql -X -c 'vacuum' first. Like analyze, it prints expected warnings about the server-wide catalogs. The tool names each problem:

The commit did not complete: if it failed, nothing was written and the import can be rerun; if it went through, a rerun is refused because the target is not empty. The connection closed while the transaction committed, for example because the tunnel dropped, so the import may have committed. Check the target before anything else: psql -X -Atc 'select count(*) from organization'. A number above zero means that the import committed. Do not run it again and do not drop the database; continue with step 10, whose checks confirm the import. Zero means nothing was written: run vacuum and rerun.
storage_collation_refused: the database uses the collation … and character type … (locale provider …) from db:migrate, or the target database uses the collation … from the import: the database was not created with the libc C collation and character type, and nothing was written to it. As the superuser, drop the database and create it with the statement the message prints, or with the statement of step 2. Then migrate (step 8) and rerun. C.UTF-8 is refused on purpose: its character type makes lower() fold non-ASCII letters, which SQLite did not.
the target database is not migrated: run pnpm --filter @mission-control/database db:migrate …: run step 8, then rerun.
the target database is not empty: table <name> has rows; import into a freshly migrated database: the database already holds data. It is either a completed import or data from a service started against it too early. First rule out a completed import with the previous bullet and step 10. Only when you are sure that it does not hold the import you want to keep, drop and recreate the database as the superuser: run drop database mission_control; and the create database mission_control … statement of step 2. Then migrate (step 8) and rerun.
A table or column present on only one side (the table sets differ … or <table>: the columns differ …): the SQLite files do not come from the last SQLite release. Import stores produced by that release. --allow-missing <table> skips a table that exists on only one side; use it only after you have confirmed that the table holds nothing you need.
the control SQLite file … has a non-empty write-ahead log beside it (or the application SQLite file …): the file was copied with cp or is still open. Make the copy with sqlite3 … ".backup …" as in step 6. An empty -wal file beside a copy is accepted; opening a copy can leave one behind.
N orphan row(s) in the SQLite files: a foreign key points at …: SQLite kept rows whose parent row no longer exists. The message lists each row by table and rowid. Correct or delete those rows in the workstation copy, as in Repair a refused value, then rerun.
<table> (<key>) column <column>: the text contains a NUL character, which Postgres cannot store, or … the text is not valid UTF-8 (…): repair that column in the workstation copy as described in Repair a refused value, then rerun.
<table> (<key>) column <column>: the JSON document is not valid jsonb (a \u0000 escape or a lone surrogate): repair that column with the JSON variant in Repair a refused value, then rerun.
<table>: inserting the rows with rowid … failed: … (a rule the Postgres schema enforces), or <table>: N rows cannot be inserted parents first …: the SQLite data holds values or references that SQLite did not enforce. Correct those rows in the workstation copy, then run vacuum and rerun.
the row counts differ in …: the import was rolled back and nothing was written. Keep the output and report it.

Never edit the live SQLite files or the copies in the host's backup directory: they are the rollback's source and its safety copy.

If the services start with STORAGE_MIGRATION_REQUIRED, run db:migrate against the same DATABASE_URL and start them again. If the deployment helper refuses shared.env because it still defines DATABASE_PATH or APPLICATION_DATABASE_PATH, remove those lines as in step 11.

If the deployment of step 12 fails, the helper prints the phase it stopped in:

preflight, artifact-validation or remote-preflight: nothing changed on the host. Correct the cause and run the helper again.
stage through verify-migrations: the links still point at the SQLite release, but the helper has staged the candidate's and the fallback's release directories under /home/mission-control/mission-control/releases/ and, from install-guard on, rewritten the services' drop-ins. Either remove those two release directories, correct the cause and run the helper again, or return to SQLite with Roll back to the SQLite release.
activate-workers or a later phase: the launch link points at the candidate. Either activate the staged Postgres fallback with a code-only scripts/rollback_vps.sh, as described in Upgrade and rollback, or return to SQLite with Roll back to the SQLite release.

The helper never overwrites a staged release. Before the next attempt, remove the release directories of the failed one once no link points at them.

Repair a refused value¶

Repair the workstation copy of step 7 (or of step 4), never the live files or the host's backup copies. Many tables carry SQLite triggers that refuse every change to a stored row. The import does not read SQLite triggers, so this repair drops the named table's triggers in the copy first.

SQLite's replace() cannot remove a NUL character: replace(<column>, char(0), '') changes nothing. Repair the bytes with this script instead. It takes the copy, the table and the column that the refusal names. The tables of the application store (Agent instructions, Chat and evaluation content) are in mission-control-applications.sqlite; sqlite3 <copy> '.tables <table>' shows whether a copy holds a table. For example, for user (id=user_owner) column name: the text contains a NUL character:

python3 - "$HOME/mission-control-cutover-20260924/mission-control.sqlite" user name <<'PY'
import json, re, sqlite3, sys
path, table, column = sys.argv[1:4]
as_json = sys.argv[4:] == ["json"]
def q(name):
    return '"' + name.replace('"', '""') + '"'
def clean(value):
    if isinstance(value, str):
        return re.sub("[\ud800-\udfff]", "\ufffd", value.replace("\x00", ""))
    if isinstance(value, list):
        return [clean(item) for item in value]
    if isinstance(value, dict):
        return {clean(key): clean(item) for key, item in value.items()}
    return value
db = sqlite3.connect(path)
for (trigger,) in db.execute("select name from sqlite_master where type = 'trigger' and tbl_name = ?", (table,)).fetchall():
    db.execute(f"drop trigger {q(trigger)}")
changed = 0
rows = db.execute(f"select rowid, cast({q(column)} as blob) from {q(table)} where typeof({q(column)}) = 'text'").fetchall()
for rowid, raw in rows:
    text = raw.replace(b"\x00", b"").decode("utf-8", "replace")
    if as_json:
        document = json.loads(text)
        if clean(document) != document:
            text = json.dumps(clean(document), ensure_ascii=False, separators=(",", ":"))
    fixed = text.encode("utf-8")
    if fixed != raw:
        db.execute(f"update {q(table)} set {q(column)} = cast(? as text) where rowid = ?", (fixed, rowid))
        changed += 1
db.commit()
db.close()
print(f"{table}.{column}: {changed} of {len(rows)} row(s) rewritten")
PY

Expected result: user.name: 1 of 6 row(s) rewritten, with your table, column and counts. The script removes NUL characters from every text value of the column and replaces ill-formed UTF-8 with U+FFFD.

For a JSON refusal, add json after the column name, for example python3 - "$HOME/mission-control-cutover-20260924/mission-control-applications.sqlite" agent_definition_content definition_json json <<'PY' followed by the same script. The script then rewrites only the documents that contain a \u0000 escape or a lone surrogate. It removes the NUL characters and replaces lone surrogates with U+FFFD, and every document stays valid JSON. The product checks some documents against a stored hash, such as Agent definition content. Such a document no longer matches its hash after the repair, and the product then refuses that one Agent version with definition_content_mismatch. Publish a corrected version of that Agent after the cutover.

For an orphan row, delete it or correct its reference in the copy with sqlite3, for example delete from <table> where rowid = <rowid>;. If a trigger refuses the change, drop the table's triggers in the copy first: select name from sqlite_master where type = 'trigger' and tbl_name = '<table>' lists them, and drop trigger <name>; drops one.

Roll back to the SQLite release¶

A rollback to the pre-Postgres release is a manual procedure on the host, as the service account, for when you decide to return to SQLite. A failed Postgres deployment does not require it: the deployment bullets under Failure recovery say how to recover each phase. scripts/rollback_vps.sh does not perform this rollback: it refuses a pre-Postgres (SQLite) release as its target before stopping anything, and its --restore-database option restores Postgres dumps only. Every change made on Postgres after the cutover is lost; the Postgres database itself stays as it is.

The service units need no change. The drop-ins start whatever release service-release points at, and their start guard holds only Postgres releases to the DATABASE_URL contract, so the SQLite release starts through the same drop-ins. Run no systemctl --user daemon-reload, and do not copy units/ from a deployment backup: it holds the same drop-ins.

Take the deployment lock, so that no deployment runs meanwhile, and stop the four services:

mkdir -m 700 /home/mission-control/mission-control/.deploy-lock
systemctl --user stop mission-control-console.service mission-control-agent-runtime.service \
  mission-control-collector.service mission-control-gateway.service

If mkdir fails, another operation holds the lock: stop and inspect it.

Keep the Postgres settings, then restore the SQLite ones from the backup directory of step 6:
```
backup=/home/mission-control/.local/share/mission-control/backups/pre-postgres-20260924T090000Z
cp -p "$HOME/.config/mission-control/shared.env" "$HOME/.config/mission-control/shared.env.postgres-20260925T090000Z"
install -m 600 "$backup/shared.env" "$HOME/.config/mission-control/shared.env"
```
If step 11 also removed lines from a per-service environment file, restore that file from the backup directory the same way, for example install -m 600 "$backup/gateway.env" "$HOME/.config/mission-control/gateway.env".
Confirm the live SQLite files are unchanged:
```
sha256sum -c "$backup/live-sqlite.sha256"
```
Expected result: OK for both files. If a live file is missing or differs, check the copy with (cd "$backup" && sha256sum -c backup-sqlite.sha256), delete that store's -wal and -shm files, and restore it from the copy with sqlite3 <backup copy> ".backup '<live path>'".

Point the launch link at the SQLite release and start the services, the gateway first:

previous="$(cat "$backup/previous-release")"
ln -sfn "$previous" /home/mission-control/mission-control/service-release.next
mv -Tf /home/mission-control/mission-control/service-release.next /home/mission-control/mission-control/service-release
systemctl --user start mission-control-gateway.service
curl --fail --retry 20 --retry-connrefused --retry-delay 1 http://127.0.0.1:4001/healthz
systemctl --user start mission-control-collector.service mission-control-agent-runtime.service \
  mission-control-console.service
curl --fail --retry 30 --retry-all-errors --retry-delay 1 http://127.0.0.1:4001/readyz

Verify the services, the process paths and the start guard:

systemctl --user is-active mission-control-gateway.service mission-control-collector.service \
  mission-control-agent-runtime.service mission-control-console.service
curl --fail --output /dev/null http://127.0.0.1:13133/health
curl --fail --output /dev/null http://127.0.0.1:4100/healthz
curl --fail --output /dev/null http://127.0.0.1:3010/sign-in
curl --output /dev/null --write-out '%{http_code}\n' http://127.0.0.1:4010/v1/models
for name in gateway collector agent-runtime console; do
  unit="mission-control-$name.service"
  readlink -f "/proc/$(systemctl --user show "$unit" --property=MainPID --value)/cwd"
  journalctl --user -u "$unit" -n 50 | grep 'mission-control release guard:' || true
done

Expected result: active four times, the three curl --fail commands succeed silently, 401, and four paths inside the SQLite release printed by echo "$previous", with no mission-control release guard: line. Then members sign in as in step 13.

Record the SQLite release as the current one and release the lock:

ln -sfn "$previous" /home/mission-control/mission-control/current.next
mv -Tf /home/mission-control/mission-control/current.next /home/mission-control/mission-control/current
rmdir /home/mission-control/mission-control/.deploy-lock

If a service refuses to start and its log (journalctl --user -u mission-control-gateway.service -n 50) shows a mission-control release guard: line saying that the release is older than the activated evaluation contract, the started release is not the one that step 6 of the procedure recorded in previous-release; the contract floor is never lowered. To return to Postgres instead, stop the services, restore shared.env.postgres-20260925T090000Z as shared.env with install -m 600, point service-release back at the Postgres release, start the services, and then point current back.

To attempt the cutover again later, start again from step 4 with new copies and a new backup directory. Before that, as the superuser, drop and recreate the database (drop database mission_control;, then the create database mission_control … statement of step 2), because the import accepts only an empty database. Also recreate mission_control_import_check if you rehearse again. Remove the release directories the earlier deployment staged under /home/mission-control/mission-control/releases/, but keep the SQLite release that current points at.