diff options
| -rw-r--r-- | src/FundLab.Api/Persistence.fs | 58 | ||||
| -rw-r--r-- | tests/FundLab.Api.Tests/CashReconciliationTests.fs | 36 |
2 files changed, 85 insertions, 9 deletions
diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index f18ad68..0f81aea 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -7149,6 +7149,13 @@ type FundRepository(connectionString: string) = /// statement derives its amount straight from the same table the live hook reads, and /// the (fund_id, reference_type, reference_id) unique key makes re-runs no-ops, so a /// migrated database is unchanged and no movement is ever counted twice. + /// + /// Stock buys executed before the 3d-34 debit fix (2dedc30) never reduced + /// available_cash, so rebuilding only their ledger event would leave the fund + /// imbalanced by the cost. That batch runs last and also debits available_cash for the + /// funds still missing it, but only where the fund reconciles to zero before the batch + /// -- exactly the case where cash never paid for the buy. Funds whose debit already + /// happened are left untouched, so it stays idempotent. member _.BackfillCashLedger() : int = let statements = [ @@ -7174,15 +7181,6 @@ type FundRepository(connectionString: string) = """ INSERT INTO cash_ledger_events (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note) - SELECT gen_random_uuid(), t.fund_id, 'stock_buy', - (t.executed_at AT TIME ZONE 'UTC')::date, -t.cost_cash, - 'stock_buy', t.id::text, NULL - FROM stock_trades t - ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING - """ - """ - INSERT INTO cash_ledger_events - (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note) SELECT gen_random_uuid(), s.fund_id, 'stock_sell', (s.executed_at AT TIME ZONE 'UTC')::date, s.proceeds, 'stock_sell', s.id::text, NULL @@ -7239,6 +7237,48 @@ type FundRepository(connectionString: string) = WHERE o.confirmed_invested_cash IS NOT NULL ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING """ + """ + WITH candidate AS ( + SELECT t.fund_id, + t.id::text AS reference_id, + (t.executed_at AT TIME ZONE 'UTC')::date AS event_date, + -t.cost_cash AS amount + FROM stock_trades t + WHERE NOT EXISTS ( + SELECT 1 + FROM cash_ledger_events e + WHERE e.fund_id = t.fund_id + AND e.reference_type = 'stock_buy' + AND e.reference_id = t.id::text + ) + ), + inserted AS ( + INSERT INTO cash_ledger_events + (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note) + SELECT gen_random_uuid(), c.fund_id, 'stock_buy', c.event_date, c.amount, + 'stock_buy', c.reference_id, NULL + FROM candidate c + ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING + RETURNING fund_id, amount + ), + totals AS ( + SELECT fund_id, SUM(amount) AS amount + FROM inserted + GROUP BY fund_id + ), + missing_debit AS ( + SELECT f.id AS fund_id, t.amount + FROM funds f + JOIN totals t ON t.fund_id = f.id + WHERE f.initial_cash + + COALESCE((SELECT SUM(e.amount) FROM cash_ledger_events e WHERE e.fund_id = f.id), 0) + - (f.available_cash + f.reserved_cash) = 0 + ) + UPDATE funds f + SET available_cash = f.available_cash + m.amount + FROM missing_debit m + WHERE f.id = m.fund_id + """ ] use connection = new NpgsqlConnection(connectionString) diff --git a/tests/FundLab.Api.Tests/CashReconciliationTests.fs b/tests/FundLab.Api.Tests/CashReconciliationTests.fs index fa14bff..b31b358 100644 --- a/tests/FundLab.Api.Tests/CashReconciliationTests.fs +++ b/tests/FundLab.Api.Tests/CashReconciliationTests.fs @@ -366,6 +366,42 @@ type CashReconciliationTests(fixture: PostgresFixture) = Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference) [<Fact>] + member _.``backfill debits cash for a pre-3d-34 stock buy that never reduced available cash``() = + let fundId = createFund 10000.00m + + // a manual stock buy from before the 3d-34 debit fix: no ledger event and no debit + buyStockNoDebit fundId "600519" 100m 10.00m (fixture.Key "cash-pre34-buy") + + // simulate a database that predates the ledger + deleteLedgerEvents fundId + Assert.Equal(0L, ledgerEventCount fundId) + + let before = + repository().GetFund fundId |> Option.defaultWith (fun () -> failwith "fund was not found") + + Assert.Equal(10000.00m, before.AvailableCash) + + repository().BackfillCashLedger() |> ignore + + let after = + repository().GetFund fundId |> Option.defaultWith (fun () -> failwith "fund was not found") + + // the rebuilt stock_buy event is paired with the missing 1000.00 debit + Assert.Equal(9000.00m, after.AvailableCash) + Assert.Equal(1L, ledgerEventCount fundId) + Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference) + + // re-running must not debit twice or write another event + repository().BackfillCashLedger() |> ignore + + let rerun = + repository().GetFund fundId |> Option.defaultWith (fun () -> failwith "fund was not found") + + Assert.Equal(9000.00m, rerun.AvailableCash) + Assert.Equal(1L, ledgerEventCount fundId) + Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference) + + [<Fact>] member _.``manual stock buy debits available cash and records a stock_buy event``() = let fundId = createFund 10000.00m buyStock fundId "600519" 100m 10.00m (fixture.Key "cash-manual-stock-buy") |