Upgrade to v0.19.0 fails: V6__routines migration checksum mismatch on existing PostgreSQL databases

[milenkobeslic]

Description

Upgrading from v0.18.0 (or any earlier release) to v0.19.0 fails on startup with:

Error: Migration failed: applied migration V6__routines is different than filesystem one V6__routines

Root Cause

PR #1151 modified the already-applied V6__routines.sql migration in place, changing:

-- v0.18.0 (sha 36f63cb, 2930 bytes)
notify_user TEXT NOT NULL DEFAULT 'default',

to:

-- v0.19.0 (sha 9697251, 2903 bytes)
notify_user TEXT,

The PR also correctly added V13__owner_scope_notify_targets.sql to handle the schema change incrementally for existing databases. However, because V6 was also modified, refinery's checksum validation rejects the upgrade before V13 can run.

The PR body acknowledges this under Database Impact:

Adds migrations/V13__owner_scope_notify_targets.sql and updates fresh-schema migrations/V6__routines.sql so routines.notify_user can be NULL.

This works for fresh installs but breaks every existing PostgreSQL database upgrading to v0.19.0.

Steps to Reproduce

1. Install IronClaw v0.18.0 (or earlier) with PostgreSQL backend
2. Run it at least once so migrations V1–V12 are applied
3. Replace the binary with v0.19.0
4. Start IronClaw — fails immediately with the checksum error

Expected Behavior

Upgrade from v0.18.0 to v0.19.0 should apply V13 incrementally without touching the checksum of already-applied migrations.

Suggested Fix

Revert V6__routines.sql to its v0.18.0 content (sha 36f63cb). The V13__owner_scope_notify_targets.sql migration already handles the schema change correctly for both existing and fresh databases (since ALTER COLUMN DROP NOT NULL is a no-op if the column is already nullable).

Environment

• IronClaw v0.18.0 → v0.19.0 upgrade
• PostgreSQL 16 + pgvector 0.8.0
• Platform: aarch64 Linux (NVIDIA DGX Spark)
• Database backend: PostgreSQL (refinery migrations)

[qstommyshu]

Facing the same issue here

[teochenglim]

This help me resolve it, by dropping and recreating db, all data lose

$ dropdb ironclaw
$ createdb ironclaw

[milenkobeslic]

Dropping and recreating the database is not a viable workaround for anyone with existing data (signals, routines, workspace memory, secrets).

The root cause is that PR #1151 modified V6__routines.sql after it had already been applied to production databases. Migration files are immutable once applied — refinery checksums them at apply time and rejects any subsequent change.

The correct fix would be to:

1. Revert V6__routines.sql to its original content (matching the checksum already stored in refinery_schema_history)
2. Move the new changes into a new migration file (e.g., the next available version number)

This preserves upgrade compatibility for all existing installations.

[ilblackdragon]

Sorry totally missed this regression.
We will fix this today and add a system to check it doesn’t happen again