diff options
Diffstat (limited to 'src/FundLab.Api/Persistence.fs')
| -rw-r--r-- | src/FundLab.Api/Persistence.fs | 350 |
1 files changed, 350 insertions, 0 deletions
diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index ab3fe0a..5000161 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -327,6 +327,44 @@ type StockTradeWriteResult = | StockTradeInvalid of string | StockTradeFundNotFound +type BondTradeCommand = + { + InstrumentCode: string + BondName: string option + Quantity: decimal + Price: decimal + } + +type BondTradeRecord = + { + Id: Guid + FundId: Guid + InstrumentCode: string + BondName: string option + Quantity: decimal + Price: decimal + CostCash: decimal + IsSynthetic: bool + ExecutedAt: DateTimeOffset + } + +type BondPositionRecord = + { + FundId: Guid + InstrumentCode: string + BondName: string option + Quantity: decimal + CostCash: decimal + LastTradedAt: DateTimeOffset + } + +type BondTradeWriteResult = + | BondTradeCreated of BondTradeRecord + | BondTradeReplayed of BondTradeRecord + | BondTradeIdempotencyConflict + | BondTradeInvalid of string + | BondTradeFundNotFound + type SipPlanRecord = { Id: Guid @@ -897,6 +935,36 @@ type FundRepository(connectionString: string) = PRIMARY KEY (fund_id, instrument_code) ); + CREATE TABLE IF NOT EXISTS bond_trades ( + id uuid PRIMARY KEY, + fund_id uuid NOT NULL REFERENCES funds(id), + instrument_code text NOT NULL, + bond_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 bond_trade_idempotencies ( + idempotency_key text PRIMARY KEY, + request_hash text NOT NULL, + trade_id uuid NOT NULL REFERENCES bond_trades(id), + fund_id uuid NOT NULL REFERENCES funds(id), + created_at timestamptz NOT NULL DEFAULT now() + ); + + CREATE TABLE IF NOT EXISTS bond_positions ( + fund_id uuid NOT NULL REFERENCES funds(id), + instrument_code text NOT NULL, + bond_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, @@ -1872,6 +1940,123 @@ type FundRepository(connectionString: string) = Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload))) + let bondTradeRecordFromReader (reader: DbDataReader) : BondTradeRecord = + { + Id = reader.GetGuid(0) + FundId = reader.GetGuid(1) + InstrumentCode = reader.GetString(2) + BondName = 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<DateTimeOffset>(8) + } + + let insertBondTrade connection transaction (trade: BondTradeRecord) = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO bond_trades + (id, fund_id, instrument_code, bond_name, quantity, price, cost_cash, is_synthetic, executed_at) + VALUES + (@id, @fund_id, @instrument_code, @bond_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.BondName with + | Some name -> box name + | None -> box DBNull.Value + + addParameter command "bond_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 insertBondTradeIdempotency connection transaction key requestHash tradeId fundId = + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO bond_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 findBondTradeIdempotency connection transaction key = + use command = + commandWithTransaction + connection + transaction + """ + SELECT request_hash, fund_id, trade_id + FROM bond_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 findBondTrade connection transaction tradeId = + use command = + commandWithTransaction + connection + transaction + """ + SELECT id, fund_id, instrument_code, bond_name, quantity, price, cost_cash, is_synthetic, executed_at + FROM bond_trades + WHERE id = @id + """ + + addParameter command "id" NpgsqlDbType.Uuid (box tradeId) |> ignore + + use reader = command.ExecuteReader() + + if reader.Read() then + Some(bondTradeRecordFromReader reader) + else + None + + let bondTradeRequestHash (fundId: Guid) (command: BondTradeCommand) = + let invariant = CultureInfo.InvariantCulture + let encoded (value: string) = sprintf "%d:%s" value.Length value + let name = command.BondName |> Option.defaultValue "" + + let payload = + String.concat + "|" + [ + "bond-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) @@ -4371,6 +4556,171 @@ type FundRepository(connectionString: string) = records |> Seq.toList + member _.CreateBondTrade(idempotencyKey: string, fundId: Guid, command: BondTradeCommand, ?executedAtOverride: DateTimeOffset) : BondTradeWriteResult = + if String.IsNullOrWhiteSpace idempotencyKey then + BondTradeWriteResult.BondTradeInvalid "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 + BondTradeWriteResult.BondTradeInvalid "bond code must contain exactly six digits" + elif command.Quantity <= 0m then + BondTradeWriteResult.BondTradeInvalid "quantity must be positive" + elif command.Price <= 0m then + BondTradeWriteResult.BondTradeInvalid "price must be positive" + else + let normalized = { command with InstrumentCode = code } + let fingerprint = bondTradeRequestHash 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 findBondTradeIdempotency connection (Some transaction) idempotencyKey with + | Some(existingHash, existingFundId, tradeId) + when existingHash = fingerprint && existingFundId = fundId -> + match findBondTrade connection (Some transaction) tradeId with + | Some trade -> + transaction.Commit() + BondTradeWriteResult.BondTradeReplayed trade + | None -> + transaction.Rollback() + BondTradeWriteResult.BondTradeInvalid "idempotency record references a missing trade" + | Some _ -> + transaction.Rollback() + BondTradeWriteResult.BondTradeIdempotencyConflict + | None -> + match lockFundForOrder connection (Some transaction) fundId with + | None -> + transaction.Rollback() + BondTradeWriteResult.BondTradeFundNotFound + | Some isSynthetic -> + let executedAt = defaultArg executedAtOverride DateTimeOffset.UtcNow + let costCash = Decimal.Round(normalized.Quantity * normalized.Price, 2, MidpointRounding.AwayFromZero) + + let trade: BondTradeRecord = + { + Id = Guid.NewGuid() + FundId = fundId + InstrumentCode = normalized.InstrumentCode + BondName = normalized.BondName + Quantity = normalized.Quantity + Price = normalized.Price + CostCash = costCash + IsSynthetic = isSynthetic + ExecutedAt = executedAt + } + + insertBondTrade connection (Some transaction) trade + insertBondTradeIdempotency connection (Some transaction) idempotencyKey fingerprint trade.Id fundId + + use positionCommand = + commandWithTransaction + connection + (Some transaction) + """ + INSERT INTO bond_positions + (fund_id, instrument_code, bond_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 = bond_positions.quantity + EXCLUDED.quantity, + cost_cash = bond_positions.cost_cash + EXCLUDED.cost_cash, + bond_name = COALESCE(EXCLUDED.bond_name, bond_positions.bond_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.BondName 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 + + transaction.Commit() + BondTradeWriteResult.BondTradeCreated trade + with error -> + try + transaction.Rollback() + with _ -> + () + + raise error + + member _.GetBondTrades(fundId: Guid) : BondTradeRecord list = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + use command = + commandWithTransaction + connection + None + """ + SELECT id, fund_id, instrument_code, bond_name, quantity, price, cost_cash, is_synthetic, executed_at + FROM bond_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<BondTradeRecord>() + + while reader.Read() do + records.Add(bondTradeRecordFromReader reader) + + records |> Seq.toList + + member _.GetBondPositions(fundId: Guid) : BondPositionRecord list = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + use command = + commandWithTransaction + connection + None + """ + SELECT instrument_code, bond_name, quantity, cost_cash, last_traded_at + FROM bond_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<BondPositionRecord>() + + while reader.Read() do + records.Add( + { + FundId = fundId + InstrumentCode = reader.GetString(0) + BondName = if reader.IsDBNull(1) then None else Some(reader.GetString(1)) + Quantity = reader.GetDecimal(2) + CostCash = reader.GetDecimal(3) + LastTradedAt = reader.GetFieldValue<DateTimeOffset>(4) + } + ) + + records |> Seq.toList + member _.GetCapitalDeposits(fundId: Guid) = use connection = new NpgsqlConnection(connectionString) connection.Open() |