nautechsystems/nautilus_trader · error · anyhow::Error

Failed to record migrated terminal transition: {e}

Error message

Failed to record migrated terminal transition: {e}

What it means

This error wraps sqlx failures from the INSERT into execution_transaction_transition that records the status transition (from the locked current_status to the terminal status) for a migrated intent. It runs only when current_status differs from the target status and uses INSERT ... SELECT from execution_transaction_hash. It fails when the INSERT errors at the DB level: constraint conflicts (e.g. duplicate transition_key on re-run), FK violations, type mismatches, or connection failures. The driver error is interpolated as {e}.

Source

Thrown at crates/adapters/blockchain/src/cache/database.rs:4308

                                    intent_id, transaction_hash_id, transition_key,
                                    from_status, to_status, block_number, block_hash
                                )
                                SELECT $1, id, $3, $4, $5, $6, $7
                                FROM execution_transaction_hash
                                WHERE intent_id = $1 AND transaction_hash = $2
                                ",
                            )
                            .bind(record.intent_id)
                            .bind(transaction_hash)
                            .bind(format!("migration:{}:{transaction_hash}", status.as_str()))
                            .bind(current_status)
                            .bind(status.as_str())
                            .bind(block_number)
                            .bind(block_hash)
                            .execute(&mut *transaction)
                            .await
                            .map_err(|e| {
                                anyhow::anyhow!(
                                    "Failed to record migrated terminal transition: {e}"
                                )
                            })?;
                        }
                    }

                    let nonce = record
                        .nonce
                        .map(i64::try_from)
                        .transpose()
                        .context("Migration nonce exceeds PostgreSQL BIGINT")?;

                    for (index, decision) in record.decisions.iter().enumerate() {
                        let height_start = decision
                            .height_start
                            .map(i64::try_from)
                            .transpose()
                            .context("Migration evidence height exceeds PostgreSQL BIGINT")?;

View on GitHub (pinned to 18893faf8b)

Solutions

1. Read the interpolated {e}; a duplicate-key error names the unique constraint — check whether the migration already applied for this intent.
2. Make the migration idempotent: add ON CONFLICT (transition_key) DO NOTHING, or track applied migrations in a marker table and skip them.
3. Confirm the execution_transaction_hash row still exists for the intent (the INSERT ... SELECT depends on it).
4. Verify the execution_transaction_transition schema (transition_key length, FKs, block column types) matches what the migration binds.
5. If the error was a transient connection failure, re-run the migration; the rollback leaves no partial transition rows.

Example fix

// before
SELECT $1, id, $3, $4, $5, $6, $7
FROM execution_transaction_hash
WHERE intent_id = $1 AND transaction_hash = $2

// after: idempotent on re-run
SELECT $1, id, $3, $4, $5, $6, $7
FROM execution_transaction_hash
WHERE intent_id = $1 AND transaction_hash = $2
ON CONFLICT (transition_key) DO NOTHING

let already = sqlx::query_scalar::<_, i64>(
    "SELECT COUNT(*) FROM execution_transaction_transition WHERE transition_key = $1",
).bind(format!("migration:{}:{}", status.as_str(), transaction_hash))
 .fetch_one(&pool).await?;
anyhow::ensure!(already == 0, "transition already recorded; migration appears re-run");

fn transition_key_is_safe(status: &str, tx_hash: &str) -> bool {
    let key = format!("migration:{status}:{tx_hash}");
    key.len() <= 255 && tx_hash.chars().all(|c| c.is_ascii_hexdigit())
}

if let Err(e) = run_migration(&pool, records).await {
    let msg = format!("{e:#}");
    if msg.contains("Failed to record migrated terminal transition") && msg.contains("duplicate key") {
        tracing::warn!("transition already exists (idempotent re-run); treating as success");
    } else {
        return Err(e);
    }
}

When it happens

Trigger: The INSERT fails due to: a unique/PK conflict on transition_key (`migration:<status>:<hash>`) because the migration already ran once, an FK violation if the hash row is gone, column type/length mismatches (transition_key or block_hash too long), a lost connection, or the transaction already aborted by a previous failed statement.

Common situations: Re-running the migration (or an overlapping batch) after a previous run inserted the same transition_key; schema drift on execution_transaction_transition; DB connectivity drops mid-batch; transition keys exceeding a length-constrained column.

Related errors

• Failed to add pool event block hash storage: {e}
• Failed to insert into pool_swap table: {e}
• Failed to insert into pool_liquidity table: {e}
• Failed to lock legacy execution transactions: {e}
• Failed to migrate execution_transaction table: {e}

AI-assisted analysis of nautechsystems/nautilus_trader@18893faf8b (2026-09-08). Data as JSON: /api/errors/e7e3cd031e0263e6. Report an issue: GitHub.