Add bond sell/redemption ledger, bond cashflow events and maturity calendar (3d-30 A)

2 files changed, 772 insertions, 4 deletions

diff --git a/src/FundLab.Api/App.fs b/src/FundLab.Api/App.fs
index 16da159..d515ca6 100644
--- a/src/FundLab.Api/App.fs
+++ b/src/FundLab.Api/App.fs
@@ -231,6 +231,53 @@ type BondCashflowsResponse =
         events: BondCashflowResponse list
     }
 
+type BondSellResponse =
+    {
+        id: Guid
+        fundId: Guid
+        instrumentCode: string
+        bondName: string option
+        quantity: string
+        price: string
+        cleanPrice: string
+        accruedInterest: string
+        parValue: string
+        settlementDate: string
+        tradeDate: string
+        feeAmount: string
+        proceeds: string
+        costReleased: string
+        realizedPnl: string
+        executedAt: string
+        isSynthetic: bool
+    }
+
+type BondSellsResponse =
+    {
+        fundId: Guid
+        sells: BondSellResponse list
+    }
+
+type BondCalendarEntryResponse =
+    {
+        instrumentCode: string
+        bondName: string option
+        eventType: string
+        eventDate: string
+        quantity: string
+        source: string
+        amount: string option
+        note: string option
+    }
+
+type BondCalendarResponse =
+    {
+        fundId: Guid
+        fromDate: string
+        toDate: string
+        entries: BondCalendarEntryResponse list
+    }
+
 type ValuationPositionResponse =
     {
         instrumentCode: string
@@ -719,6 +766,27 @@ module App =
             createdAt = timestampText record.CreatedAt
         }
 
+    let private bondSellResponse (record: BondSellRecord) : BondSellResponse =
+        {
+            id = record.Id
+            fundId = record.FundId
+            instrumentCode = record.InstrumentCode
+            bondName = record.BondName
+            quantity = decimalText record.Quantity
+            price = decimalText record.Price
+            cleanPrice = decimalText record.CleanPrice
+            accruedInterest = decimalText record.AccruedInterest
+            parValue = decimalText record.ParValue
+            settlementDate = dateText record.SettlementDate
+            tradeDate = dateText record.TradeDate
+            feeAmount = cashText record.FeeAmount
+            proceeds = cashText record.Proceeds
+            costReleased = cashText record.CostReleased
+            realizedPnl = cashText record.RealizedPnl
+            executedAt = timestampText record.ExecutedAt
+            isSynthetic = record.IsSynthetic
+        }
+
     let private bondPositionResponse (position: BondPositionRecord) : BondPositionResponse =
         {
             instrumentCode = position.InstrumentCode
@@ -2605,6 +2673,273 @@ module App =
         with
         | :? JsonException -> Error "request body must be valid JSON"
 
+    let private parseBondSellCommand (body: string) : Result<BondSellCommand, string> =
+        try
+            use document = JsonDocument.Parse(body)
+            let root = document.RootElement
+
+            if root.ValueKind <> JsonValueKind.Object then
+                Error "request body must be a JSON object"
+            else
+                match tryStringProperty root "instrumentCode", tryStringProperty root "quantity" with
+                | Some code, Some quantityText ->
+                    if code.Trim().Length <> 6 || not (code.Trim() |> Seq.forall Char.IsDigit) then
+                        Error "instrumentCode must contain exactly six digits"
+                    else
+                        match tryDecimal "quantity" quantityText with
+                        | Error message -> Error message
+                        | Ok quantity ->
+                            let tradeDate =
+                                match tryStringProperty root "tradeDate" with
+                                | None -> Ok None
+                                | Some text ->
+                                    match DateOnly.TryParseExact(text, "yyyy-MM-dd", CultureInfo.InvariantCulture, DateTimeStyles.None) with
+                                    | true, date -> Ok(Some date)
+                                    | false, _ -> Error "tradeDate must be an ISO date (yyyy-MM-dd)"
+
+                            let fee =
+                                match tryStringProperty root "feeAmount" with
+                                | None -> Ok 0m
+                                | Some text -> tryDecimal "feeAmount" text
+
+                            match tradeDate, fee with
+                            | Error message, _ -> Error message
+                            | _, Error message -> Error message
+                            | Ok tradeDate, Ok fee ->
+                                Ok
+                                    {
+                                        InstrumentCode = code.Trim()
+                                        BondName = tryStringProperty root "bondName"
+                                        Quantity = quantity
+                                        Price = 0m
+                                        CleanPrice = 0m
+                                        AccruedInterest = 0m
+                                        ParValue = 100m
+                                        SettlementDate = DateOnly.FromDateTime DateTime.UtcNow
+                                        TradeDate = tradeDate
+                                        FeeAmount = fee
+                                    }
+                | _ -> Error "instrumentCode and quantity are required"
+        with
+        | :? JsonException -> Error "request body must be valid JSON"
+
+    let private createBondSell (repository: FundRepository) (probes: MarketProbes option) (fundIdText: string) : HttpHandler =
+        fun next ctx ->
+            task {
+                match Guid.TryParse fundIdText with
+                | false, _ ->
+                    return! invokeHandler (errorResponse 400 "INVALID_BOND_SELL_REQUEST" "fund id must be a UUID") next ctx
+                | true, fundId ->
+                    use reader = new StreamReader(ctx.Request.Body)
+                    let! body = reader.ReadToEndAsync()
+                    let idempotencyKey = ctx.Request.Headers["Idempotency-Key"].ToString()
+
+                    match parseBondSellCommand body with
+                    | Error message ->
+                        return! invokeHandler (errorResponse 400 "INVALID_BOND_SELL_REQUEST" message) next ctx
+                    | Ok command ->
+                        match probes with
+                        | None ->
+                            return! invokeHandler (marketDataError (MarketDataCollectorUnavailable "bond quote probe is not configured")) next ctx
+                        | Some configured ->
+                            match configured.BondQuotes.GetQuote(command.InstrumentCode, ctx.RequestAborted) with
+                            | Error failure -> return! invokeHandler (marketDataError failure) next ctx
+                            | Ok quote ->
+                                let terms = BondQuote.tryTerms quote
+                                let cleanPrice = quote.CleanPrice |> Option.orElse quote.Price
+
+                                match cleanPrice with
+                                | None ->
+                                    return!
+                                        invokeHandler
+                                            (marketDataError (InvalidMarketDataPayload "bond quote did not include a price"))
+                                            next
+                                            ctx
+                                | Some clean ->
+                                    let asOf =
+                                        command.TradeDate
+                                        |> Option.orElse quote.Date
+                                        |> Option.orElse quote.PublishDate
+                                        |> Option.defaultValue (DateOnly.FromDateTime DateTime.UtcNow)
+
+                                    let computedAccrued =
+                                        terms |> Option.map (fun value -> BondRules.accruedInterest value asOf)
+
+                                    let accrued =
+                                        quote.AccruedInterest |> Option.orElse computedAccrued |> Option.defaultValue 0m
+
+                                    let dirtyPrice = BondRules.dirtyPrice clean accrued
+                                    let parValue = quote.ParValue |> Option.defaultValue 100m
+
+                                    let settlement =
+                                        terms
+                                        |> Option.map (fun value -> BondRules.settlementDate value asOf)
+                                        |> Option.defaultValue asOf
+
+                                    let resolvedName =
+                                        match command.BondName with
+                                        | Some name when not (String.IsNullOrWhiteSpace name) -> Some name
+                                        | _ ->
+                                            match quote.Name with
+                                            | Some name when not (String.IsNullOrWhiteSpace name) -> Some name
+                                            | _ -> None
+
+                                    let priced =
+                                        {
+                                            command with
+                                                Price = dirtyPrice
+                                                CleanPrice = clean
+                                                AccruedInterest = accrued
+                                                ParValue = parValue
+                                                SettlementDate = settlement
+                                                TradeDate = Some asOf
+                                                BondName = resolvedName
+                                        }
+
+                                    try
+                                        match repository.CreateBondSell(idempotencyKey, fundId, priced) with
+                                        | BondSellWriteResult.BondSellCreated sell ->
+                                            return! invokeHandler (setStatusCode 201 >=> json (bondSellResponse sell)) next ctx
+                                        | BondSellWriteResult.BondSellReplayed sell ->
+                                            return! invokeHandler (json (bondSellResponse sell)) next ctx
+                                        | BondSellWriteResult.BondSellIdempotencyConflict ->
+                                            return! invokeHandler (errorResponse 409 "IDEMPOTENCY_CONFLICT" "idempotency key was used with a different request") next ctx
+                                        | BondSellWriteResult.BondSellInvalid message ->
+                                            return! invokeHandler (errorResponse 400 "INVALID_BOND_SELL_REQUEST" message) next ctx
+                                        | BondSellWriteResult.BondSellInsufficientHoldings message ->
+                                            return! invokeHandler (errorResponse 400 "INSUFFICIENT_BOND_HOLDINGS" message) next ctx
+                                        | BondSellWriteResult.BondSellFundNotFound ->
+                                            return! invokeHandler (errorResponse 404 "FUND_NOT_FOUND" "fund was not found") next ctx
+                                    with _ ->
+                                        return! invokeHandler (errorResponse 500 "PERSISTENCE_ERROR" "bond sell persistence failed") next ctx
+            }
+
+    let private getBondSells (repository: FundRepository) (fundIdText: string) : HttpHandler =
+        fun next ctx ->
+            match Guid.TryParse fundIdText with
+            | false, _ -> errorResponse 400 "INVALID_BOND_SELL_REQUEST" "fund id must be a UUID" next ctx
+            | true, fundId ->
+                try
+                    match repository.GetFund fundId with
+                    | None -> errorResponse 404 "FUND_NOT_FOUND" "fund was not found" next ctx
+                    | Some fund ->
+                        let sells =
+                            repository.GetBondSells fundId |> List.map bondSellResponse
+
+                        json ({ fundId = fund.Id; sells = sells } : BondSellsResponse) next ctx
+                with _ ->
+                    errorResponse 500 "PERSISTENCE_ERROR" "bond sell persistence failed" next ctx
+
+    let private getBondCalendar (repository: FundRepository) (fundIdText: string) : HttpHandler =
+        fun next ctx ->
+            match Guid.TryParse fundIdText with
+            | false, _ -> errorResponse 400 "INVALID_BOND_CALENDAR_REQUEST" "fund id must be a UUID" next ctx
+            | true, fundId ->
+                let today = DateOnly.FromDateTime DateTime.UtcNow
+
+                let parseDate (raw: string) (fallback: DateOnly) =
+                    if String.IsNullOrWhiteSpace raw then
+                        fallback
+                    else
+                        match DateOnly.TryParseExact(raw, "yyyy-MM-dd", CultureInfo.InvariantCulture, DateTimeStyles.None) with
+                        | true, date -> date
+                        | _ -> fallback
+
+                let from = parseDate (ctx.Request.Query["from"].ToString()) today
+                let toDate = parseDate (ctx.Request.Query["to"].ToString()) (today.AddYears 1)
+
+                try
+                    match repository.GetFund fundId with
+                    | None -> errorResponse 404 "FUND_NOT_FOUND" "fund was not found" next ctx
+                    | Some fund ->
+                        let positions = repository.GetBondPositions fundId
+
+                        let termsByCode =
+                            repository.GetBondTrades fundId
+                            |> List.fold
+                                (fun acc trade ->
+                                    match trade.ValueDate, trade.MaturityDate, trade.CouponRate with
+                                    | Some valueDate, Some maturityDate, Some couponRate ->
+                                        Map.add
+                                            trade.InstrumentCode
+                                            (BondTerms.create trade.ParValue couponRate 1 valueDate maturityDate 10m 0 0m)
+                                            acc
+                                    | _ -> acc)
+                                Map.empty
+
+                        let scheduled =
+                            positions
+                            |> List.collect (fun position ->
+                                match termsByCode |> Map.tryFind position.InstrumentCode with
+                                | None -> []
+                                | Some terms ->
+                                    let coupons =
+                                        BondRules.couponSchedule terms
+                                        |> List.filter (fun date ->
+                                            date > terms.ValueDate
+                                            && date < terms.MaturityDate
+                                            && date >= from
+                                            && date <= toDate)
+                                        |> List.map (fun date ->
+                                            {
+                                                instrumentCode = position.InstrumentCode
+                                                bondName = position.BondName
+                                                eventType = "coupon"
+                                                eventDate = dateText date
+                                                quantity = decimalText position.Quantity
+                                                source = "scheduled"
+                                                amount = None
+                                                note = None
+                                            })
+
+                                    let maturity =
+                                        if terms.MaturityDate >= from && terms.MaturityDate <= toDate then
+                                            [
+                                                {
+                                                    instrumentCode = position.InstrumentCode
+                                                    bondName = position.BondName
+                                                    eventType = "maturity"
+                                                    eventDate = dateText terms.MaturityDate
+                                                    quantity = decimalText position.Quantity
+                                                    source = "scheduled"
+                                                    amount = None
+                                                    note = None
+                                                }
+                                            ]
+                                        else
+                                            []
+
+                                    coupons @ maturity)
+
+                        let recorded =
+                            repository.GetBondCashflows fundId
+                            |> List.filter (fun event -> event.EventDate >= from && event.EventDate <= toDate)
+                            |> List.map (fun event ->
+                                {
+                                    instrumentCode = event.InstrumentCode
+                                    bondName = event.BondName
+                                    eventType = event.EventType
+                                    eventDate = dateText event.EventDate
+                                    quantity = decimalText event.Quantity
+                                    source = "recorded"
+                                    amount = Some(cashText event.Amount)
+                                    note = event.Note
+                                })
+
+                        let entries =
+                            (recorded @ scheduled) |> List.sortBy (fun entry -> entry.eventDate, entry.instrumentCode)
+
+                        json
+                            ({ fundId = fund.Id
+                               fromDate = dateText from
+                               toDate = dateText toDate
+                               entries = entries }
+                             : BondCalendarResponse)
+                            next
+                            ctx
+                with _ ->
+                    errorResponse 500 "PERSISTENCE_ERROR" "bond calendar failed" next ctx
+
     let private recordBondCashflow (repository: FundRepository) (fundIdText: string) : HttpHandler =
         fun next ctx ->
             task {
@@ -3065,8 +3400,11 @@ module App =
                 GET >=> routef "/funds/%s/stock-positions" (getStockPositions repository)
                 POST >=> routef "/funds/%s/bond-trades" (createBondTrade repository probes)
                 GET >=> routef "/funds/%s/bond-positions" (getBondPositions repository)
+                POST >=> routef "/funds/%s/bond-sells" (createBondSell repository probes)
+                GET >=> routef "/funds/%s/bond-sells" (getBondSells repository)
                 POST >=> routef "/funds/%s/bond-cashflows" (recordBondCashflow repository)
                 GET >=> routef "/funds/%s/bond-cashflows" (getBondCashflows repository)
+                GET >=> routef "/funds/%s/bond-calendar" (getBondCalendar repository)
                 GET >=> routef "/funds/%s/valuation" (getFundValuation repository probes)
                 POST >=> routef "/funds/%s/market-data/refresh" (fun fundId -> refreshFundMarketData repository marketData probes fundId)
                 GET >=> routef "/funds/%s" (getFund repository)
diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs
index 2471c58..9ccb0c7 100644
--- a/src/FundLab.Api/Persistence.fs
+++ b/src/FundLab.Api/Persistence.fs
@@ -444,6 +444,52 @@ type BondPositionRecord =
         LastTradedAt: DateTimeOffset
     }
 
+type BondSellCommand =
+    {
+        InstrumentCode: string
+        BondName: string option
+        Quantity: decimal
+        /// All-in (dirty/全价) sell price per 100 of face value.
+        Price: decimal
+        CleanPrice: decimal
+        AccruedInterest: decimal
+        ParValue: decimal
+        SettlementDate: DateOnly
+        TradeDate: DateOnly option
+        FeeAmount: decimal
+    }
+
+type BondSellRecord =
+    {
+        Id: Guid
+        FundId: Guid
+        InstrumentCode: string
+        BondName: string option
+        Quantity: decimal
+        Price: decimal
+        CleanPrice: decimal
+        AccruedInterest: decimal
+        ParValue: decimal
+        SettlementDate: DateOnly
+        TradeDate: DateOnly
+        FeeAmount: decimal
+        /// Cash credited to the fund after fees (dirty amount minus fee).
+        Proceeds: decimal
+        /// Average-cost basis removed from the position.
+        CostReleased: decimal
+        RealizedPnl: decimal
+        IsSynthetic: bool
+        ExecutedAt: DateTimeOffset
+    }
+
+type BondSellWriteResult =
+    | BondSellCreated of BondSellRecord
+    | BondSellReplayed of BondSellRecord
+    | BondSellIdempotencyConflict
+    | BondSellInvalid of string
+    | BondSellInsufficientHoldings of string
+    | BondSellFundNotFound
+
 type InstrumentSnapshotRecord =
     {
         InstrumentCode: string
@@ -1096,7 +1142,7 @@ type FundRepository(connectionString: string) =
             fund_id uuid NOT NULL REFERENCES funds(id),
             instrument_code text NOT NULL,
             bond_name text NULL,
-            event_type text NOT NULL CHECK (event_type IN ('coupon', 'maturity')),
+            event_type text NOT NULL CHECK (event_type IN ('coupon', 'maturity', 'redemption')),
             event_date date NOT NULL,
             quantity numeric(28, 8) NOT NULL CHECK (quantity > 0),
             amount numeric(20, 2) NOT NULL CHECK (amount >= 0),
@@ -1105,6 +1151,9 @@ type FundRepository(connectionString: string) =
             created_at timestamptz NOT NULL
         );
 
+        ALTER TABLE bond_cashflow_events DROP CONSTRAINT IF EXISTS bond_cashflow_events_event_type_check;
+        ALTER TABLE bond_cashflow_events ADD CONSTRAINT bond_cashflow_events_event_type_check CHECK (event_type IN ('coupon', 'maturity', 'redemption'));
+
         CREATE TABLE IF NOT EXISTS bond_cashflow_idempotencies (
             idempotency_key text PRIMARY KEY,
             request_hash text NOT NULL,
@@ -1113,6 +1162,34 @@ type FundRepository(connectionString: string) =
             created_at timestamptz NOT NULL DEFAULT now()
         );
 
+        CREATE TABLE IF NOT EXISTS bond_sells (
+            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),
+            clean_price numeric(20, 4) NOT NULL DEFAULT 0,
+            accrued_interest numeric(20, 4) NOT NULL DEFAULT 0,
+            par_value numeric(20, 4) NOT NULL DEFAULT 100,
+            settlement_date date NOT NULL DEFAULT CURRENT_DATE,
+            trade_date date NOT NULL DEFAULT CURRENT_DATE,
+            fee_amount numeric(20, 2) NOT NULL DEFAULT 0 CHECK (fee_amount >= 0),
+            proceeds numeric(20, 2) NOT NULL DEFAULT 0,
+            cost_released numeric(20, 2) NOT NULL DEFAULT 0,
+            realized_pnl numeric(20, 2) NOT NULL DEFAULT 0,
+            is_synthetic boolean NOT NULL,
+            executed_at timestamptz NOT NULL
+        );
+
+        CREATE TABLE IF NOT EXISTS bond_sell_idempotencies (
+            idempotency_key text PRIMARY KEY,
+            request_hash text NOT NULL,
+            sell_id uuid NOT NULL REFERENCES bond_sells(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,
@@ -2540,6 +2617,154 @@ type FundRepository(connectionString: string) =
 
         Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload)))
 
+    let bondSellRecordFromReader (reader: DbDataReader) : BondSellRecord =
+        {
+            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)
+            CleanPrice = reader.GetDecimal(6)
+            AccruedInterest = reader.GetDecimal(7)
+            ParValue = reader.GetDecimal(8)
+            SettlementDate = reader.GetFieldValue<DateOnly>(9)
+            TradeDate = reader.GetFieldValue<DateOnly>(10)
+            FeeAmount = reader.GetDecimal(11)
+            Proceeds = reader.GetDecimal(12)
+            CostReleased = reader.GetDecimal(13)
+            RealizedPnl = reader.GetDecimal(14)
+            IsSynthetic = reader.GetBoolean(15)
+            ExecutedAt = reader.GetFieldValue<DateTimeOffset>(16)
+        }
+
+    let bondSellColumns =
+        "id, fund_id, instrument_code, bond_name, quantity, price, clean_price, accrued_interest, par_value, settlement_date, trade_date, fee_amount, proceeds, cost_released, realized_pnl, is_synthetic, executed_at"
+
+    let insertBondSell connection transaction (record: BondSellRecord) =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                INSERT INTO bond_sells
+                    (id, fund_id, instrument_code, bond_name, quantity, price, clean_price, accrued_interest,
+                     par_value, settlement_date, trade_date, fee_amount, proceeds, cost_released, realized_pnl,
+                     is_synthetic, executed_at)
+                VALUES
+                    (@id, @fund_id, @instrument_code, @bond_name, @quantity, @price, @clean_price, @accrued_interest,
+                     @par_value, @settlement_date, @trade_date, @fee_amount, @proceeds, @cost_released, @realized_pnl,
+                     @is_synthetic, @executed_at)
+                """
+
+        addParameter command "id" NpgsqlDbType.Uuid (box record.Id) |> ignore
+        addParameter command "fund_id" NpgsqlDbType.Uuid (box record.FundId) |> ignore
+        addParameter command "instrument_code" NpgsqlDbType.Text (box record.InstrumentCode) |> ignore
+
+        let nameParameter =
+            match record.BondName with
+            | Some name -> box name
+            | None -> box DBNull.Value
+
+        addParameter command "bond_name" NpgsqlDbType.Text nameParameter |> ignore
+        addParameter command "quantity" NpgsqlDbType.Numeric (box record.Quantity) |> ignore
+        addParameter command "price" NpgsqlDbType.Numeric (box record.Price) |> ignore
+        addParameter command "clean_price" NpgsqlDbType.Numeric (box record.CleanPrice) |> ignore
+        addParameter command "accrued_interest" NpgsqlDbType.Numeric (box record.AccruedInterest) |> ignore
+        addParameter command "par_value" NpgsqlDbType.Numeric (box record.ParValue) |> ignore
+        addParameter command "settlement_date" NpgsqlDbType.Date (box record.SettlementDate) |> ignore
+        addParameter command "trade_date" NpgsqlDbType.Date (box record.TradeDate) |> ignore
+        addParameter command "fee_amount" NpgsqlDbType.Numeric (box record.FeeAmount) |> ignore
+        addParameter command "proceeds" NpgsqlDbType.Numeric (box record.Proceeds) |> ignore
+        addParameter command "cost_released" NpgsqlDbType.Numeric (box record.CostReleased) |> ignore
+        addParameter command "realized_pnl" NpgsqlDbType.Numeric (box record.RealizedPnl) |> ignore
+        addParameter command "is_synthetic" NpgsqlDbType.Boolean (box record.IsSynthetic) |> ignore
+        addParameter command "executed_at" NpgsqlDbType.TimestampTz (box record.ExecutedAt) |> ignore
+        command.ExecuteNonQuery() |> ignore
+
+    let insertBondSellIdempotency connection transaction key requestHash sellId fundId =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                INSERT INTO bond_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 findBondSellIdempotency connection transaction key =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                SELECT request_hash, fund_id, sell_id
+                FROM bond_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 findBondSell connection transaction sellId =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                $"""
+                SELECT {bondSellColumns}
+                FROM bond_sells
+                WHERE id = @id
+                """
+
+        addParameter command "id" NpgsqlDbType.Uuid (box sellId) |> ignore
+
+        use reader = command.ExecuteReader()
+
+        if reader.Read() then
+            Some(bondSellRecordFromReader reader)
+        else
+            None
+
+    let bondSellRequestHash (fundId: Guid) (command: BondSellCommand) =
+        let invariant = CultureInfo.InvariantCulture
+        let encoded (value: string) = sprintf "%d:%s" value.Length value
+        let name = command.BondName |> Option.defaultValue ""
+        let dateText (value: DateOnly) = value.ToString("yyyy-MM-dd", invariant)
+        let optionTradeDate = command.TradeDate |> Option.map dateText |> Option.defaultValue ""
+
+        let payload =
+            String.concat
+                "|"
+                [
+                    "bond-sell"
+                    encoded (fundId.ToString("D"))
+                    encoded command.InstrumentCode
+                    encoded name
+                    encoded (command.Quantity.ToString("G29", invariant))
+                    encoded (command.Price.ToString("G29", invariant))
+                    encoded (command.CleanPrice.ToString("G29", invariant))
+                    encoded (command.AccruedInterest.ToString("G29", invariant))
+                    encoded (command.ParValue.ToString("G29", invariant))
+                    encoded (dateText command.SettlementDate)
+                    encoded optionTradeDate
+                    encoded (command.FeeAmount.ToString("G29", invariant))
+                ]
+
+        Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload)))
+
     let sipPlanRecordFromReader (reader: DbDataReader) : SipPlanRecord =
         {
             Id = reader.GetGuid(0)
@@ -5590,8 +5815,8 @@ type FundRepository(connectionString: string) =
             BondCashflowWriteResult.BondCashflowInvalid "idempotency key cannot be empty"
         elif code.Length <> 6 || not (code |> Seq.forall Char.IsDigit) then
             BondCashflowWriteResult.BondCashflowInvalid "bond code must contain exactly six digits"
-        elif eventType <> "coupon" && eventType <> "maturity" then
-            BondCashflowWriteResult.BondCashflowInvalid "event type must be coupon or maturity"
+        elif eventType <> "coupon" && eventType <> "maturity" && eventType <> "redemption" then
+            BondCashflowWriteResult.BondCashflowInvalid "event type must be coupon, maturity or redemption"
         elif command.Quantity <= 0m then
             BondCashflowWriteResult.BondCashflowInvalid "quantity must be positive"
         elif command.Amount < 0m then
@@ -5676,7 +5901,7 @@ type FundRepository(connectionString: string) =
                             addParameter cashCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
                             cashCommand.ExecuteNonQuery() |> ignore
 
-                            if normalized.EventType = "maturity" then
+                            if normalized.EventType = "maturity" || normalized.EventType = "redemption" then
                                 use removeCommand =
                                     commandWithTransaction
                                         connection
@@ -5722,6 +5947,211 @@ type FundRepository(connectionString: string) =
 
         records |> Seq.toList
 
+    member _.CreateBondSell(idempotencyKey: string, fundId: Guid, command: BondSellCommand, ?executedAtOverride: DateTimeOffset) : BondSellWriteResult =
+        let code = if isNull command.InstrumentCode then "" else command.InstrumentCode.Trim()
+
+        if String.IsNullOrWhiteSpace idempotencyKey then
+            BondSellWriteResult.BondSellInvalid "idempotency key cannot be empty"
+        elif code.Length <> 6 || not (code |> Seq.forall Char.IsDigit) then
+            BondSellWriteResult.BondSellInvalid "bond code must contain exactly six digits"
+        elif command.Quantity <= 0m then
+            BondSellWriteResult.BondSellInvalid "quantity must be positive"
+        elif command.Price <= 0m then
+            BondSellWriteResult.BondSellInvalid "price must be positive"
+        elif command.CleanPrice <= 0m then
+            BondSellWriteResult.BondSellInvalid "clean price must be positive"
+        elif command.ParValue <= 0m then
+            BondSellWriteResult.BondSellInvalid "par value must be positive"
+        elif command.AccruedInterest < 0m then
+            BondSellWriteResult.BondSellInvalid "accrued interest cannot be negative"
+        elif command.FeeAmount < 0m then
+            BondSellWriteResult.BondSellInvalid "fee cannot be negative"
+        else
+            let normalized = { command with InstrumentCode = code }
+            let fingerprint = bondSellRequestHash 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 findBondSellIdempotency connection (Some transaction) idempotencyKey with
+                | Some(existingHash, existingFundId, sellId)
+                    when existingHash = fingerprint && existingFundId = fundId ->
+                    match findBondSell connection (Some transaction) sellId with
+                    | Some record ->
+                        transaction.Commit()
+                        BondSellWriteResult.BondSellReplayed record
+                    | None ->
+                        transaction.Rollback()
+                        BondSellWriteResult.BondSellInvalid "idempotency record references a missing sell"
+                | Some _ ->
+                    transaction.Rollback()
+                    BondSellWriteResult.BondSellIdempotencyConflict
+                | None ->
+                    match lockFundForOrder connection (Some transaction) fundId with
+                    | None ->
+                        transaction.Rollback()
+                        BondSellWriteResult.BondSellFundNotFound
+                    | Some isSynthetic ->
+                        let position =
+                            use positionQuery =
+                                commandWithTransaction
+                                    connection
+                                    (Some transaction)
+                                    "SELECT quantity, cost_cash FROM bond_positions WHERE fund_id = @fund_id AND instrument_code = @code FOR UPDATE"
+
+                            addParameter positionQuery "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+                            addParameter positionQuery "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore
+                            use reader = positionQuery.ExecuteReader()
+                            if reader.Read() then Some(reader.GetDecimal(0), reader.GetDecimal(1)) else None
+
+                        match position with
+                        | None ->
+                            transaction.Rollback()
+                            BondSellWriteResult.BondSellInsufficientHoldings "fund does not hold this bond"
+                        | Some(positionQuantity, positionCost) ->
+                            if normalized.Quantity > positionQuantity then
+                                transaction.Rollback()
+
+                                BondSellWriteResult.BondSellInsufficientHoldings(
+                                    sprintf "cannot sell %O 张: only %O held" normalized.Quantity positionQuantity
+                                )
+                            else
+                                let executedAt = defaultArg executedAtOverride DateTimeOffset.UtcNow
+
+                                let gross =
+                                    Decimal.Round(
+                                        normalized.Quantity * normalized.Price * normalized.ParValue / 100m,
+                                        2,
+                                        MidpointRounding.AwayFromZero
+                                    )
+
+                                let proceeds = Decimal.Round(gross - normalized.FeeAmount, 2, MidpointRounding.AwayFromZero)
+
+                                if proceeds < 0m then
+                                    transaction.Rollback()
+                                    BondSellWriteResult.BondSellInvalid "fee exceeds gross proceeds"
+                                else
+                                    let costReleased =
+                                        if normalized.Quantity = positionQuantity then
+                                            positionCost
+                                        else
+                                            Decimal.Round(
+                                                positionCost * normalized.Quantity / positionQuantity,
+                                                2,
+                                                MidpointRounding.AwayFromZero
+                                            )
+
+                                    let realizedPnl = Decimal.Round(proceeds - costReleased, 2, MidpointRounding.AwayFromZero)
+
+                                    let record: BondSellRecord =
+                                        {
+                                            Id = Guid.NewGuid()
+                                            FundId = fundId
+                                            InstrumentCode = normalized.InstrumentCode
+                                            BondName = normalized.BondName
+                                            Quantity = normalized.Quantity
+                                            Price = normalized.Price
+                                            CleanPrice = normalized.CleanPrice
+                                            AccruedInterest = normalized.AccruedInterest
+                                            ParValue = normalized.ParValue
+                                            SettlementDate = normalized.SettlementDate
+                                            TradeDate = normalized.TradeDate |> Option.defaultValue normalized.SettlementDate
+                                            FeeAmount = normalized.FeeAmount
+                                            Proceeds = proceeds
+                                            CostReleased = costReleased
+                                            RealizedPnl = realizedPnl
+                                            IsSynthetic = isSynthetic
+                                            ExecutedAt = executedAt
+                                        }
+
+                                    insertBondSell connection (Some transaction) record
+                                    insertBondSellIdempotency connection (Some transaction) idempotencyKey fingerprint record.Id fundId
+
+                                    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
+
+                                    if normalized.Quantity = positionQuantity then
+                                        use removeCommand =
+                                            commandWithTransaction
+                                                connection
+                                                (Some transaction)
+                                                "DELETE FROM bond_positions WHERE fund_id = @fund_id AND instrument_code = @code"
+
+                                        addParameter removeCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+                                        addParameter removeCommand "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore
+                                        removeCommand.ExecuteNonQuery() |> ignore
+                                    else
+                                        use positionCommand =
+                                            commandWithTransaction
+                                                connection
+                                                (Some transaction)
+                                                """
+                                                UPDATE bond_positions
+                                                SET quantity = quantity - @quantity,
+                                                    cost_cash = cost_cash - @cost_released,
+                                                    last_traded_at = @last_traded_at
+                                                WHERE fund_id = @fund_id AND instrument_code = @code
+                                                """
+
+                                        addParameter positionCommand "quantity" NpgsqlDbType.Numeric (box normalized.Quantity) |> ignore
+                                        addParameter positionCommand "cost_released" NpgsqlDbType.Numeric (box costReleased) |> ignore
+                                        addParameter positionCommand "last_traded_at" NpgsqlDbType.TimestampTz (box executedAt) |> ignore
+                                        addParameter positionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+                                        addParameter positionCommand "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore
+                                        positionCommand.ExecuteNonQuery() |> ignore
+
+                                    transaction.Commit()
+                                    BondSellWriteResult.BondSellCreated record
+            with error ->
+                try
+                    transaction.Rollback()
+                with _ ->
+                    ()
+
+                raise error
+
+    member _.GetBondSells(fundId: Guid) : BondSellRecord list =
+        use connection = new NpgsqlConnection(connectionString)
+        connection.Open()
+
+        use command =
+            commandWithTransaction
+                connection
+                None
+                $"""
+                SELECT {bondSellColumns}
+                FROM bond_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<BondSellRecord>()
+
+        while reader.Read() do
+            records.Add(bondSellRecordFromReader reader)
+
+        records |> Seq.toList
+
     member _.GetBondPositions(fundId: Guid) : BondPositionRecord list =
         use connection = new NpgsqlConnection(connectionString)
         connection.Open()