Wrap workspace_history DDL migration in explicit transaction

[mmx003]

Problem

The workspace_history table migration in init_db() (sessions.py:107-130) performs four sequential operations without an explicit transaction:

CREATE TABLE workspace_history_new (...)
INSERT INTO workspace_history_new (...) SELECT ... FROM workspace_history
DROP TABLE workspace_history
ALTER TABLE workspace_history_new RENAME TO workspace_history

Python's sqlite3 module (and aiosqlite on top of it) uses isolation_level="" by default, which implicitly commits before DDL statements. This means each DDL operation runs in its own implicit transaction. If the process crashes between DROP TABLE and ALTER TABLE ... RENAME, the original table is gone and the new table still has its temporary name — data is effectively lost.

With large workspace_history tables, the INSERT ... SELECT can take non-trivial time, widening the vulnerability window.

Fix

Wrap the migration in an explicit transaction. SQLite fully supports DDL inside transactions:

await _get_db().execute("BEGIN IMMEDIATE")
try:
    await _get_db().execute("CREATE TABLE workspace_history_new (...)")
    await _get_db().execute("INSERT INTO workspace_history_new ... SELECT ...")
    await _get_db().execute("DROP TABLE workspace_history")
    await _get_db().execute("ALTER TABLE workspace_history_new RENAME TO workspace_history")
    await _get_db().execute("COMMIT")
except Exception:
    await _get_db().execute("ROLLBACK")
    raise

This makes the entire migration atomic — either all four steps succeed, or none of them take effect.

Introduced in #98.

[dcellison]

Triage by Kai

Triage summary: The workspace_history table migration runs four DDL steps without an explicit transaction, creating a data-loss window if the process crashes between DROP TABLE and the RENAME. The fix is well-defined: wrap the migration in BEGIN IMMEDIATE / COMMIT with a ROLLBACK on failure.

Priority: high
Labels applied: bug, good first issue