diff options
Diffstat (limited to 'src/FundLab.Api/Persistence.fs')
| -rw-r--r-- | src/FundLab.Api/Persistence.fs | 223 |
1 files changed, 223 insertions, 0 deletions
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 |