v3.5.0: ALTER-built V13 databases fail to migrate to V14 with `unsupported_lineage` (#52 defect class reappears)

[pablontiv]

Summary

backscroll v3.5.0 fails on every operational command for databases whose V13 schema was produced by the historical ALTER TABLE-based migration path:

migration_failed ... unsupported_lineage sha256:9cdad03b571fd70d...

recover --dry-run reports the same. The migration transaction rolls back, so the database stays at V13 and the tool is unusable until the schema DDL text is rewritten by hand.

This is the same defect class as #52 (fixed for V13 in #55/#56), reappearing in the new V14 migration added by #54.

It is not Windows-specific. It was first hit on Windows, but the trigger is the database's build lineage, not the platform — reproduced below on Linux from checked-in fixtures, yielding the exact signature seen in the field (sha256:9cdad03b…).

Reproduction

Drop this into internal/storage/ and run it:

package storage

import (
	"context"
	"testing"
)

func TestReproV13LineagesUpgradeToV14(t *testing.T) {
	for _, fixture := range []string{
		"v13.sql",
		"v13-legacy-existing-schema-migrations.sql",
		"v13-legacy-alter-built.sql",
		"v13-development-alter-built.sql",
	} {
		t.Run(fixture, func(t *testing.T) {
			dbPath := createFixtureDatabase(t, fixture)
			db, diag, err := OpenCompatible(context.Background(), dbPath)
			if db != nil {
				defer func() { _ = db.Close() }()
			}
			if err != nil || diag != nil {
				t.Errorf("REPRO: %s -> err=%v diag=%+v", fixture, err, diag)
			}
		})
	}
}

Result on main (ed7e90a), Linux:

--- PASS: v13.sql
--- PASS: v13-legacy-existing-schema-migrations.sql
--- FAIL: v13-legacy-alter-built.sql
      err=verify final schema before commit: unsupported_lineage:
      unsupported index schema sha256:9cdad03b571fd70df9c1045eae6469864b0e2a66498d7ec9f6dc7ad0af2b2122
--- FAIL: v13-development-alter-built.sql
      err=verify final schema before commit: unsupported_lineage:
      unsupported index schema sha256:f6a081b9df13b30fdac598103d558325c2636e4cec6625f77d1294d26bd47f89

Two of the four V13 lineages already in the catalog cannot reach V14.

Root cause

1. normalizeSQL is whitespace-insensitive between tokens, but not adjacent to punctuation. It collapses runs of whitespace to a single space; it does not drop a space next to (, ) or ,. Verified directly:

   "CREATE TABLE s (\n    a TEXT,\n    b INTEGER\n)"  ->  "CREATE TABLE s ( a TEXT, b INTEGER )"
   "CREATE TABLE s (\n    a TEXT, b INTEGER)"         ->  "CREATE TABLE s ( a TEXT, b INTEGER)"
                                                                                        ^ differs
   

2. A table whose last column was appended by ALTER TABLE ... ADD COLUMN has DDL ending ..., was_interrupted INTEGER) — no whitespace before ). A freshly created table ends ... was_interrupted INTEGER\n). So search_items normalizes differently in ALTER-built vs. fresh-built lineages, and the two keep distinct V13 signatures (sha256:6003ed9f… vs sha256:5d6baa68…). That is intentional today — both are manifested as separate catalog entries.

3. V14 (sqlV14) is ALTER-only, so it preserves that per-lineage distinction and produces one distinct V14 signature per V13 lineage: e4973d30… (fresh), 9cdad03b… (legacy ALTER-built), f6a081b9… (development ALTER-built).

4. Only one V14 fixture exists — internal/compat/testdata/release-schemas/v14.sql, signature sha256:e4973d30…, derived from the fresh-built lineage. compat.VerifyCurrentShape (called at internal/storage/migration_plan.go:109 inside the migration transaction, and again at :121 after commit) rejects the other two signatures as unsupported_lineage, aborting the transaction and rolling back.

Net effect: users on the fresh lineage upgrade fine; users on either ALTER-built lineage are hard-blocked.

Test gap

No test asserts that every fixture in the catalog can be migrated to head. The existing compat tests check fixture signatures against manifest.json and check migration-ledger rows, but never drive a legacy fixture through OpenCompatible to the current version — which is exactly the path that breaks. The repro above is a candidate regression test.

Structural concern

The manifest lists fixtures, not lineages, and V(N) fixtures are captured from a single build path. Because every new ALTER-based migration multiplies the surviving lineages, this will recur at V15, V16, … unless the coverage rule changes. #52 fixed the V13 instance; this is the V14 instance.

Possible directions

Two independent options — the first is a point fix, the second removes the defect class:

1. Manifest the missing V14 signatures. Derive a V14 fixture from every known V13 lineage (v14-legacy-alter-built.sql, v14-development-alter-built.sql) and add them to manifest.json. Cheap, but the multiplication continues at each future migration.

2. Make normalizeSQL punctuation-insensitive (drop whitespace adjacent to (, ), , outside quoted/comment context). ALTER-built and fresh-built DDL then collapse to a single signature, so a lineage stops depending on how its columns were added, and the fixture count stops growing. Requires regenerating all manifest signatures (REGEN_MANIFEST=1) and re-checking the collision-consistency guard, since several currently-distinct entries would merge.

Whichever is chosen, a regression test that migrates every catalog fixture to head would have caught this before release.

Workaround (verified in the field)

With the database at V13, back it up, then rewrite only the DDL text of search_items in sqlite_master to the fresh-built form:

PRAGMA writable_schema=ON;
UPDATE sqlite_master SET sql=<multi-line search_items DDL> WHERE type='table' AND name='search_items';
PRAGMA writable_schema=OFF;

No column or data change (integrity_check ok; row count unchanged). The resulting V13 signature is sha256:5d6baa68…, a known lineage, and backscroll then applies V14 itself and reaches the manifested sha256:e4973d30…. This is a manual escape hatch, not a fix — it requires hand-editing sqlite_master, which no user should have to do.

Environment

• backscroll v3.5.0 (main @ ed7e90a)
• First observed on Windows 11; reproduced on Linux from checked-in fixtures
• Related: bug: index migrated V1→V13 by official releases is rejected as unsupported_lineage #52, bug: recover --from <active-db> rejects the -shm sidecar its own startup creates #51, PRs fix(compat): whitespace-insensitive schema lineage (#52) #55, fix(compat): handle SQL lexical structure in normalizeSQL #56, perf(sync): metadata prefilter for startup optimization #54

[pablontiv]

Confirmed on a second platform: macOS (Darwin 25.5.0, arm64), field database at ~/.backscroll.db (V13, 13 ledger rows, 21,335 search_items), v3.5.0 release binary.

backscroll status fails with the exact signature reported here:

diagnostic: migration_failed: prepare index failed: verify final schema before commit: unsupported_lineage: unsupported index schema sha256:9cdad03b571fd70df9c1045eae6469864b0e2a66498d7ec9f6dc7ad0af2b2122

Two additional data points from testing release binaries (checksums verified against each release's checksums.txt, run from a temp dir via BACKSCROLL_DATABASE_PATH against copies of the same database):

1. Downgrading is not a workaround. v3.3.2, v3.4.0, v3.4.1, v3.4.4 and v3.5.0 all reject this database today (v3.4.1 reports the pre-normalization signature sha256:73c11969… from bug: index migrated V1→V13 by official releases is rejected as unsupported_lineage #52; the others report 9cdad03b… at final verify).
2. A second uncatalogued variant exists. The same schema with the V9 ledger checksum set to the original a84857… (the v13-development-alter-built value) fails final verify with sha256:f6a081b9df13b30fdac598103d558325c2636e4cec6625f77d1294d26bd47f89 on every version above. If the fix enumerates post-V14 shapes per V13 alias, this is the dev-alter-built-checksum counterpart of 9cdad03b….

Database left untouched at V13 with the true b16094… V9 checksum, matching the field shape above.