Automation tab unavailable: stale SQLite DB migration crashes automation service on startup

Human Description

I think this happens because I started with an older agent-canvas but new users would not experience this. I think.

---

Bug Description

When upgrading openhands-automation between versions that restructure their Alembic migration history, the automation service crashes on startup with:

Failed to apply SQLite migrations: Can't locate revision identified by '007'

This causes the automation backend to exit with code 3, making the Automate tab completely unavailable in the UI.

Root Cause

The automation service stores its SQLite database at ~/.openhands/automation/automations.db. When the openhands-automation package is updated to a version that has restructured its Alembic migration revisions (e.g., the old DB references revision 007 which no longer exists in the new migration chain), Alembic's upgrade head command fails because it can't find the revision the DB is currently at.

The dev script (dev-with-automation.mjs) spawns the automation backend but has no recovery logic for this failure — the service simply crashes and the tab remains broken.

Steps to Reproduce

1. Run npm run dev with an older version of openhands-automation so that the DB is created with an older migration chain
2. Update to a newer version of openhands-automation (e.g., via git pull on agent-canvas main which bumps the pinned version)
3. Run npm run dev again
4. Observe the automation service crash with the migration error
5. The Automate tab is unavailable

Expected Behavior

The dev script should detect the migration failure, automatically reset the stale database, and restart the automation service — with a clear log message explaining what happened.

Actual Behavior

The automation service exits with code 3 and logs a long traceback. The Automate tab shows as unavailable with no guidance on how to fix it.

Environment

• OS: Windows 11
• agent-canvas: main branch (latest)
• openhands-automation: 1.0.0a6

Relevant Logs

[automation] {"message": "Failed to apply SQLite migrations: Can't locate revision identified by '007'", "severity": "ERROR"}
[automation] {"message": "Application startup failed. Exiting.", "severity": "ERROR"}
[automation] Exited with code 3

Proposed Fix

Add migration-error detection and auto-recovery in startAutomationBackend() within scripts/dev-with-automation.mjs:

1. Capture automation service output and watch for migration error patterns
2. On crash with migration error, delete the stale automations.db and retry once
3. Log clear messages about what happened and what was done

This issue was created by an AI agent (OpenHands) on behalf of the user.