Concurrent opens can wedge aft.db permanently: V9 is re-runnable and lowers schema_version, V10 is not idempotent

[iceteaSA]

Summary

Two AFT processes opening the same aft.db at the same time can leave it permanently unopenable by every build: old ones refuse it as too new, new ones fail with table compression_event_rollups already exists. It happened here on a production store, and I can reproduce the end state deterministically.

The root cause is that run_migrations plans its work from a read taken outside any transaction, and MIGRATION_V9 happens to be re-runnable while MIGRATION_V10 is not.

Mechanism

run_migrations (crates/aft/src/db/mod.rs):

let db_version = current_schema_version(conn)?;   // read on the bare connection
if db_version > CURRENT_SCHEMA_VERSION { return Err(OpenError::DowngradeRefused { .. }); }
for version in (db_version + 1)..=CURRENT_SCHEMA_VERSION {
    apply_migration(conn, version)?;              // each step opens its own IMMEDIATE tx
}

The version read and the migration loop are not in the same transaction, so two openers can both plan 9..=10 from the same stale value.

MIGRATION_V9 is re-runnable by construction. It opens with DROP INDEX IF EXISTS on both indexes and ends with DROP TABLE bash_pattern_watches_without_task_fk, so applying it a second time succeeds. apply_migration then writes the version it just applied, so a re-application lowers schema_version.

MIGRATION_V10 is not idempotent. It creates compression_event_rollups, idx_compression_created, and compression_retention_cursor with no IF NOT EXISTS.

The interleaving:

1. Process A reads 8, applies V9 (commits, version 9), applies V10 (commits, version 10). The three V10 objects now exist.
2. Process B, planning from its own stale read of 8, applies V9 again. It succeeds, and its transaction sets schema_version back to 9.
3. Process B applies V10 → table compression_event_rollups already exists → its transaction rolls back, leaving version 9.

Final state: every V10 object present, schema_version = 9. Nothing can open it. A build at CURRENT_SCHEMA_VERSION = 8 refuses because 9 > 8; a build at 10 re-runs V10 and dies on the existing table.

Reproduction

From a DB already migrated to 10, apply V9's SQL again inside one transaction ending in DELETE FROM schema_version; INSERT INTO schema_version VALUES (9);. That is exactly what step 2 above does.

Then both builds refuse it:

v10 build: failed to open aft.db: database migration 9->10 failed:
           table compression_event_rollups already exists
v8  build: failed to open aft.db: database schema version 9 is newer than
           supported version 8 — running with JSON-only persistence

How we hit it

Ad-hoc cargo nextest/cargo test runs that did not carry the hermetic env from scripts/rust-test-gate.sh (which correctly unsets AFT_STORAGE_DIR/AFT_CACHE_DIR and sets its own XDG_DATA_HOME). They inherited the default data home and opened the real store, several test processes at once. That part is our mistake. The wedge it produced is not something a caller can recover from without hand-editing the database, which is why it seems worth reporting.

Impact was a live daemon logging ~3,800 standing roots reconciliation refused lines over 45 minutes. The already-open connection kept working, so only new connections failed, which made it look narrower than it was.

Recovery, for anyone who lands here

The two new tables are empty at this point, so nothing is lost:

DROP TABLE IF EXISTS compression_event_rollups;
DROP TABLE IF EXISTS compression_retention_cursor;
DROP INDEX IF EXISTS idx_compression_created;

Then open with a build at CURRENT_SCHEMA_VERSION >= 10; it migrates 9 → 10 cleanly. Verified against a 904 MB production copy and then on the live store: schema 10, quick_check ok, and the full .schema byte-identical to a freshly initialised v10 database.

On row counts, one caveat worth stating precisely: the migration itself preserves everything (on the offline copy the counts were unchanged at 262,874 bash_tasks, 283,105 compression_events, 67,812 backups). On the live daemon compression_events then dropped by 459 and backups by 8, which is the v10 retention fold doing its job on first open rather than anything the repair lost: compression_event_rollups picked up 26 rows covering 500 folded events, and compression_retention_cursor got its singleton.

Note the recovery requires a build that can reach 10. If the fleet is pinned to an 8-capable release, dropping the objects is not enough on its own.

Suggested fix

Two independent changes, either of which breaks the loop:

1. Take the write transaction before reading schema_version, or re-read it inside each migration's transaction and skip steps already applied. Right now the plan is computed from a value that another process can invalidate before the first step runs.
2. Make the migration DDL idempotent (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS). V9 already is, accidentally; V10 is not.

(1) is the real fix, since (2) alone still allows the version to regress. Worth noting that V9's accidental re-runnability is what makes the regression possible at all, so a future migration that is not re-runnable would fail differently rather than safely.

[aft-alfonso]

Confirmed from source, and your interleaving is exact: run_migrations plans the range from a read on the bare connection, each step opens its own IMMEDIATE transaction, V9 is re-runnable and apply_migration writes the version it just applied, so a stale-planned second opener regresses to 9 and then dies on V10's CREATE TABLE. Thank you for the deterministic end state and the recovery SQL — both go straight into the tests.

Fix in progress, all three parts: the version is re-read inside each step's transaction and an already-applied step is skipped (the version can then only advance); V10's DDL becomes IF NOT EXISTS as the belt, with a test that applies every migration twice; and the wedged state your fleet is already in (V10 objects present at version 9) opens and records 10 with one log line, so the hand repair is not required on stores that hit this before the fix lands. The 3,800-line reconciliation flood gets rate-limited to once per state change as well. Landing sha here when it is on main.

[aft-alfonso]

Shipped in v0.56.0: migrations are planned inside each step's own transaction with idempotent DDL, a database wedged at V9 with V10 objects self-heals, and a current schema is detected with a read-first fast path that takes no write locks. Thanks for the reproduction.