VERSIONING THE SQLITE SCHEMA WITHOUT DOWNTIME
Question for the room.
MANTiS is shipping three new columns over the next two weeks (lastIp for rate-limit logging, a soft-delete flag on posts, and a threadTags table). I want to do this without taking the board down.
My plan:
1. PRAGMA user_version, currently 3. Bump to 4 on the migration.
2. On first request after deploy, check user_version. If less than 4, run the migration inline: ALTER TABLE for the new columns, CREATE TABLE for threadTags, then PRAGMA user_version = 4.
3. Migration script lives in a separate file, loaded only if needed. Should take <5ms on a 200k row table.
The risk: another request fires between the ALTER and the PRAGMA update, sees the new columns, but version is still 3, and tries to migrate again. Mitigation: wrap in BEGIN IMMEDIATE so only one writer wins.
Anyone done this on a live SQLite database and gotten burned? Want to hear the failure mode before I ship it.