From b6008d67232756d743699472865025159f6ec533 Mon Sep 17 00:00:00 2001 From: "Somhairle H. Marisol" Date: Tue, 22 Sep 2026 04:29:42 +0800 Subject: Add stock buy to holdings minimal vertical slice (3d-21) --- src/FundLab.Api/Persistence.fs | 351 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 351 insertions(+) (limited to 'src/FundLab.Api/Persistence.fs') diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index 6028b7c..ab3fe0a 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -289,6 +289,44 @@ type SipPlanCommand = Frequency: SipFrequency } +type StockTradeCommand = + { + InstrumentCode: string + StockName: string option + Quantity: decimal + Price: decimal + } + +type StockTradeRecord = + { + Id: Guid + FundId: Guid + InstrumentCode: string + StockName: string option + Quantity: decimal + Price: decimal + CostCash: decimal + IsSynthetic: bool + ExecutedAt: DateTimeOffset + } + +type StockPositionRecord = + { + FundId: Guid + InstrumentCode: string + StockName: string option + Quantity: decimal + CostCash: decimal + LastTradedAt: DateTimeOffset + } + +type StockTradeWriteResult = + | StockTradeCreated of StockTradeRecord + | StockTradeReplayed of StockTradeRecord + | StockTradeIdempotencyConflict + | StockTradeInvalid of string + | StockTradeFundNotFound + type SipPlanRecord = { Id: Guid @@ -829,6 +867,36 @@ type FundRepository(connectionString: string) = created_at timestamptz NOT NULL DEFAULT now() ); + CREATE TABLE IF NOT EXISTS stock_trades ( + 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), + cost_cash numeric(20, 2) NOT NULL CHECK (cost_cash >= 0), + is_synthetic boolean NOT NULL, + executed_at timestamptz NOT NULL + ); + + CREATE TABLE IF NOT EXISTS stock_trade_idempotencies ( + idempotency_key text PRIMARY KEY, + request_hash text NOT NULL, + trade_id uuid NOT NULL REFERENCES stock_trades(id), + fund_id uuid NOT NULL REFERENCES funds(id), + created_at timestamptz NOT NULL DEFAULT now() + ); + + CREATE TABLE IF NOT EXISTS stock_positions ( + 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), + cost_cash numeric(20, 2) NOT NULL CHECK (cost_cash >= 0), + last_traded_at timestamptz NOT NULL, + PRIMARY KEY (fund_id, instrument_code) + ); + CREATE TABLE IF NOT EXISTS dividend_idempotencies ( idempotency_key text PRIMARY KEY, request_hash text NOT NULL, @@ -1687,6 +1755,123 @@ type FundRepository(connectionString: string) = Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload))) + let stockTradeRecordFromReader (reader: DbDataReader) : StockTradeRecord = + { + 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) + CostCash = reader.GetDecimal(6) + IsSynthetic = reader.GetBoolean(7) + ExecutedAt = reader.GetFieldValue(8) + } + + let insertStockTrade connection transaction (trade: StockTradeRecord) = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO stock_trades + (id, fund_id, instrument_code, stock_name, quantity, price, cost_cash, is_synthetic, executed_at) + VALUES + (@id, @fund_id, @instrument_code, @stock_name, @quantity, @price, @cost_cash, @is_synthetic, @executed_at) + """ + + addParameter command "id" NpgsqlDbType.Uuid (box trade.Id) |> ignore + addParameter command "fund_id" NpgsqlDbType.Uuid (box trade.FundId) |> ignore + addParameter command "instrument_code" NpgsqlDbType.Text (box trade.InstrumentCode) |> ignore + + let nameParameter = + match trade.StockName with + | Some name -> box name + | None -> box DBNull.Value + + addParameter command "stock_name" NpgsqlDbType.Text nameParameter |> ignore + addParameter command "quantity" NpgsqlDbType.Numeric (box trade.Quantity) |> ignore + addParameter command "price" NpgsqlDbType.Numeric (box trade.Price) |> ignore + addParameter command "cost_cash" NpgsqlDbType.Numeric (box trade.CostCash) |> ignore + addParameter command "is_synthetic" NpgsqlDbType.Boolean (box trade.IsSynthetic) |> ignore + addParameter command "executed_at" NpgsqlDbType.TimestampTz (box trade.ExecutedAt) |> ignore + command.ExecuteNonQuery() |> ignore + + let insertStockTradeIdempotency connection transaction key requestHash tradeId fundId = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO stock_trade_idempotencies (idempotency_key, request_hash, trade_id, fund_id) + VALUES (@idempotency_key, @request_hash, @trade_id, @fund_id) + """ + + addParameter command "idempotency_key" NpgsqlDbType.Text (box key) |> ignore + addParameter command "request_hash" NpgsqlDbType.Text (box requestHash) |> ignore + addParameter command "trade_id" NpgsqlDbType.Uuid (box tradeId) |> ignore + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + command.ExecuteNonQuery() |> ignore + + let findStockTradeIdempotency connection transaction key = + use command = + commandWithTransaction + connection + transaction + """ + SELECT request_hash, fund_id, trade_id + FROM stock_trade_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 findStockTrade connection transaction tradeId = + use command = + commandWithTransaction + connection + transaction + """ + SELECT id, fund_id, instrument_code, stock_name, quantity, price, cost_cash, is_synthetic, executed_at + FROM stock_trades + WHERE id = @id + """ + + addParameter command "id" NpgsqlDbType.Uuid (box tradeId) |> ignore + + use reader = command.ExecuteReader() + + if reader.Read() then + Some(stockTradeRecordFromReader reader) + else + None + + let stockTradeRequestHash (fundId: Guid) (command: StockTradeCommand) = + 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-trade" + encoded (fundId.ToString("D")) + encoded command.InstrumentCode + encoded name + encoded (command.Quantity.ToString("G29", invariant)) + encoded (command.Price.ToString("G29", invariant)) + ] + + Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload))) + let sipPlanRecordFromReader (reader: DbDataReader) : SipPlanRecord = { Id = reader.GetGuid(0) @@ -4020,6 +4205,172 @@ type FundRepository(connectionString: string) = raise error + member _.CreateStockTrade(idempotencyKey: string, fundId: Guid, command: StockTradeCommand, ?executedAtOverride: DateTimeOffset) : StockTradeWriteResult = + if String.IsNullOrWhiteSpace idempotencyKey then + StockTradeWriteResult.StockTradeInvalid "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 + StockTradeWriteResult.StockTradeInvalid "stock code must contain exactly six digits" + elif command.Quantity <= 0m then + StockTradeWriteResult.StockTradeInvalid "quantity must be positive" + elif command.Price <= 0m then + StockTradeWriteResult.StockTradeInvalid "price must be positive" + else + let normalized = { command with InstrumentCode = code } + let fingerprint = stockTradeRequestHash 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 findStockTradeIdempotency connection (Some transaction) idempotencyKey with + | Some(existingHash, existingFundId, tradeId) + when existingHash = fingerprint && existingFundId = fundId -> + match findStockTrade connection (Some transaction) tradeId with + | Some trade -> + transaction.Commit() + StockTradeWriteResult.StockTradeReplayed trade + | None -> + transaction.Rollback() + StockTradeWriteResult.StockTradeInvalid "idempotency record references a missing trade" + | Some _ -> + transaction.Rollback() + StockTradeWriteResult.StockTradeIdempotencyConflict + | None -> + match lockFundForOrder connection (Some transaction) fundId with + | None -> + transaction.Rollback() + StockTradeWriteResult.StockTradeFundNotFound + | Some isSynthetic -> + let executedAt = defaultArg executedAtOverride DateTimeOffset.UtcNow + let costCash = Decimal.Round(normalized.Quantity * normalized.Price, 2, MidpointRounding.AwayFromZero) + + let trade: StockTradeRecord = + { + Id = Guid.NewGuid() + FundId = fundId + InstrumentCode = normalized.InstrumentCode + StockName = normalized.StockName + Quantity = normalized.Quantity + Price = normalized.Price + CostCash = costCash + IsSynthetic = isSynthetic + ExecutedAt = executedAt + } + + insertStockTrade connection (Some transaction) trade + insertStockTradeIdempotency connection (Some transaction) idempotencyKey fingerprint trade.Id fundId + + use positionCommand = + commandWithTransaction + connection + (Some transaction) + """ + INSERT INTO stock_positions + (fund_id, instrument_code, stock_name, quantity, cost_cash, last_traded_at) + VALUES (@fund_id, @code, @name, @quantity, @cost_cash, @last_traded_at) + ON CONFLICT (fund_id, instrument_code) DO UPDATE + SET quantity = stock_positions.quantity + EXCLUDED.quantity, + cost_cash = stock_positions.cost_cash + EXCLUDED.cost_cash, + stock_name = COALESCE(EXCLUDED.stock_name, stock_positions.stock_name), + last_traded_at = EXCLUDED.last_traded_at + """ + + addParameter positionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter positionCommand "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore + + let nameParameter = + match normalized.StockName with + | Some name -> box name + | None -> box DBNull.Value + + addParameter positionCommand "name" NpgsqlDbType.Text nameParameter |> ignore + addParameter positionCommand "quantity" NpgsqlDbType.Numeric (box normalized.Quantity) |> ignore + addParameter positionCommand "cost_cash" NpgsqlDbType.Numeric (box costCash) |> ignore + addParameter positionCommand "last_traded_at" NpgsqlDbType.TimestampTz (box executedAt) |> ignore + positionCommand.ExecuteNonQuery() |> ignore + + let persisted = { trade with ExecutedAt = executedAt } + transaction.Commit() + StockTradeWriteResult.StockTradeCreated persisted + with error -> + try + transaction.Rollback() + with _ -> + () + + raise error + + member _.GetStockTrades(fundId: Guid) : StockTradeRecord list = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + use command = + commandWithTransaction + connection + None + """ + SELECT id, fund_id, instrument_code, stock_name, quantity, price, cost_cash, is_synthetic, executed_at + FROM stock_trades + 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(stockTradeRecordFromReader reader) + + records |> Seq.toList + + member _.GetStockPositions(fundId: Guid) : StockPositionRecord list = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + use command = + commandWithTransaction + connection + None + """ + SELECT instrument_code, stock_name, quantity, cost_cash, last_traded_at + FROM stock_positions + WHERE fund_id = @fund_id + ORDER BY instrument_code + """ + + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + + use reader = command.ExecuteReader() + let records = ResizeArray() + + while reader.Read() do + records.Add( + { + FundId = fundId + InstrumentCode = reader.GetString(0) + StockName = if reader.IsDBNull(1) then None else Some(reader.GetString(1)) + Quantity = reader.GetDecimal(2) + CostCash = reader.GetDecimal(3) + LastTradedAt = reader.GetFieldValue(4) + } + ) + + records |> Seq.toList + member _.GetCapitalDeposits(fundId: Guid) = use connection = new NpgsqlConnection(connectionString) connection.Open() -- cgit v1.2.3