From c48b5560778548e2e87e05b1df5b170d7db1a345 Mon Sep 17 00:00:00 2001 From: "Somhairle H. Marisol" Date: Tue, 22 Sep 2026 06:20:25 +0800 Subject: Add stock sell vertical slice with cash recovery and snapshot pricing (3d-26) --- src/FundLab.Api/Persistence.fs | 366 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 366 insertions(+) (limited to 'src/FundLab.Api/Persistence.fs') diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index 09e3e0e..1cf68cd 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -327,6 +327,37 @@ type StockTradeWriteResult = | StockTradeInvalid of string | StockTradeFundNotFound +type StockSellCommand = + { + InstrumentCode: string + StockName: string option + Quantity: decimal + Price: decimal + FeeAmount: decimal + } + +type StockSellRecord = + { + Id: Guid + FundId: Guid + InstrumentCode: string + StockName: string option + Quantity: decimal + Price: decimal + FeeAmount: decimal + Proceeds: decimal + IsSynthetic: bool + ExecutedAt: DateTimeOffset + } + +type StockSellWriteResult = + | StockSellCreated of StockSellRecord + | StockSellReplayed of StockSellRecord + | StockSellIdempotencyConflict + | StockSellInvalid of string + | StockSellInsufficientHoldings of string + | StockSellFundNotFound + type BondTradeCommand = { InstrumentCode: string @@ -955,6 +986,27 @@ type FundRepository(connectionString: string) = PRIMARY KEY (fund_id, instrument_code) ); + CREATE TABLE IF NOT EXISTS stock_sells ( + id uuid PRIMARY KEY, + fund_id uuid NOT NULL REFERENCES funds(id), + instrument_code text NOT NULL, + stock_name text NULL, + quantity numeric(28, 8) NOT NULL CHECK (quantity > 0), + price numeric(20, 4) NOT NULL CHECK (price > 0), + fee_amount numeric(20, 2) NOT NULL CHECK (fee_amount >= 0), + proceeds numeric(20, 2) NOT NULL CHECK (proceeds >= 0), + is_synthetic boolean NOT NULL, + executed_at timestamptz NOT NULL + ); + + CREATE TABLE IF NOT EXISTS stock_sell_idempotencies ( + idempotency_key text PRIMARY KEY, + request_hash text NOT NULL, + sell_id uuid NOT NULL REFERENCES stock_sells(id), + fund_id uuid NOT NULL REFERENCES funds(id), + created_at timestamptz NOT NULL DEFAULT now() + ); + CREATE TABLE IF NOT EXISTS bond_trades ( id uuid PRIMARY KEY, fund_id uuid NOT NULL REFERENCES funds(id), @@ -1977,6 +2029,126 @@ type FundRepository(connectionString: string) = Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload))) + let stockSellRecordFromReader (reader: DbDataReader) : StockSellRecord = + { + Id = reader.GetGuid(0) + FundId = reader.GetGuid(1) + InstrumentCode = reader.GetString(2) + StockName = if reader.IsDBNull(3) then None else Some(reader.GetString(3)) + Quantity = reader.GetDecimal(4) + Price = reader.GetDecimal(5) + FeeAmount = reader.GetDecimal(6) + Proceeds = reader.GetDecimal(7) + IsSynthetic = reader.GetBoolean(8) + ExecutedAt = reader.GetFieldValue(9) + } + + let insertStockSell connection transaction (sell: StockSellRecord) = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO stock_sells + (id, fund_id, instrument_code, stock_name, quantity, price, fee_amount, proceeds, is_synthetic, executed_at) + VALUES + (@id, @fund_id, @instrument_code, @stock_name, @quantity, @price, @fee_amount, @proceeds, @is_synthetic, @executed_at) + """ + + addParameter command "id" NpgsqlDbType.Uuid (box sell.Id) |> ignore + addParameter command "fund_id" NpgsqlDbType.Uuid (box sell.FundId) |> ignore + addParameter command "instrument_code" NpgsqlDbType.Text (box sell.InstrumentCode) |> ignore + + let nameParameter = + match sell.StockName with + | Some name -> box name + | None -> box DBNull.Value + + addParameter command "stock_name" NpgsqlDbType.Text nameParameter |> ignore + addParameter command "quantity" NpgsqlDbType.Numeric (box sell.Quantity) |> ignore + addParameter command "price" NpgsqlDbType.Numeric (box sell.Price) |> ignore + addParameter command "fee_amount" NpgsqlDbType.Numeric (box sell.FeeAmount) |> ignore + addParameter command "proceeds" NpgsqlDbType.Numeric (box sell.Proceeds) |> ignore + addParameter command "is_synthetic" NpgsqlDbType.Boolean (box sell.IsSynthetic) |> ignore + addParameter command "executed_at" NpgsqlDbType.TimestampTz (box sell.ExecutedAt) |> ignore + command.ExecuteNonQuery() |> ignore + + let insertStockSellIdempotency connection transaction key requestHash sellId fundId = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO stock_sell_idempotencies (idempotency_key, request_hash, sell_id, fund_id) + VALUES (@idempotency_key, @request_hash, @sell_id, @fund_id) + """ + + addParameter command "idempotency_key" NpgsqlDbType.Text (box key) |> ignore + addParameter command "request_hash" NpgsqlDbType.Text (box requestHash) |> ignore + addParameter command "sell_id" NpgsqlDbType.Uuid (box sellId) |> ignore + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + command.ExecuteNonQuery() |> ignore + + let findStockSellIdempotency connection transaction key = + use command = + commandWithTransaction + connection + transaction + """ + SELECT request_hash, fund_id, sell_id + FROM stock_sell_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 findStockSell connection transaction sellId = + use command = + commandWithTransaction + connection + transaction + """ + SELECT id, fund_id, instrument_code, stock_name, quantity, price, fee_amount, proceeds, is_synthetic, executed_at + FROM stock_sells + WHERE id = @id + """ + + addParameter command "id" NpgsqlDbType.Uuid (box sellId) |> ignore + + use reader = command.ExecuteReader() + + if reader.Read() then + Some(stockSellRecordFromReader reader) + else + None + + let stockSellRequestHash (fundId: Guid) (command: StockSellCommand) = + let invariant = CultureInfo.InvariantCulture + let encoded (value: string) = sprintf "%d:%s" value.Length value + let name = command.StockName |> Option.defaultValue "" + + let payload = + String.concat + "|" + [ + "stock-sell" + encoded (fundId.ToString("D")) + encoded command.InstrumentCode + encoded name + encoded (command.Quantity.ToString("G29", invariant)) + encoded (command.Price.ToString("G29", invariant)) + encoded (command.FeeAmount.ToString("G29", invariant)) + ] + + Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload))) + let bondTradeRecordFromReader (reader: DbDataReader) : BondTradeRecord = { Id = reader.GetGuid(0) @@ -4791,6 +4963,200 @@ type FundRepository(connectionString: string) = records |> Seq.toList + member _.GetStockSells(fundId: Guid) : StockSellRecord list = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + use command = + commandWithTransaction + connection + None + """ + SELECT id, fund_id, instrument_code, stock_name, quantity, price, fee_amount, proceeds, is_synthetic, executed_at + FROM stock_sells + WHERE fund_id = @fund_id + ORDER BY executed_at, id + """ + + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + + use reader = command.ExecuteReader() + let records = ResizeArray() + + while reader.Read() do + records.Add(stockSellRecordFromReader reader) + + records |> Seq.toList + + member _.CreateStockSell(idempotencyKey: string, fundId: Guid, command: StockSellCommand, ?executedAtOverride: DateTimeOffset) : StockSellWriteResult = + if String.IsNullOrWhiteSpace idempotencyKey then + StockSellWriteResult.StockSellInvalid "idempotency key cannot be empty" + else + let code = if isNull command.InstrumentCode then "" else command.InstrumentCode.Trim() + + if code.Length <> 6 || not (code |> Seq.forall Char.IsDigit) then + StockSellWriteResult.StockSellInvalid "stock code must contain exactly six digits" + elif command.Quantity <= 0m then + StockSellWriteResult.StockSellInvalid "quantity must be positive" + elif command.Price <= 0m then + StockSellWriteResult.StockSellInvalid "price must be positive" + elif command.FeeAmount < 0m then + StockSellWriteResult.StockSellInvalid "fee amount cannot be negative" + elif Decimal.Round(command.FeeAmount, 2) <> command.FeeAmount then + StockSellWriteResult.StockSellInvalid "fee amount exceeds cash precision" + else + let normalized = { command with InstrumentCode = code } + let gross = Decimal.Round(normalized.Quantity * normalized.Price, 2, MidpointRounding.AwayFromZero) + + if normalized.FeeAmount > gross then + StockSellWriteResult.StockSellInvalid "fee amount cannot exceed the sale proceeds" + else + let fingerprint = stockSellRequestHash fundId normalized + 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 findStockSellIdempotency connection (Some transaction) idempotencyKey with + | Some(existingHash, existingFundId, sellId) + when existingHash = fingerprint && existingFundId = fundId -> + match findStockSell connection (Some transaction) sellId with + | Some sell -> + transaction.Commit() + StockSellWriteResult.StockSellReplayed sell + | None -> + transaction.Rollback() + StockSellWriteResult.StockSellInvalid "idempotency record references a missing sale" + | Some _ -> + transaction.Rollback() + StockSellWriteResult.StockSellIdempotencyConflict + | None -> + match lockFundForOrder connection (Some transaction) fundId with + | None -> + transaction.Rollback() + StockSellWriteResult.StockSellFundNotFound + | Some isSynthetic -> + let position = + use positionCommand = + commandWithTransaction + connection + (Some transaction) + """ + SELECT quantity, cost_cash + FROM stock_positions + WHERE fund_id = @fund_id AND instrument_code = @instrument_code + FOR UPDATE + """ + + addParameter positionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter positionCommand "instrument_code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore + + use reader = positionCommand.ExecuteReader() + + if reader.Read() then + Some(reader.GetDecimal(0), reader.GetDecimal(1)) + else + None + + match position with + | None -> + transaction.Rollback() + StockSellWriteResult.StockSellInsufficientHoldings(sprintf "no stock position in %s to sell" normalized.InstrumentCode) + | Some(heldQuantity, _) when heldQuantity < normalized.Quantity -> + transaction.Rollback() + + StockSellWriteResult.StockSellInsufficientHoldings( + sprintf + "available holdings %s are not enough for the requested sale quantity %s" + (heldQuantity.ToString("G29", CultureInfo.InvariantCulture)) + (normalized.Quantity.ToString("G29", CultureInfo.InvariantCulture)) + ) + | Some(heldQuantity, heldCost) -> + let executedAt = defaultArg executedAtOverride DateTimeOffset.UtcNow + let proceeds = gross - normalized.FeeAmount + let remainingQuantity = heldQuantity - normalized.Quantity + + let releasedCost = + if remainingQuantity <= 0m then + heldCost + else + Decimal.Round(heldCost * (normalized.Quantity / heldQuantity), 2, MidpointRounding.AwayFromZero) + + if remainingQuantity <= 0m then + use deleteCommand = + commandWithTransaction + connection + (Some transaction) + "DELETE FROM stock_positions WHERE fund_id = @fund_id AND instrument_code = @instrument_code" + + addParameter deleteCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter deleteCommand "instrument_code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore + deleteCommand.ExecuteNonQuery() |> ignore + else + use updateCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE stock_positions + SET quantity = @quantity, + cost_cash = @cost_cash, + last_traded_at = @last_traded_at + WHERE fund_id = @fund_id AND instrument_code = @instrument_code + """ + + addParameter updateCommand "quantity" NpgsqlDbType.Numeric (box remainingQuantity) |> ignore + addParameter updateCommand "cost_cash" NpgsqlDbType.Numeric (box (max 0m (heldCost - releasedCost))) |> ignore + addParameter updateCommand "last_traded_at" NpgsqlDbType.TimestampTz (box executedAt) |> ignore + addParameter updateCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter updateCommand "instrument_code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore + updateCommand.ExecuteNonQuery() |> ignore + + use cashCommand = + commandWithTransaction + connection + (Some transaction) + "UPDATE funds SET available_cash = available_cash + @proceeds WHERE id = @fund_id" + + addParameter cashCommand "proceeds" NpgsqlDbType.Numeric (box proceeds) |> ignore + addParameter cashCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + cashCommand.ExecuteNonQuery() |> ignore + + let sell: StockSellRecord = + { + Id = Guid.NewGuid() + FundId = fundId + InstrumentCode = normalized.InstrumentCode + StockName = normalized.StockName + Quantity = normalized.Quantity + Price = normalized.Price + FeeAmount = normalized.FeeAmount + Proceeds = proceeds + IsSynthetic = isSynthetic + ExecutedAt = executedAt + } + + insertStockSell connection (Some transaction) sell + insertStockSellIdempotency connection (Some transaction) idempotencyKey fingerprint sell.Id fundId + transaction.Commit() + StockSellWriteResult.StockSellCreated sell + with error -> + try + transaction.Rollback() + with _ -> + () + + raise error + member _.CreateBondTrade(idempotencyKey: string, fundId: Guid, command: BondTradeCommand, ?executedAtOverride: DateTimeOffset) : BondTradeWriteResult = if String.IsNullOrWhiteSpace idempotencyKey then BondTradeWriteResult.BondTradeInvalid "idempotency key cannot be empty" -- cgit v1.2.3