Migrator: model().create()/.update()/.deleteAll() inside up()/down() silently rolls back the row (nested-transaction conflict with the migrator's outer transaction) #2789

Summary

Calling model().create(), .update(), or .deleteAll() inside a migration's up() or down() causes the inserted/updated row to be silently rolled back while related rows inserted in the same closure (via other code paths, e.g., raw queryExecute, addRecord(), or operations on a different model) persist as orphans referencing the rolled-back row's nonexistent ID.

Root cause: the Wheels Migrator (vendor/wheels/Migrator.cfc:98-118) already wraps each up()/down() call in its own transaction {} block. Wheels Model's default transaction = "commit" mode opens a NESTED transaction that immediately commits — at the JDBC level, on SQL Server this nested commit appears to mark a savepoint but doesn't actually release the row, and then the outer transaction's eventual transaction action="commit" doesn't see the nested-committed row to keep. The net effect for the user is that Model.create() "succeeds" (returns the object, validations pass, no errors) but no row appears in the database. Related rows inserted via paths that don't open a nested transaction (raw queryExecute, addRecord) commit normally with the outer transaction.

Repro

// app/migrator/migrations/20260521160000_seed_datapai_system_roles.cfc
component extends="wheels.migrator.Migration" hint="seeds five system roles with their permission assignments" {

    function up() {
        // Inside the migrator-provided outer transaction.

        var Role = model("DataPaiRole");
        var RolePerm = model("DataPaiRolePermission");

        // This call APPEARS to succeed — no errors, no validation failures.
        var role = Role.create(name="DataPaiSuperAdmin", description="bootstrap role", isSystem=true);

        // The role's auto-increment ID is populated (e.g., role.id == 115).
        // We can read it here.

        // Insert role-permission assignments using that ID via raw SQL.
        queryExecute(
            "INSERT INTO datapai_role_permissions (datapai_role_id, datapai_permission_id, createdAt, updatedAt) VALUES (?, ?, GETDATE(), GETDATE())",
            [
                {value: role.id, cfsqltype: "cf_sql_integer"},
                {value: 1, cfsqltype: "cf_sql_integer"}
            ],
            {datasource: application.wheels.dataSourceName}
        );
    }
}

After wheels migrate latest:

SELECT * FROM datapai_roles WHERE name = 'DataPaiSuperAdmin';
-- 0 rows. The create() was silently rolled back.

SELECT * FROM datapai_role_permissions WHERE datapai_role_id = 115;
-- 1 row. The raw INSERT committed via the outer transaction.
-- It now references role ID 115 which does NOT exist — an orphan FK.

The IDENTITY value 115 IS consumed (the next inserted row gets 116), confirming the INSERT ran and the row was alive at some point during the migration before being rolled back.

Impact

This is the most insidious failure mode we hit during Phase 0 of a real production application build. We didn't notice it during initial run of the migration — wheels migrate latest printed no errors, the migration was marked migrated in wheels_migrator_versions, and the migration's up() body completed without throwing. The bug only surfaced when downstream code that depended on those roles (a session-loader query joining datapai_roles against datapai_user_roles) returned zero results.

Every CFWheels engineer doing seed-style migrations is at risk. The plan documentation pattern (seed via Model.create() inside up()) is intuitive and the code APPEARS to work in isolation, so this lands silently and shows up later as data-integrity bugs.

We worked around it by rewriting the seed migration to use raw queryExecute() + the migrator's built-in addRecord() / updateRecord() / removeRecord() helpers exclusively. See paiindustries/titan PR #3337 commit ff15bb2ae for the working pattern.

Suggested fixes (any of)

1. Detect the outer transaction and short-circuit Model's transaction handling. When model("X").create() is invoked inside a context where transactionStatus() (or equivalent CFML reflection) shows an active transaction, skip the model's transaction="commit" wrapper and let the outer transaction handle commit/rollback. The model's transaction behavior is a feature for ad-hoc app-code use, not for batch contexts like migrations.

2. Print a warning when Model.create()/.update()/.deleteAll() is called from inside a wheels.migrator.Migration subclass. Use stack-frame inspection (callStackGet()) to detect the migrator context and emit a one-line warning recommending addRecord() or raw queryExecute() instead.

3. Document the limitation prominently in guides.wheels.dev/v4-0-0/migrations/ — at the top of the "Writing migrations" page, with a comparison table:

   Operation	OK inside migration?	Alternative
   model().create()	❌ silently rolls back	addRecord(table=..., col=val, ...)
   model().update()	❌	updateRecord(table=..., where=..., col=val, ...)
   model().deleteAll()	❌	removeRecord(table=..., where=...)
   queryExecute()	✅	—
   addRecord() / updateRecord() / removeRecord()	✅	—

4. Long-term: align Model's transaction mode with CFML's nested-transaction semantics on each adapter. SQL Server, MySQL, Oracle, and PostgreSQL all handle nested transactions differently. The current Wheels Model assumption that transaction="commit" is always safe doesn't hold inside an explicit outer transaction.

Repo / version

• Wheels Core: 4.0.0-SNAPSHOT+1779
• File: vendor/wheels/Migrator.cfc:98-118 (outer transaction wrapper)
• Adapter: MSSQL (MicrosoftSQLServerMigrator.cfc) — but I'd expect the same on other adapters whose JDBC drivers don't implement savepoint-based nested transactions.

Where found

Surfaced building the DataPAI portal seed migrations in Titan (Phase 0). The first seed migration (20260521150000_seed_datapai_permissions.cfc) hit it for the permissions table, and the second (20260521160000_seed_datapai_system_roles.cfc) hit it harder because it had a model-create-then-FK-insert pattern that left orphan join rows. Both migrations were rewritten to use raw SQL + migrator helpers; the rewrites are committed and tagged at datapai-phase-0-complete.