Plugin migrations are position-addressed with no verification, so a mid-list edit silently diverges existing databases from fresh ones

[albrand]

Summary

api.database.migrate() documents its contract: "Append-only — never reorder or edit shipped statements." A plugin I maintain broke that rule. The rule is clear and the mistake was mine; this is a report that breaking it is silent, in case that's worth changing.

Statement index is migration id. A database that has applied N never re-runs statement N-k, whatever that statement now says. When the shipped list and the applied list disagree, nothing compares them and nothing reports it — so the same plugin build produces one schema on a fresh install and a different one on an existing install, with no error at the point of divergence.

How it presented

Three ALTER TABLE ... ADD COLUMN statements were inserted mid-list, ahead of statements that had already run:

• fresh installs: all columns present, full suite green
• the live install: columns never added, every command failing no such column: drifted_at

The plugin's tests all built their database by replaying the whole list from empty into memory, which is the one arrangement where this cannot show up, so the suite stayed green throughout. It surfaced days later on a running install.

Why it may be worth enforcing rather than documenting

The documentation exists and I didn't follow it. The reason this might still be worth a check is that a plugin author has no way to detect the mistake:

1. No signal at write time. The host applies and records a count. Nothing was skipped from its side; the count was already past.
2. No signal at read time. No way to ask what a database has applied versus what the plugin now ships.
3. The failure surfaces arbitrarily far away, at the first query touching the column, in an unrelated subsystem, possibly weeks later.

The failure also doesn't look like a migration problem when it appears, which is what made it slow to find.

Proposed solution

Content-address the statements instead of trusting position. One column, one check at load.

A working prototype of the proposed migrate() body, with the migration-path tests, is attached below. It needs better-sqlite3 on the resolution path, so run it from any checkout that already has it (npx tsx migrate-guard.mts); in an empty directory it exits 1 with ERR_MODULE_NOT_FOUND, which is the dependency and not the check. All five cases pass, and a failure exits nonzero.

1. ALTER TABLE _bb_migrations ADD COLUMN statement_sha256 TEXT, added by the helper itself on first run.

2. Verify the applied prefix before running anything new; on mismatch throw naming the index and both digests:

plugin migrations: statement 1 differs from the one this database applied
(applied 3f2a1c0b9e44, shipped 91ce77a05d12). Statements are append-only;
1 has been edited or reordered. Append a new statement instead.

3. Rows written before this ships carry null and are trusted, so no backfill and no break.

Tests supplied, all passing against the prototype:

case	result
legacy database with null digests still upgrades, and an append after it applies	pass
editing an already-applied statement fails, naming the index	pass
inserting mid-list fails rather than silently skipping (the case described above)	pass
appending after a verified upgrade still applies	pass
limit: a legacy null row cannot detect a historical edit	pass

That last row is the honest boundary and is asserted rather than described. Databases that predate the column are unverifiable for their existing prefix — the check protects everything applied from then on, and cannot retroactively prove what an older install ran. A plugin author upgrading an old install gets protection for future edits only.

Properties that matter:

• Fails at reload, next to the edit — not at first query in an unrelated subsystem weeks later.
• The message is actionable. no such column is not.
• Appending stays silent, because appending is the safe operation.

4. Optional, near-free: migrate(db, statements: readonly string[]), making the array's immutability visible at the call site.

Local mitigation, for reference

The plugin now pins its shipped statements by digest in its own suite and fails on any edit, reorder, removal or mid-list insertion. About 60 lines, verified against the actual defect.

One note in case it's useful: an upgrade-path test written alongside it turned out to be useless and was removed. It replayed the same sequence for both the reference schema and the simulated upgrade, so the two could never diverge, and the defect passed it clean. Position-addressing is awkward to test from inside a plugin, which is why the check may belong in the host — but that's a suggestion, not a diagnosis of your codebase.

Prototype + tests (migrate-guard.mts) — all five pass, exits nonzero on failure

// Prototype of the proposed host-side check, with the migration-path tests.
//
// Requires better-sqlite3 on the resolution path, so run it from a directory
// that already has it — inside this repo, or any plugin checkout:
//
//   cp migrate-guard.mts <a-package-with-better-sqlite3>/ && npx tsx migrate-guard.mts
//
// A bare `npx tsx migrate-guard.mts` in an empty directory exits 1 with
// ERR_MODULE_NOT_FOUND; that is the dependency, not the prototype.
import Database from "better-sqlite3";
import { createHash } from "node:crypto";

const sha = (s: string) => createHash("sha256").update(s).digest("hex");

/** Proposed replacement for the body of api.database.migrate(). */
function migrate(db: Database.Database, statements: readonly string[]): void {
  db.exec(
    `CREATE TABLE IF NOT EXISTS _bb_migrations (
       id INTEGER PRIMARY KEY, applied_at INTEGER NOT NULL, statement_sha256 TEXT)`,
  );
  // Column is added the same way any other new column would be.
  const cols = db.prepare("PRAGMA table_info(_bb_migrations)").all() as Array<{ name: string }>;
  if (!cols.some((c) => c.name === "statement_sha256")) {
    db.exec("ALTER TABLE _bb_migrations ADD COLUMN statement_sha256 TEXT");
  }

  const applied = db
    .prepare("SELECT id, statement_sha256 FROM _bb_migrations ORDER BY id")
    .all() as Array<{ id: number; statement_sha256: string | null }>;

  // VERIFY THE PREFIX BEFORE RUNNING ANYTHING NEW.
  for (const row of applied) {
    if (row.statement_sha256 === null) continue; // legacy row: cannot verify, trusted
    const shipped = statements[row.id];
    if (shipped === undefined) {
      throw new Error(
        `plugin migrations: this database has applied ${applied.length} statements but the ` +
          `plugin ships ${statements.length}. Statement ${row.id} has been removed.`,
      );
    }
    if (sha(shipped) !== row.statement_sha256) {
      throw new Error(
        `plugin migrations: statement ${row.id} differs from the one this database applied ` +
          `(applied ${row.statement_sha256.slice(0, 12)}, shipped ${sha(shipped).slice(0, 12)}). ` +
          `Statements are append-only; ${row.id} has been edited or reordered. ` +
          `Append a new statement instead.`,
      );
    }
  }

  const run = db.transaction(() => {
    for (let i = applied.length; i < statements.length; i += 1) {
      db.exec(statements[i]);
      db.prepare(
        "INSERT INTO _bb_migrations (id, applied_at, statement_sha256) VALUES (?, ?, ?)",
      ).run(i, 1, sha(statements[i]));
    }
  });
  run();
}

let failed = 0;
const check = (name: string, fn: () => void) => {
  try {
    fn();
    console.log(`  pass  ${name}`);
  } catch (e) {
    failed += 1;
    console.log(`  FAIL  ${name}: ${String(e).split("\n")[0].slice(0, 120)}`);
  }
};
const throws = (fn: () => void, must: RegExp) => {
  try {
    fn();
  } catch (e) {
    if (must.test(String(e))) return;
    throw new Error(`threw, but not matching ${must}: ${String(e).slice(0, 100)}`);
  }
  throw new Error("did not throw");
};

const V1 = ["CREATE TABLE t (a TEXT)", "ALTER TABLE t ADD COLUMN b TEXT"];

// 1. LEGACY: rows written before this ships carry null digests and must load.
check("a legacy database with null digests still upgrades", () => {
  const db = new Database(":memory:");
  db.exec(`CREATE TABLE _bb_migrations (id INTEGER PRIMARY KEY, applied_at INTEGER NOT NULL)`);
  for (const [i, s] of V1.entries()) {
    db.exec(s);
    db.prepare("INSERT INTO _bb_migrations (id, applied_at) VALUES (?, ?)").run(i, 1);
  }
  migrate(db, [...V1, "ALTER TABLE t ADD COLUMN c TEXT"]); // append after legacy
  const cols = (db.prepare("PRAGMA table_info(t)").all() as Array<{ name: string }>).map((c) => c.name);
  if (!cols.includes("c")) throw new Error(`append did not apply: ${cols}`);
});

// 2. THE DEFECT: an edit below the applied count must fail loudly.
check("editing an already-applied statement fails, naming the index", () => {
  const db = new Database(":memory:");
  migrate(db, V1);
  throws(
    () => migrate(db, ["CREATE TABLE t (a TEXT)", "ALTER TABLE t ADD COLUMN EDITED TEXT"]),
    /statement 1 differs/,
  );
});

// 2b. The case described above: INSERTION mid-list.
check("inserting mid-list fails rather than silently skipping", () => {
  const db = new Database(":memory:");
  migrate(db, V1);
  throws(
    () => migrate(db, ["CREATE TABLE t (a TEXT)", "ALTER TABLE t ADD COLUMN inserted TEXT", ...V1.slice(1)]),
    /statement 1 differs/,
  );
});

// 3. Appending after a verified upgrade still applies.
check("appending after the upgrade still applies", () => {
  const db = new Database(":memory:");
  migrate(db, V1);
  migrate(db, [...V1, "ALTER TABLE t ADD COLUMN d TEXT"]);
  const cols = (db.prepare("PRAGMA table_info(t)").all() as Array<{ name: string }>).map((c) => c.name);
  if (!cols.includes("d")) throw new Error(`append did not apply: ${cols}`);
});

// 4. The honest limit, asserted rather than claimed in prose.
check("LIMIT: a legacy null row cannot detect a historical edit", () => {
  const db = new Database(":memory:");
  db.exec(`CREATE TABLE _bb_migrations (id INTEGER PRIMARY KEY, applied_at INTEGER NOT NULL)`);
  for (const [i, s] of V1.entries()) {
    db.exec(s);
    db.prepare("INSERT INTO _bb_migrations (id, applied_at) VALUES (?, ?)").run(i, 1);
  }
  migrate(db, ["CREATE TABLE t (a TEXT)", "ALTER TABLE t ADD COLUMN EDITED TEXT"]); // must NOT throw
});

console.log(failed === 0 ? "\nmigrate-guard: all pass" : `\nmigrate-guard: ${failed} FAIL`);
process.exit(failed === 0 ? 0 : 1);