mirror of
https://github.com/maybe-finance/maybe.git
synced 2025-08-08 06:55:21 +02:00
da2045dbd8
* Initial schema iteration * Add new balance components * Add existing data migrator to backfill components * Update calculator test assertions for new balance components * Update flow assertions for forward calculator * Update reverse calculator flows assumptions * Forward calculator tests passing * Get all calculator tests passing * Assert flows factor
59 lines
2.2 KiB
Ruby
59 lines
2.2 KiB
Ruby
class BalanceComponentMigrator
|
|
def self.run
|
|
ActiveRecord::Base.transaction do
|
|
# Step 1: Update flows factor
|
|
ActiveRecord::Base.connection.execute <<~SQL
|
|
UPDATE balances SET
|
|
flows_factor = CASE WHEN a.classification = 'asset' THEN 1 ELSE -1 END
|
|
FROM accounts a
|
|
WHERE a.id = balances.account_id
|
|
SQL
|
|
|
|
# Step 2: Set start values using LOCF (Last Observation Carried Forward)
|
|
ActiveRecord::Base.connection.execute <<~SQL
|
|
UPDATE balances b1
|
|
SET
|
|
start_cash_balance = COALESCE(prev.cash_balance, 0),
|
|
start_non_cash_balance = COALESCE(prev.balance - prev.cash_balance, 0)
|
|
FROM balances b1_inner
|
|
LEFT JOIN LATERAL (
|
|
SELECT
|
|
b2.cash_balance,
|
|
b2.balance
|
|
FROM balances b2
|
|
WHERE b2.account_id = b1_inner.account_id
|
|
AND b2.currency = b1_inner.currency
|
|
AND b2.date < b1_inner.date
|
|
ORDER BY b2.date DESC
|
|
LIMIT 1
|
|
) prev ON true
|
|
WHERE b1.id = b1_inner.id
|
|
SQL
|
|
|
|
# Step 3: Calculate net inflows
|
|
# A slight workaround to the fact that we can't easily derive inflows/outflows from our current data model, and
|
|
# the tradeoff not worth it since each new sync will fix it. So instead, we sum up *net* flows, and throw the signed
|
|
# amount in the "inflows" column, and zero-out the "outflows" column so our math works correctly with incomplete data.
|
|
ActiveRecord::Base.connection.execute <<~SQL
|
|
UPDATE balances SET
|
|
cash_inflows = (cash_balance - start_cash_balance) * flows_factor,
|
|
cash_outflows = 0,
|
|
non_cash_inflows = ((balance - cash_balance) - start_non_cash_balance) * flows_factor,
|
|
non_cash_outflows = 0,
|
|
net_market_flows = 0
|
|
SQL
|
|
|
|
# Verify data integrity
|
|
# All end_balance values should match the original balance
|
|
invalid_count = ActiveRecord::Base.connection.select_value(<<~SQL)
|
|
SELECT COUNT(*)
|
|
FROM balances b
|
|
WHERE ABS(b.balance - b.end_balance) > 0.0001
|
|
SQL
|
|
|
|
if invalid_count > 0
|
|
raise "Data migration failed validation: #{invalid_count} balances have incorrect end_balance values"
|
|
end
|
|
end
|
|
end
|
|
end
|