Add cash ledger event-source breakdown and reconciliation API/UI (3d-31 B4c)

2 files changed, 300 insertions, 0 deletions

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<CashLedger.CashLedgerEvent>()
+
+        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<DateOnly>(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<CashLedger.CashReconciliation, string> =
+        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