From 9818daebaf6fb99124a3196f880b3b8a7998d3a6 Mon Sep 17 00:00:00 2001 From: "Somhairle H. Marisol" Date: Tue, 22 Sep 2026 11:08:05 +0800 Subject: Add cash ledger event-source breakdown and reconciliation API/UI (3d-31 B4c) --- src/FundLab.Api/App.fs | 77 ++++++++++++++ src/FundLab.Api/Persistence.fs | 223 +++++++++++++++++++++++++++++++++++++++++ 2 files changed, 300 insertions(+) (limited to 'src/FundLab.Api') diff --git a/src/FundLab.Api/App.fs b/src/FundLab.Api/App.fs index d5c3a50..3d89dae 100644 --- a/src/FundLab.Api/App.fs +++ b/src/FundLab.Api/App.fs @@ -487,6 +487,35 @@ type CapitalDepositResponse = createdAt: string } +type CashLedgerEventResponse = + { + source: string + eventDate: string + amount: string + referenceType: string + referenceId: string + note: string option + } + +type CashLedgerSourceResponse = + { + source: string + netAmount: string + eventCount: int + } + +type CashReconciliationResponse = + { + fundId: Guid + openingCash: string + netInflow: string + closingCash: string + ledgerBalance: string + difference: string + sources: CashLedgerSourceResponse list + events: CashLedgerEventResponse list + } + type FundReturnsPointResponse = { date: string @@ -846,6 +875,39 @@ module App = createdAt = timestampText deposit.CreatedAt } + let private cashLedgerEventResponse (event: CashLedger.CashLedgerEvent) : CashLedgerEventResponse = + { + source = CashLedger.sourceText event.Source + eventDate = dateText event.EventDate + amount = cashText event.Amount + referenceType = event.ReferenceType + referenceId = event.ReferenceId + note = event.Note + } + + let private cashReconciliationResponse + (fundId: Guid) + (reconciliation: CashLedger.CashReconciliation) + (events: CashLedger.CashLedgerEvent list) + : CashReconciliationResponse = + { + fundId = fundId + openingCash = cashText reconciliation.OpeningCash + netInflow = cashText reconciliation.NetInflow + closingCash = cashText reconciliation.ClosingCash + ledgerBalance = cashText reconciliation.LedgerBalance + difference = cashText reconciliation.Difference + sources = + reconciliation.Sources + |> List.map (fun total -> + { + source = CashLedger.sourceText total.Source + netAmount = cashText total.NetAmount + eventCount = total.EventCount + }) + events = events |> List.map cashLedgerEventResponse + } + let private sipPlanResponse (plan: SipPlanRecord) : SipPlanResponse = { id = plan.Id @@ -1763,6 +1825,20 @@ module App = with _ -> errorResponse 500 "PERSISTENCE_ERROR" "capital deposit persistence failed" next ctx + let private getCashReconciliation (repository: FundRepository) (fundIdText: string) : HttpHandler = + fun next ctx -> + match Guid.TryParse fundIdText with + | false, _ -> errorResponse 400 "INVALID_FUND_ID" "fund id must be a UUID" next ctx + | true, fundId -> + try + match repository.GetCashReconciliation fundId with + | Error _ -> errorResponse 404 "FUND_NOT_FOUND" "fund was not found" next ctx + | Ok reconciliation -> + let events = repository.GetCashLedger fundId + json (cashReconciliationResponse fundId reconciliation events) next ctx + with _ -> + errorResponse 500 "PERSISTENCE_ERROR" "cash reconciliation failed" next ctx + let private createSipPlan (repository: FundRepository) (fundIdText: string) : HttpHandler = fun next ctx -> task { @@ -3555,6 +3631,7 @@ module App = GET >=> routef "/funds/%s/positions" (getPositions repository) POST >=> routef "/funds/%s/capital/deposit" (createCapitalDeposit repository) GET >=> routef "/funds/%s/capital/deposits" (getCapitalDeposits repository) + GET >=> routef "/funds/%s/cash-reconciliation" (getCashReconciliation repository) POST >=> routef "/funds/%s/sip/plans" (createSipPlan repository) GET >=> routef "/funds/%s/sip/plans" (getSipPlans repository) POST >=> routef "/funds/%s/sip/advance" (advanceSipPlans repository probes) diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index 267eff3..47c4312 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -1019,6 +1019,22 @@ type FundRepository(connectionString: string) = created_at timestamptz NOT NULL DEFAULT now() ); + CREATE TABLE IF NOT EXISTS cash_ledger_events ( + id uuid PRIMARY KEY, + fund_id uuid NOT NULL REFERENCES funds(id), + source_category text NOT NULL, + event_date date NOT NULL, + amount numeric(20, 2) NOT NULL, + reference_type text NOT NULL, + reference_id text NOT NULL, + note text NULL, + created_at timestamptz NOT NULL DEFAULT now(), + UNIQUE (fund_id, reference_type, reference_id) + ); + + CREATE INDEX IF NOT EXISTS cash_ledger_events_fund_idx + ON cash_ledger_events (fund_id, event_date, created_at); + CREATE TABLE IF NOT EXISTS sip_plans ( id uuid PRIMARY KEY, fund_id uuid NOT NULL REFERENCES funds(id), @@ -1683,6 +1699,18 @@ type FundRepository(connectionString: string) = addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore command.ExecuteNonQuery() |> ignore + let findOrderIdempotencyKeyByOrderId connection transaction orderId = + use command = + commandWithTransaction + connection + transaction + "SELECT idempotency_key FROM subscription_order_idempotencies WHERE order_id = @order_id LIMIT 1" + + addParameter command "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + + use reader = command.ExecuteReader() + if reader.Read() then Some(reader.GetString(0)) else None + let upsertInstrument connection transaction (payload: MarketDataSearchPayload) payloadHash (instrument: MarketDataInstrument) = use command = commandWithTransaction @@ -2134,6 +2162,48 @@ type FundRepository(connectionString: string) = addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore command.ExecuteNonQuery() |> ignore + /// Append a signed cash movement to the ledger. The unique key on + /// (fund_id, reference_type, reference_id) makes replays no-ops, so a retried + /// operation never double-counts. Callers pass the same delta as the cash UPDATE. + let insertCashLedgerEvent + connection + transaction + (fundId: Guid) + (source: CashLedger.CashEventSource) + (eventDate: DateOnly) + (amount: decimal) + (referenceType: string) + (referenceId: string) + (note: string option) + = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO cash_ledger_events + (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note) + VALUES + (@id, @fund_id, @source_category, @event_date, @amount, @reference_type, @reference_id, @note) + ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING + """ + + addParameter command "id" NpgsqlDbType.Uuid (box (Guid.NewGuid())) |> ignore + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter command "source_category" NpgsqlDbType.Text (box (CashLedger.sourceText source)) |> ignore + addParameter command "event_date" NpgsqlDbType.Date (box eventDate) |> ignore + addParameter command "amount" NpgsqlDbType.Numeric (box amount) |> ignore + addParameter command "reference_type" NpgsqlDbType.Text (box referenceType) |> ignore + addParameter command "reference_id" NpgsqlDbType.Text (box referenceId) |> ignore + + let noteParameter = + match note with + | Some value -> box value + | None -> box DBNull.Value + + addParameter command "note" NpgsqlDbType.Text noteParameter |> ignore + command.ExecuteNonQuery() |> ignore + let capitalDepositRequestHash (fundId: Guid) (command: CapitalDepositCommand) = let invariant = CultureInfo.InvariantCulture let encoded (value: string) = sprintf "%d:%s" value.Length value @@ -5640,6 +5710,18 @@ type FundRepository(connectionString: string) = let createdAt = insertDividendRecord connection (Some transaction) record insertDividendIdempotency connection (Some transaction) schemeKey fingerprint recordId fundId + + insertCashLedgerEvent + connection + (Some transaction) + fundId + CashLedger.Dividend + command.NavDate + payout.GrossCash + "dividend" + (recordId.ToString("D")) + None + transaction.Commit() StageBooked { record with CreatedAt = createdAt } with error -> @@ -5843,6 +5925,18 @@ type FundRepository(connectionString: string) = let createdAt = insertCapitalDeposit connection (Some transaction) deposit insertCapitalDepositIdempotency connection (Some transaction) idempotencyKey fingerprint deposit.Id fundId + + insertCashLedgerEvent + connection + (Some transaction) + fundId + CashLedger.CapitalDeposit + (ConfirmationPolicy.eventDateFor deposit.CreatedAt) + command.Amount + "capital_deposit" + (deposit.Id.ToString("D")) + None + transaction.Commit() CapitalDepositWriteResult.CapitalDepositCreated { deposit with CreatedAt = createdAt } with error -> @@ -5943,6 +6037,18 @@ type FundRepository(connectionString: string) = insertStockTrade connection (Some transaction) trade insertStockTradeIdempotency connection (Some transaction) idempotencyKey fingerprint trade.Id fundId + if defaultArg debitAvailableCash false then + insertCashLedgerEvent + connection + (Some transaction) + fundId + CashLedger.StockBuy + (DateOnly.FromDateTime executedAt.UtcDateTime) + -costCash + "stock_buy" + (trade.Id.ToString("D")) + None + use positionCommand = commandWithTransaction connection @@ -6354,6 +6460,17 @@ type FundRepository(connectionString: string) = insertStockCashflow connection (Some transaction) sellEvent + insertCashLedgerEvent + connection + (Some transaction) + fundId + CashLedger.StockSell + (DateOnly.FromDateTime executedAt.UtcDateTime) + proceeds + "stock_sell" + (sell.Id.ToString("D")) + None + transaction.Commit() StockSellWriteResult.StockSellCreated sell with error -> @@ -6620,6 +6737,23 @@ type FundRepository(connectionString: string) = addParameter removeCommand "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore removeCommand.ExecuteNonQuery() |> ignore + let ledgerSource = + match normalized.EventType with + | "coupon" -> CashLedger.BondCoupon + | "maturity" -> CashLedger.BondMaturity + | _ -> CashLedger.BondSell + + insertCashLedgerEvent + connection + (Some transaction) + fundId + ledgerSource + record.EventDate + normalized.Amount + "bond_cashflow" + (record.Id.ToString("D")) + None + transaction.Commit() BondCashflowWriteResult.BondCashflowCreated record with error -> @@ -6825,6 +6959,17 @@ type FundRepository(connectionString: string) = addParameter positionCommand "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore positionCommand.ExecuteNonQuery() |> ignore + insertCashLedgerEvent + connection + (Some transaction) + fundId + CashLedger.BondSell + (DateOnly.FromDateTime executedAt.UtcDateTime) + proceeds + "bond_sell" + (record.Id.ToString("D")) + None + transaction.Commit() BondSellWriteResult.BondSellCreated record with error -> @@ -6914,6 +7059,56 @@ type FundRepository(connectionString: string) = records |> Seq.toList + member _.GetCashLedger(fundId: Guid) : CashLedger.CashLedgerEvent list = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + use command = + commandWithTransaction + connection + None + """ + SELECT source_category, event_date, amount, reference_type, reference_id, note + FROM cash_ledger_events + WHERE fund_id = @fund_id + ORDER BY event_date, created_at, id + """ + + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + + use reader = command.ExecuteReader() + let events = ResizeArray() + + while reader.Read() do + let source = + match CashLedger.parseSource (reader.GetString(0)) with + | Some value -> value + | None -> failwithf "unknown cash ledger source %s" (reader.GetString(0)) + + events.Add( + { + CashLedger.Source = source + CashLedger.EventDate = reader.GetFieldValue(1) + CashLedger.Amount = reader.GetDecimal(2) + CashLedger.ReferenceType = reader.GetString(3) + CashLedger.ReferenceId = reader.GetString(4) + CashLedger.Note = readStringOption reader 5 + } + ) + + events |> Seq.toList + + /// Opening cash (the fund's initial capital) plus every ledger movement must equal the + /// fund's actual cash (available plus reserved held against pending orders). The + /// difference is what the reconciliation endpoint exposes and tests assert is zero. + member this.GetCashReconciliation(fundId: Guid) : Result = + match this.GetFund fundId with + | None -> Error "fund was not found" + | Some fund -> + let events = this.GetCashLedger fundId + let ledgerBalance = fund.AvailableCash + fund.ReservedCash + Ok(CashLedger.reconcile fund.InitialCash ledgerBalance events) + member _.CreateRedemptionOrder(idempotencyKey: string, fundId: Guid, command: RedemptionCommand) : RedemptionWriteResult = if String.IsNullOrWhiteSpace idempotencyKey then RedemptionWriteResult.RedemptionInvalid "idempotency key cannot be empty" @@ -7325,6 +7520,18 @@ type FundRepository(connectionString: string) = confirmCommand.ExecuteNonQuery() |> ignore insertRedemptionConfirmIdempotency connection (Some transaction) idempotencyKey "" orderId fundId + + insertCashLedgerEvent + connection + (Some transaction) + fundId + CashLedger.Redemption + tradeDate + computation.Proceeds + "redemption" + (orderId.ToString("D")) + None + transaction.Commit() match findRedemptionOrder connection None orderId with @@ -7673,6 +7880,22 @@ type FundRepository(connectionString: string) = addParameter idempotencyCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore idempotencyCommand.ExecuteNonQuery() |> ignore + let ledgerSource = + match findOrderIdempotencyKeyByOrderId connection (Some transaction) orderId with + | Some key -> CashLedger.subscriptionSourceForKey key + | None -> CashLedger.Subscription + + insertCashLedgerEvent + connection + (Some transaction) + fundId + ledgerSource + quote.NavDate + -(computation.InvestedCash + order.FeeAmount) + "subscription_settle" + (orderId.ToString("D")) + None + transaction.Commit() match findOrder connection None orderId with -- cgit v1.2.3