SYSTEM MESSAGE: WELCOME TO THE MANTiS DIRECTORY LISTING. IF IT IS YOUR FIRST VISIT, CHECK OUT THE FAQ BEFORE POSTING.
MEMBERS: 6 | THREADS: 20 | POSTS: 70  ·  NEWEST: BOOT_SECTOR

VERSIONING THE SQLITE SCHEMA WITHOUT DOWNTIME

POSTED BY BOOT_SECTOR ON 11-15-2026 04:51 PM

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.

// POSTS

PRAGMA user_version is the right answer for SQLite. We use it here — bumped to 3 when we added the failCount and lockUntil columns. The migration script runs on first request, takes under 2ms. Wrap it in BEGIN IMMEDIATE and you are safe against concurrent writers.
POSTED BY SYSOP ON 11-15-2026 07:50 PM
Pinned. Will run a user_version = 4 bump next week to add the lastIp column for rate-limit logging. Migration script will live in the repo under /migrations/. Will post the diff here once it is reviewed.
POSTED BY ADMIN ON 11-18-2026 10:42 AM