Drive SIP plans through the shared order pipeline (3d-6)

Advance executes due periods as real subscription orders: each due
trade date claims a sip_executions row (PRIMARY KEY plan_id+trade_date
makes same-plan same-day runs atomic), places the order through
CreateSubscriptionOrder with a deterministic idempotency key
(sip:{plan}:{date}) and the period's trade date, then confirms through
the shared ConfirmSubscriptionOrder pipeline with key
sip-confirm:{plan}:{date} — identical freeze/fee/units math as manual
orders, nothing bypasses confirmation. Outcomes: succeeded,
pending_nav (cash stays frozen; later drives retry the confirmation
once the NAV lands), insufficient_cash (no order placed, period fails
visibly, reruns keep the marker), failed. Plans roll their
next_trade_date past the processed window and repeats of the same
endDate replay recorded executions instead of re-debiting.

Domain adds SipPolicy.advancePlan (due-date enumeration; weekends-only
trading-calendar approximation, noted in code). POST
/funds/{id}/sip/advance {endDate?, limit?} drives execution on demand
(no daemon). Plan responses/rows now carry last execution status/date;
front-end shows 已执行/待净值/现金不足 per plan. Optional
tradeDateOverride/anchorOverride keep manual flows untouched.

1 files changed, 339 insertions, 0 deletions

diff --git a/tests/FundLab.Api.Tests/SipAdvanceTests.fs b/tests/FundLab.Api.Tests/SipAdvanceTests.fs
new file mode 100644
index 0000000..7ac305d
--- /dev/null
+++ b/tests/FundLab.Api.Tests/SipAdvanceTests.fs
@@ -0,0 +1,339 @@
+namespace FundLab.Api.Tests
+
+open System
+open Npgsql
+open Xunit
+open FundLab.Api
+open FundLab.Domain
+
+[<Collection("postgres")>]
+type SipAdvanceTests(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, 8, 0, 0, TimeSpan.Zero)
+                Instruments = [ { Code = code; Name = "定投执行测试基金"; FundType = None } ]
+            }
+
+        repository().UpsertInstruments(payload, "sip-advance-test-hash")
+        code
+
+    let createFund (initialCash: decimal) =
+        let command =
+            {
+                Name = "定投执行测试 FOF"
+                InitialCash = initialCash
+                InitialUnitNav = 1.00000000m
+                IsSynthetic = true
+            }
+
+        let key = fixture.Key(sprintf "sip-adv-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 truncateMicroseconds (moment: DateTimeOffset) =
+        let utc = moment.ToUniversalTime()
+        DateTimeOffset(utc.Ticks - (utc.Ticks % 10L), TimeSpan.Zero)
+
+    let insertQuoteOnDate (code: string) (nav: decimal) (navDate: DateOnly) =
+        let revision = sprintf "akshare-test/%O" (Guid.NewGuid())
+        let payload: MarketDataNavPayload =
+            {
+                Source = "akshare"
+                SourceRevision = revision
+                CollectedAt = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
+                Code = code
+                Observations =
+                    [
+                        {
+                            NavDate = navDate
+                            PublishedAt = None
+                            Nav = nav
+                            AccumulatedNav = Some nav
+                            DailyReturn = Some 0.0m
+                        }
+                    ]
+            }
+
+        repository().UpsertNavObservations(payload, sprintf "sip-advance-hash/%s" revision)
+
+    let postPlan (fundId: Guid) (code: string) (amount: string) (frequency: string) =
+        let body =
+            sprintf "{\"instrumentCode\":\"%s\",\"amount\":\"%s\",\"frequency\":\"%s\"}" code amount frequency
+
+        PersistenceTestHelpers.invoke
+            (app ())
+            "POST"
+            (sprintf "/api/funds/%O/sip/plans" fundId)
+            [
+                "Authorization", "Bearer test-token"
+                "Idempotency-Key", fixture.Key(sprintf "sip-plan-%s" (Guid.NewGuid().ToString("N")))
+            ]
+            body
+        |> fun (status, response) ->
+            if status <> 201 then failwithf "unexpected plan status %d: %s" status response
+            PersistenceTestHelpers.responseId response
+
+    // plans anchored in the past so due periods fall on dates whose NAV the tests can seed
+    let createPlanWithAnchor (fundId: Guid) (code: string) (amount: decimal) (frequency: SipFrequency) =
+        let key = fixture.Key(sprintf "sip-plan-%s" (Guid.NewGuid().ToString("N")))
+
+        match repository().CreateSipPlan(key, fundId, { InstrumentCode = code; Amount = amount; Frequency = frequency }, DateOnly(2026, 9, 7)) with
+        | SipPlanWriteResult.SipPlanCreated plan -> plan.Id
+        | other -> failwithf "unexpected sip plan result: %A" other
+
+    let postManualOrder (fundId: Guid) (code: string) (amount: string) =
+        let body =
+            sprintf "{\"fundCode\":\"%s\",\"amount\":\"%s\",\"feeAmount\":\"0.00\"}" code amount
+
+        PersistenceTestHelpers.invoke
+            (app ())
+            "POST"
+            (sprintf "/api/funds/%O/orders" fundId)
+            [
+                "Authorization", "Bearer test-token"
+                "Idempotency-Key", fixture.Key(sprintf "manual-%s" (Guid.NewGuid().ToString("N")))
+            ]
+            body
+        |> fun (status, response) ->
+            if status <> 201 then failwithf "unexpected manual order status %d: %s" status response
+            PersistenceTestHelpers.responseId response
+
+    let confirmManualOrder (fundId: Guid) (orderId: Guid) =
+        PersistenceTestHelpers.invoke
+            (app ())
+            "POST"
+            (sprintf "/api/funds/%O/orders/%O/confirm" fundId orderId)
+            [
+                "Authorization", "Bearer test-token"
+                "Idempotency-Key", fixture.Key(sprintf "manual-confirm-%s" (Guid.NewGuid().ToString("N")))
+            ]
+            ""
+        |> fun (status, _) ->
+            if status <> 200 then failwithf "unexpected manual confirm status %d" status
+
+    let advance (fundId: Guid) (body: string) =
+        PersistenceTestHelpers.invoke
+            (app ())
+            "POST"
+            (sprintf "/api/funds/%O/sip/advance" fundId)
+            [ "Authorization", "Bearer test-token" ]
+            body
+
+    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 availableCash fundId =
+        scalarDecimal "SELECT available_cash FROM funds WHERE id = @fund_id" [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ]
+
+    let executionCount fundId =
+        scalarDecimal
+            "SELECT count(*)::numeric FROM sip_executions e JOIN sip_plans p ON p.id = e.plan_id WHERE p.fund_id = @fund_id"
+            [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ]
+        |> int64
+
+    let confirmedOrderCount fundId =
+        scalarDecimal
+            "SELECT count(*)::numeric FROM subscription_orders WHERE fund_id = @fund_id AND status = 'confirmed'"
+            [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ]
+        |> int64
+
+    let sipOrderSnapshot fundId =
+        use connection = new NpgsqlConnection(fixture.ConnectionString)
+        connection.Open()
+        use command = connection.CreateCommand()
+        command.CommandText <-
+            "SELECT o.confirmed_units, o.confirmed_invested_cash, o.confirmed_residual_cash FROM subscription_orders o JOIN sip_executions e ON e.order_id = o.id WHERE o.fund_id = @fund_id ORDER BY o.submitted_at, o.id"
+        let parameter = command.Parameters.Add("fund_id", NpgsqlTypes.NpgsqlDbType.Uuid)
+        parameter.Value <- box fundId
+        use reader = command.ExecuteReader()
+        let rows = ResizeArray<decimal * decimal * decimal>()
+
+        while reader.Read() do
+            rows.Add(reader.GetDecimal(0), reader.GetDecimal(1), reader.GetDecimal(2))
+
+        rows |> Seq.toList
+
+    [<Fact>]
+    member _.``sip advance executes orders through the shared confirmation pipeline``() =
+        let fundId = createFund 10000.00m
+        let code = seedInstrument ()
+        let planId = createPlanWithAnchor fundId code 100.00m Weekly
+
+        insertQuoteOnDate code 2.5m (DateOnly(2026, 9, 14))
+        insertQuoteOnDate code 2.5m (DateOnly(2026, 9, 21))
+
+        let status, response = advance fundId "{\"endDate\":\"2026-09-21\"}"
+
+        Assert.Equal(200, status)
+        Assert.Contains("\"status\":\"succeeded\"", response)
+        Assert.Contains("\"tradeDate\":\"2026-09-14\"", response)
+        Assert.Contains("\"tradeDate\":\"2026-09-21\"", response)
+        Assert.Contains("\"nextTradeDate\":\"2026-09-28\"", response)
+
+        Assert.Equal(9800.00m, availableCash fundId)
+        Assert.Equal(2L, executionCount fundId)
+        Assert.Equal(2L, confirmedOrderCount fundId)
+
+        Assert.All(sipOrderSnapshot fundId, fun (units, invested, residual) ->
+            Assert.Equal(40.00000000m, units)
+            Assert.Equal(100.00m, invested)
+            Assert.Equal(0.00m, residual))
+
+    [<Fact>]
+    member _.``sip orders match a manual order with the same parameters exactly``() =
+        let fundId = createFund 10000.00m
+        let code = seedInstrument ()
+        let planId = createPlanWithAnchor fundId code 100.00m Weekly
+        insertQuoteOnDate code 2.5m (DateOnly(2026, 9, 14))
+        insertQuoteOnDate code 2.5m (DateOnly(2026, 9, 21))
+
+        let _, _ = advance fundId "{\"endDate\":\"2026-09-14\"}"
+
+        let manualOrderId = postManualOrder fundId code "100.00"
+        confirmManualOrder fundId manualOrderId
+
+        use connection = new NpgsqlConnection(fixture.ConnectionString)
+        connection.Open()
+        use command = connection.CreateCommand()
+        command.CommandText <-
+            "SELECT confirmed_units, confirmed_invested_cash, confirmed_residual_cash FROM subscription_orders WHERE id = @order_id"
+        let parameter = command.Parameters.Add("order_id", NpgsqlTypes.NpgsqlDbType.Uuid)
+        parameter.Value <- box manualOrderId
+        use reader = command.ExecuteReader()
+        reader.Read() |> ignore
+        let manualUnits = reader.GetDecimal(0)
+        let manualInvested = reader.GetDecimal(1)
+        let manualResidual = reader.GetDecimal(2)
+
+        let sipSnapshots = sipOrderSnapshot fundId
+
+        Assert.Equal(1, sipSnapshots.Length)
+
+        let sipUnits, sipInvested, sipResidual = sipSnapshots[0]
+
+        Assert.Equal(manualUnits, sipUnits)
+        Assert.Equal(manualInvested, sipInvested)
+        Assert.Equal(manualResidual, sipResidual)
+
+    [<Fact>]
+    member _.``sip advance reruns are idempotent for the same trade dates``() =
+        let fundId = createFund 10000.00m
+        let code = seedInstrument ()
+        let planId = createPlanWithAnchor fundId code 100.00m Weekly
+        insertQuoteOnDate code 2.5m (DateOnly(2026, 9, 14))
+        insertQuoteOnDate code 2.5m (DateOnly(2026, 9, 21))
+
+        let firstStatus, firstResponse = advance fundId "{\"endDate\":\"2026-09-21\"}"
+        let firstCash = availableCash fundId
+        let firstCount = executionCount fundId
+        let firstOrders = confirmedOrderCount fundId
+
+        let secondStatus, secondResponse = advance fundId "{\"endDate\":\"2026-09-21\"}"
+
+        Assert.Equal(200, firstStatus)
+        Assert.Equal(200, secondStatus)
+        Assert.Equal(firstResponse, secondResponse)
+        Assert.Equal(firstCash, availableCash fundId)
+        Assert.Equal(firstCount, executionCount fundId)
+        Assert.Equal(firstOrders, confirmedOrderCount fundId)
+
+    [<Fact>]
+    member _.``insufficient cash marks the period without placing any order``() =
+        let fundId = createFund 1.00m
+        let code = seedInstrument ()
+        let planId = createPlanWithAnchor fundId code 100.00m Weekly
+        insertQuoteOnDate code 2.5m (DateOnly(2026, 9, 14))
+
+        let status, response = advance fundId "{\"endDate\":\"2026-09-14\"}"
+
+        Assert.Equal(200, status)
+        Assert.Contains("\"status\":\"insufficient_cash\"", response)
+        Assert.Contains("\"orderId\":null", response)
+        Assert.Equal(1L, executionCount fundId)
+        Assert.Equal(0L, confirmedOrderCount fundId)
+        Assert.Equal(1.00m, availableCash fundId)
+
+        // rerun keeps the failed marker and never debits cash
+        let rerunStatus, rerunResponse = advance fundId "{\"endDate\":\"2026-09-14\"}"
+        Assert.Equal(200, rerunStatus)
+        Assert.Equal(response, rerunResponse)
+        Assert.Equal(1.00m, availableCash fundId)
+
+    [<Fact>]
+    member _.``pending nav executions are retried once the nav lands``() =
+        let fundId = createFund 10000.00m
+        let code = seedInstrument ()
+        let planId = createPlanWithAnchor fundId code 100.00m Weekly
+
+        let status, response = advance fundId "{\"endDate\":\"2026-09-14\"}"
+
+        Assert.Equal(200, status)
+        Assert.Contains("\"status\":\"pending_nav\"", response)
+
+        // frozen cash is held by the submitted order until confirmation
+        let frozen = availableCash fundId
+        Assert.Equal(9900.00m, frozen)
+
+        insertQuoteOnDate code 2.5m (DateOnly(2026, 9, 14))
+
+        let retryStatus, retryResponse = advance fundId "{\"endDate\":\"2026-09-14\"}"
+
+        Assert.Equal(200, retryStatus)
+        Assert.Contains("\"status\":\"succeeded\"", retryResponse)
+        // freeze happened on first drive; confirmation converts frozen cash to cost, no extra flow
+        Assert.Equal(9900.00m, availableCash fundId)
+        Assert.Equal(1L, executionCount fundId)
+
+    [<Fact>]
+    member _.``redemption frozen shares do not bypass the cash check``() =
+        let fundId = createFund 10000.00m
+        let code = seedInstrument ()
+        let planId = createPlanWithAnchor fundId code 100.00m Weekly
+
+        // drain available cash through a subscription freeze, mirroring held positions
+        let body = sprintf "{\"fundCode\":\"%s\",\"amount\":\"10000.00\",\"feeAmount\":\"0.00\"}" code
+
+        PersistenceTestHelpers.invoke
+            (app ())
+            "POST"
+            (sprintf "/api/funds/%O/orders" fundId)
+            [
+                "Authorization", "Bearer test-token"
+                "Idempotency-Key", fixture.Key(sprintf "hold-%s" (Guid.NewGuid().ToString("N")))
+            ]
+            body
+        |> fun (status, _) -> Assert.Equal(201, status)
+
+        insertQuoteOnDate code 2.5m (DateOnly(2026, 9, 14))
+
+        let status, response = advance fundId "{\"endDate\":\"2026-09-14\"}"
+
+        Assert.Equal(200, status)
+        Assert.Contains("\"status\":\"insufficient_cash\"", response)
+        Assert.Equal(0L, confirmedOrderCount fundId)
+        Assert.Equal(0.00m, availableCash fundId)