{"record":{"id":"81c72bc80aaf2a4e","repo":"nautechsystems/nautilus_trader","slug":"failed-to-insert-into-account-event-table-e","errorCode":null,"errorMessage":"Failed to insert into account_event table: {e}","messagePattern":"Failed to insert into account_event table: (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/infrastructure/src/sql/queries.rs","lineNumber":1155,"sourceCode":"            ON CONFLICT (id)\n            DO UPDATE\n            SET\n                kind = $2, account_id = $3, base_currency = $4, balances = $5, margins = $6, is_reported = $7,\n                ts_event = $8, ts_init = $9, updated_at = CURRENT_TIMESTAMP\n        \"#)\n            .bind(account_event.event_id.to_string())\n            .bind(account_event.account_type.to_string())\n            .bind(account_event.account_id.to_string())\n            .bind(account_event.base_currency.map(|x| x.code.as_str()))\n            .bind(balances)\n            .bind(margins)\n            .bind(account_event.is_reported)\n            .bind(account_event.ts_event.to_string())\n            .bind(account_event.ts_init.to_string())\n            .execute(&mut *transaction)\n            .await\n            .map(|_| ())\n            .map_err(|e| anyhow::anyhow!(\"Failed to insert into account_event table: {e}\"))?;\n        transaction\n            .commit()\n            .await\n            .map_err(|e| anyhow::anyhow!(\"Failed to commit add_account transaction: {e}\"))\n    }\n\n    /// Loads all account events for `account_id` via the provided `pool`.\n    ///\n    /// # Errors\n    ///\n    /// Returns an error if the SQL SELECT or deserialization fails.\n    pub async fn load_account_events(\n        pool: &PgPool,\n        account_id: &AccountId,\n    ) -> anyhow::Result<Vec<AccountState>> {\n        sqlx::query_as::<_, AccountEventRow>(\n            r#\"SELECT * FROM \"account_event\" WHERE account_id = $1 ORDER BY created_at ASC\"#,\n        )","sourceCodeStart":1137,"sourceCodeEnd":1173,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/infrastructure/src/sql/queries.rs#L1137-L1173","documentation":"add_account then upserts the full event into \"account_event\" with an ON CONFLICT (id) DO UPDATE. Failure here is wrapped with this message. Likely causes: schema drift (missing columns or changed types like the balances/margins JSON columns), connection failure, or constraint violations on account_id.","triggerScenarios":"The INSERT INTO \"account_event\" is rejected — column missing after an upgrade (e.g. is_reported added), type mismatch binding JSON values, FK violation on account, or a dead connection.","commonSituations":"Running a newer NautilusTrader against an older DB schema (or vice versa); the account row insert succeeded earlier but the connection dropped before this statement; JSON columns typed as text without cast in a custom schema variant.","solutions":["Read the embedded sqlx error to classify: schema vs connection vs constraint.","Run all migrations so account_event matches the struct's fields, then retry.","For FK violations, ensure the account row exists (the preceding INSERT handles this — check it didn't fail silently in an earlier partial run).","Retry transient connection errors; the whole add_account transaction rolls back atomically on failure."],"exampleFix":"// before: column missing in stale schema\nALTER TABLE account_event ADD COLUMN IF NOT EXISTS is_reported BOOLEAN NOT NULL DEFAULT false;\n-- then retry add_account","handlingStrategy":"retry","validationCode":"// confirm schema has all bound columns\nlet cols = [\"id\",\"kind\",\"account_id\",\"base_currency\",\"balances\",\"margins\",\"is_reported\",\"ts_event\",\"ts_init\"];\nfor c in cols {\n    sqlx::query(\"SELECT 1 FROM information_schema.columns WHERE table_name='account_event' AND column_name=$1\")\n        .bind(c).fetch_optional(pool).await?;\n}","typeGuard":null,"tryCatchPattern":"match add_account(&pool, updated, state).await {\n    Err(e) if e.to_string().contains(\"column\") => apply_migrations().await?,\n    Err(e) if is_transient(&e) => retry_with_backoff(|| add_account(&pool, updated, state.clone())).await?,\n    other => other?,\n}","preventionTips":["Keep DB schema and crate version in lockstep; run migrations on every upgrade.","Make account_event upserts idempotent (they already are via ON CONFLICT) and retry whole transactions.","Watch for FK issues by verifying the account row insert ran in the same transaction.","Alert on connection drops between statements in transactional paths."],"tags":["database","postgres","sqlx","account","upsert"],"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-14T00:17:10.932Z"}