sqlite-store: sync hard-fails with "new nonce N is less than old nonce M" while a local transaction is in flight

[WiktorStarczewski]

Symptom

On next (reproduced at rev 733720a7 via miden-client-cli against devnet 0.15.0), a sync issued between submitting a transaction for a public account and that transaction committing on-chain fails the entire sync with:

client error
├─▶ storage error
╰─▶ database-related non-query error: replace_account_header: new nonce 1 is
    less than old nonce 2 for account 0xf3e526607a6cc11109911065d6bda8

Repro (Miden Wallet blockchain e2e, fresh store every run): new-account ... --deploy a public faucet → mint → sync while the mint is still in flight. The node returns the account's pre-mint state (nonce 1); the local store holds the optimistic post-submit state (nonce 2).

Root cause

crates/sqlite-store/src/account/accounts.rs — replace_account_header (called from update_account_state, the sync path that replaces a public account's full state) errors when new.nonce < old.nonce:

• The guard was introduced with the watch-accounts work (feat: watch accounts #2143, e941a4314).
• During sync, "fetched on-chain state is older than local" is a normal, transient condition whenever a local transaction has been submitted but not yet committed — the correct behavior is to keep the newer local state and reconcile on a later sync, not to fail the whole sync.
• The condition self-heals (once the tx commits, nonces match and sync passes), but until then every sync errors, which downstream surfaces as cli::client_error / connectivity-looking failures.

Notably the web SDK's IndexedDB store does not error on the same sequence — the two store backends now diverge on this behavior.

Suggested fix

In update_account_state (or its caller in the sync applier), treat fetched.nonce < local.nonce as a skip (keep local state, return Ok), reserving the replace_account_header nonce guard for genuinely invalid replacements (e.g. apply_account_delta). Equal nonces with differing commitments should probably still error.

Workaround

The wallet's e2e CLI helper now treats this error string as transient and retries until the in-flight tx commits (0xMiden/wallet#277), but any CLI/sqlite-store consumer that syncs with a tx in flight will hit it.

[igamigo]

Thanks for opening the issue! Taking a look.

[igamigo]

Closed by #2247