From 8d98ba88f75852f5cc4b5d66c5b30210b7eefe6c Mon Sep 17 00:00:00 2001 From: "Somhairle H. Marisol" Date: Tue, 22 Sep 2026 11:59:00 +0800 Subject: Backfill cash ledger from historical transactions and add reconciliation QA (3d-32) --- src/FundLab.Api/Persistence.fs | 109 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 109 insertions(+) (limited to 'src/FundLab.Api/Persistence.fs') diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index 47c4312..2ca11cd 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -7109,6 +7109,115 @@ type FundRepository(connectionString: string) = let ledgerBalance = fund.AvailableCash + fund.ReservedCash Ok(CashLedger.reconcile fund.InitialCash ledgerBalance events) + /// Backfill cash-ledger events for funds whose history predates the ledger. Each + /// 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. + member _.BackfillCashLedger() : int = + let statements = + [ + """ + INSERT INTO cash_ledger_events + (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note) + SELECT gen_random_uuid(), d.fund_id, 'capital_deposit', + (d.created_at AT TIME ZONE 'Asia/Shanghai')::date, d.amount, + 'capital_deposit', d.id::text, NULL + FROM fund_capital_deposits d + 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(), r.fund_id, 'dividend', + r.nav_date, r.gross_cash, + 'dividend', r.id::text, NULL + FROM dividend_records r + WHERE r.gross_cash IS NOT NULL + 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(), 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 + FROM stock_sells s + 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(), c.fund_id, + CASE c.event_type + WHEN 'coupon' THEN 'bond_coupon' + WHEN 'maturity' THEN 'bond_maturity' + ELSE 'bond_sell' + END, + c.event_date, c.amount, + 'bond_cashflow', c.id::text, NULL + FROM bond_cashflow_events c + 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, 'bond_sell', + (s.executed_at AT TIME ZONE 'UTC')::date, s.proceeds, + 'bond_sell', s.id::text, NULL + FROM bond_sells s + 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(), o.fund_id, 'redemption', + COALESCE(o.confirmed_nav_date, o.trade_date), o.confirmed_proceeds, + 'redemption', o.id::text, NULL + FROM redemption_orders o + WHERE o.confirmed_proceeds IS NOT NULL + 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(), o.fund_id, + CASE + WHEN i.idempotency_key LIKE 'sip:%' OR i.idempotency_key LIKE 'investment-plan:%' + THEN 'sip' + ELSE 'subscription' + END, + COALESCE(o.confirmed_nav_date, o.trade_date), + -(o.confirmed_invested_cash + o.fee_amount), + 'subscription_settle', o.id::text, NULL + FROM subscription_orders o + LEFT JOIN subscription_order_idempotencies i ON i.order_id = o.id + WHERE o.confirmed_invested_cash IS NOT NULL + ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING + """ + ] + + use connection = new NpgsqlConnection(connectionString) + connection.Open() + use transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted) + + let mutable inserted = 0 + + for statement in statements do + use command = commandWithTransaction connection (Some transaction) statement + inserted <- inserted + command.ExecuteNonQuery() + + transaction.Commit() + inserted + member _.CreateRedemptionOrder(idempotencyKey: string, fundId: Guid, command: RedemptionCommand) : RedemptionWriteResult = if String.IsNullOrWhiteSpace idempotencyKey then RedemptionWriteResult.RedemptionInvalid "idempotency key cannot be empty" -- cgit v1.2.3