Upgrades & Migrations

Upgrades and migrations: how Chinaski’s schema and code move forward. This page is grounded in the repository’s actual mechanics: sixty-two numbered migration files as the schema-evolution story, and two concrete upgrade paths: the on-line deploy script and the air-gapped signed-bundle script.

The migration story (schema evolution)

migrations/ carries 62 numbered SQL files, 001_phase2_database_foundation.sql through 062_form_submissions_read_at.sql. Each is self-documenting: a header listing what changes, and the apply command (sqlite3 chinaski.db < migrations/…) on every file. The file names double as a changelog: read the directory bottom-up and you read the product’s history: the phase-2 foundation (CHECK constraints, dedup indexes, dropped dead columns), documents (phase 45), the whole stacks family (020 through inheritance in 043), forms hardening (ref tokens, attribution, quotas, consent validation, read-at), event logging, nav hierarchy/slots/renumber, promotion guard versions, build durability parameters, and the render cache.
schema.sql is the fresh-install endpoint of the same history: a new install bootstraps from schema.sql directly, so both paths must land in the same shape. That’s enforced, not assumed: bin/check-schema-sync.pl compares a live database against schema.sql and exits nonzero with a “Fix: add the missing tables/columns to schema.sql” note when they drift. The fix memo is the project’s own contract: fresh installs have to match the live ones.
Migration order is load-bearing: the run script comment notes “Must run before phase41: pages/posts rebuild SELECTs og_image_url from existing rows, so a DB predating the column would fail that rebuild.” A fat block of helper writers in the code each runs only when their turn comes.
Every migration is written for idempotency: IF NOT EXISTS-style notes across the migration corpus (the language has run twice in a fresh-install/boot path many times), so a startup migration sweep never explodes on re-runs.

Running migrations: automatic

Migrations run on app startup: Chinaski::Migrations run_all() chains the _apply_* helpers in a fixed order (_apply_perf_indexes first, then the content/stacks/forms/nav/doc families in dependency order). An operator never runs a migration by hand; the application converges its own database on the code’s current schema.

Version stamps exist where a migration skips by old-DB detection: a bump of SQLite’s PRAGMA user_version inside one of the _apply_* helpers in the chain (observed: the gate that moves a pre-53 entry straight to 53 to skip prior work on a recent database). The mapping to current-version numbers is in code, not in a table; no operator-facing version list ships.

Upgrading a running instance

Online (on a connected host)

deploy/deploy.sh <N>: the standing recipe: 1. git pulls the installation tree and installs dependencies. 2. perl -c cms.pl syntax-checks the application (a syntax breakage aborts before touching anything). 3. Rebuild the static site with CHINASKI_DB/CHINASKI_OUTPUT set explicitly. 4. Restart the service, unless the builder failed, in which case the deploy aborts and the service keeps the previously-served tree (a restart on top of half-output would publish service corruption).

Air-gapped

scripts/apply_update.sh: the offline-upgrade script: 1. On a connected machine, fetch the release bundle from its manifest (a .tar.gz) and its Ed25519 signature. 2. Transfer both to the target and provide your release public key. 3. The script verifies the signature first, backs up the current application tree, then applies the update, keeping the previous version recoverable.

Notification of updates: set an update manifest URL when updates should be checked at all; air-gapped installs with no CHINASKI_UPDATE_MANIFEST_URL never connect.

Restoring across version boundaries

Restoring a backup refuses a schema-version mismatch: an old backup restores only into matching-scheme, i.e. into the matching app version. So upgrading the app to match the backup’s schema version is part of a restore procedure (the disaster-recovery page walks that path).

When an upgrade lands schema changes, the backup restore machinery reflects them in the schema gate (the tooling’s restore gate), not an automatic migration; the app starts up the change at the next bootstrap.

The pinning: tests

Migration hygiene is enforced in the test suite as well as scripts:

Migration lint (migrations_lint.t) checks that migration files follow the project’s hygiene (clean ids, safe re-run).
Migration/code-suite tests exercise the actual run-all flow against fixture DBs; migrations_helpers runs the machinery with dry/mocked database handlers (CHINASKI_DB pointed at any path; skips in tests).

Checklist for an upgrade

Backup first (database + files you care about, either the system backup or an in-app archive).
deploy.sh <N> on connected hosts (which does syntax-check, rebuild, restart), or apply_update.sh with a signed bundle on an air-gapped one.
If the deploy.sh failed at the schema level (e.g. migrations didn’t run), check the journal: migrations run at startup, not during the deploy script itself.
Fresh-install drift: run bin/check-schema-sync.pl to compare schema.sql against the live DB, its nonzero exit immediately flags the edit that fresh installs will need to add.
Verify the site renderspages plus /health, verify build completeness via the build history.