{"record":{"id":"e7e3cd031e0263e6","repo":"nautechsystems/nautilus_trader","slug":"failed-to-record-migrated-terminal-transition-e","errorCode":null,"errorMessage":"Failed to record migrated terminal transition: {e}","messagePattern":"Failed to record migrated terminal transition: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":4308,"sourceCode":"                                    intent_id, transaction_hash_id, transition_key,\n                                    from_status, to_status, block_number, block_hash\n                                )\n                                SELECT $1, id, $3, $4, $5, $6, $7\n                                FROM execution_transaction_hash\n                                WHERE intent_id = $1 AND transaction_hash = $2\n                                \",\n                            )\n                            .bind(record.intent_id)\n                            .bind(transaction_hash)\n                            .bind(format!(\"migration:{}:{transaction_hash}\", status.as_str()))\n                            .bind(current_status)\n                            .bind(status.as_str())\n                            .bind(block_number)\n                            .bind(block_hash)\n                            .execute(&mut *transaction)\n                            .await\n                            .map_err(|e| {\n                                anyhow::anyhow!(\n                                    \"Failed to record migrated terminal transition: {e}\"\n                                )\n                            })?;\n                        }\n                    }\n\n                    let nonce = record\n                        .nonce\n                        .map(i64::try_from)\n                        .transpose()\n                        .context(\"Migration nonce exceeds PostgreSQL BIGINT\")?;\n\n                    for (index, decision) in record.decisions.iter().enumerate() {\n                        let height_start = decision\n                            .height_start\n                            .map(i64::try_from)\n                            .transpose()\n                            .context(\"Migration evidence height exceeds PostgreSQL BIGINT\")?;","sourceCodeStart":4290,"sourceCodeEnd":4326,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L4290-L4326","documentation":"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}.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Read the interpolated {e}; a duplicate-key error names the unique constraint — check whether the migration already applied for this intent.","Make the migration idempotent: add ON CONFLICT (transition_key) DO NOTHING, or track applied migrations in a marker table and skip them.","Confirm the execution_transaction_hash row still exists for the intent (the INSERT ... SELECT depends on it).","Verify the execution_transaction_transition schema (transition_key length, FKs, block column types) matches what the migration binds.","If the error was a transient connection failure, re-run the migration; the rollback leaves no partial transition rows."],"exampleFix":"// before\nSELECT $1, id, $3, $4, $5, $6, $7\nFROM execution_transaction_hash\nWHERE intent_id = $1 AND transaction_hash = $2\n\n// after: idempotent on re-run\nSELECT $1, id, $3, $4, $5, $6, $7\nFROM execution_transaction_hash\nWHERE intent_id = $1 AND transaction_hash = $2\nON CONFLICT (transition_key) DO NOTHING","handlingStrategy":"try-catch","validationCode":"let already = sqlx::query_scalar::<_, i64>(\n    \"SELECT COUNT(*) FROM execution_transaction_transition WHERE transition_key = $1\",\n).bind(format!(\"migration:{}:{}\", status.as_str(), transaction_hash))\n .fetch_one(&pool).await?;\nanyhow::ensure!(already == 0, \"transition already recorded; migration appears re-run\");","typeGuard":"fn transition_key_is_safe(status: &str, tx_hash: &str) -> bool {\n    let key = format!(\"migration:{status}:{tx_hash}\");\n    key.len() <= 255 && tx_hash.chars().all(|c| c.is_ascii_hexdigit())\n}","tryCatchPattern":"if let Err(e) = run_migration(&pool, records).await {\n    let msg = format!(\"{e:#}\");\n    if msg.contains(\"Failed to record migrated terminal transition\") && msg.contains(\"duplicate key\") {\n        tracing::warn!(\"transition already exists (idempotent re-run); treating as success\");\n    } else {\n        return Err(e);\n    }\n}","preventionTips":["Make the migration idempotent with ON CONFLICT (transition_key) DO NOTHING so re-runs cannot fail on duplicates.","Track applied migrations in a marker table and skip already-applied records.","Verify execution_transaction_transition schema (transition_key length, FK to hash table) before migrating.","Run migrations once from a single instance under a lock (e.g. pg_advisory_lock)."],"tags":["database","postgres","migration","insert-failed","idempotency"],"backgroundTag":"database-write-failed","analyzedSha":"18893faf8b356be3320add8de2f861b0b647cf06","analyzedAt":"2026-09-08T20:49:34.690Z","contentChangedAt":"2026-09-08T20:49:34.690Z","schemaVersion":2},"datasetVersion":"2026-09-14T05:17:10.506Z"}