SQLite storage: additive schema changes permanently lock out old readers

[jasonhan3]

SQLite storage: additive schema changes permanently lock out old readers

Summary

SQLiteStorage stores a schema version string (bloqade_schema.version_number) and
aborts on any value other than the one exact string it was compiled against.
Combined with the eager migration introduced in #101 (which rewrites the version on
open), this means a purely additive schema change — one that old code can
demonstrably still read and write — permanently locks every released bloqade-core
version out of any storage file the new version has merely opened.

Storage files are explicitly designed to be shared across sessions, machines, and
environments (Future.from_storage, Future.from_task_id), so mixed bloqade-core
versions touching the same file is a supported workflow, not an edge case.

Current behavior

Released code (≤ current PyPI) checks the version with strict string equality on open:

if stored_version != "0.1.0":
    raise ValueError(f"Schema version mismatch: expected 0.1.0, found {stored_version}")

PR #101 adds a group_id TEXT column (nullable) to task_definitions, bumps the
version to "0.2.0", and migrates in SQLiteStorage.__init__ — opening a file,
even just to read it, rewrites it.

The resulting compatibility matrix:

	old file (0.1.0)	new file (0.2.0)
old code	✅ works	❌ ValueError on open
new code	✅ works, but mutates the file on open (moves it to the right column for everyone else)	✅ works

Aggravating bug: stray version row

Old code runs INSERT OR IGNORE INTO bloqade_schema VALUES ('0.1.0') before its
version check. Against a 0.2.0 file this inserts a second row (the version is the
primary key, and '0.1.0' ≠ '0.2.0'). If new code later opens that file and happens
to read the stray '0.1.0' row, its migration runs
UPDATE bloqade_schema SET version_number = '0.2.0' across both rows and dies with
a primary-key IntegrityError — a corrupted-state dead end reachable purely through
supported version mixing.

Why the lockout is unjustified

The schema change is additive and old code is naturally forward-compatible with it:

• All old writes use explicit column lists (INSERT INTO task_definitions (task_id, program_language, creation_time) VALUES (?, ?, ?)) — the new nullable
  column is simply filled with its default.
• All old reads are SELECT * into name-keyed dicts or single-column selects — the
  extra column is invisible.

Verified empirically: a database created by the new code (with group_id
populated) and the version marker left at "0.1.0" was opened by the actual code from
main, which successfully listed tasks, reconstructed task definitions, read shots,
and wrote a new task definition. Re-opening with new code showed the grouped task's
UUID intact and the old-code-written task with group_id = NULL. Full round-trip, no
data loss.

The only thing that breaks old readers is the version string changing — the abort is a
policy failure, not a real incompatibility.

Root cause

One field is forced to answer two different questions with one equality check:

1. What schema is this file? (a fact about the file — changes on every migration)
2. May this reader safely use it? (a compatibility contract — should only change on
   breaking migrations)

Because released readers compare the field with !=, any honest answer to
question 1 destroys the answer to question 2.

Proposed fix

1. Freeze the legacy marker. bloqade_schema.version_number stays "0.1.0"
   forever (documented as a fossilized compatibility gate for already-shipped readers,
   not a live version). Remove the 0.2.0 bump and its migration block.

2. Stop mutating on open; add the column lazily.

   • __init__ performs no migration on existing files. New files get group_id in
     the initial CREATE TABLE.
   • get_task_group_id feature-detects the column (PRAGMA table_info, cached per
     connection) and returns None when absent.
   • add_task_definition runs ALTER TABLE ... ADD COLUMN just-in-time before its
     insert. Mutation is earned by a write, never by an open — read-only usage leaves
     files byte-identical (works on read-only mounts, keeps backups/checksums clean).

3. Introduce an honest version mechanism old code never sees. Use
   PRAGMA user_version (or a small key-value table) to store two integers:

   • schema_version — bumped on every schema change (now: 2); drives a
     sequential, numbered migration chain (if v < 2: ..., each step transactional).
   • min_reader_version — bumped only on breaking changes (stays 1 for this
     additive change). New code refuses a file only when
     min_reader_version > highest version it understands.

   Future additive changes then never lock anyone out, and future breaking changes
   lock out old readers deliberately and with an accurate error. Shipping the range
   check now means a generation of readers already honors it before the first breaking
   change arrives.

Alternatives considered

• Bump to 0.2.0 (current PR) or 0.1.1: any change to the string is equally
  fatal to released readers because of the equality check; the semver semantics are
  theater when no reader implements them.
• Keep eager migration on open: canonical only for app-owned embedded databases
  with a single owning version; wrong convention for a shared artifact. The right
  model is SQLite's own file format: newer libraries write compatibly and only bump
  file requirements when a new feature is actually used.

Acceptance criteria

• All four cells of the version-mixing matrix work; opening a file never modifies it.
• group_id round-trips through storage on new code; old-code-written tasks read
  back with group_id = None.
• bloqade_schema row is never rewritten; stray-row scenario can no longer corrupt
  migration.
• user_version-based schema_version / min_reader_version checks and
  sequential migration chain in place, with tests covering: old file, new file,
  file with future min_reader_version (must refuse), file with future
  schema_version but compatible min_reader_version (must proceed).
• Mixed-version round-trip regression test (new writes → old reads/writes → new
  reads), using the main version of local_storage.py against the new file.

References

• PR feat: integration with QLAM 0.6.0 #101 (feat: integration with QLAM 0.5.0) — introduces group_id, the 0.2.0
  bump, and the eager on-open migration (src/bloqade/core/device/local_storage.py).
• Old reader check: src/bloqade/core/device/local_storage.py on main (strict
  equality in SQLiteStorage.__init__).