Balance issue after currency change in transfer updates

[antoniomrfranco]

Hello,

I encountered an issue with balances in Firefly III after making a currency change in one of my accounts. This issue specifically happens during transfer updates. Here’s the sequence of events:

1. I had two accounts with the same currency and created a transfer from Account A to Account B.
2. Later, I updated Account B to change its currency.
3. I sent an API request to PUT /api/v1/transactions to set foreign_amount and foreign_currency_id for the transfer. The transfer was successfully updated.
4. However, after restarting Firefly III, the balance of Account B changed unexpectedly.

When I checked the logs, I saw errors like this (where %d is the journal ID):

Sum of journal #%d is not zero, journal is broken and now fixed.

To investigate further, I ran some database queries and noticed that the foreign_amount and foreign_currency_id fields were NULL for the destination transaction of the transfer. This mismatch caused the errors and balance issues.

To resolve the immediate issue, I ran the following SQL script to fix these cases in the database:

CREATE TEMPORARY TABLE temp_transaction_updates AS
WITH source_transactions AS (
  SELECT
    t.id AS source_id,
    t.transaction_journal_id,
    t.amount AS source_amount,
    t.foreign_amount AS source_foreign_amount,
    t.transaction_currency_id AS source_currency_id,
    t.foreign_currency_id AS source_foreign_currency_id
  FROM
    transactions t
  JOIN
    transaction_journals tj ON t.transaction_journal_id = tj.id
  WHERE
    tj.transaction_type_id = 3 -- transfer
    AND t.amount < 0
),
destination_transactions AS (
  SELECT
    t.id AS destination_id,
    t.transaction_journal_id,
    t.amount AS destination_amount,
    t.foreign_amount AS destination_foreign_amount,
    t.transaction_currency_id AS destination_currency_id,
    t.foreign_currency_id AS destination_foreign_currency_id
  FROM
    transactions t
  JOIN
    transaction_journals tj ON t.transaction_journal_id = tj.id
  WHERE
    tj.transaction_type_id = 3 -- transfer
    AND t.amount > 0
),
non_zero_sum AS (
  SELECT
    transaction_journal_id,
    SUM(amount) AS the_sum
  FROM
    transactions
  WHERE
    deleted_at IS NULL
  GROUP BY transaction_journal_id
  HAVING SUM(amount) != 0
)
SELECT
  nzs.transaction_journal_id,
  st.source_id,
  st.source_amount,
  st.source_foreign_amount,
  st.source_currency_id,
  st.source_foreign_currency_id,
  dt.destination_id,
  dt.destination_amount,
  dt.destination_foreign_amount,
  dt.destination_currency_id,
  dt.destination_foreign_currency_id
FROM source_transactions st
JOIN destination_transactions dt ON st.transaction_journal_id = dt.transaction_journal_id
JOIN non_zero_sum nzs ON st.transaction_journal_id = nzs.transaction_journal_id
WHERE
  dt.destination_foreign_amount IS NULL
  AND dt.destination_foreign_currency_id IS NULL;


UPDATE transactions t
JOIN temp_transaction_updates tmp ON t.id = tmp.destination_id
SET
  t.foreign_amount = (tmp.source_amount * -1),
  t.foreign_currency_id = tmp.source_currency_id
WHERE
  t.foreign_amount IS NULL AND t.foreign_currency_id IS NULL;


DROP TEMPORARY TABLE temp_transaction_updates;

To address the root cause, I’ve also made a code change and will be raising a PR to fix this issue. The proposed fix ensures that foreign_amount and foreign_currency_id are properly set for destination transactions when handling foreign currency transfers.

I’d appreciate any feedback or thoughts on this! Let me know if you’ve encountered similar issues or have suggestions for improvement.

Thanks!

[JC5]

Nice find, and thanks for submitting a fix. I'll check it out!

[JC5]

Nice! Merged and fixed.