nautechsystems/nautilus_trader · error

Failed to insert into account_event table: {e}

Error message

Failed to insert into account_event table: {e}

What it means

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.

Source

Thrown at crates/infrastructure/src/sql/queries.rs:1155

            ON CONFLICT (id)
            DO UPDATE
            SET
                kind = $2, account_id = $3, base_currency = $4, balances = $5, margins = $6, is_reported = $7,
                ts_event = $8, ts_init = $9, updated_at = CURRENT_TIMESTAMP
        "#)
            .bind(account_event.event_id.to_string())
            .bind(account_event.account_type.to_string())
            .bind(account_event.account_id.to_string())
            .bind(account_event.base_currency.map(|x| x.code.as_str()))
            .bind(balances)
            .bind(margins)
            .bind(account_event.is_reported)
            .bind(account_event.ts_event.to_string())
            .bind(account_event.ts_init.to_string())
            .execute(&mut *transaction)
            .await
            .map(|_| ())
            .map_err(|e| anyhow::anyhow!("Failed to insert into account_event table: {e}"))?;
        transaction
            .commit()
            .await
            .map_err(|e| anyhow::anyhow!("Failed to commit add_account transaction: {e}"))
    }

    /// Loads all account events for `account_id` via the provided `pool`.
    ///
    /// # Errors
    ///
    /// Returns an error if the SQL SELECT or deserialization fails.
    pub async fn load_account_events(
        pool: &PgPool,
        account_id: &AccountId,
    ) -> anyhow::Result<Vec<AccountState>> {
        sqlx::query_as::<_, AccountEventRow>(
            r#"SELECT * FROM "account_event" WHERE account_id = $1 ORDER BY created_at ASC"#,
        )

View on GitHub (pinned to 18893faf8b)

Solutions

1. Read the embedded sqlx error to classify: schema vs connection vs constraint.
2. Run all migrations so account_event matches the struct's fields, then retry.
3. For FK violations, ensure the account row exists (the preceding INSERT handles this — check it didn't fail silently in an earlier partial run).
4. Retry transient connection errors; the whole add_account transaction rolls back atomically on failure.

Example fix

// before: column missing in stale schema
ALTER TABLE account_event ADD COLUMN IF NOT EXISTS is_reported BOOLEAN NOT NULL DEFAULT false;
-- then retry add_account

// confirm schema has all bound columns
let cols = ["id","kind","account_id","base_currency","balances","margins","is_reported","ts_event","ts_init"];
for c in cols {
    sqlx::query("SELECT 1 FROM information_schema.columns WHERE table_name='account_event' AND column_name=$1")
        .bind(c).fetch_optional(pool).await?;
}

match add_account(&pool, updated, state).await {
    Err(e) if e.to_string().contains("column") => apply_migrations().await?,
    Err(e) if is_transient(&e) => retry_with_backoff(|| add_account(&pool, updated, state.clone())).await?,
    other => other?,
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• Failed to insert item {} into instrument table: {:?}
• Failed to insert into account table: {e}
• Failed to persist replacement hash {transaction_hash}: {e}
• Failed to seed chain table: {e}
• Failed to call create_block_partition for chain {}: {e}

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