From b8811922f4e167d50efb8817da35a12d8a9d653b Mon Sep 17 00:00:00 2001 From: "Somhairle H. Marisol" Date: Mon, 21 Sep 2026 11:21:51 +0800 Subject: Add redemption order minimal closed loop (3d-3) Redeem confirmed holdings end to end with exact decimal semantics: submission freezes position units (reserved_units) after an available-units check without touching cash, confirmation settles at the trade-date NAV with evidence and deferral rules identical to subscriptions, credits the fund with proceeds net of the stated fee, and writes off position cost pro rata (full redemption removes the position row). Cash conservation holds at every step: frozen shares and receivable cash never appear as available cash before settlement. Domain gains RedemptionPolicy (request validation plus proceeds/fee/ cost-release computation) with unit tests. Persistence adds redemption_orders, idempotency tables, reserved_units on positions and Create/Confirm repository members; the API exposes POST/GET /funds/{id}/redemptions and POST .../confirm. The frontend adds a 06/REDEEM panel (submit, confirm, pending reasons, confirmed proceeds/cost display), frozen-share display on the holdings panel, and refresh wiring. Browser QA gains a K-series covering freeze, settlement, cash accounting, replay and over-redemption rejection. --- src/FundLab.Api/Persistence.fs | 689 ++++++++++++++++++++++++++++++++++++++++- 1 file changed, 683 insertions(+), 6 deletions(-) (limited to 'src/FundLab.Api/Persistence.fs') diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index 6080beb..85396d5 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -202,6 +202,7 @@ type FundPositionRecord = FundId: Guid InstrumentCode: string Units: decimal + ReservedUnits: decimal CostCash: decimal LastConfirmedAt: DateTimeOffset ValuationNav: decimal option @@ -209,6 +210,52 @@ type FundPositionRecord = ValuationCollectedAt: DateTimeOffset option } +type RedemptionCommand = + { + InstrumentCode: string + Units: decimal + FeeAmount: decimal + } + +type RedemptionOrderRecord = + { + Id: Guid + FundId: Guid + InstrumentCode: string + Units: decimal + FeeAmount: decimal + Status: string + IsSynthetic: bool + SubmittedAt: DateTimeOffset + TradeDate: DateOnly + ConfirmIdempotencyKey: string option + PendingReason: string option + ConfirmedAt: DateTimeOffset option + ConfirmedNav: decimal option + ConfirmedNavDate: DateOnly option + ConfirmedProceeds: decimal option + ConfirmedCostReleased: decimal option + } + +type RedemptionWriteResult = + | RedemptionCreated of RedemptionOrderRecord + | RedemptionReplayed of RedemptionOrderRecord + | RedemptionIdempotencyConflict + | RedemptionInvalid of string + | RedemptionFundNotFound + | RedemptionInstrumentNotFound + | RedemptionInsufficientUnits + +type RedemptionConfirmResult = + | RedemptionConfirmed of RedemptionOrderRecord + | RedemptionConfirmReplayed of RedemptionOrderRecord + | RedemptionPendingNav of RedemptionOrderRecord + | RedemptionConfirmIdempotencyConflict + | RedemptionAlreadyConfirmed + | RedemptionOrderNotFound + | RedemptionInvalidStatus + | RedemptionInvalid of string + type FundRepository(connectionString: string) = let cashMaximum = 999999999999999999.99m let unitNavMaximum = 99999999999999999999.99999999m @@ -349,6 +396,43 @@ type FundRepository(connectionString: string) = fund_id uuid NOT NULL REFERENCES funds(id), created_at timestamptz NOT NULL DEFAULT now() ); + + CREATE TABLE IF NOT EXISTS redemption_orders ( + id uuid PRIMARY KEY, + fund_id uuid NOT NULL REFERENCES funds(id), + instrument_code text NOT NULL REFERENCES instruments(code), + units numeric(28, 8) NOT NULL CHECK (units > 0), + fee_amount numeric(20, 2) NOT NULL CHECK (fee_amount >= 0), + status text NOT NULL, + is_synthetic boolean NOT NULL, + submitted_at timestamptz NOT NULL DEFAULT now(), + trade_date date NOT NULL, + confirm_idempotency_key text NULL, + pending_reason text NULL, + confirmed_at timestamptz NULL, + confirmed_nav numeric(28, 8) NULL, + confirmed_nav_date date NULL, + confirmed_proceeds numeric(20, 2) NULL, + confirmed_cost_released numeric(20, 2) NULL + ); + + CREATE TABLE IF NOT EXISTS redemption_order_idempotencies ( + idempotency_key text PRIMARY KEY, + request_hash text NOT NULL, + order_id uuid NOT NULL REFERENCES redemption_orders(id), + fund_id uuid NOT NULL REFERENCES funds(id), + created_at timestamptz NOT NULL DEFAULT now() + ); + + CREATE TABLE IF NOT EXISTS redemption_confirm_idempotencies ( + idempotency_key text PRIMARY KEY, + request_hash text NOT NULL, + order_id uuid NOT NULL REFERENCES redemption_orders(id), + fund_id uuid NOT NULL REFERENCES funds(id), + created_at timestamptz NOT NULL DEFAULT now() + ); + + ALTER TABLE fund_positions ADD COLUMN IF NOT EXISTS reserved_units numeric(28, 8) NOT NULL DEFAULT 0; """ let statusText status = @@ -897,6 +981,173 @@ type FundRepository(connectionString: string) = | FundAlreadyExists fundId -> sprintf "fund %O already exists" fundId | other -> sprintf "%A" other + let redemptionRecordFromReader (reader: DbDataReader) : RedemptionOrderRecord = + { + Id = reader.GetGuid(0) + FundId = reader.GetGuid(1) + InstrumentCode = reader.GetString(2) + Units = reader.GetDecimal(3) + FeeAmount = reader.GetDecimal(4) + Status = reader.GetString(5) + IsSynthetic = reader.GetBoolean(6) + SubmittedAt = reader.GetFieldValue(7) + TradeDate = reader.GetFieldValue(8) + ConfirmIdempotencyKey = readStringOption reader 9 + PendingReason = readStringOption reader 10 + ConfirmedAt = optionalDateTimeOffsetFromReader reader 11 + ConfirmedNav = readDecimalOption reader 12 + ConfirmedNavDate = + if reader.IsDBNull(13) then + None + else + Some(reader.GetFieldValue(13)) + ConfirmedProceeds = readDecimalOption reader 14 + ConfirmedCostReleased = readDecimalOption reader 15 + } + + let redemptionOrderColumns = + """ + SELECT id, fund_id, instrument_code, units, fee_amount, + status, is_synthetic, submitted_at, trade_date, + confirm_idempotency_key, pending_reason, confirmed_at, + confirmed_nav, confirmed_nav_date, confirmed_proceeds, confirmed_cost_released + FROM redemption_orders + """ + + let findRedemptionOrder connection transaction orderId = + use command = + commandWithTransaction connection transaction (redemptionOrderColumns + " WHERE id = @order_id") + + addParameter command "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + + use reader = command.ExecuteReader() + if reader.Read() then Some(redemptionRecordFromReader reader) else None + + let findRedemptionOrderIdempotency connection transaction key = + use command = + commandWithTransaction + connection + transaction + "SELECT request_hash, fund_id, order_id FROM redemption_order_idempotencies WHERE idempotency_key = @idempotency_key" + + addParameter command "idempotency_key" NpgsqlDbType.Text (box key) |> ignore + + use reader = command.ExecuteReader() + if reader.Read() then + Some(reader.GetString(0), reader.GetGuid(1), reader.GetGuid(2)) + else + None + + let findRedemptionConfirmIdempotency connection transaction key = + use command = + commandWithTransaction + connection + transaction + "SELECT request_hash, fund_id, order_id FROM redemption_confirm_idempotencies WHERE idempotency_key = @idempotency_key" + + addParameter command "idempotency_key" NpgsqlDbType.Text (box key) |> ignore + + use reader = command.ExecuteReader() + if reader.Read() then + Some(reader.GetString(0), reader.GetGuid(1), reader.GetGuid(2)) + else + None + + let insertRedemptionOrder connection transaction (order: RedemptionOrderRecord) = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO redemption_orders + (id, fund_id, instrument_code, units, fee_amount, + status, is_synthetic, trade_date) + VALUES + (@id, @fund_id, @instrument_code, @units, @fee_amount, + @status, @is_synthetic, @trade_date) + RETURNING submitted_at + """ + + addParameter command "id" NpgsqlDbType.Uuid (box order.Id) |> ignore + addParameter command "fund_id" NpgsqlDbType.Uuid (box order.FundId) |> ignore + addParameter command "instrument_code" NpgsqlDbType.Text (box order.InstrumentCode) |> ignore + addParameter command "units" NpgsqlDbType.Numeric (box order.Units) |> ignore + addParameter command "fee_amount" NpgsqlDbType.Numeric (box order.FeeAmount) |> ignore + addParameter command "status" NpgsqlDbType.Text (box order.Status) |> ignore + addParameter command "is_synthetic" NpgsqlDbType.Boolean (box order.IsSynthetic) |> ignore + addParameter command "trade_date" NpgsqlDbType.Date (box order.TradeDate) |> ignore + + use reader = command.ExecuteReader() + reader.Read() |> ignore + reader.GetFieldValue(0) + + let insertRedemptionOrderIdempotency connection transaction key requestHash orderId fundId = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO redemption_order_idempotencies (idempotency_key, request_hash, order_id, fund_id) + VALUES (@idempotency_key, @request_hash, @order_id, @fund_id) + """ + + addParameter command "idempotency_key" NpgsqlDbType.Text (box key) |> ignore + addParameter command "request_hash" NpgsqlDbType.Text (box requestHash) |> ignore + addParameter command "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + command.ExecuteNonQuery() |> ignore + + let insertRedemptionConfirmIdempotency connection transaction key requestHash orderId fundId = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO redemption_confirm_idempotencies (idempotency_key, request_hash, order_id, fund_id) + VALUES (@idempotency_key, @request_hash, @order_id, @fund_id) + """ + + addParameter command "idempotency_key" NpgsqlDbType.Text (box key) |> ignore + addParameter command "request_hash" NpgsqlDbType.Text (box requestHash) |> ignore + addParameter command "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + command.ExecuteNonQuery() |> ignore + + let redemptionRequestHash (fundId: Guid) (command: RedemptionCommand) = + let invariant = CultureInfo.InvariantCulture + let encoded (value: string) = sprintf "%d:%s" value.Length value + let code = if isNull command.InstrumentCode then "" else command.InstrumentCode + let payload = + String.concat + "|" + [ + "redemption-order" + encoded (fundId.ToString("D")) + encoded code + (encoded (command.Units.ToString("G29", invariant))) + (encoded (command.FeeAmount.ToString("G29", invariant))) + ] + + Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload))) + + let validateRedemptionCommand (command: RedemptionCommand) = + if String.IsNullOrWhiteSpace command.InstrumentCode then + Error "instrument code cannot be empty" + elif command.Units <= 0m then + Error "units must be positive" + elif command.FeeAmount < 0m then + Error "fee amount cannot be negative" + elif Decimal.Round(command.Units, 8) <> command.Units then + Error "units exceed supported precision" + elif Decimal.Round(command.FeeAmount, 2) <> command.FeeAmount then + Error "fee amount exceeds cash precision" + elif command.Units > RedemptionPolicy.unitsMaximum then + Error "units exceed database precision" + elif command.FeeAmount > cashMaximum then + Error "fee amount exceeds database precision" + else + Ok() + member _.EnsureSchema() = use connection = new NpgsqlConnection(connectionString) connection.Open() @@ -1200,6 +1451,431 @@ type FundRepository(connectionString: string) = records |> Seq.toList + member _.CreateRedemptionOrder(idempotencyKey: string, fundId: Guid, command: RedemptionCommand) : RedemptionWriteResult = + if String.IsNullOrWhiteSpace idempotencyKey then + RedemptionWriteResult.RedemptionInvalid "idempotency key cannot be empty" + else + match validateRedemptionCommand command with + | Error message -> RedemptionWriteResult.RedemptionInvalid message + | Ok() -> + let fingerprint = redemptionRequestHash fundId command + use connection = new NpgsqlConnection(connectionString) + connection.Open() + use transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted) + + try + use lockCommand = + commandWithTransaction + connection + (Some transaction) + "SELECT pg_advisory_xact_lock(hashtext(@lock_key))" + + addParameter lockCommand "lock_key" NpgsqlDbType.Text (box idempotencyKey) |> ignore + lockCommand.ExecuteNonQuery() |> ignore + + match findRedemptionOrderIdempotency connection (Some transaction) idempotencyKey with + | Some(existingHash, existingFundId, orderId) + when existingHash = fingerprint && existingFundId = fundId -> + match findRedemptionOrder connection (Some transaction) orderId with + | Some order -> + transaction.Commit() + RedemptionReplayed order + | None -> + transaction.Rollback() + RedemptionWriteResult.RedemptionInvalid "idempotency record references a missing order" + | Some _ -> + transaction.Rollback() + RedemptionIdempotencyConflict + | None -> + match lockFundForOrder connection (Some transaction) fundId with + | None -> + transaction.Rollback() + RedemptionFundNotFound + | Some isSynthetic -> + if instrumentExists connection (Some transaction) command.InstrumentCode then + use freezeCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE fund_positions + SET reserved_units = reserved_units + @units + WHERE fund_id = @fund_id + AND instrument_code = @code + AND units - reserved_units >= @units + """ + + addParameter freezeCommand "units" NpgsqlDbType.Numeric (box command.Units) |> ignore + addParameter freezeCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter freezeCommand "code" NpgsqlDbType.Text (box command.InstrumentCode) |> ignore + + if freezeCommand.ExecuteNonQuery() = 0 then + transaction.Rollback() + RedemptionInsufficientUnits + else + let submittedAt = DateTimeOffset.UtcNow + let order: RedemptionOrderRecord = + { + Id = Guid.NewGuid() + FundId = fundId + InstrumentCode = command.InstrumentCode + Units = command.Units + FeeAmount = command.FeeAmount + Status = "submitted" + IsSynthetic = isSynthetic + SubmittedAt = submittedAt + TradeDate = ConfirmationPolicy.tradeDateFor submittedAt + ConfirmIdempotencyKey = None + PendingReason = None + ConfirmedAt = None + ConfirmedNav = None + ConfirmedNavDate = None + ConfirmedProceeds = None + ConfirmedCostReleased = None + } + + let submittedAt = insertRedemptionOrder connection (Some transaction) order + insertRedemptionOrderIdempotency connection (Some transaction) idempotencyKey fingerprint order.Id fundId + transaction.Commit() + RedemptionCreated { order with SubmittedAt = submittedAt } + else + transaction.Rollback() + RedemptionInstrumentNotFound + with error -> + try + transaction.Rollback() + with _ -> + () + + raise error + + member _.GetRedemptionOrders(fundId: Guid) = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + use command = + commandWithTransaction connection None (redemptionOrderColumns + " WHERE fund_id = @fund_id ORDER BY submitted_at DESC, id") + + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + + use reader = command.ExecuteReader() + let records = ResizeArray() + + while reader.Read() do + records.Add(redemptionRecordFromReader reader) + + records |> Seq.toList + + member _.ConfirmRedemptionOrder(idempotencyKey: string, fundId: Guid, orderId: Guid) : RedemptionConfirmResult = + if String.IsNullOrWhiteSpace idempotencyKey then + RedemptionConfirmResult.RedemptionInvalid "idempotency key cannot be empty" + else + use connection = new NpgsqlConnection(connectionString) + connection.Open() + use transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted) + + try + let confirmedAt = DateTimeOffset.UtcNow + let today = ConfirmationPolicy.shanghaiDate confirmedAt + + use lockCommand = + commandWithTransaction + connection + (Some transaction) + "SELECT pg_advisory_xact_lock(hashtext(@lock_key))" + + addParameter lockCommand "lock_key" NpgsqlDbType.Text (box (sprintf "confirm-redemption:%O" orderId)) |> ignore + lockCommand.ExecuteNonQuery() |> ignore + + match findRedemptionOrder connection (Some transaction) orderId with + | None -> + transaction.Rollback() + RedemptionOrderNotFound + | Some order when order.FundId <> fundId -> + transaction.Rollback() + RedemptionOrderNotFound + | Some order -> + if order.Status = "confirmed" then + match order.ConfirmIdempotencyKey with + | Some storedKey when storedKey = idempotencyKey -> + transaction.Commit() + RedemptionConfirmReplayed order + | _ -> + match findRedemptionConfirmIdempotency connection (Some transaction) idempotencyKey with + | Some(_, _, storedOrderId) when storedOrderId = orderId -> + transaction.Commit() + RedemptionConfirmReplayed order + | Some _ -> + transaction.Rollback() + RedemptionConfirmIdempotencyConflict + | None -> + transaction.Rollback() + RedemptionAlreadyConfirmed + elif order.Status <> "submitted" && order.Status <> "pending_nav" then + transaction.Rollback() + RedemptionInvalidStatus + else + match findRedemptionConfirmIdempotency connection (Some transaction) idempotencyKey with + | Some _ -> + transaction.Rollback() + RedemptionConfirmIdempotencyConflict + | None -> + let tradeDate = order.TradeDate + let tradeDateText = tradeDate.ToString("yyyy-MM-dd") + + use quoteCommand = + commandWithTransaction + connection + (Some transaction) + """ + SELECT o.nav, o.nav_date, o.source, o.source_revision, o.source_collected_at, o.published_at, + o.source_payload_hash, o.first_seen_at, e.first_seen_at + FROM fund_nav_observations o + LEFT JOIN fund_nav_observation_evidence e + ON e.instrument_code = o.instrument_code + AND e.nav_date = o.nav_date + AND e.source_payload_hash = o.source_payload_hash + WHERE o.instrument_code = @code AND o.nav_date = @trade_date AND o.nav > 0 + ORDER BY o.source_collected_at DESC, o.published_at DESC NULLS LAST, o.source_revision DESC + LIMIT 1 + """ + + addParameter quoteCommand "code" NpgsqlDbType.Text (box order.InstrumentCode) |> ignore + addParameter quoteCommand "trade_date" NpgsqlDbType.Date (box tradeDate) |> ignore + + use quoteReader = quoteCommand.ExecuteReader() + let quoteFound = quoteReader.Read() + + let selectedQuote = + if quoteFound then + let evidenceFirstSeen = + if quoteReader.IsDBNull(8) then + None + else + Some(quoteReader.GetFieldValue(8)) + + Some + ({ + Nav = quoteReader.GetDecimal(0) + NavDate = quoteReader.GetFieldValue(1) + Source = quoteReader.GetString(2) + Revision = quoteReader.GetString(3) + CollectedAt = quoteReader.GetFieldValue(4) + PublishedAt = + if quoteReader.IsDBNull(5) then + None + else + Some(quoteReader.GetFieldValue(5)) + PayloadHash = quoteReader.GetString(6) + FirstSeenAt = quoteReader.GetFieldValue(7) + }, + evidenceFirstSeen) + else + None + + quoteReader.Close() + + let boundQuote = + match selectedQuote with + | Some(quote, Some firstSeen) -> Some { quote with FirstSeenAt = firstSeen } + | _ -> None + + let deferralReason = + match selectedQuote with + | None -> + Some(sprintf "nav for trade date %s is not available yet" tradeDateText) + | Some(_, evidenceFirstSeen) when evidenceFirstSeen.IsNone -> + Some(sprintf "nav revision for trade date %s has no observation evidence recorded" tradeDateText) + | Some(quote, _) when quote.FirstSeenAt > confirmedAt -> + Some(sprintf "nav revision for trade date %s was first observed after the confirmation attempt" tradeDateText) + | Some(quote, _) -> + ConfirmationPolicy.navDeferralReason + { + Nav = quote.Nav + NavDate = quote.NavDate + CollectedAt = quote.CollectedAt + PublishedAt = quote.PublishedAt + } + tradeDate + today + confirmedAt + + match deferralReason with + | Some reason -> + use pendingCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE redemption_orders + SET status = 'pending_nav', + pending_reason = @reason, + confirm_idempotency_key = NULL, + confirmed_at = NULL, + confirmed_nav = NULL, + confirmed_nav_date = NULL, + confirmed_proceeds = NULL, + confirmed_cost_released = NULL + WHERE id = @order_id + """ + + addParameter pendingCommand "reason" NpgsqlDbType.Text (box reason) |> ignore + addParameter pendingCommand "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + pendingCommand.ExecuteNonQuery() |> ignore + transaction.Commit() + + match findRedemptionOrder connection None orderId with + | Some pendingOrder -> RedemptionPendingNav pendingOrder + | None -> failwith "pending redemption disappeared after confirmation deferral" + | None -> + let nav = boundQuote |> Option.get |> fun quote -> quote.Nav + + use positionCommand = + commandWithTransaction + connection + (Some transaction) + """ + SELECT units, reserved_units, cost_cash + FROM fund_positions + WHERE fund_id = @fund_id AND instrument_code = @code + FOR UPDATE + """ + + addParameter positionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter positionCommand "code" NpgsqlDbType.Text (box order.InstrumentCode) |> ignore + + use positionReader = positionCommand.ExecuteReader() + let positionFound = positionReader.Read() + let positionUnits = if positionFound then positionReader.GetDecimal(0) else 0m + let positionReserved = if positionFound then positionReader.GetDecimal(1) else 0m + let positionCost = if positionFound then positionReader.GetDecimal(2) else 0m + positionReader.Close() + + if not positionFound || positionReserved < order.Units then + failwith "frozen units are missing for redemption confirmation" + + match RedemptionPolicy.compute order.Units nav order.FeeAmount positionCost positionUnits with + | Error message -> + use pendingCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE redemption_orders + SET status = 'pending_nav', + pending_reason = @reason + WHERE id = @order_id + """ + + addParameter pendingCommand "reason" NpgsqlDbType.Text (box message) |> ignore + addParameter pendingCommand "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + pendingCommand.ExecuteNonQuery() |> ignore + transaction.Commit() + + match findRedemptionOrder connection None orderId with + | Some pendingOrder -> RedemptionPendingNav pendingOrder + | None -> failwith "pending redemption disappeared after computation failure" + | Ok computation -> + use settlePositionCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE fund_positions + SET units = units - @units, + reserved_units = reserved_units - @units, + cost_cash = cost_cash - @cost_released + WHERE fund_id = @fund_id + AND instrument_code = @code + AND units > @units + AND reserved_units >= @units + AND cost_cash >= @cost_released + """ + + addParameter settlePositionCommand "units" NpgsqlDbType.Numeric (box order.Units) |> ignore + addParameter settlePositionCommand "cost_released" NpgsqlDbType.Numeric (box computation.RedeemedCost) |> ignore + addParameter settlePositionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter settlePositionCommand "code" NpgsqlDbType.Text (box order.InstrumentCode) |> ignore + + if settlePositionCommand.ExecuteNonQuery() = 0 then + use deletePositionCommand = + commandWithTransaction + connection + (Some transaction) + """ + DELETE FROM fund_positions + WHERE fund_id = @fund_id + AND instrument_code = @code + AND units = @units + AND reserved_units >= @units + AND cost_cash >= @cost_released + """ + + addParameter deletePositionCommand "units" NpgsqlDbType.Numeric (box order.Units) |> ignore + addParameter deletePositionCommand "cost_released" NpgsqlDbType.Numeric (box computation.RedeemedCost) |> ignore + addParameter deletePositionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter deletePositionCommand "code" NpgsqlDbType.Text (box order.InstrumentCode) |> ignore + + if deletePositionCommand.ExecuteNonQuery() = 0 then + failwith "position units changed during redemption confirmation" + + use cashCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE funds + SET available_cash = available_cash + @proceeds + WHERE id = @fund_id + """ + + addParameter cashCommand "proceeds" NpgsqlDbType.Numeric (box computation.Proceeds) |> ignore + addParameter cashCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + + if cashCommand.ExecuteNonQuery() = 0 then + failwith "fund disappeared during redemption confirmation" + + use confirmCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE redemption_orders + SET status = 'confirmed', + pending_reason = NULL, + confirm_idempotency_key = @idempotency_key, + confirmed_at = @confirmed_at, + confirmed_nav = @nav, + confirmed_nav_date = @nav_date, + confirmed_proceeds = @proceeds, + confirmed_cost_released = @cost_released + WHERE id = @order_id + """ + + addParameter confirmCommand "idempotency_key" NpgsqlDbType.Text (box idempotencyKey) |> ignore + addParameter confirmCommand "confirmed_at" NpgsqlDbType.TimestampTz (box confirmedAt) |> ignore + addParameter confirmCommand "nav" NpgsqlDbType.Numeric (box nav) |> ignore + addParameter confirmCommand "nav_date" NpgsqlDbType.Date (box tradeDate) |> ignore + addParameter confirmCommand "proceeds" NpgsqlDbType.Numeric (box computation.Proceeds) |> ignore + addParameter confirmCommand "cost_released" NpgsqlDbType.Numeric (box computation.RedeemedCost) |> ignore + addParameter confirmCommand "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + confirmCommand.ExecuteNonQuery() |> ignore + + insertRedemptionConfirmIdempotency connection (Some transaction) idempotencyKey "" orderId fundId + transaction.Commit() + + match findRedemptionOrder connection None orderId with + | Some confirmedOrder -> RedemptionConfirmed confirmedOrder + | None -> failwith "confirmed redemption disappeared after commit" + + with error -> + try + transaction.Rollback() + with _ -> + () + + raise error + member _.ConfirmSubscriptionOrder(idempotencyKey: string, fundId: Guid, orderId: Guid) = if String.IsNullOrWhiteSpace idempotencyKey then ConfirmInvalid "idempotency key cannot be empty" @@ -1556,7 +2232,7 @@ type FundRepository(connectionString: string) = connection None """ - SELECT p.instrument_code, p.units, p.cost_cash, p.last_confirmed_at, + SELECT p.instrument_code, p.units, p.reserved_units, p.cost_cash, p.last_confirmed_at, q.nav, q.nav_date, q.source_collected_at FROM fund_positions p LEFT JOIN LATERAL ( @@ -1581,21 +2257,22 @@ type FundRepository(connectionString: string) = while reader.Read() do let valuationNav = - if reader.IsDBNull(4) then None else Some(reader.GetDecimal(4)) + if reader.IsDBNull(5) then None else Some(reader.GetDecimal(5)) let valuationNavDate = - if reader.IsDBNull(5) then None else Some(reader.GetFieldValue(5)) + if reader.IsDBNull(6) then None else Some(reader.GetFieldValue(6)) let valuationCollectedAt = - if reader.IsDBNull(6) then None else Some(reader.GetFieldValue(6)) + if reader.IsDBNull(7) then None else Some(reader.GetFieldValue(7)) records.Add( { FundId = fundId InstrumentCode = reader.GetString(0) Units = reader.GetDecimal(1) - CostCash = reader.GetDecimal(2) - LastConfirmedAt = reader.GetFieldValue(3) + ReservedUnits = reader.GetDecimal(2) + CostCash = reader.GetDecimal(3) + LastConfirmedAt = reader.GetFieldValue(4) ValuationNav = valuationNav ValuationNavDate = valuationNavDate ValuationCollectedAt = valuationCollectedAt -- cgit v1.2.3