Speakr v0.10.5-alpha

A database migration fix for long-running installations, plus hardening of how migrations run at startup. Upgrading is routine and no configuration changes are required.

The fix

When SSO support was added, user.password had to become nullable. SQLite cannot alter a column constraint, so that migration rebuilt the entire user table through a temporary copy. Two problems followed from it.

On databases old enough to predate one particular column, the copy step failed. SQLite's Python driver does not wrap schema changes in a transaction, so the temporary table had already been written to disk and survived the rollback. Every startup since retried the same migration and stopped at table user_new already exists, and the migration could never complete on its own (#379).

The more serious problem was latent. The rebuild recreated the user table from a column list written when that table had seventeen columns. It now has forty four. Any database where the rebuild ran to completion would have silently lost every later column and its values, including token budgets, email verification state, transcription hints and timestamp preferences, along with two indexes. The failure above was, in effect, protecting affected installations from that outcome, which is why the obvious cleanup of deleting the leftover table would have been the damaging move.

The migration no longer rebuilds the table. It alters only the column it needs, so every other column, index, constraint and value is untouched, and it runs inside an explicit transaction so it can no longer strand a partial copy. On PostgreSQL it uses the native ALTER COLUMN as before. A leftover temporary table is removed automatically, but only once the real user table is confirmed present and populated.

Migration hardening

• Startup migrations are serialised across processes with a lock (flock on SQLite, an advisory lock on PostgreSQL). initialize_database() runs roughly five times per container start, several of them concurrently, and could previously interleave. The lock fails open after a timeout so it can never hold a container down.
• Migrations are grouped into named sections. A failure is contained to its own section, and the sections after it still apply, where previously the first unguarded error silently skipped every remaining migration.
• A failed section rolls back the ORM session, so its partial writes cannot be committed later by an unrelated section, and it cannot hold the SQLite write lock while other sections wait on it.
• Migration failures are reported with a clear banner naming each failed section and its traceback, rather than as a single warning line among ordinary startup output.
• One-shot data migrations are recorded in a schema_migrations ledger instead of being re-detected on every boot. The first of these no longer loads every user account on each startup.

Testing

Upgrades are now tested against real database schemas from five earlier releases (v0.5.8, v0.7.0, v0.8.21, v0.9.7 and v0.10.3), generated from each release's own models. The tests assert that no column, index or stored value is lost, that the schema reaches the current models, and that a second startup changes nothing. Both SQLite and PostgreSQL are covered, and the checks run in CI and in the pre-commit hook.

Upgrading

Pull the new image and start as usual. If a previous version left a user_new table behind, this release removes it for you. Taking a copy of transcriptions.db before upgrading is sensible practice, as with any release.

If you override the container start command, note that since v0.10.4-alpha the stock image uses threaded gunicorn workers. Add --worker-class gthread --threads 8 to your command to get the same behaviour.

Full Changelog: v0.10.4-alpha...v0.10.5-alpha