diff options
| -rw-r--r-- | src/FundLab.Api/Persistence.fs | 656 | ||||
| -rw-r--r-- | tests/FundLab.Api.Tests/OrderTests.fs | 599 |
2 files changed, 1250 insertions, 5 deletions
diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index 6c60bdd..de8061b 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -9,6 +9,105 @@ open System.Text open FundLab.Domain open Npgsql open NpgsqlTypes +open System.Text.Json + +module ConfirmationPolicy = + let cutoffTimeOfDay = TimeSpan(15, 0, 0) + let unitsMaximum = 99999999999999999999.99999999m + let amountMaximum = 999999999999999999.99m + + let private shanghaiZone = + lazy ( + try + TimeZoneInfo.FindSystemTimeZoneById("Asia/Shanghai") + with _ -> + TimeZoneInfo.CreateCustomTimeZone( + "Asia/Shanghai (UTC+8)", + TimeSpan(8, 0, 0), + "Asia/Shanghai (UTC+8)", + "Asia/Shanghai (UTC+8)" + ) + ) + + let shanghaiDate (moment: DateTimeOffset) : DateOnly = + DateOnly.FromDateTime(TimeZoneInfo.ConvertTime(moment, shanghaiZone.Value).Date) + + let private isWeekend (date: DateOnly) = + date.DayOfWeek = DayOfWeek.Saturday || date.DayOfWeek = DayOfWeek.Sunday + + let rec private rollToWeekday (date: DateOnly) = + if isWeekend date then rollToWeekday (date.AddDays 1) else date + + let isModeledTradingDay (date: DateOnly) : bool = not (isWeekend date) + + let tradeDateFor (submittedAt: DateTimeOffset) : DateOnly = + let local = TimeZoneInfo.ConvertTime(submittedAt, shanghaiZone.Value) + let date = DateOnly.FromDateTime(local.Date) + let candidate = if local.TimeOfDay >= cutoffTimeOfDay then date.AddDays 1 else date + rollToWeekday candidate + + type NavQuote = + { NavDate: DateOnly + Nav: decimal + CollectedAt: DateTimeOffset + PublishedAt: DateTimeOffset option } + + type ConfirmationComputation = + { + Units: decimal + InvestedCash: decimal + ResidualCash: decimal + } + + let navDeferralReason + (quote: NavQuote) + (tradeDate: DateOnly) + (today: DateOnly) + (now: DateTimeOffset) + : string option = + let tradeDateText = tradeDate.ToString("yyyy-MM-dd") + + if quote.Nav <= 0m then + Some(sprintf "nav for trade date %s is not positive" tradeDateText) + elif quote.NavDate > today then + Some(sprintf "nav for trade date %s is dated in the future" tradeDateText) + elif quote.CollectedAt > now then + Some(sprintf "nav for trade date %s is not yet collected" tradeDateText) + elif quote.PublishedAt |> Option.exists (fun publishedAt -> publishedAt > now) then + Some(sprintf "nav for trade date %s is not yet published" tradeDateText) + elif quote.NavDate <> tradeDate then + Some(sprintf "nav for trade date %s is not available yet" tradeDateText) + else + None + + let compute (amount: decimal) (nav: decimal) : Result<ConfirmationComputation, string> = + if amount <= 0m then + Error "amount must be positive" + elif amount > amountMaximum then + Error "amount exceeds supported precision" + elif nav <= 0m then + Error "unit nav must be positive" + elif nav < 0.00000001m then + Error "unit nav is below database precision" + else + let rawUnits = amount / nav + + if rawUnits > unitsMaximum then + Error "unit amount exceeds database precision" + else + let units = Decimal.Truncate(rawUnits * 100000000m) / 100000000m + + if units <= 0m then + Error "amount converts to zero units at this unit nav" + else + let invested = Decimal.Truncate(units * nav * 100m) / 100m + + Ok + { + Units = units + InvestedCash = invested + ResidualCash = amount - invested + } [<CLIMutable>] type FundCreateCommand = @@ -46,6 +145,18 @@ type SubscriptionOrderCommand = FeeAmount: decimal } +type ConfirmedQuoteEvidence = + { + Nav: decimal + NavDate: DateOnly + Source: string + Revision: string + CollectedAt: DateTimeOffset + PublishedAt: DateTimeOffset option + PayloadHash: string + FirstSeenAt: DateTimeOffset + } + type SubscriptionOrderRecord = { Id: Guid @@ -57,6 +168,14 @@ type SubscriptionOrderRecord = Status: string SubmittedAt: DateTimeOffset IsSynthetic: bool + TradeDate: DateOnly + ConfirmIdempotencyKey: string option + PendingReason: string option + ConfirmedAt: DateTimeOffset option + ConfirmedQuote: ConfirmedQuoteEvidence option + ConfirmedUnits: decimal option + ConfirmedInvestedCash: decimal option + ConfirmedResidualCash: decimal option } type SubscriptionOrderWriteResult = @@ -68,6 +187,28 @@ type SubscriptionOrderWriteResult = | OrderInstrumentNotFound | OrderInsufficientFunds +type SubscriptionConfirmResult = + | OrderConfirmed of SubscriptionOrderRecord + | ConfirmReplayed of SubscriptionOrderRecord + | ConfirmPendingNav of SubscriptionOrderRecord + | ConfirmIdempotencyConflict + | ConfirmAlreadyConfirmed + | ConfirmOrderNotFound + | ConfirmInvalidStatus + | ConfirmInvalid of string + +type FundPositionRecord = + { + FundId: Guid + InstrumentCode: string + Units: decimal + CostCash: decimal + LastConfirmedAt: DateTimeOffset + ValuationNav: decimal option + ValuationNavDate: DateOnly option + ValuationCollectedAt: DateTimeOffset option + } + type FundRepository(connectionString: string) = let cashMaximum = 999999999999999999.99m let unitNavMaximum = 99999999999999999999.99999999m @@ -148,6 +289,53 @@ type FundRepository(connectionString: string) = fund_id uuid NOT NULL REFERENCES funds(id), created_at timestamptz NOT NULL DEFAULT now() ); + + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirm_idempotency_key text NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS pending_reason text NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_at timestamptz NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_nav numeric(28, 8) NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_nav_date date NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_nav_source text NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_nav_revision text NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_nav_collected_at timestamptz NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_nav_published_at timestamptz NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_units numeric(28, 8) NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_invested_cash numeric(20, 2) NULL; + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_residual_cash numeric(20, 2) NULL; + + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_nav_payload_hash text NULL; + + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS confirmed_nav_first_seen_at timestamptz NULL; + + ALTER TABLE subscription_orders ADD COLUMN IF NOT EXISTS trade_date date NULL; + + UPDATE subscription_orders SET trade_date = (submitted_at AT TIME ZONE 'Asia/Shanghai')::date WHERE trade_date IS NULL; + + CREATE TABLE IF NOT EXISTS fund_positions ( + 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), + cost_cash numeric(20, 2) NOT NULL CHECK (cost_cash >= 0), + first_confirmed_at timestamptz NOT NULL, + last_confirmed_at timestamptz NOT NULL, + PRIMARY KEY (fund_id, instrument_code) + ); + + CREATE TABLE IF NOT EXISTS subscription_order_events ( + id bigserial PRIMARY KEY, + order_id uuid NOT NULL REFERENCES subscription_orders(id), + event_type text NOT NULL, + detail jsonb NOT NULL, + created_at timestamptz NOT NULL DEFAULT now() + ); + + CREATE TABLE IF NOT EXISTS subscription_confirm_idempotencies ( + idempotency_key text PRIMARY KEY, + request_hash text NOT NULL, + order_id uuid NOT NULL REFERENCES subscription_orders(id), + fund_id uuid NOT NULL REFERENCES funds(id), + created_at timestamptz NOT NULL DEFAULT now() + ); """ let statusText status = @@ -311,7 +499,41 @@ type FundRepository(connectionString: string) = let orderStatusText = "submitted" + let readStringOption (reader: DbDataReader) ordinal = + if reader.IsDBNull ordinal then None else Some(reader.GetString ordinal) + + let readDecimalOption (reader: DbDataReader) ordinal = + if reader.IsDBNull ordinal then None else Some(reader.GetDecimal ordinal) + let orderRecordFromReader (reader: DbDataReader) : SubscriptionOrderRecord = + let confirmedQuote = + if reader.IsDBNull(13) then + None + else + Some + { + Nav = reader.GetDecimal(13) + NavDate = reader.GetFieldValue<DateOnly>(14) + Source = reader.GetString(15) + Revision = reader.GetString(16) + CollectedAt = reader.GetFieldValue<DateTimeOffset>(17) + PublishedAt = + if reader.IsDBNull(18) then + None + else + Some(reader.GetFieldValue<DateTimeOffset>(18)) + PayloadHash = + if reader.IsDBNull(19) then + "" + else + reader.GetString(19) + FirstSeenAt = + if reader.IsDBNull(20) then + DateTimeOffset.UnixEpoch + else + reader.GetFieldValue<DateTimeOffset>(20) + } + { Id = reader.GetGuid(0) FundId = reader.GetGuid(1) @@ -322,6 +544,18 @@ type FundRepository(connectionString: string) = Status = reader.GetString(6) IsSynthetic = reader.GetBoolean(7) SubmittedAt = reader.GetFieldValue<DateTimeOffset>(8) + TradeDate = reader.GetFieldValue<DateOnly>(9) + ConfirmIdempotencyKey = readStringOption reader 10 + PendingReason = readStringOption reader 11 + ConfirmedAt = + if reader.IsDBNull(12) then + None + else + Some(reader.GetFieldValue<DateTimeOffset>(12)) + ConfirmedUnits = readDecimalOption reader 21 + ConfirmedQuote = confirmedQuote + ConfirmedInvestedCash = readDecimalOption reader 22 + ConfirmedResidualCash = readDecimalOption reader 23 } let findOrder connection transaction orderId = @@ -331,7 +565,13 @@ type FundRepository(connectionString: string) = transaction """ SELECT id, fund_id, fund_code, amount, fee_amount, - reserved_total, status, is_synthetic, submitted_at + reserved_total, status, is_synthetic, submitted_at, + trade_date, + confirm_idempotency_key, pending_reason, confirmed_at, + confirmed_nav, confirmed_nav_date, confirmed_nav_source, + confirmed_nav_revision, confirmed_nav_collected_at, confirmed_nav_published_at, + confirmed_nav_payload_hash, confirmed_nav_first_seen_at, + confirmed_units, confirmed_invested_cash, confirmed_residual_cash FROM subscription_orders WHERE id = @order_id """ @@ -341,6 +581,21 @@ type FundRepository(connectionString: string) = use reader = command.ExecuteReader() if reader.Read() then Some(orderRecordFromReader reader) else None + let findConfirmIdempotency connection transaction key = + use command = + commandWithTransaction + connection + transaction + "SELECT request_hash, fund_id, order_id FROM subscription_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 findOrderIdempotency connection transaction key = use command = commandWithTransaction @@ -388,10 +643,10 @@ type FundRepository(connectionString: string) = """ INSERT INTO subscription_orders (id, fund_id, fund_code, amount, fee_amount, - reserved_total, status, is_synthetic) + reserved_total, status, is_synthetic, trade_date) VALUES (@id, @fund_id, @fund_code, @amount, @fee_amount, - @reserved_total, @status, @is_synthetic) + @reserved_total, @status, @is_synthetic, @trade_date) RETURNING submitted_at """ @@ -403,6 +658,7 @@ type FundRepository(connectionString: string) = addParameter command "reserved_total" NpgsqlDbType.Numeric (box order.ReservedTotal) |> 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 @@ -825,6 +1081,7 @@ type FundRepository(connectionString: string) = transaction.Rollback() OrderInsufficientFunds else + let submittedAt = DateTimeOffset.UtcNow let order: SubscriptionOrderRecord = { Id = Guid.NewGuid() @@ -834,8 +1091,16 @@ type FundRepository(connectionString: string) = FeeAmount = command.FeeAmount ReservedTotal = reservedTotal Status = orderStatusText - SubmittedAt = DateTimeOffset.UnixEpoch + SubmittedAt = submittedAt IsSynthetic = isSynthetic + TradeDate = ConfirmationPolicy.tradeDateFor submittedAt + ConfirmIdempotencyKey = None + PendingReason = None + ConfirmedAt = None + ConfirmedQuote = None + ConfirmedUnits = None + ConfirmedInvestedCash = None + ConfirmedResidualCash = None } let submittedAt = insertSubscriptionOrder connection (Some transaction) order @@ -863,7 +1128,13 @@ type FundRepository(connectionString: string) = None """ SELECT id, fund_id, fund_code, amount, fee_amount, - reserved_total, status, is_synthetic, submitted_at + reserved_total, status, is_synthetic, submitted_at, + trade_date, + confirm_idempotency_key, pending_reason, confirmed_at, + confirmed_nav, confirmed_nav_date, confirmed_nav_source, + confirmed_nav_revision, confirmed_nav_collected_at, confirmed_nav_published_at, + confirmed_nav_payload_hash, confirmed_nav_first_seen_at, + confirmed_units, confirmed_invested_cash, confirmed_residual_cash FROM subscription_orders WHERE fund_id = @fund_id ORDER BY submitted_at DESC, id @@ -878,3 +1149,378 @@ type FundRepository(connectionString: string) = records.Add(orderRecordFromReader reader) records |> Seq.toList + + member _.ConfirmSubscriptionOrder(idempotencyKey: string, fundId: Guid, orderId: Guid) = + if String.IsNullOrWhiteSpace idempotencyKey then + ConfirmInvalid "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 + + let requestHash = + Convert + .ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(sprintf "confirm:%O:%O" fundId orderId))) + .ToLowerInvariant() + + use lockCommand = + commandWithTransaction + connection + (Some transaction) + "SELECT pg_advisory_xact_lock(hashtext(@lock_key))" + + addParameter lockCommand "lock_key" NpgsqlDbType.Text (box (sprintf "confirm-order:%O" orderId)) |> ignore + lockCommand.ExecuteNonQuery() |> ignore + + match findOrder connection (Some transaction) orderId with + | None -> + transaction.Rollback() + ConfirmOrderNotFound + | Some order when order.FundId <> fundId -> + transaction.Rollback() + ConfirmOrderNotFound + | Some order -> + if order.Status = "confirmed" then + match order.ConfirmIdempotencyKey with + | Some storedKey when storedKey = idempotencyKey -> + transaction.Commit() + ConfirmReplayed order + | _ -> + match findConfirmIdempotency connection (Some transaction) idempotencyKey with + | Some(_, _, storedOrderId) when storedOrderId = orderId -> + transaction.Commit() + ConfirmReplayed order + | Some _ -> + transaction.Rollback() + ConfirmIdempotencyConflict + | None -> + transaction.Rollback() + ConfirmAlreadyConfirmed + elif order.Status <> "submitted" && order.Status <> "pending_nav" then + transaction.Rollback() + ConfirmInvalidStatus + else + match findConfirmIdempotency connection (Some transaction) idempotencyKey with + | Some _ -> + transaction.Rollback() + ConfirmIdempotencyConflict + | None -> + let tradeDate = order.TradeDate + let tradeDateText = tradeDate.ToString("yyyy-MM-dd") + + use quoteCommand = + commandWithTransaction + connection + (Some transaction) + """ + SELECT nav, nav_date, source, source_revision, source_collected_at, published_at, + source_payload_hash, first_seen_at + FROM fund_nav_observations + WHERE instrument_code = @code AND nav_date = @trade_date AND nav > 0 + ORDER BY source_collected_at DESC, published_at DESC NULLS LAST, source_revision DESC + LIMIT 1 + """ + + addParameter quoteCommand "code" NpgsqlDbType.Text (box order.FundCode) |> ignore + addParameter quoteCommand "trade_date" NpgsqlDbType.Date (box tradeDate) |> ignore + + use quoteReader = quoteCommand.ExecuteReader() + let quoteFound = quoteReader.Read() + + let selectedQuote = + if quoteFound then + Some + { + Nav = quoteReader.GetDecimal(0) + NavDate = quoteReader.GetFieldValue<DateOnly>(1) + Source = quoteReader.GetString(2) + Revision = quoteReader.GetString(3) + CollectedAt = quoteReader.GetFieldValue<DateTimeOffset>(4) + PublishedAt = + if quoteReader.IsDBNull(5) then + None + else + Some(quoteReader.GetFieldValue<DateTimeOffset>(5)) + PayloadHash = quoteReader.GetString(6) + FirstSeenAt = quoteReader.GetFieldValue<DateTimeOffset>(7) + } + else + None + + quoteReader.Close() + + let deferralReason = + match selectedQuote with + | None -> Some(sprintf "nav for trade date %s is not available yet" tradeDateText) + | Some quote -> + ConfirmationPolicy.navDeferralReason + { + Nav = quote.Nav + NavDate = quote.NavDate + CollectedAt = quote.CollectedAt + PublishedAt = quote.PublishedAt + } + tradeDate + today + confirmedAt + + let markPending reason = + use pendingCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE subscription_orders + SET status = 'pending_nav', + pending_reason = @reason, + confirm_idempotency_key = NULL, + confirmed_at = NULL, + confirmed_nav = NULL, + confirmed_nav_date = NULL, + confirmed_nav_source = NULL, + confirmed_nav_revision = NULL, + confirmed_nav_collected_at = NULL, + confirmed_nav_published_at = NULL, + confirmed_nav_payload_hash = NULL, + confirmed_nav_first_seen_at = NULL, + confirmed_units = NULL, + confirmed_invested_cash = NULL, + confirmed_residual_cash = 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 + + let readPendingAfterCommit () = + match findOrder connection None orderId with + | Some pendingOrder -> ConfirmPendingNav pendingOrder + | None -> failwith "pending order disappeared after confirmation deferral" + + match deferralReason with + | Some reason -> + markPending reason + transaction.Commit() + readPendingAfterCommit () + | None -> + let quote = selectedQuote |> Option.get + let source = quote.Source + let revision = quote.Revision + + match ConfirmationPolicy.compute order.Amount quote.Nav with + | Error message -> + markPending message + transaction.Commit() + readPendingAfterCommit () + | Ok computation -> + use cashCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE funds + SET available_cash = available_cash + @residual, + reserved_cash = reserved_cash - @reserved_total + WHERE id = @fund_id AND reserved_cash >= @reserved_total + """ + + addParameter cashCommand "residual" NpgsqlDbType.Numeric (box computation.ResidualCash) |> ignore + addParameter cashCommand "reserved_total" NpgsqlDbType.Numeric (box order.ReservedTotal) |> ignore + addParameter cashCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + + if cashCommand.ExecuteNonQuery() = 0 then + failwith "insufficient reserved cash for order confirmation" + + let publishedParameter = + match quote.PublishedAt with + | Some published -> box published + | None -> box DBNull.Value + + use confirmCommand = + commandWithTransaction + connection + (Some transaction) + """ + UPDATE subscription_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_nav_source = @source, + confirmed_nav_revision = @revision, + confirmed_nav_collected_at = @collected_at, + confirmed_nav_published_at = @published_at, + confirmed_nav_payload_hash = @payload_hash, + confirmed_nav_first_seen_at = @first_seen_at, + confirmed_units = @units, + confirmed_invested_cash = @invested, + confirmed_residual_cash = @residual + 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 quote.Nav) |> ignore + addParameter confirmCommand "nav_date" NpgsqlDbType.Date (box quote.NavDate) |> ignore + addParameter confirmCommand "source" NpgsqlDbType.Text (box source) |> ignore + addParameter confirmCommand "revision" NpgsqlDbType.Text (box revision) |> ignore + addParameter confirmCommand "collected_at" NpgsqlDbType.TimestampTz (box quote.CollectedAt) |> ignore + addParameter confirmCommand "published_at" NpgsqlDbType.TimestampTz publishedParameter |> ignore + addParameter confirmCommand "payload_hash" NpgsqlDbType.Text (box quote.PayloadHash) |> ignore + addParameter confirmCommand "first_seen_at" NpgsqlDbType.TimestampTz (box quote.FirstSeenAt) |> ignore + addParameter confirmCommand "units" NpgsqlDbType.Numeric (box computation.Units) |> ignore + addParameter confirmCommand "invested" NpgsqlDbType.Numeric (box computation.InvestedCash) |> ignore + addParameter confirmCommand "residual" NpgsqlDbType.Numeric (box computation.ResidualCash) |> ignore + addParameter confirmCommand "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + confirmCommand.ExecuteNonQuery() |> ignore + + use positionCommand = + commandWithTransaction + connection + (Some transaction) + """ + INSERT INTO fund_positions + (fund_id, instrument_code, units, cost_cash, first_confirmed_at, last_confirmed_at) + VALUES + (@fund_id, @code, @units, @invested, @confirmed_at, @confirmed_at) + ON CONFLICT (fund_id, instrument_code) DO UPDATE + SET units = fund_positions.units + EXCLUDED.units, + cost_cash = fund_positions.cost_cash + EXCLUDED.cost_cash, + last_confirmed_at = EXCLUDED.last_confirmed_at + """ + + addParameter positionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + addParameter positionCommand "code" NpgsqlDbType.Text (box order.FundCode) |> ignore + addParameter positionCommand "units" NpgsqlDbType.Numeric (box computation.Units) |> ignore + addParameter positionCommand "invested" NpgsqlDbType.Numeric (box computation.InvestedCash) |> ignore + addParameter positionCommand "confirmed_at" NpgsqlDbType.TimestampTz (box confirmedAt) |> ignore + positionCommand.ExecuteNonQuery() |> ignore + + let publishedJson = + match quote.PublishedAt with + | Some published -> sprintf "\"%O\"" published + | None -> "null" + + let detail = + sprintf + "{\"fund_id\":\"%O\",\"order_id\":\"%O\",\"units\":\"%M\",\"invested_cash\":\"%M\",\"residual_cash\":\"%M\",\"quote\":{\"nav_date\":\"%s\",\"nav\":\"%M\",\"source\":%s,\"source_revision\":%s,\"source_collected_at\":\"%O\",\"published_at\":%s,\"source_payload_hash\":%s,\"first_seen_at\":\"%O\"}}" + fundId + orderId + computation.Units + computation.InvestedCash + computation.ResidualCash + (quote.NavDate.ToString("yyyy-MM-dd")) + quote.Nav + (JsonSerializer.Serialize source) + (JsonSerializer.Serialize revision) + quote.CollectedAt + publishedJson + (JsonSerializer.Serialize quote.PayloadHash) + quote.FirstSeenAt + + use eventCommand = + commandWithTransaction + connection + (Some transaction) + """ + INSERT INTO subscription_order_events (order_id, event_type, detail) + VALUES (@order_id, 'confirmed', @detail) + """ + + addParameter eventCommand "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + addParameter eventCommand "detail" NpgsqlDbType.Jsonb (box detail) |> ignore + eventCommand.ExecuteNonQuery() |> ignore + + use idempotencyCommand = + commandWithTransaction + connection + (Some transaction) + """ + INSERT INTO subscription_confirm_idempotencies + (idempotency_key, request_hash, order_id, fund_id) + VALUES + (@idempotency_key, @request_hash, @order_id, @fund_id) + """ + + addParameter idempotencyCommand "idempotency_key" NpgsqlDbType.Text (box idempotencyKey) |> ignore + addParameter idempotencyCommand "request_hash" NpgsqlDbType.Text (box requestHash) |> ignore + addParameter idempotencyCommand "order_id" NpgsqlDbType.Uuid (box orderId) |> ignore + addParameter idempotencyCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + idempotencyCommand.ExecuteNonQuery() |> ignore + + transaction.Commit() + + match findOrder connection None orderId with + | Some confirmedOrder -> OrderConfirmed confirmedOrder + | None -> failwith "confirmed order disappeared after commit" + with error -> + try + transaction.Rollback() + with _ -> + () + + raise error + + member _.GetFundPositions(fundId: Guid) = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + use command = + commandWithTransaction + connection + None + """ + SELECT p.instrument_code, p.units, p.cost_cash, p.last_confirmed_at, + q.nav, q.nav_date, q.source_collected_at + FROM fund_positions p + LEFT JOIN LATERAL ( + SELECT nav, nav_date, source_collected_at + FROM fund_nav_observations + WHERE instrument_code = p.instrument_code + AND nav_date <= (now() AT TIME ZONE 'Asia/Shanghai')::date + AND nav > 0 + AND source_collected_at <= now() + AND (published_at IS NULL OR published_at <= now()) + ORDER BY nav_date DESC, source_collected_at DESC + LIMIT 1 + ) q ON true + WHERE p.fund_id = @fund_id + ORDER BY p.instrument_code + """ + + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + + use reader = command.ExecuteReader() + let records = ResizeArray<FundPositionRecord>() + + while reader.Read() do + let valuationNav = + if reader.IsDBNull(4) then None else Some(reader.GetDecimal(4)) + + let valuationNavDate = + if reader.IsDBNull(5) then None else Some(reader.GetFieldValue<DateOnly>(5)) + + let valuationCollectedAt = + if reader.IsDBNull(6) then None else Some(reader.GetFieldValue<DateTimeOffset>(6)) + + records.Add( + { + FundId = fundId + InstrumentCode = reader.GetString(0) + Units = reader.GetDecimal(1) + CostCash = reader.GetDecimal(2) + LastConfirmedAt = reader.GetFieldValue<DateTimeOffset>(3) + ValuationNav = valuationNav + ValuationNavDate = valuationNavDate + ValuationCollectedAt = valuationCollectedAt + } + ) + + records |> Seq.toList diff --git a/tests/FundLab.Api.Tests/OrderTests.fs b/tests/FundLab.Api.Tests/OrderTests.fs index d1f57df..d975462 100644 --- a/tests/FundLab.Api.Tests/OrderTests.fs +++ b/tests/FundLab.Api.Tests/OrderTests.fs @@ -473,3 +473,602 @@ type OrderTests(fixture: PostgresFixture) = Assert.Equal(0L, negativeCount) Assert.Equal(1L, zeroCount) + +type ConfirmationPolicyTests() = + let shanghai (hour: int) (minute: int) (second: int) (day: int) = + DateTimeOffset(2026, 9, day, hour, minute, second, TimeSpan.FromHours(8.0)) + + let navQuote navDate nav collectedAt publishedAt : ConfirmationPolicy.NavQuote = + { NavDate = navDate + Nav = nav + CollectedAt = collectedAt + PublishedAt = publishedAt } + + [<Fact>] + member _.``trade date is the shanghai date before the 15:00 cutoff``() = + let submitted = shanghai 14 59 59 21 + Assert.Equal(DateOnly(2026, 9, 21), ConfirmationPolicy.tradeDateFor submitted) + + [<Fact>] + member _.``trade date rolls to the next day at the 15:00 cutoff``() = + let submitted = shanghai 15 0 0 21 + Assert.Equal(DateOnly(2026, 9, 22), ConfirmationPolicy.tradeDateFor submitted) + + [<Fact>] + member _.``trade date is derived from shanghai wall clock regardless of offset``() = + let utcBeforeCutoff = DateTimeOffset(2026, 9, 21, 6, 59, 59, TimeSpan.Zero) + let utcAtCutoff = DateTimeOffset(2026, 9, 21, 7, 0, 0, TimeSpan.Zero) + Assert.Equal(DateOnly(2026, 9, 21), ConfirmationPolicy.tradeDateFor utcBeforeCutoff) + Assert.Equal(DateOnly(2026, 9, 22), ConfirmationPolicy.tradeDateFor utcAtCutoff) + + [<Fact>] + member _.``friday cutoff rolls the trade date to monday``() = + let submitted = shanghai 15 0 0 25 + Assert.Equal(DateOnly(2026, 9, 28), ConfirmationPolicy.tradeDateFor submitted) + + [<Fact>] + member _.``weekend submissions roll the trade date to monday``() = + Assert.Equal(DateOnly(2026, 9, 28), ConfirmationPolicy.tradeDateFor (shanghai 10 0 0 26)) + Assert.Equal(DateOnly(2026, 9, 28), ConfirmationPolicy.tradeDateFor (shanghai 15 0 0 27)) + + [<Fact>] + member _.``modeled trading day covers weekends only``() = + Assert.False(ConfirmationPolicy.isModeledTradingDay (DateOnly(2026, 9, 26))) + Assert.True(ConfirmationPolicy.isModeledTradingDay (DateOnly(2026, 9, 21))) + Assert.True(ConfirmationPolicy.isModeledTradingDay (DateOnly(2026, 10, 1))) + + [<Fact>] + member _.``units truncate at eight decimals and invested cash at cents``() = + match ConfirmationPolicy.compute 1000.00m 2.375m with + | Ok confirmed -> + Assert.Equal(421.05263157m, confirmed.Units) + Assert.Equal(999.99m, confirmed.InvestedCash) + Assert.Equal(0.01m, confirmed.ResidualCash) + | Error reason -> failwithf "unexpected deferral: %s" reason + + [<Fact>] + member _.``exact division keeps the full amount invested``() = + match ConfirmationPolicy.compute 100.00m 2.5m with + | Ok confirmed -> + Assert.Equal(40m, confirmed.Units) + Assert.Equal(100.00m, confirmed.InvestedCash) + Assert.Equal(0.00m, confirmed.ResidualCash) + | Error reason -> failwithf "unexpected deferral: %s" reason + + [<Fact>] + member _.``amounts below one unit are deferred``() = + Assert.Equal( + Error "amount converts to zero units at this unit nav", + ConfirmationPolicy.compute 0.00000001m 2.5m + ) + + [<Fact>] + member _.``unit amounts beyond database precision are deferred``() = + Assert.Equal( + Error "unit amount exceeds database precision", + ConfirmationPolicy.compute 999999999999999999.99m 0.00000001m + ) + + [<Fact>] + member _.``non positive unit nav is rejected``() = + Assert.Equal(Error "unit nav must be positive", ConfirmationPolicy.compute 100.00m 0m) + + [<Fact>] + member _.``usable nav quotes pass the no lookahead checks``() = + let quote = + navQuote (DateOnly(2026, 9, 21)) 2.5m (shanghai 14 0 0 21) (Some(shanghai 13 0 0 21)) + + let now = shanghai 15 0 0 21 + + Assert.Equal( + None, + ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now + ) + + [<Fact>] + member _.``quotes observed before confirmation without a publish timestamp are usable``() = + let quote = navQuote (DateOnly(2026, 9, 21)) 2.5m (shanghai 14 0 0 21) None + let now = shanghai 15 0 0 21 + + Assert.Equal( + None, + ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now + ) + + [<Fact>] + member _.``quotes collected after the confirmation moment defer without backdating``() = + let quote = navQuote (DateOnly(2026, 9, 21)) 2.5m (shanghai 16 0 0 21) None + let now = shanghai 15 0 0 21 + + Assert.Equal( + Some "nav for trade date 2026-09-21 is not yet collected", + ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now + ) + + [<Fact>] + member _.``stale nav quotes defer confirmation``() = + let quote = navQuote (DateOnly(2026, 9, 20)) 2.5m (shanghai 14 0 0 21) None + let now = shanghai 15 0 0 21 + + Assert.Equal( + Some "nav for trade date 2026-09-21 is not available yet", + ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now + ) + + [<Fact>] + member _.``future dated nav quotes defer confirmation``() = + let quote = navQuote (DateOnly(2026, 9, 22)) 2.5m (shanghai 14 0 0 21) None + let now = shanghai 15 0 0 21 + + Assert.Equal( + Some "nav for trade date 2026-09-21 is dated in the future", + ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now + ) + + [<Fact>] + member _.``unpublished nav quotes defer confirmation``() = + let quote = + navQuote (DateOnly(2026, 9, 21)) 2.5m (shanghai 14 0 0 21) (Some(shanghai 16 0 0 21)) + + let now = shanghai 15 0 0 21 + + Assert.Equal( + Some "nav for trade date 2026-09-21 is not yet published", + ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now + ) + + [<Fact>] + member _.``holiday trade dates without a matching nav stay pending``() = + let quote = navQuote (DateOnly(2026, 9, 30)) 2.5m (shanghai 10 0 0 30) None + let now = DateTimeOffset(2026, 10, 1, 15, 0, 0, TimeSpan.FromHours(8.0)) + + Assert.Equal( + Some "nav for trade date 2026-10-01 is not available yet", + ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 10, 1)) (DateOnly(2026, 10, 1)) now + ) + +[<Collection("postgres")>] +type SubscriptionConfirmationTests(fixture: PostgresFixture) = + let sharedRepository = + lazy + let value = FundRepository(fixture.ConnectionString) + value.EnsureSchema() + value + + let repository () = sharedRepository.Value + + let seedInstrument () = + let code = Random.Shared.Next(0, 1000000).ToString("D6") + let payload = + { + Source = "akshare" + SourceRevision = "akshare-test/eastmoney" + CollectedAt = DateTimeOffset(2026, 9, 21, 10, 0, 0, TimeSpan.Zero) + Instruments = [ { Code = code; Name = "确认测试基金"; FundType = None } ] + } + + repository().UpsertInstruments(payload, "confirm-test-hash") + code + + let createFund (initialCash: decimal) = + let command = + { + Name = "确认测试 FOF" + InitialCash = initialCash + InitialUnitNav = 1.00000000m + IsSynthetic = true + } + + let key = fixture.Key(sprintf "confirm-fund-%s" (Guid.NewGuid().ToString("N"))) + + match repository().CreateFund(key, command) with + | FundWriteResult.Created fund -> fund.Id + | other -> failwithf "unexpected fund creation result: %A" other + + let app () = App.createApplication (repository ()) + + let postOrder fundId idempotencyKey amount fee = + let body = + sprintf + "{\"fundCode\":\"%s\",\"amount\":\"%s\",\"feeAmount\":\"%s\"}" + (seedInstrument ()) + amount + fee + + PersistenceTestHelpers.invoke + (app ()) + "POST" + (sprintf "/api/funds/%O/orders" fundId) + [ + "Authorization", "Bearer test-token" + "Idempotency-Key", idempotencyKey + ] + body + |> fun (status, response) -> + if status <> 201 then failwithf "unexpected order status %d: %s" status response + PersistenceTestHelpers.responseId response + + let truncateMicroseconds (moment: DateTimeOffset) = + let utc = moment.ToUniversalTime() + DateTimeOffset(utc.Ticks - (utc.Ticks % 10L), TimeSpan.Zero) + + let insertQuoteOnDate (code: string) (nav: decimal) (navDate: DateOnly) (collectedAt: DateTimeOffset) (publishedAt: DateTimeOffset option) = + let payload: MarketDataNavPayload = + { + Source = "akshare" + SourceRevision = sprintf "akshare-test/%O" (Guid.NewGuid()) + CollectedAt = collectedAt + Code = code + Observations = + [ + { + NavDate = navDate + PublishedAt = publishedAt + Nav = nav + AccumulatedNav = Some nav + DailyReturn = Some 0.0m + } + ] + } + + repository().UpsertNavObservations(payload, "confirm-quote-hash") + + let insertQuote (code: string) (nav: decimal) (collectedAt: DateTimeOffset) (publishedAt: DateTimeOffset option) = + insertQuoteOnDate code nav (ConfirmationPolicy.tradeDateFor DateTimeOffset.Now) collectedAt publishedAt + + let execute (sql: string) (parameters: (string * obj * NpgsqlTypes.NpgsqlDbType) list) = + use connection = new NpgsqlConnection(fixture.ConnectionString) + connection.Open() + use command = connection.CreateCommand() + command.CommandText <- sql + + for name, value, dbType in parameters do + let parameter = command.Parameters.Add(name, dbType) + parameter.Value <- value + + command.ExecuteNonQuery() |> ignore + + let scalarDecimal (sql: string) (parameters: (string * obj * NpgsqlTypes.NpgsqlDbType) list) = + use connection = new NpgsqlConnection(fixture.ConnectionString) + connection.Open() + use command = connection.CreateCommand() + command.CommandText <- sql + + for name, value, dbType in parameters do + let parameter = command.Parameters.Add(name, dbType) + parameter.Value <- value + + command.ExecuteScalar() :?> decimal + + let scalarText (sql: string) (parameters: (string * obj * NpgsqlTypes.NpgsqlDbType) list) = + use connection = new NpgsqlConnection(fixture.ConnectionString) + connection.Open() + use command = connection.CreateCommand() + command.CommandText <- sql + + for name, value, dbType in parameters do + let parameter = command.Parameters.Add(name, dbType) + parameter.Value <- value + + command.ExecuteScalar() :?> string + + let orderFundCode orderId = + scalarText "SELECT fund_code FROM subscription_orders WHERE id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ] + + let availableCash fundId = + scalarDecimal "SELECT available_cash FROM funds WHERE id = @fund_id" [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ] + + let reservedCash fundId = + scalarDecimal "SELECT reserved_cash FROM funds WHERE id = @fund_id" [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ] + + let orderStatus orderId = + scalarDecimal + "SELECT count(*)::numeric FROM subscription_orders WHERE id = @order_id AND status = 'confirmed'" + [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ] + |> int64 + + let pendingOrderReason orderId = + use connection = new NpgsqlConnection(fixture.ConnectionString) + connection.Open() + use command = connection.CreateCommand() + command.CommandText <- "SELECT status || '|' || coalesce(pending_reason, '') FROM subscription_orders WHERE id = @order_id" + let parameter = command.Parameters.Add("order_id", NpgsqlTypes.NpgsqlDbType.Uuid) + parameter.Value <- box orderId + command.ExecuteScalar() :?> string + + let confirmKey fundId orderId key = repository().ConfirmSubscriptionOrder(key, fundId, orderId) + + [<Fact>] + member _.``confirmation persists quote evidence cash and positions atomically``() = + let fundId = createFund 10000.00m + let orderId = postOrder fundId "confirm-success-1" "1000.00" "0.00" + let code = orderFundCode orderId + let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0)) + insertQuote code 2.5m collected None + let tradeDate = ConfirmationPolicy.tradeDateFor DateTimeOffset.Now + let key = fixture.Key("confirm-success-1") + + match confirmKey fundId orderId key with + | OrderConfirmed confirmed -> + Assert.Equal("confirmed", confirmed.Status) + Assert.Equal(Some key, confirmed.ConfirmIdempotencyKey) + Assert.Equal(Some 400.00000000m, confirmed.ConfirmedUnits) + Assert.Equal(Some 1000.00m, confirmed.ConfirmedInvestedCash) + Assert.Equal(Some 0.00m, confirmed.ConfirmedResidualCash) + Assert.True(confirmed.ConfirmedAt.IsSome) + + match confirmed.ConfirmedQuote with + | Some quote -> + Assert.Equal(2.5m, quote.Nav) + Assert.Equal(tradeDate, quote.NavDate) + Assert.Equal("akshare", quote.Source) + Assert.Equal(collected, quote.CollectedAt) + Assert.Equal(None, quote.PublishedAt) + | None -> failwith "confirmed quote evidence missing" + + Assert.Equal(9000.00m, availableCash fundId) + Assert.Equal(0.00m, reservedCash fundId) + + let positions = repository().GetFundPositions(fundId) + Assert.Equal(1, positions.Length) + let position = positions.[0] + Assert.Equal(400.00000000m, position.Units) + Assert.Equal(1000.00m, position.CostCash) + Assert.Equal(Some 2.5m, position.ValuationNav) + Assert.Equal(Some tradeDate, position.ValuationNavDate) + | other -> failwithf "unexpected confirm result: %A" other + + [<Fact>] + member _.``replaying the same idempotency key never double executes``() = + let fundId = createFund 10000.00m + let orderId = postOrder fundId "confirm-replay-1" "1000.00" "0.00" + let code = orderFundCode orderId + let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0)) + insertQuote code 2.5m collected None + let key = fixture.Key("confirm-replay-key") + + match confirmKey fundId orderId key with + | OrderConfirmed _ -> () + | other -> failwithf "unexpected first confirm result: %A" other + + let cashAfterFirst = availableCash fundId + + insertQuote code 9.9m (truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-8.0))) None + + match confirmKey fundId orderId key with + | ConfirmReplayed replayed -> + Assert.Equal("confirmed", replayed.Status) + + match replayed.ConfirmedQuote with + | Some quote -> + Assert.Equal(2.5m, quote.Nav) + Assert.Equal(collected, quote.CollectedAt) + Assert.Equal(None, quote.PublishedAt) + | None -> failwith "replayed quote evidence missing" + + Assert.Equal(400.00000000m, replayed.ConfirmedUnits |> Option.get) + | other -> failwithf "unexpected replay result: %A" other + + Assert.Equal(cashAfterFirst, availableCash fundId) + Assert.Equal(1, (repository().GetFundPositions(fundId)).Length) + Assert.Equal(1L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM subscription_order_events WHERE order_id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ]) + + [<Fact>] + member _.``a confirmed order cannot be confirmed with a different key``() = + let fundId = createFund 10000.00m + let orderId = postOrder fundId "confirm-other-key-1" "1000.00" "0.00" + let code = orderFundCode orderId + let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0)) + insertQuote code 2.5m collected None + let cashAfterFirst = + match confirmKey fundId orderId (fixture.Key("confirm-other-key-first")) with + | OrderConfirmed _ -> availableCash fundId + | other -> failwithf "unexpected first confirm result: %A" other + + match confirmKey fundId orderId (fixture.Key("confirm-other-key-second")) with + | ConfirmAlreadyConfirmed -> Assert.Equal(cashAfterFirst, availableCash fundId) + | other -> failwithf "unexpected second confirm result: %A" other + + [<Fact>] + member _.``a consumed confirm key cannot bind to another order``() = + let fundId = createFund 10000.00m + let firstOrder = postOrder fundId "confirm-consume-1" "1000.00" "0.00" + let code = orderFundCode firstOrder + let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0)) + insertQuote code 2.5m collected None + let key = fixture.Key("confirm-consume-shared-key") + + match confirmKey fundId firstOrder key with + | OrderConfirmed _ -> () + | other -> failwithf "unexpected first confirm result: %A" other + + let secondOrder = postOrder fundId "confirm-consume-2" "500.00" "0.00" + + match confirmKey fundId secondOrder key with + | ConfirmIdempotencyConflict -> + Assert.Equal(0L, orderStatus secondOrder) + Assert.Equal(400.00000000m, (repository().GetFundPositions(fundId)).[0].Units) + | other -> failwithf "unexpected conflict result: %A" other + + [<Fact>] + member _.``mid confirmation failure rolls back every write and the same key can retry``() = + let fundId = createFund 10000.00m + let orderId = postOrder fundId "confirm-rollback-1" "1000.00" "0.00" + let code = orderFundCode orderId + let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0)) + insertQuote code 2.5m collected None + let key = fixture.Key("confirm-rollback-key") + + execute + "UPDATE subscription_orders SET reserved_total = reserved_total + 1 WHERE id = @order_id" + [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ] + + Assert.Throws<Exception>(fun () -> confirmKey fundId orderId key |> ignore) |> ignore + + Assert.Equal(0L, orderStatus orderId) + Assert.Equal(0L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM subscription_confirm_idempotencies WHERE order_id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ]) + Assert.Equal(0L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM subscription_order_events WHERE order_id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ]) + Assert.Equal(0L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM fund_positions WHERE fund_id = @fund_id" [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ]) + Assert.Equal(9000.00m, availableCash fundId) + Assert.Equal(1000.00m, reservedCash fundId) + + execute + "UPDATE subscription_orders SET reserved_total = reserved_total - 1 WHERE id = @order_id" + [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ] + + match confirmKey fundId orderId key with + | OrderConfirmed confirmed -> + Assert.Equal(400.00000000m, confirmed.ConfirmedUnits |> Option.get) + Assert.Equal(9000.00m, availableCash fundId) + | other -> failwithf "unexpected retry result: %A" other + + [<Fact>] + member _.``missing nav stays pending without consuming the key and confirms later``() = + let fundId = createFund 10000.00m + let orderId = postOrder fundId "confirm-pending-1" "1000.00" "0.00" + let key = fixture.Key("confirm-pending-key") + + match confirmKey fundId orderId key with + | ConfirmPendingNav pending -> + Assert.Equal("pending_nav", pending.Status) + Assert.True(pending.PendingReason.IsSome) + Assert.True(pending.PendingReason.Value.Contains("not available yet")) + Assert.Equal(None, pending.ConfirmIdempotencyKey) + Assert.Equal(None, pending.ConfirmedAt) + Assert.Equal(None, pending.ConfirmedQuote) + | other -> failwithf "unexpected pending result: %A" other + + let code = orderFundCode orderId + let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0)) + insertQuote code 2.5m collected None + + match confirmKey fundId orderId key with + | OrderConfirmed confirmed -> + Assert.Equal(Some key, confirmed.ConfirmIdempotencyKey) + Assert.Equal(9000.00m, availableCash fundId) + + match confirmed.ConfirmedQuote with + | Some quote -> + Assert.Equal(ConfirmationPolicy.tradeDateFor DateTimeOffset.Now, quote.NavDate) + Assert.Equal(collected, quote.CollectedAt) + | None -> failwith "late confirmed quote evidence missing" + | other -> failwithf "unexpected late confirm result: %A" other + + [<Fact>] + member _.``retrying after the cutoff keeps the original order trade date``() = + let fundId = createFund 10000.00m + let orderId = postOrder fundId "confirm-overnight-1" "1000.00" "0.00" + let code = orderFundCode orderId + let originalTradeDate = ConfirmationPolicy.tradeDateFor (DateTimeOffset.Now.AddDays(-1.0)) + let originalCollected = truncateMicroseconds (DateTimeOffset.Now.AddDays(-1.0).AddHours(-2.0)) + insertQuoteOnDate code 3.7m originalTradeDate originalCollected None + + execute + "UPDATE subscription_orders SET submitted_at = submitted_at - interval '1 day', trade_date = @trade_date WHERE id = @order_id" + [ "trade_date", box originalTradeDate, NpgsqlTypes.NpgsqlDbType.Date; "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ] + + match confirmKey fundId orderId (fixture.Key("confirm-overnight-key")) with + | OrderConfirmed confirmed -> + Assert.Equal(originalTradeDate, confirmed.TradeDate) + + match confirmed.ConfirmedQuote with + | Some quote -> + Assert.Equal(originalTradeDate, quote.NavDate) + Assert.Equal(3.7m, quote.Nav) + Assert.Equal(originalCollected, quote.CollectedAt) + | None -> failwith "overnight quote evidence missing" + + Assert.Equal(Some 270.27027027m, confirmed.ConfirmedUnits) + Assert.Equal(Some 999.99m, confirmed.ConfirmedInvestedCash) + Assert.Equal(9000.01m, availableCash fundId) + | other -> failwithf "unexpected overnight confirm result: %A" other + + [<Fact>] + member _.``quotes collected after the confirmation moment stay pending``() = + let fundId = createFund 10000.00m + let orderId = postOrder fundId "confirm-future-collect-1" "1000.00" "0.00" + let code = orderFundCode orderId + let futureCollected = truncateMicroseconds (DateTimeOffset.Now.AddHours(1.0)) + insertQuote code 2.5m futureCollected None + + match confirmKey fundId orderId (fixture.Key("confirm-future-collect-key")) with + | ConfirmPendingNav pending -> + Assert.Equal("pending_nav", pending.Status) + Assert.True(pending.PendingReason.Value.Contains("not yet collected")) + Assert.Equal(None, pending.ConfirmIdempotencyKey) + | other -> failwithf "unexpected future collected result: %A" other + + [<Fact>] + member _.``concurrent confirms with distinct keys execute exactly once``() = + let fundId = createFund 10000.00m + let orderId = postOrder fundId "confirm-parallel-1" "1000.00" "0.00" + let code = orderFundCode orderId + let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0)) + insertQuote code 2.5m collected None + + let results = + [| 1..4 |] + |> Array.map (fun index -> + Task.Run(fun () -> + confirmKey fundId orderId (fixture.Key(sprintf "confirm-parallel-key-%d" index)))) + |> Task.WhenAll + |> fun task -> task.GetAwaiter().GetResult() + |> Array.toList + + let confirmedCount = + results + |> List.filter (function + | OrderConfirmed _ -> true + | _ -> false) + |> List.length + + Assert.Equal(1, confirmedCount) + + for result in results do + match result with + | OrderConfirmed _ -> () + | ConfirmAlreadyConfirmed + | ConfirmIdempotencyConflict -> () + | other -> failwithf "unexpected concurrent result: %A" other + + Assert.Equal(9000.00m, availableCash fundId) + Assert.Equal(0.00m, reservedCash fundId) + Assert.Equal(1, (repository().GetFundPositions(fundId)).Length) + Assert.Equal(400.00000000m, (repository().GetFundPositions(fundId)).[0].Units) + + [<Fact>] + member _.``a rebuilt repository re reads the persisted evidence``() = + let fundId = createFund 10000.00m + let orderId = postOrder fundId "confirm-rebuild-1" "1000.00" "0.00" + let code = orderFundCode orderId + let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0)) + insertQuote code 2.5m collected (Some(truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-5.0)))) + let key = fixture.Key("confirm-rebuild-key") + + match confirmKey fundId orderId key with + | OrderConfirmed _ -> () + | other -> failwithf "unexpected confirm result: %A" other + + let rebuilt = FundRepository(fixture.ConnectionString) + rebuilt.EnsureSchema() + + let orders = rebuilt.GetSubscriptionOrders(fundId) + Assert.Equal(1, orders.Length) + let order = orders.[0] + + match order.ConfirmedQuote with + | Some quote -> + Assert.Equal(2.5m, quote.Nav) + Assert.Equal("akshare", quote.Source) + Assert.Equal(collected, quote.CollectedAt) + Assert.True(quote.PublishedAt.IsSome) + | None -> failwith "rebuilt quote evidence missing" + + Assert.Equal(Some key, order.ConfirmIdempotencyKey) + Assert.Equal(Some 400.00000000m, order.ConfirmedUnits) + Assert.Equal(Some 1000.00m, order.ConfirmedInvestedCash) + Assert.Equal(Some 0.00m, order.ConfirmedResidualCash) + + let positions = rebuilt.GetFundPositions(fundId) + Assert.Equal(1, positions.Length) + Assert.Equal(400.00000000m, positions.[0].Units) |